https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/ba-p/155355 <P><SPAN>Every data warehouse has the same foundational problem: keeping dimension tables in sync with operational systems. Customer records change. Orders get updated. Accounts get closed. Getting those changes into your warehouse -- correctly, reliably, and in the right order -- is the most critical pipeline pattern in any data platform. And yet, the tool most teams reach for -- MERGE INTO -- was never designed for this job. It does not understand event ordering, deduplication, or delete propagation. Engineers build all of that themselves, and what starts as "just merge the new records" spirals into 150+ lines of procedural SQL. Every team reimplements this pattern. Some get it subtly wrong. The failures are silent -- you find out weeks later when a dashboard is wrong or an audit fails.</SPAN></P> <P><SPAN><A href="https://docs.databricks.com/aws/en/ldp/dbsql/streaming#apply-change-data-capture-cdc-with-auto-cdc-flows" target="_self">AUTO CDC</A> is a new declarative SQL API in <A href="https://docs.databricks.com/aws/en/ldp/dbsql/dbsql-for-ldp" target="_self">Databricks SQL</A> that handles all of this for you. You declare your keys, your ordering column, and your SCD type -- and AUTO CDC handles dedup, ordering, deletes, checkpointing, and history tracking automatically. What used to take 150+ lines of fragile MERGE logic becomes about 7 lines of SQL. AUTO CDC is available now in Beta -- you can try it today in the Databricks SQL Query Editor, running directly on your SQL warehouse.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot 2026-04-23 at 17.23.21.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/26365i6C621CB1E6953CC0/image-size/large?v=v2&amp;px=999" role="button" title="Screenshot 2026-04-23 at 17.23.21.png" alt="Screenshot 2026-04-23 at 17.23.21.png" /></span></P> <P> <SPAN>In this post, we will walk through the pain points that every warehouse engineer knows too well, show how AUTO CDC eliminates them, and build a working SCD Type 2 pipeline from scratch.</SPAN></P> <H2><STRONG>The Challenge: MERGE INTO creates Technical Debt</STRONG></H2> <P><SPAN>If you have built <A href="https://docs.databricks.com/aws/en/ldp/what-is-change-data-capture" target="_self">Change Data Capture</A> (CDC) pipelines with MERGE INTO, these scenarios will feel familiar:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Multiple updates to the same key in one batch</STRONG><SPAN> -- Your source sends two updates for </SPAN><SPAN>customer_id = 42</SPAN><SPAN> in the same load. Which one wins? Without explicit dedup logic, the answer is "whichever the query optimizer picks" -- and that is not deterministic.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Out-of-order records across batches</STRONG><SPAN> -- A record with </SPAN><SPAN>updated_at = 10:05 AM</SPAN><SPAN> arrives in batch 3, but the record with </SPAN><SPAN>updated_at = 10:02 AM</SPAN><SPAN> arrives in batch 4. Your MERGE happily overwrites the newer data with the older data. Silently.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Delete handling</STRONG><SPAN> -- You process a delete for </SPAN><SPAN>customer_id = 99</SPAN><SPAN>, then a stale update for the same customer arrives in the next batch. Your MERGE re-inserts the record. The customer is back from the dead.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>SCD Type 2 on top of all that</STRONG><SPAN> -- Now layer in historical tracking. You need checksums to detect real changes, CTEs to stage the merge, </SPAN><SPAN>LAG</SPAN><SPAN>/</SPAN><SPAN>LEAD</SPAN><SPAN> window functions to compute effective dates, and a </SPAN><SPAN>MERGE</SPAN><SPAN> statement that handles matched-updates, matched-closes, and not-matched-inserts all at once.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Error recovery</STRONG><SPAN> -- Something fails mid-pipeline. The recovery plan? Truncate the target table and rerun from the beginning. Hope it finishes this time.</SPAN></LI> </UL> <P><SPAN>Here is a simplified glimpse of what that procedural approach looks like, and this is the short version!</SPAN></P> <P><STRONG>Procedural SCD Type 2: The "short" version (~30 lines, real-world is 150+)</STRONG></P> <P><STRONG>Step 1: Stage incoming data, dedup by key, keep latest</STRONG></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">CREATE OR REPLACE TEMP VIEW staged AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY updated_at DESC ) AS rn FROM customer_updates ) WHERE rn = 1; </LI-CODE></TD> </TR> </TBODY> </TABLE> <P><STRONG><SPAN>Step 2: Compute checksums to detect real changes</SPAN></STRONG></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">CREATE OR REPLACE TEMP VIEW changes AS SELECT s.*, md5(concat_ws('|', s.name, s.email, s.city)) AS new_hash FROM staged s JOIN customer_dim d ON s.customer_id = d.customer_id AND d.is_current = true WHERE md5(concat_ws('|', d.name, d.email, d.city)) != md5(concat_ws('|', s.name, s.email, s.city)); </LI-CODE></TD> </TR> </TBODY> </TABLE> <P><STRONG>Step 3: Close existing records + insert new versions</STRONG></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">MERGE INTO customer_dim AS target USING ( SELECT customer_id, name, email, city, updated_at, true AS is_current, updated_at AS effective_from, NULL AS effective_to FROM changes UNION ALL SELECT d.customer_id, d.name, d.email, d.city, d.updated_at, false, d.effective_from, c.updated_at FROM customer_dim d JOIN changes c ON d.customer_id = c.customer_id AND d.is_current = true ) AS source ON target.customer_id = source.customer_id AND target.is_current = source.is_current WHEN MATCHED AND source.is_current = false THEN UPDATE SET is_current = false, effective_to = source.effective_to WHEN NOT MATCHED THEN INSERT *;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>And this still does not handle out-of-order records, deletes, or error recovery. Every one of those requires more code, more edge cases, and more testing.</SPAN></P> <H3><STRONG>How AUTO CDC Works</STRONG></H3> <P>Instead of writing procedural logic to handle every edge case, you declare what you want and <A href="https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-streaming-table-auto-cdc" target="_self">AUTO CDC</A> figures out how:</P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Out-of-order records</STRONG><SPAN> -- </SPAN><SPAN>SEQUENCE BY</SPAN><SPAN> ensures records are processed in the correct order, regardless of when they arrive. Late-arriving records are slotted into the right position automatically.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Delete propagation</STRONG><SPAN> -- </SPAN><SPAN>APPLY AS DELETE WHEN</SPAN><SPAN> lets you declaratively specify which records represent deletes. No more ghost records.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>SCD Type 1 &amp; Type 2</STRONG><SPAN> -- A single keyword: </SPAN><SPAN>STORED AS SCD TYPE 1</SPAN><SPAN> keeps only the latest version. </SPAN><SPAN>STORED AS SCD TYPE 2</SPAN><SPAN> maintains full history with </SPAN><SPAN>__START_AT</SPAN><SPAN> and </SPAN><SPAN>__END_AT</SPAN><SPAN> columns -- no checksums, no window functions, no CTEs.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Partial updates -- IGNORE NULL UPDATES prevents null values in sparse CDC records from overwriting existing data -- a common headache with Debezium and other CDC connectors that send partial rows.</STRONG></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Selective history tracking -- TRACK HISTORY ON lets you control which columns trigger a new SCD Type 2 history row. Changes to untracked columns (e.g., last_login_at) update the current row in place instead of creating unnecessary version bloat.</STRONG></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Error recovery</STRONG><SPAN> -- Built-in checkpointing means the pipeline picks up exactly where it left off. No truncate-and-rerun.</SPAN></LI> </UL> <P><SPAN>Here is how those pain points map across approaches:</SPAN></P> <TABLE> <TBODY> <TR> <TD> <P><STRONG>Pain Point</STRONG></P> </TD> <TD> <P><STRONG>Procedural MERGE</STRONG></P> </TD> <TD> <P><STRONG>AUTO CDC</STRONG></P> </TD> </TR> <TR> <TD> <P><SPAN>Dedup / multiple updates per key</SPAN></P> </TD> <TD> <P><SPAN>ROW_NUMBER() + manual dedup CTE</SPAN></P> </TD> <TD> <P><SPAN>Handled automatically</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Out-of-order records</SPAN></P> </TD> <TD> <P><SPAN>Complex CASE logic or ignored entirely</SPAN></P> </TD> <TD> <P><SPAN>SEQUENCE BY</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Delete handling</SPAN></P> </TD> <TD> <P><SPAN>Manual MERGE with delete flags</SPAN></P> </TD> <TD> <P><SPAN>APPLY AS DELETE WHEN</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>SCD Type 2 history</SPAN></P> </TD> <TD> <P><SPAN>Checksums + LAG/LEAD + multi-arm MERGE</SPAN></P> </TD> <TD> <P><SPAN>STORED AS SCD TYPE 2</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Error recovery</SPAN></P> </TD> <TD> <P><SPAN>Truncate and rerun</SPAN></P> </TD> <TD> <P><SPAN>Automatic checkpointing</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Partial / sparse updates</SPAN></P> </TD> <TD> <P><SPAN>COALESCE() on every column</SPAN></P> </TD> <TD> <P><SPAN>IGNORE NULL UPDATES</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Selective history tracking</SPAN></P> </TD> <TD> <P><SPAN>Custom checksums on selected columns</SPAN></P> </TD> <TD> <P><SPAN>TRACK HISTORY ON</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Lines of code</SPAN></P> </TD> <TD> <P><SPAN>150+</SPAN></P> </TD> <TD> <P><SPAN>~7</SPAN></P> </TD> </TR> </TBODY> </TABLE> <H2>&nbsp;</H2> <H2><STRONG>AUTO CDC Syntax at a Glance</STRONG></H2> <P><SPAN>The syntax is built around a few intuitive building blocks:</SPAN></P> <UL> <LI><STRONG style="color: #1b3139; font-family: inherit;">`KEYS`</STRONG><SPAN> -- The business key(s) that uniquely identify a record</SPAN></LI> <LI><STRONG>`SEQUENCE BY`</STRONG><SPAN> -- The column that determines record order (timestamp, sequence number, etc.)</SPAN></LI> <LI><STRONG style="color: #1b3139; font-family: inherit;">`STORED AS SCD TYPE 1|2` </STRONG>-- Whether to keep only the latest version or full history (default: SCD Type 1)</LI> <LI><STRONG style="color: #1b3139; font-family: inherit;">`APPLY AS DELETE WHEN`</STRONG><SPAN> -- A condition that marks records as deletes</SPAN></LI> </UL> <P><SPAN>SCD Type 1 is the default, and that is a deliberate design choice. The most common CDC problem is deceptively simple: keep a "golden record" table that always reflects the latest state of each entity. Think of a customer dimension that powers your dashboards and reports -- you do not need every historical version, you just need the current truth. But as we saw earlier, even this "simple" case gets complicated fast with MERGE INTO when you factor in duplicate keys, out-of-order arrivals, and delete handling. AUTO CDC makes the common case effortless.</SPAN></P> <P><SPAN>Here is what SCD Type 1 looks like -- keep only the latest version of each booking:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">CREATE OR REFRESH STREAMING TABLE bookings_current FLOW AUTO CDC FROM STREAM samples.wanderbricks.booking_updates KEYS (booking_id) SEQUENCE BY updated_at STORED AS SCD TYPE 1;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>Seven lines. No dedup logic. No out-of-order handling. No error recovery code. It just works.&nbsp;</SPAN></P> <P><SPAN>But sometimes the current state is not enough. Regulatory audits need to know what a customer's address was on a specific date. Finance teams need to reconstruct metrics as they existed at quarter-close. Fraud investigations need to trace every change to an account. That is when you upgrade from the default to SCD Type 2 -- and the only change is one line:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">CREATE OR REFRESH STREAMING TABLE bookings_history FLOW AUTO CDC FROM STREAM samples.wanderbricks.booking_updates KEYS (booking_id) SEQUENCE BY updated_at STORED AS SCD TYPE 2;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>The only difference from the default SCD Type 1 is the last line. AUTO CDC automatically adds __START_AT and __END_AT columns to track when each version of a record was active. The current version has __END_AT = NULL. You start with Type 1 as the default, and when the business need arises, upgrading to full history is a one-line change -- not a rewrite.</SPAN></P> <P><SPAN>Want to avoid version bloat? Use TRACK HISTORY ON to control which columns trigger new history rows. For example, if changes to city should not create a new version:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">CREATE OR REFRESH STREAMING TABLE bookings_history_selective FLOW AUTO CDC FROM STREAM samples.wanderbricks.booking_updates KEYS (booking_id) SEQUENCE BY updated_at STORED AS SCD TYPE 2 TRACK HISTORY ON * EXCEPT (city);</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>With this configuration, changes to city update the current row in place, while changes to any other column create a new historical version. This keeps your history table focused on the changes that actually matter to your business.</SPAN></P> <P>&nbsp;</P> <H2><STRONG>Building an SCD Type 2 Model in your Data Warehouse</STRONG></H2> <P><SPAN>Let's build a complete example from scratch using the Databricks SQL Query Editor connected to a SQL warehouse. Every step below runs directly in the Query Editor -- no notebooks, no pipeline UI, no Python. We will create a customer table with Change Data Feed (CDF) enabled, simulate real-world changes -- inserts, updates, and deletes -- and watch AUTO CDC handle all of it declaratively.</SPAN></P> <P><STRONG>Step 1: Create a source table with Change Data Feed enabled</STRONG></P> <P><SPAN>Change Data Feed captures row-level changes on a Delta table. This is our CDC source.</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">CREATE OR REPLACE TABLE mycatalog.default.customers ( customer_id INT, first_name STRING, last_name STRING, email STRING, city STRING ) TBLPROPERTIES (delta.enableChangeDataFeed = true);</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><STRONG>Step 2: Insert initial records</STRONG></P> <P><SPAN>Load 10 customers as our starting dataset:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">INSERT INTO mycatalog.default.customers VALUES (1, 'Alice', 'Johnson', 'alice.johnson@example.com', 'Seattle'), (2, 'Bob', 'Smith', 'bob.smith@example.com', 'Portland'), (3, 'Carol', 'Williams', 'carol.williams@example.com', 'Denver'), (4, 'David', 'Brown', 'david.brown@example.com', 'Austin'), (5, 'Eve', 'Davis', 'eve.davis@example.com', 'Chicago'), (6, 'Frank', 'Miller', 'frank.miller@example.com', 'Boston'), (7, 'Grace', 'Wilson', 'grace.wilson@example.com', 'Miami'), (8, 'Hank', 'Moore', 'hank.moore@example.com', 'Atlanta'), (9, 'Ivy', 'Taylor', 'ivy.taylor@example.com', 'Dallas'), (10, 'Jack', 'Anderson', 'jack.anderson@example.com', 'Phoenix');</LI-CODE></TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P><STRONG>Step 3: Create the SCD Type 2 streaming table with AUTO CDC</STRONG></P> <P><SPAN>This is the core of the entire pipeline -- and it is just one statement:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">CREATE OR REFRESH STREAMING TABLE mycatalog.default.customers_scd2 SCHEDULE REFRESH EVERY 1 DAY FLOW AUTO CDC FROM STREAM mycatalog.default.customers WITH (readChangeFeed = true) KEYS (customer_id) APPLY AS DELETE WHEN _change_type = 'delete' SEQUENCE BY _commit_timestamp COLUMNS * EXCEPT (_change_type, _commit_version, _commit_timestamp) STORED AS SCD TYPE 2;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>Let's break down what each line does:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>`FROM STREAM ... WITH (readChangeFeed = true)`</STRONG><SPAN> -- Reads the Change Data Feed from the source table, which provides row-level insert, update, and delete events.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>`KEYS (customer_id)`</STRONG><SPAN> -- Identifies each customer uniquely.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>`APPLY AS DELETE WHEN _change_type = 'delete'`</STRONG><SPAN> -- When CDF reports a delete, AUTO CDC closes the record's history (sets </SPAN><SPAN>__END_AT</SPAN><SPAN>) rather than leaving a ghost row.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>`SEQUENCE BY _commit_timestamp`</STRONG><SPAN> -- Uses the CDF commit timestamp to order changes correctly -- even if they arrive out of sequence.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>`COLUMNS * EXCEPT (...)`</STRONG><SPAN> -- Strips out the CDF metadata columns so the target table contains only business data.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>`STORED AS SCD TYPE 2`</STRONG><SPAN> -- Maintains full history. Every change creates a new version with </SPAN><SPAN>__START_AT</SPAN><SPAN> and </SPAN><SPAN>__END_AT</SPAN><SPAN> tracking. If you want to only maintain the current version, simply swap this out for `STORED AS SCD TYPE 1.`</SPAN></LI> </UL> <P><STRONG>Step 4: Simulate real-world changes</STRONG></P> <P><SPAN>Now let's simulate what happens in a production system -- new customers sign up, existing customers update their information, and some customers are removed.</SPAN></P> <P><SPAN>Insert 2 new customers:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">INSERT INTO mycatalog.default.customers VALUES (11, 'Karen', 'Thomas', 'karen.thomas@example.com', 'San Francisco'), (12, 'Leo', 'Jackson', 'leo.jackson@example.com', 'New York');</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>Update 2 existing customers -- Alice moved to Los Angeles, Bob changed his email:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">UPDATE mycatalog.default.customers SET city = 'Los Angeles' WHERE customer_id = 1; UPDATE mycatalog.default.customers SET email = 'bob.updated@example.com' WHERE customer_id = 2;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>Delete 2 customers:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">DELETE FROM mycatalog.default.customers WHERE customer_id = 9; DELETE FROM mycatalog.default.customers WHERE customer_id = 10;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><STRONG>Step 5: Refresh and query the results</STRONG></P> <P><SPAN>Refresh the streaming table to process all the changes:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">REFRESH STREAMING TABLE mycatalog.default.customers_scd2;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P><SPAN>Now query the SCD Type 2 table:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%"><LI-CODE lang="markup">SELECT * FROM mycatalog.default.customers_scd2;</LI-CODE></TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="CarlosR_1-1776958918597.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/26361iB6449AFF40FCC2A9/image-size/large?v=v2&amp;px=999" role="button" title="CarlosR_1-1776958918597.png" alt="CarlosR_1-1776958918597.png" /></span></P> <P><LI-WRAPPER></LI-WRAPPER></P> <P><SPAN>Here is what you will see in the results:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Customers 3-8</STRONG><SPAN> -- Unchanged. One row each with </SPAN><SPAN>__END_AT = NULL</SPAN><SPAN> (current and active).</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Customer 1 (Alice)</STRONG><SPAN> -- Two rows. The original Seattle version has a </SPAN><SPAN>__END_AT</SPAN><SPAN> timestamp marking when it was superseded. The new Los Angeles version has </SPAN><SPAN>__END_AT = NULL</SPAN><SPAN> -- she is current.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Customer 2 (Bob)</STRONG><SPAN> -- Two rows. Same pattern -- the old email version is closed, the new email version is current.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Customers 9 and 10 (Ivy and Jack)</STRONG><SPAN> -- Their records have </SPAN><SPAN>__END_AT</SPAN><SPAN> set to the deletion timestamp. They are closed -- not deleted from the table, but historically tracked as removed.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Customers 11 and 12 (Karen and Leo)</STRONG><SPAN> -- One row each with </SPAN><SPAN>__END_AT = NULL</SPAN><SPAN>. New additions, fully tracked from their first appearance.</SPAN></LI> </UL> <P><SPAN>No dedup logic. No checksums. No window functions. No multi-arm MERGE. AUTO CDC handled all of it -- inserts, updates, deletes, and history tracking -- from a single declarative statement.</SPAN></P> <H2><SPAN>When to Use AUTO CDC</SPAN></H2> <P><SPAN>AUTO CDC is a natural fit any time you are building dimensional tables from change data:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>CDC feeds from operational databases</STRONG><SPAN> -- Whether you are consuming Change Data Feed from Delta tables, Debezium streams, or any source that provides row-level changes, AUTO CDC handles the complexity of turning those changes into clean dimensions.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Replacing existing MERGE-based SCD pipelines</STRONG><SPAN> -- If you have existing pipelines with the 150-line MERGE pattern, AUTO CDC is a straightforward modernization path. Same result, a fraction of the code, and better error recovery.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Out-of-order or late-arriving data</STRONG><SPAN> -- If your source data does not arrive in perfect chronological order -- and it rarely does in production -- AUTO CDC's </SPAN><SPAN>SEQUENCE BY</SPAN><SPAN> handles this automatically instead of silently corrupting your dimensions.</SPAN></LI> </UL> <P><SPAN>Note: What about sources without a change feed? Many legacy databases and JDBC sources only provide periodic snapshots, not row-level change events. AUTO CDC FROM SNAPSHOT -- which automatically infers inserts, updates, and deletes by comparing successive snapshots -- is available today in Spark Declarative Pipelines via Python. SQL support is not yet available but is planned for a future release.</SPAN></P> <H3><STRONG>Requirements</STRONG></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Compute: </STRONG>Serverless SQL Warehouse, or SDP Pro / Advanced edition</LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Runtime</STRONG><SPAN>: Databricks Runtime 17.3 or later</SPAN></LI> </UL> <H2><STRONG>Get Started</STRONG></H2> <P><SPAN>AUTO CDC is available today in Beta. Here is how to get started:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN><A href="https://docs.databricks.com/aws/en/sql/get-started/sql-etl-tutorial" target="_self">Incremental ETL tutorial</A>&nbsp;-- Tutorial on how to run ETL inside Databricks SQL</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://docs.databricks.com/aws/en/ldp/dbsql/streaming#apply-change-data-capture-cdc-with-auto-cdc-flows" target="_blank" rel="noopener"><SPAN>AUTO CDC in Databricks SQL Documentation</SPAN></A><SPAN> -- Full syntax reference, parameters, and examples</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://docs.databricks.com/aws/en/ldp/dbsql/streaming" target="_blank" rel="noopener"><SPAN>Streaming Tables in Databricks SQL</SPAN></A><SPAN> -- Background on streaming tables and flows in DBSQL</SPAN></LI> </UL> <P><SPAN>Try it with your own data -- swap out the mycatalog.default.customers table for one of your CDC sources, and see how much procedural code you can retire. Take an existing MERGE-based SCD pipeline, rewrite it with AUTO CDC, and compare the results. We think you will be surprised how much complexity disappears.</SPAN></P> <P><SPAN>If you have feedback or questions, drop them in the comments below.</SPAN></P> <P>&nbsp;</P> Fri, 24 Apr 2026 14:14:54 GMT CarlosR 2026-04-24T14:14:54Z https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/ba-p/155355 <P><A href="https://docs.databricks.com/aws/en/ldp/what-is-change-data-capture" target="_self">https://docs.databricks.com/aws/en/ldp/what-is-change-data-capture</A>&nbsp;<A href="https://docs.databricks.com/aws/en/ldp/what-is-change-data-capture" target="_self">https://docs.databricks.com/aws/en/ldp/what-is-change-data-capture</A>&nbsp;<span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot 2026-04-23 at 16.13.06.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/26363iE2DCA61E7E8E1DCA/image-size/large?v=v2&amp;px=999" role="button" title="Screenshot 2026-04-23 at 16.13.06.png" alt="Screenshot 2026-04-23 at 16.13.06.png" /></span></P> <P><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="6">Time to stop writing 150+ lines of fragile<SPAN>&nbsp;</SPAN></SPAN><CODE class="appsElementsGenerativeaiAstInlineCode">MERGE INTO</CODE><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="8"><SPAN>&nbsp;</SPAN>SQL for Change Data Capture (CDC) workloads that don't handle out-of-order records or deletes reliably. Introducing AUTO CDC in Databricks SQL, the new declarative API that simplifies complex SCD Type 1 and Type 2 logic into just 7 lines of code. Read how you can eliminate technical debt, automatically manage data history, ordering, and error recovery, and modernize your data warehouse ETL today</SPAN><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="9">.</SPAN></P> Fri, 24 Apr 2026 14:14:54 GMT https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/ba-p/155355 CarlosR 2026-04-24T14:14:54Z https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155469#M1024 <P>With standard tables we have the possibility to use columns with identity (sequence like) value. From the modeling perspective surrogate keys are considered as a good practice. How autocdc handles surrogate keys? You probably don't want your BI tool of choice join the facts and dimension tables on business keys as especially in SCD2 they are not unique.</P> Fri, 24 Apr 2026 21:11:03 GMT https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155469#M1024 pepco-personal 2026-04-24T21:11:03Z https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155474#M1025 <P>Thank you for such an informative post on one of my favourite topics—<STRONG>MERGE INTO</STRONG>.</P><P>I completely agree that <STRONG>AUTO CDC</STRONG> significantly simplifies handling both SCD Type 1 and Type 2 patterns. The side-by-side comparison between a manual MERGE INTO implementation and AUTO CDC makes the difference very clear. From a developer’s perspective, AUTO CDC not only expedites the build phase but also makes long-term maintenance much easier.</P><P>That said, while I agree with most of the advantages highlighted, I’m not entirely convinced that <EM>“Error recovery”</EM> is a distinct benefit of AUTO CDC over a manual MERGE INTO approach. My understanding is that MERGE INTO operations are atomic—either the entire transaction succeeds or it fails without partial updates. Based on that, I would expect error recovery characteristics to be similar.</P><P>I’d be interested to hear your thoughts on this in case I’m overlooking something.</P><P>Thanks again for sharing such valuable insights.</P> Sat, 25 Apr 2026 01:43:42 GMT https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155474#M1025 Surya2 2026-04-25T01:43:42Z https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155578#M1027 <P>Thanks for commenting both!</P> <P>We're working on adding identity column support for AUTO CDC! As of right now, we recommend adding the surrogate key in the source table before AUTO CDC.&nbsp;<BR /><BR />You're correct that MERGE INTO itself is atomic, but if a batch is incorrect but still successfully processed (for example, duplicates, out-of-order updates, or partial data), a manual MERGE can leave the table in a wrong state—and recovery often means truncating and rerunning. AUTO CDC reduces that risk by handling ordering, deduplication, and idempotency upfront, and then uses checkpointing to safely resume if something does fail.</P> Mon, 27 Apr 2026 15:32:39 GMT https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155578#M1027 shanelleroman97 2026-04-27T15:32:39Z https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155629#M1029 <P>Thank you&nbsp;<a href="https://community.databricks.com/t5/user/viewprofilepage/user-id/226548">@shanelleroman97</a>&nbsp;for the clarifications.</P> Tue, 28 Apr 2026 00:09:55 GMT https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155629#M1029 Surya2 2026-04-28T00:09:55Z https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155957#M1035 <BLOCKQUOTE><HR /><SPAN>We're working on adding identity column support for AUTO CDC! As of right now, we recommend adding the surrogate key in the source table before AUTO CDC.&nbsp;</SPAN></BLOCKQUOTE><P>With regards to this, it might not be possible or even desired to add this complexity to the source table. uuid might be an alternative but the nice thing about identity is that you don't need to think about them too much + they are integers so by default have smaller footprint.</P><P>Also when we are talking about table, are there some more complex examples or some migration from complex batch processing? It's all nice to see how this works with simple tables, but often you need to join, transform, clean, aggregate data etc. before you actually write to the target table. I probably lack imagination, but then again I consider myself still the beginner.</P> Fri, 01 May 2026 17:30:48 GMT https://community.databricks.com/t5/technical-blog/from-150-lines-of-merge-into-to-7-lines-of-sql-auto-cdc-comes-to/bc-p/155957#M1035 pepco-personal 2026-05-01T17:30:48Z