Data Integration and Analytics Design
AZ-305 AZ-305 · Design data storage solutions
Data creates value only when it moves: from operational systems into analytical stores, from streaming sources into real-time dashboards, from silos into a single governed platform. This guide covers the Azure data integration and analytics services — Data Factory, Synapse, Databricks, Fabric, Event Hubs, and Stream Analytics — and the design decisions that separate a working pipeline from an expensive mess.
Batch integration: Azure Data Factory
Azure Data Factory (ADF) is Azure's cloud ETL/ELT orchestration service: it moves data between 100+ sources and sinks on a schedule or trigger, transforming it with mapping data flows (visual, Spark-based) or external compute. The design pattern that matters is ELT over ETL: load raw data into the lake or warehouse first, then transform it there, so the raw data is preserved and transformations can be re-run. ADF's integration runtime is the compute that moves data — Azure IR for cloud sources, self-hosted IR for on-premises sources behind a firewall. For change data capture, ADF supports incremental loads using watermark columns or native CDC connectors. When a scenario describes scheduled batch movement between systems with transformation, ADF is the standard answer. Note that Microsoft Fabric now includes Data Factory as part of its unified analytics platform; for greenfield analytics, Fabric's integrated experience is increasingly the recommended path, while standalone ADF remains the right choice for pure integration workloads.
Analytics platforms: Synapse, Databricks, Fabric
Azure Synapse Analytics unifies data warehousing (dedicated SQL pools), big-data processing (Spark pools), and data integration (pipelines) in one workspace. Choose dedicated SQL pools for predictable, high-performance warehousing with massive parallel processing; choose serverless SQL pools for ad-hoc querying over the data lake without provisioning. Azure Databricks is the Apache Spark-based analytics platform for data engineering, machine learning, and collaborative notebooks — choose it when the team works in Spark, needs Delta Lake, or builds ML pipelines. Microsoft Fabric is the unified SaaS analytics platform combining data engineering, warehousing, real-time analytics, and Power BI in one experience with OneLake as the shared storage layer. The exam-level distinctions: Synapse for enterprise warehousing with SQL; Databricks for Spark-based engineering and ML; Fabric for a unified, simplified platform where the team wants one tool for everything. All three can read from Data Lake Storage Gen2, so the lake remains the common foundation.
Real-time data: Event Hubs, Stream Analytics, IoT Hub
Streaming workloads need different tools. Azure Event Hubs is the big-data streaming ingestion service — millions of events per second from applications, devices, or logs, with Kafka compatibility. Azure Stream Analytics processes event streams in real time with a SQL-like query language: filtering, aggregating, joining streams, and detecting patterns, with outputs to databases, dashboards, or Power BI. Azure IoT Hub is the managed service for bidirectional communication with IoT devices — device management, commands, and telemetry — choose it when devices need to be managed, not just to send events. The classic pattern is: Event Hubs ingests, Stream Analytics processes, Cosmos DB or Synapse stores, Power BI visualises. Event Grid is different: it routes discrete events (a blob was created, a resource changed) to handlers for reactive workflows — event-driven architecture, not streaming analytics. The exam tests this distinction: Event Hubs for high-volume telemetry streams, Event Grid for discrete event notifications.
Designing for the analytics workload
Several cross-cutting decisions recur. Partitioning data in the lake (by date, region, or tenant) determines query performance and cost — partition on the columns queries filter by. File formats matter: Parquet and Delta Lake give columnar compression and predicate pushdown, making analytical queries far cheaper than raw CSV or JSON. Medallion architecture (bronze/silver/gold) organises the lake: bronze holds raw ingested data, silver holds cleaned and conformed data, gold holds aggregated business-ready data. Data governance comes from Microsoft Purview (data catalog, lineage, classification, and sensitivity labels) — the answer when a scenario asks how the organisation will discover, classify, and govern data across the estate. For reporting, Power BI is the visualisation layer; use DirectQuery for real-time data and import mode for performance on large models.
Cost and performance trade-offs
Analytics cost is driven by compute (dedicated pools, Databricks clusters, Stream Analytics units) and by data movement. Design for cost by: using serverless or auto-scaling compute where workloads are bursty; pausing dedicated SQL pools when unused; choosing the right file format; and partitioning to reduce scanned data. For performance, the levers are partitioning, distribution (hash-distributed tables in Synapse for large fact tables), caching, and materialised views. The exam often presents a scenario where a warehouse query is slow: the answer is usually distribution or partitioning, not simply adding more compute.
Choosing between Synapse, Databricks, and Fabric
The analytics platform choice is one of the exam's most tested judgement calls, so it deserves a direct comparison. Azure Synapse Analytics is the enterprise data warehousing choice: dedicated SQL pools with massive parallel processing for predictable, high-performance SQL workloads, plus serverless SQL pools for ad-hoc lake queries and Spark pools for big-data processing — all in one workspace. Choose Synapse when the team is SQL-centric and the workload is warehousing. Azure Databricks is the Spark-native platform: collaborative notebooks, Delta Lake, MLflow integration, and the broadest Spark ecosystem. Choose Databricks when the team works in Spark, Python, or Scala, or when machine learning is a first-class workload. Microsoft Fabric is the unified SaaS platform: data engineering, warehousing, real-time analytics, and Power BI in one experience with OneLake as shared storage and a single capacity-based billing model. Choose Fabric for greenfield analytics where the team wants one platform and simplified governance. The three are not mutually exclusive — a Synapse warehouse can read Databricks-produced Delta tables from the same lake — but the exam usually presents one dominant requirement that points to one platform: SQL warehousing to Synapse, Spark and ML to Databricks, unified simplicity to Fabric.
Securing data movement
Data pipelines move an organisation's most sensitive asset, so security design matters as much as throughput. Use managed identities for Data Factory, Synapse, and Databricks to authenticate to sources and sinks — never store connection strings with passwords. Connect to data sources over private endpoints so pipeline traffic never crosses the public internet, and use Data Factory's managed virtual network for an isolated integration runtime environment. Encrypt data in transit (TLS) and at rest (service-managed or customer-managed keys), and classify sensitive columns with Purview sensitivity labels so downstream consumers know what they are handling. For cross-tenant or partner data sharing, Azure Data Share provides governed, snapshot-based sharing with terms of use. In exam scenarios, "the pipeline must not use stored credentials" points to managed identities, and "data must not traverse the public internet" points to private endpoints.
Exam traps to watch for
- Confusing Event Hubs with Event Grid — Event Hubs ingests high-volume telemetry streams; Event Grid routes discrete events to handlers.
- Choosing ETL when ELT is better — loading raw data first preserves it and lets transformations be re-run.
- Recommending dedicated SQL pools for ad-hoc lake queries — serverless SQL pools are the right tool for occasional querying without provisioning.
- Forgetting data governance — when a scenario asks about discovery, classification, and lineage, the answer is Microsoft Purview, not the analytics engine.
Practice questions
Test yourself on this guide. Answers are checked instantly, with a full explanation for each question.
Q1.A company needs to move data nightly from an on-premises SQL Server database behind a firewall into Azure Data Lake Storage Gen2, transforming it during the load. Which Azure Data Factory component enables access to the on-premises source?
Explanation: The self-hosted integration runtime runs on a machine inside the on-premises network and securely moves data to Azure without opening inbound firewall ports. Option B is wrong because the Azure IR runs in Azure and cannot reach on-premises sources behind a firewall. Option D is wrong because a managed VNet isolates ADF's compute, not on-premises connectivity. Option C is wrong because Private Link provides private connectivity to the Data Factory service itself, not to on-premises data sources.
Exam objective: AZ-305 Domain 2: Design data integration
Q2.A logistics company ingests 500,000 telemetry events per second from delivery vehicles and needs to detect speeding events in real time and alert dispatchers. Which combination should the architect recommend?
Explanation: Event Hubs handles millions of events per second of telemetry ingestion, and Stream Analytics processes the stream in real time with SQL-like queries to detect speeding events — the standard streaming pattern. Option C is wrong because Event Grid routes discrete events, not high-volume telemetry streams. Option D is wrong because nightly batch processing cannot detect events in real time. Option B is wrong because Service Bus is a message broker for application messaging, not a streaming analytics platform.
Exam objective: AZ-305 Domain 2: Design data integration
Q3.Analysts need to run occasional ad-hoc SQL queries over Parquet files in a data lake. The queries run a few times a week and the company wants no always-on infrastructure. Which Synapse capability should the architect recommend?
Explanation: Serverless SQL pools let analysts query data in the lake with T-SQL on demand, paying per query with no infrastructure to provision or manage — ideal for occasional ad-hoc analysis. Option D is wrong because dedicated pools require provisioned, always-on compute. Option C is wrong because Spark pools are for big-data processing and notebooks, not ad-hoc SQL. Option A is wrong because Data Explorer pools are for log and telemetry analytics, not general lake queries.
Exam objective: AZ-305 Domain 2: Design data integration
Q4.A retail company wants to build a data platform where raw sales data is preserved exactly as ingested, then cleaned and conformed, then aggregated for reporting. Which architecture pattern should the architect recommend?
Explanation: The medallion architecture organises the lake into bronze (raw, preserved as ingested), silver (cleaned and conformed), and gold (business-ready aggregates) — matching the requirement exactly. Option B is wrong because a warehouse-only design with no staging loses the raw data. Option A is wrong because importing raw data directly into Power BI skips cleaning and governance. Option D is wrong because Cosmos DB is an operational database, not an analytics lake.
Exam objective: AZ-305 Domain 2: Design data integration
Q5.A company has data spread across Azure SQL Database, Data Lake Storage, and on-premises systems. The compliance team needs a single catalog showing where sensitive data lives, its lineage, and its classification. Which service should the architect recommend?
Explanation: Microsoft Purview provides data discovery, cataloging, lineage, and classification across Azure and on-premises sources — the data governance answer. Option A is wrong because Data Factory moves data; it does not catalog or classify it. Option B is wrong because Azure Monitor tracks infrastructure telemetry, not data governance. Option D is wrong because Power BI visualises data; it does not discover or classify it across sources.
Exam objective: AZ-305 Domain 2: Design data integration
Q6.A data engineering team builds Spark jobs and machine learning models collaboratively, needs Delta Lake support, and wants notebooks with version control. Which platform should the architect recommend?
Explanation: Azure Databricks is the Spark-based platform for collaborative data engineering and ML, with native Delta Lake support and notebook-based development with version control. Option C is wrong because dedicated SQL pools are a warehousing engine, not a Spark/ML platform. Option A is wrong because mapping data flows are visual ETL transformations, not a Spark notebook environment. Option B is wrong because Real-Time Intelligence focuses on streaming analytics, not Spark engineering and ML.
Exam objective: AZ-305 Domain 2: Design data integration
Q7.A factory has 10,000 IoT sensors that send telemetry to Azure. The operations team also needs to send commands back to individual devices and manage device firmware. Which service should the architect recommend?
Explanation: IoT Hub provides bidirectional device communication — telemetry ingestion plus device management, commands, and firmware updates — which is the requirement. Option C is wrong because Event Hubs only ingests events; it cannot manage devices or send commands. Option D is wrong because Event Grid routes discrete events and has no device management. Option B is wrong because Service Bus is application messaging, not an IoT device platform.
Exam objective: AZ-305 Domain 2: Design data integration
Q8.A Synapse dedicated SQL pool query over a 2 TB fact table is slow. The table is round-robin distributed and queries always filter on customer ID. What is the most effective first change?
Explanation: Hash-distributing on customer ID co-locates the rows each query needs, eliminating the data movement that round-robin distribution causes for filtered queries — the primary performance lever for large fact tables. Option B is wrong because more compute does not fix data-movement overhead and costs more. Option A is wrong because serverless pools are for ad-hoc queries, not for optimising a dedicated warehouse workload. Option D is wrong because clustered columnstore indexes are already the default for Synapse tables and do not address distribution.
Exam objective: AZ-305 Domain 2: Design data integration
Q9.A company wants to trigger an image-processing function automatically whenever a new image is uploaded to Blob Storage. Which service should the architect recommend to detect the upload and invoke the function?
Explanation: Event Grid routes discrete events such as blob creation to handlers like Azure Functions, with low latency and no polling — the standard event-driven pattern. Option C is wrong because Event Hubs is for high-volume telemetry streams, not discrete storage events. Option B is wrong because Stream Analytics processes continuous streams, not individual blob events. Option A is wrong because a scheduled Data Factory trigger would poll on a schedule rather than reacting immediately to each upload.
Exam objective: AZ-305 Domain 2: Design data integration