Full Load vs Incremental Load
People think an incremental load gives the same result as a full load, only faster, and it does not. A full load copies every row, so the copy always matches. An incremental load copies only rows past a marker, such as a time stamp. A row deleted at the source never passes that marker, so it stays in the copy. Many teams run both: small loads each night and a full one now and then.
Full Load
Copy the whole table again each time. Simple, but slow when the table is big.
A full load copies every row from a source each time it runs. It first empties the old copy, or replaces it. Then it copies the whole table again. It is the simplest way to load data, and nothing can slip past. But it gets slow and costly as the table grows. Small tables are often loaded this way every night.
Incremental Load
Copy only what is new since last time. Do not copy it all again.
An incremental load copies only the rows that are new or changed since the last run. It keeps a mark of how far it got, often the latest updated time or the highest ID. The next run asks only for rows past that mark. A full load copies the whole table each time instead. Incremental loads are far faster on big tables.
In one line
- Full Load
- Copy the whole table again each time. Simple, but slow when the table is big.
- Incremental Load
- Copy only what is new since last time. Do not copy it all again.