Data Quality Metrics and Exception Triage

Why Data Quality Metrics Need Context

A data-quality percentage is useful only if its numerator and denominator describe the same population and the measure answers a defined question. A small exception rate can still affect a rating-critical field or a large dollar amount; a larger rate in a noncritical field may be containable. Ratemaking review therefore combines consistent metrics with an explicit decision about severity and escalation.

This tutorial defines four complementary measures: completeness, validity, uniqueness, and reconciliation. They describe different failure modes, so they should not be combined into one score without a defensible method. The focus is on interpreting these measures and deciding what to do with exceptions, not on setting data rules or reconciling exposure and loss totals in depth.

Definition: Completeness measures whether required data are present. Validity measures whether observed values meet specified rules. Uniqueness measures whether records are represented only as intended under a defined key. Reconciliation compares corresponding totals or records from two sources or processing stages against an agreed benchmark.

Define Each Metric and Its Population

For completeness, define the required fields and the eligible records before counting. If there are $N$ required field values and $M$ are missing, the missing rate is $M/N$, and the completeness rate is $1-M/N$. For a particular field, the denominator should be the number of records on which that field is required, not automatically the total number of rows in the file.

Validity needs a separate denominator. A useful field-level measure is the invalid rate among nonmissing values: invalid, observed values divided by all observed values subject to the rule. Report completeness alongside it. If missing values are excluded from the validity denominator, a high validity rate does not imply that the field is broadly usable. The overall usable rate—values both present and valid divided by required values—can make that distinction clear.

\text{Completeness}=\frac{\text{required values present}}{\text{required values}}, \qquad \text{Validity among observed values}=\frac{\text{observed values passing rules}}{\text{observed values}}

For uniqueness, state both the key and the row population. If a transaction extract contains $T$ eligible rows and $D$ are excess duplicate rows under the agreed key rule, the excess duplicate-row rate is $D/T$. This is not the same as the percentage of keys that have duplicates: that measure has distinct keys as its denominator and requires a different numerator. Never divide excess duplicate rows by a count of policies and label the result a duplicate-row rate unless the denominator is in fact the eligible row population.

For reconciliation, define which source is the benchmark, what quantity is compared, and whether the reported difference is signed or absolute. A signed difference preserves direction; an absolute relative difference expresses the size of the discrepancy without direction. A zero difference is not proof of correctness if the sources share the same error, but a material difference is a reason to investigate.

\text{Signed relative difference} =\frac{\text{comparison total}-\text{benchmark total}}{\text{benchmark total}}, \qquad \text{Absolute relative difference} =\frac{|\text{comparison total}-\text{benchmark total}|}{|\text{benchmark total}|}

These ratios require a nonzero benchmark and comparable definitions, units, periods, and scope. If those conditions are not met, report the absolute difference and explain why a percentage comparison is not meaningful. In particular, an exposure total in policy-years should not be compared directly with a count of policy records.

Worked Examples

Worked Example: Separating Completeness, Validity, and Duplicate Rows

A hypothetical policy-term extract is expected to contain 1,200 unique policy-term records. The raw transaction file has 1,216 eligible rows, including 16 excess duplicate rows under the agreed policy-term key. For the field metrics, an approved source-selection rule identifies one record per expected policy term. Of those 1,200 selected records, 18 lack a required annual mileage value. The remaining values include 11 that fail the documented rule that mileage must be a positive number no greater than 100,000. Calculate the four relevant rates: completeness, invalidity among observed values, overall usability, and excess duplicate-row rate.

Method. Keep the populations distinct. Calculate the field measures on the 1,200 selected policy-term records, and calculate the duplicate-row rate on all 1,216 eligible transaction rows. This avoids treating a row-based numerator as though it were a policy-based numerator.

Completeness. The number of observed mileage values is $1,200-18=1,182$. Thus completeness is $1,182/1,200=0.985$, or 98.5%; the missing rate is $18/1,200=1.5\%$.

Validity among observed values. There are 11 invalid observed values out of 1,182 observed values. The invalid rate is $11/1,182=0.009306$, or 0.931%. The validity rate is $1-0.009306=0.990694$, or 99.069%.

Overall usability. There are $1,182-11=1,171$ values that are both present and valid. Therefore the overall usable rate is $1,171/1,200=0.975833$, or 97.583%. This is lower than the validity rate because missing values are not included in the validity denominator.

Excess duplicate-row rate. Using the stated eligible transaction-row population, the rate is $16/1,216=0.013158$, or 1.316%. The calculation is checked against the stated 1,216-row denominator; it is not $16/1,200$, because 1,200 is the expected unique-record count, not the transaction-row population.

