Recommend a Solution for Data Analysis — Lesson — Designing Microsoft Azure Infrastructure Solutions (AZ-305) Study Notes | BrainyBee

Analytics is the part of the Azure data platform that turns raw business events — transactions, clicks, sensor readings, application logs — into the dashboards, reports, and ML features that drive decisions. The catch on AZ-305 is that Azure offers four very different analytics engines whose marketing brochures all sound similar: Azure Synapse Analytics, Microsoft Fabric, Azure Databricks, and Azure Data Explorer. Choosing the wrong one quietly inflates cost by 3–10× and pushes the team into months of unnecessary rebuilding.

This lesson teaches you how to read the requirements an architect gets handed — workload type, data volume, latency tolerance, team skills, governance posture — and map them deterministically to one of these engines (or a mix), then size the compute. We work in the AZ-305 exam style: scenarios, trade-offs, and the "close-but-wrong" sibling services the exam loves to test.

Why This Matters

Every Azure architect interview eventually lands on the same question: "A line-of-business team brings you 50 TB of historical sales data, a real-time clickstream, and a directive to power Power BI dashboards — what do you build?" The exam mirrors this in three or four scenario items per attempt. In production, the answer separates a $$15{,}000$ /month bill from a $$150{,}000$ /month bill, and a 30-minute query from a 30-second one. AZ-305 expects you to design for the right blend of performance, cost, governance, and team skill — not just to know that Azure has an analytics service. Get this LO right and you also unlock the downstream design for Power BI, ML feature stores, and real-time scoring pipelines.

Prerequisites

Azure storage tiers and ADLS Gen2 — analytics engines read from object storage in 99% of designs. Quick check: what is the difference between a flat blob and a hierarchical namespace?
Data ingestion patterns (LO20) — Synapse Pipelines, ADF, Event Hubs, and IoT Hub feed every analytics workload. Quick check: which Azure service is the typical front door for high-throughput event ingestion?
Parquet and Delta Lake — every lakehouse design depends on columnar storage and ACID transactions over object storage. Quick check: why is Parquet preferred over CSV for analytical queries?
Microsoft Entra ID and RBAC — analytics platforms enforce row- and column-level security with Entra ID group membership. Quick check: how does Entra ID authentication differ from SQL authentication?
Power BI fundamentals — the consumption layer for almost every analytics design. Quick check: what is a semantic model (formerly "dataset") in Power BI?
T-SQL or Spark literacy — you don't need to be expert, but you must read a join plan and recognise when a query is data-bound versus compute-bound. Quick check: what does a broadcast hash join optimise versus a shuffle hash join?

Learning Objectives

By the end of this lesson, the learner will be able to:

Differentiate the four core Azure analytics engines (Synapse, Fabric, Databricks, Data Explorer) by workload pattern, compute model, and pricing.
Recommend an analytics engine for a given scenario using a deterministic decision tree.
Evaluate the trade-offs between dedicated SQL pool, serverless SQL pool, and Apache Spark pool within Synapse.
Design a Fabric capacity sizing and OneLake layout for a multi-workload tenant.
Analyse governance and security implications (RBAC, row-level security, customer-managed keys) across the four engines.
Map any chosen analytics tier to a Power BI consumption mode (Import / DirectQuery / Direct Lake).

Building Blocks

Data warehouse — Analogy: a corporate library where every book has been catalogued, indexed, and shelved by topic. Formal definition: a centralized relational store optimized for analytical (OLAP) queries on structured, modelled data, typically using star or snowflake schemas. Why it matters: warehouses are the predictable, governed home for "known questions" — finance reports, KPI dashboards, regulatory submissions.

Data lakehouse — Analogy: a warehouse built on top of a freight yard — the same indexing and governance, but the underlying storage is open object storage instead of proprietary disk. Formal definition: an architecture that applies warehouse-grade transactional and governance features (ACID transactions, schema enforcement, time travel) directly to data lake storage, typically via Delta Lake, Apache Iceberg, or Apache Hudi. Why it matters: one copy of data serves both ad-hoc data science and structured BI — no costly ETL hops.

MPP (Massively Parallel Processing) — Analogy: a 60-person warehouse picking team where each picker takes one aisle. Formal definition: a database architecture that distributes data and computation across many compute nodes, each operating on a partition in parallel. Why it matters: dedicated SQL pool (Synapse) and Fabric Warehouse are MPP — they scale linearly to petabyte-scale tables that single-node engines cannot touch.

Dedicated SQL pool (formerly SQL DW) — Analogy: renting a private warehouse with a fixed crew, billed by the hour whether you ship or not. Formal definition: a provisioned MPP engine in Synapse measured in DWU (Data Warehouse Units), with reserved compute and storage. Why it matters: predictable performance for steady analytical workloads, but you pay for it even when idle.

Serverless SQL pool — Analogy: a pay-per-query warehouse that materializes pickers only when an order arrives. Formal definition: a pay-per-TB-scanned T-SQL query service in Synapse that queries Parquet, Delta, CSV, and JSON directly from ADLS Gen2, with no provisioned compute. Why it matters: ideal for exploratory or sporadic queries over a data lake at near-zero idle cost.

Apache Spark pool — Analogy: a fleet of forklifts that materialises on demand to move pallets between warehouse zones. Formal definition: a managed Spark cluster (Synapse or Databricks variant) that runs notebook and job workloads in Python, Scala, Spark SQL, or R. Why it matters: the engineering and ML workhorse — the place where data is shaped before it lands in warehouses or semantic models.

OneLake — Analogy: a single nationwide warehouse where every department's goods are addressable through one zip-code system. Formal definition: a tenant-wide logical data lake built on ADLS Gen2 and exposed through Microsoft Fabric — every Fabric workload reads and writes the same physical files in Delta Parquet format. Why it matters: eliminates per-workload data copies and is the foundation of Direct Lake mode.

KQL (Kusto Query Language) — Analogy: a domain-specific search syntax tuned for telemetry haystacks. Formal definition: a read-only query language for time-series, log, and high-cardinality semi-structured data, native to Azure Data Explorer, Azure Monitor Logs, and Microsoft Sentinel. Why it matters: KQL slices billions of telemetry rows in seconds where SQL would time out.

Direct Lake mode — Analogy: reading the book directly off the warehouse shelf instead of asking the librarian to photocopy a chapter and bring it to you. Formal definition: a Power BI semantic-model storage mode (Fabric-only) that loads Delta Parquet files from OneLake into the VertiPaq engine memory on demand, with no scheduled refresh and no DirectQuery latency. Why it matters: combines Import-mode performance with DirectQuery freshness, but only inside Fabric capacities.

Capacity Unit (CU) — Analogy: a single shared electricity meter for the whole building. Formal definition: the unit of compute in Microsoft Fabric, allocated through F-SKU capacities (F2 through F2048), shared across all workloads within that capacity, with smoothing and throttling on burst usage. Why it matters: Fabric does not let you size individual engines — you size the capacity once and every workload competes for it.

Unity Catalog — Analogy: a centralized library card catalog that grants reader privileges and tracks who borrowed what. Formal definition: Databricks' three-level (catalog.schema.table) governance and metastore layer that adds fine-grained access controls, column-level lineage, and Delta Sharing. Why it matters: the comparison point against Microsoft Purview for cross-workspace governance in lakehouse designs.

Star schema — Analogy: a hub-and-spoke wheel with a central fact and surrounding dimensions. Formal definition: a denormalised dimensional model with one or more fact tables joined to a small number of dimension tables, optimised for OLAP queries. Why it matters: the data shape that MPP engines (dedicated SQL pool, Fabric Warehouse) are tuned for — recognising it in a scenario is a strong signal toward Synapse or Fabric over Databricks or ADX.

Deep Dive

Azure Synapse Analytics — the IaaS/PaaS workhorse

Synapse Analytics is the older but still-dominant enterprise analytics platform on Azure. It packages three independent compute engines into one workspace, each consuming data from a shared ADLS Gen2 account.

Dedicated SQL pool is the MPP engine. You provision a fixed-size cluster measured in DWU100c increments (from DW100c to DW30000c); compute and storage are coupled in the legacy Gen1 model and decoupled in Gen2 (the only model offered for new workspaces). Distributions — HASH, ROUND_ROBIN, REPLICATE — are the make-or-break design choice; pick a HASH distribution column with high cardinality and no skew, and joins stay local to each compute node instead of triggering a network shuffle.

sql

CREATE TABLE dbo.FactSales
WITH (
  DISTRIBUTION = HASH(CustomerKey),
  CLUSTERED COLUMNSTORE INDEX,
  PARTITION (
    OrderDateKey RANGE RIGHT FOR VALUES
    (20240101, 20240201, 20240301, 20240401)
  )
)
AS
SELECT * FROM ext.Sales_Staging;

[!TIP] Choose the smallest dedicated SQL pool size that meets your P95 query SLA, then scale up on demand with ALTER DATABASE ... MODIFY (SERVICE_OBJECTIVE). Pausing the pool (0 DWU) stops compute billing entirely — storage billing continues.

Serverless SQL pool is the always-on, pay-per-TB-scanned engine. It is enabled by default in every Synapse workspace and is priced at roughly $$5$ per TB of data processed. It cannot ingest or modify data — it is a read-only T-SQL surface over Parquet, Delta, CSV, and JSON in ADLS Gen2. Use it for exploratory analysis, lightweight reporting, and as the SQL endpoint that Power BI Import-mode datasets can hit.

[!WARNING] Serverless SQL pool meters bytes scanned, not rows returned. A SELECT TOP 10 over an unpartitioned 20 TB Parquet table can cost real money. Always partition external tables and push predicates that prune partitions.

Apache Spark pool in Synapse is a managed Spark $3.x$ cluster with autoscale and auto-pause. It executes notebooks (Python, Scala, Spark SQL, .NET) and Spark job definitions. The integration point is a shared metastore (Lake Database) so a Spark notebook can write a Delta table that serverless SQL pool then queries.

Synapse engine	Pricing model	Best for	Avoid for
`Dedicated SQL pool`	Per-DWU hour (paused = compute free)	Steady BI / regulatory workloads, $> 1$ TB warehouse	Sporadic ad-hoc queries; modern lakehouse
`Serverless SQL pool`	Per-TB scanned ( $$5$ /TB)	Ad-hoc data lake exploration; logical data warehouse	Frequent dashboard hits over the same data
`Apache Spark pool`	Per-vCore hour	Data engineering, ML feature prep, large Delta tables	Sub-second BI queries

Microsoft Fabric — the SaaS analytics platform

Fabric is Microsoft's SaaS-ification of analytics: one tenant-wide capacity (an F2 to F2048 SKU), one storage layer (OneLake), one identity model (Entra ID), and seven workload "experiences" on top — Data Factory, Synapse Data Engineering, Synapse Data Warehouse, Synapse Data Science, Synapse Real-Time Intelligence, Power BI, and Data Activator.

Where Synapse exposes the compute knobs (DWU, vCore, autoscale limits), Fabric hides them behind a single Capacity Unit (CU) abstraction. An F64 capacity yields 64 CUs that all workloads share via a smoothing/throttling engine; bursts beyond your allocation are absorbed for up to 24 hours before throttling kicks in.

Loading Diagram...

Figure 1 — Mermaid diagram

