Extract, transform, load (ETL) is a process for moving data from the systems where it is created into a central place where it can be analyzed. It extracts data from sources such as business applications, databases and files; transforms it by cleaning, standardizing, combining and reshaping it; and loads the result into a target, most often a data warehouse. ETL is the plumbing behind most business reporting and dashboards, and the same term is used for both the process and the tools that run it.
Not to be confused with early termination liability, a contract charge sometimes abbreviated ETL; see early termination fee (ETF).
At a glance
- Three steps: extract from sources, transform into a consistent shape, load into a target system.
- The target is typically a data warehouse, but can also be a data lake, database or another application.
- Transformations include fixing formats, removing duplicates, matching records across systems, calculating fields and masking sensitive data.
- Runs on a schedule in batches, or continuously in near real time, depending on the tool and the need.
- ELT, a variant that loads first and transforms inside the target, has become common with cloud data warehouses.
What problem it solves
Business data lives in many places: the CRM holds customers, the ERP holds orders, the billing system holds invoices, and spreadsheets fill the gaps. Each system uses its own formats, codes and definitions, so a simple question such as “which customers are most profitable?” can’t be answered from any one of them. Teams end up exporting files and stitching them together by hand, which is slow, error-prone and hard to repeat.
ETL automates that work. It pulls data from each system on a reliable schedule, applies agreed rules so a customer or product means the same thing everywhere, and delivers clean, combined data to a place where analytics and business intelligence tools can use it. Done well, it is what makes numbers in reports consistent and trustworthy.
How it works
Extract. Connectors read data from source systems through APIs, database queries, file exports or change data capture, which picks up only records that changed since the last run. Extraction is designed to avoid slowing down the source systems that people use day to day.
Transform. The data is cleaned and reshaped in a processing engine or staging area. Typical steps include converting dates and currencies, standardizing codes, removing duplicates, joining records from different systems, applying business rules and aggregating figures. Sensitive fields can be masked or removed before data moves further.
Load. The transformed data is written to the target, either replacing previous data or adding new and changed records. Loads are usually scheduled to finish before people need the data, such as before business hours.
Orchestration and monitoring. A scheduler runs pipelines in the right order, retries failures and alerts someone when a job breaks or data looks wrong. Without monitoring, failed loads often go unnoticed until a report is visibly wrong.
Tools. Options range from custom scripts to commercial ETL software, open-source frameworks and fully managed cloud data integration services with prebuilt connectors to common SaaS applications. Pricing models vary, including by connector, data volume, rows processed or compute time.
For help connecting data to reporting, see our analytics and business intelligence page.
When it matters for buyers
- Building or replacing a data warehouse. The pipelines feeding it are often a larger and longer-running cost than the warehouse itself.
- Adding new SaaS applications. Each new system is another source to connect; check whether your tools support it.
- Reporting disagreements. When dashboards don’t match source systems, the transformation rules and load failures are the first places to look.
- Privacy and compliance. Pipelines copy personal data into new places; data governance and data classification should cover them.
- Cost reviews. Usage-based integration pricing can grow quickly with data volume and frequency.
Questions to ask vendors
- Which of our source systems have prebuilt connectors, and how are new or custom sources handled?
- How is pricing calculated: by connector, rows, data volume, compute time or users?
- Do you support both ETL and ELT patterns, and batch as well as near-real-time loading?
- How are failures detected, retried and reported, and who is alerted?
- How do you handle changes in source systems, such as new or renamed fields?
- How are credentials to our source systems stored and protected?
- Can sensitive fields be masked or excluded before data leaves the source environment?
How it differs from ELT
ELT (extract, load, transform) uses the same three steps in a different order. Raw data is loaded into the target first, usually a cloud data warehouse or lakehouse, and transformed there using the target’s own processing power, often with SQL. Because modern cloud warehouses can scale processing on demand, ELT is simpler to set up and keeps raw data available for new uses. ETL transforms data before it lands, which suits targets with limited processing power, strict control over what is stored, or a need to mask sensitive data before it reaches the target. Many organizations use both, and many tools support both patterns.