Lesson: Azure Data Factory (ADF) Design Patterns

1. Introduction

In modern data engineering, building robust, scalable, and maintainable data pipelines is critical. Azure Data Factory (ADF) is a cloud-based ETL and data integration service that allows you to create data-driven workflows for orchestrating data movement and transforming data at scale.

However, simply connecting sources to sinks is insufficient for enterprise-grade solutions. Design Patterns are reusable solutions to commonly occurring problems in pipeline architecture. By adopting established patterns, you reduce development time, improve reliability, and ensure your data integration layer can handle the complexities of evolving business requirements.

2. Core ADF Design Patterns

A. The "Metadata-Driven" Pipeline Pattern

Instead of hardcoding pipeline activities for every table or file, the metadata-driven pattern uses a control table (stored in Azure SQL or Cosmos DB) to define the scope of data movement.

How it works:

1. A Lookup activity queries a database table containing source/sink metadata (e.g., table names, schema, watermarks).
2. A ForEach activity iterates through the list of tables.
3. Inside the loop, an Execute Pipeline or Copy Activity uses dynamic expressions to map the metadata to the connection strings or file paths.

Example Dynamic Expression: If you are iterating through a list of tables, you can dynamically reference the source table in your Copy Activity source dataset:

// Dynamic expression for source table name
@item().SourceTableName

B. The "Incremental Load" (Watermark) Pattern

Loading full datasets every day is inefficient and costly. The Incremental Load pattern ensures only new or modified data is processed.

How it works:

1. Lookup Old Watermark: Fetch the last processed timestamp from a control table.
2. Lookup New Watermark: Fetch the MAX(LastModifiedDate) from the source system.
3. Copy Activity: Use a query filter: SELECT * FROM Orders WHERE LastModifiedDate > '@{activity('OldWatermark').output.firstRow.WatermarkValue}' AND LastModifiedDate <= '@{activity('NewWatermark').output.firstRow.NewValue}'.
4. Stored Procedure: Update the control table with the new watermark value.

C. The "Hub-and-Spoke" Orchestration Pattern

In large organizations, you often have a "Master" pipeline that triggers "Child" pipelines. This promotes modularity.

• Master Pipeline: Handles global logic, such as error logging, variable initialization, and controlling the sequence of execution.
• Child Pipelines: Perform specific, reusable tasks (e.g., "Ingest Salesforce Data," "Clean Customer Dim," "Archive Logs").

3. Practical Implementation: Dynamic Copy

To implement a dynamic pattern, you must use Parameterized Datasets.

Step 1: Create a Dataset Parameter In your dataset, go to the Parameters tab and add SchemaName and TableName.

Step 2: Use the parameter in the Connection string In the Connection tab, use the dynamic content editor:

@dataset().SchemaName
@dataset().TableName

Step 3: Call from Pipeline When using the Copy Activity, pass these values from your ForEach loop:

// Inside ForEach activity
"typeProperties": {
    "dataset": {
        "referenceName": "SourceTableDataset",
        "type": "DatasetReference",
        "parameters": {
            "SchemaName": "@item().Schema",
            "TableName": "@item().Table"
        }
    }
}

4. Best Practices & Common Pitfalls

Best Practices

• Use Parameterization: Never hardcode connection strings, file paths, or table names. Use ADF Parameters and Global Parameters.
• Implement Error Handling: Always connect a "Failure" path (red arrow) from your activities to a logging or notification activity (e.g., Send an email via Logic Apps or log to a SQL table).
• Self-Hosted Integration Runtime (SHIR) Scaling: If moving massive amounts of data from on-premises, ensure your SHIR is installed on a machine with sufficient CPU/RAM and consider high-availability clusters.
• Source Control (Git): Always integrate your ADF instance with Azure DevOps or GitHub. Never develop directly in the "Live" mode for production environments.

Common Pitfalls

• Over-complicating Logic: If your pipeline has 50+ activities, it is too complex. Break it down into smaller, child pipelines.
• Ignoring Cost: The "ForEach" activity, when configured with isSequential: false, runs in parallel. If you have 100 iterations, you may hit your source system's throughput limits or trigger unexpected costs.
• Hardcoding Credentials: Never put passwords or API keys in pipeline strings. Always use Azure Key Vault linked services to reference secrets.

💡 Pro-Tip: The "Wait" Activity

When dealing with dependencies (e.g., waiting for a file to land in Data Lake), avoid using a "Wait" activity. Instead, use an Until activity coupled with a Get Metadata activity to check for the file's existence. This is more efficient and reliable.

5. Key Takeaways

1. Modularity is Key: Design your pipelines to be reusable. If you find yourself copying and pasting an activity block, it should be a child pipeline.
2. Metadata is Power: Use metadata-driven patterns to allow your ADF environment to scale automatically as you add more data sources without needing to manually create new pipelines.
3. Security First: Always use Managed Identities and Azure Key Vault. Avoid passing plain-text credentials at all costs.
4. Observability: Your design should always include logging. If a pipeline fails, you should know exactly which step failed and why, without needing to manually inspect the ADF UI.
5. Performance: Monitor your Data Integration Units (DIUs) and SHIR performance. Parallelism is good, but throttle it based on the constraints of your source and sink systems.