Engineering Atlas › System Design Fundamentals

Moving data with ETL pipelines

11 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Extract

There are three common patterns. A full snapshot copies the whole table each run; easy and correct, but a 2 TB table nightly is expensive and hammers the source. An incremental cursor selects rows where updated_at > last_watermark; cheap, but it misses hard deletes and rows whose clock was skewed or updated inside the same transaction window. CDC reads the database's replication log (MySQL binlog, Postgres WAL) and emits every insert, update, and delete in commit order (Debezium docs), which is the only approach that captures deletes and intermediate states reliably.

Transform

Transformations clean types, deduplicate, conform keys across systems, join reference data, and build modelled tables such as facts and dimensions. A good layout has layers: raw (exact copy of the source), staging (typed and deduplicated), and marts (business-level tables). Each layer is a pure function of the one below, so you can rebuild any layer from raw.

Load

Loads should replace a whole partition atomically: write to a temporary location, validate, then swap. INSERT appends are the classic source of double counting when a job retries. For slowly changing dimensions, a MERGE keyed on the business key keeps history rows with valid_from and valid_to.

Orchestration

An orchestrator (Airflow, Dagster, Prefect) runs tasks as a DAG on a schedule, handles retries, and tracks which logical date each run covers. The important idea is the logical date: the run for 2026-10-14 processes exactly that day's partition, whenever it actually executes. That makes backfills a loop over dates, provided tasks are idempotent and read the interval from the run instead of calling now() (Airflow docs).

Down the stack

Layer 1The contract

For every logical date, the published partition reflects every committed source change in that interval exactly once, no matter how many times the job ran. Readers see either the old partition or the new one, never a mix. Data is published by a stated freshness deadline (for example 06:00 UTC), or an alert fires. If checks fail, the previous good version stays visible.

Layer 2The mechanism

In Postgres, CDC uses a logical replication slot with the pgoutput plugin. The slot remembers a confirmed_flush_lsn; the server keeps all WAL after that point. The connector decodes WAL into BEGIN, INSERT, UPDATE, DELETE, and COMMIT messages with the commit LSN, and acknowledges an LSN only after Kafka has durably accepted the events. In MySQL, the connector reads the row-based binlog (WRITE_ROWS, UPDATE_ROWS, DELETE_ROWS events) and tracks a GTID set. The initial load takes a consistent snapshot, records the log position at the snapshot, then streams from that position, so no change is missed between the two (Debezium docs). Until Postgres 17, a logical slot lived only on the primary, so a failover lost the slot and forced a fresh snapshot; Postgres 17 can sync slots created with failover to a standby (sync_replication_slots) (Postgres docs), and Debezium exposes this as slot.failover. In Airflow, each run carries data_interval_start and data_interval_end; tasks template these into queries, and a backfill simply creates runs for past intervals. Airflow 3.0 (April 2025) changed the details: execution_date is gone in favour of logical_date, manual and asset-triggered runs can have no logical date at all, catchup now defaults to off, and a bare cron string now uses a timetable with no data interval unless create_cron_data_intervals is set. Backfills became scheduler-managed runs started from the UI or API rather than a separate CLI job (Airflow release notes). A pipeline upgraded from Airflow 2 without checking these can silently process empty intervals. The load step writes files to a new path and then updates the catalog's partition location (or commits a table-format snapshot), which is the atomic switch readers observe.

Layer 3The machine

A replication slot pins WAL on the database's disk. A busy primary writing 50 MB/s of WAL fills 180 GB per hour if the connector stops, so a dead connector can fill the primary's disk and take down production. The cap, max_slot_wal_keep_size, defaults to -1 (unlimited) and invalidates the slot when exceeded; Postgres 18 adds idle_replication_slot_timeout to invalidate slots that sit unused (Postgres docs). Either way the connector must re-snapshot, which is cheaper than a full disk. A slot on a quiet database can also pin WAL while connected, because other databases on the same server keep writing; a periodic heartbeat write lets it advance. Each commit on the source is an fsync of WAL, typically 0.5 to 2 ms on SSD, and CDC adds no extra fsyncs because it reads the same log. On the load side, writers buffer rows into Parquet row groups (128 MB by default in parquet-java (parquet-java)) in memory before compressing and uploading them; S3 accepts multipart uploads of up to 10,000 parts of 5 MiB to 5 GiB each (AWS docs), and each PUT costs 20 to 100 ms, so writing thousands of tiny files is dominated by request latency rather than bandwidth.