Decision implication. These rates identify different exceptions. The mileage field has 29 unusable selected records, while the raw transaction file has excess duplicate rows. The metric values alone do not establish that either issue is immaterial; the reviewer must assess affected rating use and confirm that the source-selection rule is valid.

Worked Example: Ranking Two Reconciliation Exceptions

A hypothetical review compares source-system totals with a ratemaking extract for the same coverage, period, and population. The agreed source benchmarks are 8,450 policy-years and $2,400,000 of incurred losses. The extract contains 8,425 policy-years and $2,370,000 of incurred losses. The review protocol sets a 0.50% absolute relative-difference tolerance for each total. Calculate both differences and decide which exceptions require escalation.

Exposure reconciliation. The signed difference is $8,425-8,450=-25$ policy-years. The absolute relative difference is $25/8,450=0.002959$, or 0.296%. This is below the stated 0.50% tolerance.

Loss reconciliation. The signed difference is $$2,370,000-$2,400,000=-$30,000$. The absolute relative difference is $$30,000/$2,400,000=0.0125$, or 1.250%. This exceeds the 0.50% tolerance.

Comparison and decision. The exposure difference is within this protocol's tolerance, so document it and confirm that the tolerance permits proceeding. The loss difference exceeds tolerance and should be investigated and escalated under the applicable review process before the affected loss indications are relied upon. The dollar difference and its possible effect on indicated results matter in addition to the rate.

As a check on scale, source loss cost is $$2,400,000/8,450=$284.02$ per policy-year, while extract loss cost is $$2,370,000/8,425=$281.31$ per policy-year, rounded to cents. Their difference illustrates why a discrepancy in losses should not be declared harmless solely because exposure also differs. These ratios are descriptive checks, not a substitute for finding the cause of the mismatch.

Worked Example: Triage When Several Metrics Fail

A hypothetical commercial auto extract is expected to contain 5,000 policy-term records. Its transaction file has 5,010 eligible rows, including 10 excess duplicate rows. After applying an approved record-selection rule, 25 of the 5,000 required territory values are missing. Of the 4,975 observed territory values, 15 are invalid under the approved territory-code list. Exposure is 17,820 policy-years in the extract versus a source benchmark of 18,000. Incurred losses are $909,000 in the extract versus a source benchmark of $900,000. The review protocol permits absolute relative differences up to 0.50% for exposure and losses. Territory is required to assign the rating territory. Decide what should be escalated and whether the file is ready for rating analysis.

Uniqueness. The excess duplicate-row rate is $10/5,010=0.001996$, or 0.200% when rounded to three decimals. The denominator is the 5,010 eligible transaction rows.

Territory completeness and validity. Completeness is $(5,000-25)/5,000=4,975/5,000=0.995$, or 99.5%. The invalid rate among observed territory values is $15/4,975=0.003015$, or 0.302%, giving a validity rate of 99.698%. There are $4,975-15=4,960$ values that are both present and valid, so overall usability is $4,960/5,000=0.992$, or 99.2%. Thus 40 policy terms, or 0.8%, lack a usable territory value.

Reconciliation. The exposure difference is $17,820-18,000=-180$ policy-years, with absolute relative difference $180/18,000=1.000\%$, above the 0.50% tolerance. The loss difference is $$909,000-$900,000=$9,000$, with absolute relative difference $$9,000/$900,000=1.000\%$, also above tolerance.

Triage decision. Escalate both reconciliation exceptions because each exceeds its stated tolerance. Also escalate the territory exceptions: although the unusable share is 0.8%, territory is required to assign a rating factor, so affected records cannot be assigned reliably without an approved resolution. Quarantine affected records or the relevant rating run under documented controls, investigate the duplicate and reconciliation causes, and obtain an authorized decision on remediation or exclusion. Do not proceed as though the entire file were ready merely because the majority of territory values are usable.

Why the Reconciliation Formula Works

A relative reconciliation difference expresses the discrepancy as a fraction of the benchmark. Let $B$ be a nonzero benchmark total and $C$ the corresponding comparison total. The absolute difference is $|C-B|$. Dividing it by $|B|$ scales the discrepancy to the benchmark's size; multiplying by 100 expresses it as a percentage. Therefore a 0.50% tolerance is met exactly when the absolute difference is no more than 0.005 times the absolute benchmark.

\frac{|C-B|}{|B|}\leq 0.005 \quad\Longleftrightarrow\quad |C-B|\leq 0.005|B|

