The Databricks Lakeflow Connect SQL Server Connector: Field Notes and Gotchas

This is a field report on using Databricks Lakeflow Connect (LFC) to ingest five SQL Server databases into a Databricks lakehouse: what worked, what broke, and what the documentation doesn't warn you about. The SQL Server connector went generally available on 25 September 2025, with change tracking, CDC and SCD Type 2 beta support, and we adopted it early.

The headline lesson first. The most painful bug we hit cost two weeks of emails with support and elaborate theories about platform detection and master-database grants, and the cause was one wrong capital letter in a schema name. Jira versus jira. That was the entire bug. Most of what follows is in that spirit: small undocumented things with large blast radii.

Key takeaways

• A Lakeflow Connect database connector is two pipelines, not one: a continuous ingestion gateway and a scheduled ingestion pipeline. Working out which one failed is the first debugging step.

• Build your first pipeline in the Databricks UI before automating with Asset Bundles, because the UI gives clearer errors at each step.

• The literal case of source identifiers in your config matters. A single wrong capital letter in a schema name can stop the connector finding any tables.

• SQL Server DATETIME values are read as UTC on the Databricks side, so reconcile local time to UTC yourself downstream.

• The continuous gateway drives most of the cost, on the order of fifteen to twenty times the scheduled ingestion pipeline.

• Change Data Capture (CDC) option does not list views and functions; users can leverage query-based capture or JDBC via a notebook for these sources.

• Lakeflow Connect does not support reading directly from SQL Server views or functions; only tables are supported as sources.

• When CDC is cycled on the source, expect to drop and rebuild both pipelines, then clean up the old staging volumes.

• Keep the Integrated CDC ingestion option in your arsenal and consider it for production release once it reaches GA if you pull data infrequently and don’t want to pay for 24/7 classic compute.

• Ask your account team to enable the pipeline flag pipelines.gateway.snapshot.useQBCSparkSnapshot to speed up snapshot ingestion.

What Lakeflow Connect is

Databricks Lakeflow Connect is a set of managed connectors that bring data from external sources into the Databricks lakehouse under Unity Catalog governance. The SQL Server connector reads from SQL Server using change tracking or change data capture, then writes into Unity Catalog streaming tables. It runs as two pipelines: a continuous ingestion gateway that extracts from the source, and a scheduled ingestion pipeline that applies those changes to the destination.

The situation: five SQL Server databases into one lakehouse

We had five SQL Server databases feeding the client's analytics, all on-premises to start with, previously served by SSIS. The goal was to bring ingestion into the Databricks lakehouse and under Unity Catalog governance.

