All 33 lessons
Start here: cloud and data 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. The three languages: SQL, PySpark and KQL side by side
- 6. Getting data in: the map of Fabric tools
Store, ingest and transform batch data (exam area 2, 30-35%)
- 7. Choosing a data store and a transformation tool
- 8. Shortcuts and mirroring: use data without copying it
- 9. Ingest data with pipelines and Copy job
- 10. Transform with PySpark and SQL: join, denormalize, aggregate
- 11. Messy data: duplicates, missing values and late arrivals
- 12. Full and incremental loads, and loading a star schema
Orchestrate and stream (exam areas 1 and 2)
Set up and secure a Fabric solution (exam area 1, 30-35%)
- 18. Workspace settings: Spark, domains, OneLake and data workflows
- 19. Version control with Git and database projects
- 20. Deployment pipelines: development, test and production
- 21. Who can see what: workspace roles and item permissions
- 22. Fine-grained security: rows, columns, objects, files and masking
- 23. Governance: sensitivity labels and endorsement
Monitor, fix and optimize (exam area 3, 30-35%)
- 24. Monitoring hub, alerts and Activator
- 25. Fixing errors in pipelines, dataflows and notebooks
- 26. Fixing errors in Eventhouse, Eventstream and T-SQL
- 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
- 28. Optimize Spark, pipelines and warehouses
- 29. Optimize eventstreams, eventhouses and queries
Exam practice and final review
18 min
Choosing a data store and a transformation tool
Pick Lakehouse, Warehouse or Eventhouse, and Dataflow Gen2, notebook or T-SQL, from a scenario.
By the end of this lesson you can
- Choose a Lakehouse, Warehouse or Eventhouse from a scenario
- Explain why the SQL analytics endpoint can't load data
- Choose Dataflow Gen2, a notebook or T-SQL to transform data
- Find the team-skills detail that decides the answer
From earlier lessons · 1 of 2
A partner's files must stay in its Amazon S3 bucket, but analysts need to query them from Fabric. What should you use?
Pick one answer.
From earlier lessons · 2 of 2
What does an Eventstream do with the events it receives?
Pick one answer.
Northwind Rides' data now has a way in. Then three teams arrive at once. Finance wants payments modeled in T-SQL, which they've written for ten years. The data engineers want PySpark for messy trip logs. Ops wants scooter events answered in seconds. You have to give each team a store and a tool, and one size won't fit 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 data such as images. You write to it with Spark.
- Warehouse: structured tables only, built and changed with full T-SQL. It supports multi-table transactions: several tables change together, or none do.
- Eventhouse: time-stamped events that stream in, queried in seconds with KQL. It holds one or more KQL databases.
| Lakehouse | Warehouse | Eventhouse | |
|---|---|---|---|
| Data shape | Any: tables, JSON, files, images. | Structured tables only. | Events: tables, JSON, free text. |
| Loads 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 T-SQL through the read-only SQL analytics endpoint. | T-SQL. | KQL, and T-SQL. |
| Built for | Big data, data engineering, machine learning. | Enterprise warehousing and SQL-based reporting. | Streaming and time-series analytics in seconds. |
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, means a Warehouse. Spark, mixed data or "not sure" means a Lakehouse.
You are a data engineer at Northwind Rides. Scooters send a GPS position every second, billions of rows a month. The ops team needs answers about the last hour in seconds, often searching JSON fields. You need to choose where to store the events. What should you use?
Pick one answer.
The SQL analytics endpoint is read-only
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 query, but it has no DML, the statements that change rows: no INSERT, UPDATE or DELETE. Its DDL, the statements that create objects, is limited to things such as views and table-valued functions (functions that return a table).
So a SQL team can read a Lakehouse in T-SQL, but it can't load one. Lakehouse tables are loaded by Spark, pipelines or dataflows, or linked in with shortcuts. If a SQL team must write tables, give it a Warehouse.
You are a data engineer at Northwind Rides. Your engineers load terabytes of trip data with PySpark. The analysts use T-SQL only to read the results for reports; they never change data. You need one store that suits both groups. What should you use?
Pick one answer.
Dataflow Gen2, notebook or T-SQL
Exam depth: know it cold.
Once data is stored, something has to clean and reshape it. You have three main choices.
- Dataflow Gen2 uses Power Query, the visual data-prep editor from Excel and Power BI. You click through steps, and Power Query records them in a formula language called M. It offers 300+ transformations and writes to many destinations, including Lakehouse, Warehouse and KQL database.
- A Notebook runs PySpark or Spark SQL code. Spark spreads the work across many machines, and you can add open-source libraries.
- T-SQL in a Warehouse transforms with SQL:
CREATE TABLE AS SELECT,INSERT ... SELECT, views and stored procedures (saved, reusable SQL scripts).
| Dataflow Gen2 | Notebook | T-SQL | |
|---|---|---|---|
| How you build | Power Query, visual steps. | Code cells. | SQL scripts in the query editor. |
| Code needed | None or very little. | Yes: PySpark, Spark SQL, Scala or R. | Yes: SQL. |
| Team skills | Power Query, Excel, M. | Spark and Python. | T-SQL. |
| Transformations | 300+ built-in steps. | Anything Spark or a library can do. | SQL logic over whole tables. |
| Writes to | Lakehouse, Warehouse, KQL database and more. | Mainly Lakehouse tables and files. | Warehouse tables. |
Decision rule: a no-code team gets Dataflow Gen2. Spark skills, very large data or complex logic get a Notebook. A SQL team working in a Warehouse gets T-SQL.
You are a data engineer at Northwind Rides. The marketing analysts build Excel reports with Power Query. Each week they must remove duplicate promo codes, fix date formats and load the list into a Warehouse table. They can't write code. What should they use?
Pick one answer.
Team skills decide the tie
Exam depth: know it cold.
Often two answers would both work. The sentence about the people breaks the tie, so read it first.
| The scenario says | Pick |
|---|---|
| "business analysts", "no code", "Power Query", "Excel skills". | Dataflow Gen2 |
| "PySpark", "Python", "data scientists", "unstructured files". | Notebook and Lakehouse |
| "existing T-SQL code", "SQL Server background", "stored procedures". | T-SQL and Warehouse |
| "KQL", "telemetry", "logs", "answers in seconds". | KQL and Eventhouse |
Predict, then reveal
A team has built SQL Server warehouses for years and wants to bring its T-SQL code to Fabric with few changes. Before you look: which store, and why?
Exam-style practice
You are a data engineer at Northwind Rides. The finance team has 200 T-SQL stored procedures from SQL Server. They build reporting tables and update several tables in one transaction. The team wants to keep its code. You need to choose a store and a transformation tool. What should you use?
Pick one answer.
You are a data engineer at Northwind Rides. Raw trip logs arrive as nested JSON, about 2 TB a day. You must flatten the nested fields and compute route distances with a Python geography library. The team knows PySpark. You need a transformation tool. What should you use?
Pick one answer.
You are a data engineer at Northwind Rides. Data scientists will train a model on photos of damaged scooters together with the trip and repair tables. You need one store for the photos and the tables. What should you use?
Pick one answer.
Worth keeping
- Streaming events go to an Eventhouse; T-SQL writers go to a Warehouse; everything else starts in a Lakehouse.
- The SQL analytics endpoint reads with T-SQL but never writes.
- Dataflow Gen2 for no-code teams, a Notebook for Spark and big messy data, T-SQL for SQL teams.
- The sentence about the team's skills usually decides the answer.
On the job
Here's how Northwind Rides settles the three requests.
Ops gets an Eventhouse. The Eventstream from last lesson feeds it, and the ops team writes KQL like the queries in lesson 5.
The data engineers get a Lakehouse. Notebooks parse the trip logs with PySpark and save clean Delta tables. Anyone who knows T-SQL can read those tables through the SQL analytics endpoint.
Finance gets a Warehouse. Their stored procedures move over with few changes and keep updating several reporting tables in one transaction. When a finance analyst needs a small no-code clean-up, a Dataflow Gen2 does it and writes to the same Warehouse.
All three stores sit in OneLake, so a finance report can still join payments to trips without a copy.
One problem is left. Some of Northwind's data sits in other clouds and databases, and nobody wants to copy it again. Next lesson: shortcuts and mirroring, and how to use data without moving 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 (7)
Each fact in the lesson is marked with the number of the page it comes from.
- [4]Microsoft Learn: Study Guide for Exam DP-700
- [41]Microsoft Learn: Eventhouse overview
- [64]Microsoft Learn: Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse
- [67]Microsoft Learn: Get data into Microsoft Fabric
- [68]Microsoft Learn: Microsoft Fabric decision guide: copy activity, Copy job, dataflow, Eventstream, or Spark
- [71]Microsoft Learn: What is Dataflow Gen2?
- [73]Microsoft Learn: Microsoft Fabric decision guide: choose a data store