Design Data Integration — Lesson

Data integration is the backbone of modern cloud solutions. Whether you're moving legacy data to Azure, building real-time analytics pipelines, or designing ETL workflows that process terabytes daily, understanding how to architect data flows is essential for the AZ-305 exam. This lesson covers the Azure services, patterns, and decision frameworks you need to design robust, scalable data integration and analysis solutions.

As an integrated Topic lesson, this document spans two Learning Objectives: LO20 — Designing a Data Integration Solution (covering Azure Data Factory, integration runtimes, and ETL orchestration) and LO21 — Designing a Data Analysis Solution (covering Synapse Analytics pools, Azure Data Explorer, Stream Analytics, and Microsoft Fabric). Each LO receives a dedicated sub-section in the Deep Dive, with cross-LO worked examples that show how integration and analytics services combine in real architectures.

Reference: Ch. 2, §2.3, p. 73–95 of the AZ-305 exam book.

Why This Matters

Organizations today are drowning in data yet starving for insights. The ability to design a data integration solution that seamlessly connects disparate sources, transforms data reliably, and delivers it to analytics engines is a cornerstone competency for Azure solution architects. In interviews and on the AZ-305 exam, you'll face scenarios asking you to choose between Azure Data Factory, Azure Synapse Analytics, and Azure Databricks—and justify your choice. Mastering this topic directly impacts your ability to design enterprise-grade solutions and advance your career.

Prerequisites

• Azure storage fundamentals (Blob, Data Lake) — Can you describe when to use Data Lake Storage vs. Blob Storage for analytics?
• Azure networking basics (VNets, Private Endpoints) — How would you ensure data never traverses the public internet?
• Database and data warehouse concepts — What's the difference between OLTP and OLAP workloads?
• SQL and basic ETL concepts — Have you written a UNION query or understood data lineage?

Learning Objectives

1. Analyse requirements and recommend appropriate data integration services (Azure Data Factory, Synapse Analytics, Databricks).
2. Design a complete Azure Data Factory pipeline with linked services, datasets, and integration runtimes.
3. Evaluate integration runtime options (self-hosted, Azure IR, SSIS IR) and justify your selection.
4. Recommend analytics engines based on workload characteristics (real-time vs. batch, SQL vs. Spark).
5. Design a Synapse workspace topology with dedicated and serverless pools for analytics.
6. Architect real-time data ingestion flows using Event Hubs and Stream Analytics.

Building Blocks

Azure Data Factory (ADF) — Think of it as a "visual ETL orchestrator." ADF is Microsoft's serverless, cloud-native data integration service that lets you define, schedule, and execute data pipelines at scale without managing infrastructure. You define what to move, when to move it, and how to transform it—ADF handles the rest. It's ideal for batch and near-real-time data movement.

Pipeline — A pipeline is the top-level execution unit in ADF. It's a logical grouping of activities (copy, transform, validate) connected in a DAG (directed acyclic graph). Pipelines execute on a schedule or on-demand and can span multiple linked services.

Linked Service — A linked service is a connection definition to a data source or compute target. It encapsulates the authentication, endpoint, and connection string. For example, a SqlServerLinkedService connects to an on-premises SQL Server via a self-hosted integration runtime.

Dataset — A dataset is a named reference to data within a linked service. It defines the schema, location, and format (CSV, Parquet, SQL table). Think of it as a contract: "this dataset points to files at path X with columns Y and Z."

Integration Runtime (IR) — An integration runtime is the compute fabric that executes activities. There are three types: Azure IR (for cloud-to-cloud, serverless), self-hosted IR (on-premises or private networks, installed on a VM), and SSIS IR (for running SQL Server Integration Services packages). Choosing the right IR is critical for security and performance.

Azure Synapse Analytics — A unified analytics platform combining data warehousing, big data, and real-time analytics. It provides dedicated SQL pools (traditional DW), serverless SQL pools (pay-per-query), and Spark pools (distributed compute). It integrates deeply with Power BI, Python, and Spark notebooks.

Dedicated SQL Pool — A provisioned data warehouse in Synapse with fixed compute (DWU scale). Use it for predictable, high-concurrency analytical queries. You pay for compute regardless of usage.