The killer feature for AZ-305 is Direct Lake mode: a Power BI semantic model that reads the underlying Delta Parquet from OneLake straight into VertiPaq with no scheduled refresh and no DirectQuery latency. The catch is that Direct Lake falls back to DirectQuery (slower) if the model exceeds capacity-level row/column thresholds, so capacity sizing matters.

Power BI mode	Where data lives	Refresh model	Performance profile	Where it is available
`Import`	VertiPaq, in-memory copy	Scheduled refresh	Fast, but stale between refreshes	All Power BI tiers
`DirectQuery`	Source SQL engine	Query on demand	Bound by source latency	All Power BI tiers
`Direct Lake`	OneLake Delta → VertiPaq on demand	None (transcoded as needed)	Import-equivalent, near-real-time	`Microsoft Fabric` capacities only

[!IMPORTANT] Fabric capacities are billed continuously while "running" — pausing the capacity (manual or via runtime API) is the only way to stop the meter. Unlike Synapse dedicated SQL pool, you cannot scale to zero by reducing the SKU; you must pause.

Decision driver	`Synapse`	`Microsoft Fabric`
Pricing transparency	Per-engine, per-DWU/vCore	Single CU pool, smoothing & throttling
Storage	`ADLS Gen2` (workspace-attached)	`OneLake` (tenant-wide)
Power BI integration	Import / DirectQuery via SQL endpoint	Direct Lake (native), Import, DirectQuery
Identity	Synapse RBAC + SQL/AD auth	`Entra ID` end-to-end
Lifecycle	GA, long-term supported	GA Nov 2023, rapid feature delivery
Best fit	Mature enterprise BI on dedicated compute	New greenfield analytics; Power BI-centric orgs

Azure Databricks — the lakehouse with deep Spark

Azure Databricks is a first-party Microsoft offering of the Databricks Lakehouse Platform, deeply integrated with Azure (managed identity, VNet injection, Private Link). It is positioned as the high-end engineering and machine-learning platform — choose it when:

You have a strong Python / Scala / SQL engineering team comfortable with notebooks.
The workload mixes large-scale ETL, ML model training, and BI on the same data.
You need open-format guarantees (Delta Lake + Unity Catalog) and want to avoid SaaS lock-in.

The Photon engine — Databricks' vectorized C++ query engine — is the differentiator versus Synapse Spark for BI/SQL workloads: it routinely runs 2– $5\times$ faster on TPC-DS-style queries.

python

# Databricks notebook: read Bronze Delta, write Silver Delta with quality checks
from pyspark.sql import functions as F

bronze = (
    spark.read.format("delta")
         .load("abfss://bronze@contosoadls.dfs.core.windows.net/orders")
)

silver = (
    bronze
    .withColumn("order_date", F.to_date("order_ts"))
    .filter("order_amount IS NOT NULL AND order_amount > 0")
    .dropDuplicates(["order_id"])
)

(
    silver.write.format("delta")
          .mode("overwrite")
          .partitionBy("order_date")
          .saveAsTable("contoso_catalog.silver.orders")
)

[!NOTE] Unity Catalog is the unit of governance — a three-level namespace catalog.schema.table with object-level grants, lineage tracking, and Delta Sharing. AZ-305 increasingly tests cross-workspace governance scenarios where Unity Catalog versus Microsoft Purview is the distractor pair.

Azure Data Explorer (ADX) and Real-Time Intelligence

Azure Data Explorer is the specialised engine for time-series, telemetry, and high-cardinality semi-structured data. It is the engine under Azure Monitor Logs, Microsoft Sentinel, and Fabric Real-Time Intelligence. Its query language is KQL.

ADX is the right answer whenever the workload is "billions of small events, queried over time windows, with sub-second latency": IoT telemetry, application logs, security events, clickstreams. It is the wrong answer for 50-row aggregate reports over a customer dimension table.

kusto

StormEvents
| where StartTime between (datetime(2025-01-01) .. datetime(2025-12-31))
| summarize EventCount = count() by State, bin(StartTime, 1d)
| top 10 by EventCount desc

ADX clusters tier data into a hot cache (SSD, sub-second) and a cold cache (blob, slower). Sizing for 99% of queries hitting hot cache is the central design choice; if hot retention is too short, queries degrade silently and the team blames the engine instead of the cache policy.

Workload signature	Right answer	Why
Time-series telemetry / logs at $> 1$ TB/day	`Azure Data Explorer` / `Fabric Real-Time Intelligence`	KQL + hot/cold cache
Star-schema BI over 1–100 TB	`Synapse dedicated SQL pool` or `Fabric Warehouse`	MPP T-SQL, predictable SLA
Lakehouse ETL + ML on petabytes	`Azure Databricks`	Photon, Unity Catalog, MLflow
Sporadic ad-hoc SQL on data lake	`Synapse serverless SQL pool`	Pay-per-TB scanned
Greenfield BI for Power BI-centric org	`Microsoft Fabric` (Lakehouse + Warehouse + Power BI)	Direct Lake, OneLake, single SKU

Worked Examples

Example 1 — Easy: dedicated vs serverless SQL pool

Scenario. Contoso Finance has a 400 GB star-schema warehouse refreshed nightly via ADF. Twelve analysts run heavy Power BI Import datasets that hit the SQL endpoint between $07{:}00$ and $19{:}00$ on weekdays; queries are idle on weekends.

Step-by-step solution.

Identify workload signature: repetitive, predictable, business-hours-bound BI on a star schema.
Compare the two pricing models: serverless at $$5$ /TB scanned versus a DW200c dedicated pool at roughly $$0.36$ /DWU-hour.
Estimate serverless cost: 12 analysts × $\sim 6$ heavy Import refreshes/day × $\sim 200$`GB scanned ≈ 14 TB/day → roughly`$70$/day →$ \sim $$1{,}500$ /month.
Estimate dedicated cost with overnight pause and weekend pause: $\sim 60$ DWU-hours/day × 20 weekdays ≈ $1{,}200$ DWU-hours/month → roughly $$430$ /month.
Provision DW200c dedicated SQL pool with an Azure Automation runbook that scales it down to DW100c overnight and pauses it on weekends.