The team and the client weighed the usual options: hand-rolled JDBC extracts, Azure Data Factory, Fivetran, and Lakeflow Connect. The decision to go with Lakeflow Connect was a joint team and client call, made for a few practical reasons. It is native to the platform, so it is one less system and one less bill. It gives you Unity Catalog governance and lineage out of the box. And it avoids onboarding another vendor. And, of course, we were curious to see what Lakeflow Connect was actually made of. (Note: Since query-based capture wasn't available at the time of setup, we used JDBC for functions and views.)

One wrinkle that matters later: all five databases started on-premises, and once we had sorted the correct port, ingestion worked cleanly across all of them. Some time afterwards the client migrated one of them, the Jira database, to Azure SQL Managed Instance. So today four are on-prem and one sits on Managed Instance, and that single move is where most of this article's drama comes from.

The third-party vendor who manages the databases handled the source-side setup, enabling CDC and running the Databricks utility scripts per the official guide. Those scripts are also just code: ours failed once because of mixed-case SQL, and re-casing it to a single case fixed it. Databricks has since shipped newer versions, so that one may already be gone.

How it actually works (the mental model)

A database connector in Lakeflow Connect is two pipelines, not one, and every later debugging session depends on knowing that.

The ingestion gateway runs on classic compute, runs continuously, and extracts from the source (snapshots plus the change stream) into a staging volume holding CDC logs, snapshots and metadata. That volume is purged after 30 days. The continuous part is not optional: if the gateway is not running when the source's log retention window rolls over, changes are dropped, and dropped changes mean a full refresh.

The ingestion pipeline runs on serverless compute, on a schedule, and applies what the gateway staged into Unity Catalog streaming tables. Databricks recommends at least five minutes between runs. You pick SCD Type 1 or Type 2 for the destination; we used Type 1.

The connection is just credentials stored in Unity Catalog, connecting over TLS and JDBC.

Two more facts worth tattooing somewhere visible. All datetimes are ingested in UTC (a whole saga, see below). And deleting the ingestion pipeline drops the destination streaming tables it created; it does not orphan them.

So the shape is: source, then a continuous classic-compute gateway, then a staging volume, then a scheduled serverless ingestion pipeline, then UC streaming tables. When something breaks, your first question is which of the two pipelines broke, because the error in one is very often a symptom of the other.

Setup: build it in the UI first

Build your first pipeline in the UI, even if your end state is fully automated with Databricks Asset Bundles (DAB). The UI walks you through the steps in an order that teaches the model, and its errors are descriptive and per-step. When the same thing fails via DAB, you often get one opaque failure with none of that context.

The UI flow is roughly:

1. Set up the connection.

2. Name the gateway pipeline and its staging schema.

3. Name the ingestion pipeline and its destination schema.

4. Let Databricks build the pipelines and connect (most source-side problems surface here, with reasonably specific errors).

5. Pick your tables, then validation and an optional scheduled job.

A few things worth knowing going in.

If you use a Databricks accelerator framework, the connector part is likely already in it; you just add the relevant config in the Unity Catalog section rather than wiring it from scratch.

Connection names need to be unique across your environments. Connections are account-wide accessible, not scoped per workspace, so a name clash will bite you. We encoded the environment into the name: our catalog represented the environment (dev, tst, prd), so the connection name was derived from the catalog variable.

Check your port. Standard SQL Server is 1433. A non-standard port is a fun afternoon to lose, and it is exactly what cost us our first attempt across the on-prem databases.

Here is a lightly anonymised version of our pipelines YAML, with the ingestion pipeline referencing the gateway by ID. The docs are thin here, so a working sample is handy:

targets:
  dev:
    resources:
      pipelines: &pipelines_anchor
        pipeline_jira_gateway:
          name: jira-gateway-pipeline
          gateway_definition:
            connection_name: jira_ms_sql_connection_${var.default_catalog}
            gateway_storage_catalog: ${var.default_catalog}
            gateway_storage_schema: raw_jira
          schema: raw_jira
          continuous: true
          catalog: ${var.default_catalog}
        pipeline_jira_ingest:
          name: jira-ingest-pipeline
          ingestion_definition:
            ingestion_gateway_id: ${resources.pipelines.pipeline_jira_gateway.id}
            objects:
              - table:
                  source_catalog: jira
                  source_schema: dbo
                  source_table: DETAILS
                  destination_catalog: ${var.default_catalog}
                  destination_schema: raw_jira
                  destination_table: DETAILS

Note connection_name: jira_ms_sql_connection_${var.default_catalog}: the catalog variable keeps it unique per environment. And note source_catalog: jira and source_schema: dbo. The case of those values is load-bearing, in ways the documentation does not adequately shout about. More on that shortly.

Two DAB tricks we had to invent.

The anchor hack for dev mode. In DAB development mode, every engineer deploys their own copy under their own name. That is fine for normal pipelines and terrible for a connector, because you do not want a dozen developers each spinning up a gateway against the same source. The native exclude mechanism in databricks.yml did not do what we needed, so we used a YAML anchor (&pipelines_anchor) and only aliased it into the environments that should actually run the connector, test and prod, under a service principal:

 tst:
    resources:
      pipelines: *pipelines_anchor
  prd:
    resources:
      pipelines: *pipelines_anchor

The prod-only-table trick. Our prod source had one extra table the lower environments did not. Rather than maintain divergent configs, we keep it commented out with sentinel markers and let CI/CD uncomment it when the target is prod:

   #<PRD-ONLY-NODE-BEGIN>: Enable only for production
    # - table:
    #     source_catalog: jira
    #     source_schema: dbo
    #     source_table: jira_only_prd_table
    #     destination_catalog: ${var.default_catalog}
    #     destination_schema: raw_jira
    #     destination_table: jira_only_prd_table
    #<PRD-ONLY-NODE-END>

Elegant, no. Works, yes.

Change tracking versus CDC

The SQL Server connector can use both Microsoft Change Tracking (CT) and Change Data Capture (CDC). The rule, from the docs: if a table has a primary key, Databricks recommends change tracking; if it does not, it recommends CDC; and if both are enabled, the connector uses change tracking.

We run CDC across all databases. It was a client decision, and the practical reason is simply that not all tables have primary keys, so CDC was the consistent choice.

For the four on-prem CDC databases this was a non-event. The one place it bit us was the single Managed Instance database, where validation threw a red "Pipeline validation error" claiming "18 tables need change tracking enabled" despite CDC being correctly enabled. It was not a recurring nag and we never saw it on the other databases, which is why it read less like a recommendation and more like a bug specific to that Managed Instance case. We hit Skip validation, the gateway started, and ingestion ran correctly. Skipping validation there was not reckless; it was the right move.

War story one: the case-sensitivity saga

After the Jira database moved to Managed Instance, we could no longer get the connector working via DAB. The UI path worked: skip validation, gateway starts, ingestion runs. The DAB path failed. Same connection, same source, different result.

The gateway, deployed via DAB and running as the service principal, threw error class INGESTION_GATEWAY_SOURCE_SCHEMA_MISSING…, "No tables are available for replication from the source." Downstream, the ingestion pipeline threw _LEGACYERROR_TEMP_155_TIMEOUT, "Timed out waiting for all replicated tables… Timed out with exception after 31 attempts."

That is the first trap: one root cause produced two unrelated-looking errors in two different pipelines. The gateway found zero tables, so the ingestion pipeline waited for tables that were never coming and timed out. Chase that timeout and you are debugging the wrong pipeline.

Here is the graveyard of things it was not. Each one is a plausible trap:

Managed Instance collation is meant to be case-insensitive, which is exactly why nobody suspected case for two weeks. The portable lesson: when a connector says it cannot find your tables, check the literal case of every source identifier in your config before you spend two weeks on permissions.

War story two: living with it

Steady-state, the connector is mostly quiet. The production failures we hit were almost all the same shape: something changing on the source side, usually CDC being cycled off and on during server maintenance.

When that happens you do not get one table erroring, you get all of them. One pipeline run showed 111 errors across the table list, the headline one being INGESTION_GATEWAY_CDC_NOT_ENABLED, "Table … is not correctly set up as neither CT nor CDC is enabled for it," repeated table after table, with timeouts and "failed due to an error in a previous update" piling on behind it.

This is the context for full refresh, a feature that sounds like it should help. In principle a per-table full refresh is the right-sized tool. But when CDC is cycled, every table is affected at once, so a per-table refresh has the same blast radius as a full rebuild. And we hit an oddity: after a full refresh, the tables showed in the UI as "Omitted" rather than active.

So what we actually do is drop and rebuild both pipelines, gateway and ingestion. Resetting only the ingestion pipeline usually is not enough. Here is the gotcha that bit me personally: when you drop the pipelines, the new gateway creates a new staging volume, but the old volume is not deleted, and a stale leftover volume can confuse the new pipeline. Clean up all artifacts after dropping. We keep a small cleanup notebook for this that grants MANAGE, drops the gateway and ingest pipelines, and removes the old volumes per schema.

Our runbook:

1. Check with the DBA team whether anything changed on the source: CDC disabled, permissions altered, a migration. This is the cause most of the time.

2. Confirm the pipeline YAML was not accidentally modified.

3. Try rebuilding one table in the UI.

4. If that is not enough, drop both pipelines, run the cleanup to remove old volumes, redeploy the DAB, and trigger the ingestion pipeline about ten minutes after the gateway so it has data staged first.

The rebuild cost is not nothing. You re-snapshot everything, which can be a lot of rows, and it is not instant: plan for a few hours for the full snapshot, then up to roughly a day for incremental to catch up.

A few schema-evolution edges worth knowing. We opted out of automatic schema handling, but either way: a new column added mid-flight will not backfill without a full refresh; a deleted source column is marked inactive rather than dropped, and a later column whose name collides with that inactive one will fail the pipeline; and renames or type changes need a full refresh. Also from the docs: aim for 250 or fewer tables per pipeline; you cannot ingest two tables in one pipeline whose names differ only in case, and only primary instances are supported, not read replicas.

If you want a quiet way to see how often a team has been through this, query system.lakeflow.pipelines and count rows that share a pipeline name but have different pipeline IDs and a delete_time. In one of our workspaces a single source system had several dozen pipeline records collapsed under just two or three names, most of them deleted. That is the sediment left by every rebuild and every manual experiment.

The timezone trap: SQL Server DATETIME read as UTC

If you take one undocumented gotcha from this, make it this one, because it is silent and it is wrong by a suspiciously round number.

Our SQL Servers are set to Melbourne time, and the DATETIME columns store local Melbourne wall-clock values. A plain SQL Server DATETIME carries no time zone information; it is just 2026-04-24 16:24:00 with no offset attached.

Lakeflow Connect preserves the digits of a SQL Server DATETIME, but Databricks reads the value as UTC. The reason is how Spark works: its timestamp type stores an instant in UTC and applies your session time zone only at display time, so a zone-less wall-clock value is treated as already being UTC. A value that was really 16:24 in Melbourne is now labelled 16:24 UTC.

Nothing errors. The bomb goes off downstream: any cluster or warehouse session set to Melbourne time then shifts the value by the offset, plus ten or eleven hours, and your timestamps are most of a day off. The tell is exactly that, reports drifting by a suspiciously round ten or eleven hours, and daylight saving makes it worse because Melbourne flips between the two.

The takeaway for anyone ingesting DATETIME from a non-UTC SQL Server: do not trust the zone label on ingested datetimes, and reconcile local time to UTC yourself downstream.

A smaller cousin lives in old dates. Spark 3.0 switched to the Proleptic Gregorian calendar, which disagrees with the older hybrid calendar for dates before 15 October 1582. You will not have genuine medieval dates, but you might have junk sentinel dates that suddenly look stranger than expected. Won't bite most people; when it does, now you know why.

Event logs

Another minor papercut: the Event logs table is only directly accessible by the service principal that deploys the LFC pipelines. If you deploy using pipelines defined as Infrastructure as Code via DAB, you won't be able to query the raw logs directly under your personal account. 

The workaround is simple enough: log in under an Admin account, create a view over the pipeline's event log table:

CREATE OR REPLACE VIEW meta.v_ingestion_pipeline_event_log AS 
SELECT * FROM meta.ingestion_pipeline_event_log;

Grant select access on that view to your email or user group, and use that view for troubleshooting and querying error details.

What Lakeflow Connect cost

Within the connector, the cost that matters is the split between the two pipelines. The continuous, classic-compute gateway costs far more than the scheduled serverless ingestion pipeline, on the order of fifteen to twenty times more in our usage, because the gateway never sleeps. If you are reasoning about this connector's cost, reason about the gateway. Add a slice of private-network cost too, though you cannot isolate it: there is no separate Lakeflow Connect networking tag, so it is billed alongside other network usage at the workspace level.

On the model: managed connectors get a free tier of 100 DBUs per workspace per day, then $0.50 per DBU, which includes the underlying compute rather than billing it separately. Pricing and promotions are region and time-specific, so check the current pricing page rather than trusting a number in an article. The practical note: an active snapshot or rebuild will comfortably exceed the free tier, so budget for the backfills rather than the idle.

If you want your own numbers, system.billing.usage joined to system.billing.list_prices gives you real per-SKU cost, and splitting by pipeline shows the gateway-versus-ingestion picture. True end-to-end latency, source change to destination row, is harder to measure cleanly; the system tables give you run durations, not lag.

Lakeflow Connect versus Fivetran

For context, I ran Fivetran in production on a different project a few years ago, not head-to-head against Lakeflow Connect, so treat this as an engineer who has used both at different times rather than a benchmark.

On dollar-for-dollar pricing I will only make the structural point, because the models reward different usage shapes. Fivetran bills on rows changed; Lakeflow Connect bills on compute. Which is cheaper depends entirely on your volumes and change rates, so model both against your own data.

What's coming to GA

Databricks offers an integrated CDC pipeline architecture in preview that unifies extraction and application into a single pipeline, eliminating the need for a 24/7 continuous gateway. Because it runs on a schedule rather than maintaining an always-on gateway compute instance, it is a significantly cheaper option for workloads with infrequent data pulls. However, pipelines must be scheduled frequently enough to ingest changes before SQL Server purges its CDC log retention window; otherwise, a full refresh is required. But this is another story: how we found a bug in Beta version and worked with Databricks to address it.

Verdict: Should you use it?

Honestly, it is less polished than Fivetran, and Databricks has only recently started down this road. But it has real advantages of its own: the native governance, the single platform, the IaC story. It is worth giving a go if you are already on Databricks with mainstream database sources. Just don't expect too much of it just yet, keep a rebuild runbook handy, mind the UTC datetime behaviour, and for the love of all that is holy, check the case of your schema names.

FAQ

What is the Databricks Lakeflow Connect SQL Server connector? A managed connector that ingests data from SQL Server into the Databricks lakehouse under Unity Catalog governance. It went generally available on 25 September 2025 and supports change tracking, change data capture and SCD Type 2.

Does the connector support both change tracking and CDC? Yes. Databricks recommends change tracking for tables with a primary key and CDC for tables without one. If both are enabled on a table, the connector uses change tracking.

Why does the connector report that it cannot find any tables? Check the literal case of every source identifier in your configuration, including catalog and schema names. Even on a case-insensitive Managed Instance, a case mismatch can make the gateway find zero tables and leave the ingestion pipeline waiting until it times out.

Why are my timestamps wrong after ingesting SQL Server DATETIME? A plain SQL Server DATETIME carries no time zone, and Databricks reads the value as UTC. If your source stores local time, reports can drift by the offset, so reconcile local time to UTC downstream.

What drives the cost of the connector? The continuous ingestion gateway runs on classic compute and never sleeps, so it costs far more than the scheduled serverless ingestion pipeline, on the order of fifteen to twenty times more in our usage. Snapshots and rebuilds add further cost.

Lakeflow Connect or Fivetran, which is cheaper? It depends on your data. Fivetran bills on rows changed and Lakeflow Connect bills on compute, so model both against your own volumes and change rates.