Serverless SQL Pool — Pay-per-query analytics on data in the data lake. No provisioning; query data in place in Parquet, Delta, or CSV format. Ideal for exploratory analysis and small-to-medium workloads.

Spark Pool — Distributed computing engine in Synapse for data engineering and ML. Write Python, Scala, or SQL and scale dynamically. Perfect for data cleaning, feature engineering, and ETL.

Azure Data Explorer (Kusto) — High-performance time-series and log analytics engine. Ingests and queries billions of rows of data with sub-second latency. Ideal for monitoring, telemetry, and real-time analytics.

Microsoft Fabric — Microsoft's unified, SaaS analytics platform. It combines data engineering, warehousing, analytics, and BI under one experience. It's newer and integrates seamlessly with Power BI.

Event Hubs — A hyper-scale event ingestion service. Stream millions of events per second from devices, applications, and services. Acts as the "front door" for real-time data architectures.

Stream Analytics — Serverless real-time stream processing. Write SQL-like queries on Event Hubs or IoT Hub streams to filter, aggregate, and route events in real-time. Outputs go to databases, data lakes, or Power BI.

Azure Databricks — Managed Apache Spark platform on Azure. Provides collaborative notebooks, MLlib, Delta Lake, and production job scheduling. Ideal for data science, ML, and large-scale ETL with fine-grained governance.

Deep Dive

LO20 — Designing a Data Integration Solution

Azure Data Factory Pipelines & Architecture

When designing an ADF solution, you orchestrate data movement and transformation across sources. A typical pattern:

1. Source Linked Service — Points to on-premises SQL Server, Salesforce, HTTP API, or cloud storage.
2. Source Dataset — References the exact table, file, or API endpoint.
3. Copy Activity — Moves data from source to sink (usually Azure Data Lake Storage or Azure Blob Storage).
4. Transformation Activity — Data Flow (visual ETL) or Databricks Notebook (Spark) cleans and transforms data.
5. Sink Linked Service & Dataset — Outputs to a data warehouse or analytics engine.
6. Triggers — Schedule the pipeline (hourly, daily) or trigger on file arrival.

Integration Runtime Selection

Decision Tree: ADF vs. Synapse Pipelines vs. Databricks ETL

[!IMPORTANT] When designing ADF pipelines, always validate data quality early. Use a Data Flow lookup to catch missing keys before writing 100 million rows to the sink. Prevention is cheaper than recovery.

ADF Linked Service Definition (JSON)

When you configure an ADF pipeline, linked services are defined as JSON. Here is an example pointing to an on-premises SQL Server through a self-hosted integration runtime:

[!TIP] Always store credentials in Azure Key Vault and reference them via a Key Vault linked service. Hard-coding passwords in JSON is a security anti-pattern and will fail compliance audits.

Deploying a Self-Hosted IR via CLI

Provisioning a self-hosted integration runtime can be scripted with Azure CLI:

After retrieving the keys, install the Microsoft Integration Runtime application on a Windows VM in the on-premises network and register it using the authentication key. The VM must have outbound HTTPS (443) connectivity to Azure but requires no inbound ports.

ADF vs. Synapse Pipelines vs. Databricks ETL — Feature Comparison

Use this table as your exam cheat-sheet: if the scenario emphasises orchestration of many data sources, lean toward ADF. If the scenario is analytics-first with integrated BI, lean toward Synapse Pipelines. If the scenario involves ML, complex Spark transformations, or Delta Lake, lean toward Databricks.

See the LO-level lesson for more on ADF pipeline design, copy activity optimization, and monitoring.

LO21 — Designing a Data Analysis Solution

Synapse Analytics for BI & Analytics

Synapse gives you three analytical engines in one workspace:

[!WARNING] Dedicated pools charge even when idle. Size conservatively and pause during off-hours. A 1000 DWU pool costs USD 6–8/hour; 24/7 annual cost exceeds USD 50k.

Azure Data Explorer (Kusto) for Time-Series

Use Kusto for telemetry, logs, and real-time analytics. It excels at ingesting millions of events per second and running time-window aggregations with sub-second latency.

Example KQL Query — Detect Anomalous Throughput

This query scans the last hour of telemetry, bins events into 5-minute windows, and surfaces any window where the event count exceeds the 95th percentile — a classic anomaly-detection pattern for operational dashboards.

