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

18 min

Messy data: duplicates, missing values and late arrivals

Find and fix duplicate, missing and late-arriving data before it reaches reports.

By the end of this lesson you can

  • Remove duplicates in PySpark and T-SQL, and keep the latest row per key
  • Choose to drop, fill or flag a missing value
  • Handle late-arriving facts and dimensions with reprocessing windows and inferred members
  • Recognize a streaming watermark and what it controls

From earlier lessons · 1 of 2

You join the trips table to the stations table so every trip row carries the station name and city. What is this called?

Pick one answer.

From earlier lessons · 2 of 2

In a Fabric Warehouse, which statement creates a new table from the result of a SELECT in one step?

Pick one answer.

Monday at Northwind Rides. The ops report says scooters made 12,400 trips last week, but billing counted 11,900. You open the raw trips table. The same trip ID shows up three times, some trips have no payment type, and some belong to customers who aren't in the customer table yet. Each problem needs a different fix.

[4]

Duplicates: keep one row per key

Exam depth: know it cold.

A duplicate is one real event stored more than once. Scooters resend a trip event when the signal drops, so one trip can land three times.

Pick the fix by what the copies look like.

The copies arePySparkT-SQL.
Identical in every columndistinct() or dropDuplicates()SELECT DISTINCT.
Same key, any copy will dodropDuplicates(["trip_id"])ROW_NUMBER(), keep 1.
Same key, keep the newestrow_number() over a window, newest firstROW_NUMBER() ordered DESC, keep 1.

dropDuplicates takes an optional list of columns to compare. With no list, it compares all columns. It has no sort option, so you can't choose which copy survives.

ROW_NUMBER is a window function: it numbers rows 1, 2, 3 inside each group, called a partition, in the order you give.

Decision rule: if a specific copy must win, such as the newest, use ROW_NUMBER.

[88][94][96]

python
from pyspark.sql import Window
from pyspark.sql.functions import col, row_number
trips = spark.read.table("bronze_trips")
# Any copy will do: one row per trip_id
any_copy = trips.dropDuplicates(["trip_id"])
# Keep the newest version of each trip
w = Window.partitionBy("trip_id").orderBy(col("updated_at").desc())
latest = (
trips.withColumn("rn", row_number().over(w))
.filter(col("rn") == 1)
.drop("rn")
)
latest.write.mode("overwrite").saveAsTable("silver_trips")
PySpark in a Fabric Notebook: two ways to remove duplicate trips.

[96]

sql
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY TripID
ORDER BY UpdatedAt DESC, EventID DESC
) AS rn
FROM staging.Trips
)
SELECT TripID, ScooterID, CustomerID, StartTime, EndTime, UpdatedAt
FROM ranked
WHERE rn = 1;
T-SQL in a Warehouse: keep the newest row per TripID. Fabric Warehouse also accepts QUALIFY ROW_NUMBER() OVER (...) = 1, which skips the CTE.

[94]

[94]

You are a data engineer at Northwind Rides. Scooters resend a trip after a fare correction, so bronze_trips holds several rows per trip_id, each with a different updated_at. The clean table must keep only the most recent version of each trip. What should you do in the notebook?

Pick one answer.

[96]

Missing values: drop, fill or flag

Exam depth: know it cold.

A missing value is a null: the column holds no value at all. You have three choices. The right one depends on the column.

ChoicePySparkT-SQLUse it when.
Drop the rowdropna(subset=[...])WHERE col IS NOT NULLThe row is useless without it, such as a trip with no ID.
Fill a defaultfillna(...)COALESCE(col, 'Unknown')A text label is fine, such as payment type.
Point to a special memberkey -1COALESCE(key, -1)A dimension key lookup finds no match (next section).

fillna takes one value, or a dictionary of column names and values. It's another name for na.fill. COALESCE returns the first value in its list that isn't null.

Decision rule: drop rows you can't use, fill text with a label, and point missing keys at a special member.

[36][97][98][99]

python
trips_clean = (
trips
.dropna(subset=["trip_id", "start_time"]) # unusable without these
.fillna({"payment_type": "Unknown", "promo_code": "None"})
)
Drop trips that have no ID or start time, then fill text columns with a label.

[98]

The Unknown member: never drop a fact

Exam depth: know it cold.

A fact table stores events, such as trips. A dimension table describes the things in them, such as customers or stations. Each fact row points to a dimension row by a key. Lesson 12 builds these tables in full.

When a trip's station key is missing or matches no station, you still load the trip. Dropping it would make trip counts wrong. Instead, point it at a special dimension member: a row you add to every dimension for these cases.

KeyMemberMeaning.
0MissingThe source had no value.
-1UnknownThe lookup failed during the fact load.
-2N/ADoesn't apply.

Later, a cleanup job finds facts on -1 and fixes their keys once the real value is known. Fabric Warehouse doesn't enforce foreign keys, so your load must run this check itself.

[36][101]

One trip, three rows, one very confused trip count. Picture: Three-panel comic strip: Panel 1, a cartoon electric scooter on a city street throws a paper airplane labeled "TRIP 42" toward a cloud.

Late-arriving facts and dimensions

Exam depth: know it cold.

