This domain is worth 25 to 30% of DP-900, tied for the largest. It covers how data gets into an analytics platform, where it is stored, which Microsoft services process it, how real-time differs from batch, and how Power BI turns it into visuals. Service names have shifted toward Microsoft Fabric, so learn Fabric terms first and know the older Azure names too.
Large-scale analytics: the building blocks
Ingestion and processing
Ingestion copies data from sources into the analytics platform; processing cleans and reshapes it.
| Step | What happens |
|---|---|
| Ingest | Pull from databases, files, APIs, SaaS and streams into a lake or warehouse |
| Transform | Filter, clean, join, aggregate, convert formats |
| Load | Write to the analytical store |
| Serve | Expose to SQL queries, BI tools and ML |
- ETL (extract, transform, load): transformation happens in a separate engine before loading. Suits strict schemas and sensitive data cleaning.
- ELT (extract, load, transform): load raw data first and transform inside the scalable store (lake, warehouse). The modern, cloud pattern.
- Pipelines orchestrate activities on a schedule or trigger: copy data, run notebooks, call stored procedures, branch and loop. They move and control data; they are not where heavy computation runs.
- Dataflows give a low-code, Power Query-style way to transform data visually.
- Processing engines: SQL, Apache Spark (distributed, in-memory, Python/Scala/SQL notebooks) and Power Query.
Analytical data stores
| Store | Description |
|---|---|
| Data warehouse | Relational, structured, modelled data (star schema) queried with SQL; designed for reporting at scale |
| Data lake | Raw files of any type in cheap object storage (ADLS Gen2, OneLake); schema applied on read |
| Lakehouse | Files in a lake plus Delta tables with a SQL endpoint; one copy of data usable by Spark and SQL |
| OLAP / semantic model | Pre-aggregated model with measures and hierarchies for fast slicing |
Tabular, columnar formats (Parquet, Delta) are the norm for analytical files.
Medallion architecture
A common way to organise a lakehouse: bronze (raw as ingested), silver (cleaned and conformed), gold (business-ready aggregates for reports).
Microsoft cloud services for large-scale analytics
Microsoft Fabric
Microsoft Fabric is a unified, SaaS analytics platform that brings data integration, engineering, warehousing, real-time analytics, data science and BI into one product, with one billing model (capacity), one security model and one lake.
- OneLake is the single, tenant-wide logical data lake (“OneDrive for data”). Everything stores data as Delta/Parquet. Shortcuts reference data in other locations (ADLS, S3, other lakehouses) without copying.
- Workspaces hold items and are tied to a Fabric capacity (F SKUs, or Power BI Premium capacity).
| Fabric experience / item | Purpose |
|---|---|
| Data Factory (pipelines, Dataflow Gen2) | Ingest and orchestrate; ETL/ELT |
| Lakehouse | Files and Delta tables in OneLake, Spark, automatic SQL analytics endpoint |
| Warehouse | Fully T-SQL relational warehouse over OneLake |
| Data Engineering | Spark notebooks and jobs |
| Data Science | ML models and experiments |
| Real-Time Intelligence | Eventstreams, Eventhouse, KQL, Real-Time dashboards, Activator |
| Power BI | Reports, semantic models, dashboards |
| Data Activator (Activator) | Trigger actions when data meets a condition |
| Mirroring | Continuous replication from Azure SQL, Cosmos DB, Snowflake and others into OneLake |
| Copilot | AI assistance for notebooks, SQL and report creation |
Azure Databricks
- A managed Apache Spark analytics platform built with Databricks, integrated with Azure (Microsoft Entra ID, ADLS, Power BI).
- Used by data engineers and data scientists with notebooks in Python, Scala, SQL and R; strong for big-data processing, streaming, machine learning and lakehouse designs using Delta Lake.
- Compute is clusters (or serverless) that scale and auto-terminate; Unity Catalog governs data and AI assets.
- Choose Databricks for open-source Spark and ML-centred teams or existing Databricks skills; choose Fabric for a SaaS, all-in-one, low-setup experience with Power BI.
Azure Synapse Analytics
- Earlier unified analytics service combining dedicated SQL pools (MPP warehouse), serverless SQL pool (query lake files on demand), Spark pools and pipelines.
- Fabric is its strategic successor for new analytics projects, but Synapse still appears in the study guide’s documentation list and in questions.
Azure Data Factory
- Managed cloud ETL/ELT and orchestration, with 90+ connectors, mapping data flows (visual Spark transforms) and integration runtimes (including self-hosted for on-premises data). Fabric Data Factory is the Fabric version.
Azure HDInsight
- Managed open-source clusters (Hadoop, Spark, Kafka, HBase). Mostly legacy; know it exists for lift and shift of Hadoop workloads.
Chooser
| Need | Pick |
|---|---|
| One SaaS platform for all analytics with Power BI | Microsoft Fabric |
| Spark/ML notebooks with open-source tooling | Azure Databricks |
| Orchestrate and copy data from many sources | Data Factory / Fabric pipelines |
| Query Parquet files in a lake with T-SQL, no infrastructure | Serverless SQL (Synapse) or Lakehouse SQL analytics endpoint (Fabric) |
| MPP data warehouse | Fabric Warehouse (or Synapse dedicated SQL pool) |
Real-time data analytics
Batch versus streaming
| Batch | Streaming | |
|---|---|---|
| Data handled | Large buffered sets processed together | Continuous events processed as they arrive |
| Latency | Minutes to hours | Milliseconds to seconds |
| Trigger | Schedule or volume | Each event or small time window |
| Example | Nightly sales load, monthly payroll | Fraud detection, live sensor alerts, clickstream |
| Cost and complexity | Simpler, cheaper per unit | Always-on, more complex |
Choose streaming when the value of data decays quickly; choose batch when a delay is acceptable. Many systems combine both.
Streaming concepts
- Event: a small record of something that happened, with a timestamp.
- Stream processing applies filters, aggregations and windowing (tumbling, hopping, sliding, session) over time.
- Event ingestion brokers decouple producers from consumers and buffer bursts.
Microsoft services for real-time
| Service | Role |
|---|---|
| Azure Event Hubs | Big-data event ingestion: millions of events per second, partitions, consumer groups, Kafka-compatible endpoint |
| Azure IoT Hub | Bidirectional device messaging, device identity, cloud-to-device commands |
| Azure Stream Analytics | Managed real-time processing using SQL-like queries over streams from Event Hubs, IoT Hub or Blob storage, with output to Power BI, SQL, storage, Cosmos DB |
| Spark Structured Streaming | Streaming in Spark notebooks (Fabric, Databricks, Synapse), writing to Delta tables |
| Fabric Real-Time Intelligence | Eventstreams (ingest, route and transform events), Eventhouse (KQL database optimised for time-series and logs), KQL querysets, Real-Time dashboards, Activator alerts |
| Azure Data Explorer | Engine behind Eventhouse; fast analytics on telemetry using Kusto Query Language (KQL) |
Typical flow: devices/apps → Event Hubs or IoT Hub → Stream Analytics (or Eventstream) → Power BI dashboard / storage / Eventhouse.
Data visualization in Microsoft Power BI
Power BI capabilities
- Power BI Desktop: free Windows authoring tool to connect, shape (Power Query), model and build reports.
- Power BI service (app.powerbi.com): SaaS for publishing, sharing, dashboards, scheduled refresh, collaboration and apps. Power BI mobile for viewing.
- Power BI Report Server: on-premises hosting of reports.
- Licences: Free, Pro, Premium Per User, and capacity-based (Fabric/Premium).
- Connects to hundreds of sources, with import, DirectQuery and Direct Lake (Fabric) connection modes.
- Typical workflow: build in Desktop, publish to the service, share as report or dashboard.
| Item | Meaning |
|---|---|
| Semantic model (formerly dataset) | The data and model behind reports |
| Report | One or more pages of visuals, from a single model |
| Dashboard | A single page of pinned tiles drawn from different reports; service only |
| Workspace | Collaboration container |
Data model features
- Tables and relationships between them, with cardinality (one-to-many, many-to-many) and cross-filter direction.
- Star schema (fact and dimension tables) is the recommended shape for performance and simpler DAX.
- Measures are calculations evaluated at query time using DAX (Data Analysis Expressions), such as
Total Sales = SUM(Sales[Amount]); calculated columns are computed at refresh and stored per row. - Hierarchies (Year > Quarter > Month) for drill-down; date tables for time intelligence.
- Row-level security (RLS) restricts which rows users see.
- Power Query (M language) cleans and shapes data before it enters the model.
Choosing the right visualization
| Goal | Visual |
|---|---|
| Compare categories | Bar or column chart |
| Trend over time | Line or area chart |
| Parts of a whole (few categories) | Pie or donut (avoid with many slices); treemap for many |
| Relationship of two numeric variables | Scatter plot |
| Single key number | Card or KPI |
| Geographic distribution | Map or filled map |
| Detailed values | Table or matrix (matrix groups rows and columns) |
| Progress toward a target | Gauge or KPI |
| Breakdown of what drives a value | Waterfall, decomposition tree, key influencers |
| Interactive filtering | Slicers |
Good practice: one message per visual, consistent colours, labelled axes, and limit visuals per page.
Exam checklist
- Describe ingest, process, store, serve; separate ETL from ELT; know pipelines orchestrate.
- Distinguish warehouse, lake, lakehouse; know Delta, Parquet and OneLake.
- Place Fabric, Databricks, Synapse, Data Factory and HDInsight by purpose.
- Contrast batch and streaming on latency and data shape.
- Know Event Hubs, IoT Hub, Stream Analytics, Eventstreams, Eventhouse and KQL, and where each sits in a pipeline.
- Know Power BI Desktop, service, report versus dashboard, DAX measures, relationships, star schema, RLS.
- Pick the right chart for the goal.