A marketplace in the mould of Flipkart reloads its 400 GB orders table into the warehouse every night. The job took 40 minutes when the table was small and now takes 3 hours, growing about 10% a month. A teammate proposes loading only rows whose updated_at is newer than the last run. What is the main risk of that change?

What is being tested

Whether you see that an incremental load trades time for correctness assumptions. A full reload is slow but has no hidden state; a watermark load is fast but is only as correct as the column it trusts.

The mechanism

A full reload copies the whole table every run, so cost grows with total size: 400 GB at 10% monthly growth is roughly 1 TB in ten months, and the window shrinks from the wrong side. A watermark load copies only WHERE updated_at > :last_run, so cost grows with daily change volume, which for orders is often 1 to 3% of the table.

The assumption hiding inside that query is that updated_at is monotonic with commit order. It is not. A transaction that starts at 23:58 and commits at 00:03 stamps its rows with a timestamp from inside the transaction, or with the application clock of a server that drifts a few seconds. If the pipeline recorded 00:01 as its high-water mark, the row is below the mark by the time it becomes visible and is skipped forever. Nothing errors. The row count drifts by a handful per day, and finance finds it at month end.

The second gap is deletes. A WHERE updated_at > ... filter can only return rows that still exist. A cancelled and purged order simply stops appearing, so the warehouse keeps it indefinitely.

Why the other options fail

• "Incremental loads always cost more" is backwards for a table this size; the whole motivation is that they cost less.
• "updated_at cannot be indexed" is false, and in practice a missing index is a cost problem on the source, not a correctness one.
• "The warehouse cannot merge by key" is false for any modern warehouse; MERGE or delete-then-insert by key is the normal way to apply the delta.

What to do instead

1. Re-read an overlap window each run (the last 1 to 2 hours behind the mark) and merge by primary key, so late commits are caught and replays are harmless.
2. Handle deletes explicitly: a soft-delete flag, a deletion log, or log-based change capture, which sees deletes natively.
3. Run a weekly full reconciliation (row counts and a checksum per partition) to catch whatever the watermark missed.

When the full reload is still right

Under about 20 to 50 GB, or when the reload fits comfortably in its window, keep the full reload. It has no state to corrupt, and a pipeline that cannot lose rows beats a faster one that occasionally does. Move to incrementals when the window is genuinely at risk, not because incremental sounds more mature.

Common weak answers

• "Use a bigger cluster." It buys a few months and leaves the cost curve unchanged.
• "Trust updated_at; the ORM sets it." The ORM sets it at statement time, not commit time.