In this lesson, you will learn how to efficiently capture delta updates by combining chronological watermarks with system-level change tracking mechanisms.
When building incremental data pipelines, the challenge is identifying exactly what has changed since the last extraction without scanning the entire source dataset. Relying solely on a last_modified timestamp column is often insufficient due to "in-flight" transactions or clock skew. To achieve robust delta detection, we pair application-level watermarks with native database change tracking.
The Watermark Strategy
A watermark is a persistent marker (stored in your metadata repository) representing the high-water mark of the last successfully processed record. If you use a last_updated_at column, your pipeline executes:
SELECT * FROM orders
WHERE last_updated_at > :last_watermark
ORDER BY last_updated_at ASC;
However, to prevent data loss from records committed after the query starts but before the watermark updates, we implement a "look-back" window or use a status flag to ensure idempotency when processing these overlapping records.
System-Level Change Tracking
For high-volume systems, relying on application columns is risky (e.g., hard deletes are missed). Instead, we leverage system-level tracking, such as Change Data Capture (CDC) or internal version columns (like xmin in Postgres). These mechanisms read the transaction log to identify row-level changes.
When you enable system-level tracking, the database maintains a version sequence. Your pipeline no longer queries by wall-clock time, but by transaction log sequence number (LSN) or system change number (SCN):
-- Querying changes based on system versioning
SELECT * FROM orders
WHERE sys_version > :last_known_version;
By storing the last_known_version as your watermark, you guarantee that every change—including inserts, updates, and hard deletes—is captured exactly once. This shifts the burden of proof from the application logic to the database engine's internal consistency model, ensuring your data pipeline remains synchronized even under heavy write contention.
Key Takeaway: Combine system-level change tracking (LSN/SCN) with a persistent watermark to guarantee that incremental data loads are both comprehensive and resilient to transaction lag.