In today’s data-driven landscape, securely sharing structured data with external partners is a common requirement. Whether it’s for compliance, reporting, or operational collaboration, having a reliable and automated pipeline is essential. This article walks through how to build an Azure Data Factory secure data feed that extracts data from an Azure SQL Database view, formats it for compatibility, and delivers it securely to Azure Blob Storage using a Secure Access Signature (SAS).
Why Use Azure Data Factory for Secure Data Feeds?
Azure Data Factory (ADF) is a powerful, cloud-native ETL (Extract, Transform, Load) service that enables you to:- Connect to a wide range of data sources and destinations.
- Transform data using SQL, mapping data flows, or external compute.
- Automate workflows with triggers and schedules.
- Secure data movement with managed identities, private endpoints, and SAS tokens.
Use Case: A Secure Data Feed from Azure SQL to Blob Storage
Let’s say you have a view in your Azure SQL Database — vwSecurePartnerFeed — that aggregates sensitive healthcare or business data. You need to:
- Export this data nightly.
- Format it as a pipe-delimited text file.
- Encapsulate each field in double quotes to preserve special characters like commas, tabs, or embedded quotes.
- Deliver it to a secure Azure Blob Storage container for access by an external partner.
This is a classic example of an Azure Data Factory secure data feed.
How to Build the Secure Data Feed
- Create a Linked Service to Azure SQL Database
- In ADF, create a linked service pointing to your Azure SQL Database.
- Use the view as the source dataset and import the schema.
- Create a Linked Service to Azure Blob Storage
- Set up a linked service to your partner’s Azure Blob Storage account.
- Use an SAS token to restrict access to only the necessary container and time window.
- Configure the Copy Data Activity
- Source: Select the view (vwSecurePartnerFeed) and ensure the schema is up to date.
- Sink: Choose a text file format with:
- Pipe (|) delimiter
- Quote character: “
- File path like: exports/PartnerFolder/SecurePartnerFeed_Export_@{formatDateTime(utcNow(),’yyyyMMdd’)}.txt
- Handle Embedded Pipes in Data
- In your SQL view, use REPLACE() to sanitize fields that may contain pipes:
- CONCAT(‘”‘, REPLACE(FieldWithPipesInIt, ‘|’, ‘-‘), ‘”‘) AS [Call Description]
- Secure the Output
- Use the SAS URL provided by your external partner instead of full credentials.
- Use the SAS token with read-only access and an expiration date.
- Schedule the Pipeline
- Add a trigger to run the pipeline nightly (e.g., at 2:00 AM ET).
- Monitor execution via ADF’s built-in logging and alerts.
Learn more about how to use the Copy activity in Azure Data Factory.
Benefits of an Azure Data Factory Secure Data Feed
- Data Integrity: Double-quoted fields ensure special characters don’t break the file structure.
- Automation: Nightly scheduling eliminates manual effort.
- Security: SAS tokens provide time-limited, permission-scoped access.
- Scalability: Easily extend the pipeline to include transformations or additional destinations.
