Integrated CDC Pipelines for SQL Server and MySQL: Configuration and Limits

1.Standard gateway-based CDC vs integrated CDC

In the standard Lakeflow Connect architecture, a database connector uses two pipelines. An ingestion gateway runs all the time and extracts changes into a staging volume, and an ingestion pipeline applies them to streaming tables. SQL Server and MySQL also offer a second option, the integrated CDC pipeline. In a single pipeline update it runs both stages one after the other. During extraction it connects through a Unity Catalog connection, takes an initial snapshot on the first run or a full refresh, and after that captures inserts, updates and deletes into a staging volume. During application it merges those changes into destination streaming tables using the configured primary keys and SCD type, with exactly-once semantics.

Both engines use the same source configuration for either architecture, so moving from one to the other does not touch the database. The differences are on the Databricks side. Integrated CDC must be enabled for your workspace by your Databricks account team, and for MySQL it is a Beta feature. Integrated pipelines can be created through the API, the CLI, notebooks or Declarative Automation Bundles, but not yet through the UI. The standard SQL Server connector does support UI authoring. On SQL Server, the integrated connector reads only from the primary instance, not from read replicas, standby or secondary instances. On compute, a SQL Server integrated pipeline defaults to classic and can be switched to serverless, while a MySQL integrated pipeline is limited to classic.

Sources12

2.Writing the ingestion_definition

An integrated CDC pipeline is defined in a single request. The top-level catalog and schema set the default destination. Inside ingestion_definition you give the connection, the connector type, and an objects list of the tables to replicate. Here is the SQL Server version created from the CLI:

databricks pipelines create --json '{
  "name": "<pipeline-name>",
  "catalog": "<destination-catalog>",
  "schema": "<destination-schema>",
  "ingestion_definition": {
    "connection_name": "<unity-catalog-connection-name>",
    "connector_type": "CDC",
    "objects": [
      {
        "table": {
          "source_catalog": "<source-database>",
          "source_schema": "<source-schema>",
          "source_table": "<source-table>"
        }
      }
    ]
  }
}'

For SQL Server, source_catalog holds the database and source_schema holds a schema such as dbo. MySQL has no separate schema level, so its table objects leave out source_catalog and put the database name in source_schema. MySQL requests have one more requirement: every pipeline creation request must include a channel field set to the preview channel.

databricks pipelines create --json '{
  "name": "<pipeline-name>",
  "channel": " ? ",
  "catalog": "<destination-catalog>",
  "schema": "<destination-schema>",
  "ingestion_definition": {
    "connection_name": "<unity-catalog-connection-name>",
    "connector_type": "CDC",

Each table can set table_configuration.scd_type to SCD_TYPE_1 or SCD_TYPE_2. In the documentation's SQL Server example, customers uses Type 1 and orders uses Type 2, which needs SQL Server CDC on the source, not just change tracking. To pick up a whole schema, use a schema object instead of table objects. The pipeline then skips any table that does not have CDC or change tracking enabled. data_staging_options sets where the staging volume goes. If you leave it out, the pipeline still creates a staging volume in the destination schema, so you need CREATE VOLUME there either way. Finally, connection_name and connector_type cannot be changed after the pipeline is created. To change the source, you create a new pipeline.

Sources21

3.Scheduling runs and recovering from log purges

An integrated pipeline has no always-on gateway. Each update extracts changes and stops by itself once it has caught up with the source, within a maximum runtime. This behaviour is called smart closure, and you cannot configure its minimum or maximum runtime. A large backlog can take several updates, each one continuing from where the last stopped. Pipelines run in triggered mode by default (continuous mode is in Beta), so for recurring ingestion you schedule them with a Lakeflow Jobs task. Every extraction stage runs for at least 10 minutes, and the documentation suggests starting with an interval of 60 minutes or longer:

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'

Because nothing reads the change logs between scheduled runs, the source's log retention becomes important. If SQL Server purges change tracking or CDC logs before the pipeline has processed them, the pipeline reports an error in the event log and you must fully refresh the affected tables. On classic compute, vertical autoscaling is on by default: if an update fails because it ran out of memory, the next update gets a larger driver.

Sources1

4.Schema evolution and limits across the three connectors

The SQL Server, MySQL and PostgreSQL connectors share most features: UI and API authoring, Declarative Automation Bundles, incremental ingestion, Unity Catalog governance, SCD type 2, and API-based column selection. None of them supports API-based row filtering. They differ in how they handle schema changes:

Sources34

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Every integrated CDC pipeline can run on serverless by adding "serverless": true.Why is that wrong?

   That flag applies to SQL Server integrated pipelines. MySQL integrated CDC pipelines run on classic compute only.

   Covered in Standard gateway-based CDC vs integrated CDC

2. 2.Any SQL Server table with change tracking enabled can be ingested as SCD Type 2.Why is that wrong?

   In the documentation's example, SCD Type 2 requires SQL Server CDC on the source.

   Covered in Writing the ingestion_definition

3. 3.You can re-point an existing integrated CDC pipeline at a different source by editing its connection_name.Why is that wrong?

   connection_name and connector_type cannot be changed after creation. To change the source, create a new pipeline.

   Covered in Writing the ingestion_definition

Practise it for real

Deploy a SQL Server integrated CDC pipeline and an hourly job with Declarative Automation Bundles

1. 1.Create a Unity Catalog connection to SQL Server in Catalog Explorer, and confirm that change tracking or CDC is enabled on the source tables.

   Why: API- and bundle-based authoring needs an existing connection, and the pipeline skips tables that do not have change capture enabled.

   You should see: The connection tests successfully from Catalog Explorer.

2. 2.In resources/integrated_cdc_pipeline.yml, define a pipeline whose ingestion_definition sets connection_name, connector_type: CDC, and a table object for dbo.customers with scd_type 'SCD_TYPE_1'.

   Why: Integrated CDC references the connection directly and requires an explicit connector type.

   You should see: A pipeline resource named integrated_cdc_pipeline in the bundle.

3. 3.In resources/integrated_cdc_job.yml, add a job with a pipeline_task pointing to the pipeline and quartz_cron_expression '0 0 * * * ?'.

   Why: Integrated pipelines are triggered and stop after catching up, so a job provides recurring ingestion. 60 minutes is the suggested starting interval.

   You should see: A job resource named integrated_cdc_job.

4. 4.Run databricks bundle deploy, then databricks bundle run integrated_cdc_job.

   Why: Deploy creates the pipeline and job. Run triggers the first update, which takes the initial snapshot.

   You should see: A customers streaming table appears in the destination schema, along with an auto-created staging volume.