Implement a Data Retention Policy – Keeping Data Safe and Secure

1. Log in to the Azure portal at https://portal.azure.com ➢ navigate to the Azure Synapse Analytics workspace you created in Exercise 3.3 ➢ select the SQL Pools navigation menu link ➢ select your dedicated SQL pool ➢ start the SQL pool ➢ select the Overview blade ➢ and then click the Open link in the Open Synapse Studio tile.
2. Navigate to the Data hub ➢ select the ellipse (…) to the right of your dedicated SQL pool ➢ select New SQL script from the pop‐up menu ➢ select Empty Script ➢ and then execute the following command. The command text is available in the uspApply90DayRetentionPolicySubjects.sql file in the Chapter08/Ch08Ex06 directory on GitHub.
   CREATE PROCEDURE dbo.uspApply90DayRetentionPolicySubjects

AS DELETE FROM dbo.SUBJECTS WHERE CREATE_DATE>DATEADD(DAY, -90,GETDATE()) GO

3. Expand your dedicated SQL pool ➢ expand the Programmability folder ➢ expand the Stored Procedures folder ➢ select the ellipse to the right of the stored procedure you created in step 2 ➢ select Add to Pipeline from the pop‐up menu ➢ select New Pipeline from the pop‐out menu ➢ enter a name (I used Subjects 90 day Retention Policy) ➢ select the Settings tab ➢ click Commit ➢ click Publish ➢ and then click OK. The configuration should resemble Figure 8.32.

FIGURE 8.32 Implement a data retention policy in Azure Synapse Analytics.

4. Click the Add Trigger button ➢ select New/Edit ➢ select + New from the Add Triggers drop‐down list box ➢ configure the scheduled task to run on the first day of every month, similar to that shown in Figure 8.33 ➢ click the Commit button ➢ and then publish the trigger.

FIGURE 8.33 Implement a data retention policy schedule pipeline trigger.

5. Stop the dedicated SQL pool.

Exercise 8.6 begins with the creation of a stored procedure that removes data from the SUBJECTS table. The WHERE clause checks if the date contained in the CREATE_DATE column is older than 90 days. If it is, then the row is deleted. You then added that stored procedure to an Azure Synapse Analytics pipeline using the SQL pool Stored Procedure activity. Once committed and published, you configured a schedule trigger to run the stored procedure once per month. This results in the deletion of data based on a 90‐day retention. You might consider not committing and publishing the trigger, unless you really want to implement the retention policy.