[!NOTE] The trigger is "steady, predictable, repeated query pattern" → dedicated SQL pool. Serverless's pricing model rewards infrequent scans, not repeated dashboard hits.

Example 2 — Medium: choose between Fabric, Synapse, and Databricks for Contoso Retail

Scenario. Contoso Retail has 80 TB of historical orders in ADLS Gen2 Parquet, ingests 200 million clickstream events per day from Event Hubs, and runs a data science team of 4 that needs to train a recommender model weekly. The business wants self-service Power BI for 150 analysts.

Step-by-step solution.

Decompose into three workloads: structured BI, real-time clickstream, ML training.
Map each workload to its engine:
- Structured BI → Microsoft Fabric (Warehouse + Direct Lake) given the 150-analyst Power BI footprint.
- Clickstream → Fabric Real-Time Intelligence (KQL Database with Eventstream); Synapse and Warehouse cannot meet sub-second latency at this event rate.
- ML training → Azure Databricks for MLflow, Photon, and the team's existing Python skills.
Connect the three through OneLake: Databricks writes Delta tables into OneLake via shortcut; Fabric workloads consume those tables natively.
Pricing sketch: a single F64 capacity (~ $$8{,}400$ /month at PAYG) absorbs BI and real-time; Databricks runs as a Premium tier workspace billed per DBU only when training jobs execute.

[!NOTE] The decision pattern: structured BI → Fabric or Synapse; deep ML/engineering → Databricks; time-series → ADX/Real-Time Intelligence. Real designs combine engines through a shared lake, not pick one.

Example 3 — Hard: migrate a legacy `SQL DW` to Fabric vs Synapse vs Databricks

Scenario. A 20 TB legacy Gen1 Azure SQL Data Warehouse powers 35 Power BI dashboards. The org has standardised on Microsoft tooling, has weak Python skills, and is on a 24-month modernisation budget. The CIO wants to retire all dedicated infrastructure within two years and is open to disruption.

Step-by-step solution.

Read the constraints as exam signals: "standardised on Microsoft tooling" + "weak Python skills" + "retire dedicated infrastructure" → Fabric is the target.
Lift the schema into a Fabric Warehouse. Warehouse is MPP T-SQL like the legacy DW, so existing stored procedures and Power BI semantic models migrate with minimal rewrites. Use COPY INTO from OneLake Parquet to reload history.
Re-platform Power BI to Direct Lake semantic models that read the same Delta tables — this collapses the Import refresh chain and saves 3–4 hours of nightly batch.
Decommission the dedicated SQL pool and move incremental ETL to Fabric Pipelines / Dataflows Gen2.
Avoid Databricks: the team's Python skills are weak, and the existing assets are T-SQL stored procedures. Avoid Synapse dedicated SQL pool as the target: the CIO mandate is to retire dedicated infrastructure.

[!NOTE] The exam will frequently use phrases like "the team has limited Python skills" or "the CIO wants SaaS" — those phrases map cleanly to Fabric or Synapse over Databricks, even when the workload would technically fit Databricks too.

Visual Explanations

Loading Diagram...

Figure 2 — Mermaid diagram

Decision tree for AZ-305-style scenario items. The two big branches are "structured BI" (Synapse/Fabric) and "everything else".

Compiling TikZ diagram…

⏳

Running TeX engine…

This may take a few seconds

Figure 3 — TikZ diagram

Shared-storage architecture: every analytics engine reads from the same Delta Parquet on ADLS Gen2 / OneLake. The lake is the integration point, not any individual engine.

Engine	T-SQL?	Spark?	KQL?	Power BI native?	Best at
`Synapse dedicated SQL pool`	Yes (MPP)	No	No	DirectQuery / Import	Star-schema BI
`Synapse serverless SQL pool`	Yes (per-TB)	No	No	DirectQuery / Import	Lake exploration
`Synapse Spark pool`	Limited	Yes	No	Indirect	Engineering, ML prep
`Fabric Warehouse`	Yes (MPP)	No	No	Direct Lake	Greenfield BI
`Fabric Lakehouse`	Limited (SQL endpoint)	Yes	No	Direct Lake	Mixed engineering + BI
`Azure Databricks`	Via Photon SQL	Yes	No	DirectQuery	ML, lakehouse ETL
`Azure Data Explorer`	Limited (T-SQL emulation)	No	Yes	DirectQuery	Telemetry, logs

Common Mistakes

❌ Myth: Microsoft Fabric replaces Synapse, so any new project should start with Fabric. ✅ Reality: Fabric is the strategic successor for greenfield BI, but Synapse dedicated SQL pool is still GA and supported, and mature enterprise warehouses on dedicated SQL pool will not be forced to migrate. AZ-305 still tests both. Why it's tricky: Marketing material implies a sunset that hasn't been announced. The exam expects you to keep Synapse on the table for "existing investment" scenarios.

❌ Myth: Serverless SQL pool is cheaper than dedicated SQL pool because there are no idle compute charges. ✅ Reality: Serverless costs $$5$ per TB scanned. A dashboard team running 50 daily query sets that each scan 200 GB pays $$50$ /day or $$1{,}500$ /month for serverless versus ~ $$400$ /month for a paused-overnight DW100c dedicated pool with the same workload. Why it's tricky: "Pay only for what you use" sounds cheap until the workload is repetitive — repetition is exactly when dedicated wins.

