Incremental Loads & Change Data Capture

The simplest incremental approach is a watermark/high-water-mark pattern: the ETL job tracks the maximum value of a reliable, monotonically increasing column (typically a LAST_MODIFIED_DATE timestamp or an ever-increasing ID) from the previous successful run, and each new run only pulls source rows with a value greater than that watermark — simple to implement, but entirely dependent on the source system reliably maintaining that timestamp/ID for every insert and update (a source system that doesn't update a "last modified" column on every change will silently miss changes).

Change Data Capture (CDC) is a more robust alternative, particularly for sources where a reliable watermark column doesn't exist or hard deletes need to be captured (a watermark approach can't see a row that was deleted, since it's simply gone). Log-based CDC (reading the source database's transaction/redo log, e.g., via Oracle GoldenGate) captures every insert, update, and delete as a discrete event without needing to query the source table repeatedly, and can achieve much lower latency (near real-time) since it doesn't wait for a batch window. Trigger-based CDC (database triggers writing changes to a separate change-tracking table) is an older, more intrusive alternative that adds write overhead to the source system itself.

A senior-level point often probed: incremental loads must correctly handle late-arriving data (a source record modified after the watermark was already advanced, due to clock skew or delayed system commits) and must be idempotent — safely re-runnable without duplicating or corrupting data if a job fails partway through and needs to be restarted, which is why merge/upsert (MERGE INTO) patterns are strongly preferred over naive INSERT-only incremental logic.