Incremental Loading Strategies: CDC, Watermarks and Snapshots

Running full-table reloads on multi-terabyte transactional tables is a fast track to runaway cloud compute bills and missed SLAs. As modern analytical platforms like Snowflake, Databricks, AWS, and Azure scale to ingest millions of events per hour, data teams must move away from brute-force extract patterns. Building reliable, cost-effective pipelines requires production-grade incremental loading strategies that ingest only mutated data—inserts, updates, and deletes—without compromising downstream consistency.

In modern ETL & ELT architectures, your ingestion pattern dictates your compute footprint, schema evolution resilience, and end-to-end data latency. Whether you are synchronizing operational databases like PostgreSQL and MySQL to Snowflake or streaming IoT events into Databricks Delta Lake, choosing the correct loading strategies requires balancing source system impact against pipeline complexity.

The three primary incremental loading strategies used in production data engineering are log-based Change Data Capture (CDC), watermark-based polling, and snapshot diffing.

---

1. Change Data Capture (CDC): Low Latency, Minimal Source Impact

Change Data Capture (CDC) represents the gold standard for high-throughput, low-latency replication. Rather than querying the source database engine directly via SQL, log-based CDC intercepts changes directly from the database's internal transaction log (e.g., MySQL binary log, PostgreSQL write-ahead log [WAL], Oracle Redo, or SQL Server transaction log).

How It Works Under the Hood

When a transaction commits on the source system, the database engine writes the row state—including BEFORE and AFTER image metadata—to its append-only commit log. A CDC engine such as Debezium (running on Apache Kafka Connect), AWS Database Migration Service (DMS), or Azure Data Factory CDC continuously reads this log file off the disk.

The engine parses the raw binary payload, serializes it into a structured event (often JSON or Apache Avro), and pushes it to an intermediate buffer (like Kafka or AWS Kinesis) or writes it directly into your cloud object store.

[Operational DB] ---> [Write-Ahead Log]
                             │
                      [CDC Engine / Debezium]
                             │
                      [Kafka / Kinesis]
                             │
              [Databricks / Snowflake / Target]

Key Advantages

• Captures Hard Deletes: Because the WAL records the physical removal of records, CDC pipelines natively detect and propagate hard DELETE operations downstream.
• Near-Zero Source CPU Overhead: Because the CDC reader reads the serialized write-ahead log files rather than executing SELECT queries across live tables, it avoids table locks, query engine contention, and buffer pool churn.
• Sub-Second Latency: Ingestion happens continuously as transactions commit, enabling real-time streaming architectures.

Architectural Pitfalls

CDC requires administrative privileges on the source engine to configure logical decoding and replication slots. If the downstream pipeline fails or experiences backpressure, unread replication slots can cause WAL files to accumulate on the source database disk, potentially consuming all available storage and crashing the production database.

Furthermore, schema migrations (e.g., ALTER TABLE DROP COLUMN) must be tightly governed using schema registries to avoid pipeline crashes.

---

2. Watermark-Based Loading: Simplicity with Operational Nuance

Watermark-based loading (often called the high-water mark pattern) is the most common incremental pattern across standard ETL & ELT workflows. It relies on a state column—typically a timestamp like updated_at or a strictly monotonically increasing surrogate key like auto_increment_id—to determine which rows have changed since the last execution.

How It Works

The orchestrator or query runner stores the maximum value retrieved during the previous pipeline run (the watermark). In the current run, the extractor requests only records where the state column exceeds this recorded threshold:

SELECT * 
FROM orders 
WHERE updated_at > :last_watermark_timestamp 
  AND updated_at <= :current_run_cutoff;

Once the extraction finishes, the pipeline commits :current_run_cutoff as the new watermark in a state store (such as a metadata database, an Airflow Variable, or an AWS DynamoDB table).

Edge Cases and Failure Modes

While straightforward to implement, watermark-based loading carries hidden complexities that can lead to silent data loss if ignored:

• Clock Drift and Out-of-Order Commits: In distributed databases or multi-tier web applications, application servers writing to the database may have slight clock skews. Furthermore, a long-running transaction with an earlier timestamp might commit after your extractor has already read past that timestamp. To mitigate this, engineers must implement a lookback window (e.g., querying updated_at > :last_watermark - INTERVAL '10 minutes') and enforce idempotency using downstream deduplication or MERGE statements.
• Invisible Hard Deletes: A SQL WHERE updated_at > ... query can never detect physical row deletions. If hard deletes occur on the source, watermark pipelines will leave stale records in your target warehouse unless paired with soft-delete flags (e.g., is_deleted = TRUE).
• Source Query Load: Unlike CDC, this approach executes analytical SELECT statements directly on the operational database. Without proper B-Tree indexing on the watermark column, these queries trigger full table scans that degrade transactional performance.

---

3. Snapshot Diffing: The Fallback for Constrained Sources

There are scenarios where neither CDC nor watermarks are viable. Many legacy mainframes, ERP exports, third-party REST APIs, and flat CSV dumps do not support replication logs, lack reliable updated_at timestamps, and execute unflagged hard deletes. In these environments, snapshot diffing becomes mandatory.

How It Works

Snapshot diffing extracts the entire current state of the source entity into a temporary landing or staging zone. The transformation layer then computes a delta by comparing the new snapshot against the prior snapshot or the target table.

To make snapshot diffing computationally feasible on large datasets:

1. Row Hashing: Instead of comparing every column, compute a deterministic hash (e.g., MD5(CONCAT_WS('||', col1, col2, col3))) across the payload columns in the staging table.
2. Delta Join: Execute a FULL OUTER JOIN or SQL set difference between the current staging table and the previous state using the primary key.
3. Classification:
   • Keys present in Staging but missing in Target are marked as INSERT.
   • Keys present in both but with different row hashes are marked as UPDATE.
   • Keys present in Target but missing in Staging are marked as DELETE.

While snapshot diffing guarantees 100% fidelity without source database configuration, network egress and storage costs scale linearly with the total table size rather than the churn volume. It is best reserved for smaller reference datasets (under a few million rows) or external batch feeds where the upstream provider cannot supply change events.

---

Comparing Incremental Loading Strategies

Choosing the right approach depends on the source system's capabilities, budget, latency requirements, and internal operational maturity:

---

Architectural Best Practices for Incremental Ingestion

Regardless of which incremental loading strategies you deploy, robust pipelines share three core architectural traits:

1. Strict Idempotency: Any batch of data must be safe to reprocess without duplicating or corrupting downstream tables. Leverage deterministic upsert logic using Delta Lake's MERGE INTO or Snowflake's transactional MERGE.
2. Schema Drift Handling: Downstream transformations will break if an upstream developer adds or drops an unhandled column. Ensure your ingestion layers leverage schema evolution frameworks, such as Databricks Auto Loader (cloudFiles.schemaLocation) or Snowflake's schema detection features.
3. Dead-Letter Queuing (DLQ): Malformed payloads, out-of-range timestamps, or serialization failures must be routed to an isolated DLQ bucket rather than blocking the execution of your ingestion workers.

Engineering teams often find that production environments require a hybrid approach: using CDC for core transactional tables, watermarks for event-driven logs, and periodic snapshot diffing to reconcile edge-case drift. At Izan Technologies, we design and implement enterprise data architectures tailored to these operational realities, ensuring your pipelines remain resilient as data volumes scale.

---

How Izan Technologies Helps

Designing, operating, and scaling robust ETL & ELT pipelines requires specialized expertise across modern cloud ecosystems like Snowflake, Databricks, AWS, and Azure. Izan Technologies builds bespoke data engineering, streaming, and machine learning infrastructure tailored to your exact business constraints.

Unlike traditional agencies that lock you into proprietary platforms or hold your codebase hostage, clients of Izan Technologies own 100% of the IP we deliver—from infrastructure-as-code scripts to downstream transformation logic.

Ready to eliminate ingestion bottlenecks and modernize your data stack?

• Try our Smart AI DataStudio
• talk to an expert