❌ Myth: Azure Data Explorer is just a logging tool — it doesn't belong in a BI design. ✅ Reality: ADX powers production analytics for time-series, IoT, security, and clickstream workloads at companies like Microsoft itself. Any scenario describing "billions of events" with "sub-second latency on time-window aggregates" is an ADX answer, not a Synapse one. Why it's tricky: The Azure Monitor and Sentinel associations make ADX feel like an ops tool, but the same engine is the right BI answer for telemetry-shaped data.

❌ Myth: Direct Lake is just DirectQuery rebranded. ✅ Reality: Direct Lake loads Delta Parquet directly into the VertiPaq in-memory engine — query performance is Import-mode-equivalent. DirectQuery pushes every visual's query down to the source SQL engine and is bottlenecked by source latency. Why it's tricky: Both modes "avoid scheduled refresh," which makes them sound interchangeable. The performance profile is radically different and is a common AZ-305 distractor pair.

❌ Myth: Databricks is the "always faster" option, so recommend it whenever performance is mentioned. ✅ Reality: Databricks Photon does outperform Spark in many BI benchmarks, but the right answer also depends on team skills, governance posture, and Power BI integration. A Python-light team with Power BI-first reporting will get more value from Fabric than from Databricks, even if Photon wins a benchmark. Why it's tricky: AZ-305 explicitly tests "fit-for-purpose" reasoning — the fastest engine in isolation is not always the right architectural choice.

Synapse serverless SQL pool over ADLS Gen2 Parquet. Cost scales with bytes scanned, not wall-clock — perfect for sporadic burst workloads. Fabric is rejected because the smallest capacity (F2) still bills continuously while running; pausing/resuming adds operational friction. Dedicated SQL pool is rejected because even paused, the team risks forgetting to pause.

Summary & Concept Map

Four engines, one storage layer. Synapse, Fabric, Databricks, and Data Explorer all read from ADLS Gen2 / OneLake Delta Parquet — pick the engine, not the data location.
Workload signature, not popularity, drives the choice. Star-schema BI → Synapse or Fabric; lakehouse ETL + ML → Databricks; telemetry / time-series → Data Explorer; ad-hoc lake SQL → serverless SQL pool.
Fabric is the SaaS successor for greenfield BI, but Synapse dedicated SQL pool remains the right answer for existing mature warehouses with predictable load.
Serverless vs dedicated is a function of repetition, not data size. Repetitive dashboards = dedicated; sporadic exploration = serverless.
Power BI consumption mode is the architecture's downstream constraint. Direct Lake (Fabric-only) is the only mode that delivers Import-mode performance with no scheduled refresh.
Governance posture and team skills are exam-decisive signals. "Limited Python skills" → not Databricks. "CIO wants SaaS" → Fabric. "CMK + Private Link + 7-year audit" → Synapse or Databricks tightly governed.
Real designs combine engines through a shared lake — single-engine answers are exam distractors more often than they are correct designs.

Loading Diagram...

Figure 4 — Mermaid diagram

Concept map: workload signature → engine → Power BI consumption mode.

Why This Matters

Prerequisites

Azure storage tiers and ADLS Gen2 — analytics engines read from object storage in 99% of designs. Quick check: what is the difference between a flat blob and a hierarchical namespace?
Data ingestion patterns (LO20) — Synapse Pipelines, ADF, Event Hubs, and IoT Hub feed every analytics workload. Quick check: which Azure service is the typical front door for high-throughput event ingestion?
Parquet and Delta Lake — every lakehouse design depends on columnar storage and ACID transactions over object storage. Quick check: why is Parquet preferred over CSV for analytical queries?
Microsoft Entra ID and RBAC — analytics platforms enforce row- and column-level security with Entra ID group membership. Quick check: how does Entra ID authentication differ from SQL authentication?
Power BI fundamentals — the consumption layer for almost every analytics design. Quick check: what is a semantic model (formerly "dataset") in Power BI?
T-SQL or Spark literacy — you don't need to be expert, but you must read a join plan and recognise when a query is data-bound versus compute-bound. Quick check: what does a broadcast hash join optimise versus a shuffle hash join?

Learning Objectives

By the end of this lesson, the learner will be able to:

Differentiate the four core Azure analytics engines (Synapse, Fabric, Databricks, Data Explorer) by workload pattern, compute model, and pricing.
Recommend an analytics engine for a given scenario using a deterministic decision tree.
Evaluate the trade-offs between dedicated SQL pool, serverless SQL pool, and Apache Spark pool within Synapse.
Design a Fabric capacity sizing and OneLake layout for a multi-workload tenant.
Analyse governance and security implications (RBAC, row-level security, customer-managed keys) across the four engines.
Map any chosen analytics tier to a Power BI consumption mode (Import / DirectQuery / Direct Lake).

Building Blocks

Deep Dive

Azure Synapse Analytics — the IaaS/PaaS workhorse

sql

CREATE TABLE dbo.FactSales
WITH (
  DISTRIBUTION = HASH(CustomerKey),
  CLUSTERED COLUMNSTORE INDEX,
  PARTITION (
    OrderDateKey RANGE RIGHT FOR VALUES
    (20240101, 20240201, 20240301, 20240401)
  )
)
AS
SELECT * FROM ext.Sales_Staging;

[!TIP] Choose the smallest dedicated SQL pool size that meets your P95 query SLA, then scale up on demand with ALTER DATABASE ... MODIFY (SERVICE_OBJECTIVE). Pausing the pool (0 DWU) stops compute billing entirely — storage billing continues.

[!WARNING] Serverless SQL pool meters bytes scanned, not rows returned. A SELECT TOP 10 over an unpartitioned 20 TB Parquet table can cost real money. Always partition external tables and push predicates that prune partitions.

Synapse engine	Pricing model	Best for	Avoid for
`Dedicated SQL pool`	Per-DWU hour (paused = compute free)	Steady BI / regulatory workloads, $> 1$ TB warehouse	Sporadic ad-hoc queries; modern lakehouse
`Serverless SQL pool`	Per-TB scanned ( $$5$ /TB)	Ad-hoc data lake exploration; logical data warehouse	Frequent dashboard hits over the same data
`Apache Spark pool`	Per-vCore hour	Data engineering, ML feature prep, large Delta tables	Sub-second BI queries

Microsoft Fabric — the SaaS analytics platform

Loading Diagram...

Figure 1 — Mermaid diagram

Power BI mode	Where data lives	Refresh model	Performance profile	Where it is available
`Import`	VertiPaq, in-memory copy	Scheduled refresh	Fast, but stale between refreshes	All Power BI tiers
`DirectQuery`	Source SQL engine	Query on demand	Bound by source latency	All Power BI tiers
`Direct Lake`	OneLake Delta → VertiPaq on demand	None (transcoded as needed)	Import-equivalent, near-real-time	`Microsoft Fabric` capacities only

[!IMPORTANT] Fabric capacities are billed continuously while "running" — pausing the capacity (manual or via runtime API) is the only way to stop the meter. Unlike Synapse dedicated SQL pool, you cannot scale to zero by reducing the SKU; you must pause.

Decision driver	`Synapse`	`Microsoft Fabric`
Pricing transparency	Per-engine, per-DWU/vCore	Single CU pool, smoothing & throttling
Storage	`ADLS Gen2` (workspace-attached)	`OneLake` (tenant-wide)
Power BI integration	Import / DirectQuery via SQL endpoint	Direct Lake (native), Import, DirectQuery
Identity	Synapse RBAC + SQL/AD auth	`Entra ID` end-to-end
Lifecycle	GA, long-term supported	GA Nov 2023, rapid feature delivery
Best fit	Mature enterprise BI on dedicated compute	New greenfield analytics; Power BI-centric orgs

Azure Databricks — the lakehouse with deep Spark

You have a strong Python / Scala / SQL engineering team comfortable with notebooks.
The workload mixes large-scale ETL, ML model training, and BI on the same data.
You need open-format guarantees (Delta Lake + Unity Catalog) and want to avoid SaaS lock-in.

The Photon engine — Databricks' vectorized C++ query engine — is the differentiator versus Synapse Spark for BI/SQL workloads: it routinely runs 2– $5\times$ faster on TPC-DS-style queries.

python

# Databricks notebook: read Bronze Delta, write Silver Delta with quality checks
from pyspark.sql import functions as F

bronze = (
    spark.read.format("delta")
         .load("abfss://bronze@contosoadls.dfs.core.windows.net/orders")
)

silver = (
    bronze
    .withColumn("order_date", F.to_date("order_ts"))
    .filter("order_amount IS NOT NULL AND order_amount > 0")
    .dropDuplicates(["order_id"])
)

(
    silver.write.format("delta")
          .mode("overwrite")
          .partitionBy("order_date")
          .saveAsTable("contoso_catalog.silver.orders")
)

[!NOTE] Unity Catalog is the unit of governance — a three-level namespace catalog.schema.table with object-level grants, lineage tracking, and Delta Sharing. AZ-305 increasingly tests cross-workspace governance scenarios where Unity Catalog versus Microsoft Purview is the distractor pair.

Azure Data Explorer (ADX) and Real-Time Intelligence

kusto

StormEvents
| where StartTime between (datetime(2025-01-01) .. datetime(2025-12-31))
| summarize EventCount = count() by State, bin(StartTime, 1d)
| top 10 by EventCount desc

Workload signature	Right answer	Why
Time-series telemetry / logs at $> 1$ TB/day	`Azure Data Explorer` / `Fabric Real-Time Intelligence`	KQL + hot/cold cache
Star-schema BI over 1–100 TB	`Synapse dedicated SQL pool` or `Fabric Warehouse`	MPP T-SQL, predictable SLA
Lakehouse ETL + ML on petabytes	`Azure Databricks`	Photon, Unity Catalog, MLflow
Sporadic ad-hoc SQL on data lake	`Synapse serverless SQL pool`	Pay-per-TB scanned
Greenfield BI for Power BI-centric org	`Microsoft Fabric` (Lakehouse + Warehouse + Power BI)	Direct Lake, OneLake, single SKU

Worked Examples

Example 1 — Easy: dedicated vs serverless SQL pool

Step-by-step solution.

Identify workload signature: repetitive, predictable, business-hours-bound BI on a star schema.
Compare the two pricing models: serverless at $$5$ /TB scanned versus a DW200c dedicated pool at roughly $$0.36$ /DWU-hour.
Estimate serverless cost: 12 analysts × $\sim 6$ heavy Import refreshes/day × $\sim 200$`GB scanned ≈ 14 TB/day → roughly`$70$/day →$ \sim $$1{,}500$ /month.
Estimate dedicated cost with overnight pause and weekend pause: $\sim 60$ DWU-hours/day × 20 weekdays ≈ $1{,}200$ DWU-hours/month → roughly $$430$ /month.
Provision DW200c dedicated SQL pool with an Azure Automation runbook that scales it down to DW100c overnight and pauses it on weekends.

[!NOTE] The trigger is "steady, predictable, repeated query pattern" → dedicated SQL pool. Serverless's pricing model rewards infrequent scans, not repeated dashboard hits.

Example 2 — Medium: choose between Fabric, Synapse, and Databricks for Contoso Retail

Step-by-step solution.

