Incremental Loading: Intermediate

A full refresh truncates the target and reloads every source row. Its correctness is complete by construction: a row removed at the source is absent from the reload, a row updated 3 years after it was created arrives with its new values, and a row whose update timestamp was never set arrives anyway because nothing filtered on it. The job keeps no state between runs, so a backfill is the same job run again. The price is that the work scales with the size of the table rather than the size of the change. Take an orders table of 40 million rows at around 400 bytes each. Reloading it means pulling the whole thing across the network every night and writing it back out, even when 99 percent of it hasn't moved since yesterday.

Row counts and change rates in this table are the running example for the whole lesson; every later figure derives from the orders row.

Read the table from right to left. dim_product has a reliable change column and a small change ratio, the textbook incremental profile, and the right answer is still a full refresh, because 40 seconds of reload costs less than any amount of watermark logic. inventory_levels changes 80 percent of its rows daily, so an incremental load would move nearly as many bytes as a refresh while taking on all 4 properties above. orders goes incremental for 1 reason: the refresh no longer fits the window. The change ratio tells you the size of the saving, the window tells you whether you need it, and the change column tells you whether it is achievable at all.

Suppose a cleanup job at 03:10 hard-deletes roughly 2,000 orders a day from the source, most of them test orders or duplicate submissions. Over a quarter that is 180,000 phantom rows in orders_fact, and every one of them carries an amount that gets summed into a daily revenue figure. Here is a 5 row slice on the morning after 2 of them were removed.

Orders 5003 and 5005 were deleted at 03:10. The 04:00 run filtered on updated_at above yesterday's watermark, found 5002, whose status changed from pending to paid, and loaded it. Rows 5003 and 5005 matched nothing and stayed in the target, which leaves the daily revenue figure 135.01 too high.

If you can change the source, the cheapest fix is to stop deleting. The cleanup job sets is_deleted = true, and the same statement bumps updated_at. The row now passes the incremental filter like any other update, arrives downstream carrying its flag, and either the load applies the delete to the target or every consumer adds WHERE NOT is_deleted. This breaks when a code path sets the flag and forgets the timestamp: the row is logically gone and invisible to the load, which combines the worst of both options. Make the timestamp bump a database trigger rather than an application convention, so no code path can skip it.

The timestamp in updated_at is written when the statement runs, inside a transaction. The row becomes visible to your extract when the transaction commits, which can be seconds or minutes later. Between those 2 moments another run can read the table, see a higher timestamp on some other row, and record it as the watermark. Here is the sequence with a 45 second transaction and a run every 15 minutes.

You can't make the column honest, so make the filter forgiving. Overlap absorbs late commits and clock skew, the lies that leave a row a few seconds behind the watermark, and nothing else. A source correction that never touches updated_at needs a targeted re-pull or a partition backfill, a forgotten code path needs the trigger-set column from the table above, and a local-time column needs storing in UTC, because a 15 minute window can't reach back across a repeated hour. For the 2 it does absorb, subtract a lookback window from the watermark that is longer than your longest transaction, 15 minutes here against a 45 second worst case, and accept that each run reloads a slice it has already seen. Then collapse duplicates on the key, keeping the newest version. The overlap turns a permanent miss into a 1 run delay, and the dedupe turns the double-count from section 0 into a single current row. Note the comparison is now >=, because many rows share a timestamp to the second and an exclusive compare drops the ones equal to the boundary.

The stage then replaces matching rows in orders_fact: delete where order_id is in the stage, insert the stage. Reloading a row you already hold produces the row you already hold, which is why a 15 minute overlap costs some scan and no correctness. That delete-and-insert pair is the same write section 3 uses for backfills.

Size the lookback from the source, never from habit. Ask for the longest transaction the application holds open, add the clock skew between its servers, and double the sum. For the orders service that is 45 seconds plus 3 seconds, doubled to about 2 minutes, so 15 minutes is generous and reloads roughly 3,000 rows a run, roughly 1 percent of the daily change. A source with 20 minute batch jobs that update rows inside a single transaction needs a 40 minute window or a different column entirely.

