Lakaflow connect Data Ingestion gatway pipeline fail often and restarts automatically

[srikanthp24]

Hello Team,

I have designed Lakeflow connect data ingestion pipeline using managed connector SQL Server. The source is SQL Server created the connection and Change Tracking enabled at the source level. Also, I have give required permission and everything. I am able to create the pipeline successfully which consists one gateway pipeline to capture the changes (Source > Staging layer) and ingestion pipeline(Staging > destination tables). It is running perfectly fine capture the continous changes and update the target table using ingestion pipeline. But My Gateway pipeline fails after running for some hours and it will restart automatically but not immediately, It will continouslu try up and run during that also it will fail multiple time. Some times the error says hostname and port is wrong. 

[DLT ERROR CODE: INGESTION_GATEWAY_CONNECTION_ERROR.SOURCE_CONNECTION_FAILURE]

[DLT ERROR CODE: INGESTION_GATEWAY_CONNECTION_ERROR.SOURCE_CONNECTION_FAILURE] 
After some time it will up. I am unable to understand why it is happening. How we can fix this issue. We don't have any notification option also keep track of the failure for gateway pipeline. Even though gateway pipeline fails. My Ingestion pipeline will run successfully that is running interval of every 30 minutes.

[ivanvyd]

@srikanthp24 the 15-minute delay is consistent with Lakeflow Connect’s automatic exponential-backoff retry. A successful ingestion pipeline run does not confirm that the gateway is healthy; it may only be processing data already written to staging.

Open the failed gateway update under Jobs & Pipelines. Use View event log for the structured error, then open Update details -> Logs and inspect the driver `stdout`. Search for `SOURCE_CONNECTION_FAILURE` and follow the `Caused by` chain to the first specific JDBC, DNS, TCP, TLS, or authentication error.

SELECT timestamp, level, event_type, message, error, details
FROM event_log('<gateway-pipeline-id>')
WHERE level IN ('ERROR', 'WARN')
ORDER BY timestamp DESC
LIMIT 100;

I would let the gateway recover automatically while checking whether the failures coincide with SQL Server maintenance, firewall changes, DNS issues, or network timeouts.

Please, let us know what you found.

[szymon_dybczak]

Hi @srikanthp24 ,

The error is more likely caused by intermittent connectivity between the Lakeflow Connect gateway and SQL Server.

Lakeflow Connect SQL Server gateway runs continuously on Databricks classic compute. Therefore, the network path from the Databricks classic-compute VPC/VNet to SQL Server must remain continuously available.

I would check thefollowing:

1. Verify DNS resolution of the SQL Server hostname from the Databricks classic-compute network. If the hostname resolves to multiple IP addresses, confirm that all IP addresses are reachable.

2. Verify firewall/security-group/NSG rules for all Databricks subnets where the ingestion gateway compute can be created. Make sure TCP connectivity to the SQL Server port is consistently allowed.

3. Check VPN, VPC/VNet peering, PrivateLink/private endpoint, Direct Connect/ExpressRoute, NAT gateway, or other networking components between Databricks and SQL Server for intermittent failures.

4. Compare the exact gateway failure timestamps with SQL Server connection/error logs. If SQL Server does not see an incoming connection during the failure, the problem is most likely in DNS/networking. If SQL Server sees and rejects the connection, investigate SQL Server-side limits, TLS, authentication, or listener configuration.

5. If SQL Server uses a named instance or dynamic port, consider using a static TCP port and stable DNS name.

6. Is your SQL Server behind Availability Groups? Maybe AG failover happens?

[data_pulse]

@srikanthp24

I have faced same issue back in the days when doing Lakeflow connect from SQL Server using CDC. 

It was found the root cause was one of the underlying table which we tried to ingest from source system has got huge volumes. It worked fine for small and medium volume tables, but for big tables had faced the Source Connection Failure issue when It was ingested for the first time as it processes all the records in the table. The logs doesn't help much to identify the possible root cause. Have restarted the pipeline many times but fails mid way with same SOURCE_CONNECTION_FAILURE.

So, we reached out to databricks support and they changed chunking flag in the background which allowed to do the first heavy load in chunks of batches. Subsequently, it ingested fine for incremental data once the heavy load has been pulled across.

[srikanthp24]

Hi @data_pulse ,

Thank you for sharing your experience! In our case, the initial load was successful and the pipeline ran perfectly fine for 3 days without any issues. It then suddenly failed and the pipeline automatically up and running it is going on. I just want to stop completely this fail. It quiet happening often

When the CDC extractor tries to get a response from the source and stops receiving one, it waits for some time before failing and re-initiating the pipeline with a new update_id.

NORMAL
│
▼
Databricks connected to SQL Server
│
▼
CT extraction progressing
│
▼
all 14 tables near real time
│
▼
intermittent issue occurs
│
▼
SQL / network path stops responding properly
│
▼
all CT table watermarks freeze together
│
▼
Databricks cluster still alive and healthy
│
▼
lag increases minute by minute
│
▼
~15 min in many incidents
│
▼
SOURCE_CONNECTION_FAILURE
│
▼
pipeline update marked FAILED
│
▼
old JVM gets SIGTERM
│
▼
RETRY_ON_FAILURE starts
│
▼
new SQL connection succeeds
│
▼
resume from committed CT position
│
▼
process accumulated backlog
│
▼
CAUGHT UP

Based on the logs, we are unable to draw a clear conclusion on the root cause. I believe it is more of a network issue rather than a volume related problem since the initial load completed successfully.

[data_pulse]

Thanks for info and bit more insights into the issue @srikanthp24.

If it's not the issue with initial load but the incremental ingestion, is it just small volumes being ingested across in the 14 tables? Could you check the staging volume folders/query the volume/ event_log tables to see the metadata and number of records being pulled across from CDC?

On the pipelines details page doe it show anything like Snapshot/ Incremental? If it's incremental then should be fine after first full snapshot has been ingested.

Regarding the Connection Failure error, I agree that logs weren't conclusive enough to identify the root cause as the retry with exponential backoff is default mechanism of the pipeline on failures, but it could possibly be Network / congestion issue as you said while doing the ingestion. Also worth checking the connection happening in SQL server side to see how much time it runs and if any heavy load as well happening.

[srikanthp24]

Hello  

1. Gateway Pipeline Status:
The gateway pipeline has been running continuously for the last 20 hours without any failures. You can see the metrics in the attached screenshot, which shows the number of rows upsert and deleted.

2. Volume Folder Structure:
I checked the staging volume folders and have attached the sub folder structure for your reference. I did not find any folder specifically named "Incremental".

3. Pipeline Architecture:
We have two pipelines in place:

• Gateway Pipeline — Captures changes from the source (CDC) and loads them into the staging layer. This runs continuously.
• Ingestion Pipeline — Loads data from staging to the target tables. This runs every 30 minutes.

4. Tables:
We have 14 tables in total — each line in the metrics represents one table.

I hope this gives a better picture of our setup. Please let me know if you need any additional information to investigate further.

Note : This following Change Tracking Mechanism

[data_pulse]

@srikanthp24 
Looking at the details, it does look like minimal volume.

I could check on the chunking setting but is not available at the pipeline level currently. It make sense to raise this to Databricks as Support ticket for triaging further by looking at the actual pipeline logs and the set up and figure out if it can be optimized any further.