Azure Data Engineering & Synapse / OpenAIModule 2 of 9

Azure Data Factory (ADF): Complete Activity Encyclopedia, UI Drag-and-Drop & Self-Hosted IR

Master enterprise pipeline orchestration: ADF Studio visual canvas drag-and-drop, complete catalog of 26 activities, Copy Activity throughput tuning (DIU, partitions, staging), and Self-Hosted Integration Runtime (SHIR) high-availability clustering.

Azure Data Factory (ADF): Complete Activity Encyclopedia, UI Drag-and-Drop & Self-Hosted IR

[!NOTE] Azure Data Factory (ADF) is Azure's premier cloud-native serverless data integration and workflow orchestration service. Unlike legacy code-only systems, ADF provides both a visual drag-and-drop UI canvas and a fully declarative JSON representation behind every activity.

1. Visual Drag-and-Drop Studio Canvas: Real-World Workflow

In ADF Studio (adf.azure.com), data pipelines are constructed visually on a drag-and-drop canvas:

Left Navigation Panel:
- Linked Services: Connection definitions with credentials (e.g. Key Vault, On-prem SQL Server via SHIR, ADLS Gen2).
- Datasets: Schema and location pointers (e.g. ds_oracle_orders, ds_adls_orders_parquet).
- Pipelines: The workflow DAG holding activities.
Authoring Canvas:
- Engineers drag activities from the Activities tool palette onto the designer grid.
- Nodes are connected via Activity Dependency Conditions:
  - Green (Success): Runs only if preceding activity succeeds.
  - Red (Failure): Runs if preceding activity fails (used for alerting & error handling).
  - Blue (Completion): Runs regardless of whether preceding activity succeeded or failed.
  - Gray (Skipped): Runs if preceding activity was skipped.

code

┌─────────────────┐       Succeeded       ┌─────────────────┐       Succeeded       ┌─────────────────────┐
│ LookupActivity  │──────────────────────▶│  IfCondition    │──────────────────────▶│   ForEachLoop       │
│ (Get Watermark) │                       │ (Files Exist?)  │                       │ (Parallel Batches)  │
└─────────────────┘                       └─────────────────┘                       └──────────┬──────────┘
         │ Failed                                                                              │
         ▼                                                                                     ▼
┌─────────────────┐                                                                 ┌─────────────────────┐
│   WebActivity   │ (Teams / Slack Incident Alert)                                  │    CopyActivity     │
│ (Alert Webhook) │                                                                 │ (Stage to ADLS Gen2)│
└─────────────────┘                                                                 └──────────┬──────────┘
                                                                                               │ Succeeded
                                                                                               ▼
                                                                                    ┌─────────────────────┐
                                                                                    │ Databricks Notebook │
                                                                                    │ (Delta Lake MERGE)  │
                                                                                    └─────────────────────┘

2. Complete ADF Activities Encyclopedia (Categorized)

Category	Activity Name	Production Role & Architectural Guidance
Data Movement	Copy Activity	Moves data between 100+ connectors. Supports DIU scaling, parallel partition queries, staging, and compression.
Control Flow	Lookup Activity	Reads up to 5,000 rows (max 4MB). Fetches control tables, high watermarks, and dynamic configs.
Control Flow	Get Metadata Activity	Inspects folder childItems, file size, exists flag, and lastModified without reading payload.
Control Flow	ForEach Activity	Iterates over array. Supports parallel (up to 50 concurrent items via `batchCount`) or sequential execution.
Control Flow	If Condition Activity	Conditional branch evaluator using dynamic expressions (e.g. `@greater(activity('GetMetadata').output.childItems, 0)`).
Control Flow	Until Activity	Loops activities until expression evaluates to true (polling long-running external tasks).
Control Flow	Switch Activity	Multi-branch routing based on case values (e.g. file format routing: CSV vs JSON vs XML).
Control Flow	Filter Activity	Filters array items using condition expressions before feeding to ForEach.
Control Flow	Set Variable / Append	Manages mutable runtime variables inside pipeline execution context.
External Compute	Databricks Notebook	Dispatches PySpark/Delta Lake jobs on interactive or ephemeral automated job clusters.
External Compute	Stored Procedure	Executes T-SQL stored procedures on Azure SQL or Synapse (used to update watermark tables).
External Compute	Web / Webhook	Web makes synchronous HTTP REST calls (e.g. alerting). Webhook waits for asynchronous callback with callbackUri.
External Compute	Azure Function	Executes custom serverless Python/C# code for complex parsing or token generation.
Transformation	Mapping Data Flow	Visual, code-free Spark transformations executed on managed behind-the-scenes Spark clusters.

