MERGE, Upserts, and Idempotent Pipelines

MERGE, Upserts, and Idempotent Pipelines From Scratch

MERGE lets Snowflake insert new rows and update existing rows in one statement. Idempotency means a pipeline can run again without corrupting data or double-counting results.

The Concepts You Must Own

• ✓MERGE compares a source dataset to a target table using a match condition.
• ✓WHEN MATCHED handles updates or deletes for existing records.
• ✓WHEN NOT MATCHED handles inserts for new records.
• ✓Deduplicate staging data before merging.
• ✓Use watermarks and load IDs to track what has been processed.

Step-by-Step Workflow

• ✓Load incoming data into a staging table.
• ✓Validate and deduplicate staging rows by business key.
• ✓MERGE staging into the target table using a stable key.
• ✓Record load metadata: file name, batch id, row counts, and timestamps.
• ✓Make reruns safe by avoiding blind appends for mutable entities.

CREATE OR REPLACE TEMP TABLE STG_ORDERS_DEDUPED AS
SELECT *
FROM STG_ORDERS
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY updated_at DESC, loaded_at DESC
) = 1;

MERGE INTO SILVER.ORDERS tgt
USING STG_ORDERS_DEDUPED src
  ON tgt.order_id = src.order_id
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET
  customer_id = src.customer_id,
  status = src.status,
  total_usd = src.total_usd,
  updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (
  order_id, customer_id, status, total_usd, updated_at
) VALUES (
  src.order_id, src.customer_id, src.status, src.total_usd, src.updated_at
);

Do not read the example as magic syntax to memorize. Read it as a production habit: name the objects clearly, make assumptions visible, preserve enough metadata to debug later, and keep the business promise attached to the SQL.

Common Mistakes That Break Snowflake Projects

How to debug this topic

Start by asking what promise failed: freshness, correctness, access, speed, or cost. Then inspect the Snowflake evidence: query history, warehouse metering, task history, copy history, grants, row counts, and sample records. Good Snowflake debugging is not guessing. It is reading the platform metadata until the failure has a shape.

Production Notes

• ✓Create a load control table with batch_id, source_file, started_at, finished_at, inserted_count, updated_count, and status.
• ✓Test rerunning the same file twice; the target row count should not change incorrectly.
• ✓Use streams/tasks or orchestration carefully when multiple merges can touch the same target.
• ✓Prefer deterministic tie-breakers when deduplicating source records.

Explain It Like a Professional

Define MERGE as conditional insert/update/delete based on matching source and target rows. Then explain idempotency: rerunning the same batch should produce the same final state. Mention staging, dedupe, stable keys, watermarks, load audit tables, and guarding against older records overwriting newer records.

Mini project

Create a customer dimension that receives daily snapshots. Stage the snapshot, dedupe by customer_id, MERGE changed rows, insert new customers, and log the batch. Then rerun the same file to prove the pipeline is safe.

Questions you should answer out loud

• ✓How would you explain MERGE, Upserts, and Idempotent Pipelines to a non-technical manager?
• ✓Which Snowflake objects, roles, or SQL statements does this topic use?
• ✓What can fail in production and which metadata view would you inspect first?
• ✓What is the cost or security risk if this is implemented carelessly?
• ✓How would you test that the result is correct and rerunnable?