Keep a table in sync with CDC merge

A CDC merge keeps a target table in step with a source by applying its changes. It sounds like one statement. In practice, merges break on duplicate keys in a batch, on changes applied out of order and on deletes that are silently ignored. This tutorial follows the flow on Databricks.

What you need

• The Emolysis desktop app, an Emolysis account and a connected Databricks workspace.
• A change feed landed in bronze, for example dev.bronze.orders_changes, with order_id, status, amount, op (insert, update or delete) and commit_ts.
• A target table, for example dev.silver.orders, or let Emolysis create it.

Step 1: Ask for the pipeline

Type: "Keep dev.silver.orders in sync with the changes in dev.bronze.orders_changes." The agent picks CDC merge and shows the slots:

• Key: order_id.
• Ordering column: commit_ts.
• Operation column: op, with values I, U and D, mapped as you confirm.
• Deletes: remove the row, or keep it with a deleted flag.
• Target: dev.silver.orders.

If your feed has a sequence number as well as a timestamp, say so. Two changes with the same timestamp need a second ordering column.

Step 2: Read the filled template

The Databricks rendering first keeps only the latest change per key within the batch, ordered by commit_ts, then runs a MERGE INTO on the Delta target: matched keys with a delete are removed or flagged, matched keys with an update are updated, and unmatched keys are inserted. Keeping one change per key first is what stops the merge failing on duplicate keys.

Step 3: Run the golden tests

The CDC merge golden test runs on DuckDB with a mix of inserts, updates and deletes, including three changes to one key in the same batch and a delete followed by a re-insert. It compares the target with the expected final state. Expect green in seconds. The golden tests docs explain how fixtures and expected outputs work.

Step 4: Add a case from your own feed

Look at your feed for something unusual, such as an update for a key that was never inserted. Add a synthetic version of it to the fixtures with the result you expect, and run the tests again. If the template's behavior differs from what you want, adjust the slot (for example, treat orphan updates as inserts) and re-run until green.

Step 5: Choose how it runs

Tell the agent how often the merge should run, or whether it should follow new changes as they land. On Databricks, the profile chooses between a scheduled job reading new rows by watermark and the change-data APIs in Lakeflow Declarative Pipelines, depending on what you ask for. Review the choice before deploying.

Step 6: Deploy

Choose Deploy or run emo run pipeline.deploy. Emolysis packages the pipeline as a Databricks Asset Bundle and deploys it to your workspace, where it runs on your compute under your account. See deploying pipelines.

Step 7: Check the result

After the first run, compare row counts between the source system and the target, and spot-check keys that were updated and deleted. If you enabled a deleted flag, confirm deleted orders carry it.

Common follow-ups

• History: if you need every past status of an order, add an SCD Type 2 step on top of the merged table.
• Backfill: to rebuild the target from an older point in the feed, use the Backfill shape.