QuickDevGuide

Home › ETL - Talend & SSIS

Incremental Loads & Change Data Capture — ETL - Talend & SSIS

Re-reading a whole source every run does not scale. Incremental techniques: Technique How it works Caveat High-watermark WHERE updatedat > :lastwatermark needs a reliable updatedat; misses hard deletes Rowversion / sequence monotonically increasing column DB-specific CDC read the DB transaction log (SQL Server CDC, Debezium) infrastructure to run Snapshot diff compare today's full extract with yesterday's expensive but catches deletes Watermark rules Store the watermark in a control table, updated in the same transaction as the load. Use the max value actually loaded, not "now", to avoid gaps with clock skew. Allow a small overlap window and rely on idempotent upserts to absorb re-reads.

Key Takeaways