Connect to source database and delete any missing keys from data lake in Azure Synapse Notebook

Question

I am pulling data from a source system that will randomly have rows deleted and I'd like to reconcile the data lake with the source system by deleting those rows in the data lake.

The source system is a Azure SQL Managed Instance

I'm also pulling table changes by using a ROWVERSION column on the source table. This allows me to only pull updates and inserts, not deletes. I pull the source changes and deletes into my bronze layer and from there I apply those changes to the silver layer. I'm hoping I don't have to pull all the keys from the source table into the bronze layer, but that I can dynamically query the source table and delete keys that were deleted in the source in the Silver layer parquet files.

In SQL this would look like this:

DELETE FROM [DestinationDB].[DestinationSchema].[DestinationTable]
         WHERE NOT EXISTS (   SELECT NULL
                                FROM [SourceServer].[SourceDatabase].[SourceSchema].[SourceTable] elt
                               WHERE elt.[SourceKey] = 
[DestinationDB].[DestinationSchema].[DestinationTable].[DestinationKey]);

How would I connect to the source server in a Synapse Notebook and then join the Silver destination parquet file to run a delete like above?

Answer 1

Hello @Jeff Born (J&CLT-ATL) , Thanks for the question and using MS Q&A platform.

You did mention that you are using notebook . If you use pandas, you can use the Merge command .
https://pandas.pydata.org/docs/user_guide/merging.html

Thanks Himanshu

Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.