Skip to main content

Deploy a SQL Server ingestion pipeline as a bundle

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

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 Databricks.
  • A Unity Catalog connection to SQL Server and the USE CONNECTION privilege on it, or the CREATE CONNECTION privilege on the metastore so that you can create the connection.
  • USE CATALOG on the destination catalog, and USE SCHEMA, CREATE TABLE, and CREATE VOLUME on 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:

YAML
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.

YAML
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:

YAML
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:

Shell
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:

  1. In your workspace, click Jobs & Pipelines.
  2. Confirm that the job run and the pipeline update complete successfully. The first update captures an initial snapshot of each table.
  3. Click Data icon. 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​