ETL / ELT Pipeline Design
The contract between source, transform, and consumer
Pipeline design is about contracts: what lands where, who transforms, what governs freshness and quality. ETL transforms before load; ELT loads raw and transforms in the warehouse — the modern default.
What you will learn
- Compare ETL vs ELT and when each fits
- Define a pipeline contract: source, landing, model, SLA
- Sketch a single pipeline from source to dashboard
What is this?
A pipeline moves data from source to consumer. ETL transforms before loading into the target; ELT loads raw first and transforms inside the warehouse. Modern practice leans ELT because warehouses compute cheaply and raw copies stay auditable. Either way, you need a contract: source shape, landing zone, modeled output, freshness SLA, and quality expectations.
Why do you need it?
- Every failure in this module traces back to a missing or bent contract.
- ELT vs ETL changes where your compute dollars and debugging time go.
- A clean pipeline design is the difference between a daily firefight and a weekly review.
Mental model
A restaurant: order ticket → kitchen → table
A data pipeline
The ticket tells the kitchen what to cook and when (contract); the kitchen transforms ingredients (transform); dishes hit the table quickest when prep happens in the kitchen that's already hot (ELT in the warehouse, transform where compute is cheap).
How it works
- Source extraction: pull or push — batch queries, file drops, CDC streams, or API paging.
- Landing: raw copy before any transformation; immutable and replayable.
- Transform: normalize, type, dedupe, model — in ELT this SQL runs in the warehouse, not a separate server.
- Load/publish: write modeled outputs that analysts consume with owned freshness SLAs.
- Orchestration: schedule, retry, backfill, alert — a DAG is a first-class citizen, not an afterthought.
ELT: raw lands, warehouse transforms.
Examples
A contract in a few sentences
# Pipeline: shop.orders_daily# Source : shopDB.orders (CDC-friendly updated_at)# Landing : lake/raw/orders/*.parquet (immutable)# Model : marts.fct_order_lines (grain: order line)# Freshness : 4h SLA, alert after 6h# Quality gate : row_count > threshold, no dup order_line_id# On failure : retry 2x, then page on-callA written contract lets a new engineer debug in minutes and lets the alert wake the right person.
In production
- A nightly ELT lands raw orders, and dbt turns them into star marts inside the warehouse.
- A streaming pipeline writes Avro to the lake; an hourly batch ELT adds the modeled BI layer.
- A finance pipeline runs ETL on a dedicated server because the warehouse lacks the needed compute/features.
Best practices
- Write the contract down; freshness/quality expectations belong in code and docs.
- Keep raw landing immutable so you can always reproduce a model from source.
- Prefer ELT where the warehouse compute is cheap and the transformation is SQL.
- Design for retry: idempotent tasks that can rerun with no double-counting.
Common mistakes
- No raw landing; transformations write over source-shaped data, losing auditability.
- Backing ETL/ELT with cron instead of a DAG tool, then having no dependency ordering or retries.
- Ignoring freshness until a dashboard launches stale and the meeting gets spicy.
Debugging tips
- When a pipeline 'works' but numbers are wrong, question the contract: grain, filters, dedup.
- Trace failures to node and rerun idempotently instead of hand-fixing the data.
- Check landing parity: if raw equals source, the bug is downstream of landing.
Interview checkpoint
Hands-on lab
- Land raw source data immutably
- Stage and model it with sequential SQL steps
- Run a quality check between steps
Build a Minimal ELT on DuckDB
35 min · beginner
Quick quiz
In ELT, the 'T' happens…
A pipeline contract includes the grain and freshness SLA, not just the schedule.