Incremental Processing & Backfills

What you will learn

1. Full load and incremental load: two ways to copy data.
2. Two ways to find new rows: by created date, or by updated_at.
3. The half-open window, so no row is read twice or missed.
4. Late rows, and a small lookback that catches them.
5. A backfill: run the same job for a day in the past.
6. A safe backfill plan, and one trap with dimension values.

A pipeline copies data from a source table to a warehouse table. The source is where the app writes. The warehouse table is the copy that analysts read.

One execution of the job is a run. This chapter uses two tables: orders in the source, and dw_orders in the warehouse. In the story, today is 2026-01-22.

The source table has a new column, updated_at. It holds the date the row last changed. The app sets it on every insert and every update.

SELECT COUNT(*)        AS rows_in_source,
       MAX(updated_at) AS newest_change
FROM   orders;

Later we change one row on purpose. On 2026-01-25, order 105 is paid. Its amount changes from NULL to 22.00, and its updated_at becomes 2026-01-25.

Remember order 105. It breaks the first idea we try.

A full load copies every source row on every run. First it clears the warehouse table. Then it inserts all rows again.

BEGIN;
DELETE FROM dw_orders;
INSERT INTO dw_orders
SELECT order_id, order_date, amount, updated_at
FROM   orders;
COMMIT;

A full load is easy to trust. If last night went wrong, tonight fixes it. The job needs no memory of earlier runs.

The cost is the problem. Tonight the job reads six rows, but only order 106 changed since last night. With six million rows, it reads six million to find that one change.

The next sections show the incremental way. Here is how the two compare:

An incremental load reads only rows that are new since the last run. So the job must remember how far it got. That saved point is the watermark.

The simplest watermark is a created date. An order gets its order_date once, when it is placed.

On 2026-01-22 the warehouse holds orders 101 to 104. The saved watermark is 2026-01-20. The job reads from the watermark up to the run date.

SELECT order_id, order_date, amount, updated_at
FROM   orders
WHERE  order_date >= '2026-01-20'   -- the saved watermark
  AND  order_date <  '2026-01-22';  -- the run date, not read yet

Now the problem. On 2026-01-25, order 105 is paid, so its row changes. Its order_date stays 2026-01-20.

The next run is on 2026-01-26. The saved watermark is 2026-01-22. The job asks for orders created from 2026-01-22 on.

SELECT order_id, order_date, amount, updated_at
FROM   orders
WHERE  order_date >= '2026-01-22'
  AND  order_date <  '2026-01-26';

The source total is 242.50. The warehouse total is 220.50, because SUM skips NULL. The warehouse is 22.00 short, and no error is shown.

A created date finds new rows. It cannot find rows that changed after they were created.

Use updated_at as the watermark instead. It changes whenever the row changes. So a changed row looks new again.

Run again on 2026-01-26. The saved watermark is still 2026-01-22.

SELECT order_id, order_date, amount, updated_at
FROM   orders
WHERE  updated_at >= '2026-01-22'
  AND  updated_at <  '2026-01-26';

Do not insert this row. The warehouse already has an order 105, so an insert would add a second copy.

Write by key with MERGE instead. The rows just read sit in a table called staged.

MERGE INTO dw_orders t
USING staged s
ON    t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
      order_date = s.order_date,
      amount     = s.amount,
      updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT
      (order_id, order_date, amount, updated_at)
VALUES (s.order_id, s.order_date, s.amount, s.updated_at);

Each run reads one window of time. A half-open window includes its start and excludes its end. We write it as [start, end).

A square bracket means "included". A round bracket means "not included".

The end of one window is the start of the next. So every row falls in exactly one window.

The common mistake is BETWEEN, which includes both ends. Run X reads 2026-01-20 to 2026-01-21, and run Y reads 2026-01-21 to 2026-01-22:

-- run X
SELECT order_id, updated_at FROM orders
WHERE  updated_at BETWEEN '2026-01-20' AND '2026-01-21';

-- run Y
SELECT order_id, updated_at FROM orders
WHERE  updated_at BETWEEN '2026-01-21' AND '2026-01-22';

Now the same two runs, half-open. The start uses >=. The end uses <.

-- run X: [2026-01-20, 2026-01-21)
SELECT order_id, updated_at FROM orders
WHERE  updated_at >= '2026-01-20' AND updated_at < '2026-01-21';

-- run Y: [2026-01-21, 2026-01-22)
SELECT order_id, updated_at FROM orders
WHERE  updated_at >= '2026-01-21' AND updated_at < '2026-01-22';

Real updated_at values hold a time, like 2026-01-21 09:30:00.500. A closed end such as <= 23:59:59 skips a row at 23:59:59.500.

The half-open end has no such gap. You met this idea in the time chapter of Python.

Also fix the end once, at the start of the run. Call it run_start.

In this chapter every run begins at midnight, so run_start is a date, such as 2026-01-26. Rows that arrive during the run belong to the next window. One run follows four steps:

1. Read the saved watermark.
2. Set run_start once, when the run begins.
3. Read the window [watermark, run_start) and write the rows by key.
4. Only after the write works, save run_start as the new watermark.