Decompose into three workloads: structured BI, real-time clickstream, ML training.
Map each workload to its engine:
- Structured BI → Microsoft Fabric (Warehouse + Direct Lake) given the 150-analyst Power BI footprint.
- Clickstream → Fabric Real-Time Intelligence (KQL Database with Eventstream); Synapse and Warehouse cannot meet sub-second latency at this event rate.
- ML training → Azure Databricks for MLflow, Photon, and the team's existing Python skills.
Connect the three through OneLake: Databricks writes Delta tables into OneLake via shortcut; Fabric workloads consume those tables natively.
Pricing sketch: a single F64 capacity (~ $$8{,}400$ /month at PAYG) absorbs BI and real-time; Databricks runs as a Premium tier workspace billed per DBU only when training jobs execute.

[!NOTE] The decision pattern: structured BI → Fabric or Synapse; deep ML/engineering → Databricks; time-series → ADX/Real-Time Intelligence. Real designs combine engines through a shared lake, not pick one.

Example 3 — Hard: migrate a legacy `SQL DW` to Fabric vs Synapse vs Databricks

Step-by-step solution.

Read the constraints as exam signals: "standardised on Microsoft tooling" + "weak Python skills" + "retire dedicated infrastructure" → Fabric is the target.
Lift the schema into a Fabric Warehouse. Warehouse is MPP T-SQL like the legacy DW, so existing stored procedures and Power BI semantic models migrate with minimal rewrites. Use COPY INTO from OneLake Parquet to reload history.
Re-platform Power BI to Direct Lake semantic models that read the same Delta tables — this collapses the Import refresh chain and saves 3–4 hours of nightly batch.
Decommission the dedicated SQL pool and move incremental ETL to Fabric Pipelines / Dataflows Gen2.
Avoid Databricks: the team's Python skills are weak, and the existing assets are T-SQL stored procedures. Avoid Synapse dedicated SQL pool as the target: the CIO mandate is to retire dedicated infrastructure.

[!NOTE] The exam will frequently use phrases like "the team has limited Python skills" or "the CIO wants SaaS" — those phrases map cleanly to Fabric or Synapse over Databricks, even when the workload would technically fit Databricks too.

Visual Explanations

Loading Diagram...

Figure 2 — Mermaid diagram

Decision tree for AZ-305-style scenario items. The two big branches are "structured BI" (Synapse/Fabric) and "everything else".

Compiling TikZ diagram…

⏳

Running TeX engine…

This may take a few seconds

Figure 3 — TikZ diagram

Shared-storage architecture: every analytics engine reads from the same Delta Parquet on ADLS Gen2 / OneLake. The lake is the integration point, not any individual engine.

Engine	T-SQL?	Spark?	KQL?	Power BI native?	Best at
`Synapse dedicated SQL pool`	Yes (MPP)	No	No	DirectQuery / Import	Star-schema BI
`Synapse serverless SQL pool`	Yes (per-TB)	No	No	DirectQuery / Import	Lake exploration
`Synapse Spark pool`	Limited	Yes	No	Indirect	Engineering, ML prep
`Fabric Warehouse`	Yes (MPP)	No	No	Direct Lake	Greenfield BI
`Fabric Lakehouse`	Limited (SQL endpoint)	Yes	No	Direct Lake	Mixed engineering + BI
`Azure Databricks`	Via Photon SQL	Yes	No	DirectQuery	ML, lakehouse ETL
`Azure Data Explorer`	Limited (T-SQL emulation)	No	Yes	DirectQuery	Telemetry, logs

Common Mistakes

❌ Myth: Microsoft Fabric replaces Synapse, so any new project should start with Fabric. ✅ Reality: Fabric is the strategic successor for greenfield BI, but Synapse dedicated SQL pool is still GA and supported, and mature enterprise warehouses on dedicated SQL pool will not be forced to migrate. AZ-305 still tests both. Why it's tricky: Marketing material implies a sunset that hasn't been announced. The exam expects you to keep Synapse on the table for "existing investment" scenarios.

❌ Myth: Serverless SQL pool is cheaper than dedicated SQL pool because there are no idle compute charges. ✅ Reality: Serverless costs $$5$ per TB scanned. A dashboard team running 50 daily query sets that each scan 200 GB pays $$50$ /day or $$1{,}500$ /month for serverless versus ~ $$400$ /month for a paused-overnight DW100c dedicated pool with the same workload. Why it's tricky: "Pay only for what you use" sounds cheap until the workload is repetitive — repetition is exactly when dedicated wins.

❌ Myth: Azure Data Explorer is just a logging tool — it doesn't belong in a BI design. ✅ Reality: ADX powers production analytics for time-series, IoT, security, and clickstream workloads at companies like Microsoft itself. Any scenario describing "billions of events" with "sub-second latency on time-window aggregates" is an ADX answer, not a Synapse one. Why it's tricky: The Azure Monitor and Sentinel associations make ADX feel like an ops tool, but the same engine is the right BI answer for telemetry-shaped data.

❌ Myth: Direct Lake is just DirectQuery rebranded. ✅ Reality: Direct Lake loads Delta Parquet directly into the VertiPaq in-memory engine — query performance is Import-mode-equivalent. DirectQuery pushes every visual's query down to the source SQL engine and is bottlenecked by source latency. Why it's tricky: Both modes "avoid scheduled refresh," which makes them sound interchangeable. The performance profile is radically different and is a common AZ-305 distractor pair.

❌ Myth: Databricks is the "always faster" option, so recommend it whenever performance is mentioned. ✅ Reality: Databricks Photon does outperform Spark in many BI benchmarks, but the right answer also depends on team skills, governance posture, and Power BI integration. A Python-light team with Power BI-first reporting will get more value from Fabric than from Databricks, even if Photon wins a benchmark. Why it's tricky: AZ-305 explicitly tests "fit-for-purpose" reasoning — the fastest engine in isolation is not always the right architectural choice.

Practice Exercises

Exercise 1 🟢 Easy

Contoso has a 300 GB warehouse refreshed nightly and queried during business hours by 20 analysts. Which Synapse compute engine should anchor the design?

▶💡 Hint

Repetitive, predictable query pattern over a moderate-size warehouse.

▶✅ Solution

Exercise 2 🟢 Easy

A team has 4 TB of historical Parquet in ADLS Gen2 and wants ad-hoc T-SQL exploration without provisioning compute. Recommend an engine.

▶💡 Hint

Pay-per-query, no compute to manage.

▶✅ Solution

Synapse serverless SQL pool with OPENROWSET over the Parquet files. Optionally promote to external tables for governance.

Exercise 3 🟡 Medium

A logistics company captures 500 million GPS-ping events per day from 2 million vehicles and needs sub-second "vehicles by region over the last hour" queries on a dashboard. Which engine fits?

▶💡 Hint

Time-series, high cardinality, sub-second latency.

▶✅ Solution

Exercise 4 🟡 Medium

▶💡 Hint

Lakehouse + ML + open formats.

▶✅ Solution

Exercise 5 🟡 Medium

▶💡 Hint

Greenfield, Power BI-centric, Microsoft-first, no Spark skills.

▶✅ Solution

Microsoft Fabric with a Warehouse for the fact tables and Direct Lake semantic models for Power BI. Sizing: start at F64, scale to F128 if Direct Lake fallback to DirectQuery becomes frequent.

Exercise 6 🔴 Hard

▶💡 Hint

Two workloads, two engines, one OneLake.

▶✅ Solution

Exercise 7 🔴 Hard

▶💡 Hint

Three governance requirements: CMK, Private Link, long audit retention.

▶✅ Solution

Exercise 8 🔴 Hard

A startup has $$3{,}000$ /month total cloud budget, expects bursty analytical workloads (30 minutes of activity, then 4 hours idle), and is unwilling to pay for any idle compute. Which engine?

▶💡 Hint

Bursty, idle-tolerant, cost-constrained.

▶✅ Solution

Summary & Concept Map

Four engines, one storage layer. Synapse, Fabric, Databricks, and Data Explorer all read from ADLS Gen2 / OneLake Delta Parquet — pick the engine, not the data location.
Workload signature, not popularity, drives the choice. Star-schema BI → Synapse or Fabric; lakehouse ETL + ML → Databricks; telemetry / time-series → Data Explorer; ad-hoc lake SQL → serverless SQL pool.
Fabric is the SaaS successor for greenfield BI, but Synapse dedicated SQL pool remains the right answer for existing mature warehouses with predictable load.
Serverless vs dedicated is a function of repetition, not data size. Repetitive dashboards = dedicated; sporadic exploration = serverless.
Power BI consumption mode is the architecture's downstream constraint. Direct Lake (Fabric-only) is the only mode that delivers Import-mode performance with no scheduled refresh.
Governance posture and team skills are exam-decisive signals. "Limited Python skills" → not Databricks. "CIO wants SaaS" → Fabric. "CMK + Private Link + 7-year audit" → Synapse or Databricks tightly governed.
Real designs combine engines through a shared lake — single-engine answers are exam distractors more often than they are correct designs.

Loading Diagram...

Figure 4 — Mermaid diagram

Concept map: workload signature → engine → Power BI consumption mode.

Recommend a Solution for Data Analysis — Lesson

Recommend a Solution for Data Analysis — Lesson

Why This Matters

Prerequisites

Learning Objectives

Building Blocks

Deep Dive

Azure Synapse Analytics — the IaaS/PaaS workhorse

Microsoft Fabric — the SaaS analytics platform

Azure Databricks — the lakehouse with deep Spark

Azure Data Explorer (ADX) and Real-Time Intelligence

Worked Examples

Example 1 — Easy: dedicated vs serverless SQL pool

Example 2 — Medium: choose between Fabric, Synapse, and Databricks for Contoso Retail

Example 3 — Hard: migrate a legacy SQL DW to Fabric vs Synapse vs Databricks

Visual Explanations

Common Mistakes

Practice Exercises

Exercise 1 🟢 Easy

Exercise 2 🟢 Easy

Exercise 3 🟡 Medium

Exercise 4 🟡 Medium

Exercise 5 🟡 Medium

Exercise 6 🔴 Hard

Exercise 7 🔴 Hard

Exercise 8 🔴 Hard

Summary & Concept Map

Recommend a Solution for Data Analysis — Lesson

Recommend a Solution for Data Analysis — Lesson

Why This Matters

Prerequisites

Learning Objectives

Building Blocks

Deep Dive

Azure Synapse Analytics — the IaaS/PaaS workhorse

Microsoft Fabric — the SaaS analytics platform

Azure Databricks — the lakehouse with deep Spark

Azure Data Explorer (ADX) and Real-Time Intelligence

Worked Examples

Example 1 — Easy: dedicated vs serverless SQL pool

Example 2 — Medium: choose between Fabric, Synapse, and Databricks for Contoso Retail

Example 3 — Hard: migrate a legacy SQL DW to Fabric vs Synapse vs Databricks

Visual Explanations

Common Mistakes

Practice Exercises

Exercise 1 🟢 Easy

Exercise 2 🟢 Easy

Exercise 3 🟡 Medium

Exercise 4 🟡 Medium

Exercise 5 🟡 Medium

Exercise 6 🔴 Hard

Exercise 7 🔴 Hard

Exercise 8 🔴 Hard

Summary & Concept Map

Example 3 — Hard: migrate a legacy `SQL DW` to Fabric vs Synapse vs Databricks

Example 3 — Hard: migrate a legacy `SQL DW` to Fabric vs Synapse vs Databricks