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.

[anuj_lathi]

Short version: the connections aren't coming from your ingestion pipeline. They come from the ingestion gateway, which reads from SQL Server in parallel, and the "2 processors" per session is most likely SQL Server's own parallel query execution (DOP), not something Databricks configures.

1. Why more than one connection?

A SQL Server Lakeflow Connect setup has two pieces:

• Ingestion gateway: a continuously running pipeline on classic compute. It is the only component that connects to your source. It extracts snapshots and change data and lands them in a Unity Catalog volume.
• Ingestion pipeline: reads from that staging volume and applies changes to your Delta tables. It never touches the source database.

So even with "one pipeline," the gateway opens multiple sessions and works on several tables at once. Your log shows the pattern:

• SELECT [DealerId… (a full column list) is the initial snapshot read of a table.
• SELECT COUNT(1) … is a row-count query that runs against tables alongside the snapshot.

Six sessions (62, 57, 139, 143, 220, 207) means roughly 3-4 tables were being snapshotted concurrently, plus the count queries. Since your last batch contained all the heavy tables, they were snapshotted together, which explains the spike.

2. Why "2 processors" each?

That's almost certainly SQL Server's parallelism, not a Databricks setting. A large SELECT over a big table can get a parallel plan with DOP 2 or more, depending on your server's MAXDOP and cost threshold for parallelism. The suspended status with high cpu_time and reads fits parallel scans waiting on I/O or exchange. You can confirm it by checking sys.dm_os_tasks or sys.dm_exec_requests (dop column) for those session IDs, and by looking at wait types such as CXPACKET/CXCONSUMER.

So this is expected behavior for a snapshot of large tables. It isn't a misconfiguration, but it is something you can and should throttle on the source side.

3. How to reduce the load

Throttle on the SQL Server side. This is the most reliable lever, because it works regardless of how many sessions the gateway opens. Use Resource Governor to isolate the connector's login:

CREATE RESOURCE POOL LakeflowPool WITH (MAX_CPU_PERCENT = 20);
CREATE WORKLOAD GROUP LakeflowGroup
  WITH (MAX_DOP = 1, REQUEST_MAX_CPU_TIME_SEC = 0)
  USING LakeflowPool;
GO
CREATE FUNCTION dbo.rg_classifier() RETURNS sysname
WITH SCHEMABINDING AS
BEGIN
  RETURN (CASE WHEN SUSER_SNAME() = 'ftariq' THEN 'LakeflowGroup' ELSE 'default' END);
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Adjust the CPU cap and MAX_DOP to what production can tolerate. A lower cap makes the snapshot take longer but protects your other workloads. Test this in a lower environment first, since Resource Governor changes apply server-wide.

Control what the gateway snapshots at once.

• Onboard the heavy tables one or two at a time instead of all together. The snapshot is the expensive phase, and steady-state change capture is much lighter.
• Schedule heavy-table onboarding for off-peak hours.
• Don't add tables to the same gateway while another large snapshot is in flight.

Source-side hygiene.

• If you have a readable secondary or a restored copy, check whether your connector setup supports pointing at it. That would take the snapshot load off the primary entirely. Verify this against the SQL Server connector requirements before relying on it.
• Make sure the tables have supporting indexes and current statistics, because the snapshot SELECTs are full scans.

About limiting connections from the Databricks side. I'm not aware of a supported setting that caps the gateway's connection count or per-table parallelism. Treat that as something to confirm, not assume. If you need a hard cap, raise it with your Databricks account team or support, and rely on Resource Governor in the meantime. Splitting tables across multiple gateways won't help, since each gateway opens its own connections and the total goes up.

Recommended next step

Put the Resource Governor limits in place for the connector login, then restart the pipeline and add the heavy tables in small batches while watching sys.dm_exec_requests for the lakeflow-conne… sessions. If the snapshot phase still exceeds your limits with a 1-DOP cap, open a support case with the gateway pipeline ID and the timestamps from your session capture.