Lakeflow Connect and CDC

Layer 1 from lesson 1 — plus the CDC machinery that turns a stream of changes into correct dimensional tables.

The six connector categories

From Lakeflow Connect (2026-07-27):

SaaS connectors — Salesforce, HubSpot, Jira, Workday, and more
Database connectors (CDC) — MySQL, PostgreSQL, SQL Server via change data capture
Query-based connectors — query the source directly, no CDC configuration required
File source connectors — Google Drive, SharePoint
Streaming connectors — message buses and event streams, including RabbitMQ
Community connectors — open-source, community-built

The 31 managed SaaS connectors currently listed: Aha!, Anthropic, Confluence, Microsoft Dynamics 365, GitHub, Google Ads, Google Analytics, HubSpot, Jira, Meta Ads, Monday.com, Netskope Logs, NetSuite, Outlook, Pendo, Reddit Ads, Salesforce, Salesforce Marketing Cloud, ServiceNow, SharePoint, Smartsheet, Square, Strac, TikTok Ads, Wiz Audit Logs, Workday HCM, Workday Reports, Zendesk Support, Zip, Zoho Books, Zoom Logs.

⚠️ Status varies per connector and I'm not going to guess. The connector list page carries no GA/Preview/Beta column, and the FAQ doesn't either. Per-connector status must be read off that connector's own page. If someone tells you "all Lakeflow Connect connectors are GA," ask for the link.

Two CDC architectures

Databricks now offers two shapes for database CDC (SQL Server overview, 2026-05-29):

	Standard CDC (gateway-based)	Integrated CDC
Pipelines	two — ingestion gateway + ingestion pipeline	one unified pipeline
Gateway	required; runs continuously	none
Staging	gateway-managed	`data_staging_options`; auto-created if unspecified

"The same source database configuration applies to both architectures."

So the source-side work is identical; the difference is how many moving parts you operate. Integrated CDC is the simpler shape where available.

SQL Server source requirements

Two different source mechanisms, and choosing between them is a real design decision:

	Change Tracking	Change Data Capture
Captures	"the fact that rows in a table have changed, but doesn't capture the actual operations"	"every operation on a table and contains a historical view on the changes made over time"
Primary key	required	not required
Source load	lightweight	"may impact source DB performance"
Version	SQL Server 2012+	2012 SP1 CU3+ (Enterprise pre-2016)

Setup has four mandatory tasks: verify version compatibility, configure firewall settings, create a dedicated Databricks database user with the specified privileges, and prepare the source database via the utility-objects script.

I'm deliberately not giving you the T-SQL. The exact enablement commands and grants live on linked pages I did not verify. Writing sys.sp_cdc_enable_db from memory into your production database is exactly the failure mode this course exists to prevent. Get it from the connector's own setup page.

AUTO CDC — the rename is confirmed

"The AUTO CDC APIs replace the APPLY CHANGES APIs and have the same syntax. The APPLY CHANGES APIs are still available, but Databricks recommends using the AUTO CDC APIs in their place." — CDC with pipelines (2026-07-14)

Old	Current
`APPLY CHANGES INTO`	`AUTO CDC INTO`
`apply_changes()`	`create_auto_cdc_flow()`
`apply_changes_from_snapshot()`	`create_auto_cdc_from_snapshot_flow()`

SQL

CREATE OR REFRESH STREAMING TABLE users_current;

CREATE FLOW apply_cdc AS AUTO CDC INTO users_current
FROM stream(main.cdc_tutorial.users_cdf)
KEYS (userId)
APPLY AS DELETE WHEN operation = "DELETE"
APPLY AS TRUNCATE WHEN operation = "TRUNCATE"
SEQUENCE BY sequenceNum
COLUMNS * EXCEPT (operation, sequenceNum)
STORED AS SCD TYPE 1;

Python

from pyspark import pipelines as dp
from pyspark.sql.functions import col, expr

@dp.view
def users():
  return spark.readStream.table("main.cdc_tutorial.users_cdf")

dp.create_streaming_table("users_current")

dp.create_auto_cdc_flow(
  target = "users_current",
  source = "users",
  keys = ["userId"],
  sequence_by = col("sequenceNum"),
  apply_as_deletes = expr("operation = 'DELETE'"),
  apply_as_truncates = expr("operation = 'TRUNCATE'"),
  except_column_list = ["operation", "sequenceNum"],
  stored_as_scd_type = 1)

The clauses that matter

KEYS — how rows are identified across changes. Get this wrong and everything downstream is wrong.
SEQUENCE BY — how ordering is determined. This is why lesson 3's "no order guarantee" matters. Files arrive in arbitrary order; SEQUENCE BY is how correctness is recovered. It must be a genuinely monotonic column from the source — an LSN, a version, a reliable event timestamp.
APPLY AS DELETE WHEN — which records represent deletions.
COLUMNS * EXCEPT — drop CDC bookkeeping columns from the target.

SCD Type 1 vs Type 2

Change stored_as_scd_type (or STORED AS SCD TYPE):

Type 1 — current state only. One row per key. History is overwritten.

Type 2 — full history. Databricks adds __START_AT and __END_AT; rows where __END_AT is null are current.

-- current rows only, from an SCD2 table
SELECT * FROM users_history WHERE __END_AT IS NULL;

Memorize those two column names — they're specific, testable, and you'll query them constantly.

MERGE INTO

When you're building CDC logic by hand rather than using AUTO CDC (MERGE INTO, 2026-06-18):

MERGE [ WITH SCHEMA EVOLUTION ] INTO target_table [target_alias]
USING source_table_reference [source_alias]
ON merge_condition
{ WHEN MATCHED [ AND condition ] THEN matched_action |
  WHEN NOT MATCHED [BY TARGET] [ AND condition ] THEN not_matched_action |
  WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN not_matched_by_source_action } [...]

Three things people miss:

WHEN NOT MATCHED BY TARGET is the default — plain WHEN NOT MATCHED means by target.
WHEN NOT MATCHED BY SOURCE exists, and supports only DELETE and UPDATE — no INSERT. It's how you handle rows that vanished from the source.
WITH SCHEMA EVOLUTION requires DBR 15.2+.

Also available: UPDATE SET * and INSERT * with EXCEPT (columns).

Prefer AUTO CDC over hand-written MERGE for CDC. Ordering, deletes, and SCD2 bookkeeping are easy to get subtly wrong, and AUTO CDC has already solved them.

Check yourself

Which SQL Server mechanism needs a primary key, and which is heavier on the source?
What replaced APPLY CHANGES INTO? Does the old form still work?
Which two columns does SCD Type 2 add, and how do you select current rows?
Why is SEQUENCE BY essential given lesson 3?
Which MERGE clause handles rows deleted from the source, and what actions may it take?
A vendor claims a specific connector is GA. How do you verify?

Answers

Change Tracking requires a primary key and is lightweight. Change Data Capture needs no primary key, captures every operation with history, and may impact source performance.
AUTO CDC INTO (SQL) / create_auto_cdc_flow() (Python). The old APIs are "still available," but Databricks recommends AUTO CDC.
__START_AT and __END_AT. Current rows: WHERE __END_AT IS NULL.
Because Auto Loader guarantees no processing order, regardless of detection mode. SEQUENCE BY supplies ordering from the data — an LSN, version, or reliable event timestamp.
WHEN NOT MATCHED BY SOURCE, supporting only DELETE and UPDATE.
Check that connector's own documentation page. The connector list and FAQ carry no status column.

Teaching this section

Start from the pain: show a hand-written SCD2 MERGE, let people find the ordering bug, then show AUTO CDC doing it in six lines. Motivation before syntax.
SEQUENCE BY is the callback moment — link it explicitly to lesson 3's no-ordering-guarantee. When those two click together, people genuinely understand streaming ingestion.
Model the refusal. Say out loud why you won't hand them T-SQL you haven't verified. That behavior transfers further than the content.
Timing: 35 min presented, 20 for a hands-on SCD2 build.

← PreviousSQL ingestion: read_files, streaming tables, COPY INTO Finished →Cheat sheet, lab & quiz