3. Self-Hosted Integration Runtime (SHIR): Hybrid Enterprise Gateway

When your pipeline needs to pull data from on-premise databases (Oracle, SQL Server, IBM DB2) or private VNET resources:

No Inbound Firewall Holes: The SHIR agent is installed on an internal Windows VM and communicates with Azure Service Bus strictly via Outbound Port 443 (HTTPS).
High Availability & Scale-Out: A single SHIR cluster can join up to 4 worker nodes. ADF automatically load-balances concurrent pipeline requests across available nodes.
Credential Encryption: When configuring linked services, database credentials are encrypted using DPAPI on the local SHIR machine or fetched dynamically from Azure Key Vault via private endpoint.

Staff Architecture: ADF Integration Runtimes, DIU/TTL Tuning & Dynamic Metadata Pipelines

Copy Activity vs Mapping Data Flows (Cost Control): Use ADF Copy Activity (DIU auto or tuned 4–32) strictly for raw binary/tabular movement into Bronze ADLS Gen2; never put complex joins inside ADF Mapping Data Flows when you already have Databricks or Microsoft Fabric Spark clusters running—spinning up Data Flow Spark clusters for 5-minute runs adds massive cold-start overhead unless a long-lived TTL (Time To Live) pool is justified.
Metadata-Driven Frameworks (Lookup + ForEach): Production Azure teams avoid authoring 200 identical pipelines. Store table watermarks, source queries, and target schemas in an Azure SQL Control Table, then drive a single parameterized Lookup -> ForEach(batchCount=20) pipeline.
Self-Hosted IR HA: Always deploy at least 2 nodes in a Self-Hosted Integration Runtime (SHIR) cluster for on-prem SQL Server/Oracle extraction so OS patching or node reboots never break overnight SLAs.

Continue Mastering This Module

Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.

Practice in Interview Arena

Complete ADF Dynamic Pipeline JSON: Lookup -> ForEach -> Copy -> Databricks

json

{
  "name": "pl_dynamic_incremental_ingest",
  "activities": [
    {
      "name": "Lookup_Watermark",
      "type": "Lookup",
      "typeProperties": {
        "source": {
          "type": "AzureSqlSource",
          "sqlReaderQuery": "SELECT SourceTable, LastWatermark, TargetFolder FROM dbo.etl_control_table WHERE IsActive = 1;"
        },
        "dataset": { "referenceName": "ds_control_db", "type": "DatasetReference" },
        "firstRowOnly": false
      }
    },
    {
      "name": "ForEach_SourceTable",
      "type": "ForEach",
      "dependsOn": [{ "activity": "Lookup_Watermark", "dependencyConditions": ["Succeeded"] }],
      "typeProperties": {
        "isSequential": false,
        "batchCount": 20,
        "items": "@activity('Lookup_Watermark').output.value",
        "activities": [
          {
            "name": "Copy_Table_To_ADLS",
            "type": "Copy",
            "typeProperties": {
              "source": {
                "type": "SqlServerSource",
                "sqlReaderQuery": "@concat('SELECT * FROM ', item().SourceTable, ' WHERE ModifiedDate > ''', item().LastWatermark, '''')"
              },
              "sink": {
                "type": "ParquetSink",
                "storeSettings": {
                  "type": "AzureBlobFSWriteSettings",
                  "copyBehavior": "FlattenHierarchy"
                }
              },
              "enableStaging": false,
              "dataIntegrationUnits": 32
            }
          }
        ]
      }
    }
  ]
}

Senior Architect Note: Production dynamic metadata-driven ADF pattern: Lookup reads control table, ForEach executes 20 concurrent table slices with parameterized queries and 32 DIUs.

PowerShell: Automate High Availability SHIR Multi-Node Registration

powershell

