Data Integration and Analytics Design

AZ-305 AZ-305 · Design data storage solutions

By Abu Anns · Last updated 20 November 2026Exam version: AZ-305 (skills measured as of 17 April 2026)

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.

Advertisement

Exam traps to watch for

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?

Report an error

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?

Report an error

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?

Report an error

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?

Report an error

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?

Report an error

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?

Report an error

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?

Report an error

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?

Report an error

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?

Report an error