Remove old data in Azure Data Lake

Remove old data in Azure Data Lake

Lately I blogged about the tool bc2adls (microsoft/bc2adls: Exporting data from Dynamics 365 Business Central to Azure data lake storage (github.com)) that you export to your Business Central data to an Azure Data Lake:
From Data Archive to Azure Data Lake – Discover Microsoft Business Central (bertverbeek.nl).

But in the synapse pipelines you will preserve all your data in Azure Data Lake. So your data will be growing and growing and sometimes after couple of years you don’t need the data anymore.

But how can you delete your data in your Azure Data Lake with the Azure Synapse pipelines? In this example I delete data that is 7 years older.

Go to the Consolidation_flow in “Azure Synapse Analytics”:

Add the new filter option to the “CurentData”

In the filtering you can put the filter:
{SystemCreatedAt-2000000001} >= addMonths(currentUTC(),-84)

Now this function filter the excisting data set with the current date minus 84 month (7 years) and showing only the records from the last 7 years.
You can also do this with the column {SystemModifiedAt-2000000003} but then you have to add this column also in your ouput:

If that is OK you have to change the parameter “Union with” in the “CombineWithDeltas” section:

This step is very important. Otherwise it doesn’t work 😊.

If you now publish your pipelines you are done and the old data isn’t there anymore.

Leave a Reply

Your email address will not be published.