Designing Data Integration Solutions

Azure Data Factory (ADF) is a cloud-based data integration service that allows you to create data-driven workflows for orchestrating and automating data movement and data transformation. It was built to solve the problem of moving data between diverse sources and destinations in a scalable, reliable, and cost-effective manner, especially in hybrid environments where data may reside on-premises, in other clouds, or in various Azure services. ADF is serverless, meaning you don't provision any infrastructure; it scales automatically based on workload.

The Copy Activity is the workhorse for data ingestion. It copies data from a source to a sink. Under the hood, the Copy Activity uses the ADF copy engine, which can read from a source, optionally apply transformations (e.g., column mapping, partitioning), and write to a sink. The engine can use parallel copies (up to 256 for Azure Blob to Azure Synapse) to improve throughput. The default copy behavior is to copy as-is, but you can specify column mappings and use staging for large copies (e.g., copy from on-premises to Azure Synapse via Blob staging).

Mapping Data Flows allow you to transform data at scale without writing code. They are visually designed transformation pipelines that run on Spark clusters managed by ADF. Data Flows support transformations like Aggregate, Join, Lookup, Derived Column, Pivot, and more. They are billed based on the number of cores used per hour (minimum 8 cores, but you can scale up). Data Flows are ideal for ETL (Extract, Transform, Load) scenarios where you need to clean and reshape data before loading into a data warehouse.

A key distinction: ADF is primarily an orchestration service. It orchestrates activities that may run on other services (e.g., Azure Databricks, HDInsight, SQL Server). For heavy transformations, you can use Data Flows or call external compute. This is different from Azure Databricks, which is a compute engine for data engineering and data science, or Azure Synapse Pipelines, which are essentially the same as ADF but integrated into Synapse Studio.

The Integration Runtime (IR) is the backbone of connectivity. The Azure IR is fully managed and can access Azure endpoints over the public internet. The Self-hosted IR is installed on a Windows machine (on-premises or Azure VM) and acts as a proxy to access on-premises data sources. It must have outbound internet access to Azure, and it communicates over HTTPS (port 443). The Azure-SSIS IR is a dedicated cluster of Azure VMs that run SSIS packages; it can be scaled up to 100 nodes and supports custom setup scripts.

ADF supports various authentication methods: Azure AD managed identity, service principal, access keys, SQL authentication, and OAuth. For on-premises sources, the Self-hosted IR uses Windows authentication or SQL authentication. Data is encrypted in transit using TLS (minimum 1.2). For storage accounts, you can use firewall rules and virtual network service endpoints to restrict access. ADF can also use Azure Key Vault to store secrets (e.g., connection strings, passwords) and reference them in linked services.

ADF provides built-in monitoring in the Azure portal and via Azure Monitor. You can view pipeline runs, activity runs, and trigger runs. For each run, you can see duration, status, and error details. You can also set up alerts on failed runs or long-running pipelines. The monitoring experience includes a visual timeline and the ability to rerun failed activities. For advanced monitoring, you can send logs to Log Analytics and create custom dashboards.

ADF pricing is based on the number of operations (pipeline runs, activity runs) and the compute used for Data Flows and Azure-SSIS IR. Copy Activity runs are charged per DIU (Data Integration Unit) hour — each DIU is a combination of CPU, memory, and network resources. The default DIU is 4, but you can set a maximum. Data Flows are charged per vCore-hour. Self-hosted IR has no additional cost beyond the VM it runs on. Triggers are charged per execution. There is also a charge for Azure-SSIS IR based on the VM size and number of nodes.

ADF integrates deeply with Azure DevOps and GitHub for CI/CD. You can export pipelines as ARM templates and deploy them via Azure Pipelines. It also integrates with Azure Purview for data lineage and cataloging. For real-time scenarios, you would use Azure Stream Analytics or Event Hubs with Azure Functions, not ADF, as ADF is batch-oriented.

# Get a list of pipelines
Get-AzDataFactoryV2Pipeline -ResourceGroupName "rg" -DataFactoryName "adf"

# Trigger a pipeline run
Invoke-AzDataFactoryV2Pipeline -ResourceGroupName "rg" -DataFactoryName "adf" -PipelineName "pipeline"

# Monitor a pipeline run
Get-AzDataFactoryV2PipelineRun -ResourceGroupName "rg" -DataFactoryName "adf" -PipelineRunId "run_id"

# Azure CLI equivalent
az datafactory pipeline list --resource-group rg --factory-name adf
az datafactory pipeline create-run --resource-group rg --factory-name adf --pipeline-name pipeline
az datafactory pipeline-run show --resource-group rg --factory-name adf --run-id run_id

A large retail company needs to move daily sales data from an on-premises SQL Server to Azure Synapse Analytics for reporting. They deploy a Self-hosted Integration Runtime on a Windows VM in their data center. They create a linked service for SQL Server (using SQL authentication) and another for Azure Synapse (using managed identity). A pipeline with a Copy Activity runs nightly via a schedule trigger (12 AM). To handle large volume (500 GB), they enable staging copy: data is first copied to Azure Blob Storage (intermediate), then loaded into Synapse using PolyBase for high throughput. They set the copy DIU to 64 for parallelism. They monitor using ADF alerts and Log Analytics. Common issues: network latency causing timeouts (solved by increasing DIU or using parallel copy) and authentication failures (resolved by rotating passwords in Key Vault).

A financial services company ingests real-time transaction logs from multiple sources into Azure Data Lake Storage Gen2. They use Azure Event Hubs to capture streaming data, then use a storage event trigger in ADF to process files as they land. ADF runs a Data Flow to parse JSON, apply schema validation, and enrich with lookup data from Azure SQL Database. The transformed data is written to Parquet files in a curated zone. They set the Data Flow compute to 16 cores to handle burst loads. They use Azure DevOps for CI/CD to deploy changes. Misconfiguration: if the trigger fires too frequently (every few seconds), it overwhelms the pipeline — they add a tumbling window trigger instead to batch files every 5 minutes.

A manufacturing company uses ADF to orchestrate an ETL process that involves heavy transformations (e.g., time-series aggregation, machine learning scoring). They use a pipeline with a Databricks Notebook activity that runs a Python script on a Databricks cluster. ADF copies raw data from IoT Hub to Blob Storage, then triggers the Databricks notebook, which reads the data, transforms it, and writes to Azure SQL Database. ADF then runs a stored procedure to update reporting tables. This hybrid approach leverages ADF's orchestration and monitoring while offloading compute-intensive work to Databricks. They use a Self-hosted IR for on-premises data sources (e.g., legacy databases). Performance bottleneck: the Databricks cluster startup time (2-3 minutes) — they use a cluster pool to reduce cold start latency.