Ingest database changes with change data capture
Use a change data capture (CDC) connector to ingest the changes from a relational database into Unity Catalog tables. This page builds the standard, gateway-based CDC architecture with the SQL Server connector. The MySQL and PostgreSQL connectors follow the same workflow, but their source preparation and authentication steps differ, so follow the source setup page for your database. MySQL, Oracle, and SQL Server also offer integrated CDC pipelines, which run extraction and application in one pipeline and don't use a separate gateway. You create SQL Server integrated pipelines with the API, the Databricks CLI, notebooks, or Declarative Automation Bundles. For SQL Server and Oracle, contact your Databricks account team to enable integrated CDC for the workspace. See Create an integrated CDC pipeline for SQL Server.
How the pipeline works
A CDC pipeline has three parts:
- Ingestion gateway: Runs continuously on classic compute in your workspace network. It reads the source database and writes the changes to a Unity Catalog staging volume.
- Ingestion pipeline: Runs on serverless compute on the schedule you set. It applies the staged changes to the destination tables.
- Destination tables: The streaming tables that the ingestion pipeline writes.
Because the gateway captures changes continuously, the ingestion pipeline can run on any schedule without losing changes. The gateway must keep running for this to work. See Troubleshooting.
Requirements
You need the following:
- Serverless compute enabled for your workspace. See Serverless compute requirements.
- A supported source database that is configured for CDC. See the source setup page for your database, such as Configure Microsoft SQL Server for ingestion into Databricks.
- A network path from classic compute in your workspace to the source database. See Network connectivity.
- Permission to create a Unity Catalog connection with
CREATE CONNECTIONon the metastore, orUSE CONNECTIONon an existing connection. - Unrestricted permissions to create clusters, or a custom compute policy. A custom policy is available only through the API. See Requirements.
- Destination privileges:
USE CATALOGon the destination catalog, andUSE SCHEMA,CREATE TABLE, andCREATE VOLUMEon an existing destination schema, orCREATE SCHEMAon the destination catalog. - A catalog and schema for the gateway's staging location. The staging catalog can't be a foreign catalog.
Choose an authentication method that your database supports. The following steps use a username and password for SQL Server.
Create the CDC pipeline
Each step after Open Data Ingestion matches a page in the ingestion wizard.
Open Data Ingestion
In the sidebar of the Databricks workspace, click Data Ingestion. On the Add data page, under Databricks connectors, click your database. This example uses SQL Server.
Select a connection
On the Connection page of the ingestion wizard, select the connection that stores your source credentials. If none exists and you have CREATE CONNECTION on the metastore, click Create connection, enter the authentication details, and then click Create connection. Click Next.
Configure the pipeline and gateway
On the Ingestion setup page, configure the pipeline and gateway:
- Enter a unique name for the ingestion pipeline.
- Select a catalog and a schema for the event logs.
- (Optional) Set Auto full refresh for all tables to On. When it's on, the pipeline tries to fix issues such as log cleanup events and some schema changes by fully refreshing the affected table. A full refresh erases the history of any table that uses history tracking.
- Enter a unique name for the ingestion gateway.
- Select a catalog and a schema for the Staging location. Databricks creates a volume there to stage the extracted data.
Click Create pipeline and continue.
Select the source tables
On the Source page, expand the source database and schema, and then select the tables to ingest. Optionally, open the Settings tab to set a Destination name or change the History tracking setting for each table. Click Next, and then click Save and continue.
Select the destination
On the Destination page, select a catalog and a schema to load the data into, and then click Save and continue.
Validate the source database
On the Database setup page, click Validate. Validation reports any missing source configuration. To see what to fix, click Complete configuration. When validation passes, click Next. You can also click Skip validation. If you skip validation, the pipeline can fail later for the same reasons.
Set a schedule and run the pipeline
On the Schedules and notifications page, optionally click Create schedule and set how often to refresh the destination tables. Optionally, click Add notification to send email on success or failure. Then click Save and run pipeline.
Don't stop the gateway manually. It must run continuously so that it captures changes before the source truncates its change logs. The schedule controls only the ingestion pipeline.
Verify the pipeline
Confirm that the pipeline ingests data and that the gateway keeps running:
- In Jobs & Pipelines, open the ingestion pipeline.
- Confirm that the run completed and that the selected tables show records processed. The list view shows the number of records as the pipeline ingests them. To see the Upserted records and Deleted records columns, use the column configuration control in the list view.
- Confirm that the gateway pipeline is still running.
Troubleshooting
Use these entries when validation fails, when the gateway can't reach the source, or when changes are missing after the gateway stops.
Database validation fails
Click Complete configuration to see what the source is missing. Common causes include CDC or change tracking that isn't enabled on the database or the table, and a connection user without the permissions that the connector needs. For the checks that the SQL Server connector runs, see Troubleshoot SQL Server ingestion.
The gateway can't reach the source database
The gateway runs on classic compute in your workspace network. Check the routing, private connectivity, firewall rules, and the database port between the workspace network and the source before you change pipeline settings.
The gateway was stopped and changes are missing
Restarting the gateway doesn't recover changes that the source has already removed from its change log. If the gateway was stopped long enough for the source to remove changes, run a full refresh of the affected tables after the gateway is healthy. Stopping and restarting the gateway also reprovisions its compute, which lengthens startup. If you need to troubleshoot the gateway, see Troubleshoot SQL Server ingestion or contact Databricks Support.
Next steps
-
- Build your first ETL pipeline
- Transform the data that you ingested with Lakeflow pipelines
-
- Managed database connectors
- Learn about managed database connectors, their components, and their network connectivity options in Lakeflow Connect