DLT Pipeline with unknown deleted source data

janglais · ‎10-22-2025

Hello.. I need help.

So the context is :

- ERP data for company in my group is stored in sql tables

- Currently, once per day we copy the last 2 months of data (creation date) from each table into our datalake landing zone (we can however do full copies if needed)

- A databricks DLT pipeline then ingests this data : append into bronze using readstream autoloader and then apply_changes upsert scd type 1 into silver

This was working fine until we realised there were rows being deleted from the source data and no flag and no reference table - i.e. the only way for us to know that a line has been deleted is to compare with the data we have.

So we want to update the silver with this information by adding a is_deleted = True flag to the rows that have been deleted.

My basic plan is based around do a weekly catch up. Every saturday we collect a full copy of all the tables.

There have then been two main attemps.

1. Compare this latest data ingestion with the current silver table

-> Immediate no go : Cannot read silver table that is the target table of the current pipeline.

2. Compare the latest data ingestion with the previous bronze data.

- At start of silver pipeline -> load bronze -> split into latest ingestion and the rest (**)

- If a line is found in previous data but not in the latest full ingestion = it has been deleted

- I added a parameter to the function such that if delete_missing_rows (signifying a full catch up)= True, it does this comparison, if= False, it does like before and just upserts the last 2 months of data.

Then i started hitting problems.

My first attempt at doing a full catch up where delete_missing_rows= True failed because the pipeline claimed that there was a schema change because there was the column "__enzyme__row__id" in the target silver table and not in the

To bypass this i do a full refresh.

This worked and i was really happy. My silver table now has flagged rows that had been deleted from the source data.

However a new problem came when i tested going back to doing a normal run (the full catch up is only once per week, the rest of the week the table is upserted into with 2 months of data).

When going back to a normal update i get the problem seen in the attachment image.png (which from what i can understand comes from the fact that ther source table which is the non temporary staging dlt.table) has changed - since there is not the same comparison and addition of deleted = true flags

I have attached the function.

Hopefully you have enough info.

Thanks in advance !