Roadmap › ETL & Data Warehousing Concepts › Module: Fundamentals
Lessons in this course (12)
2. Full load vs incremental (delta) load
Basic★ Must knowLesson 2 of 12
| Full load | Incremental load | |
|---|---|---|
| What | Reload everything each run | Only new/changed rows since last run |
| Simple? | Very | Needs change detection |
| Volume/time | Grows with data | Small and fast |
| Deletes | Handled naturally (truncate + load) | Must be detected separately |
| Use | Small tables, initial load, dimensions with no change tracking | Large/fast-growing tables |
Change detection techniques:
- Watermark column —
ModifiedDate > @LastLoadTime(store the high-water mark in a control table). - Rowversion/timestamp column.
- Change Data Capture (CDC) — SQL Server reads the transaction log into change tables (inserts/updates/deletes with before/after values).
- Change Tracking — lightweight: which rows changed (not the values).
- Hash comparison — compare a hash (e.g.
HASHBYTES('SHA2_256', ...)) of columns between source and target. - Triggers writing to audit tables (adds OLTP overhead).
sql
-- Watermark pattern
DECLARE @Last DATETIME2 = (SELECT LastLoaded FROM etl.Watermark WHERE TableName = 'Orders');
DECLARE @Now DATETIME2 = SYSUTCDATETIME();
INSERT INTO stg.Orders SELECT * FROM src.Orders WHERE ModifiedAt > @Last AND ModifiedAt <= @Now;
-- transform + load ...
UPDATE etl.Watermark SET LastLoaded = @Now WHERE TableName = 'Orders'; -- only after success