Data
ETL pipeline
An ETL pipeline is a data workflow that extracts data from source systems, transforms it into a consistent shape and loads it into a warehouse for analysis.
Teams build ETL pipelines because the data they need to analyze lives in a dozen systems with a dozen formats, and a report that joins them by hand is wrong by the time it is finished. The pipeline does the joining once, on a schedule, into one place.
Why does an ETL pipeline matter?
- One source for analysis. Sales, product and finance data end up in the same data warehouse with consistent keys, where anyone can query it.
- History. Source systems overwrite records. A pipeline can keep every version, which is what trend analysis needs.
What are the parts of an ETL pipeline?
The hero diagram shows a nightly pipeline into a warehouse. Every ETL pipeline has the same stages and the same two supporting parts.
- Sources. An application database read through a replica or through change data capture, SaaS products read through their APIs, and files dropped in a bucket. Each has its own format and failure modes.
- Extract. Pull the new or changed data from each source since the last run. Incremental extraction keeps the load on the source small.
- Transform. Clean, deduplicate, convert types, join sources, apply business rules and shape the result into the warehouse’s schema. This is where most of the logic and most of the bugs live.
- Load. Write the result into the destination, usually by replacing a partition or merging on a key so a re-run does not duplicate rows.
- Orchestrator. The scheduler that runs the stages in order, retries failures, enforces dependencies and records what ran.
- Data quality checks. Tests that run before the load: row counts within range, no nulls in keys, no duplicates, totals that reconcile with the source. A failure stops the load and raises an alert.
How does an ETL pipeline work?
Follow the diagram at the top of this page from left to right. At 02:00 the orchestrator starts the extract stage, which reads yesterday’s orders from the app database replica, pulls the day’s tickets from the support tool’s API and picks up the finance team’s CSV exports.
The transform stage converts currencies, standardizes customer IDs across the three sources, joins orders to tickets and builds the fact and dimension tables the warehouse expects. Then the quality checks run. If the order count is within range and no customer key is null, the load replaces yesterday’s partition and the dashboards refresh. If a check fails, nothing is loaded, the on-call engineer is paged and yesterday’s numbers stay in place.
What are the types of ETL pipeline?
| Type | How it moves data | Use it when |
|---|---|---|
| Batch ETL | Runs on a schedule, transforms on its own server, loads modeled tables | Data must be cleaned or masked before it is stored |
| Batch ELT | Loads raw data on a schedule and transforms inside the warehouse with SQL | A cloud warehouse with cheap storage and scalable compute, which is most teams today |
| Streaming | Processes each event as it arrives, with seconds of delay | Decisions depend on data that is minutes old: fraud, monitoring, personalization |
The ETL versus ELT choice is mostly about where the compute lives. Cloud warehouses made storage cheap and SQL fast, so loading raw data and transforming it in place became simpler and kept the raw history for free. ETL still wins when data must be masked before it is stored. ELT pipelines often land data in layers named bronze, silver and gold, which is the medallion architecture.
How do you build an ETL pipeline?
- Start from the questions. List the reports and metrics the pipeline must support, and work back to the sources and fields they need.
- Design the target schema. Model the warehouse tables first, usually as a star schema, so the transform stage has something concrete to aim at.
- Write each stage as an idempotent job. Extract by time window, load by replacing or merging, so any run can be repeated safely.
- Add quality checks before the load. Start with row counts, key nulls and duplicates, then add reconciliation against the source.
- Put it under an orchestrator. Define the dependencies, schedule, retries and alerts, and keep the definition in version control.
- Record lineage. Track which source fields feed which warehouse columns, so a change upstream can be traced to every report it affects; see data lineage.
What are common ETL pipeline mistakes?
- Appending on retry. A job that appends rows duplicates them the first time it is re-run. Replace or merge instead.
- No quality gate. Without checks, a source outage becomes a dashboard that quietly shows zero revenue.
- Monitoring only failures. A pipeline that succeeds with half the rows is worse than one that fails. Alert on volume and freshness too.
How does an ETL pipeline compare with related diagrams?
| Diagram | What it shows | How it relates |
|---|---|---|
| Data flow diagram | Processes, data stores and the flows between them | The general notation; an ETL diagram is a data flow diagram with scheduling and quality added |
| Data lineage | Where each field came from and what depends on it | The column-level record the pipeline should produce |
| Database schema | The tables the pipeline reads and writes | The source and target definitions on either side of the pipeline |
| Data warehouse | The destination built for analysis | What the pipeline fills |
What tools help with ETL pipelines?
- Orchestration tools. Schedulers that define jobs as dependency graphs, run them, retry failures and show what ran.
- Managed ingestion and transformation tools. Services with ready-made connectors for common sources and a framework for SQL transformations with tests, which remove most of the extract and load code.
- AI diagramming tools. Tools like Eraser turn a description of your sources, stages and destination into a data flow diagram you can keep editing as text, which suits the design review before the first job is written.
For more on this kind of diagram, see data flow diagrams.
Frequently asked questions
-
What is the difference between ETL and ELT?
ETL transforms data on its way to the destination, so only modeled tables land in the warehouse. ELT loads the raw data first and transforms it inside the warehouse with SQL. ELT keeps the raw history and uses the warehouse's own compute, which is why most cloud teams use it, while ETL fits when data must be masked before it is stored.
-
What is the difference between an ETL pipeline and a data pipeline?
A data pipeline is any automated flow of data from one system to another, including streaming, replication and reverse ETL back into applications. An ETL pipeline is the kind that extracts from sources, transforms and loads into an analytical store. Every ETL pipeline is a data pipeline, but not the reverse.
-
What is idempotency in an ETL pipeline?
An idempotent pipeline produces the same result however many times it runs for the same period. Loading by replacing a partition or merging on a key is idempotent; appending rows is not, because a retry duplicates them. Idempotency is what makes it safe to re-run a failed job, which every pipeline eventually needs.
-
How often should an ETL pipeline run?
As often as the people reading the data need it to be fresh, and no more. Nightly is enough for most finance and reporting work. Hourly suits operational dashboards. When decisions depend on data that is minutes old, a streaming pipeline is a better fit than a batch job that runs every minute.
