Data Governance
Part of Data quality checks
Detecting duplicate business records before aggregation
Duplicate records can inflate a sum without looking obviously wrong in a chart.
Before aggregating sales, contacts or cases, decide what one row represents and what must be unique at that grain. Then test repeated keys, generate entity-match candidates, review them and apply only confirmed decisions to the aggregation.
Start with the row definition
Write down what one row means and identify its expected key. An order-line table can contain several rows with the same order ID because each line is a different item.
For that table, a combination such as order ID and line number may be the intended unique key. An order header table may require the order ID alone, so testing the wrong key can either miss duplicates or flag valid records.
To find repeated key combinations in SQL, group by the intended key columns and return groups whose count exceeds one: SELECT order_id, line_number, COUNT(*) AS row_count FROM source_table GROUP BY order_id, line_number HAVING COUNT(*) > 1; Change the grouped columns to match the table’s expected key.
A zero-row result means the check found no repeated key in the tested data; it does not prove that distinct keys represent distinct real-world entities. A generated customer ID can be unique for every row even when multiple rows describe one customer.
Great Expectations (GX) provides Expectations for validating uniqueness. In dbt, a singular SQL data test can return duplicate key groups as failing rows; run it with dbt test, where zero failing rows means the assertion passes.
For entity matching, form candidate pairs from records that share a selected identifier or contact value, then compare the other fields. This is blocking: Machop is a named entity-matching framework that uses blocking to generate candidate pairs.
A candidate rule could combine a normalised company name with a registration number. Similar names alone are weak evidence because abbreviations, shared addresses and family names can mislead.
Key Metrics in Duplicate Record Management
Review candidates before removing anything
Build a review view showing the candidate rows, source system, creation time, status and fields used to match them. Include the downstream measure they could affect, then classify each pair as duplicate, legitimate separate record or unresolved.
In the sample customer data, customer_id 1 is John Doe with [email protected] and government_id 123-45-6789; customer_id 5 is J Doe with [email protected] and the same government ID. Treat them as a candidate pair to review, not an automatic merge.
For example, a repeated invoice number and amount with different ingestion timestamps may be a retry of one invoice. A reused invoice number across separate legal entities or periods might be valid, so check the entity, period and source identifiers; do not deduplicate on amount alone.
Keep the original records and the decision trail; a silent delete can make later reconciliation impossible. Similar-looking records may be legitimate separate events, so retain unresolved cases for review rather than merging by guesswork.
Protect the aggregation
If duplicates are ingestion retries, fix the loading rule and use a stable event identifier for idempotent processing. If multiple profiles represent one customer, preserve source rows but map them to a reviewed canonical entity before calculating customer counts.
For unresolved cases, label the metric or exclude that segment under an agreed rule rather than merging records by guesswork. Calculate the candidate-group count and compare the affected metric as-is with the metric under the proposed deduplication; the difference is the potential value at risk in that metric.
Re-run the check after each source or transformation change. Keep the matching rule, reviewed decisions and exceptions so the aggregation can be audited.
