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
22 min
Full and incremental loads, and loading a star schema
Design full and incremental loads and prepare data for fact and dimension tables.
By the end of this lesson you can
- Choose between a full load and an incremental load
- Build an incremental load with a watermark and MERGE
- Name the parts of a star schema: facts, dimensions, surrogate keys
- Load a dimension with SCD Type 1 or Type 2, inside a medallion design
From earlier lessons · 1 of 2
A table holds several versions of each trip. You must keep the newest one per TripID. Which ROW_NUMBER setup does that, keeping rn = 1?
Pick one answer.
From earlier lessons · 2 of 2
A trip arrives for a customer who isn't in DimCustomer yet. You insert a row with the real customer ID, Unknown attributes and a flag set to true. What is that row called?
Pick one answer.
Northwind Rides copies its whole Payments table, 400 million rows, from SQL Server every night. The copy now runs past 6 a.m., and the ops report opens empty. Finance also asks a hard question: when a customer switched plans last spring, did their old trips move to the new plan in the reports? Nobody knows.
Full load or incremental load
Exam depth: know it cold.
A full load reads every row from the source on each run and replaces the destination table. An incremental load reads only the rows that are new or changed since the last run.
| Full load | Incremental load. | |
|---|---|---|
| Fine when | The table is small, or the source can't say what changed. | The table is big and has a reliable change column or CDC. |
| Run time grows with | The whole table. | Only the changes. |
| Watch out for | Truncating a dimension breaks the keys facts point to. | You must track what you loaded last. |
For a large fact table, truncate and reload is a last resort. It costs time and compute, and it puts load on the source system.
Decision rule: small table or no way to spot changes, full load. Big table with a change column, incremental.
The watermark pattern
Exam depth: know it cold.
A watermark is the highest change value you loaded last time, such as the latest ModifiedDate or order number. Each run works in four steps.
- Read the old watermark from a control table, a small table that stores one value per source table. A pipeline parameter works too.
- Select source rows where
ModifiedDateis later than the watermark. - Load those rows into the destination.
- Only after the load succeeds, save the new highest
ModifiedDateas the watermark.
No reliable change column? Some databases offer change data capture (CDC): the database itself records every insert, update and delete. SQL Server has it.
In Fabric, a Copy job can load incrementally with either a watermark column or native CDC, with no code.
Upsert with MERGE
Exam depth: know it cold.
Incremental rows can be new or changed. An upsert handles both: update the row if it exists, insert it if it doesn't. MERGE does an upsert in one statement.
MERGE matches source rows to target rows on a key, then runs a clause for each case.
WHEN MATCHED: the key exists, so update it.WHEN NOT MATCHED BY TARGET: the key is new, so insert it.WHEN NOT MATCHED BY SOURCE: the target row isn't in the source. You can delete it, but only when the source is a complete copy.
Fabric Warehouse supports T-SQL MERGE, and it's generally available. In a Lakehouse, Spark runs MERGE on Delta tables.
In both engines, the source must hold at most one row per key. Remove duplicates first, as in lesson 11.
-- 1. Read the old watermark from the control tableDECLARE @Last datetime2 =(SELECT LastValue FROM etl.Watermark WHERE TableName = 'Stations');-- 2. Upsert only rows changed since thenMERGE dbo.DimStation AS targetUSING (SELECT StationID, StationName, City, ModifiedDateFROM staging.StationsWHERE ModifiedDate >= @Last) AS sourceON target.StationID = source.StationIDWHEN MATCHED AND source.ModifiedDate > target.ModifiedDate THENUPDATE SET StationName = source.StationName, City = source.City,ModifiedDate = source.ModifiedDateWHEN NOT MATCHED BY TARGET THENINSERT (StationID, StationName, City, ModifiedDate)VALUES (source.StationID, source.StationName, source.City, source.ModifiedDate);-- 3. Only after success: move the watermark forwardUPDATE etl.WatermarkSET LastValue = (SELECT MAX(ModifiedDate) FROM staging.Stations)WHERE TableName = 'Stations';
from delta.tables import DeltaTabletarget = DeltaTable.forName(spark, "silver_stations")changes = spark.read.table("bronze_stations_changed")(target.alias("t").merge(changes.alias("s"), "t.station_id = s.station_id").whenMatchedUpdateAll().whenNotMatchedInsertAll().execute())
You are a data engineer at Northwind Rides. The Payments table in SQL Server has 400 million rows and a reliable ModifiedDate column. A nightly full copy now takes six hours. You need the nightly load to read only new and changed payments. What should you do?
Pick one answer.
Star schema: facts and dimensions
Exam depth: know it cold.
A dimensional model shapes data for reporting. Its common form is the star schema: one fact table in the middle, dimension tables around it like the points of a star.
- Fact table: one row per event, such as a trip. It holds measures, numbers you add up such as distance and fare, plus keys to dimensions.
- Dimension table: one row per thing, such as a customer or station. Its attributes, such as city or plan, are what you filter and group by.
- Natural key: the ID from the source system, such as customer
C-554. Also called a business key. - Surrogate key: a new integer ID the warehouse creates for each dimension row. Facts store it. It lets one customer have several history rows, which SCD Type 2 needs.
- Conformed dimension: a dimension shared by several fact tables, such as one date dimension for trips and payments.
Load dimensions first, then facts, so every fact finds its keys.
Match
Match each Northwind Rides column or table to its role
Pick an item on the left, then its match on the right.
Slowly changing dimensions: Type 1 and Type 2
Exam depth: know it cold.
Dimension values change now and then: a customer moves or switches plans. A slowly changing dimension (SCD) is a rule for what the table does when that happens.
- SCD Type 1: overwrite. Update the row in place. No history is kept. Use it for corrections and for columns nobody reports history on, such as email.
- SCD Type 2: add a version. Expire the current row and insert a new one with its own surrogate key. History stays. Use it when old facts must keep the old value.
A Type 2 table adds three columns: ValidFrom, ValidTo and IsCurrent. The current row's ValidTo is a far-future date. When you load new facts, look up the surrogate key of the current version.
One dimension can use both types, column by column.
Decision rule: if a report must show the past as it was, Type 2. Otherwise, Type 1.
Worked example: Ana switches plans (SCD Type 2 on PlanName).
Before, DimCustomer has one row for customer C-554.
| CustomerSK | CustomerID | PlanName | ValidFrom | ValidTo | IsCurrent. |
|---|---|---|---|---|---|
| 101 | C-554 | Pay as you go | 2025-03-01 | 9999-12-31 | 1. |
On 2026-06-10, Ana moves to a Monthly pass. The load does two things.
- Expire row 101: set
ValidToto 2026-06-10 andIsCurrentto 0. - Insert a new row with a new surrogate key, 245.
| CustomerSK | CustomerID | PlanName | ValidFrom | ValidTo | IsCurrent. |
|---|---|---|---|---|---|
| 101 | C-554 | Pay as you go | 2025-03-01 | 2026-06-10 | 0. |
| 245 | C-554 | Monthly pass | 2026-06-10 | 9999-12-31 | 1. |
Trips before June 10 still point at 101, so they stay under Pay as you go. New trips get 245. That answers finance's question from the hook.
With Type 1 instead, row 101 would just say Monthly pass. All of Ana's old trips would move to the new plan.
DECLARE @Today date = CAST(GETDATE() AS date);-- 1. Expire the current version when the plan changedUPDATE dbo.DimCustomerSET ValidTo = @Today, IsCurrent = 0WHERE IsCurrent = 1AND EXISTS (SELECT 1 FROM staging.Customers AS sWHERE s.CustomerID = dbo.DimCustomer.CustomerIDAND s.PlanName <> dbo.DimCustomer.PlanName);-- 2. Insert a current version for new and changed customersINSERT INTO dbo.DimCustomer(CustomerID, PlanName, ValidFrom, ValidTo, IsCurrent)SELECT s.CustomerID, s.PlanName, @Today, '9999-12-31', 1FROM staging.Customers AS sWHERE NOT EXISTS (SELECT 1 FROM dbo.DimCustomer AS dWHERE d.CustomerID = s.CustomerID AND d.IsCurrent = 1);
You are a data engineer at Northwind Rides. Finance reports revenue by customer plan. When a customer moves from Pay as you go to a Monthly pass, last year's revenue must stay under Pay as you go. Email changes need no history. How should you load DimCustomer?
Pick one answer.
Medallion architecture: bronze, silver, gold
Exam depth: know it cold.
Medallion architecture is the design Fabric recommends for organizing a lakehouse. Data moves through three layers, getting cleaner at each step.
- Bronze (raw): data exactly as it arrived. Nothing changes here. If the source already sits in OneLake or cloud storage such as ADLS or Amazon S3, use a shortcut instead of copying.
- Silver (enriched): errors fixed, formats standardized, duplicates removed. This is where lesson 11's fixes run.
- Gold (curated): shaped for reports, such as this lesson's star schema.
Keep each layer in its own lakehouse or warehouse. Microsoft recommends a separate workspace per layer for control. Two common setups: all three layers as lakehouses, or bronze and silver as lakehouses with gold as a Warehouse.
Silver and gold should be Delta tables.
Exam-style practice
You are a data engineer at Northwind Rides. A nightly MERGE loads stations changed since the watermark into DimStation. After the first run, most stations vanish from the dimension. The statement ends with WHEN NOT MATCHED BY SOURCE THEN DELETE. You need to fix the load. What should you do?
Pick one answer.
You are a data engineer at Northwind Rides. Raw trip JSON lands from the scooters. Auditors want the raw files kept exactly as received. Analysts need clean, deduplicated trips, and Power BI needs a star schema. Where should the deduplicated trips table live?
Pick one answer.
Worth keeping
- Small table or no change column: full load. Otherwise: incremental with a watermark.
- Move the watermark only after the load succeeds; MERGE makes reruns safe.
- Never delete unmatched rows in an incremental MERGE.
- Star schema: facts hold measures and surrogate keys; dimensions hold attributes.
- SCD Type 2 when the past must stay as it was; Type 1 when it needn't.
You now have a watermark load, a MERGE and an SCD step. Something has to run them in the right order every night, and retry when one fails. Should that be a pipeline, or a notebook that calls other notebooks? That's next.
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 (13)
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
- [35]Microsoft Learn: Dimensional modeling in Fabric Data Warehouse
- [36]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
- [70]Microsoft Learn: Microsoft Fabric decision guide: Choose a data integration strategy
- [92]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
- [93]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
- [99]Microsoft Learn: Ingest Delta Lake tables with T-SQL
- [100]Microsoft Learn: Ingest from warehouse tables and mirrored databases with T-SQL
- [101]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Load tables
- [103]Microsoft Learn: Low shuffle merge optimization on Delta tables
- [104]Microsoft Learn: Upsert into a Delta Lake table using merge
- [105]Microsoft Learn: Understand medallion architecture for Fabric with OneLake
- [302]Microsoft Learn: Troubleshoot lakehouse issues for Microsoft Fabric Data Engineering