Synapse Dedicated Pool Sizing Guide

[!NOTE] DWU scales linearly: doubling DWU doubles compute power and concurrency slots. Start small, benchmark with representative queries, and scale up only when queue wait times exceed your SLA.

Real-Time Analytics: Event Hubs + Stream Analytics

For live dashboards or real-time decisions, you combine Event Hubs as the ingestion front door with Stream Analytics as the processing engine. The architecture follows a three-stage pattern:

1. Ingest — Event Hubs receives streams from IoT sensors, application logs, clickstream data, or POS systems. It supports up to 1 million events per second per namespace and retains events for 1–90 days (depending on tier). Partition the hub by device ID or region for parallel processing.
2. Process — Stream Analytics applies SQL-like queries to the event stream. It supports tumbling windows (fixed, non-overlapping intervals), hopping windows (overlapping intervals for smooth metrics), sliding windows (triggered on every event), and session windows (grouped by activity gaps). Each window type suits a different analytics pattern.
3. Serve — Output to Azure SQL Database for transactional queries, Azure Data Lake for archival, Cosmos DB for global distribution, or Power BI for real-time dashboard tiles that refresh every few seconds.

Stream Analytics Windowing Functions

Microsoft Fabric and the Lakehouse Paradigm

Microsoft Fabric represents Microsoft's unified SaaS analytics platform, combining data engineering (pipelines, Spark), data warehousing, real-time intelligence, and Power BI into a single product. The key differentiator is OneLake — a single, governed data lake that all Fabric workloads share. This eliminates data duplication between warehouses and lakes. For new greenfield projects, Fabric simplifies architecture by removing the need to manage separate ADF, Synapse, and Databricks instances. For brownfield migrations, Fabric supports shortcuts to existing ADLS Gen2 storage, enabling incremental adoption.

[!IMPORTANT] On the AZ-305 exam, you may see questions comparing Fabric to Synapse. The key distinction: Fabric is SaaS (fully managed, capacity-based billing), while Synapse is PaaS (you manage pools and runtimes). Choose Fabric when the organisation wants minimal operational overhead; choose Synapse when they need fine-grained control over compute resources.

[!TIP] Stream Analytics can windowing aggregations (tumbling, hopping, sliding) natively. Use hopping windows (e.g., 5-min window every 1 min) for smooth real-time metrics.

Decision Tree: Which Analytics Engine?

See the LO-level lesson for more on pool sizing, Kusto query language, and Stream Analytics windowing.

Worked Examples

Easy: Migrate on-premises CSV files to ADLS

Problem: A retail company has daily inventory files (CSVs) on an on-premises file server. They want to land these in Azure Data Lake daily at 11 PM EST without exposing the on-prem network to the internet.

Solution:

1. Deploy a self-hosted IR on an on-prem Windows VM (connected to the file server via LAN).
2. In ADF, create a linked service for the on-prem file share (with self-hosted IR selected).
3. Create a dataset pointing to the CSV folder.
4. Create a sink dataset in ADLS Gen2 with Parquet format (for compression and columnar performance).
5. Add a Copy activity in a pipeline.
6. Set a schedule trigger for 11 PM EST.
7. Monitor with ADF's built-in diagnostics; alert on failures.

[!NOTE] Self-hosted IR runs on a 1-2 VM per data center. It's not a bottleneck—IR can handle gigabits per second throughput. Cost is minimal (~$0.15/hour per VM).

---

Medium: Design a real-time KPI dashboard

Problem: An e-commerce company wants a dashboard showing orders, revenue, and top products updated every 30 seconds. Source: IoT devices and POS systems streaming to Event Hubs.

Solution:

1. Configure Event Hubs to ingest POS events (10k/sec).
2. Write a Stream Analytics job with a 30-second hopping window to aggregate sales by product category.
3. Output to a SQL Database staging table.
4. Connect Power BI to SQL Database with automatic refresh (every 30 sec).
5. Create visuals (line chart of revenue trend, top-10 products card).
6. Set alerts: if revenue drops > 20% in any 10-min window, trigger an email.