If the write fails, the watermark does not move. The retry reads the same window. That is safe, because the write is by key.

A late row reaches the source after the window that should have caught it. Its updated_at is old, so the next window skips it.

Change the story. A slow system saves the payment of order 105 on 2026-01-26 at 10:00.

It writes updated_at as 2026-01-25. The 2026-01-26 run began at midnight, so it did not see the row.

From now on, the job runs every day. The 2026-01-25 run saved the watermark 2026-01-25.

A lookback fixes this. The window starts N days before the watermark, and here N is 2.

Look at the four daily runs:

The plain window never finds order 105. The lookback finds it on 2026-01-27. Here is that run:

SELECT order_id, order_date, amount, updated_at
FROM   orders
WHERE  updated_at >= '2026-01-24'   -- watermark 2026-01-26 minus 2 days
  AND  updated_at <  '2026-01-27';  -- run_start

The lookback re-reads rows it already loaded. This costs a little each night. It is safe only because the write is idempotent.

Choose N from real data. Measure how late rows usually arrive, then add a margin. A row later than N days is missed, so check counts against the source often, for example once a week.

A partition is one slice of a table. Here, one partition is all rows with the same order_date. You can replace one partition without touching the others.

Count the rows in each partition of the warehouse table:

SELECT order_date, COUNT(*) AS rows_in_partition
FROM   dw_orders
GROUP  BY order_date
ORDER  BY order_date;

This is why a backfill can go one day at a time. Each day is its own slice, so re-running one day leaves the other days alone.

A backfill runs the job again for days in the past. You do it when an old day is wrong or missing.

Your manager says: "Run it for last Tuesday." In our story, that is 2026-01-20.

Go back to the created-date job from earlier. With that job, the warehouse copy of order 105 still shows a NULL amount. The source says 22.00.

A job that asks the clock for "today" cannot do this. It can only make today.

So give the job a logical date. A logical date is the day the run is about, not the day it runs. You met this idea in the idempotency chapter.

from datetime import date, timedelta

def window(logical_date):
    start = logical_date
    end = logical_date + timedelta(days=1)
    return start, end

print(window(date(2026, 1, 20)))

A backfill writes a whole partition at once: one day of order_date.

The job deletes that partition, then inserts it again. Both steps are one transaction.

BEGIN;
DELETE FROM dw_orders
WHERE  order_date >= '2026-01-20' AND order_date < '2026-01-21';

INSERT INTO dw_orders
SELECT order_id, order_date, amount, updated_at
FROM   orders
WHERE  order_date >= '2026-01-20' AND order_date < '2026-01-21';
COMMIT;

Say you must fix three days: 2026-01-19, 2026-01-20 and 2026-01-21. Do not run it as one big job. A safe backfill has six steps:

1. Choose the range of logical dates, oldest first.
2. Run one day at a time, with the same job as every night.
3. Write each day by overwrite, so a re-run does not add rows.
4. After each day, compare the row count and the amount total with the source.
5. Stop at the first day that does not match.
6. Write down each finished day, so a stopped backfill can resume.

Look at the check for one day. It compares the count and the amount total:

SELECT
  (SELECT COUNT(*) FROM orders
   WHERE order_date >= '2026-01-20' AND order_date < '2026-01-21') AS source_rows,
  (SELECT COUNT(*) FROM dw_orders
   WHERE order_date >= '2026-01-20' AND order_date < '2026-01-21') AS warehouse_rows,
  (SELECT SUM(amount) FROM orders
   WHERE order_date >= '2026-01-20' AND order_date < '2026-01-21') AS source_amount,
  (SELECT SUM(amount) FROM dw_orders
   WHERE order_date >= '2026-01-20' AND order_date < '2026-01-21') AS warehouse_amount;

Here is the plan for all three days, with the numbers each check should show:

A backfill must give the numbers the job would have given on that day. A dimension is a table of descriptions, such as the country of a customer. It can change over time.

Asha (customer 1) lived in IN until 2026-01-08. Then she moved to DE. We now backfill revenue by country for 2026-01-03 to 2026-01-10, using today’s customers table.

SELECT c.country, SUM(o.amount) AS revenue
FROM   orders o
JOIN   customers c ON c.customer_id = o.customer_id
WHERE  o.order_date >= '2026-01-03'
  AND  o.order_date <  '2026-01-11'
GROUP  BY c.country
ORDER  BY c.country;

The fix is the SCD type 2 table from the modeling chapter, dim_customer. Each row says which dates a value was true. The join picks the row that was true on the order date.

valid_to is the first day the row stopped being true. It is not included. 9999-12-31 means it is still true.

SELECT d.country, SUM(o.amount) AS revenue
FROM   orders o
JOIN   dim_customer d
  ON   d.customer_id = o.customer_id
 AND   o.order_date >= d.valid_from
 AND   o.order_date <  d.valid_to
WHERE  o.order_date >= '2026-01-03'
  AND  o.order_date <  '2026-01-11'
GROUP  BY d.country
ORDER  BY d.country;

The same trap hits any value that changes: currency rates, tax rules, product categories. The rule is the same. A backfill for a past day must use the values from that day.

Five scenarios. Build each one on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed.