Skip to main content
Lakeflow Connect guide · SQL Server

SQL Server to Databricks with Lakeflow Connect

Ingest SQL Server snapshots and incremental changes into governed Unity Catalog tables without building and maintaining a custom CDC framework.

Read the Lakeflow Connect guide
Initial snapshot Incremental ingestion Unity Catalog
SQL Server replication lifecycle
Managed ingestion

Source

SQL Server

Orders · inventory · production · master data

First load

Initial snapshot

complete source state

Afterwards

Incremental changes

INSERT UPDATE DELETE

Destination

Unity Catalog streaming tables

Source-aligned Delta data ready for downstream Lakeflow transformations.

Lakeflow Connect separates source replication from downstream manufacturing transformations and business logic.

Where Lakeflow Connect fits

Use managed ingestion when SQL Server changes need to become durable Databricks data

Lakeflow Connect is strongest when you want a maintained copy of SQL Server data in Delta tables, including initial load, incremental changes, deletes, schema handling and recovery. It is not the only SQL Server access pattern.

Managed ingestion

Choose Lakeflow Connect when Databricks should own an incrementally maintained copy.

Simple batch extraction

JDBC can still be appropriate for small, low-change reference tables or exceptional sources.

Zero-copy access

Lakehouse Federation fits selected live-query use cases where copying the data adds little value.

Choose by the data access requirement

Start with the outcome, not the connector name.

Need

Durable local copy + ongoing changes

Lakeflow Connect

managed snapshot + incremental ingest

Need

Small scheduled extract

JDBC batch

simple pull where CDC adds little value

Need

Live source query without copying

Lakehouse Federation

remote query path

Choose the SQL Server change mechanism

Primary key and history requirements drive the choice.

Do you need SCD Type 2 history?

Yes

Use CDC

SCD Type 2 requires source CDC

No

Does the table have a primary key?

Yes

Change Tracking

No

CDC

Important: if Change Tracking and CDC are both enabled on a table, the SQL Server connector uses Change Tracking.

Change Tracking vs CDC

Prefer Change Tracking for keyed tables unless you need CDC history

Databricks recommends Change Tracking for tables with a primary key because it is lighter on SQL Server. CDC captures every operation and also works without a primary key, but it can place more load on the source.

Change Tracking

Requires a primary key and records which rows changed without retaining the full operation history.

Change Data Capture

Captures operation history and is required when Lakeflow Connect must publish SCD Type 2 history.

Source impact matters

Benchmark snapshot and change extraction against real ERP workload windows rather than treating CDC as free.

Replication lifecycle

The first load and the change stream are two different operational phases

The initial snapshot creates the baseline. Incremental capture then keeps the destination current. Design source capacity, retention and recovery for both phases.

1 · Snapshot

Read current source state

Large tables can create a very different SQL Server load profile from steady-state ingestion.

2 · Track changes

Protect the change window

CT or CDC retention must outlast realistic outages so changes are still available when ingestion resumes.

3 · Apply changes

Upsert and delete incrementally

Lakeflow Connect applies source changes to destination streaming tables using the configured keys and SCD mode.

4 · Reconcile

Verify freshness and completeness

Track snapshot progress, CDC lag, row counts and recovery events instead of monitoring only job success.

Standard CDC architecture

Keep capture continuous and destination refresh independently scheduled

The standard database-connector architecture uses a continuous ingestion gateway plus a separate managed ingestion pipeline. The gateway protects the source change window by staging snapshot and change data continuously.

Gateway on classic compute

It must remain running continuously and have network access to SQL Server.

Unity Catalog staging volume

Extracted snapshot and CDC data is buffered between source capture and destination application.

Scheduled serverless ingestion

The destination pipeline can run to the freshness cadence your consumers actually need.

Standard Lakeflow Connect path
2 pipelines

SQL Server

snapshot · CT / CDC

continuous

Ingestion gateway

classic compute

Staging volume

buffer snapshot and incremental changes

triggered

Ingestion pipeline

serverless compute

Streaming tables

Unity Catalog destination

YAML · standard architecture essentials
resources:
  pipelines:
    sqlserver_gateway:
      continuous: true
      gateway_definition:
        connection_name: sqlserver_connection
        gateway_storage_catalog: main
        gateway_storage_schema: ingest_staging

    sqlserver_ingestion:
      serverless: true
      ingestion_definition:
        ingestion_gateway_id: ${resources.pipelines.sqlserver_gateway.id}
        objects:
          - table:
              source_catalog: ERP
              source_schema: dbo
              source_table: ProductionOrder
              destination_catalog: main
              destination_schema: bronze
Integrated CDC pipeline update
Beta

Lakeflow Job trigger

Start one pipeline update

Stage 1

Extract

snapshot or incremental changes to staging volume

Stage 2

Apply

merge changes into destination streaming tables

Smart closure

Stop when caught up or runtime cap is reached

lag-converged
max-runtime-cap-hit

Integrated CDC · Beta

One pipeline combines extraction and application per update

Integrated CDC removes the always-on gateway from the topology. Each triggered update extracts changes, stages them and applies them before the run stops. The feature is still Beta and must be enabled for the workspace.

Single pipeline resource

The pipeline references the Unity Catalog SQL Server connection directly with connector_type: CDC.

Triggered only

Schedule recurring updates with Lakeflow Jobs. An hourly interval is a sensible starting point for many workloads.

Classic or serverless compute

Whichever compute mode you choose must have a valid network path to the primary SQL Server instance.

Preview deployment interfaces

Create Integrated CDC with the API, CLI, notebooks or Declarative Automation Bundles. UI creation is not available yet.

YAML · integrated CDC essentials
resources:
  pipelines:
    sqlserver_integrated:
      name: sqlserver-integrated-cdc
      channel: PREVIEW
      catalog: main
      schema: bronze
      ingestion_definition:
        connection_name: sqlserver_connection
        connector_type: CDC
        objects:
          - table:
              source_catalog: ERP
              source_schema: dbo
              source_table: ProductionOrder
              table_configuration:
                scd_type: SCD_TYPE_1

Connectivity

The extraction compute must be able to reach the primary SQL Server instance

Network design changes with the Lakeflow Connect architecture. Standard CDC reaches SQL Server from the classic gateway. Integrated CDC can use classic or serverless compute, so its egress path must be designed accordingly.

SQL Server network path

Private and on-prem sources need explicit connectivity from the compute plane that performs extraction.

SQL Server source

on-prem · Azure VM · Azure SQL · Managed Instance

primary instance required

Network path

VNet peering
ExpressRoute
VPN
Public endpoint

Use the path appropriate to your security architecture and compute mode.

Standard CDC

Classic gateway

workspace VNet path to source

Integrated CDC

Classic or serverless

serverless requires configured serverless networking

New database connector pipelines validate the SQL Server TLS certificate. If the server uses a private CA, add the CA certificate to the Unity Catalog connection rather than disabling validation in production.

Schema and row history

Know which source changes are automatic and which require a reset

Lakeflow Connect handles several common schema changes automatically, but not every DDL change can be applied incrementally. Full refreshes also have consequences when history tracking is enabled.

New columns

Can be picked up automatically, unless you opt out or use explicit column selection.

Renames and type changes

Require a full refresh of the affected target table.

SCD Type 2

Preserves row versions during normal ingestion, but a full refresh replaces the table and restarts history from that point.

Source change → connector behaviour

Plan the exceptional paths before production.

New column

Automatic on next ingestion

Deleted column

Marked inactive in destination

Column rename

Full refresh required

Data type change

Full refresh required

Source table deleted

Destination remains until removed manually

CT / CDC retention missed

Full refresh affected tables

Ingestion health · ERP SQL Server
Healthy

Snapshot progress

82%

ETA 14m

CDC discovery lag

38 sec

source commit to gateway event

Upserted

18,420

latest observation window

Deleted

214

latest observation window

Gateway event log

snapshot progress · rows · bytes · CDC latency · errors · liveness

Illustrative values. Use event-log metrics and reconciliation controls from your own source and pipeline.

Operations and recovery

Monitor whether the data is caught up, not only whether the pipeline is green

Database ingestion can report a healthy process while the destination is still behind the source. Treat freshness, snapshot progress, retention exposure and reconciliation as operational KPIs.

Observe progress per table

Gateway event logs expose row and byte counters, snapshot progress and CDC discovery latency.

Reconcile business keys and control totals

Compare source and destination counts or high-water marks so silent completeness issues are visible.

Plan full refresh recovery

Use auto full refresh and refresh windows deliberately, especially where a reset can affect source load or SCD history.

Read Integrated CDC completion reasons

A triggered update reports whether it caught up with the source or stopped at its runtime cap so the next scheduled run can continue.

Common mistakes

Avoid treating managed ingestion as a zero-design connector

Lakeflow Connect removes connector code, but source configuration, network reachability, retention, keys and recovery still determine whether ingestion is reliable.

Avoid

Enabling CDC on every keyed table by default

Better default

Use Change Tracking for keyed tables unless CDC history is required

Avoid

Stopping the standard ingestion gateway between loads

Better default

Keep the gateway continuous and schedule only the ingestion pipeline

Avoid

Sizing only for steady-state changes

Better default

Benchmark the initial snapshot against production SQL Server load

Avoid

Assuming schema changes are always automatic

Better default

Plan full refreshes for renames and data type changes

Avoid

Treating a green pipeline as proof of freshness

Better default

Monitor lag, snapshot progress and reconciliation controls

Avoid

Choosing Integrated CDC only because it uses one pipeline

Better default

Accept the Beta support model deliberately before production use

How Food For Analytics implements it

Titan uses Lakeflow Connect as the SQL Server replication boundary

We keep source replication separate from manufacturing transformations. Lakeflow Connect lands source-aligned SQL Server data first; Titan then adds identities, time context, conformance and business data products downstream.

SQL Server source

Capture source evidence

Use Change Tracking or CDC according to keys, history and source-load requirements.

ERPordersinventory

Titan on Azure Databricks

Land a governed source copy

Lakeflow Connect manages snapshot, incremental changes, staging and destination streaming tables.

Unity Catalog connection
Lakeflow Connect
staging
streaming tables

Downstream Titan model

Add manufacturing context later

Lakeflow pipelines build the contextualized, governed data products used by reporting, analytics and Ask Titan.

identitytimebusiness logic
Explore Titan

Food manufacturing example

From an on-prem SQL Server ERP to governed manufacturing data

A food manufacturer runs production, inventory, sales and costing processes in an on-prem SQL Server ERP. These tables do not all need the same change capture strategy.

Example manufacturer

One ERP database with different data behaviours

Operational tables mainly need the current state. Selected financial or historical tables need the actual sequence of changes.

Production orders
Inventory lots
Sales order lines
Cost records

Capture strategy

Choose per table

Do not choose one replication method for the entire ERP database.

Production orders

Current order and production status

Change Tracking

Inventory lots

Current quantity by SKU, batch and location

Change Tracking

Sales order lines

Current customer demand and fulfilment status

Change Tracking

Cost records

Preserve changes to the historical cost basis

CDC

Capture only what the downstream use case needs

Lakeflow Connect handles source replication. Product, batch, order and other manufacturing context are added downstream in Titan.

Illustrative example. The capture method depends on source keys, history requirements and the SQL Server configuration.

FAQ

Frequently asked questions

Practical answers about SQL Server ingestion with Databricks Lakeflow Connect.

Should I use Change Tracking or CDC for SQL Server?

Databricks recommends Change Tracking for tables with a primary key because it is lighter on the source. Use CDC when a table has no primary key or when you need SCD Type 2 history. If both Change Tracking and CDC are enabled, the connector uses Change Tracking.

What is the difference between standard CDC and Integrated CDC in Lakeflow Connect?

Standard CDC uses a continuously running classic-compute ingestion gateway plus a separate serverless ingestion pipeline. Integrated CDC is a Beta architecture that combines extraction and application into one triggered pipeline update.

Can Lakeflow Connect ingest on-premises SQL Server?

Yes. The SQL Server connector supports on-premises sources when the extraction compute has sufficient network connectivity, for example through Azure ExpressRoute or VPN.

Can Lakeflow Connect preserve SCD Type 2 history?

Yes. SCD Type 2 is supported, but it requires SQL Server CDC on the source. A full refresh replaces the target table and removes the previously tracked row versions.

What happens when the SQL Server schema changes?

New columns can be ingested automatically. Deleted columns are marked inactive in the destination. Column renames and data type changes require a full refresh of the affected target table.

Can I stop the standard ingestion gateway between scheduled loads?

No. The gateway must run continuously so source changes are captured before the SQL Server change-retention window expires. If changes are lost, affected tables require a full refresh.

Should I use Lakeflow Connect or Lakehouse Federation for SQL Server?

Use Lakeflow Connect when Databricks should maintain a local Delta copy for analytics and downstream data products. Lakehouse Federation is better suited to selected zero-copy queries where data should remain in SQL Server.

Practical next step

Design SQL Server ingestion around source behaviour and recovery

We can review Change Tracking versus CDC, network reachability, snapshot load, retention and your target data-product architecture before you deploy Lakeflow Connect.