Transformation Strategy

Concepts covered: ELT vs ETL, Full vs Incremental Load, Idempotency, Deduplication Strategies, Backfill Patterns

ELT because raw data is cheap to keep

ETL transforms before loading, so the warehouse holds only the processed shape. ELT loads raw first and transforms in place with SQL, which in practice means dbt models over bronze with Spark or the warehouse as the engine. The difference shows on the day you reprocess: in ETL a historical month comes from the source again, and in ELT it sits in bronze and the reprocess is a rerun with a different date range.

Storage first: S3 standard is $0.023 per GB-month, and a year of raw events at 40 GB of JSON a day is about 15 TB, or $335 a month. Then the rerun. In bronze, 6 weeks of order CDC is about 176 million change events, and a 16-executor Spark cluster merging around 10 million rows a minute rebuilds silver orders in under 20 minutes, with the gold models adding a few more. The re-extract it replaces starts with 50,000 Stripe requests per 5 million charges, around 8 minutes at the rate limit, and then stalls: 6 weeks of order changes the WAL recycled once the slot confirmed them, and courier exports the partner has deleted. The comparison to say is 25 minutes of compute against a rebuild that cannot finish. Transforming before the load still makes sense in 1 place: card numbers and any other field that may never land in the lake get tokenised or dropped before the write.

ETL: transform before load
  • Warehouse holds only the processed shape
  • A logic bug means re-extracting from every source
  • Rate limits and deleted exports cap what can be rebuilt
  • Right for fields that cannot be stored raw
ELT: load raw, transform in place
  • Bronze keeps the year for around $335 a month
  • A logic bug means a dbt rerun over bronze
  • 6 weeks of orders rebuild in about 20 minutes
  • Right for everything the lake is allowed to hold

Incremental, and the merge that makes it safe

The orders fact holds 438 million rows after a year, and the operations model runs every 5 minutes. A full refresh therefore rereads the whole fact 288 times a day to apply 1.2 million new orders. An incremental model reads only the change events that landed in bronze since the last run, roughly 4.2 million a day across all runs, and merges them into the target.

MERGE INTO silver.orders AS t
USING (
    SELECT order_id, user_id, city, status, amount_cents, updated_at, op
    FROM (
        SELECT *, ROW_NUMBER() OVER (
            PARTITION BY order_id ORDER BY kafka_offset DESC
        ) AS rn
        FROM bronze.orders_cdc
        WHERE ingested_at > {{ last_run_ingested_at }}
    ) AS latest
    WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
    status = s.status,
    amount_cents = s.amount_cents,
    updated_at = s.updated_at
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT
    (order_id, user_id, city, status, amount_cents, updated_at)
VALUES
    (s.order_id, s.user_id, s.city, s.status, s.amount_cents, s.updated_at);

MERGE makes the run idempotent. When a replayed change event matches its key, the updated_at guard rejects it, leaving the row and every downstream count unchanged, where a plain INSERT would add a second row and double them. The ROW_NUMBER step matters as much: 1 order can change 3 times inside 5 minutes, and Spark with Iceberg and Snowflake by default reject a MERGE in which several source rows match 1 target row. Filtering on bronze ingestion time rather than updated_at keeps a late event from being skipped forever, because it carries an old updated_at and a new ingested_at. Once you have covered all 3 in that order, the interviewer stops probing.

Full refresh where the table is small

Incremental everywhere is its own trap. Gold daily_revenue holds 365 rows after a year and depends on refunds that arrive up to 30 days after the charge. Rebuilding its last 45 days every morning reads around 54 million order rows plus the charges joined to them and finishes in a few minutes. It needs neither a watermark nor a merge key, and late data needs no special logic, because every run recomputes the window. The rule: incremental for the fact with hundreds of millions of rows, full refresh over a trailing window when that window's input rescans in minutes. dbt expresses either as 1 config line, and the interviewer wants to hear that you picked per model rather than per pipeline.

About this lesson

This section is part of the Design a Pipeline: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.