# Execute on On-Premises Windows Gateway VM to join active SHIR cluster
$AdfResourceGroup = "rg-data-engineering-prod"
$AdfFactoryName = "adf-enterprise-prod"
$ShirName = "shir-onprem-cluster"

# Retrieve SHIR Auth Key from Azure
$AuthKey = (Get-AzDataFactoryV2IntegrationRuntimeKey `
    -ResourceGroupName $AdfResourceGroup `
    -DataFactoryName $AdfFactoryName `
    -Name $ShirName).AuthKey1

# Register node into the 4-node high availability pool
& 'C:\Program Files\Microsoft Integration Runtime\6.0\PowerShellScript\RegisterIntegrationRuntime.ps1' `
    -gatewayKey $AuthKey `
    -nodeName "SHIR-NODE-02" `
    -enableRemoteAccessIntoHostLocalNetwork $true

Write-Host "SHIR Node 02 successfully joined high-availability cluster."

Senior Architect Note: Automates the registration of node 2 into a multi-node high-availability SHIR cluster using Azure PowerShell.

Interview Question #1Asked at Accenture

What is the difference between the Lookup Activity and the Get Metadata Activity in Azure Data Factory, and what are their payload limitations?

Architectural Answer:The Lookup Activity reads data contents from a dataset or SQL query into memory, returning either a single row or up to 5,000 rows with a strict 4MB maximum payload size limit. If the query exceeds 5,000 rows or 4MB, the activity fails. Lookup is designed for fetching metadata, configuration tables, and watermarks. In contrast, the Get Metadata Activity inspects filesystem and storage object properties without parsing file contents. It returns properties like childItems (list of files in a directory), exists (boolean), size (byte size), lastModified date, and column structure. It is significantly faster and cheaper than Lookup when verifying whether files have arrived in a storage container.

Staff Hiring Bar: Candidate cites exact limitations (5,000 rows / 4MB limit on Lookup) and explains why Get Metadata is preferred for directory presence checks.

Interview Question #2Asked at Infosys

How do you build a metadata-driven (dynamic) pipeline in ADF instead of creating hundreds of individual pipelines for each table?

Architectural Answer:In a metadata-driven architecture: 1) Maintain an Azure SQL Control Table storing table names, source connection details, primary keys, watermark dates, and active flags. 2) In ADF, use a Lookup Activity with `firstRowOnly: false` to select all active rows from the control table. 3) Pass the array output (`@activity('Lookup').output.value`) into a ForEach Activity. 4) Inside ForEach, parameterize a single generic Copy Activity using `@item().TableName` in the source query and target ADLS Gen2 path (`abfss://lakehouse/bronze/@{item().TableName}/`). 5) On success of each copy, call a Stored Procedure activity to update the watermark in the control table. This single pipeline can process 500+ tables dynamically without creating individual pipelines.

Staff Hiring Bar: Candidate outlines the 5-step control table -> Lookup -> ForEach -> parameterized Copy -> watermark update lifecycle.

Interview Question #3Asked at TCS

What are Data Integration Units (DIU) in ADF Copy Activity, and how do you optimize throughput when copying terabytes of data?

Architectural Answer:A Data Integration Unit (DIU) is a combination of CPU, memory, and network resource allocation representing compute power used by the Copy Activity (ranging from 4 to 256 DIUs). To optimize performance for terabyte-scale copies: 1) Increase DIU sizing from default auto/4 up to 32, 64, or 128 depending on payload volume. 2) Configure 'Degree of Copy Parallelism' to execute concurrent reads. 3) Enable source partitioning (e.g. Physical Partitions of Table or Dynamic Range slicing on integer/date columns) so multiple threads read different database chunks concurrently. 4) For cloud sinks like Synapse or Snowflake, enable staging in Blob storage and use bulk mechanisms (PolyBase, COPY INTO) rather than row-by-row batch inserts.

Staff Hiring Bar: Candidate explains DIU compute allocation, partition reading options (physical vs dynamic range), and staging bulk loads.

Interview Question #4Asked at Deloitte

What is the difference between a Schedule Trigger and a Tumbling Window Trigger in Azure Data Factory?

