How to setup data retention for Azure synapse analytics?

Question

How do we delete/archive data older than certain time period in Azure synapse Analytics?

Answer 1

Hello Jaswanth ,

To delete record you can use the the Stored proc activity , which will initiate a proc on synapse . I took some dummy data .

Create table  LetsDeleteSomeData   
(  
SomeData varchar(100)  
,insertedDate datetime   
)  

Insert some data

INSERT INTO LetsDeleteSomeData VALUES ('John','2020-07-30 22:02:46.223')  
INSERT INTO LetsDeleteSomeData VALUES ('James','2020-06-30 22:02:46.223')  
INSERT INTO LetsDeleteSomeData VALUES ('David','2020-05-30 22:02:46.223')  
INSERT INTO LetsDeleteSomeData VALUES ('Don','2020-04-30 22:02:46.223')  

Create a proc which we will call from the pipeline .

CREATE PROC proc_DeletfromTable   
AS   
DELETE from LetsDeleteSomeData    
where insertedDate > dateadd(day,-60,getutcdate())   

On the workspace you can play with the stored proc activity and point that to the proc which we created .

On the other ask about migration , if you are planning to move from a table1 to table2 you can still use the stored proc activity . but if you are planning to move data out ( eg. container etc ) then you can use the copy activity .

I think that for the archive thing to implement correctly , we will copy the stale/old data and delete the data from the tables , you will have to use both . You can chain these two in one pipeline .

Let me know if you have further questions .

Thanks Himanshu

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members