Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 33 lessons
  1. Start here: cloud and data 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. The three languages: SQL, PySpark and KQL side by side
    6. 6. Getting data in: the map of Fabric tools
  2. Store, ingest and transform batch data (exam area 2, 30-35%)

    1. 7. Choosing a data store and a transformation tool
    2. 8. Shortcuts and mirroring: use data without copying it
    3. 9. Ingest data with pipelines and Copy job
    4. 10. Transform with PySpark and SQL: join, denormalize, aggregate
    5. 11. Messy data: duplicates, missing values and late arrivals
    6. 12. Full and incremental loads, and loading a star schema
  3. Orchestrate and stream (exam areas 1 and 2)

    1. 13. Pipeline or notebook? Choosing how to orchestrate
    2. 14. Schedules and event-based triggers
    3. 15. Parameters and dynamic expressions
    4. 16. Streaming in Fabric: Eventstreams, Spark streaming and KQL
    5. 17. KQL and windowing functions for real-time data
  4. Set up and secure a Fabric solution (exam area 1, 30-35%)

    1. 18. Workspace settings: Spark, domains, OneLake and data workflows
    2. 19. Version control with Git and database projects
    3. 20. Deployment pipelines: development, test and production
    4. 21. Who can see what: workspace roles and item permissions
    5. 22. Fine-grained security: rows, columns, objects, files and masking
    6. 23. Governance: sensitivity labels and endorsement
  5. Monitor, fix and optimize (exam area 3, 30-35%)

    1. 24. Monitoring hub, alerts and Activator
    2. 25. Fixing errors in pipelines, dataflows and notebooks
    3. 26. Fixing errors in Eventhouse, Eventstream and T-SQL
    4. 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
    5. 28. Optimize Spark, pipelines and warehouses
    6. 29. Optimize eventstreams, eventhouses and queries
  6. Exam practice and final review

    1. 30. Exam day: question types, timing and how to think
    2. 31. Mock exam 1
    3. 32. Mock exam 2
    4. 33. Final review: every decision on one page
See the leaderboard

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.

[4]

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 loadIncremental load.
Fine whenThe 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 withThe whole table.Only the changes.
Watch out forTruncating 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.

[101]

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.

  1. Read the old watermark from a control table, a small table that stores one value per source table. A pipeline parameter works too.
  2. Select source rows where ModifiedDate is later than the watermark.
  3. Load those rows into the destination.
  4. Only after the load succeeds, save the new highest ModifiedDate as 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.

[70][99][101]

Rendering diagram…
The watermark moves forward only after a load succeeds, so a failed run is simply retried.

[99]

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.

[92][93][103][104]

sql
-- 1. Read the old watermark from the control table
DECLARE @Last datetime2 =
(SELECT LastValue FROM etl.Watermark WHERE TableName = 'Stations');
-- 2. Upsert only rows changed since then
MERGE dbo.DimStation AS target
USING (
SELECT StationID, StationName, City, ModifiedDate
FROM staging.Stations
WHERE ModifiedDate >= @Last
) AS source
ON target.StationID = source.StationID
WHEN MATCHED AND source.ModifiedDate > target.ModifiedDate THEN
UPDATE SET StationName = source.StationName, City = source.City,
ModifiedDate = source.ModifiedDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (StationID, StationName, City, ModifiedDate)
VALUES (source.StationID, source.StationName, source.City, source.ModifiedDate);
-- 3. Only after success: move the watermark forward
UPDATE etl.Watermark
SET LastValue = (SELECT MAX(ModifiedDate) FROM staging.Stations)
WHERE TableName = 'Stations';
Warehouse: watermark plus MERGE for the station dimension. Using >= rereads the boundary rows; MERGE makes that harmless.

[99][100]

python
from delta.tables import DeltaTable
target = 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()
)
Lakehouse Notebook: the same upsert on a Delta table with PySpark.

[302]

[92][99]

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.

[101]

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.

[35][36]

Rendering diagram…
Northwind's trip star. FactTrip stores surrogate keys and measures; DimDate is shared with the payments star, so it's conformed.

[35][36]

Match

Match each Northwind Rides column or table to its role

Pick an item on the left, then its match on the right.

[36]

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.

[36][101]

Worked example: Ana switches plans (SCD Type 2 on PlanName).

Before, DimCustomer has one row for customer C-554.

CustomerSKCustomerIDPlanNameValidFromValidToIsCurrent.
101C-554Pay as you go2025-03-019999-12-311.

On 2026-06-10, Ana moves to a Monthly pass. The load does two things.

  1. Expire row 101: set ValidTo to 2026-06-10 and IsCurrent to 0.
  2. Insert a new row with a new surrogate key, 245.
CustomerSKCustomerIDPlanNameValidFromValidToIsCurrent.
101C-554Pay as you go2025-03-012026-06-100.
245C-554Monthly pass2026-06-109999-12-311.

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.

[36]

sql
DECLARE @Today date = CAST(GETDATE() AS date);
-- 1. Expire the current version when the plan changed
UPDATE dbo.DimCustomer
SET ValidTo = @Today, IsCurrent = 0
WHERE IsCurrent = 1
AND EXISTS (SELECT 1 FROM staging.Customers AS s
WHERE s.CustomerID = dbo.DimCustomer.CustomerID
AND s.PlanName <> dbo.DimCustomer.PlanName);
-- 2. Insert a current version for new and changed customers
INSERT INTO dbo.DimCustomer
(CustomerID, PlanName, ValidFrom, ValidTo, IsCurrent)
SELECT s.CustomerID, s.PlanName, @Today, '9999-12-31', 1
FROM staging.Customers AS s
WHERE NOT EXISTS (SELECT 1 FROM dbo.DimCustomer AS d
WHERE d.CustomerID = s.CustomerID AND d.IsCurrent = 1);
SCD Type 2 in a Warehouse: expire changed rows, then insert new current versions. CustomerSK is an IDENTITY column, which numbers new rows automatically.

[101]

[36][101]

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.

[36]

[36]

SCD Type 1: you never switched plans. SCD Type 2: you did, and here's the receipt. Picture: Whiteboard doodle in black and blue marker: on the left, a stick-figure cyclist being erased by a giant pencil eraser, labeled "TYPE 1".

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.

[105]

Rendering diagram…
Northwind's medallion flow. Incremental loads and MERGE move data between layers.

[105]

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.

[92]

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.

[105]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[35][36][70][92][93][101][105]

Worth keeping

  1. Small table or no change column: full load. Otherwise: incremental with a watermark.
  2. Move the watermark only after the load succeeds; MERGE makes reruns safe.
  3. Never delete unmatched rows in an incremental MERGE.
  4. Star schema: facts hold measures and surrogate keys; dimensions hold attributes.
  5. 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.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [35]Microsoft Learn: Dimensional modeling in Fabric Data Warehouse
  3. [36]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
  4. [70]Microsoft Learn: Microsoft Fabric decision guide: Choose a data integration strategy
  5. [92]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
  6. [93]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
  7. [99]Microsoft Learn: Ingest Delta Lake tables with T-SQL
  8. [100]Microsoft Learn: Ingest from warehouse tables and mirrored databases with T-SQL
  9. [101]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Load tables
  10. [103]Microsoft Learn: Low shuffle merge optimization on Delta tables
  11. [104]Microsoft Learn: Upsert into a Delta Lake table using merge
  12. [105]Microsoft Learn: Understand medallion architecture for Fabric with OneLake
  13. [302]Microsoft Learn: Troubleshoot lakehouse issues for Microsoft Fabric Data Engineering