Y

ETL and data pipelines · Advanced

Incremental loads, late data and backfills

3 min read

Why it matters

Reloading everything every night works until the table has a billion rows. Senior data engineering roles expect incremental loads, and interviews ask about idempotency, late-arriving data and backfills. These are also where most production data incidents happen.

The concept

  • Watermark: the latest value (timestamp or id) already loaded. Each run loads rows after it.

  • Lookback window: reprocess a few days before the watermark to catch late-arriving or corrected rows.

  • Merge (upsert): insert new keys, update changed ones, by a unique key. Requires a reliable key.

  • Partition overwrite: for append-only data partitioned by date, replace whole affected partitions. Simple and idempotent.

  • Backfill: rerun history for a date range, usually after a logic change. Your pipeline must accept a date range as input.

Worked example: merge with a lookback in DuckDB

Order lines from the UCI Online Retail II data arrive daily. Simulate two loads: everything up to 30 November 2011, then a later run that also sees corrections.

incremental.sql
create or replace table source as
select Invoice as invoice, StockCode as stock_code, Quantity as quantity, Price as price,
       InvoiceDate as invoiced_at,
       row_number() over (partition by Invoice, StockCode order by InvoiceDate) as line_no
from read_csv_auto('online_retail_2010_2011.csv');

create or replace table target (
  invoice varchar, stock_code varchar, line_no bigint, quantity bigint, price double,
  invoiced_at timestamp, loaded_at timestamp,
  primary key (invoice, stock_code, line_no)
);

-- Run 1: initial load up to the end of November
insert into target
select invoice, stock_code, line_no, quantity, price, invoiced_at, now()
from source where invoiced_at < '2011-12-01';

-- Run 2: watermark minus a 3-day lookback, then upsert
insert or replace into target
select invoice, stock_code, line_no, quantity, price, invoiced_at, now()
from source
where invoiced_at >= (select max(invoiced_at) - interval 3 day from target);

select count(*) as rows_loaded, max(invoiced_at) as watermark from target;

The lookback re-reads the last three days of November, which already exist. Because the table has a primary key and the statement is insert or replace, those rows are updated, not duplicated. The result is 541,910 rows, exactly the source count, no matter how many times you run step 2.

The key needed care: invoice plus stock code is not unique in this data (the same product can appear twice on one invoice), so line_no from row_number() completes it. Finding the real key is usually the hardest part of an incremental design.

Common mistakes

  • Watermarks on the wrong column. Use the time the row arrived or changed, not a business date that can be backdated.

  • No lookback. Late rows just behind the watermark are never loaded.

  • Keys that are not unique. Merges then update the wrong rows. Test uniqueness before relying on it.

  • Backfills that fight the scheduler. Pause the daily job, or make both write disjoint partitions.

Interview angle

"How would you load a 2 TB table daily?" Talk about incremental extraction with a watermark and lookback, merge keys or partition overwrite, idempotent reruns, and how you backfill after a bug fix. Mentioning the late data problem unprompted is a strong signal.

Practice task

  1. Change run 2 to use partition overwrite by day instead of merge. Which is simpler here, and why?

  2. Simulate a correction: change the quantity of one November line in source and show that run 2 picks it up only if it falls inside the lookback.

  3. Write a backfill command that reprocesses a given date range.

Further reading