Prevent bad records from reaching reports by defining what each row represents, enforcing a stable key where data enters the system, testing the transformed datasets, and deciding in advance what happens when checks fail. A unique row ID alone is not enough: the same customer or event can still appear in multiple rows under different IDs. For records that may legitimately overlap, use documented field-by-field survivorship rules and preserve an audit trail rather than letting an arbitrary update decide which value wins.
Start by defining what one row means
Write a plain-language grain statement for every table that feeds a report, such as “one row per customer” or “one row per order line.” The right uniqueness test depends on that statement: a customer can have many orders, and an order can have many lines, so a customer ID cannot be unique in either of those tables.
As an Amazon Associate I earn from qualifying purchases.
Then decide what the key identifies. It may identify a source record, a real-world entity, or an event; those are different things. A source-generated ID can be unique while the same person is represented by several source records. Great Expectations distinguishes checking primary-key uniqueness from identifying duplicate real-world entities.
- Choose a stable source or business key that matches the table’s grain.
- Use a composite key when multiple stable fields together identify the entity or event. Confirm the fields are stable and sufficiently identifying; a display name or convenient label may repeat.
- Where identifiers can overlap between systems or regions, include the relevant source or regional identity in the key rather than assuming an ID is globally unique.
- Assign ownership and precedence for important fields, so teams know which source is authoritative when values disagree.
Stop duplicates near ingestion, but do not rely on that alone
Where the storage platform supports it, enforce primary-key or alternate-key uniqueness at the data boundary. This blocks a second write with the same exact key before it becomes part of the reporting pipeline.
#1 Best Overall
Exact key checks and fuzzy entity matching address different problems. Exact checks are predictable when stable identifiers exist. Matching on names, email addresses, phone numbers, or other imperfect attributes can surface likely real-world duplicates, but similar attributes do not prove that two records refer to the same entity. Route uncertain matches for review rather than silently merging them.
For example, Microsoft Dataverse documents rule-based duplicate detection on create, update, and import, using match codes. Microsoft also warns that duplicate detection based on existing match codes can miss records processed at the same time. In practice, ingress controls reduce risk; they do not eliminate the need for scheduled duplicate scans and downstream validation.
Rank #2
Test the data at each stage that can change it
A form-level rule cannot catch every issue introduced by imports, concurrent processing, or transformations. Test both staging data and report-facing models, focusing on the key and the business rules that make the resulting metrics trustworthy.
Recommended Free Tools
- Uniqueness and completeness: test that the intended key is unique and non-null at the relevant grain.
- Relationships: test that foreign keys refer to records that exist in the related dataset.
- Allowed values: constrain controlled categories, such as status codes, to accepted values.
- Business consistency: check rules that should hold together, such as incompatible statuses or dates that violate the process’s logic.
dbt documents tests for uniqueness, non-nullness, accepted values, and relationships; its tests are SQL queries that return failing records. Great Expectations supports uniqueness expectations for single columns and combinations of columns. These are examples of ways to implement the checks, not requirements to use a particular product.
Check incremental loads and merge behavior
If a pipeline processes only new or changed rows, verify that its configured unique key actually identifies each incoming row and that the warehouse supports the intended update or merge behavior. dbt’s incremental-model documentation notes that a row can be inserted when its unique key is absent from existing data. Declaring a key does not fix a key definition that is incomplete, unstable, or wrong for the grain.
Resolve conflicting records with explicit survivorship rules
Before merging records, establish that they represent the same entity. Then specify, field by field, how to choose the retained value. A policy might give one source precedence for a particular field, prefer a more recent value where recency is meaningful, or prefer a populated value over an empty one. Those rules should reflect the field’s meaning; “latest wins” is not a safe universal rule.
Keep the source identifiers and record the merge or correction, selected values, decision basis, and relevant timestamps. That lineage lets report users trace how a displayed value was chosen and gives stewards a way to investigate later changes.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteMicrosoft Learn describes a Dataverse merge workflow that exposes conflicting fields and lets a user choose which record’s value to retain. That is a useful example of explicit survivorship, not a capability that should be assumed in every system; verify the supported scope and behavior of the platform in use. Microsoft Learn notes: “Duplicate records can creep into your data when you or others enter data manually or import data in bulk.”
Best Value
Decide what a failed check does before it happens
A failed quality check is a reporting control, not just a developer notification. For each critical dataset, choose whether an anomaly should warn, quarantine the affected batch, or block publication. Base the action on the consequences of publishing a wrong metric and the cost of delaying a refresh.
- Warn: allow publication when the issue is within an explicitly accepted tolerance, while notifying the owner.
- Quarantine: isolate suspect rows or a batch so valid data can proceed only if the report’s design makes that safe.
- Block: stop publication when a critical invariant—such as uniqueness of a report’s primary key—fails.
When a check fails, retain the failing rows, identify the affected models and reports, assign an owner, record remediation, and rerun the checks before treating affected metrics as trusted. Include refresh time and check status in operational reporting so report consumers can see whether the latest data passed its controls.
Reconcile and monitor beyond individual checks
Track quality over time, not just pass or fail on a single run. Monitor uniqueness and consistency trends, review repeated failures, and investigate whether a change in source behavior or transformation caused the shift. For suitable datasets, reconcile input and output row counts or other control totals to detect unexpected loss or multiplication during processing.
Free tools Windows power users keep installed
One-click scans. No signup required.
Microsoft Purview’s data quality reporting describes dimensions including uniqueness and consistency, with the ability to view failures by rule and asset. That kind of visibility helps stewards locate recurring problem areas; the specific reporting features depend on product configuration.
Schedule cleanup for issues that prevention misses
Run recurring duplicate scans against existing data, inspect candidate matches, and merge only after review under the survivorship policy. Microsoft Dataverse documents scheduled duplicate-detection jobs and emphasizes that published detection rules must be enabled first. Cleanup should also lead to a prevention change when the same failure pattern recurs; otherwise the next import or concurrent write may recreate it.
Quick Recap
Choose controls according to the risk
| Decision | Best fit | Trade-off |
|---|---|---|
| Exact key enforcement or fuzzy matching | Use exact enforcement when stable identifiers exist; use matching rules to surface possible duplicates across imperfect attributes. | Fuzzy matching can produce false positives and generally needs review; exact equality will not find differently keyed records for the same entity. |
| Source prevention or downstream detection | Use source controls to reject some invalid writes early and downstream tests to catch problems from imports, concurrency, or transformations. | Source controls alone have blind spots; downstream detection finds issues later, after they may have entered a pipeline. |
| Automatic merge or reviewed survivorship | Automate only when field precedence is well-defined and low risk; use a review for ambiguous or sensitive conflicts. | Automation can apply a rule consistently, but an incorrect rule can systematically overwrite good data. Review adds effort while preserving a human decision for uncertain cases. |
| Warning, quarantine, or blocking | Set the action per dataset according to the impact of a wrong report and acceptable refresh delay. | Warnings preserve availability but require follow-up; quarantine or blocking can delay reporting while protecting consumers from suspect results. |
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
