Module 15 · Big data processing · Lesson 4 of 10
ETL and ELT pipelines
Moving data from operational systems into analytics: extract, transform, load, and the modern ELT variant that transforms inside the warehouse. Orchestration as a DAG, idempotent and incremental loads, backfills, data quality checks.
Why it matters
Every company eventually needs answers that span systems: revenue by marketing channel, churn by product usage, inventory against demand. The data lives in a dozen operational databases and SaaS tools, none of which can answer cross-system questions or should be queried by analysts. Pipelines move and reshape that data into a place built for analysis. They are unglamorous and they are where most data incidents happen: a silent schema change, a double-loaded day, a job that "succeeded" with zero rows.
The idea
ETL extracts data from sources, transforms it (clean, join, aggregate, conform) on a processing engine, and loads the result into the analytics store. ELT extracts and loads raw data into the warehouse first, then transforms it there with SQL. Cheap, elastic warehouse compute made ELT the modern default: raw data is preserved, transformations are versioned SQL (dbt), and analysts can rebuild any model from the raw layer.
| ETL | ELT | |
|---|---|---|
| Transform where | Separate engine (Spark, custom code) before loading | Inside the warehouse with SQL after loading |
| Raw data kept | Often not; only the transformed result lands | Yes; raw layer is the replayable source |
| Who writes transforms | Data engineers in code | Analytics engineers and analysts in SQL (dbt) |
| Fits | Heavy non-SQL processing, sensitive data that must be filtered before landing, constrained warehouses | Most modern analytics stacks |
Orchestration
A pipeline is a DAG of tasks with dependencies: extract orders and customers, then build the orders staging model, then the revenue mart, then refresh dashboards. An orchestrator (Airflow, Dagster, Prefect, cloud workflows) schedules runs, tracks state, retries failures and alerts. Good orchestration is data-aware: a task runs for a specific partition (a date, an hour), so failures and reruns are scoped to that partition.
The properties of a trustworthy pipeline
- 1
Idempotent
Running a task twice for the same partition produces the same result: overwrite the partition (INSERT OVERWRITE, MERGE on keys) instead of appending. Retries and reruns then never double-count. - 2
Incremental
Process only new or changed data (by partition, by updated_at watermark, or from CDC), not a full reload every day, so cost and run time track change volume rather than total size. - 3
Backfillable
Any partition can be recomputed on demand, for a bug fix or a new column, by running the same task for past dates. Idempotence makes this safe. - 4
Tested
Checks between layers: row counts within expected ranges, primary keys unique and not null, foreign keys resolve, freshness within SLA, distributions not shifted wildly. A failing check stops downstream tasks rather than publishing bad numbers. - 5
Observable
Lineage from source column to dashboard, run durations, data freshness per table, and alerts that reach the owner. A pipeline that fails silently is worse than one that fails loudly.
Common failure modes
Schema drift: a source renames a column and the extract silently loads nulls; detect with schema contracts and tests. Late-arriving data: yesterday's partition receives records today; design for reprocessing a window of recent partitions. Duplicates from at-least-once extraction: deduplicate on keys in staging. The "succeeded with zero rows" job: volume checks catch it. Timezone and calendar bugs: store UTC, convert at presentation.
Batch, micro-batch or streaming loads
Nightly loads suit finance reports; hourly suits most operational analytics; CDC streaming into the warehouse gives minutes of freshness for live dashboards. Freshness is a requirement to state per mart, and the cheapest schedule that meets it is the right one.