This equivalence explains why the benchmark must be stated: changing the denominator can change whether an exception passes a tolerance. It also explains why the comparison must use commensurable quantities. A percentage does not repair a mismatch in definitions or units.

Rank Exceptions and Choose an Escalation

Metrics help quantify exceptions; triage determines their significance and response. A practical review should establish the governing thresholds and escalation authority in advance where possible. A threshold is a trigger for review, not proof that an exception below it is harmless or that one above it is automatically a model failure.

Confirm the metric.
Check the rule, numerator, denominator, unit, period, and population. Verify that the measure is calculated on the intended records and that missing values are not silently excluded from a measure that is meant to include them.

Assess consequence and scope.
Determine whether the affected field or total is necessary for rating, how many records are affected, and whether the issue could alter exposure, losses, or rate indications. A low rate can be important when it affects a required input or a concentrated subset of business.

Contain and assign.
Decide whether to hold the affected records or analysis, assign an owner, and set a resolution path. Escalate exceptions beyond approved tolerance or those with potentially material consequences, even if a broad portfolio percentage appears small.

Document the disposition.
Record the metric, source populations, threshold, affected use, decision-maker, and whether the issue was corrected, excluded, or accepted with justification. A disposition should not imply that an unresolved exception was corrected.

Avoid ranking issues by percentage alone. Consider at least the size of the exception, rating criticality, potential dollar or indication impact, concentration in a segment, ability to identify affected records, and whether the data can be safely contained. A missing value in a required classification field may prevent valid rating for those records even if completeness is high. Conversely, a reconciliation difference within an approved tolerance may still merit investigation if the source definition is uncertain.

Common Mistakes

Using mismatched populations. Do not divide excess duplicate transaction rows by a policy count and call the result a duplicate-row rate. State the eligible transaction-row denominator, or give the measure a different, accurate label.
Hiding missingness inside validity. A validity rate calculated only among observed values must be identified as conditional on being observed. Report completeness separately.
Changing the reconciliation benchmark without saying so. Relative differences depend on the denominator. Name the benchmark and report the signed difference when direction matters.
Treating thresholds as automatic permission. A measure within tolerance does not resolve uncertainty about definitions, concentrated effects, or rating-critical records.
Adding unlike measures into one score. Completeness, duplicate rows, invalid codes, and dollar reconciliation have different units and meanings. Rank them using documented decision criteria rather than an arbitrary sum.

Key takeaway: A defensible data-quality conclusion states what was measured, on which population, against which rule or benchmark, and why the resulting exception does or does not affect the intended ratemaking use. Escalation should follow both measured size and plausible consequence.

Application to Ratemaking Review

For an actuarial review, the output of triage is a reasoned disposition, not just a dashboard of percentages. A reviewer should be able to trace each material exception to its source population, understand why its severity was assigned, and see whether it was resolved or contained before affected data were used. These practices support a defensible data review aligned with the CAS Exam 8 (Advanced Ratemaking) syllabus.

Check Your Understanding

For each problem, identify the denominator before calculating and state what the result implies.

A hypothetical extract has 400 required values for a field. Four are missing, and 8 of the remaining values fail a validity rule. What are completeness, validity among observed values, and overall usability? (Answer: completeness $396/400=99.0\%$; validity $388/396=97.98\%$; overall usability $388/400=97.0\%$.)
A transaction file has 1,008 eligible rows, including 8 excess duplicate rows under its defined key. What is the excess duplicate-row rate? (Answer: $8/1,008=0.7937\%$.)
A benchmark is 2,080 policy-years and the comparison extract has 2,055. Calculate the signed and absolute relative difference using the benchmark as denominator. (Answer: signed difference $=-25$ policy-years; absolute relative difference $25/2,080=1.2019\%$, with a negative signed relative difference.)
A required field has 980 observed values and 14 are invalid; there are also 20 missing values among 1,000 required records. Calculate the invalid rate among observed values and overall usable rate. (Answer: invalid rate $14/980=1.4286\%$; usable values $966$, so overall usable rate $966/1,000=96.6\%$.)
A hypothetical rating extract has a 0.20% duplicate-row rate, but its required territory field is missing or invalid for 30 of 2,500 unique policy terms. The review tolerance for duplicate rows is 0.50%, and territory is necessary to assign a rating factor. What is the unusable territory rate, and should the territory exception be escalated? (Answer: $30/2,500=1.20\%$ unusable; escalate and contain the affected records because the field is rating-critical, even though the duplicate-row rate is within tolerance.)

What You'll Learn