Lakeflow connect CT pipeline keeps running multiple connections in source

[Fatimah-Tariq]

I'm working on an ingestion pipeline. I was adding tables to it gradually so that we can monitor the load on our prod server side by side and with my last set of tables added (with them, all the heavy duty tables were inside that pipeline), the load on our actual prod databases(source) went over the board and via logs, it showed this lakeflow connect pipeline running 5 connections each using 2 processors. That affected our prod database alot and I had to stop this pipeline. What I'm not sure about is why is this pipeline running 5 connections when I just created 1 ingestion pipeline on databricks? What is the reason of 5 running connections each using 2 processors? Is this some configuration issue or what? Is there any why by which I can make it better?
I'm also attaching the load log from my source database. Look at all the lakeflow connect connections with name ftariq

[Ashwin_DSA]

Hi @Fatimah-Tariq,

I can see exactly what is happening from your log screenshot. You created one ingestion pipeline, but you are seeing 5 separate sessions from lakeflow-connect.. under the ftariq login on your source SQL Server. This is not a bug and not a misconfiguration on your part. It's how the Lakeflow Connect ingestion gateway works internally.

When you create a single Lakeflow Connect database ingestion pipeline, Databricks actually spins up two components behind the scenes... an ingestion gateway (runs continuously on classic compute) and an ingestion pipeline (runs on serverless compute on a schedule). The gateway is the piece that connects to your source SQL Server. See here for concepts.

The gateway performs multiple activities in parallel against your source database:

1. Snapshot extraction for tables that still need their initial full load
2. CDC (Change Data Capture) / Change Tracking reads for tables already past the initial snapshot
3. Schema evolution detection via queries against LakeFlow Connect's proprietary tracking objects
4. Metadata and schema fetching for table discovery and validation

Each of these activities opens its own database connection. The gateway maintains a connection pool for snapshot operations and a separate pool for real-time CDC operations. The default maximum is 10 connections each for snapshot and real-time extractions. So the 5 connections you're seeing with 2 processors each is actually the gateway being relatively conservative. It could go up to 10 per pool depending on how many tables are being processed concurrently.

The reason the load spiked when you added your heavy-duty tables is that the gateway now has to run initial snapshots (full SELECT * FROM table reads) for those large tables while simultaneously continuing CDC reads for the tables you added earlier. Snapshot is the most resource-intensive phase because it reads all existing data from the source.

To reduce the load on your source DB, here are a some options...

1. Downsize the gateway driver node. The gateway runs entirely on the driver node of a classic compute cluster. The parallelism is tied to the number of CPU cores on that driver. If you are using a node with, say, 8 or 16 cores, the gateway can run more concurrent threads against your source. Switching to a smaller driver node (the minimum supported is 2 cores) will naturally reduce the number of concurrent connections and queries hitting your source. You can configure this in the gateway pipeline's cluster settings.
   
   
2. Add tables in smaller batches. Instead of adding all your heavy tables at once, add them in waves. The initial snapshot phase is the most demanding part. Once a table's snapshot is complete, the ongoing CDC reads are much lighter. Let each batch finish its snapshot before adding the next group.
   
   
3. Schedule your ingestion pipeline during off-peak hours. The ingestion pipeline (the serverless part that moves data from staging to destination tables) can be scheduled. If you time it for low-traffic windows on your prod database, the snapshot load won't compete with your application workload.
   
   
4. Consider stopping and restarting the gateway strategically. The gateway can be stopped and restarted. If your production database has specific maintenance windows, you could stop the gateway during peak business hours and restart it during quieter periods. Keep in mind that the gateway needs to run continuously to capture CDC changes before change logs get truncated, so don't leave it stopped for too long.
   
5. Work with your DBA to tune the source side. Make sure Change Tracking cleanup jobs on your SQL Server aren't running at the same time as the gateway's snapshot operations. There have been cases where the combination of LakeFlow Connect sessions and CT cleanup jobs creates blocking on the source. Staggering these can help significantly.

Hope this helps.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[Fatimah-Tariq]

Hi @Ashwin_DSA, 

Thankyou for responding. In your suggested solutions, you mentioned "Downsize the gateway driver node. The gateway runs entirely on the driver node of a classic compute cluster. The parallelism is tied to the number of CPU cores on that driver. If you are using a node with, say, 8 or 16 cores, the gateway can run more concurrent threads against your source. Switching to a smaller driver node (the minimum supported is 2 cores) will naturally reduce the number of concurrent connections and queries hitting your source. You can configure this in the gateway pipeline's cluster settings."

Where exactly can I set this setting while creating an ingestion pipeline? I don't remember seeing this setting anywhere while creating my current pipeline.

[Ashwin_DSA]

@Fatimah-Tariq 

Correction to my earlier reply, and thanks to @AbhilashNagilla for catching it.

I suggested stopping and restarting the gateway during maintenance windows to ease the load on the source. That was wrong, and @AbhilashNagilla  is right. You should not manually stop the ingestion gateway. It has to run continuously, otherwise the source truncates its change-tracking/CDC data on its own retention schedule before Databricks reads it, and any dropped changes then force a full refresh of the affected tables. Please disregard that part of my earlier message.

The docs are explicit on this.

• SQL Server Connector Limits: The ingestion gateway must run in continuous mode to avoid changes being dropped due to log retention. and Do not manually stop the gateway.
• SQL Server pipeline: If the gateway is stopped, changes can be dropped due to log retention, requiring a full refresh of all affected tables.

So the right levers for source load are adding tables in smaller batches, so snapshots finish sequentially, sizing the gateway driver appropriately, and, if you ever genuinely need downtime, first extending Change Tracking retention on the source with the setup utility rather than stopping the gateway. Check here.

@Fatimah-Tariq  - On your follow-up... gateway driver size is not a field in the guided ingestion pipeline wizard. The gateway runs as its own pipeline object, and its compute is configured outside that wizard.

The documented, recommended path is a Job Compute cluster policy that pins the node types, which you then attach to the gateway pipeline. Two sizing notes from the docs.

• The driver needs at least 8 cores. "The minimum requirement for the driver node is 8 cores to enable efficient and performant data extraction from your source database."
• Workers can be the smallest available. "Databricks recommends specifying the smallest possible worker nodes for ingestion gateways because they do not impact gateway performance."

Example cluster policy from the docs.
{
"driver_node_type_id": { "type": "fixed", "value": "r5n.16xlarge" },
"node_type_id": { "type": "fixed", "value": "m5n.large" }
}

You then reference that policy on the gateway pipeline's clusters block, via the pipeline settings JSON, the Pipelines API, Asset Bundles, or Terraform. Compute configuration guidance is here and the Pipelines API, which exposes node_type_id and driver_node_type_id, is here.

Worth knowing... lowering driver cores does reduce how much parallelism the gateway runs, which is one way to lighten the source, but keep the driver at or above the 8-core minimum. Pair the sizing with batching your table additions so a heavy snapshot is not hitting the source at the same time as CDC on your existing tables.

Hope this helps.

[AbhilashNagilla]

For a standard connector, the gateway continuously extracts snapshots, change logs, and metadata, while a separately scheduled serverless pipeline applies staged data (connector components). Changing the serverless pipeline schedule therefore does not move source extraction to off-peak hours (SQL Server connector FAQs).

1. Query operation_progress in the gateway event log to identify which tables are snapshotting when SQL Server load rises (monitor gateway progress).

2. Keep the gateway running; manually stopping it can allow source logs to truncate, drop changes, and require a full refresh (SQL Server pipeline guidance).

3. If source impact remains unacceptable, have an authorized support contact open a support case.