Data quality metrics: measure defects without guessing missing facts
Data quality metrics quantify specific dataset defects against declared rules and populations. Report the defect count, denominator, exclusions and affected source rows so another reviewer can reproduce each rate. The six-row exercise below measures required-field completeness, allowed-value validity, excess duplicate keys and unresolved status conflicts while keeping unknown facts visible.
Completeness asks whether required observations are present; validity asks whether supplied values meet a rule. Neither establishes that a value is true. A fully populated, allowed status can still disagree with reality, and assessing that disagreement requires an independent source of evidence.
Quality scorecard transfer status: documented. This sheet-writing proposal is documented in the catalogue; not production-tested by Ai Flowi. The rates below are derived from invented records, with no external benchmark.
Copy the six-row source dataset
For this exercise, enquiry_id and status are required on every source row. Allowed statuses are exactly new, contacted and closed. Identifiers are case-sensitive text, and the contract expects one record per enquiry ID. Amount is optional, expressed in sen; an empty amount is unknown and a supplied zero remains zero.
source_row,enquiry_id,status,amount_sen
R1,Q01,new,12500
R2,Q02,contacted,
R3,Q03,waiting,0
R4,Q04,,8000
R5,Q02,closed,5000
R6,,new,9000source_row is an observation reference, not the business identity. R2 and R5 can therefore remain individually traceable while sharing Q02. No event timestamp or authoritative revision is supplied, so this dataset cannot tell the reviewer which Q02 status is current. Retain both observations and the conflict instead of choosing the later displayed row.
Freeze this source as SYN-QUALITY-A and record the rule revision before calculating. Do not clean or discard rows first: doing so would change the population whose defects the scorecard is supposed to describe. A separate accepted dataset can be prepared later, with its own lineage and decisions.
Copy the quality scorecard
metric,numerator,denominator,rate_percent,exclusions,affected_source_rows
Required-cell completeness,10,12,83.33,none,R4 and R6 missing cells
Status validity among populated statuses,4,5,80,R4 empty status,R3 invalid status
Excess duplicate-key rows,1,5,20,R6 missing key,R2 and R5 form duplicate group
Conflicted key groups,1,4,25,R6 missing key,Q02 represented by R2 and R5All four values are synthetic arithmetic rounded for display where needed. Keep the integer counts alongside the rounded rate. The completeness and validity percentages describe passing observations; the duplicate and conflict percentages describe defects. Do not give them a shared “higher is better” legend.
The metric-definition table supports horizontal scrolling and keyboard focus so its calculation and uncertainty columns remain accessible.
| Measure | Calculation | What remains unknown |
|---|---|---|
| Required-cell completeness | Six rows times two required fields gives 12 cells. R4 lacks status and R6 lacks enquiry_id, leaving 10 populated cells: 10 / 12. | Whether the ten supplied values are correct. |
| Allowed-value validity | Five statuses are populated. Four match the allowed vocabulary; R3’s waiting does not: 4 / 5. | Whether those four allowed statuses reflect actual enquiry stages. |
| Excess duplicate keys | Five rows have keys and four distinct keys remain. There is one excess occurrence: (5 − 4) / 5. | Which Q02 observation should represent the enquiry. |
| Unresolved conflict groups | One of four distinct nonblank keys has incompatible supplied statuses: 1 / 4. | The authoritative status for Q02. |
Distinguish cell completeness from complete rows
The headline completeness figure is 10 / 12, or approximately 83.33%, because its unit is required cells. If the question instead asks how many rows contain both required fields, R1, R2, R3 and R5 qualify. That row-completeness rate is 4 / 6, or approximately 66.67%. R3 counts as populated even though waiting is invalid.
Keep those two definitions separate. Calling 83.33% “complete records” would change the meaning of the denominator. Also retain R4 in missing-status reporting rather than letting its absence vanish because status validity excludes blank cells. A high validity rate over supplied values can coexist with extensive missingness.
Optional amount cells do not enter this required-field completeness calculation. R2’s empty amount needs an unknown state in any amount summary, while R3’s zero is a supplied amount. Filling R2 with zero would improve its appearance without acquiring evidence. Use the data dictionary template to agree those meanings before adopting a reporting rule.
Define what a duplicate rate counts
This scorecard counts excess occurrences beyond one per nonblank key. Q02 occurs twice, yielding one excess row. Another useful measure counts all rows involved in duplicated keys: R2 and R5 give 2 / 5, or 40%. Both calculations are reproducible, but they answer different questions. Name the chosen one in every report.
Missing identifiers are excluded from key-based metrics because their business identities are unresolved. R6 still appears in completeness and the missing-key issue list. Grouping every blank identifier together as one duplicated enquiry would invent a shared identity that the source does not establish.
The conflict calculation groups observations by nonblank key and compares their supplied status values. Q02 has contacted and closed, so one group requires review. The empty amount on R2 and populated amount on R5 do not independently prove an amount conflict: absence supplies no competing amount. Without authority or event timing, a changed stage remains an unresolved conflict under this snapshot’s one-record contract.
Review defects before changing records
Assign R3’s vocabulary question, R4’s missing status, R6’s missing key and Q02’s conflict to the relevant source custodian. Record the question and evidence needed for resolution. A reviewer can confirm that waiting should become an allowed stage, but that changes the rule revision; it does not establish that the original calculation was wrong.
Compare reporting periods only when required fields, population scope, exclusion rules and duplicate definitions agree. If a revised export omits rejected rows, a lower defect rate may reflect changed coverage. Retain the source count and issue references alongside the rate so that difference can be investigated.
Do not average these four percentages into a predictive quality score. Their units, directions and exclusions differ, and several defects can affect the same observation. Use data cleaning for justified repairs and ETL testing for transformation checks. The scorecard measures the supplied snapshot before those separate actions.
Transfer accepted metric rows to a spreadsheet
example configuration, not a recorded test
Refer to the Update Range catalogue entry for the shared write interface and the Google access guide to authorise your own account for the scorecard sheet.
Build values from the six-column scorecard CSV, with its header and four calculated metric rows occupying range_name Quality!A1:F5. Copy the exclusions and affected-source references as well as the rates. Column D contains numbers already expressed in percent: 83.33 denotes approximately 83.33%, while 80 denotes 80%. Applying a percentage display convention intended for fractions would misrepresent those supplied numbers. The transfer is a snapshot of the calculations above, not a formula that recalculates when source rows change.
Inspect the JSON result and reconcile each sheet row to SYN-QUALITY-A: completeness uses twelve required cells, validity uses five populated statuses, duplicate excess uses five keyed rows, and conflict uses four key groups. Do not replace those denominators with one common source-row count. Check that R4 remains named in the validity exclusion even though it also contributes a missing required cell. For combining this scorecard with other reporting evidence, use report automation.
What are the failure checks and unsuitable uses of data quality metrics?
Verify that all six source references remain available and that the metric counts reproduce the CSV. Test an empty dataset: each rate with a zero denominator should stay undefined. Remove R3’s status: required-cell completeness becomes 9 / 12 and validity becomes 4 / 4; the higher validity percentage must not conceal the new missing observation.
Change R5’s status to contacted: the duplicate excess stays one, while this status-conflict count becomes zero. That illustrates why deduplication and conflict measurement need separate rules. This exercise provides no accuracy audit, freshness guarantee, benchmark or automatic conflict resolution. Avoid using its rates as proof that the dataset is trustworthy enough for a consequential decision.
By Ai Flowi · Published .