Late-arriving data shows up after the load it belongs to has already run. It comes in two kinds.

Late-arriving facts. A Saturday trip arrives on Tuesday, after Saturday was loaded. The fix is a reprocessing window: each run reloads the last few days, not just today. It uses MERGE, a statement that updates rows already there and inserts new ones, so nothing loads twice. Make the window wide enough for how late data really gets.

Late-arriving dimensions. A trip arrives for customer C-981, but the customer's details haven't synced yet. You know only the ID. So the fact load inserts an inferred member: a customer row with the real ID, Unknown in every other column, and an IsInferredMember flag set to true.

The trip gets that row's key right away. When the details arrive, the dimension load fills them in and sets the flag to false.

[100][101]

Rendering diagram…
The trip loads on time. The customer row starts as a placeholder and gets its details later.

[101]

sql
DECLARE @ReloadFrom date = DATEADD(day, -3, CAST(GETDATE() AS date));
MERGE gold.FactTrip AS target
USING (
SELECT TripID, CustomerKey, StationKey, TripDate, DistanceKm
FROM silver.Trips
WHERE TripDate >= @ReloadFrom
) AS source
ON target.TripID = source.TripID
WHEN MATCHED THEN
UPDATE SET CustomerKey = source.CustomerKey, StationKey = source.StationKey,
TripDate = source.TripDate, DistanceKm = source.DistanceKm
WHEN NOT MATCHED BY TARGET THEN
INSERT (TripID, CustomerKey, StationKey, TripDate, DistanceKm)
VALUES (source.TripID, source.CustomerKey, source.StationKey,
source.TripDate, source.DistanceKm);
A three-day reprocessing window in a Warehouse. Late trips are inserted; corrected trips are updated.

[100]

[101]

You are a data engineer at Northwind Rides. Trips load into a fact table every hour. Sometimes a trip arrives for a new customer whose details sync from the app a day later. The trip must show in reports right away, and the customer's details must fill in later. What should you do?

Pick one answer.

[101]

Match

Match each Northwind Rides problem to its fix

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

[36][101]

Late data in streams: watermarks

Exam depth: recognize it.

A stream never ends, so Spark can't wait forever for late events. Spark Structured Streaming is Spark's engine for data that keeps arriving. In it, a watermark says how late an event can be and still count.

trips.withWatermark("event_time", "10 minutes")

This accepts events up to 10 minutes late, judged by the event_time column. Spark can then drop the running results it keeps in memory for older time periods, called state. Events later than that might be ignored.

Too short, and you lose valid late events. Too long, and Spark keeps more state. Lesson 16 covers streaming in full.

Don't confuse this with the watermark in lesson 12's incremental loads. That one is the last value you loaded.

[102]

Exam-style practice

You are a data engineer at Northwind Rides. A Warehouse query should keep the newest version of each trip, but reports show old fares. It uses ROW_NUMBER() OVER (PARTITION BY TripID ORDER BY UpdatedAt ASC) AS rn and keeps rows WHERE rn = 1. You need to fix the query. What should you change?

Pick one answer.

[94]

You are a data engineer at Northwind Rides. Some trips carry a station code that isn't in DimStation yet, so the key lookup returns null. The fact table must keep every trip, and station reports must show these trips in one clear bucket. What should you do when you load the fact table?

Pick one answer.

[36][101]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[36][94][96][98][100][101][102]

Worth keeping

  1. Identical copies: dropDuplicates or DISTINCT. A specific copy must win: ROW_NUMBER, newest first.
  2. Drop rows you can't use, fill text with a label, and don't fill measures with 0.
  3. Never drop a fact for a bad key: point it at the Unknown member and fix it later.
  4. Late facts: reprocessing window plus MERGE. Late dimensions: inferred member with a flag.
  5. A streaming watermark sets how late an event can be and still count.

On the job

Here's how the trip-count gap from the hook gets closed.

You add a cleaning step to the nightly Notebook. It keeps the newest row per trip_id with row_number, drops rows with no ID, and fills a missing payment type with Unknown.

The fact load in the Warehouse now looks up every station and customer key. A failed station lookup gets -1. A missing customer gets an inferred member, so new sign-ups show up in reports the same hour.

Because scooters sometimes upload trips days late, the fact load reprocesses the last three days with MERGE. A weekly job lists facts still on -1 and inferred members still flagged, and sends the list to the ops team.

The next Monday, ops and billing count the same 11,900 trips.

[100][101]

Reloading three days catches late trips. But Northwind has three years of trips, and reloading all of them every night won't last. Next lesson: load only what changed, with a watermark and MERGE, into a proper star schema.

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 (11)

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. [36]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
  3. [88]Microsoft Learn: PySpark basics
  4. [94]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
  5. [96]Microsoft Learn: dropDuplicates (PySpark reference)
  6. [97]Microsoft Learn: DataFrame class (PySpark reference)
  7. [98]Microsoft Learn: fillna (PySpark reference)
  8. [99]Microsoft Learn: Ingest Delta Lake tables with T-SQL
  9. [100]Microsoft Learn: Ingest from warehouse tables and mirrored databases with T-SQL
  10. [101]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Load tables
  11. [102]Microsoft Learn: Stateful processing with Structured Streaming