Incremental tables that do not drift

The nightly rebuild took forty minutes in January and four hours by June, so somebody gave the job an incremental mode: load yesterday, leave the rest alone. The dashboard went back to being fast. Eight weeks later the revenue total in the warehouse sits below the revenue total in the source system, and nobody can say when the two parted company.

The examples are Postgres, which has both MERGE and the older INSERT ... ON CONFLICT; they run unchanged in DuckDB. BigQuery, Snowflake and Databricks all have MERGE with the same shape.

Why full rebuilds stop scaling

The cost is not just runtime. A full rebuild reads the entire history every day, so its cost grows with the age of the business while the amount of new information stays flat. On a warehouse billed by bytes scanned, that is a bill that rises with no matching change in value. It also holds a lock or a swap on the output table for the whole run, and the window for that swap shrinks as the run grows.

Incremental builds fix the cost and introduce a new failure mode: the output is now a function of every run that came before it. Miss one run, filter one boundary wrong, and the table is quietly missing rows. Nothing errors. The number just gets smaller than it should be, which looks exactly like a bad week of business.

Ingestion time is the key, event time is the grain

The obvious incremental filter is the event timestamp: load everything that happened since the last run. It breaks on the first late-arriving row. A mobile client that was offline uploads events three days later. A payment processor restates yesterday’s settlements this morning. A backfill loads a month of history at once. All of those rows have event times below your watermark, so an event-time filter never sees them.

Filter on a column your own pipeline controls and never moves backwards — ingestion time, load timestamp, or a monotonic batch id.

Everything below runs on the sample data, where raw_purchases is the purchase events with an ingestion time bolted on — the sample has no such column and every real pipeline does.

create table if not exists etl_watermark (
  table_name      text primary key,
  loaded_through  timestamp not null
);

create table if not exists purchases_daily (
  day        date primary key,
  purchases  bigint,
  revenue    numeric,
  updated_at timestamp
);

-- Most rows land as they happen; some arrive up to four days late.
create table raw_purchases as
  select event_id, customer_id, occurred_at, amount,
         occurred_at + interval '1 hour' * (event_id % 97) as ingested_at
  from events
  where event_type = 'purchase';

insert into etl_watermark
values ('purchases_daily', timestamp '2024-01-01')
on conflict do nothing;

Then use ingestion time to find which rows are new, and event time only to decide which output rows those rows affect. Those are different jobs and mixing them is the root of most incremental bugs.

-- Setup repeated from the block above, so this query runs on its own.
drop table if exists raw_purchases;
create table raw_purchases as
  select event_id, customer_id, occurred_at, amount,
         occurred_at + interval '1 hour' * (event_id % 97) as ingested_at
  from events
  where event_type = 'purchase';

drop table if exists etl_watermark;
create table etl_watermark as
  select 'purchases_daily' as table_name,
         timestamp '2024-01-01' as loaded_through;

with bounds as (
  select loaded_through as lo,
         now() - interval '5 minutes' as hi
  from etl_watermark
  where table_name = 'purchases_daily'
),
new_rows as (
  select p.*
  from raw_purchases p, bounds b
  where p.ingested_at >  b.lo
    and p.ingested_at <= b.hi
),
affected_days as (
  select distinct occurred_at::date as day from new_rows
)
select * from affected_days;

The five-minute lag on the upper bound matters. Rows being written while the query runs may commit after the snapshot and still carry an earlier ingested_at, so a boundary of now() skips them permanently. Give writers a margin, and always use half-open bounds — > lo and <= hi — so no row is loaded twice and none is skipped at the seam.

Restate the affected partitions, do not append

Once you know which days changed, recompute those days completely from raw and upsert them. Appending deltas to an aggregate is where drift is born: any retraction, correction, or duplicate makes the sum wrong forever, because nothing ever recomputes it.

-- Setup repeated again, so this query runs on its own.
drop table if exists raw_purchases;
create table raw_purchases as
  select event_id, customer_id, occurred_at, amount,
         occurred_at + interval '1 hour' * (event_id % 97) as ingested_at
  from events
  where event_type = 'purchase';

drop table if exists purchases_daily;
create table purchases_daily (
  day        date primary key,
  purchases  bigint,
  revenue    numeric,
  updated_at timestamp
);

