Why Record-Level Decisions Matter
Ratemaking data can contain blank fields, repeated records, and values that appear impossible or inconsistent with the data definition. Each issue requires a controlled decision: retain the record as is, correct it using reliable evidence, or exclude it under a documented rule. A decision made without tracing its evidence can change exposure, losses, classification variables, and ultimately the indications produced from those inputs.
The aim is not to make a data set look tidy. It is to preserve valid information, prevent the same information from being counted more than once, and avoid treating an unsupported guess as fact. Apply the quality rules and definitions established for the analysis, and retain enough information for another reviewer to understand and reproduce each decision.
For each exception, identify the affected record and field, the rule that raised the concern, the evidence reviewed, the disposition, and the effect on downstream inputs. A correction should be traceable to an authoritative source or a verified transformation. If a value cannot be established, do not silently substitute a convenient value such as zero, the portfolio average, or the most common category.
A Controlled Resolution Process
A practical workflow separates detection from disposition. The rule identifies a record for review; it does not, by itself, prove that the record is wrong. For example, two records with the same policy identifier may represent duplicated policy-period data, or they may represent separate coverage parts. Likewise, a large claim can be unusual but valid.
Specify the field, record key, and rule involved. Preserve the source record and its original value so the review starts from a reproducible state.
Check relevant source systems, record definitions, transaction identifiers, and related fields. Similarity alone is not sufficient evidence of duplication or error.
Retain, correct, or exclude the affected field or record. State the rule and evidence. If unresolved, preserve it as an exception rather than inventing a value.
Update the relevant model fields and summaries, then confirm that the applied change agrees with the documented decision.
The scope of a disposition matters. A missing rating territory may require excluding a record from a territory-specific analysis while retaining it for a total-loss reconciliation. A duplicate claim payment may require removing one payment transaction, not deleting the policy and its exposure. Record exactly which analysis or fields are affected.
Missing Fields: Recover, Exclude, or Retain as Unknown
First determine what the field means and whether it is required for the intended analysis. If an authoritative source contains the value, a controlled recovery can be appropriate; document the source and the mapping used. If no reliable value is available, possible responses include excluding the record from an analysis that requires the field or retaining the record with an explicit missing status when the method supports it. The right response depends on the field, the analysis, and the applicable data rules.
Do not treat blank as zero unless the data definition explicitly says that blank means zero. A missing loss amount, for instance, is not necessarily a zero loss. Nor should a missing classification be assigned to a default group simply to avoid losing a record. Such choices can distort group totals and can change the mix of risks represented in model inputs.
Worked Example: Excluding an Unresolved Record
A hypothetical ratemaking extract contains 40 policy-years and $24,000 of losses, including one unresolved record with 0.5 policy-years and $600 of losses. The data rule for this analysis requires both fields to be reliable; the record cannot be verified, so it is excluded from both totals. Calculate the resulting loss cost and the amounts excluded. The data are deterministic; no interest, mortality, or probability distribution assumption is used.
Method. Subtract the record's exposure and loss from their respective totals, then divide retained losses by retained exposure. This avoids removing a questionable record from only one side of the calculation.
Step 1: Retained exposure. \(40-0.5=39.5\) policy-years.
Step 2: Retained losses. \(\$24{,}000-\$600=\$23{,}400\).
Step 3: Recalculate loss cost.
The excluded amounts are $600 of losses and 0.5 policy-years. The result is checked by multiplying the retained loss cost before rounding by 39.5, which returns $23,400. Round only the final loss cost to the nearest cent. The common mistake is to report $600 per policy-year: that is the excluded record's own loss cost, not the loss cost of the retained portfolio.
Why Excluding Both Sides Changes the Result
Loss cost is a ratio of aggregate losses to aggregate exposure, not an average of record-level loss costs unless the averaging is exposure-weighted. Let the original totals be \(L\) losses and \(E\) exposure, and let an excluded record contribute \(l\) losses and \(e\) exposure, with \(0 \le e < E\). The new loss cost is \((L-l)/(E-e)\). Its change from the original ratio is:
This expression shows why a correction's effect depends on both the loss and exposure removed. If the record's loss cost \(l/e\) is above the original \(L/E\), removing it tends to reduce the portfolio loss cost; if it is below, removing it tends to increase the result, assuming positive exposure. The formula does not apply when the record's exposure is zero as a record-level ratio, and a missing or unreliable exposure should not be treated as a known \(e\).
Duplicates: Match the Underlying Transaction
A duplicate check should use fields that identify the underlying unit, such as a source transaction identifier, policy-period key, claim number, and transaction sequence, as appropriate to the data. The key should reflect how the source system distinguishes legitimate records. Two claims on one policy are not duplicates merely because the policy number matches, and an original payment plus a reversal are not duplicate payments merely because their amounts relate.
Once a true duplicate is confirmed, remove only the repeated representation of the observation. Preserve the retained record, identify the duplicate key, and document why the repeated row is not a separate exposure or transaction. Compare totals before and after removal so the impact is visible.
Worked Example: Removing a Confirmed Duplicate Policy-Period Row
A hypothetical extract reports 80 policy-years and $52,000 of losses. Review of a policy-period key and the source transaction log confirms that one repeated row is a duplicate; that row contributes 1 policy-year and $900 of losses. Remove one copy, retaining the verified original. Calculate the corrected totals and loss cost. No interest, mortality, or probability distribution assumption is used.
Method. Deduplicate by the confirmed policy-period key and source evidence, then recompute the aggregate ratio. Do not infer duplication from the fact that two rows happen to have the same dollar amount.
Step 1: Correct exposure. \(80-1=79\) policy-years.
Step 2: Correct losses. \(\$52{,}000-\$900=\$51{,}100\).
Step 3: Correct loss cost. \(\$51{,}100/79=\$646.8354\ldots\), or $646.84 per policy-year to the nearest cent.
Before correction the ratio was \(\$52{,}000/80=\$650.00\) per policy-year. The corrected ratio is lower, consistent with removing a row whose loss cost was \(\$900/1=\$900\) per policy-year, above the original portfolio ratio. The common mistake is to subtract the duplicate's $900 of losses but leave its 1 policy-year in the denominator; that would mix inconsistent populations.
Implausible Values: Investigate Before Changing
A value can be flagged because it violates a hard data constraint, such as earned exposure greater than the permitted maximum for a defined annual policy record. Other flags are soft checks: an unusually large loss or an uncommon classification may be valid and should not be capped or replaced merely because it is rare. A plausible range is meaningful only in relation to the field definition, unit, time period, and product.
When evidence confirms a transcription or transformation error, correct the value from the source and record both the original and corrected value. When the value remains unverified, retain it as an unresolved exception or exclude it according to a pre-established rule. Do not winsorize, cap, or substitute an extreme observation unless that treatment is explicitly justified and governed for the analysis.
Worked Example: Correcting an Exposure Value From Source Evidence
A hypothetical annual-policy file has 40 policy-years and $24,000 of losses. One row is recorded as 1.20 policy-years, exceeding the stated maximum of 1.00 for this annual-policy field. The source policy record confirms the correct exposure is 0.20 policy-years; the losses of $900 on that row are verified. Recalculate the portfolio exposure and loss cost. Treat the data as deterministic, with no interest, mortality, or probability distribution assumption.
Method. Correct only the field supported by source evidence. Since the loss amount is verified, retain it; change exposure by the difference between the confirmed and recorded values.
Step 1: Exposure adjustment. The recorded value exceeds the confirmed value by \(1.20-0.20=1.00\) policy-year. Corrected total exposure is \(40-1.00=39.00\) policy-years.
Step 2: Loss total. No loss adjustment is supported, so losses remain $24,000.
Step 3: Corrected loss cost. \(\$24{,}000/39.00=\$615.3846\ldots\), or $615.38 per policy-year to the nearest cent.
As a check, the original ratio was \(\$24{,}000/40=\$600.00\). Correcting exposure downward while retaining losses raises the ratio. The common mistake is to delete the entire row, including its verified $900 loss, when the evidence supports correcting only exposure. If the source had not established 0.20, the value should remain unresolved or be handled by an explicit exclusion rule, not guessed.
Documenting the Decision and Its Model Effect
A useful exception log records enough detail to connect the data issue to its treatment and impact. At minimum, include the record key, affected field, original value, rule or trigger, source evidence reviewed, disposition, corrected value if any, and reviewer or approval status. Also record which downstream data set or analysis is affected and the before-and-after counts or totals relevant to that decision.
The log should distinguish an actual correction from an exclusion and from a flag that remains unresolved. This matters because different dispositions have different consequences: correcting a classification can move a record between rating groups, while excluding a record can change both the population and its aggregate results. Preserve the original input and the transformation so the effect can be traced.
| Issue | Evidence-based response | Effect to check |
|---|---|---|
| Missing field | Recover from an authoritative source, retain as explicitly missing where supported, or exclude under a defined rule | Records and exposure/loss totals included in the affected analysis |
| Duplicate record | Confirm the underlying observation or transaction is repeated, then remove the repeated representation | Duplicate count and relevant exposure, loss, or transaction totals |
| Implausible value | Verify against field definitions and source evidence; correct only when supported | Changed field values, affected classifications, and downstream totals |
A final check compares the applied data change with the recorded disposition. If the log says that a 1.00 policy-year excess was corrected, the adjusted exposure total should reflect that exact difference. If a claim transaction was removed, the loss movement should equal the transaction amount, unless another documented adjustment also occurred. This is a targeted control, not a substitute for the broader reconciliation of exposure and loss totals.
Common Mistakes
- Replacing missing with zero by default. Zero is a substantive value, not a neutral placeholder; use it only when the field definition supports that interpretation.
- Calling similar rows duplicates. Confirm that they describe the same underlying observation or transaction before removing one.
- Deleting an entire record when one field is wrong. Correct only the field supported by evidence when the rest of the record is valid.
- Removing losses but not their associated exposure, or vice versa. Apply the documented scope consistently and recalculate all affected quantities.
- Automatically capping unusual values. Unusual does not mean erroneous; investigate against definitions and sources first.
- Failing to measure the effect. Record what changed in the analysis inputs, not just that an exception was reviewed.
Check Your Understanding
For each hypothetical case, apply the stated rule and calculate the requested result. Round money-based loss costs to the nearest cent.
- A file has 32 policy-years and $19,200 of losses, including an unresolved record with 0.4 policy-years and $320 of losses. The rule requires excluding that record from both totals. Find the retained loss cost and the excluded amounts. (Answer: 31.6 policy-years and $18,880 retained; $597.47 per policy-year; exclude 0.4 policy-years and $320.)
- A confirmed duplicate payment of $400 is included in losses of $18,900 for 30 policy-years. Remove the extra payment only. What is the corrected loss cost? (Answer: $18,500 losses; $616.67 per policy-year.)
- A portfolio has $12,000 of verified losses and 25 policy-years, including one annual-policy row recorded as 1.5 exposure. Source evidence confirms its exposure is 0.5. Correct exposure only and find the loss cost. (Answer: 24 policy-years; $500.00 per policy-year.)
- Totals of 52 policy-years and $40,300 of losses include a confirmed duplicated policy-period row contributing 2 policy-years and $1,100 of losses. Remove one duplicate and calculate the corrected loss cost. (Answer: 50 policy-years and $39,200; $784.00 per policy-year.)
- A territory analysis has 44 policy-years and $28,600 of losses, including 12 records with missing territory that contribute 6 policy-years and $4,200 of losses. The analysis rule excludes these records from the territory analysis. Find the retained territory-analysis loss cost and state what must be logged. (Answer: 38 policy-years and $24,400; $642.11 per policy-year; log the affected records, missing territory, exclusion rule, and excluded exposure and losses.)