Automating SCD Type 4 in Azure SQL Database with Azure Data Factory

In my previous article, I explained how to implement Slowly Changing Dimension (SCD) Type 4 in Azure SQL Database. In this article, we will explore how to automate this process by creating an Azure SQL Database and executing the stored procedure using Azure Data Factory (ADF).

Creating an Azure SQL Database

1. Navigate to Azure Portal: Go to portal.azure.com and open the marketplace.
   
2. Search for Azure SQL Database and click Create.
3. Provide Required Details.
   • Select the appropriate Subscription and Resource Group (create a new one if needed).
   • Provide a Database Name.
   • If no existing server is available, create a new one.
     

Creating a New SQL Server

1. Provide a Server Name and choose the Server Hosting Location.
2. For authentication, select Microsoft Entra-only Authentication, as this is a best practice.
   • Why not SQL Server Authentication? SQL Server Authentication relies on username/password combinations, which pose security risks such as credential leaks and brute-force attacks. Entra authentication enhances security through multi-factor authentication (MFA) and centralized identity management.
3. Select the Entra Admin and click Create.
4. When prompted for Elastic Pool, select No.
   • What is an Elastic Pool? Elastic pools provide a cost-effective way to manage multiple databases with varying usage patterns by sharing resources among them.
5. Leave the remaining settings as default and click Review & Create.
   

Configuring Firewall Rules for SQL Server

1. Navigate to: SQL Server > Security > Networking > Public Network Access.
2. Select Selected Networks.
3. Add your Client IPv4 Address and configure Virtual Network Rules if necessary.
4. Enable "Allow Azure Services & Resources".
   • Why enable this? This setting allows services like Azure Data Factory to communicate with the database without requiring additional firewall configurations.
     

Testing SQL Server Connectivity

1. Open SQL Server Management Studio (SSMS).
2. Click Connect > Database Engine.
3. Enter the following details:
   • Server Name: <server name>
   • Authentication: Microsoft Entra MFA
   • Username: <Entra Admin Username>
4. Click Connect. Complete MFA authentication if prompted.
5. The connection should be successful.
   
   

Enabling System Assigned Managed Identity for Data Factory

To allow ADF to interact with the SQL Server securely, we will use System Assigned Managed Identity.

Step 1. Grant Data Factory Access to SQL Server.

• Ensure System Assigned Managed Identity is enabled for SQL Server under Security > Identity.
  

Step 2. Assign Access to Data Factory.

1. Navigate to Access Control (IAM).
2. Select Role Assignments > Add Role Assignment.
3. Under Assign Access To, choose Managed Identity.
4. Search for ADF’s Managed Identity (using Identity ID/Name).
5. Click Review & Assign.
   

Step 3. Configure Database Permissions.

While we have granted access to ADF at the server level, additional permissions are required for operations such as executing stored procedures, creating and dropping tables, and modifying database objects. To achieve this, we execute the following SQL commands.

• Create a user for the managed identity
• Grant read and write access
• Provide execution privileges for the stored procedure schema (Sproc)
• Allow creation, alteration, and dropping of database objects

CREATE USER [<managed-identity-name>] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [<managed-identity-name>];

ALTER ROLE db_datawriter ADD MEMBER [<managed-identity-name>];

GRANT EXECUTE ON SCHEMA::[Sproc] TO [<managed-identity-name>];

ALTER ROLE db_ddladmin ADD MEMBER [<managed-identity-name>];

These permissions ensure that ADF can execute stored procedures and perform necessary modifications to the database objects.

Creating a Linked Service in Azure Data Factory

A Linked Service establishes a connection between ADF and the database.

1. Navigate to: Manage > Linked Services > New.
2. Select Data Store: Choose Azure SQL Database.
3. Provide Connection Details.
   • Enter a Name for the linked service.
   • Choose System Assigned Managed Identity as the Authentication Type.
   • Note the Managed Identity Name & Object ID for access configuration.
4. Click Test Connection and Save.

Types of Authentication in Linked Services

1. SQL Authentication: Uses a username and password stored in ADF or Azure Key Vault. Suitable for legacy systems.
2. Managed Identity: Uses Azure’s built-in identity system to authenticate securely without storing credentials. Best for Azure services.
3. Service Principal: Authenticates using an Azure AD service principal (client ID & secret/certificate). Ideal for automated access.
   
   

Creating a Pipeline to Execute the Stored Procedure

1. Navigate to the Author Tab (Pencil Icon).
2. Click + (Create New Pipeline).
3. Drag and drop the Stored Procedure Activity onto the canvas.
4. In the Settings Tab.
   • Select the Linked Service created earlier.
   • Choose or manually enter the Stored Procedure Name.
   • If the procedure has parameters, click Import and provide values. While executing the pipeline, this value will be passed as an argument to the stored procedure. In my stored procedure, there are no parameters, so nothing was imported.
     
     

Adding a Lookup Activity to Validate Data

1. Drag and drop Lookup Activity onto the canvas.
2. In Settings.
   • Click create dataset if not created. A dataset in ADF stores metadata about a data source and allows activities such as Lookup to retrieve data.
   • Select the Linked Service and the corresponding Table.
   • Enable First Row Only.
   • Provide a SQL Query to fetch row count.
     
     

Scheduling Pipeline Execution

1. Go to Manage > Triggers > New.
2. Select a Trigger Type
   • Schedule: Runs at specific intervals.
   • Tumbling Window: Processes data in batches.
   • Event-based: Executes when a file is uploaded.
3. Configure recurrence and click OK.
4. Click Publish All to save changes (as there's no Save option without Git Integration).
   

Testing the Pipeline Execution

• Click Debug to validate the pipeline (this executes all configured actions).
• Delete a few rows from the staging table and re-execute the pipeline.
  
  
• Monitor execution under Monitor > Pipelines.
  
• Verify data changes in Dimension & History Tables.
  

Conclusion

In this article, we successfully.

1. Created an Azure SQL Database.
2. Configured firewall rules and tested connectivity.
3. Enabled Managed Identity authentication for ADF.
4. Created a Linked Service for ADF to connect to the database.
5. Scheduled a pipeline to automate stored procedure execution.

For more details on setting up a storage account and creating datafactory and moving files using ADF, refer to my previous article: Create Storage Account & Move Files Using ADF.