Snowflake MERGE: make source cardinality explicit

A merge is a rule for applying changes, not a general duplicate-removal tool. Before running it, define the business key, the winning source version and what should happen when a target row already exists. The same source batch should lead to the same decision when replayed.

Scenario

Illustrative lab: a product feed supplies two prices for product 42. The source version increases from 3 to 4; the learning task is to choose the newer version before a target update.

Worked example

Snowflake SQL. Read-only example; reviewed against official documentation, not run in a Snowflake account.

WITH changes AS (
  SELECT column1::INTEGER AS product_id,
         column2::INTEGER AS source_version,
         column3::NUMBER(10,2) AS price
  FROM VALUES (42, 3, 100.00), (42, 4, 120.00)
)
SELECT product_id, source_version, price
FROM changes
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY product_id ORDER BY source_version DESC
) = 1;

Expected output

PRODUCT_ID | SOURCE_VERSION | PRICE
42         | 4              | 120.00

Walk through the code

1. VALUES creates two sample rows without making a persistent table.
2. Explicit casts document the identifier, version and currency precision expected by the lab.
3. The window groups by product and orders versions from newest to oldest.
4. QUALIFY keeps version 4. In a real feed, equal conflicting versions require a separate ambiguity check.

Reason about the design

A good source-selection policy is testable without writing to the target. Start there. Then test the actual merge separately against a disposable target, including an absent key, an existing key, a delete event and a replay. Do not assume every duplicate source produces the same error: the matched and not-matched branches have different consequences. Keep nondeterministic-merge errors visible rather than turning them off to silence a broken contract.

Common mistakes

• Grouping by the key and independently taking MAX of every value. The resulting row can combine fields from different source events.
• Assuming a declared primary key on every Snowflake table type will automatically protect against duplicate data. Verify enforcement for the table type you use.
• Treating a bigger warehouse as a fix for an incorrect join condition. More compute does not fix wrong match logic.

Troubleshooting checklist

• Run a source-key count before the merge and inspect conflicting versions.
• Check the target key count separately. A clean source does not guarantee a clean target.
• Review the join predicate and the query profile after correctness is established; separate row explosion from slow scanning.

Try it yourself

Add product 43 at version 1. Expect two output rows. Add a second conflicting row for product 42 at version 4 and design a validation query that rejects that ambiguity before writing.

Interview follow-up

What tests distinguish a deterministic source selection from an idempotent end-to-end ingestion job?

Questions learners ask

Should I disable the nondeterministic merge error?

Resolve the source ambiguity instead. Suppressing the error can conceal a missing business rule.

Can I use this pattern for CDC?

It can be part of a CDC design, but you must also model deletes, transaction boundaries, event ordering and stream consumption.

Primary reference

Official documentation for this topic. Check the documentation for your deployed product version before applying the example.