Incremental Data Loading: A Practical Guide
Incremental data loading is what separates a pipeline that runs in seconds from one that times out at 3 a.m. Every time you move data from a source to a destination, you have exactly two choices: full reload (grab everything, every run) or incremental (only grab what changed since last time). This guide covers the three patterns that cover 95% of real-world cases, with SQL you can copy.
updated_at column. If your source table overwrites that column or has hard deletes, move to a high-water mark on a monotonically increasing ID. CDC is the gold standard but requires infrastructure you might not have yet.Full reload vs incremental: the trade-off
A full reload truncates the target table and re-inserts every row from the source. It’s simple, consistent, and impossible to get wrong — which is why it’s the default in many pipelines. The downside is obvious: when the source table has 50 million rows, a full reload burns compute, storage I/O, and time that you don’t have.
Incremental loading only processes rows that are new or changed since the last run. The trade-off is complexity: you need a reliable way to identify “changed since last time,” and you need to handle deletes. Done right, it turns a 45-minute pipeline into a 30-second one.
Here’s the rule of thumb DataStack Daily uses: if the source has fewer than 100,000 rows and the pipeline runs once a day, full reload is fine. Above that line, or if your pipeline runs hourly or more often, think incrementally.
Pattern 1: Timestamp delta
Filter the source on a timestamp column—typically updated_at—and only grab rows where that timestamp is after the pipeline’s last successful run.
| What you need | Example |
|---|---|
| A reliable timestamp column | updated_at TIMESTAMP that the application sets on every write |
| A state store for the last run time | A control table or the MAX(updated_at) of the target |
| Late-arriving data tolerance | A lookback window: subtract 1-2 hours from your watermark |
-- 1. Get the waterline from the last loaded row
SELECT COALESCE(MAX(updated_at), '1900-01-01') AS waterline
FROM target_schema.orders;
-- 2. Pull source rows newer than the waterline (minus lookback)
SELECT *
FROM source_db.public.orders
WHERE updated_at > '2026-07-09 12:00:00'::TIMESTAMP - INTERVAL '2 hours';
Watch out for: The timestamp delta fails if the source application can update rows without touching updated_at (some ORMs have a config flag for this; some legacy systems simply don’t have the column). It also can’t detect hard deletes—if a row is removed from the source, you won’t know to remove it from the target.
Use this pattern when: your source has a trustworthy updated_at, you have a small buffer for late data, and hard deletes are either rare or handled by a separate soft-delete flag.
Pattern 2: High-water mark
Version 2 of the same idea: instead of a timestamp, track a monotonically increasing integer—usually an auto-increment id or a sequence. Store the last loaded MAX(id), and on the next run, pull every row with a higher ID.
Pros
- Immune to timestamp drift or timezone bugs
- Works even if the source doesn’t have
updated_at - Deterministic: you can’t miss a row due to clock skew
Cons
- Won’t catch updates to existing rows (IDs don’t change)
- Still can’t detect deletes
- Requires your source table to have a monotonically increasing column
Use this pattern when: you’re loading append-only tables (event logs, audit trails, clickstreams) or tables where updates to old rows are genuinely rare. Combine with a periodic full reload to clean up straggling updates.
Pattern 3: Change data capture (CDC)
CDC is the gold standard: instead of querying the source table directly, you read from a changelog that records every INSERT, UPDATE, and DELETE as a separate event with the operation type and a timestamp.
In a pipeline, the load step becomes: read the changelog since your last checkpoint, apply each change event to the target table. INSERT events add rows, UPDATE events modify them, DELETE events remove them. Everything is accounted for.
Watch out for: CDC requires infrastructure (Kafka, a replication slot in Postgres, or a cloud service like DMS). It’s the right answer at scale, but it’s a project, not a query tweak. And replication slots can fill up if the consumer falls behind—monitor slot lag.
How to choose the right pattern
| Your situation | Start with | Upgrade to |
|---|---|---|
Source has updated_at, < 1M rows/day | Timestamp delta | High-water mark (if updates are rare) |
| Append-only table (logs, events) | High-water mark on ID | Timestamp delta if you need recency |
| Need to catch updates and deletes reliably | CDC (log-based) | — |
| Small dimension table (< 100K rows) | Full reload | Timestamp delta |
Here’s the wisdom of experience: start simple, and only add complexity when the pipeline actually breaks. A timestamp delta on an updated_at column covers 70% of batch-load use cases. Add a data quality check on row counts and you’ll catch most failures before they reach a dashboard.
FAQ
What happens if the pipeline fails mid-run?
When the waterline is the last successful run time (stored in a control table, updated after the load commits), a failure simply re-runs the same range. If you’re using MAX(updated_at) from the target as your waterline, a partial load will have partial data, and the next run will pick up from the highest loaded timestamp—which may skip rows that should have been included. Control tables are safer.
How do I handle hard deletes with a timestamp or high-water pattern?
For append-only targets where deletes are rare, run a periodic (weekly or monthly) full reload to clean up. If you need live delete accuracy, upgrade to CDC. Some teams use a soft-delete convention (is_deleted = TRUE) in the source so the incremental query can detect and propagate it.
What is the lookback window and how big should it be?
The lookback window is a buffer subtracted from your waterline to catch late-arriving rows. A row written at 09:58 might not be committed until 10:02. A 1-2 hour lookback covers clock skew and slow commits in most operational databases. Adjust based on your source’s latency profile.
Can I combine patterns?
Yes. A common real-world setup: high-water mark on an append-only events table (fast), CDC on the core transactional tables (accurate), and a monthly full reload of small dimension tables (simple). Each table gets the right strategy for its characteristics.
Every data pipeline eventually hits the point where a full reload doesn’t fit the run window. When it does, the three patterns above will cover almost every table in your warehouse. Start simple, test your waterline logic with a dry run, and always run a row-count reconciliation before declaring victory. Ready for the next step? See how these patterns fit inside the modern data stack and where data quality checks plug in.
Last updated: July 10, 2026
Comments
Post a Comment