Why Data Quality Rules Matter
A ratemaking analysis can be carefully designed and still produce misleading results if its inputs are malformed, incomplete, or inconsistent. A text value stored in a numeric field can disrupt a calculation; an exposure recorded in months but interpreted as years can distort indicated rates; and a claim linked to a nonexistent policy cannot be reliably assigned to a risk. Data-quality rules make these issues detectable before the data are used.
A rule is a testable condition applied to a field, record, or relationship between records. A failed rule identifies an exception for review. It does not, by itself, prove the underlying business record is wrong: a failed check may reveal an actual error, a legitimate unusual case, or an overly restrictive rule. Rules should therefore have a stated purpose, a defined scope, and an action for handling exceptions.
Specify Rules Before Testing Data
A useful rule is precise enough that two reviewers would apply it consistently. For each rule, identify the table and field, the condition to test, the population to which it applies, and the expected response to a failure. Include units and time basis. For example, “exposure is between zero and one” is meaningful only if exposure is measured in annual earned-exposure units and the record represents no more than one year.
Schema checks come first. A field expected to contain a date should not be accepted as an arbitrary text string merely because some text values resemble dates. A field expected to contain a nonnegative dollar amount should have a numeric representation and an identified currency basis. A policy identifier that serves as the key for related claims must be populated and uniquely identify a policy record in the relevant table.
A range rule can be a hard constraint or a reasonableness threshold. A hard constraint follows from the field definition or contract structure: a count of vehicles cannot be negative. A reasonableness threshold instead identifies a value for review; it might flag unusually high exposure or premium, but the threshold must reflect the portfolio and the field's definition. There is no universal upper bound suitable for every ratemaking field.
Completeness is often conditional. A driver-age field might be required only when the policy covers a driver for whom age is applicable. A claim's paid amount might be required only after a payment transaction has occurred. Treating every blank as an error can be just as misleading as overlooking a required blank. A blank, zero, and “not applicable” are distinct states and should not be substituted for one another without a defined rule.
| Rule family | Typical test | Question to settle before applying it |
|---|---|---|
| Schema | Exposure is numeric and expressed in annual units | What type and unit does each field represent? |
| Range | Annual exposure is at least zero and no more than one | Does the bound follow from the record's defined scope? |
| Completeness | Policy effective date is present for every in-force policy record | For which records and conditions is the value required? |
| Referential integrity | Every claim policy identifier matches a policy identifier | Which table supplies the authoritative set of valid keys? |
A Reliable Validation Sequence
A practical sequence moves from interpreting individual fields to checking relationships. The sequence also makes failures easier to diagnose: a value cannot be assessed against a range sensibly until its type and units are understood.
State what one row represents, name the fields, specify types and units, and identify required and key fields.
Test allowed categories, numeric bounds, date logic, and completeness conditions.
Compare foreign-key values, such as a claim's policy identifier, with the valid keys in the referenced table.
Count records tested and exceptions, identify which rules failed, and review whether each failure is an error or an explainable exception.
The logic of a referential-integrity test is direct. Let \(K\) be the set of valid policy identifiers and let \(p_j\) be the policy identifier on claim record \(j\). The claim satisfies the reference rule exactly when \(p_j \in K\). Thus, the set of orphan claims is the set of claim records for which \(p_j \notin K\). This is why checking that a field is merely nonblank is insufficient: a populated identifier can still fail to refer to any policy.
Worked Examples
Worked Example: Validating Policy Exposure Inputs
A hypothetical personal-lines analysis uses one row per policy term. Its exposure field is annual earned exposure, and each row represents at most one year. The schema requires a numeric exposure, a nonblank policy identifier, a territory in the set T1, T2, T3, and a numeric premium of at least zero. For this defined annual unit, exposure must be in the interval from zero to one, inclusive. Apply the rules to the five records below and calculate the proportion that passes every rule.
No probability distribution, interest rate, or mortality assumption is used: this is a deterministic validation exercise. The five records are: (P101, exposure 1.00, territory T1, premium $840); (P102, exposure 0.50, territory T2, premium $0); (P103, exposure 1.15, territory T1, premium $920); (P104, exposure 0.75, territory T9, premium $610); and (P105, exposure blank, territory T3, premium $700). Each identifier is present and unique; all premiums are numeric and nonnegative.
Step 1: Apply the exposure rule. P101 passes because \(0 \leq 1.00 \leq 1\). P102 passes because \(0 \leq 0.50 \leq 1\). P103 fails because \(1.15 > 1\). P104 passes because \(0 \leq 0.75 \leq 1\). P105 fails the required-value check because its exposure is blank; it cannot be treated as zero.
Step 2: Apply the territory rule. T1, T2, and T3 are allowed. P104 fails because T9 is not in the allowed set. The other four records pass this rule.
Step 3: Combine the rules by record. P101 and P102 pass all checks. P103 fails the exposure range, P104 fails the territory domain, and P105 fails exposure completeness. Therefore, 2 of 5 records pass all checks.
Step 4: Calculate the pass rate. The fraction passing is \(2/5=0.40\). As a percentage, \(0.40 \times 100=40\%\). The check is consistent: 2 passing records plus 3 failing records equals the 5 records tested.
Method and common mistake avoided. Apply each field rule first, then count records that pass the full set. Do not count a record as passing merely because its premium is valid, and do not silently replace the blank exposure with zero.
Worked Example: Checking Claim References and Date Logic
A hypothetical insurer validates claims against two policy terms: A1 is effective from January 1 through December 31, 2025, and A2 is effective from April 1 through December 31, 2025. Each claim must have a unique, nonblank claim identifier, a policy identifier that matches one of these policies, a loss date within that policy's effective dates, a report date on or after the loss date, and a numeric incurred amount of at least zero. Determine which of the five claims pass every rule.
No probability distribution, interest rate, or mortality assumption is used. The records are (claim ID, policy ID, loss date, report date, incurred amount): C1, A1, March 2, March 4, $1,200; C2, A2, December 20, December 21, $0; C3, A9, May 5, May 6, $300; C4, A1, December 31, 2024, January 2, 2025, $500; and C5, A2, June 1, May 30, \(-$40\). All claim identifiers are unique and nonblank.
Step 1: Check the policy reference. A1 and A2 exist in the policy table. C1, C2, C4, and C5 therefore pass the reference check. C3 refers to A9, which is absent, so C3 is an orphan and fails.
Step 2: Check coverage dates. C1's March 2 loss is within A1's term, and C2's December 20 loss is within A2's term. C4's December 31, 2024 loss is before A1's January 1, 2025 start date, so C4 fails. C5's June 1 loss is within A2's term. C3's loss date cannot be validated against a policy term because its policy reference does not resolve.
Step 3: Check chronology and amount. C1, C2, C3, and C4 have report dates on or after their loss dates. C5 fails because May 30 is before June 1. C1 through C4 have nonnegative numeric incurred amounts. C5 also fails the amount rule because \(-40 < 0\).
Step 4: Identify complete passes. C1 and C2 pass every applicable check. C3 fails the reference, C4 fails the coverage-date rule, and C5 fails both chronology and amount. Thus, 2 of 5 claims pass all checks, giving \(2/5=0.40=40\%\).
Method and common mistake avoided. Check each claim against its own referenced policy term, not merely against a portfolio-wide date range. Also retain separate failure reasons: C5 has two failed rules, but it is one claim record failing the overall validation.
Worked Example: Combining Rule Results Without Misreporting a Percentage
A hypothetical small commercial portfolio has five claim records. Four rules apply: the policy identifier must match a policy record; loss date must fall within that policy's term; incurred amount must be numeric and nonnegative; and report date must be on or after loss date. The review finds: C11 passes all four rules; C12 fails the policy reference; C13 fails the amount rule; C14 fails the coverage-date rule; and C15 fails the report-date rule. Calculate the number and percentage of records passing every rule.
No probability distribution, interest rate, or mortality assumption is used. First count the records that pass all rules: only C11 passes, so the passing count is 1. The total tested count is 5. The pass fraction is \(1/5=0.20\).
To express a fraction as a percentage, multiply by 100: \(0.20 \times 100=20\%\). Therefore, 1 of 5 records passes, and the pass rate is 20%. The remaining \(5-1=4\) records fail at least one rule.
Check. The failure percentage is \(4/5 \times 100=80\%\), and \(20\%+80\%=100\%\). The arithmetic is internally consistent.
Method and common mistake avoided. Keep the count, fraction, decimal, and percentage distinct: 1 record; \(1/5\); 0.20 as a decimal; or 20% as a percentage. Writing “0.20%” would be incorrect because \(0.20\%\) equals \(0.002\) as a decimal, not \(0.20\).
Interpreting Exceptions and Avoiding Traps
A validation result is evidence about the data under the chosen rules, not an automatic correction instruction. If an exposure exceeds a stated bound, first confirm that the record really is an annual exposure rather than a multi-year term or a different unit. If a claim's policy reference does not match, check whether the relevant policy table is complete and whether identifiers were transformed consistently. A result can point to a data defect or to an incorrectly specified test.
The definition of the tested population matters. For a completeness rule, state the denominator: all records, only records for which the field is applicable, or another explicit subset. For an overall pass rate, count each record once even if it fails several rules. A separate rule-failure count may count multiple failures on one record, so it need not equal the number of records failing validation.
Common traps include treating zero as missing, comparing values in inconsistent units, using a range without defining the exposure period, checking only whether a foreign key is populated, and reporting a decimal fraction as though it were a percentage. Rules should make these distinctions explicit. Record the test condition and the outcome in a way that allows a reviewer to reproduce the result.
Check Your Understanding
For each problem, apply the stated rules exactly and distinguish the record count from the percentage.
- A hypothetical annual policy file has four exposure values: 0.25, 0, 1.02, and blank. Exposure must be numeric, present, and between zero and one inclusive. How many records pass, and what is the pass percentage? (Answer: 2 of 4 pass; 50%.)
- A policy table contains keys P1 and P2. Three claims refer to P1, P3, and P2, respectively. How many fail the rule that each claim's policy key must match a policy-table key? (Answer: 1 claim, the one referring to P3.)
- In a hypothetical telematics program, a response field is required only for policies with consent recorded as Yes. Of 12 policies, 9 have Yes consent; 1 of those 9 has a blank response. What is completeness among applicable policies? (Answer: 8 of 9, or 88.9%.)
- Eight claim records are tested against all stated rules. Six pass every rule and two fail at least one rule. What are the overall pass count and pass percentage? (Answer: 6 of 8; 75%.)
- Twenty policy records are tested for a required territory and a valid exposure range. Eighteen have a valid territory, 16 have a valid exposure, and 15 pass both. What percentage pass both rules? (Answer: 15 of 20; 75%.)