Working With Incremental Loads

Why this matters

As an Analytics Engineer, you often move data from raw sources into clean, analytics-ready tables. Full refreshes can be slow, expensive, and risky. Incremental loads let you process only new or changed rows, keep tables fresh within minutes, and support upserts and deletes—all while staying cost-effective.

• Daily job: Upsert yesterday's order changes into a fact table.
• Near-real-time need: Load the last 15 minutes of events with overlap to handle late data.
• Governance: Track a high-watermark so reruns are safe and idempotent.

Concept explained simply

Incremental loading means you only pull the slice of data that changed since the last successful run (the watermark), deduplicate it, and merge it into the target table.

Mental model

Think of a bookmark in a long book. Each run, you start reading at your bookmark minus a small overlap, re-read a few lines to be safe, then move the bookmark forward. The overlap prevents you missing words that were printed late.

• Bookmark = high-watermark (e.g., max updated_at processed).
• Overlap window = safety margin (e.g., 10 minutes) to capture late-arriving rows.
• Re-reading = deduping so duplicates don't accumulate.

Core patterns

-- Stage slice with overlap and dedupe
generate_slice as (
  select *
  from source
  where updated_at >= :last_watermark - interval '10 minute'
),
latest_per_id as (
  select s.*,
         row_number() over(partition by id order by updated_at desc) as rn
  from generate_slice s
)
select * from latest_per_id where rn = 1;

select id,
       md5(coalesce(status,'') || '|' || coalesce(amount::text,'')) as row_hash,
       *
from daily_extract;

Worked examples

Example 1: Timestamp-based upsert with MERGE

Goal: Upsert orders by id using updated_at as the watermark.

-- Inputs: source.orders_raw(id, status, amount, updated_at)
-- Target: analytics.dim_orders(id primary key, status, amount, updated_at)
with slice as (
  select *
  from source.orders_raw
  where updated_at >= :last_watermark - interval '10 minute'
), latest as (
  select *, row_number() over(partition by id order by updated_at desc) rn
  from slice
)
merge into analytics.dim_orders t
using (select * from latest where rn = 1) s
   on t.id = s.id
when matched and s.updated_at > t.updated_at then
  update set status = s.status,
             amount = s.amount,
             updated_at = s.updated_at
when not matched then
  insert (id, status, amount, updated_at)
  values (s.id, s.status, s.amount, s.updated_at);
-- After a successful run, set :last_watermark = max(s.updated_at)

Example 2: Deduplicate by business key

Goal: Keep latest customer record per email, in case of multiple rows in the slice.

with slice as (
  select * from landing.customers_daily
  where file_date = :run_date
), latest as (
  select *, row_number() over(
    partition by lower(trim(email))
    order by coalesce(updated_at, ingested_at) desc
  ) rn
  from slice
)
select * from latest where rn = 1;

Example 3: Applying deletes via tombstones

Goal: Remove rows marked deleted in CDC.

merge into analytics.dim_products t
using (
  select product_id, is_deleted, updated_at, name
  from cdc.products
  where commit_ts >= :last_watermark - interval '10 minute'
) s
on t.product_id = s.product_id
when matched and s.is_deleted = true then delete
when matched and s.updated_at > t.updated_at and coalesce(s.is_deleted,false) = false then
  update set name = s.name, updated_at = s.updated_at
when not matched and coalesce(s.is_deleted,false) = false then
  insert (product_id, name, updated_at)
  values (s.product_id, s.name, s.updated_at);

Step-by-step implementation

1. Choose the change detector: updated_at, CDC commit_ts, or a row hash.
2. Define overlap: e.g., 10 minutes for eventual consistency.
3. Stage slice: Filter by watermark - overlap.
4. Dedupe: Use row_number() over business key ordered by freshness.
5. MERGE: Upsert and delete as needed.
6. Validate: Compare counts, spot-check a few keys.
7. Advance watermark: Set to max(change detector) from processed slice.

Exercises

Note: The quick test is available to everyone; only logged-in users get saved progress.

Self-check checklist

• Dedupe keeps only the latest row per key in the slice.
• MERGE only updates when source is newer or hash changed.
• Watermark advances to the max processed value.
• Overlap is applied to prevent missed late rows.

Common mistakes and how to self-check

• No overlap: Risk of missing late rows. Fix: use 5–30 minute overlap.
• Updating regardless of recency: Can overwrite with stale data. Fix: compare timestamps or hashes.
• Skipping dedupe: Duplicates from multiple files or overlap. Fix: window function row_number().
• Advancing watermark too early: If MERGE fails, you can lose data. Fix: advance only after validation success.
• Timezone mismatch: Source and warehouse differ. Fix: normalize to UTC before comparisons.
• Large MERGE scans: Slow and costly. Fix: partition/cluster and prefilter source keys.

Practical projects

• Orders pipeline: Build a daily incremental upsert for orders with 10-minute overlap and a watermark table.
• Customer SCD1: Upsert customer attributes using a row hash and detect changes without reliable updated_at.
• CDC deletes: Apply delete tombstones from a CDC feed to a product dimension.

Who this is for

• Analytics Engineers implementing data models in a warehouse.
• BI Developers needing timely, cost-effective refreshes.
• Data Engineers building reliable ELT workflows.

Prerequisites

• Comfort with SQL joins, window functions, and basic DDL.
• Understanding of primary keys and uniqueness.
• Basic knowledge of your warehouse MERGE syntax.

Learning path

1. Window functions for dedupe.
2. MERGE patterns for upserts and deletes.
3. Watermarks and idempotent scheduling.
4. Optimization: partitions, clustering, and prune keys.

Next steps

• Parameterize your SQL (watermark and overlap as inputs).
• Add row-count and freshness validations post-merge.
• Document the watermark table and recovery steps.

Mini challenge

Design an incremental load for a web_events table that arrives out-of-order up to 20 minutes and sometimes emits duplicate event_ids. Describe your overlap window, dedupe key(s), and how you would update the watermark.