Azure Data Factory for Data Integration Design

Azure Data Factory (ADF) is a cloud-based data integration service that allows you to create, schedule, and orchestrate data workflows (pipelines) at scale. It was designed to solve the challenge of moving and transforming data across heterogeneous sources—on-premises, cloud, and SaaS—without writing complex code. ADF provides a code-free visual interface for building pipelines, but also supports code-first development via SDKs and Git integration.

ADF operates on a set of core concepts: pipelines, activities, datasets, linked services, integration runtimes (IR), and triggers.

- Pipelines: A logical group of activities that perform a unit of work. Activities can be chained with dependencies (e.g., success, failure, completion). - Activities: The actual work performed, such as Copy Data, Data Flow, Execute Pipeline, or a custom .NET activity. - Datasets: Named views of data that point to the data you want to use as input or output. They reference linked services and define structure (e.g., table name, file path). - Linked Services: Connection strings that define how to connect to external resources (e.g., SQL Server, Blob Storage, REST API). They contain credentials stored securely in Azure Key Vault or encrypted in ADF. - Integration Runtime (IR): The compute infrastructure used by ADF to perform data movement, activity dispatch, and SSIS package execution. There are three types: - Azure IR: Fully managed, scales automatically, used for cloud-to-cloud operations. - Self-hosted IR: Installed on-premises or in a VM, used for hybrid scenarios (e.g., connecting to on-premises databases). - Azure-SSIS IR: A dedicated cluster for running SQL Server Integration Services (SSIS) packages in the cloud. - Triggers: Define when a pipeline runs. Types include: - Schedule trigger: Runs at specified intervals (e.g., every 5 minutes, daily at 2 AM). - Tumbling window trigger: Runs at fixed intervals from a start time, with stateful execution (e.g., every hour, processing data for that hour). - Event trigger: Runs when a blob is created or deleted in Azure Blob Storage.

To create a pipeline using Azure CLI:

az datafactory pipeline create --factory-name MyFactory \
    --resource-group MyRG \
    --name MyPipeline \
    --pipeline @pipeline.json

To trigger a pipeline run:

az datafactory pipeline create-run --factory-name MyFactory \
    --resource-group MyRG \
    --name MyPipeline

To monitor pipeline runs:

az datafactory pipeline-run query-by-factory --factory-name MyFactory \
    --resource-group MyRG \
    --filters "[{\"operand\": \"Status\", \"operator\": \"Equals\", \"values\": [\"Failed\"]}]"

ADFActivityRun
| where Status == 'Failed'
| summarize count() by PipelineName, ActivityName

A global retailer needs to consolidate sales data from thousands of on-premises SQL Server databases in stores worldwide into Azure Synapse Analytics for reporting. The challenge: each store has a different schema version, and data must be cleansed and transformed before loading. ADF is deployed with a self-hosted IR installed on a VM in each region (e.g., North America, Europe, Asia) to handle the on-premises connections. The pipeline uses a Copy activity to stage raw data into Azure Blob Storage (Parquet format), then a Mapping Data Flow to apply schema mapping, remove duplicates, and aggregate sales by product. The pipeline runs on a tumbling window trigger every hour. In production, the self-hosted IR nodes are scaled to 8 cores each to handle peak loads. A common misconfiguration is not enabling parallel copies, causing slow data movement. The solution is to set parallelCopies to 10 and use staging copy to reduce load on the source SQL Server.

A manufacturing company streams sensor data from factory equipment to Azure Event Hubs. They need to process this data in near real-time and load it into Azure Data Lake Storage Gen2 for analytics. ADF is used with an event trigger that fires every time a new blob is created in the landing zone (Blob Storage). The pipeline runs a Data Flow to parse JSON sensor data, filter out anomalies, and write to Delta Lake format. The challenge is handling high throughput (thousands of blobs per minute). The solution uses multiple pipelines with different triggers, and the self-hosted IR is not needed because all sources are cloud-based. Performance tuning involves increasing the data flow compute size to 16 cores and setting the cluster TTL to 10 minutes to avoid cold starts. A common pitfall is hitting the concurrent pipeline run limit (100), which requires distributing pipelines across multiple factories.

A marketing agency uses Salesforce, Marketo, and Google Analytics. They need to pull data daily into Azure SQL Database for cross-channel analysis. ADF provides built-in connectors for Salesforce and Marketo. The pipeline uses a Copy activity to extract data from Salesforce (using the Salesforce connector) and Marketo (using REST API linked service). The data is then transformed using a Data Flow to join and aggregate. The schedule trigger runs at 2 AM daily to avoid business hours. The key challenge is handling API rate limits from SaaS providers. ADF's retry policy is configured with 3 retries and a 30-second interval. Additionally, the 'Enable incremental copy' option is used to only copy new records, reducing API calls. A common mistake is not setting the 'Query' property for Salesforce to filter only recent records, causing full table scans and hitting API limits.