Why it's built this way

  1. Source databases are tuned for transactions, and long analytical reads hurt them, so pipelines read the replication log instead of querying tables.
  2. The log is ordered and contains deletes, so it is the only complete record of change; but it is retained only briefly, so CDC lands it in cheap durable storage quickly.
  3. Jobs fail and retry, so every write must be idempotent: keyed by partition and deduplicated by log position.
  4. Readers must never see partial results, and object stores lack atomic directory renames, so publication is a single metadata update.
  5. Pipelines break silently, so checks gate publication and freshness is measured as an SLO, end to end rather than per stage, since one stage can succeed while the next drops a field (SRE workbook).

Diagram

flowchart LR
  DB["OLTP database"] -->|"CDC / binlog"| RAW["Raw zone"]
  API["SaaS API"] -->|"incremental pull"| RAW
  RAW --> STG["Staging: typed, deduped"]
  STG --> QC{"Quality checks"}
  QC -->|"pass"| SWAP["Atomic partition swap"]
  QC -->|"fail"| ALERT["Alert, keep old data"]
  SWAP --> MART["Marts: facts and dims"]
  ORCH["Orchestrator"] -.->|"schedule, retry, backfill"| STG

Worked example

An e-commerce company wants a daily orders_fact table. The orders table holds 400 million rows (about 180 GB) and receives 3 million inserts and 1 million updates per day.

A nightly full snapshot would move 180 GB and hold a long read transaction on a replica for about 40 minutes. An incremental cursor on updated_at moves only 4 million rows, about 2 GB, in under a minute, but cancelled orders that are hard-deleted never appear. The team chooses CDC through Debezium into Kafka, landing roughly 4 million change events per day into the raw zone as hourly Parquet files.

The staging job deduplicates to the latest version per order_id within the day:

INSERT OVERWRITE staging.orders PARTITION (ds = '2026-10-14')
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY order_id ORDER BY commit_lsn DESC) AS rn
  FROM raw.orders_cdc WHERE ds = '2026-10-14'
) WHERE rn = 1;

Then orders_fact is merged with this partition. Checks run before publish: row count within 30 percent of the 7-day average, no null customer_id, revenue total within 5 percent of the payments system. A failed check leaves yesterday's table in place and pages the data on-call.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Classic ETL (transform before load)Only clean data lands; less warehouse costRaw data lost; reprocessing needs the source againStrict PII rules, small fixed targets
ELT (load raw, transform in warehouse)Replayable raw history, SQL-based transforms, elastic computeWarehouse bill grows; raw zone needs governanceMost modern analytics stacks
Incremental cursor extractSimple, cheapMisses deletes; clock skew gapsAppend-only or soft-delete sources
CDC extractExact, ordered, includes deletesConnectors, schema evolution, log retention to manageLarge mutable tables, near-real-time needs

Pitfalls in production

Leadership lens

Check yourself

  1. Why does an incremental extract based on updated_at miss data even when the column is always set?

    Show answer

    It cannot see hard deletes, and a long transaction can commit a row with an updated_at earlier than the last watermark, so the row falls behind the cursor.

  2. What does a logical date give you that wall-clock scheduling does not?

    Show answer

    Each run is tied to a specific data interval, so reruns and backfills recompute exactly that interval and produce the same result.

  3. Why keep the raw layer in ELT even after staging tables exist?

    Show answer

    It lets you rebuild every downstream table after a logic bug without going back to the source system, which may no longer have the history.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →