3.5 Data Quality Remediation

Why Data Quality Is Scored Heavily

Within the 45-50% Prepare data domain, several items describe dirty source data and ask for the correct remediation. Wrong answers are usually destructive (drop everything) or naive (ignore nulls); right answers are deliberate and preserve the authoritative record while isolating bad data. The unifying principle the exam rewards is isolate and preserve, do not destroy.

The Core Remediation Patterns

1. Deduplication

Duplicates almost always mean the same business entity arrived more than once, not byte-identical rows. Remediate by:

• Identifying the business key (e.g., CustomerId, OrderNumber).
• Ranking duplicates by a recency or authority column (load timestamp, source priority).
• Keeping one row per key (the latest/authoritative) and discarding or quarantining the rest.

Deduplicating on the entire row misses near-duplicates that differ only in a load timestamp or a trailing space — a common distractor. In Spark this is a Window.partitionBy(businessKey).orderBy(loadTs.desc()) with row_number() = 1; in Power Query it is a group-and-keep-max.

2. Null and Missing Values

Nulls are a design decision, not an accident to ignore:

Never average or sum over unexplained nulls — it silently distorts the measures the semantic model will surface later. A null in a grain or key column means the row cannot be trusted and should be quarantined, not defaulted.

3. Type and Standardization

Before any join or aggregation:

• Type conversion — text-typed numbers/dates converted to proper types; failed conversions routed to an error path rather than coerced to null.
• Standardization — trim whitespace, normalize case, unify date formats, and canonicalize codes/units (USA/US/United States -> one value).

Keys only match after standardization; a join on inconsistent casing or padded strings silently drops rows and undercounts your facts.

Schema Drift, Outliers, and Late Data

• Schema drift — the source added, removed, or renamed columns. Detect and handle it (map explicitly or fail loudly) rather than letting silent column mismatches corrupt the load.
• Outliers — values outside plausible ranges (negative quantities, dates in the year 2999). Flag or quarantine; do not auto-delete unless the business rule is certain.
• Late-arriving data — events that arrive after their period closed. Use watermarks/incremental logic so late records are reprocessed correctly instead of lost or double-counted.

Validation as Enforceable Rules

Quality must be measured, not assumed. Implement validation as explicit rules:

• Constraints / not-null / uniqueness checks in the warehouse.
• Expectation or check queries in notebooks/pipelines (row counts, key uniqueness, referential integrity).
• Quarantine / error output so failing records land in a separate table for review while good records flow through — the exam strongly prefers this over failing the entire pipeline.

Quality Rules vs. Anti-Patterns

Remediation Decision List

1. Profile first — row counts, distinct keys, null rates, value ranges.
2. Standardize types and formats.
3. Deduplicate on the business key, keeping the authoritative row.
4. Resolve nulls per column strategy.
5. Validate with rules; route failures to quarantine.
6. Load clean data; report quality metrics.

The consistent exam principle: quarantining bad rows keeps the pipeline running and keeps an audit trail, which beats both "drop the rows" and "fail the load."

Where Each Remediation Lives in Fabric

DP-600 also tests where you implement these patterns. Match the cleaning step to the engine:

• Dataflow Gen2 (Power Query) — interactive profiling (column quality, distribution, and profile cards), Trim, Clean, type changes, replace-values, and remove-duplicates for low-code analysts.
• Spark notebook — dropDuplicates, window-function dedup, na.fill/na.drop, regex standardization, and writing rejected rows to a separate quarantine Delta table at scale.
• Warehouse T-SQL — NOT NULL and UNIQUE constraints, MERGE for upserts, and check queries (SELECT COUNT(*) ... HAVING COUNT(*) > 1) for key uniqueness.

A Worked Remediation Scenario

A daily product feed arrives with mixed casing (usa vs USA), padded SKUs (" A100 "), some null Category values, and occasional resends. The correct pipeline: trim and upper-case the country and SKU so keys match; default null Category to Unknown because it is descriptive, not a grain column; deduplicate on SKU keeping the latest LoadTimestamp; then validate SKU uniqueness and route any row still failing to a quarantine table. Only after those steps do clean rows merge into the product dimension, and a row count of quarantined records is logged as a quality metric.

This sequence — standardize, dedup, resolve nulls, validate, quarantine, load — is the pattern the exam expects you to recognize and reproduce.