Microsoft DP-700: Building Data Quality and Reliability Controls

Data engineering reliability is not just uptime. A pipeline can complete every day and still publish duplicates, stale records, invalid keys, or silently missing partitions. DP-700 explicitly calls out duplicate, missing, late-arriving, and error conditions because trustworthy analytics require controls over the meaning of the data as well as the execution of the platform.

Quality controls should be designed into ingestion and transformation stages so defects are detected near their source and handled consistently.

Define quality as measurable expectations

“The data should be good” is not a test. Useful dimensions include completeness, validity, uniqueness, consistency, timeliness, and reconciliation to a trusted total. Each important data product should define which dimensions matter and what threshold is acceptable.

A customer table may require unique customer IDs and non-null country codes. A payments feed may require reconciliation to source transaction count and amount. Telemetry may tolerate some late events but not a sustained drop in device coverage.

The Microsoft DP-700 exam increasingly treats these rules as engineering decisions rather than cleanup after the pipeline.

Validate early without destroying source evidence

Early checks can reject unreadable files, malformed records, impossible types, or missing required keys before bad data spreads through downstream models. But the original payload should often be preserved long enough for diagnosis and replay.

Quarantine is useful when a subset of records is bad but the rest of the load can continue safely. The quarantine record should retain source identity, failure reason, and processing context so remediation does not depend on reconstructing the event later.

For severe contract violations, failing the entire load may be safer than publishing a partial data product. The business impact should determine the behavior.

Handle duplicates with business-aware keys

Duplicates are not always byte-for-byte identical rows. A retry may resend the same transaction with a new arrival timestamp. Two source systems may represent the same entity with different identifiers. Deduplication therefore requires a business key and a rule for choosing the authoritative record.

Sequence numbers, update timestamps, source priority, and version flags can help. The rule should be deterministic so rerunning the process produces the same result.

If duplicates are expected from at-least-once delivery, treat deduplication as part of normal processing rather than an exceptional cleanup.

Distinguish missing data from zero activity

A source that legitimately produced no sales is different from a source that failed to deliver a file. Both can result in zero rows, but they require different operational responses. Pipelines should know whether an expected input arrived and whether the reported business volume is plausible.

Missing fields also need context. A nullable description may be acceptable, while a missing account ID can make the record unusable. Default values should not be inserted simply to make validation pass if they change the business meaning.

Quality rules must therefore be tied to the role each field plays in downstream analysis.

Design for late-arriving data

Late data is normal in many systems. Mobile devices reconnect, upstream jobs finish late, and corrections arrive after an accounting period closes. The engineering design should define how long a processing window remains open and which downstream structures need to be updated.

Fact data and dimension data can arrive in different order. A transaction may reference a customer dimension row that has not yet appeared. The solution may use an unknown member temporarily, delay the fact, or reprocess after the dimension arrives. Each choice affects reporting consistency.

The current DP-700 objectives explicitly mention late-arriving data because candidates need to reason about these trade-offs.

Reconcile important outputs to authoritative totals

Row-level validation can still miss systemic loss. Reconciliation compares a target to an independent source measure such as record count, transaction amount, control total, or expected partition list.

For financial or regulated data, reconciliation can be more important than any single schema rule because it answers whether the delivered product represents the complete source population.

The broader data engineering role includes building these controls so analysts do not have to discover missing data during reporting.

Turn quality failures into an operational workflow

A dashboard that shows invalid records is useful only if someone owns the next action. Define who investigates source defects, who approves corrections, how repaired data is replayed, and how the incident is closed.

Repeated quality failures should trigger root-cause work. If every daily file violates the same rule, the organization should fix the producer contract or the rule rather than normalizing permanent failure.

Quality telemetry should be retained over time so teams can see whether a source is improving or degrading.

Test quality controls when logic changes

Transformation code changes can invalidate quality assumptions. A new join may multiply rows, a changed filter may drop valid history, and a schema migration may convert previously non-null fields. Automated tests should validate important quality rules before deployment and again on representative integrated data.

Quality checks also need versioning. When a business rule changes legitimately, the team should know which historical data was validated under the old rule and whether backfill is required.

Adjacent analytical work such as Microsoft DP-600 relies on stable engineered data; DP-700 is responsible for making that trust measurable and repeatable.

Freshness should be measured from the business event, not just the last successful pipeline run. A pipeline can run on time while its source is three hours stale. Tracking the newest source timestamp exposes that difference and gives consumers a more honest view of the product.

Referential integrity deserves explicit monitoring in analytical systems even when the storage engine does not enforce it. Facts that point to missing dimensions, orphaned relationships, or invalid hierarchy members can break totals and segmentation without causing a technical error.

Distribution checks can catch defects that row-level rules miss. A sudden shift in country mix, order value, event type, or null percentage may indicate an upstream mapping problem even though every individual row passes its validation rules.

Quality thresholds should have severity levels. A minor optional-field degradation may generate a warning, while a reconciliation mismatch in a financial fact table should block publication. Treating every rule identically creates either too much noise or too little protection.

Consumers need visibility into quality status. If a data product is published with a known partial issue, the affected scope and expected correction time should be communicated clearly. Silent degradation damages trust more than an explicit, bounded incident.

Remediation should preserve auditability. Corrected records need a reason, source, timestamp, and processing path so teams can explain why the current result differs from an earlier published version.

Quality ownership should follow the defect. Engineering can detect a malformed source value, but the operational system owner may be the only team able to prevent recurrence. Mature processes route issues to the owner who can correct the root cause instead of making the data platform absorb permanent compensating logic.

Schema contracts should define not only field names and types but also semantics. A column called “status” can be technically valid while its allowed values or meaning change. Contract tests that check enumerations, units, time zones, and key behavior catch this class of breaking change earlier.

Data-quality rules need performance awareness. Running an expensive full-table uniqueness scan after every micro-batch may be unnecessary. Some checks can be scoped to the changed partition while periodic deeper audits validate the broader data set.

Lineage strengthens remediation. When a bad source file affects several curated tables and reports, lineage helps identify the downstream blast radius and prioritize reprocessing. Without it, teams may fix the first visible dashboard while leaving other products inconsistent.

Quality incident severity should reflect consumer impact. A missing optional description in an internal table is not equivalent to duplicated revenue in an executive report. Prioritization keeps the engineering team focused on defects that materially affect decisions.

Reliable data products should expose their latest successful processing and quality status so consumers can make informed choices. A dashboard that clearly shows “data through 09:00, all checks passed” is more trustworthy than one that silently displays whatever happened to load last.

Data reliability also includes deterministic reprocessing. When a defect is fixed, rerunning the same source interval should create a known corrected state rather than a third version that depends on execution order. Stable business keys and controlled merge logic make remediation repeatable.

Quality metrics should be reviewed with source owners, not only data engineers. A persistent rise in rejected records can signal a producer-process problem that should be solved upstream.

When a quality rule is intentionally relaxed, record the business reason and effective date. Otherwise future engineers may interpret the weaker threshold as an accidental defect and change it without context.

Transparent rule history makes quality governance easier to audit and maintain.

Microsoft’s announced October 19, 2026 DP-700 guide explicitly includes duplicates, missing data, late-arriving data, error resolution, monitoring, and optimization. Those objectives reinforce that reliability is a data property as well as a platform property.

For scenario questions, identify the quality risk, decide whether to reject, quarantine, correct, or tolerate it, and ensure the process remains replayable and observable after the decision.