SQL Server MERGE with OUTPUT

A SQL Server MERGE that captures changed rows using the OUTPUT clause. Lineage covers both the target table and the audit table.

The lineage

The SQL

CREATE TABLE dim_product (
  product_id INT PRIMARY KEY,
  name NVARCHAR(200),
  price DECIMAL(10,2)
);

CREATE TABLE stg_product (
  product_id INT,
  name NVARCHAR(200),
  price DECIMAL(10,2)
);

CREATE TABLE dim_product_audit (
  change_action NVARCHAR(20),
  product_id INT,
  old_price DECIMAL(10,2),
  new_price DECIMAL(10,2),
  changed_at DATETIME2 DEFAULT SYSUTCDATETIME()
);

MERGE dim_product AS t
USING stg_product AS s ON t.product_id = s.product_id
WHEN MATCHED AND t.price <> s.price THEN
  UPDATE SET t.name = s.name, t.price = s.price
WHEN NOT MATCHED THEN
  INSERT (product_id, name, price) VALUES (s.product_id, s.name, s.price)
OUTPUT $action, inserted.product_id, deleted.price, inserted.price
INTO dim_product_audit (change_action, product_id, old_price, new_price);

What the lineage shows

• dim_product columns are fed by stg_product columns of the same name.
• dim_product_audit.old_price comes from the deleted (pre-update) row, new_price from inserted.
• OUTPUT lineage flows from MERGE into a separate audit sink.