Incremental load: stop reloading two years to add one day

A nightly job that truncates a table and reloads two years of history to add one day is simple and correct. It is also slow, and it gets slower every month. This page covers full load versus incremental, the watermark pattern, the upsert, and the late data that arrives after your watermark has moved on. The print-ready A4 PDF is at the bottom.

Full load

• TRUNCATE plus INSERT: simple, correct, slow.
• Every night it reads everything to add one day.

Incremental

• WHERE updated_at > :mark pulls only what changed.
• The watermark is the max value you loaded last time. Store it.
• An upsert writes updates too, not only inserts.

The watermark pattern

-- 1. where did we stop?
SELECT MAX(updated_at) FROM dw.orders;   -- :mark

-- 2. only what changed since, with a 2-day overlap
INSERT INTO dw.orders
SELECT * FROM src.orders
 WHERE updated_at > :mark - INTERVAL '2 days'
ON CONFLICT (order_id) DO UPDATE
   SET status = EXCLUDED.status,
       updated_at = EXCLUDED.updated_at;

Read the max you loaded last time, pull everything above it, write it with an upsert, store the new max.

The overlap costs two days of rows and buys you the late ones. The upsert makes the overlap harmless: a row loaded twice is updated, not duplicated. The ON CONFLICT needs a unique key on order_id in the target table.

Remember

• Late data lands after the watermark. This is the one to plan for.
• Overlap: reload the last two days and dedupe.
• A full load is still right for small tables.

The trap: what an incremental load misses

Three cases a full load handles for free and an incremental one has to plan for.

CDC means change data capture: reading the change log of the source database instead of asking a column.

Rule of thumb: if the table fits a full load in minutes, do the full load. Incremental is for when it does not.