MERGE / UPSERT Pattern

A canonical MERGE that upserts staging rows into a dimension. Lineage exposes which target columns are sourced from which staging columns.

The lineage

The SQL

CREATE OR REPLACE TABLE stg_customers (
  customer_id NUMBER,
  email STRING,
  full_name STRING,
  updated_at TIMESTAMP
);

CREATE OR REPLACE TABLE dim_customers (
  customer_id NUMBER,
  email STRING,
  full_name STRING,
  updated_at TIMESTAMP
);

MERGE INTO dim_customers t
USING stg_customers s
  ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  email = s.email,
  full_name = s.full_name,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, email, full_name, updated_at)
VALUES (s.customer_id, s.email, s.full_name, s.updated_at);

What the lineage shows

• Each dim_customers column is fed by the matching stg_customers column.
• MERGE lineage is identical for INSERT and UPDATE branches when columns line up.
• Use the lineage view to confirm no target column was accidentally hardcoded.