topic Re: How to deal with delete records from the source Files in DLT . in Data Engineering

How to deal with delete records from the source Files in DLT .

ManojReddy — Mon, 27 Nov 2023 09:54:28 GMT

Can apply_changes feature deal with deleted records in incoming source Files?
By delete I mean record is being removed (Not a soft delete with Flag).
If not, how to automate with deleting records from Bronze Streaming table based on source Files.

Re: How to deal with delete records from the source Files in DLT .

ManojReddy — Wed, 29 Nov 2023 10:49:51 GMT

APPLY CHANGES can do upserts but I have doubts regarding deleting records by key.

Does APPLY CHANGES can deletes record from bronze streaming if key is not present in source delta files.

Re: How to deal with delete records from the source Files in DLT .

Edthehead — Fri, 15 Mar 2024 08:04:24 GMT

Hi Manoj,

No. APPLY CHANGES does not delete the data from bonze if key/data is not present in source. It will delete it based on the value of some incoming field. Something like a status (="Delete") or such.

If no status can be provided from source then you will need to execute the deletes once again in each layer. Make sure that you set the skipChangeCommits flag to true so the streams ignore any deletes and updates. Streaming is append only and hence does not expect any deletes or updates in source. Link

A common example is if you are clearing out old data from the source tables, You will need to do this for all layers. DLT will not do it automatically for you.

Re: How to deal with delete records from the source Files in DLT .

2vinodhkumar — Sun, 23 Jun 2024 00:42:30 GMT

Hi Manoj,

Did you get the solution or design change for this problem. We have 200K files on to S3 bucket and when there is change in upstream app we get new feed, feed name is fixed. On DLT we should have only new records from replaced file but in dlt we have previously added records from same file name. As we don't have any status indicator on deleted records as these are events from upstream,we are unable to do apply_changes also we can't do full refresh as we have almost 200K files on one file replacement this full refresh takes time.

Re: How to deal with delete records from the source Files in DLT .

ManojReddy — Sun, 23 Jun 2024 07:29:59 GMT

Hi Vinodh,

Seems like DLT cannot handle it on its own.
I think of a solution which goes like this.

1) Maintain the copy of 200k files in a location (copied path). DLT should point to this copied path.

2)If there is any change in the incoming file. Run a process to insert the deleted records with status indicator as delete and copy this file over to copied path. For inserting the deleted records you need to compare with the file in the copied path. These you can use DLT because you have status indicator.

Basically there should be a job with runs in certain interval(ex: 10mins) which tracks the changed file based on last update date and then compare with existing file in copied path to insert the deleted records with status indicator.