[!NOTE] Stream Analytics charges per streaming unit (SU). 1 SU ≈ USD 0.13/hour. For 10k events/sec with simple aggregations, 3–4 SUs suffice. Total cost: ~USD 10/day.

---

Hard: Design a multi-tier data lake + analytics architecture

Problem: A financial services firm ingests 500 GB/day from 50+ data sources (databases, APIs, file drops) and runs nightly ML models for risk scoring. They need cost optimization, data governance, and sub-second query latency on curated data.

Solution:

1. Ingestion Layer (Bronze): ADF pipelines copy raw data from sources to ADLS Gen2 in Parquet (partitioned by date/source).
2. Transformation Layer (Silver): Azure Databricks notebooks read Bronze data, clean (handle nulls, deduplicate), enrich (join reference data), and write to Silver (Delta Lake format for ACID guarantees).
3. Curated Layer (Gold): Spark jobs aggregate Silver into analytics-ready Gold tables (star schema for BI).
4. Analytics:
   • Power BI connects to Synapse Dedicated Pool (curated Gold data synced daily).
   • ML Models run in Databricks on Silver data (risk scoring, anomaly detection).
   • Real-time Dashboards on fraud signals via Kusto (log events from APIs).
5. Governance: Azure Purview catalogs all assets; Synapse SQL RBAC controls access; Databricks notebooks audit all transformations.

[!NOTE] Using Delta Lake (Databricks) instead of Parquet in Silver provides ACID transactions, time-travel, and schema evolution. Critical for data quality and compliance audits. Synapse Dedicated Pool stores Gold for fast BI queries; Spark handles the heavy lifting and costs USD 5–15/day vs. USD 500+/day for dedicated compute.

Visual Explanations

Data Integration Architecture Overview

Decision Tree: Service Selection

Synapse Analytics Pool Selection Matrix

Bicep: Deploy an ADF with Self-Hosted IR

Infrastructure-as-code is critical for repeatable deployments. Here is a Bicep template that provisions an Azure Data Factory instance with a self-hosted integration runtime:

This template creates a managed identity for the factory (enabling passwordless auth to Key Vault and storage) and registers a self-hosted IR that you install on an on-premises VM.

TikZ: Data Flow Control & Lineage

ADF Pipeline Activity Sequence

Common Integration Patterns

Common Mistakes

❌ Myth: "We'll use one Azure IR for all 500 data factories across the company." ✅ Reality: Each ADF instance should have its own IR (or region-local IR). Sharing a single IR across factories creates a bottleneck and single point of failure. IRs are stateless and cheap; provision per data factory or region. Why it's tricky: IR appears to be a "global" resource, but it's really a fabric that optimizes for data locality. A single IR in US East serving 50 factories in Europe incurs high latency and egress costs.

---

❌ Myth: "Dedicated SQL pools are always cheaper than serverless pools." ✅ Reality: Dedicated pools have fixed hourly costs; serverless charges per TB scanned. For occasional queries on large tables, serverless is cheaper. For frequent OLAP queries on curated data, dedicated pools win. Why it's tricky: Pricing models are fundamentally different. A 1000 DWU pool ($6/hr) is cheaper than scanning 100 TB/day with serverless ($0.6/TB × 100 = $60/query). Know your query patterns first.

---

❌ Myth: "Event Hubs and IoT Hub are interchangeable; pick one arbitrarily." ✅ Reality: Event Hubs is for high-scale events (millions/sec); IoT Hub adds device management, twin state, and firmware updates. For sensor data without device management, use Event Hubs. For managed IoT fleets, use IoT Hub. Why it's tricky: Both ingest streams, but IoT Hub is purpose-built for enterprise IoT device fleets. Using Event Hubs for 10 sensors is over-engineering; using IoT Hub for 10M anonymous event sources is under-featured.

Practice Exercises

Exercise 1: Choose the Right IR 🟢 Easy

You need to copy data from a Teradata database (on-prem, firewall-restricted) to ADLS. Which IR should you use and why?

▶💡 Hint

Consider network topology. Can Azure reach the Teradata server directly? What about reverse access?

▶✅ Solution

Use a self-hosted IR deployed on an on-prem server. The self-hosted IR can reach Teradata via LAN and push data to ADLS over the internet (egress only). The alternative—opening a firewall hole for Azure to pull directly—is riskier and slower.

