Templates in development

Written for Databricks, Snowflake and Fabric and tested on DuckDB. None has run on a real platform yet, and none is released.

CDC merge

CDC merge: every change, in order.

Apply a change feed of inserts, updates and deletes to a target table: the latest change per key, applied in order, with deletes honored and no failures from repeated keys.

Template in development
Sync and history
Databricks first
Platform notesSQL sketch
ShareLinkedInXRedditBluesky

Shape card

03 / 10

  1. Source (used)
  2. Bronze (used)
  3. Silver (used)
  4. Gold
  5. Apps
Group
Sync and history
Works across
Source to Silver
Reads
A change feed
Writes
A table in its current state
Slots
7 typed slots
Tested by
Golden tests on DuckDB
Platforms
Databricks first, then Snowflake and Microsoft Fabric

Overview

What CDC merge does.

Apply inserts, updates and deletes from a change feed to a target table.

What it does

CDC merge reads a change feed, such as the output of database change data capture or a platform's own change feed, and merges it into the target. New keys are inserted, changed keys are updated, and deleted keys are removed or flagged.

Within a batch, only the latest change for each key is applied, ordered by a sequence number or a commit timestamp. That is what makes the merge well defined: every key meets the target once, and an older change can never overwrite a newer one.

Use it when

  • Mirroring operational tables into the lakehouse.
  • Keeping a silver table in step with a source system.
  • Any source whose rows change after they are first written, including rows that are deleted.

Use another shape when

  • Rows are never updated and never deleted:

    02Incremental append

  • You need every past version of a record, not only its current state:

    04SCD Type 2

  • The source has no change feed and the table is small enough to copy whole:

    05Snapshot

Slots

Inputs, outputs and slots.

What the CDC merge template reads, what it writes and the typed slots it asks you for. The agent fills most slots from your catalog and your request.

In

Inputs

  • A change feed with a key, an operation and an ordering value on every change
  • The target table
  • The ordering value already stored in the target, to recognize stale changes

CDC merge

  1. Source (used)
  2. Bronze (used)
  3. Silver (used)
  4. Gold
  5. Apps

Out

Outputs

  • The target table, in its new current state
  • Deleted keys removed or flagged, as chosen
  • A run record: inserts, updates and deletes applied, and changes ignored as stale

Template slots

7 slots · names and types may change before release

change_source
table
The change feed: a table, view or set of files of change records.
target_table
table
The table that mirrors the source.
key_columns
columns
The columns that identify a record.
order_column
column
The sequence number or commit timestamp that orders changes to one key.
operation_column
column
The column that says whether a change is an insert, an update or a delete.
operation_values
string
The values that mean insert, update and delete in your feed, such as I, U and D.
delete_mode
enum
Remove the row, or keep it and flag it as deleted.

Slot types are identifier, table, column, columns, expression, expressions, string, integer, boolean, duration and enum. What is a template slot.

Testing

Tested before it ships.

Every template comes with golden tests that run on DuckDB on your machine before anything reaches your platform.

How a run goes

The golden test feeds a deliberately awkward batch and compares the target with the expected final state on DuckDB.

  1. 01Fixtures go in: small tables with the hard cases for the shape.
  2. 02The template runs on DuckDB on your machine, with no cluster and no warehouse compute.
  3. 03The result is compared with the expected output, row for row.
  4. 04Green can deploy. Red sends the agent back to the template.
What is a golden testGolden tests in the docs

What the CDC merge test checks

  1. Case 01

    Several changes to one key in the same batch, such as an insert followed by two updates. The key ends with the last update, applied once.

  2. Case 02

    An older change arrives after a newer one for the same key. It is ignored.

  3. Case 03

    A delete removes the row, or flags it, as the delete_mode slot says.

  4. Case 04

    A change with a null key is reported and never merged.

  5. Case 05

    The same batch applied twice leaves the target unchanged.

Each template is also judged per platform: yellow when it passes on DuckDB, green when it also passes on the real platform. Nothing is released, so no template has a green mark yet.

Platforms

Written for the platform you run.

The same shape has a different correct form on each platform. This is what the CDC merge template uses on each one, and the platform facts that decide it.

Databricks

In development

The Databricks template uses MERGE INTO on Delta tables, with Delta Change Data Feed as the source where it is on, or the change-data APIs in Lakeflow Declarative Pipelines.

  • A Delta MERGE fails when more than one source row matches the same target row. That is why the template reduces each key to its latest change first.
  • Once Change Data Feed is enabled on a table, it exposes each row change with its change type and commit version, ready to use as the change source.
  • The AUTO CDC flow in Lakeflow Declarative Pipelines (previously APPLY CHANGES) takes the keys, a sequence column and a delete condition, and handles out-of-order records declaratively.
  • The job's principal needs MODIFY on the target table in Unity Catalog.
Emolysis for Databricks

Snowflake

Planned

