topic Re: Deleting Records from DLT Bronze and Silver Tables in Data Engineering

Deleting Records from DLT Bronze and Silver Tables

Gianfranco — Wed, 25 Sep 2024 15:01:07 GMT

I have a pipeline that generates two DLT streaming tables: a Bronze table and a Silver table. I need to delete specific records from both tables. I've read an article (https://www.databricks.com/blog/handling-right-be-forgotten-gdpr-and-ccpa-using-delta-live-tables-dlt) suggesting that with two streaming tables, the only option is to delete the data in the Bronze table and then perform a full refresh of the Silver table.

However, the Silver table is very large, and I'd like to avoid a full refresh. Are there any alternative solutions available?

Thanks

Re: Deleting Records from DLT Bronze and Silver Tables

Brahmareddy — Wed, 02 Oct 2024 03:58:12 GMT

Hi @Gianfranco,

How are you doing it today?

As per my understanding, Consider using a selective delete approach on both the Bronze and Silver tables to avoid a full refresh. Instead of deleting data and refreshing the entire Silver table, you could delete the specific records in both tables by writing an appropriate delete query directly against each Delta table. This way, you're only removing the required records without needing to rebuild the Silver table from scratch. Another option might be to implement Change Data Capture (CDC), so you can track and update only the affected records, reducing the need for full table operations. Additionally, explore using vacuum or optimize commands to maintain table performance after deletions.

Give a try and see if it works.

Good day.

Regards,

Brahma

Re: Deleting Records from DLT Bronze and Silver Tables

filipniziol — Wed, 02 Oct 2024 06:40:04 GMT

Hi @Gianfranco ,

Try APPLY CHANGES. This will work well in your scenario, as it supports DELETE operation as well:
https://docs.databricks.com/en/delta-live-tables/cdc.html

Re: Deleting Records from DLT Bronze and Silver Tables

karthickrs — Sat, 28 Dec 2024 04:29:44 GMT

Remove records using the DELETE operation in both Bronze & Silver tables.

After doing each delete step, you can Optimize the table which rewrites the parquet files for that table behind the scenes to improve the data layout (Read more about optimize here: https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/delta-optimize). It is a simple command: OPTIMIZE <Table Name>

Also, as you move on in this process, you can run the VACUUM command to clean up old versions of the data and free up storage space: https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/delta-vacuum

Hope this helps.