ETL vs ELT and pipeline design

Every pipeline does three things: Extract data from a source, Transform it, and Load it into a destination. The order of those letters, and where the transformation happens, is the difference between ETL and ELT. This lesson explains both, then the design habits that separate a pipeline that works once from one you can trust for years: incremental loads, idempotency, backfills, late data and failure handling.

ETL (extract, transform, load): data is pulled from the source, cleaned and reshaped by a separate processing step (a Python script, Spark job or an ETL tool), and only the final result is loaded into the warehouse. This was the norm when warehouses were expensive and slow, so you wanted to load only what was needed.

ELT (extract, load, transform): data is loaded into the warehouse or lake almost as it is, and the transformation runs inside the warehouse using SQL. Cloud warehouses are powerful and storage is cheap, so keeping the raw data and transforming it later is now the common approach. Tools like dbt exist for exactly this (see *Transformations with dbt*).

They are not rivals. Many real pipelines are a mix: a small Python step that parses files or removes personal data (ETL-style) before loading, then SQL models inside the warehouse (ELT-style). Interviewers want to hear that the choice depends on where the compute is cheapest and what must happen before data is stored.

Start with batch. It is simpler, cheaper, easier to test and easier to fix by re-running. Move to streaming only when the business really needs results faster than batch can give. Streaming is covered in *Streaming with Apache Kafka* and *Stream processing*.

A full load copies the whole source table every run. It is simple and always correct but gets slow and expensive as data grows. An incremental load copies only what is new or changed since last time. To do that you need a way to know what changed. There are three common ways.

-- Run for each batch; :last_watermark is saved from the previous successful run
SELECT order_id, customer_id, status, total, updated_at
FROM orders
WHERE updated_at > :last_watermark
ORDER BY updated_at;

After the load succeeds, store the max(updated_at) you saw as the new watermark. Store it only after success, otherwise a failed run would skip rows.

Pipelines fail. Networks drop, servers restart, someone re-runs a job by mistake. A pipeline is idempotent if running it twice with the same input leaves the same result as running it once. Without it, a re-run after a crash duplicates rows and quietly doubles your revenue.

The simplest idempotent patterns are below.

-- Pattern 1: replace the partition
BEGIN;
DELETE FROM order_lines WHERE order_date = '2026-10-01';
INSERT INTO order_lines SELECT * FROM staging_order_lines;
COMMIT;

-- Pattern 2: upsert by key (PostgreSQL and SQLite 3.24+)
INSERT INTO orders (order_id, status, total)
SELECT order_id, status, total FROM staging_orders WHERE true
ON CONFLICT (order_id) DO UPDATE
SET status = excluded.status, total = excluded.total;

Run either one twice and the table is the same. A plain INSERT run twice would duplicate rows. The WHERE true is needed in SQLite to avoid a parsing ambiguity and is harmless elsewhere. Warehouses such as BigQuery and Snowflake use MERGE for the upsert.

A backfill re-runs a pipeline for past dates, for example after fixing a bug or adding a new column. If each run takes a date as a parameter (--date 2026-10-01) and is idempotent, a backfill is just a loop over dates. Hard-coding "yesterday" inside the script makes backfills painful. Always pass the date in.

A real pipeline has many steps that depend on each other: extract, then validate, then transform, then publish. An orchestrator runs the steps in the right order on a schedule, retries failures, shows what failed and lets you re-run one date. Apache Airflow is the most widely used; Dagster, Prefect and cloud services such as Cloud Composer, Azure Data Factory and AWS Step Functions do similar jobs. Cron works for one simple script. You build an Airflow pipeline in *Orchestration with Apache Airflow*.

You are a senior data engineer reviewing a pipeline design. Check it against these questions: is it idempotent, how does it handle late data and schema changes, what happens if it fails halfway, how are bad rows handled, and how would I backfill 30 days? Give concrete fixes.

Source: [system and table]
Destination: [warehouse or lake]
Volume and schedule: [rows per day, how often it runs]
Current design: [describe each step, and how you load: full, watermark, CDC]

What to expect: Look for specific fixes such as a replace-partition load, an overlap window on the watermark and a rejects table, not generic advice.