with bounds as (            -- lo comes from etl_watermark in the real job
  select timestamp '2024-01-01' as lo,
         now() - interval '5 minutes' as hi
),
new_rows as (
  select p.*
  from raw_purchases p, bounds b
  where p.ingested_at >  b.lo
    and p.ingested_at <= b.hi
),
affected_days as (
  select distinct occurred_at::date as day from new_rows
),
recomputed as (
  select p.occurred_at::date as day,
         count(*)            as purchases,
         sum(p.amount)       as revenue
  from raw_purchases p
  join affected_days a on p.occurred_at::date = a.day
  group by 1
)
insert into purchases_daily (day, purchases, revenue, updated_at)
select day, purchases, revenue, now() from recomputed
on conflict (day) do update
  set purchases  = excluded.purchases,
      revenue    = excluded.revenue,
      updated_at = now();

Advance the watermark to hi in the same transaction as the upsert. If the watermark moves and the write fails, those rows are gone from the output and no future run will look for them again.

This pattern only works if the recomputation is cheap, which means the aggregation stays in the warehouse next to the data rather than being pulled into a dataframe — the SQL or pandas question has a clear answer once a job runs unattended every hour.

A periodic full refresh is not optional

Incremental logic cannot see three things. Hard deletes in the source leave no row with a new ingestion time, so the output keeps a record that no longer exists. A change to the transformation itself — a new filter, a fixed currency conversion — applies only to days that happen to get touched afterwards, so the table ends up half old logic and half new. And any run that failed and got skipped leaves a hole that the watermark has already stepped over.

Schedule a full rebuild — weekly is a reasonable default, monthly if the table is enormous — into a shadow table, and swap it in. Treat it as part of the pipeline, not as maintenance that gets postponed. If a full rebuild has become too expensive to run even monthly, that is information: the table is too coarse, or the raw retention window is longer than the business needs.

Diff the two versions and alert on the gap

The shadow rebuild has a second use. Before swapping it in, compare it to the incremental table. This is the only check that actually proves the incremental path is still correct.

-- Setup repeated once more, so this query runs on its own: the raw table,
-- and purchases_daily as one incremental run would have left it.
drop table if exists raw_purchases;
create table raw_purchases as
  select event_id, customer_id, occurred_at, amount,
         occurred_at + interval '1 hour' * (event_id % 97) as ingested_at
  from events
  where event_type = 'purchase';

drop table if exists purchases_daily;
create table purchases_daily as
  with affected_days as (
    select distinct occurred_at::date as day
    from raw_purchases
    where ingested_at >  timestamp '2024-01-01'
      and ingested_at <= now() - interval '5 minutes'
  )
  select p.occurred_at::date as day,
         count(*)            as purchases,
         sum(p.amount)       as revenue,
         now()               as updated_at
  from raw_purchases p
  join affected_days a on p.occurred_at::date = a.day
  group by 1;

drop table if exists purchases_daily_full;
create table purchases_daily_full as
  select occurred_at::date as day,
         count(*)          as purchases,
         sum(amount)       as revenue
  from raw_purchases
  group by 1;

select coalesce(i.day, f.day) as day,
       i.purchases as inc_purchases, f.purchases as full_purchases,
       i.revenue   as inc_revenue,   f.revenue   as full_revenue
from purchases_daily i
full join purchases_daily_full f using (day)
where i.day is null
   or f.day is null
   or i.purchases is distinct from f.purchases
   or abs(coalesce(i.revenue, 0) - coalesce(f.revenue, 0)) > 0.01
order by 1 desc;

On the sample data it returns nothing, which is what a healthy pipeline looks like. Any row returned is a bug, and the pattern of rows names it: differences only in the last few days mean the late-data window is too short; differences scattered through history mean the transformation changed under you; days present in one table and missing in the other mean a skipped run. Log the count of differing days every week, and watch that number the same way you would watch a model’s inputs — a table that has quietly diverged and a model whose features have quietly shifted fail identically, by continuing to return plausible numbers.

If the pipeline has no diff today, the cheap version is an afternoon. Build one full-refresh copy into a scratch schema, schedule the query above weekly, alert on any row it returns, and put the count of differing days on the same dashboard the model inputs already sit on. One page, one person looking at it, both failure modes visible in the same glance.