The best practice to remove old data from DLT pipeline created tables

[LasseL]

Hi, didn't find any "reasonable" way to clean old data from DLT pipeline tables. In DLT we have used materialized views and streaming tables (scd1, append only). What is the best way to delete old data from the tables (storage size increases linearly, of course new measures comes every day and old data is not deleted... yet). Let say that we want do delete all measurement which measurement timestamp is older than one month.

[Kaniz_Fatma]

Hi @LasseL, To manage old data in Delta Live Tables (DLT), use a combination of Delta Lake's features and DLT capabilities: start by using Delta Lake's `DELETE` command to remove outdated records, for example, by deleting entries older than one month. Integrate this deletion logic into your DLT pipeline with a Python function that filters out old data. Schedule regular maintenance tasks using Databricks Jobs to automate this process. After deletion, run the `OPTIMIZE` command to enhance query performance and use `VACUUM` to clean up unused files and free up storage space. These steps will help keep your storage usage in check and ensure efficient data management.

[artem2663]

This approach doesn't work with DLT, because the manual DELETE command immediately causes pipeline corruption. We have to use skipChangeCommits=True, but this option has several limitations, at least it cannot be used with apply_changes(). Is there any other approach to cleaning up old data in DLT?

[LasseL]

Exactly, this is not a "trivial problem", one possible solution is take bronze out of DLT pipeline (to manage by yourself, for example structured streaming from source with skipChangeCommits and partitioned by year/month, what ever you want to do to make delete handling optional), then define silver layer in DLT with "full refresh/overwrite". Of course this not fit in all situations. Just gave some idea.

[Rishabh-Pandey]

One effective way is to use partitioning in your Delta tables based on the timestamp of your data. This way, you can take advantage of Delta Lake’s  Vacuum command to remove old files and reduce the size of your storage.

also vacuum and optimise command can be run on delta live Table.