The Snowflake template uses MERGE fed by a stream, or a dynamic table.

  • A standard stream on a source table reports each update as a delete and an insert pair. The template reads the stream's metadata columns to tell them apart.
  • By default Snowflake raises an error when a MERGE matches several source rows to one target row. Turning that check off lets it pick an arbitrary row instead. Reducing to one change per key first avoids both.
  • A stream's offset moves only when the statement that reads it commits, so a failed task run replays the same changes.
  • A dynamic table can present the latest row per key with QUALIFY and filter out deleted keys. It is a lighter option when you need a current-state view rather than a table you maintain.
Emolysis for Snowflake

Microsoft Fabric

Planned

The Fabric template uses MERGE on lakehouse Delta tables with Spark SQL.

  • A notebook runs the same Delta MERGE as on Databricks, including the reduction to one change per key.
  • A Data Pipeline runs the notebook on a schedule and can pass the batch's range as parameters.
  • The merged table is a Delta table in OneLake, so the SQL analytics endpoint and other engines read the same files.
  • The template targets lakehouse tables. A warehouse target would use T-SQL, whose surface differs from SQL Server's.
Emolysis for Microsoft Fabric

Whatever the platform, the template is tested on DuckDB first. The platform notes describe each product in general terms, so check the details against the version you run.

Mistakes

Mistakes it prevents.

The failures that most often hit a pipeline of this shape in production. Each one is a case in the template's golden test or a rule in its design.

  1. A merge that fails, or picks an arbitrary row

    When a key appears twice in one batch, strict engines raise an error and lenient ones pick any matching row. The template reduces every key to its latest change before the merge.

  2. Updates applied out of order

    A late-delivered older change overwrites a newer value. The template orders by a sequence number or commit time and ignores changes older than what the target holds.

  3. Deletes silently ignored

    A merge with only insert and update clauses keeps rows the source has deleted, forever. The delete clause is part of the template, and the golden test checks it.

  4. Rows with null keys

    A null never matches, so such a row inserts a new copy on every run. The template reports null keys instead of merging them.

SQL sketch

The idea in SQL.

Latest change per key, then merge

Illustrative sketch in generic SQL. The Databricks, Snowflake and Fabric templates use each platform's merge syntax and change source.

Illustrative sketch · SQL
-- 1. Reduce the batch to the latest change per key, in a total order.
CREATE TEMPORARY VIEW latest_change AS
SELECT customer_id, name, email, op, commit_ts
FROM (
  SELECT
    c.*,
    ROW_NUMBER() OVER (
      PARTITION BY c.customer_id
      ORDER BY c.commit_ts DESC, c.seq DESC
    ) AS rn
  FROM staged.customer_changes AS c
  WHERE c.customer_id IS NOT NULL
) AS ranked
WHERE rn = 1;

-- 2. Apply it. A change older than what the target already holds is ignored.
--    With delete_mode = flag, the delete clause would set a flag instead.
MERGE INTO silver.customers AS t
USING latest_change AS s
  ON t.customer_id = s.customer_id
WHEN MATCHED AND s.commit_ts >= t.source_commit_ts AND s.op = 'D' THEN
  DELETE
WHEN MATCHED AND s.commit_ts >= t.source_commit_ts THEN
  UPDATE SET name = s.name, email = s.email, source_commit_ts = s.commit_ts
WHEN NOT MATCHED AND s.op <> 'D' THEN
  INSERT (customer_id, name, email, source_commit_ts)
  VALUES (s.customer_id, s.name, s.email, s.commit_ts);

Related

Shapes that work alongside it.

Real pipelines are several shapes in a row. These are the ones that most often sit next to this one.

04

Sync and history

SCD Type 2

To keep every version of a record instead of only its current state.

Explore SCD Type 2
02

Load

Incremental append

For sources whose rows are never updated.

Explore Incremental append
08

Model and serve

Dedupe / conform

For clean silver tables when the source is not a change feed.

Explore Dedupe / conform

Glossary

Keep reading

FAQ

Questions about CDC merge.

What is a CDC merge?

Change data capture records the inserts, updates and deletes made to a source table. A CDC merge applies that change feed to a target table with a MERGE: new keys are inserted, changed keys are updated and deleted keys are removed or flagged. The result is a target that mirrors the source's current state.

Why reduce to one change per key before merging?

A MERGE is only well defined when each key appears once in the incoming batch. With several changes to one key, Delta and Snowflake raise an error by default, and with the check turned off Snowflake picks an arbitrary row. Keeping the latest change per key, ordered by sequence number or commit time, gives the same result on every run.

How are deletes handled?

The delete_mode slot chooses between removing the row and keeping it with a deleted flag. A flag keeps history available for audits and lets downstream tables treat a delete as an update. Either way, the golden test includes a delete, so a merge that ignores deletes fails before deploy.

What is the difference between Delta Change Data Feed and a Snowflake stream?

Both expose the row-level changes of a table. Delta Change Data Feed is stored with the table and can be read for any range of versions the table still retains, as often as you like. A Snowflake stream keeps an offset that moves when a statement reading it commits, so it is consumed as it is used. The template reads each one the way its platform intends.

In development · Not released

Test first, then deploy.

The CDC merge template is written for Databricks, Snowflake and Microsoft Fabric, and its golden tests pass on DuckDB. Nothing is released yet. Early access is by invitation, for teams that want to help shape it.

Apply for early accessAll ten shapes