Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

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.

[6]

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.

[39][81][82]

LakehouseWarehouseEventhouse
Data typeAny: tables, JSON, files, images.Structured tables.Events, logs, telemetry, JSON, text.
Writes withSpark, pipelines, dataflows; shortcuts link data in.T-SQL (COPY INTO, INSERT, CTAS), pipelines, dataflows.Eventstream, Get data and other ingestion.
Reads withSpark, or read-only T-SQL on the SQL analytics endpoint.T-SQL.KQL, and T-SQL.
Team skillsPySpark, notebooks.SQL, star schema design.KQL, no code.
FreshnessAs of the last load.As of the last load.Seconds, as events arrive.
Built forBig 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.

[81][82]

Rendering diagram…
Streaming first, then operational apps, then T-SQL writes. Everything else starts in a Lakehouse.

[81][82]

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.

[39][81]

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.

[82]

[81]

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.

[81][82]

The SQL analytics endpoint: you can read every table and save views. You can't load a single row. Picture: Vintage instruction-manual drawing, beige paper with fine black line art.

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.

ItemWhat it isThe scenario says
SQL database in FabricThe Azure SQL engine. For operational (OLTP) apps with many small reads and writes."operational", "OLTP", "app database", "foreign keys".
Cosmos DB in FabricA NoSQL database for apps, reached through REST APIs."NoSQL", "documents", "app developers".
Mirrored databaseA 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.

[40][81]

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.

[81]

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.

[40][81][82]

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?

[81]

The constraint words that decide the store:

The scenario saysPick
"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

[81][82]

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.

[82]

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.

[81][82]

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.

[81][82]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[39][40][81][82]

Worth keeping

  1. Streaming events: Eventhouse. T-SQL writers or multi-table transactions: Warehouse. Everything else: Lakehouse.
  2. The SQL analytics endpoint reads with T-SQL and holds views, but never loads tables.
  3. SQL database is for operational apps, Cosmos DB for NoSQL, mirrored databases for read-only replicas.
  4. All stores share OneLake, so three-part names join them without copies.
  5. 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.

[81][82]

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.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [39]Microsoft Learn: Eventhouse overview
  3. [40]Microsoft Learn: What is Mirroring in Fabric?
  4. [81]Microsoft Learn: Microsoft Fabric decision guide: choose a data store
  5. [82]Microsoft Learn: Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse