How to truncate table in Azure Data Factory

Sometimes you have a requirement where you have to truncate the table in azure data factory. Or may you have a situation where you want azure data factory truncate the table before copy. In this post we will discuss about the all possible ways in which we can truncate the table using the azure data factory.

1. Truncate the table using the lookup activity.

2. Truncate the table using pre copy script.

3. Truncate table using Stored Procedure.

Let’s dive into each and every possible in details and see how we can truncate the table in ADF

Azure Data Factory Tutorial With Practical

Azure Data Lake Interview Questions and Answers

Truncate the table using the lookup activity

Lookup activity is usually used to get some value or look up some value. Here in the look up activity we can instead of looking or finding some value we can simply write the truncate query. When lookup activity runs it will execute your truncate sql query and remove the table from the database.

In case if you want to truncate the table before copy, you can have two activity joined sequentially. First activity could be the lookup activity which will truncate the table and other activity would be the copy activity as per your business logic.

Truncate the table using pre copy script

Second approach could be using the pre script in the copy activity. This could be good way of truncating the table before copy. In the copy activity of azure data factory you source and the sink. Source provides the data source details from where and how we need to pull the data. Sink provides the destination source details where and how we need to copy the data.

In the Sink of the copy acitivity there option to provide the pre copy script. It is the script or the query which is getting execute right before starting the copying of data from source to destination. Here you can provide the truncate table query to truncate table before copy.

Cons of pre copy script approach in Azure Data Factory

Drawback of this pre copy script is that you can’t rollback it. Assume a situation where you have truncate the table using the pre copy script however at the time of copying if some error occurred then copy activity would get failed and your pre copy activity won’t rollback the truncated data.

Truncate table using Stored Procedure.

This by far would be the best approach incase you want to truncate table before copy and in case if copy fails you want to rollback the truncate data. In this approach instead of putting the truncate query and the insert/update query in the ADF, write all these query as part of the stored procedure and call the stored procedure from the azure data factory.

Final Thoughts:

In this article we have gone thorough various ways in which we can truncate table in adf and truncate before copy in azure data factory. I hope information would have been insightful for you and it has served your purpose.