Loading a full table on every pipeline run is expensive and slow. Once your source tables grow beyond a few million rows, full loads become impractical. Incremental loading — processing only new and changed data since the last run — is the pattern that makes production data pipelines scalable.
This tutorial covers three incremental load patterns every data engineer needs to know, with complete working SQL for each.
The Dataset
-- Source system: transactional orders table
CREATE TABLE source.orders (
order_id INT,
customer_id INT,
amount DECIMAL(10,2),
status VARCHAR(20),
created_at TIMESTAMP,
updated_at TIMESTAMP
);
-- Warehouse target
CREATE TABLE warehouse.orders (
order_id INT,
customer_id INT,
amount DECIMAL(10,2),
status VARCHAR(20),
created_at TIMESTAMP,
updated_at TIMESTAMP,
loaded_at TIMESTAMP
);
-- Pipeline metadata table — tracks last successful load
CREATE TABLE pipeline.watermarks (
table_name VARCHAR(100),
last_loaded TIMESTAMP
);
INSERT INTO pipeline.watermarks VALUES ('orders', '2024-01-31 23:59:59');
Pattern 1 — Timestamp Watermark (Most Common)
The simplest and most widely used incremental pattern. You store the timestamp of the last successful load, then on each run you only process rows where updated_at is greater than that watermark.
Step 1: Read the watermark
SELECT last_loaded
FROM pipeline.watermarks
WHERE table_name = 'orders';
Returns: 2024-01-31 23:59:59
Step 2: Extract new and changed rows since the watermark
-- In your pipeline, bind last_loaded as a parameter
SELECT
order_id,
customer_id,
amount,
status,
created_at,
updated_at
FROM source.orders
WHERE updated_at > '2024-01-31 23:59:59' -- replace with parameter
ORDER BY updated_at;
Output (rows changed since Jan 31):
| order_id | customer_id | amount | status | created_at | updated_at |
|---|---|---|---|---|---|
| 2001 | 301 | 500.00 | completed | 2024-02-01 | 2024-02-01 09:00:00 |
| 2002 | 302 | 750.00 | pending | 2024-02-01 | 2024-02-01 09:05:00 |
| 1850 | 290 | 300.00 | returned | 2024-01-20 | 2024-02-01 10:00:00 |
Notice order 1850 — it was created on January 20th but updated on February 1st. Filtering on updated_at correctly captures this change even though created_at is in the past period.
Step 3: Load into the warehouse using MERGE
MERGE INTO warehouse.orders AS target
USING (
SELECT * FROM source.orders
WHERE updated_at > '2024-01-31 23:59:59'
) AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
UPDATE SET
target.amount = source.amount,
target.status = source.status,
target.updated_at = source.updated_at,
target.loaded_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status, created_at, updated_at, loaded_at)
VALUES (source.order_id, source.customer_id, source.amount, source.status,
source.created_at, source.updated_at, CURRENT_TIMESTAMP);
Step 4: Update the watermark after a successful load
UPDATE pipeline.watermarks
SET last_loaded = (
SELECT MAX(updated_at) FROM source.orders
WHERE updated_at > '2024-01-31 23:59:59'
)
WHERE table_name = 'orders';
Critical: Only update the watermark after the MERGE completes successfully. If you update it before and the MERGE fails, your pipeline will skip those rows on the next run — silent data loss.
Pattern 2 — Auto-Incrementing ID Watermark
When your source table has a reliable auto-incrementing primary key (and rows are never updated, only inserted), you can use the max ID as your watermark instead of a timestamp. This is faster because integer comparisons are cheaper than timestamp comparisons on large tables.
-- Track last loaded ID instead of timestamp
CREATE TABLE pipeline.watermarks (
table_name VARCHAR(100),
last_id BIGINT
);
INSERT INTO pipeline.watermarks VALUES ('orders', 1999);
Extract rows with ID above the watermark
SELECT *
FROM source.orders
WHERE order_id > (
SELECT last_id FROM pipeline.watermarks WHERE table_name = 'orders'
)
ORDER BY order_id;
Update watermark after load
UPDATE pipeline.watermarks
SET last_id = (SELECT MAX(order_id) FROM warehouse.orders)
WHERE table_name = 'orders';
When to use this vs timestamp: Use ID-based watermarks for append-only tables (event logs, audit trails, clickstream). Use timestamp-based watermarks when rows can be updated (orders that change status, customer records that get edited).
Danger: Never use ID watermarks on tables where rows can be updated. A changed row will never be re-processed because its ID is already below the watermark.
Pattern 3 — CDC-Based Incremental Load
Change Data Capture (CDC) is the most powerful incremental pattern. Instead of polling the source table, CDC reads the database transaction log (binlog in MySQL, WAL in PostgreSQL, change feed in Databricks Delta) and captures every INSERT, UPDATE, and DELETE as an event.
In Databricks, you enable CDC with Delta Lake’s Change Data Feed:
-- Enable CDF on a Delta source table
ALTER TABLE source.orders
SET TBLPROPERTIES (delta.enableChangeDataFeed = true);
Read changes since a specific version
-- Read all changes since table version 5
SELECT
order_id,
customer_id,
amount,
status,
updated_at,
_change_type, -- 'insert', 'update_preimage', 'update_postimage', 'delete'
_commit_version,
_commit_timestamp
FROM table_changes('source.orders', 5)
ORDER BY _commit_timestamp;
Output:
| order_id | amount | status | _change_type | _commit_version |
|---|---|---|---|---|
| 2001 | 500.00 | completed | insert | 6 |
| 1850 | 300.00 | pending | update_preimage | 7 |
| 1850 | 300.00 | returned | update_postimage | 7 |
| 1999 | 200.00 | completed | delete | 8 |
Understanding the change types:
insert— new row addedupdate_preimage— what the row looked like before the updateupdate_postimage— what the row looks like after the updatedelete— row was removed
Process CDC events into the warehouse
-- Apply CDC changes to target table
MERGE INTO warehouse.orders AS target
USING (
-- Take only the latest state per order (postimage > insert > ignore preimage)
SELECT order_id, customer_id, amount, status, updated_at, _change_type
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY _commit_version DESC
) AS rn
FROM table_changes('source.orders', 5)
WHERE _change_type IN ('insert', 'update_postimage', 'delete')
)
WHERE rn = 1
) AS source
ON target.order_id = source.order_id
WHEN MATCHED AND source._change_type = 'delete' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET target.amount = source.amount, target.status = source.status,
target.updated_at = source.updated_at
WHEN NOT MATCHED AND source._change_type != 'delete' THEN
INSERT (order_id, customer_id, amount, status, updated_at)
VALUES (source.order_id, source.customer_id, source.amount,
source.status, source.updated_at);
Track processed version in watermark table
UPDATE pipeline.watermarks
SET last_loaded = CAST(MAX(_commit_version) AS VARCHAR)
FROM (SELECT MAX(_commit_version) AS v FROM table_changes('source.orders', 5)) t
WHERE table_name = 'orders';
Handling Late-Arriving Data
One of the most common production problems with watermark-based loads is late-arriving data — rows that appear in the source after their event timestamp has already passed your watermark.
Example: An order from January 28th arrives in the source table on February 3rd due to an upstream system delay. Your January 31st watermark will miss it.
Solution — use a lookback buffer:
-- Instead of exact watermark, subtract a buffer period
SELECT *
FROM source.orders
WHERE updated_at > TIMESTAMP '2024-01-31 23:59:59' - INTERVAL '3 days'
AND updated_at <= CURRENT_TIMESTAMP;
The 3-day lookback means you re-process the last 3 days on every run, catching any late arrivals. The MERGE will update existing rows with no net change and insert genuinely new ones. The cost is slightly more data processed per run, but the benefit is no silent data gaps.
Common Mistakes
Mistake 1 — Using created_at instead of updated_at as your watermark column
created_at only captures new rows — it misses updates. Always use updated_at (or a similar modified timestamp) as your watermark column.
Mistake 2 — Updating the watermark before the load completes
If the load fails after you have already updated the watermark, those rows will never be re-processed on the next run.
Mistake 3 — No index on the watermark column in the source
WHERE updated_at > ? on a 50 million row table with no index will do a full table scan on every pipeline run. Always ensure the source table has an index on your watermark column.
Mistake 4 — Timezone mismatches
If your source stores timestamps in UTC and your pipeline runs in a different timezone, your watermark comparisons will be off. Always store and compare watermarks in UTC.
Quick Reference — Which Pattern to Use
| Pattern | Best For | Limitation |
|---|---|---|
| Timestamp watermark | Tables with an updated_at column | Misses rows with no update timestamp |
| ID watermark | Append-only event/log tables | Misses updates to existing rows |
| CDC (Delta CDF / binlog) | Full accuracy including deletes | Requires CDC-enabled source |
What to Learn Next
Incremental loading feeds data into your Silver layer. The next challenge is building Gold layer aggregations that stay performant as your Silver tables grow — the SCD Type 2 tutorial covers how to handle historical dimension tracking on top of your incremental Silver tables.