Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Define a Lakeflow Connect ingestion pipeline for SQL Server in Declarative Automation Bundles, so that you version control the pipeline and deploy it the same way to development, staging, and production. This page uses an integrated change data capture (CDC) pipeline, which extracts changes from SQL Server and applies them to Unity Catalog tables in a single pipeline. You don't create a separate ingestion gateway.
To learn what bundles are and how they fit into a continuous integration and continuous delivery (CI/CD) workflow, see Operations and CI/CD. To compare the integrated pipeline with the gateway-based architecture, see When to use the integrated CDC connector.
What the bundle deploys
The bundle on this page defines the following resources:
| Resource | Description |
|---|---|
| Integrated CDC pipeline | Extracts changes from SQL Server through a Unity Catalog connection, stages them in a Unity Catalog volume, and applies them to the destination tables |
| Job | Runs the pipeline on a schedule |
Each pipeline update stops automatically after it catches up with the source or reaches its maximum runtime, so the job controls how often new changes arrive. A large backlog can take more than one update to apply. See Smart closure for integrated CDC pipelines.
Requirements
You need the following before you deploy the bundle:
- A workspace that is enabled for Unity Catalog, with the integrated CDC connector enabled. See Requirements.
- The Databricks CLI, authenticated with
databricks auth login. - Access to the primary SQL Server instance. Read replicas, standby instances, and secondary instances aren't supported.
- Change tracking or CDC enabled on the source database and on the tables that you plan to ingest. See Configure Microsoft SQL Server for ingestion into Azure Databricks.
- A Unity Catalog connection to SQL Server and the
USE CONNECTIONprivilege on it, or theCREATE CONNECTIONprivilege on the metastore so that you can create the connection. USE CATALOGon the destination catalog, andUSE SCHEMA,CREATE TABLE, andCREATE VOLUMEon the destination schema. The pipeline creates its staging volume in the destination schema.- Compute that can reach SQL Server over the network. See Compute requirements.
Deploy the bundle
Define the pipeline and the job in a bundle, and then deploy it to your workspace.
Create the SQL Server connection
If you don't have a connection to SQL Server, create one in Catalog Explorer. See Create a SQL Server connection.
Create the bundle configuration
In a new directory, create a databricks.yml file. It names the bundle, includes the resource files that you create in the next steps, and sets the variable values for each target:
bundle:
name: sql_server_cdc
include:
- resources/*.yml
targets:
dev:
mode: development
default: true
workspace:
host: https://<workspace-url>
variables:
pipeline_name: sql_server_cdc_dev
connection_name: <connection-name>
dest_catalog: <catalog>
dest_schema: <schema>
Replace <workspace-url>, <connection-name>, <catalog>, and <schema> with your values. Add a target with its own values for each environment, such as staging and production. See Declarative Automation Bundles configuration.
Define the pipeline
In your bundle, create a file such as resources/integrated_cdc_pipeline.yml. Set connector_type to CDC, and list each source table under objects. You can't change connection_name or connector_type after the pipeline is created.
variables:
pipeline_name:
description: 'Name for the integrated CDC pipeline'
connection_name:
description: 'Unity Catalog connection name'
dest_catalog:
description: 'Destination catalog for ingested data'
dest_schema:
description: 'Destination schema for ingested data'
resources:
pipelines:
integrated_cdc_pipeline:
name: ${var.pipeline_name}
catalog: ${var.dest_catalog}
schema: ${var.dest_schema}
ingestion_definition:
connection_name: ${var.connection_name}
connector_type: CDC
objects:
- table:
source_catalog: 'my_database'
source_schema: 'dbo'
source_table: 'customers'
destination_catalog: ${var.dest_catalog}
destination_schema: ${var.dest_schema}
destination_table: 'customers'
table_configuration:
scd_type: 'SCD_TYPE_1'
Replace my_database, dbo, and customers with your source database, schema, and table. To ingest every table in a source schema, or to use slowly changing dimension (SCD) type 2, see Create an integrated CDC pipeline.
Define the job
Create a file such as resources/integrated_cdc_job.yml that runs the pipeline on a schedule. Each extraction stage runs for at least 10 minutes, so start with an interval of 60 minutes or longer. This example runs every hour:
resources:
jobs:
integrated_cdc_job:
name: '${var.pipeline_name}-job'
tasks:
- task_key: 'cdc_ingestion'
pipeline_task:
pipeline_id: ${resources.pipelines.integrated_cdc_pipeline.id}
schedule:
quartz_cron_expression: '0 0 * * * ?'
timezone_id: 'UTC'
Deploy and run the bundle
Deploy the bundle, and then run the job to start the first pipeline update:
databricks bundle deploy
databricks bundle run integrated_cdc_job
To deploy to another target, add -t <target> to both commands. To override a variable value for one command, pass --var="<name>=<value>". See Set a variable's value. In a development mode target, the bundle pauses the job's schedule. See Declarative Automation Bundles deployment modes.
Verify the deployment
Check the pipeline status and the destination tables to confirm that the deployment ingests data:
- In your workspace, click Jobs & Pipelines.
- Confirm that the job run and the pipeline update complete successfully. The first update captures an initial snapshot of each table.
- Click
Catalog, open your destination catalog and schema, and confirm that the ingested tables contain data.
Troubleshooting
The following entries cover a pipeline that can't reach SQL Server, a missing staging volume, and a permission error when you deploy.
The pipeline can't connect to SQL Server
The pipeline's compute must be able to reach SQL Server over the network. For classic compute, check the peering, routing, security group, and firewall configuration between the workspace's virtual private cloud (VPC) or virtual network (VNet) and SQL Server. For serverless compute, check the serverless network connectivity configuration. Then, in Catalog Explorer, open the connection and click Test connection to confirm that the credentials are valid. See Compute requirements.
The pipeline fails to create its staging volume
The pipeline creates a staging volume in the destination schema. The user or service principal that runs the pipeline needs the CREATE VOLUME privilege on that schema.
The deployment fails with a permission error
The user or service principal that deploys the bundle must have permission to create pipelines in the workspace and the USE CONNECTION privilege on the SQL Server connection.
Next steps
| Resource | Description |
|---|---|
| Build your first ETL pipeline | Transform the data that you ingested with Lakeflow pipelines |
| Create an integrated CDC pipeline for SQL Server | Learn all the options for an integrated CDC pipeline for SQL Server |