DP-700 - Ingest and Transform Data (33% of the exam) - Section 2.8

Handle duplicate, missing, and late-arriving data during ingestion and transformation.

Apply deduplication strategies, null handling, and data quality checks during ingestion and transformation pipelines. Recognise late-arriving dimensions and facts, and use watermarks for late events to decide whether to include, exclude, or reprocess delayed records.

deduplication strategieshandling null and missing valueslate-arriving dimensions and factswatermarks for late eventsdata quality checks

Practice question for this objective

Free sampleIngest and Transform Datahard

A silver transformation in Microsoft Fabric reads a customer feed where the Region column is sometimes null because the upstream system omits it for older records. Downstream reports group revenue by Region and must show these records under a single bucket labelled Unknown rather than dropping them or leaving blanks that break the grouping. Which handling of the missing Region values best meets the requirement?

AFilter out every row whose Region is null in the silver layer so the downstream grouping only ever sees populated Region values.
BLeave the Region nulls in place and rely on the reporting layer to coalesce nulls into their own group at query time on each run.
CReplace null Region values with the literal string Unknown during the silver transformation so every record carries a non-null grouping value. Correct
DImpute the missing Region by copying the most frequent Region in the feed into each null so the records join a real, populated region group.

Handle missing categorical values by substituting an explicit sentinel such as Unknown so records survive and group into a single defined bucket. The report must retain the records and place them in one bucket, so the missing values need an explicit, consistent replacement at the silver layer; substituting Unknown keeps every row and gives grouping a stable key, whereas filtering drops valid revenue and imputing a real region misattributes it to a location the record never had.

Why A is wrong: Dropping null-region rows produces clean grouping keys but discards valid revenue records, so totals understate reality; the requirement explicitly says these records must still appear, just under an Unknown bucket.

Why B is wrong: Deferring the fix to the report works only if every consumer remembers to coalesce, and many grouping engines treat nulls inconsistently; standardising the value in silver is more reliable than trusting each downstream query.

Why C is correct: Substituting a sentinel value such as Unknown for nulls keeps every record in the dataset and gives the grouping a single explicit bucket, which is exactly the behaviour the report requires for the missing-region rows.

Why D is wrong: Filling nulls with the most common region invents a location the record never had and inflates that region's revenue with misattributed rows; the requirement wants a distinct Unknown bucket, not a guessed real region.

See more DP-700 practice questions, answers explained.

Exam traps in Ingest and Transform Data

Answers that look right on this material and are not. Each one is a distractor from a different question in the DP-700 bank for this domain.

Schedule a weekly report that profiles the silver table and emails the team a summary of null rates and row counts for the preceding seven days.
Why it is wrong: A weekly profile is useful for trend visibility, but it inspects data already written to silver and only days later, so a bad batch still lands and propagates; the rules must gate the write, not report after it.
Quarantine every fact row with an unmatched or null CustomerId in a holding table and load it into the fact only once a matching dimension member is confirmed to exist.
Why it is wrong: Quarantining keeps the fact free of problem keys, but it withholds the revenue until later, and the requirement is explicit that analysts must count and sum those rows the same night, so holding the rows back directly violates the stated need.
Switching the output mode from update to complete, so every micro-batch re-emits the full result set and any late event is reflected in the recomputed totals.
Why it is wrong: Complete mode forces the engine to retain and re-emit all windows indefinitely, which actually prevents state cleanup and grows memory, so it worsens rather than solves the unbounded-state problem.

Examworthy is not affiliated with or endorsed by Microsoft. Original, blueprint-aligned practice material only.