1 · ETL vs ELT Concepts
ETL (Extract-Transform-Load) — data is transformed in a staging area before landing in the target. Classic approach for data warehouses with strict schemas.
ELT (Extract-Load-Transform) — raw data lands in a data lake first, then powerful compute (Spark, SQL pools) transforms it in place. Preferred for modern lakehouse architectures because it preserves raw data and scales transforms independently.
2 · ADF Pipeline Architecture
- Linked Service — credentials + endpoint for a data store or compute (e.g., Azure Blob, on-prem SQL, Salesforce).
- Dataset — points to specific data within a linked service (table, file path, container).
- Activity — unit of work: Copy Data, Mapping Data Flow, Execute Pipeline, Lookup, Web, etc.
- Pipeline — orchestrates activities with dependencies, branching (If/ForEach/Until), and parameters.
- Integration Runtime (IR) — the compute engine that executes activities.
3 · Integration Runtimes
| IR Type | Use Case | Key Detail |
|---|---|---|
| Azure IR | Cloud-to-cloud moves & data flows | Auto-resolve region or pin to specific region for compliance |
| Self-Hosted IR | On-prem / private-network sources | Install on Windows VM behind firewall; outbound HTTPS only |
| Azure-SSIS IR | Lift-and-shift SSIS packages | Managed cluster running SQL Server Integration Services |
4 · Data Flows for Transformations
Mapping Data Flows execute on a managed Spark cluster (Azure IR). They provide a visual, code-free transformation layer:
- Source → Transformations → Sink — the canonical flow shape.
- Transformations include: Filter, Derived Column, Aggregate, Join, Pivot, Window, Surrogate Key, Flatten (JSON).
- Debug mode spins up a warm cluster for interactive preview (billable per minute).
- Flows compile to Spark — scale to billions of rows without custom code.
Power Query (Wrangling Data Flows) — M-language-based, familiar to Excel/Power BI users; limited to smaller-scale prep.
5 · Triggers & Orchestration
- Schedule trigger — cron-like recurrence (e.g., daily at 02:00 UTC).
- Tumbling window trigger — fixed-size, non-overlapping time slices with retry & dependency chaining.
- Event trigger — fires on Blob created/deleted events (via Event Grid).
- Custom event trigger — fires on custom Event Grid topics.
Orchestration patterns: Execute Pipeline for parent/child decomposition; ForEach for fan-out parallelism; Until for polling loops; Web Activity to call REST APIs mid-pipeline.
6 · ADF vs Synapse Pipelines vs Logic Apps
| Dimension | Azure Data Factory | Synapse Pipelines | Logic Apps |
|---|---|---|---|
| Primary purpose | Data integration & ETL/ELT | Same engine, embedded in Synapse workspace | Workflow automation & app integration |
| Connectors | 100+ data stores | Same as ADF + Synapse-native pools | 400+ SaaS/API connectors |
| Transformations | Mapping Data Flows (Spark) | Data Flows + Spark notebooks + SQL scripts | Minimal — expressions & inline code |
| Compute | Managed Spark (data flows) + IR | Dedicated/serverless SQL, Spark pools | Consumption or Standard (serverless) |
| Pricing model | Per activity run + DIU-hours | Included in Synapse workspace billing | Per action execution |
| Best for | Standalone data pipelines | Unified analytics platform | Event-driven business workflows |
7 · Real-World Scenario
Hybrid Integration: On-Prem SQL + SaaS APIs → Data Lake
Situation: A retailer needs nightly consolidation of on-prem SQL Server inventory data and Shopify order data into ADLS Gen2 for analytics.
- Self-Hosted IR installed on a domain-joined VM in the corporate network connects to SQL Server (no VPN tunnel needed — outbound 443 only).
- Azure IR handles the REST connector to the Shopify API (OAuth2 linked service).
- A parent pipeline uses
Execute Pipelineto orchestrate two child pipelines in parallel (SQL copy + Shopify copy). - After both succeed, a Mapping Data Flow joins, deduplicates, and writes Parquet to the curated zone.
- A tumbling window trigger runs nightly at 01:00 UTC with a 2-hour retry window.
- Monitoring via ADF Monitor + Azure Monitor alerts on pipeline failure.
Exam Tip
🎯 The exam loves Self-Hosted IR scenarios: "on-premises data behind a firewall" → Self-Hosted IR. Remember it requires outbound HTTPS only, supports HA with multiple nodes, and cannot run Mapping Data Flows. If the question asks about transforming on-prem data, you still need Azure IR for the data flow — the Self-Hosted IR only handles the copy.