---

Exercise 2: Pool Sizing for Nightly ETL 🟡 Medium

Your team runs a nightly ETL (midnight–6 AM) that processes 1 TB of customer transactions. During business hours, you run 50 concurrent BI queries. What Synapse topology would you recommend?

▶💡 Hint

Consider two workload patterns: ETL is heavy and batch; BI is concurrent and real-time. Can you separate them?

▶✅ Solution

Use a Dedicated SQL Pool (500–600 DWU) for BI queries. Run ETL in a Spark Pool or Databricks cluster. This separates workloads: Spark handles heavy ETL without starving BI queries on the dedicated pool. If you run ETL in the dedicated pool, BI queries queue up and timeout. Alternative: use Serverless SQL Pool for BI (if your BI tool supports external tables) and Spark for ETL, saving dedicated pool costs.

---

Exercise 3: Real-Time vs. Batch Trade-offs 🟡 Medium

A company wants to detect fraudulent credit card transactions in < 100 ms. Which architecture: Stream Analytics or nightly batch scan with Databricks?

▶💡 Hint

Consider latency SLAs. What's the cost of processing a transaction 100 ms later vs. processing 1 hour later?

▶✅ Solution

Stream Analytics is necessary for sub-100ms detection. Batch processing (even hourly) means fraudsters can execute 36,000 transactions before detection. Stream Analytics with event-driven rules (e.g., "block if > 3 transactions in 10 sec") catches fraud in real-time. Cost: USD 1–5/day. Loss prevention: millions of dollars.

---

Exercise 4: Data Governance in a Multi-Layer Lake 🔴 Hard

You have Bronze (raw), Silver (cleaned), and Gold (curated) layers. Different teams own different layers. How do you prevent unauthorized transformations and ensure lineage?

▶💡 Hint

Think about access control, audit logging, and data discovery. Which Azure services help?

▶✅ Solution

1. Azure Purview: Register all datasets (Bronze, Silver, Gold) and scan for PII. Track column-level lineage automatically.
2. Synapse SQL RBAC: Grant Silver team read-only access to Bronze; Gold team read-only access to Silver. No one can write directly to Silver except the transformation service principal.
3. Databricks Cluster Policies: Restrict who can create clusters; enforce auto-termination after 2 hours to prevent runaway costs and unauthorized long-running jobs.
4. Azure Monitor alerts: If Bronze is read by an unexpected user, alert compliance team.

---

Exercise 5: Cost Optimization for Exploratory Analytics 🟡 Medium

Data scientists run ad-hoc queries on 50 TB of raw data (Parquet files in ADLS). They're currently using a 1000 DWU Dedicated pool and it's underutilized. What alternative would cut costs 80%?

▶💡 Hint

Remember: dedicated pools charge per hour; serverless per TB scanned. For sporadic queries, which is cheaper?

▶✅ Solution

Switch to Synapse Serverless SQL Pool. Write external tables over the Parquet files. Scientists query with SELECT... FROM dbo.external_table WHERE .... Cost: $0.6/TB scanned vs. $6/hr for dedicated pool. If scientists scan 5 TB per day, serverless = $3/day vs. $144/day for dedicated. Save: $140+/day or 97%. Trade-off: serverless is slightly slower for complex aggregations, but fine for exploration.

---

Exercise 6: Handling Late-Arriving Data 🔴 Hard

Your Stream Analytics job aggregates sensor readings in 5-minute tumbling windows. Sensors occasionally send readings 10 minutes late due to network latency. How do you handle this?

▶💡 Hint

Stream Analytics has watermark and late-arrival settings. Should you adjust the window or the policy?

▶✅ Solution

Set late arrival tolerance to 10 minutes in the Stream Analytics job configuration. Events arriving within 10 minutes of the window end are included in the window (results are updated). Events arriving after 10 minutes are dropped (or sent to a dead-letter queue for manual review). Without this, you'd lose data or miss readings. Adjust tolerance based on your SLA: if 5 min late is acceptable, set to 5 min; if all data must be captured, set to 15+ min (but results are delayed).

---

Exercise 7: Securing Sensitive Data in the Lake 🔴 Hard

Your Silver layer contains Personally Identifiable Information (PII): customer names, emails, SSNs. How do you prevent Gold-layer consumers from accessing raw PII?

▶💡 Hint

Consider masking, encryption, and role-based access. Which tool prevents a Gold analyst from running SELECT ssn FROM silver.customers?

▶✅ Solution

1. Column-level Security (CLS) in Synapse: Create a security predicate that masks the ssn column for Gold-tier users. SELECT shows ***-**-1234 instead of full SSN.
2. Databricks Table ACLs: Grant Silver team write access to the entire table; Gold team read access to only non-PII columns (via a filtered view).
3. Azure Key Vault + Transparent Data Encryption (TDE): Encrypt the entire Silver layer at rest. Gold layer decrypts only authorized columns.
4. Synapse SQL column encryption: Encrypt SSN at the storage layer; Gold users' queries fail if they try to read SSN (unless they have the decryption key).

---

Exercise 8: Building a CI/CD Pipeline for ADF 🔴 Hard

You have ADF pipelines in Dev, Test, and Prod environments. Changes to a pipeline in Dev should automatically promote to Test after unit tests pass, then to Prod after smoke tests. How do you set this up?

▶💡 Hint

Which service helps you version, test, and deploy ADF code? (Hint: starts with 'git')

▶✅ Solution

1. Git Integration: Connect ADF to Azure Repos or GitHub. Publish ADF definitions (ARM templates) to Git's main branch.
2. Azure Pipelines: Create a YAML pipeline that:
   • Triggers on PR merge to main.
   • Exports ADF artifacts and generates ARM templates.
   • Runs unit tests (validate linked services, test copy activities with mock data in Dev).
   • If tests pass, deploy to Test environment (parameterized ARM template).
   • Run smoke tests (copy 1000 rows, validate row count).
   • If smoke tests pass, create a release approval gate and promote to Prod.
3. Parameterization: Use linked service properties (e.g., sql_server_name) as parameters so the same pipeline code works across environments.

Summary & Concept Map

Key Takeaways:

1. ADF is the workhorse for batch and scheduled data movement. Use it for on-prem-to-cloud migrations, file ingestion, and ETL orchestration. Self-hosted IR enables hybrid scenarios without exposing on-prem networks.

2. Choose your analytics engine based on workload: Dedicated pools for OLAP BI, serverless for ad-hoc queries, Spark for ML and heavy transforms, Kusto for real-time time-series, Stream Analytics for event-driven aggregations.

3. Real-time requires event architecture: Event Hubs (ingestion) → Stream Analytics (processing) → Power BI or databases (output). For sub-100ms, avoid batch; for historical analysis, batch is cheaper.

4. Multi-tier data lakes (Bronze → Silver → Gold) separate raw ingestion, transformation, and consumption. Use Databricks + Delta Lake to ensure ACID transactions and schema evolution during transforms.

5. Governance and security are non-negotiable. Use Purview for lineage, Synapse RBAC for access, and encryption for sensitive data. Audit all transformations.

6. Cost optimization requires right-sizing: pause dedicated pools, use serverless for sporadic queries, set auto-scaling for Spark pools, and monitor IR throughput.

Concept Map: Data Integration Ecosystem

Connections & Next Steps

This lesson is a summary of two Learning Objectives:

1. LO20 — Designing a Data Integration Solution (see dedicated lesson for deep dives on ADF pipeline patterns, activity configuration, copy optimization, and monitoring).

2. LO21 — Designing a Data Analysis Solution (see dedicated lesson for pool sizing, schema design, and analytics governance).

Related Topics & Units:

• Unit 1, Topic 1: Azure storage fundamentals (ADLS, Blob, File Share) — prerequisite for understanding data lakes.
• Unit 2, Topic 2: Design identity and security — leverage Synapse RBAC and Azure Key Vault for secrets management.
• Unit 3: Design monitoring and logging — set up Application Insights alerts for ADF failures and Stream Analytics lag.
• Exam Topics: You'll face 2–4 scenario questions combining data integration with security (VNets, MSI), governance (Purview), and cost optimization.

Next: Review LO-level lessons for hands-on ADF/Synapse configuration. Practice designing architectures for provided scenarios (on-prem migration, real-time analytics, cost-constrained exploration).