ETL vs ELT
Moving data from sources to where it's used:
- ETL (Extract → Transform → Load) — transform before loading. The classic approach when storage/compute was expensive.
- ELT (Extract → Load → Transform) — load raw data into the warehouse first, then transform inside it with SQL. The modern default, because cloud warehouses (Snowflake, BigQuery) make in-warehouse transformation cheap and flexible. dbt is the standard ELT transformation tool.
Orchestration
Pipelines are graphs of dependent tasks that must run in order, on schedule, with retries and monitoring. Airflow (and Dagster, Prefect) orchestrates them as DAGs (directed acyclic graphs): define tasks and dependencies, schedule runs, handle retries/backfills, and alert on failure. Know: scheduling, dependencies, backfills (re-running for past dates), and idempotency.
Idempotency & reliability (the through-line)
Pipelines fail and get re-run, so a task must be idempotent — running it twice produces the same result, not duplicates. Patterns: overwrite a partition (delete-then-insert for the date being processed) rather than blind append, use natural keys to dedupe, and design for late/duplicate data. A non-idempotent pipeline silently double-counts on every retry — a classic data-quality bug.
dbt: transformation as software
dbt brings software engineering to SQL transformations: models (SELECT statements) with dependencies, tests (uniqueness, not-null, referential), documentation, version control, and incremental models (process only new data). It's the layer that turns "a pile of SQL scripts" into a tested, documented, dependency-aware transformation pipeline — and it's near-ubiquitous in modern data teams.