Partition orders_fact by order_date and make every write a replacement of 1 day: delete the day, insert the day. Running the pair twice yields the rows that running it once yields. Running it for 2026-09-14 touches nothing outside September 14. The same 2 statements serve the nightly load and the backfill of any date in history, which removes the need for a separate backfill code path that drifts from the one in production.

The literal 2026-09-14 must arrive from the orchestrator, never from current_date inside the query. A task that computes its own date processes today every time it runs, so a backfill of the last quarter re-runs October 23 for all 90 dates and declares victory. With the date injected, the scheduler supplies 1 value per task instance, and 90 backfill instances run in parallel against 90 disjoint partitions, which is why the diagram below carries a parallelism of 90.

By the time you need March again, the source may have archived March, and it has certainly hard-deleted 2,000 rows a day since. The raw landing layer holds every row ever extracted, partitioned by the day of extraction, together with the daily key snapshots from section 1 kept as tombstones. A backfill replays that history: for a target partition, take every raw row with that order_date, keep the latest version per key, drop any key a tombstone covers, replace the partition. The source is read only on the first extraction. Keeping each raw day of rows costs roughly 124 MB, which over 2 years is under 100 GB before the key snapshots.

The nightly load advances the watermark; the backfill must never touch it. Keep them as 2 separate state entries, or run the backfill as a task that has no write access to the watermark at all. A backfill of 2026-09-14 that stores the max(updated_at) of the rows it processed into the shared entry drags the next nightly run back by 5 weeks and reloads 11 million rows through the overlap and dedupe path, at roughly 1 hour of extra work. The opposite accident is a backfill of recent days storing a watermark higher than the nightly run has reached. The nightly run then skips rows without any error, and section 2 covered how long a silent skip lasts.

The entire mechanism is a watermark table with a nightly row and a backfill row; the discipline is that each task may write only its own row.

An order placed on 2026-09-14 and refunded on 2026-10-20 carries order_date September 14 and updated_at October 20. The nightly load catches it by updated_at and stages it. The replace step then rewrites the September 14 partition with the refund applied, 36 days after the order. A nightly run that only replaces the last 7 partitions is therefore wrong: it must replace every partition that appears in the stage, however old. In the example table a status change can arrive up to 90 days after the order, so a typical night touches about 40 distinct partitions, and any report that cached September is 1 day stale the next morning.

The old row closes at the instant the new one opens, so the 2 intervals meet without gap or overlap and any order timestamp falls into exactly 1 of them. valid_to on the current row is a far-future sentinel rather than NULL, so range comparisons need no COALESCE at query time. The surrogate key customer_sk is what the fact table stores: a March order carries 88 and an October order carries 2311 forever, so reports join on 1 integer and never touch the dates.

Compare each incoming row from the overlap and dedupe stage of section 2 against the customer's current version. A row counts as a change in 2 cases: the customer is new and has no current version, or a tracked attribute differs. Write the comparison as a hash over the tracked columns so that tracking a new column later is a 1 line edit, and COALESCE any nullable tracked column to a marker before concatenating, because a NULL inside the hash input makes the whole comparison NULL and the change goes undetected. New versions are appended to customer_versions with a surrogate key continuing from the current maximum.

customer_versions is append-only, 1 row per detected change, and the dimension is derived from it instead of edited in place. Each version's valid_to is the next version's valid_from for the same customer, which a window function produces without a self join. Rebuilding the dimension is a full refresh of a small table, delete all rows then insert, and section 0 already made the case that a full refresh is right there: 2 million customers at 3 versions each is 6 million rows and under a minute.

When an order loads, look up the version whose interval contains the order timestamp and store that key. The range condition runs once per order at load time, and every report after that joins on customer_sk alone.

A change that arrives late, with a valid_from earlier than the current version, is the SCD failure most candidates miss. A design that edits the dimension in place closes the current row at the late timestamp and opens a new one, producing 2 current rows or an interval running backwards. The version log design orders by valid_from when it derives intervals, so a late version slots between its neighbours and LEAD recomputes both of them on the next rebuild. Deletes fit the same log with 1 extra flag column: a tombstone version closes the last real version and carries no attributes forward. And because the dedupe stage guarantees 1 row per customer per load, 2 versions never share a valid_from, so the order within a customer is total.