Architectural Answer:A Schedule Trigger executes pipelines on a periodic calendar time (e.g. daily at 2:00 AM UTC). It is point-in-time, does not retain state from previous runs, cannot track dependencies between successive runs, and cannot automatically backfill missed historical windows. A Tumbling Window Trigger executes at fixed, non-overlapping, continuous time intervals (e.g. every 1 hour). It tracks historical state, supports self-dependency (ensuring Window N only runs after Window N-1 succeeds), provides automatic backfilling for past dates, supports built-in retry policies with maxConcurrency limits, and exposes system window variables: `@trigger().outputs.windowStartTime` and `@trigger().outputs.windowEndTime`.

Staff Hiring Bar: Candidate explains self-dependency, historical backfilling, and windowStart/windowEnd system parameters.

Previous ModuleADLS Gen2 Storage Hierarchy, RBAC, Managed Identities & SAS Tokens

Loading Academy Hub...

1. Visual Drag-and-Drop Studio Canvas: Real-World Workflow

In ADF Studio (adf.azure.com), data pipelines are constructed visually on a drag-and-drop canvas:

Left Navigation Panel:

Linked Services: Connection definitions with credentials (e.g. Key Vault, On-prem SQL Server via SHIR, ADLS Gen2).
Datasets: Schema and location pointers (e.g. ds_oracle_orders, ds_adls_orders_parquet).
Pipelines: The workflow DAG holding activities.

Authoring Canvas:

Engineers drag activities from the Activities tool palette onto the designer grid.
Nodes are connected via Activity Dependency Conditions:
- Green (Success): Runs only if preceding activity succeeds.
- Red (Failure): Runs if preceding activity fails (used for alerting & error handling).
- Blue (Completion): Runs regardless of whether preceding activity succeeded or failed.
- Gray (Skipped): Runs if preceding activity was skipped.

code

┌─────────────────┐       Succeeded       ┌─────────────────┐       Succeeded       ┌─────────────────────┐
│ LookupActivity  │──────────────────────▶│  IfCondition    │──────────────────────▶│   ForEachLoop       │
│ (Get Watermark) │                       │ (Files Exist?)  │                       │ (Parallel Batches)  │
└─────────────────┘                       └─────────────────┘                       └──────────┬──────────┘
         │ Failed                                                                              │
         ▼                                                                                     ▼
┌─────────────────┐                                                                 ┌─────────────────────┐
│   WebActivity   │ (Teams / Slack Incident Alert)                                  │    CopyActivity     │
│ (Alert Webhook) │                                                                 │ (Stage to ADLS Gen2)│
└─────────────────┘                                                                 └──────────┬──────────┘
                                                                                               │ Succeeded
                                                                                               ▼
                                                                                    ┌─────────────────────┐
                                                                                    │ Databricks Notebook │
                                                                                    │ (Delta Lake MERGE)  │
                                                                                    └─────────────────────┘

2. Complete ADF Activities Encyclopedia (Categorized)

Category	Activity Name	Production Role & Architectural Guidance
Data Movement	Copy Activity	Moves data between 100+ connectors. Supports DIU scaling, parallel partition queries, staging, and compression.
Control Flow	Lookup Activity	Reads up to 5,000 rows (max 4MB). Fetches control tables, high watermarks, and dynamic configs.
Control Flow	Get Metadata Activity	Inspects folder childItems, file size, exists flag, and lastModified without reading payload.
Control Flow	ForEach Activity	Iterates over array. Supports parallel (up to 50 concurrent items via `batchCount`) or sequential execution.
Control Flow	If Condition Activity	Conditional branch evaluator using dynamic expressions (e.g. `@greater(activity('GetMetadata').output.childItems, 0)`).
Control Flow	Until Activity	Loops activities until expression evaluates to true (polling long-running external tasks).
Control Flow	Switch Activity	Multi-branch routing based on case values (e.g. file format routing: CSV vs JSON vs XML).
Control Flow	Filter Activity	Filters array items using condition expressions before feeding to ForEach.
Control Flow	Set Variable / Append	Manages mutable runtime variables inside pipeline execution context.
External Compute	Databricks Notebook	Dispatches PySpark/Delta Lake jobs on interactive or ephemeral automated job clusters.
External Compute	Stored Procedure	Executes T-SQL stored procedures on Azure SQL or Synapse (used to update watermark tables).
External Compute	Web / Webhook	Web makes synchronous HTTP REST calls (e.g. alerting). Webhook waits for asynchronous callback with callbackUri.
External Compute	Azure Function	Executes custom serverless Python/C# code for complex parsing or token generation.
Transformation	Mapping Data Flow	Visual, code-free Spark transformations executed on managed behind-the-scenes Spark clusters.

