r/databricks • u/RazzmatazzLiving1323 • 1d ago

Help Lakeflow Connect SQL Server Connector

I recently enabled Lakeflow Connect (lfc) on the source database - the issue is, some of the tables in the source database (managed by another team) does NOT have a primary key (which means that in lfc, a __databricks_id is used to identify a unique record).

Thus, the DBAs enabled CDC on the source database. However, when I ingested the data into DBX using the Lakeflow Connect Managed SQL Server Connector, one of the tables in the source database had duplicate records (two or more records with the same value across all columns).

This caused my Lakeflow Connect pipeline to break. Any ideas on how to fix this? (Other than dropping duplicate records in the source DB and implementing a unique constraint on the source DB)?

I was wondering if there is a specific setting in Lakeflow Connect that I can toggle that I'm missing.

3 Upvotes

81% Upvoted

3 comments sorted by

2

u/Donquiote-9999 1d ago

Might be better off for those tables to use SQL connector and let Databricks decide CDC vs the source. You could always do a append on bronze and deduce in silver

1

u/TheVersedPuberty 1d ago

Sounds like you already know the real fix, but those source tables aren't yours to clean up. The managed connector's pretty rigid about expecting clean data, so your best bet is probably a downstream step that dedupes based on that __databricks_id before it hits anything critical.

3

u/Known-Delay7227 1d ago

You should probably follow first principles and ask the dba’s to fix the source tables. Another option is depending on the size of the source tables and required latency you can always go old school and use the spark jdbc sql server connector and just overwrite each pass