An ETL pipeline is the route data takes from the systems that create it to the place where someone makes a decision with it. Extract, transform, load describes three verbs. Architecture describes the promises around them: that the same input produces the same output, that a failed run can be repeated safely, and that a number on a dashboard can be traced back to a record.
This explainer is for managers and product owners who need to understand what they are buying or reviewing. It describes a reference architecture, walks through a commerce example, and names the design decisions that separate a pipeline that survives from one that needs a person to babysit it.
ETL and ELT are not the same bet
In classic ETL, data is transformed before it enters the warehouse. In ELT, raw data lands first and transformation happens inside the warehouse or lakehouse using its compute. ELT has become common because storage is cheaper and warehouse engines are capable, but the choice is not automatic.
ETL still fits when data must be reduced or masked before storage, when the target has limited compute, or when a regulated dataset should not land in raw form. ELT fits when you want to preserve raw history and iterate on transformations without re-extracting from source systems. Either way, the pipeline needs the same controls.
The reference flow
- Source inventory. Name every system, owner, identifier and extraction method. Include spreadsheets and manual exports; they are sources too.
- Extraction. Pull full or incremental data using a stable cursor such as an updated timestamp, sequence number or change data capture stream.
- Landing zone. Store the raw payload exactly as received, with an ingestion timestamp and source reference. This is the evidence layer.
- Validation and staging. Check schema, required fields, duplicates and referential integrity before transformation.
- Transformation. Apply business rules: currency conversion, tax treatment, status mapping, deduplication and derived measures.
- Load. Write to the warehouse or mart using an idempotent pattern such as merge or partition replace.
- Orchestration. Schedule tasks, manage dependencies, retries, backfills and alerts.
- Observability and governance. Track freshness, volume, failures, lineage, access and cost.
The landing zone is often skipped to save storage. That decision makes every later dispute harder, because the team can no longer prove what the source system actually sent.
A worked example
An online retailer wants a daily report of gross margin by product and delivery performance by courier. The sources are Shopify orders, a warehouse stock table, a payment provider settlement file and a courier status export.
The pipeline extracts orders incrementally by update timestamp, lands the raw JSON, and stages one row per order line. It validates that every line has a variant identifier and a price. It joins stock movements to calculate cost of goods, and joins settlement records to distinguish collected revenue from invoiced revenue. A separate branch maps courier status events into four states: dispatched, delivered, returned and failed.
The output is two marts: margin by product per day, and delivery performance by courier per week. Orchestration runs the order branch first, then the settlement and courier branches, then the marts. If the courier file arrives late, the pipeline marks the delivery mart as stale rather than publishing a partial week.
Design decisions that decide the outcome
Idempotency
A pipeline will be re-run. A backfill, a bug fix or a provider outage guarantees it. Every write must be safe to repeat: merge on a natural key, replace a partition, or delete and insert within a transaction. A pipeline that appends on every run doubles the revenue when someone retries it.
Incremental extraction
Full extracts are simple and increasingly expensive. Incremental extracts need a reliable cursor, a late-arrival window and a plan for records that change without updating the cursor. Test what happens when a record is edited during extraction.
Late-arriving data
Orders, refunds and courier events can arrive days after the business date. Decide how many days the pipeline will reopen. A common pattern is to recompute a rolling window rather than only today's partition.
Schema drift
Source systems add fields and change types. A pipeline that fails loudly on an unexpected column is safer than one that silently drops it. Record the schema version with each load and alert on change.
History and slowly changing dimensions
If a customer changes address or a product changes category, reports need to know which version applied at the time. Type 2 history keeps effective dates; type 1 overwrites. Choose per dimension and document the choice.
Replay and lineage
Every output should be reproducible from a named input set. Store the run identifier, source snapshot and code version. When a number is questioned, the team should be able to reconstruct the answer rather than explain the pipeline from memory.
Failure modes to design for
| Symptom | Likely cause | Design response |
|---|---|---|
| Revenue doubles after a retry | Append-only load without a key | Idempotent merge or partition replace |
| Yesterday is missing from the dashboard | Late source file or failed dependency | Freshness check and stale-state flag |
| Totals differ from the source system | Filter applied during extraction | Reconcile row counts and sums at the landing zone |
| Product names change retroactively | Dimension overwritten in place | Effective-dated history |
| Pipeline succeeds but data is wrong | Tests check execution, not content | Data quality tests on keys, ranges and relationships |
| Cloud bill rises without volume growth | Full refreshes and unpartitioned scans | Incremental models, partitioning and cost monitoring |
When you do not need this
If one person exports a CSV once a month and the answer fits in a spreadsheet, a warehouse pipeline is overkill. The architecture starts to pay when multiple sources must be joined repeatedly, when the same definitions are rebuilt by different people, or when a decision needs data fresher than the monthly export.
Common misconceptions
- A pipeline is a script. A script moves data once. A pipeline has ownership, scheduling, testing, retries and documentation.
- The warehouse is the hard part. The hard part is agreeing what a customer, an order and revenue mean across systems.
- More tests mean more confidence. Tests that nobody reads or acts on add cost without reducing risk.
- Real-time is always better. Freshness has a cost. Daily data is sufficient for many management decisions.
- Once built, it runs itself. Sources change, volumes grow and providers deprecate endpoints. Pipelines need maintenance.
Where our team fits
We design pipelines, data models and reporting layers under Business Automation. Scope and starting points are on the pricing page. If you are selecting tools and partners rather than learning the architecture, read ETL pipeline architecture: a buying guide.
Sources
- dbt Documentation, Introduction, accessed 17 November 2026.
- Apache Airflow Documentation, accessed 17 November 2026.
- Apache Spark Documentation, accessed 17 November 2026.
- Apache Iceberg Documentation, accessed 17 November 2026.
Responses (0)
No comments yet. Add the first one below.
Leave a response
Your email address will not be published. Required fields are marked *.