All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
18 min
Choosing a data store: lakehouse, warehouse or eventhouse
Pick the right Fabric store from the data, the team's skills and the workload.
By the end of this lesson you can
- Choose a Lakehouse, Warehouse or Eventhouse from a scenario
- Explain what the SQL analytics endpoint can and can't do
- Recognize when SQL database, Cosmos DB or a mirrored database fits
- Spot the constraint word that decides the store
From earlier lessons · 1 of 2
A SQL Server database sits in Juniper Coffee's head office, behind the firewall. What must exist before Fabric can read it?
Pick one answer.
From earlier lessons · 2 of 2
In the OneLake catalog, where do you look to see what feeds a lakehouse and what depends on it?
Pick one answer.
Juniper Coffee's data has a way in. Now three requests land on your desk. Finance wants a sales model built in T-SQL, which they've written for years. The data science intern wants café photos next to the sales tables. The loyalty team wants app events answered in seconds. Each one needs a home, and one store won't suit all three.
Lakehouse, Warehouse or Eventhouse
Exam depth: know it cold.
All three keep their data in OneLake. They differ in the data they suit, the language that writes to them, and how fresh the data is.
- Lakehouse: files and Delta tables of any shape. That includes structured tables, semi-structured data such as JSON, and unstructured files such as images. You write to it with Spark.
- Warehouse: structured tables, built and changed with full T-SQL. T-SQL is Microsoft's dialect of SQL. A Warehouse supports multi-table transactions: several tables change together, or none do.
- Eventhouse: time-stamped events that stream in, queried in seconds with KQL (Kusto Query Language). It holds one or more KQL databases.
| Lakehouse | Warehouse | Eventhouse | |
|---|---|---|---|
| Data type | Any: tables, JSON, files, images. | Structured tables. | Events, logs, telemetry, JSON, text. |
| Writes with | Spark, pipelines, dataflows; shortcuts link data in. | T-SQL (COPY INTO, INSERT, CTAS), pipelines, dataflows. | Eventstream, Get data and other ingestion. |
| Reads with | Spark, or read-only T-SQL on the SQL analytics endpoint. | T-SQL. | KQL, and T-SQL. |
| Team skills | PySpark, notebooks. | SQL, star schema design. | KQL, no code. |
| Freshness | As of the last load. | As of the last load. | Seconds, as events arrive. |
| Built for | Big data, data engineering, machine learning. | Enterprise warehousing, SQL-based BI. | Streaming and time-series analytics. |
CTAS is CREATE TABLE AS SELECT: build a new table from a query.
Decision rule: streaming events go to an Eventhouse. A T-SQL team that writes, or a need for multi-table transactions, gets a Warehouse. Spark, mixed data or "not sure" gets a Lakehouse.
You are an analytics engineer at Juniper Coffee. The loyalty app sends an event every time a customer scans, millions a day, as JSON. Marketing needs answers about the last hour within seconds. You need to choose where to store the events. What should you use?
Pick one answer.
The SQL analytics endpoint reads, it doesn't write
Exam depth: know it cold.
Every Lakehouse gets a SQL analytics endpoint: a T-SQL view of its Delta tables, created for you. It runs any SELECT query. But it has no DML, the statements that change rows: no INSERT, UPDATE or DELETE on its tables.
Its DDL, the statements that create objects, is limited. You can create views and table-valued functions (functions that return a table). You can't create or load tables.
So a SQL team can read a Lakehouse in T-SQL, but it can't load one. Spark, pipelines, dataflows or shortcuts fill Lakehouse tables. If a SQL team must write tables, give it a Warehouse.
You are an analytics engineer at Juniper Coffee. Data engineers load the till sales into Delta tables with PySpark. You and the BI team only read those tables with T-SQL, and you want saved views for reports. You need one store for everyone. What should you use?
Pick one answer.
SQL database, Cosmos DB and mirrored databases
Exam depth: recognize it.
Three more items store data in Fabric. You won't design with them on DP-600, but you must spot them in an option list.
| Item | What it is | The scenario says |
|---|---|---|
| SQL database in Fabric | The Azure SQL engine. For operational (OLTP) apps with many small reads and writes. | "operational", "OLTP", "app database", "foreign keys". |
| Cosmos DB in Fabric | A NoSQL database for apps, reached through REST APIs. | "NoSQL", "documents", "app developers". |
| Mirrored database | A read-only, near real-time copy of an outside database, such as Azure SQL Database or Snowflake. | "replicate", "no ETL", "keep in sync". |
All three make their data available in OneLake in an open table format. So reports read the copy in OneLake, not the busy source. Mirroring gets a full lesson next.
You are an analytics engineer at Juniper Coffee. A developer is building a stock-ordering app for the cafés. It needs many small inserts and updates, strict foreign keys and full transactions. You also want its data available for analytics in OneLake. What should the developer use?
Pick one answer.
One lake, many stores
Exam depth: know it cold.
Choosing one store doesn't lock the data away. Lakehouse, Warehouse and mirrored database tables are all Delta tables in OneLake.
Inside one workspace, a Warehouse query can join them with three-part names: item.schema.table. For example, juniper_lakehouse.dbo.till_sales reads a Lakehouse table from inside a Warehouse query. This is a cross-database query, and it copies nothing.
One exception: an Eventhouse's KQL database isn't in OneLake as Delta by default. You turn that on with OneLake availability, covered next lesson.
Predict, then reveal
Juniper's finance team has built SQL Server warehouses for years and wants to bring its T-SQL scripts to Fabric with few changes. Before you look: which store, and why?
The constraint words that decide the store:
| The scenario says | Pick |
|---|---|
| "streaming", "telemetry", "logs", "within seconds", "KQL". | Eventhouse |
| "multi-table transactions", "T-SQL team loads", "stored procedures", "existing SQL Server code". | Warehouse |
| "PySpark", "notebooks", "images", "unstructured", "machine learning". | Lakehouse |
| "T-SQL users only read". | Lakehouse with its SQL analytics endpoint |
| "operational app", "OLTP". | SQL database in Fabric |
| "NoSQL", "documents". | Cosmos DB in Fabric |
Exam-style practice
You are an analytics engineer at Juniper Coffee. Each night, a T-SQL script must update the sales fact table and the store dimension together. If either update fails, neither may change. The team writes only T-SQL. You need a store. What should you use?
Pick one answer.
You are an analytics engineer at Juniper Coffee. A data scientist will train a model on photos of café displays together with the daily sales tables. You need one store for the photos and the tables. What should you use?
Pick one answer.
You are an analytics engineer at Juniper Coffee. Finance has 150 T-SQL stored procedures that load and reshape budget tables. They want to keep the code, and nobody on the team knows Spark. You need a store for the budget data. What should you use?
Pick one answer.
Worth keeping
- Streaming events: Eventhouse. T-SQL writers or multi-table transactions: Warehouse. Everything else: Lakehouse.
- The SQL analytics endpoint reads with T-SQL and holds views, but never loads tables.
- SQL database is for operational apps, Cosmos DB for NoSQL, mirrored databases for read-only replicas.
- All stores share OneLake, so three-part names join them without copies.
- The sentence about the team or the freshness need usually decides the answer.
On the job
Here's how Juniper Coffee settles the three requests.
The loyalty team gets an Eventhouse. The loyalty-app stream you found in Real-Time hub feeds it, and their dashboards query the last hour in seconds.
The data science intern gets a Lakehouse. Café photos go in Files, and the clean sales tables sit in Tables.
Finance gets a Warehouse. Their T-SQL scripts move over with few changes. They join the Lakehouse sales tables with three-part names, so nobody copies the sales data twice.
Your Power BI semantic models can read all three. That's where the analytics engineer's real work begins.
Some of Juniper's data already lives somewhere tidy: a partner's cloud bucket, the till database, even a finance Power BI model. Next lesson: shortcuts, mirroring and OneLake integration, or how to use data without copying it.
Leaderboard
Loading…
50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.
Sources for this lesson (5)
Each fact in the lesson is marked with the number of the page it comes from.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [39]Microsoft Learn: Eventhouse overview
- [40]Microsoft Learn: What is Mirroring in Fabric?
- [81]Microsoft Learn: Microsoft Fabric decision guide: choose a data store
- [82]Microsoft Learn: Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse