Incremental Data Loading Strategies for ETL Pipelines

Full table reloads are the simplest ETL strategy. Pull every row from the source, write it to the destination, done. This works when tables are small, sources can handle the query load, and freshness requirements are relaxed. As data volume grows, full loads become slow, expensive, and impractical. Incremental loading is the solution — and there are several strategies for implementing it correctly, each suited to different source characteristics.

Why Incremental Loading Matters at Scale

Consider a source table with 500 million rows. A full reload queries all 500 million rows, transfers them across a network, and writes them to the destination on every pipeline run. At scale, this takes hours, strains the source database, and costs significant compute and storage. An incremental load that processes only the 100,000 rows changed since the last run is orders of magnitude faster and cheaper.

Incremental loading also enables higher data freshness. A full reload that takes four hours can only run a few times per day. An incremental load that takes two minutes can run every five minutes.

The High-Water Mark Pattern

The most common and simplest incremental strategy. It works when the source table has a column that monotonically increases for new or updated rows — typically a created_at or updated_at timestamp, or an auto-incrementing ID.

How it works:

1. Store the maximum value of the incremental key from the last successful run (the high-water mark).
2. On each run, query the source for all rows where the incremental key is greater than the stored high-water mark.
3. Upsert those rows into the destination.
4. After a successful run, update the stored high-water mark to the maximum key value from this run.

Limitations:

• Requires a reliable incremental key. If updated_at is nullable, not updated on all writes, or not indexed, this strategy breaks.
• Does not capture hard deletes — if a row is deleted from the source, the high-water mark strategy will not propagate that deletion to the destination.
• Clock skew between the source system and the pipeline can cause rows written just before a run boundary to be missed.

Clock skew mitigation: Use a lookback window. Instead of querying updated_at > last_high_water_mark, query updated_at > last_high_water_mark - 5 minutes. Reprocess the last few minutes of the previous window to catch any rows that arrived slightly out of order. Handle resulting duplicates with upserts.

Change Data Capture (CDC)

CDC reads the database's replication log (binlog in MySQL, WAL in Postgres) and captures every change — inserts, updates, and deletes — as a stream of events. This is the most complete incremental strategy because it captures all change types, including deletes, with low latency.

How it works:

The database engine writes a log of every committed change. CDC tools (Debezium, AWS DMS, Fivetran, Airbyte) read this log and publish changes as events that can be consumed by the destination pipeline.

Strengths:

• Captures inserts, updates, and deletes — the high-water mark pattern misses deletes entirely.
• Very low latency — changes can be propagated within seconds of being committed.
• Minimal load on the source — reading the replication log is far lighter than running large queries.

Limitations:

• Requires database-level configuration (enabling binlog or WAL replication). Not all sources support this.
• More complex to set up and operate than query-based incremental loading.
• Log-based CDC requires managed infrastructure or a dedicated service (Debezium on Kafka, a managed connector service).
• Schema changes require careful handling — the log structure changes when table schemas change.

When to use CDC: When you need near-real-time data freshness, when deletes must be propagated, or when the source table is too large and active for even incremental queries to be practical.

Partition-Based Loading

Instead of tracking individual row changes, partition-based loading processes data in fixed time windows. On each run, a specific partition (a day, an hour, a month) is reprocessed completely.

How it works:

1. Identify the time partition to process (e.g., today's date, or the current hour).
2. Delete all rows in the destination for that partition.
3. Query all source rows that belong to that partition.
4. Insert them into the destination.

Strengths:

• Simple and idempotent by design — the partition delete-then-insert pattern makes retries safe.
• Easy to backfill historical data — re-run a specific partition without affecting others.
• Works well for append-only data (events, logs) where rows are associated with a fixed time period and do not change after creation.

Limitations:

• Does not handle late-arriving data well by default. If a row with a timestamp from three days ago arrives today, it belongs to the three-day-old partition, not today's partition.
• Requires all rows to have a reliable partition key (typically an event timestamp).
• If a source row can be updated after creation, partition-based loading on the creation timestamp will miss updates.

Late data handling: Process a rolling window of partitions rather than just the current one. For example, always reprocess the last three days on each run. This catches late-arriving data at the cost of additional query and processing volume.

Soft-Delete Propagation

The high-water mark pattern and partition-based loading both fail to propagate hard deletes from the source. If your application uses soft deletes (a deleted_at column rather than actual row deletion), the high-water mark pattern works naturally — a soft delete updates updated_at, which falls within the incremental window.

For hard deletes, options are:

• CDC — the most complete solution.
• Periodic full reconciliation — run a full table hash comparison between source and destination periodically (weekly or monthly) and propagate any detected deletes. This is cheap to implement but introduces a lag between the delete and the propagation.
• Delete log table — the source application writes to a deleted_ids log table when rows are deleted. The pipeline reads this log incrementally.

Choosing the Right Strategy

Strategy	Complexity	Captures Deletes	Latency	Best For
High-water mark	Low	No	Minutes	Tables with reliable updated_at
CDC	High	Yes	Seconds	Large tables, real-time needs
Partition-based	Low	By partition	Minutes/Hours	Append-only, event data
Soft-delete aware	Low	Soft deletes only	Minutes	Apps using soft-delete pattern

The right strategy depends on your source schema, latency requirements, and whether deletes must be propagated. Most production data stacks use a combination: high-water mark for most tables, CDC for high-volume or latency-sensitive tables, and partition-based for event and log data.

If you are designing the data ingestion layer for a growing product and want to get the incremental strategy right before the data volume makes it expensive to change, Clixo's engineering team builds and reviews production data pipeline architecture.