3. Self-Hosted Integration Runtime (SHIR): Hybrid Enterprise Gateway

When your pipeline needs to pull data from on-premise databases (Oracle, SQL Server, IBM DB2) or private VNET resources:

No Inbound Firewall Holes: The SHIR agent is installed on an internal Windows VM and communicates with Azure Service Bus strictly via Outbound Port 443 (HTTPS).

High Availability & Scale-Out: A single SHIR cluster can join up to 4 worker nodes. ADF automatically load-balances concurrent pipeline requests across available nodes.

Credential Encryption: When configuring linked services, database credentials are encrypted using DPAPI on the local SHIR machine or fetched dynamically from Azure Key Vault via private endpoint.

Staff Architecture: ADF Integration Runtimes, DIU/TTL Tuning & Dynamic Metadata Pipelines

Copy Activity vs Mapping Data Flows (Cost Control): Use ADF Copy Activity (DIU auto or tuned 4–32) strictly for raw binary/tabular movement into Bronze ADLS Gen2; never put complex joins inside ADF Mapping Data Flows when you already have Databricks or Microsoft Fabric Spark clusters running—spinning up Data Flow Spark clusters for 5-minute runs adds massive cold-start overhead unless a long-lived TTL (Time To Live) pool is justified.

Metadata-Driven Frameworks (Lookup + ForEach): Production Azure teams avoid authoring 200 identical pipelines. Store table watermarks, source queries, and target schemas in an Azure SQL Control Table, then drive a single parameterized Lookup -> ForEach(batchCount=20) pipeline.

Self-Hosted IR HA: Always deploy at least 2 nodes in a Self-Hosted Integration Runtime (SHIR) cluster for on-prem SQL Server/Oracle extraction so OS patching or node reboots never break overnight SLAs.

{ "name": "pl_dynamic_incremental_ingest", "activities": [ { "name": "Lookup_Watermark", "type": "Lookup", "typeProperties": { "source": { "type": "AzureSqlSource", "sqlReaderQuery": "SELECT SourceTable, LastWatermark, TargetFolder FROM dbo.etl_control_table WHERE IsActive = 1;" }, "dataset": { "referenceName": "ds_control_db", "type": "DatasetReference" }, "firstRowOnly": false } }, { "name": "ForEach_SourceTable", "type": "ForEach", "dependsOn": [{ "activity": "Lookup_Watermark", "dependencyConditions": ["Succeeded"] }], "typeProperties": { "isSequential": false, "batchCount": 20, "items": "@activity('Lookup_Watermark').output.value", "activities": [ { "name": "Copy_Table_To_ADLS", "type": "Copy", "typeProperties": { "source": { "type": "SqlServerSource", "sqlReaderQuery": "@concat('SELECT * FROM ', item().SourceTable, ' WHERE ModifiedDate > ''', item().LastWatermark, '''')" }, "sink": { "type": "ParquetSink", "storeSettings": { "type": "AzureBlobFSWriteSettings", "copyBehavior": "FlattenHierarchy" } }, "enableStaging": false, "dataIntegrationUnits": 32 } } ] } } ] }

# Execute on On-Premises Windows Gateway VM to join active SHIR cluster $AdfResourceGroup = "rg-data-engineering-prod" $AdfFactoryName = "adf-enterprise-prod" $ShirName = "shir-onprem-cluster" # Retrieve SHIR Auth Key from Azure $AuthKey = (Get-AzDataFactoryV2IntegrationRuntimeKey ` -ResourceGroupName $AdfResourceGroup ` -DataFactoryName $AdfFactoryName ` -Name $ShirName).AuthKey1 # Register node into the 4-node high availability pool & 'C:\Program Files\Microsoft Integration Runtime\6.0\PowerShellScript\RegisterIntegrationRuntime.ps1' ` -gatewayKey $AuthKey ` -nodeName "SHIR-NODE-02" ` -enableRemoteAccessIntoHostLocalNetwork $true Write-Host "SHIR Node 02 successfully joined high-availability cluster."