## 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.

```text
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:

```sql
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.

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

```sql
-- 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.

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

```sql
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.

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

```sql
-- 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.

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

```sql
-- 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