how to do incremental loads without full refresh

TL;DR

Load only what changed: track a watermark column like updated_at, then MERGE or INSERT new rows and UPDATE changed ones keyed on the primary key. Incremental loads turn a full-table rewrite into a small delta, which is faster, cheaper, and easier to make idempotent.

how to do incremental loads without full refresh

Use this when

• A full refresh job is too slow or too expensive
• Source tables have an updated_at timestamp or incrementing id
• You are designing a new pipeline and want deltas from day one

Not for this skill when

• You need to backfill years of history once (thats a backfill, different pattern)
• Data arrives as a real-time stream (use a streaming framework)
• The current load is failing (fix the failure first, then optimize)

Steps

1. Confirm you have a reliable watermark: a column that changes on every write:

SELECT max(updated_at) AS watermark, count(*) AS rows
FROM source_orders;

Expected output: a recent timestamp and the row count. If updated_at is NULL for some rows or never updated on change, stop here and fix the source; no watermark means no incremental load.

1. Store the last successful watermark and pull only newer rows:

-- watermark table holds one row per pipeline
SELECT last_watermark FROM etl_watermark WHERE pipeline = 'orders';
-- delta extract
SELECT * FROM source_orders WHERE updated_at > '2026-10-03 06:00:00';

Expected output: just the changed rows. Save the new max as the watermark only after the load succeeds, or a failed run will skip rows forever.

1. Upsert the delta into the target keyed on the primary key:

MERGE INTO dim_orders AS t
USING staging_orders_delta AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount, updated_at)
VALUES (s.order_id, s.status, s.amount, s.updated_at);

Expected output: a merge count of updated plus inserted rows. On Postgres without MERGE (pre-15), use INSERT ... ON CONFLICT (order_id) DO UPDATE.

1. Handle deletes explicitly, because a watermark cant see them:

-- option A: source provides a deleted flag or tombstones
DELETE FROM dim_orders
WHERE order_id IN (SELECT order_id FROM staging_deletes);
-- option B: soft-delete so history survives
UPDATE dim_orders SET is_deleted = true
WHERE order_id IN (SELECT order_id FROM staging_deletes);

Expected output: deleted rows removed or flagged. If the source gives you no delete signal, do a periodic full key comparison to catch them.

1. Make reruns safe by keying the delta on idempotent upsert, not on append:

-- rerunning the same delta twice changes nothing
SELECT order_id, count(*) FROM dim_orders GROUP BY order_id HAVING count(*) > 1;

Expected output: zero rows. Because the MERGE matches on primary key, replaying a delta is a no-op instead of duplicating data.

Variant phrasings

incremental load vs full load SQL

Full load is TRUNCATE plus INSERT of everything; incremental is watermark plus upsert. Steps 2-3 are the whole difference.

how to do delta load in data pipeline

Same pattern: extract the delta (step 2), merge it (step 3), advance the watermark. The orchestration differs, the SQL doesnt.

dbt incremental model

{{ config(materialized='incremental') }} with {% if is_incremental() %} filtering on the watermark. Same watermark-plus-upsert idea, managed by dbt.

Why it happens

Full refreshes rescan and rewrite data that hasnt changed, so cost and runtime grow with table size instead of change volume. A watermark column turns "what changed" into a range predicate the source can answer with an index, and a keyed upsert makes the apply step safe to retry. The two together give you loads that scale with churn, not size.

Edge cases

• Late-arriving data older than the watermark gets missed; use a lookback window (watermark minus a few hours) and rely on idempotent upsert to absorb replays.
• Updates that dont bump updated_at (bulk backfills, direct SQL) are invisible; prefer change-data-capture when you cant trust the column.
• Schema changes in the source break the delta silently; version your staging table and validate columns before merging.
• The first run has no watermark: seed it with a full load or a far-past timestamp, and document which you chose.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_W5sXSqsxlzTV6eOt1nAlEA