Comparison
Full loadvsIncremental load
Full load
the job reads all four hundred million rows every night because nobody could work out which ones had changed.
Copying the entire source table on every run and replacing what was there. It is the simplest thing that is definitely correct: no cursor to get wrong, no missed updates, no drift between source and copy. It stops working on size and on politeness — a full read hammers the source database and the run time grows with the table, so the switch to incremental usually happens the week the job stops finishing before the working day.
Full entry →Incremental load
each run asks the source only for rows changed since the last run, and one row updated in the same second as the cutoff is now missing forever.
Reading only what has changed since the previous run, tracked by a cursor of some kind. It is the standard way to make a big table affordable, and it introduces a whole class of quiet bug: rows changed exactly on the boundary, rows updated without their timestamp moving, and hard deletes, which by definition leave nothing to find. The failures are silent because a missing row does not error, it simply lowers a total.
Full entry →