Why Duplicate Records Matter
Exposure calculations depend on counting each covered risk for the time it was covered, using the exposure basis appropriate to that coverage. A copied row can make the same risk appear covered twice, overstating exposure. But a policy can also cover multiple vehicles, buildings, or other items; deleting records merely because the policy number repeats can understate exposure. The central task is to determine what each record represents before deciding whether two records are duplicates.
This tutorial uses “duplicate” to mean a second data record that represents the same underlying covered exposure for the same coverage interval, rather than a separate risk or a distinct portion of coverage. A repeated policy number, by itself, is not enough to establish duplication. The earlier material on stable identifiers and record linkage is useful here: identifiers help connect records, while the exposure meaning of the connected records still has to be checked.
Decide What Makes a Record the Same
A comparison needs a business key: the combination of fields that identifies the exposure represented by a record. A possible coverage-interval key may include the policy identifier, policy term, coverage, insured item or location, and interval start and end dates. The appropriate fields depend on the product and exposure basis. If coverage limits, layers, or other attributes define separate exposure units for the analysis, those attributes may also be needed.
A database row number or import timestamp is often a technical identifier, not part of the exposure’s business identity. Two rows with different technical IDs can still describe the same exposure. Conversely, two rows with the same policy number may be legitimate separate exposures if they describe different vehicles, locations, or other covered items.
For interval comparisons, it is helpful to specify whether an interval includes its end date. In this tutorial, intervals are treated as half-open: the start is included and the end is excluded. Thus \([a,b)\) followed by \([b,c)\) is adjacent, not overlapping. A practical data process must use a consistent date convention; otherwise, a boundary date can be counted twice or omitted.
| Record comparison | What it may mean | What to check |
|---|---|---|
| Same policy and term; different covered items | Legitimate multiple exposures | Whether the items are distinct under the exposure basis |
| Same policy, coverage, item, and interval | Possible duplicate | Whether any field or business rule makes the records distinct |
| Same item and coverage; adjacent intervals | Potentially valid segmentation | Whether intervals meet at a boundary without a gap or overlap |
| Same item and coverage; overlapping intervals | Possible duplicate or distinct coverage record | Whether the overlap reflects duplicate data or separately defined coverage |
An exact field match is a strong signal to investigate, not a complete decision rule. Some records are repeated because a system stores multiple versions or transactions. A repeated row in an extract may not mean that the underlying policy provided twice the coverage. Likewise, two records with the same dates might represent different covered items or coverage components. The exposure meaning, not just the visual similarity of rows, determines whether the records should both contribute.
How Duplication Changes Exposure
Suppose each record \(j\) contributes exposure \(e_j\), measured on the chosen basis. The total is the sum of the contributions of the records included in the calculation. If a row representing exposure \(e\) is copied and both copies are summed, the reported amount includes \(e+e=2e\), rather than the correct \(e\). The excess is \(e\), so an isolated exact duplicate doubles that exposure contribution.
This result follows directly from the additive calculation: the duplicate contributes a second copy of the same exposure amount, even though no second risk or covered time exists. If the full exposure total is \(T\), and the duplicated amount \(e\) is part of that total, the total becomes \(T+e\). The percentage overstatement of the total is \(e/T\), not necessarily 100%; 100% applies to the duplicated contribution itself.
By contrast, if two records represent distinct exposure units, their contributions are added. For example, two vehicles covered for a full year each contribute one vehicle-year when vehicle-years are the selected basis. Repeated policy identifiers do not make those two vehicle exposures identical.
Worked Example: A Repeated Policy-Term Record
A hypothetical auto policy has a term from January 1, 2025, to January 1, 2026. The exposure table contains two rows, each for policy P-417, bodily injury coverage, vehicle V8, and that same full-term interval. The rows differ only in their import batch IDs. The selected exposure basis is vehicle-years, prorated by covered months over 12. Determine whether both rows should contribute and quantify the effect of counting both.
Method. Compare the business-key fields that identify the covered exposure: policy, term, coverage, vehicle, and interval. The import batch ID identifies how a row entered the table, not a second covered vehicle or time interval.
For either row, the full-term contribution is \(12/12=1\) vehicle-year. Because the business-key fields match, and there is no evidence of a second vehicle or separate coverage interval, the second row is a duplicate representation of the same exposure.
The excess is \(2-1=1\) vehicle-year, equal to 100% of the correct contribution. Retain one contribution for this exposure. Common mistake avoided: treating different import IDs as proof of distinct exposure. They are not; the insured item and covered interval are the same. The check is that the corrected amount agrees with the single full-term vehicle exposure described in the facts.
Legitimate Multiplicity and Interval Checks
A repeated policy-term record is not automatically a duplicate. If one policy insures several distinct exposure units, each may contribute exposure under the chosen basis. The unit matters: vehicle-years, building-years, and policy-years do not count the same thing. Before summing records, confirm that the fields distinguishing units correspond to real separate risks, rather than technical row IDs or duplicate descriptions.
Intervals require a separate check. Identical start and end dates for the same coverage and item may indicate a duplicate, but overlapping or adjacent intervals need interpretation. Adjacent intervals can form a continuous period of coverage. Overlapping intervals can result from repeated data, or can arise when the records represent distinct coverage components. Do not infer which explanation applies from the dates alone.
Worked Example: Two Vehicles on One Policy
A hypothetical commercial auto policy covers vehicles V1 and V2 for the same 12-month term. The records have the same policy identifier and collision coverage, but one record identifies V1 and the other identifies V2. Each vehicle is covered for all 12 months. The exposure basis is vehicle-years. Determine the valid total and explain whether the repeated policy and coverage fields justify removing a row.
Method. Compare the insured exposure unit as well as the policy and coverage. Here, the vehicle identifier differs, so the rows describe two distinct vehicles.
Vehicle V1 contributes \(12/12=1\) vehicle-year. Vehicle V2 also contributes \(12/12=1\) vehicle-year. Add the distinct units:
Both records belong in the calculation. Removing one because the policy identifier and coverage are repeated would understate exposure by 1 vehicle-year, or 50% of the correct total. Common mistake avoided: using policy and coverage alone as the key. For a vehicle-year basis, the vehicle is essential to distinguishing separate units. The check is that the total represents two vehicles, each covered for one full year.
Worked Example: Adjacent Intervals and a Copied Segment
A hypothetical property policy covers one building under a specified coverage from January 1 through the following January 1. Records use half-open intervals and monthly proration. The table contains a January 1–July 1 interval, a July 1–following January 1 interval, and a second copy of the January 1–July 1 interval with a different row ID. Determine the exposure if every row is summed and the exposure after removing the duplicate representation.
Method. Compare policy, coverage, building, and interval. The first two intervals are adjacent: the first ends exactly when the second begins. Together they represent the full year. The third row repeats the first interval for the same building and coverage.
The first interval is six months, so it contributes \(6/12=0.5\) building-years. The second interval also contributes \(6/12=0.5\) building-years. The copied first interval contributes another \(6/12=0.5\) if it is mistakenly included.
Count the January–July segment once and the July–January segment once:
The adjacent intervals do not overlap under the stated half-open convention, and they cover 12 months in total. The copied segment adds an unjustified 0.5 building-years. Common mistake avoided: treating every row with a different row ID as unique exposure, or treating adjacent intervals as overlapping. The check is that the two distinct segments together represent one full year for one building.
A Practical Review Process
A consistent process makes the decision reproducible and reduces both overcounting and accidental deletion of legitimate exposure. The review should preserve enough information to explain why records were treated as duplicates or distinct. It should not rely on a single broad rule such as “keep one row per policy.”
Confirm whether the calculation counts policy terms, vehicles, buildings, payroll, or another basis. This determines which differences can represent separate exposure.
List the fields needed to identify the policy, term, coverage, insured item, and relevant interval. Include other distinguishing fields when they define separate coverage units.
Look for exact matches and near-matches. Different technical IDs do not establish different exposure, while a meaningful difference in item or coverage may establish separate exposure.
Determine whether intervals are identical, adjacent, overlapping, or separated by a gap, using the stated endpoint convention. Investigate overlaps rather than automatically counting or deleting them.
Compute exposure under the supported interpretation, quantify any duplicate contribution, and record why the records were treated as one exposure or as separate exposures.
Common Traps
- Deduplicating on policy number alone. One policy may cover multiple vehicles or locations. Check the exposure unit before removing a row.
- Keeping every row with a different database ID. Technical identifiers can differ even when the underlying coverage record is repeated.
- Assuming matching dates prove duplication. Identical intervals can describe distinct insured items or separately defined coverage. Check the full business key.
- Assuming overlapping dates always mean duplicate exposure. Overlap is a reason to investigate, not a conclusion. The records may represent distinct coverage components or may repeat the same risk.
- Ignoring the interval convention. Without a consistent rule for endpoints, adjacent intervals may be mistakenly treated as overlapping or may leave a boundary uncovered in the data.
A duplicate review is ultimately an exposure-definition problem as well as a data-matching problem. First establish what counts as one unit of exposure; then identify which records describe that unit and its covered time. This avoids both double counting and the opposite error of discarding valid additional risks.
Check Your Understanding
For each problem, identify the relevant exposure units or intervals and state the resulting exposure. Assume monthly proration over 12 months where a year is specified.
- A hypothetical policy covers one insured item for four months. The same policy, coverage, item, and interval appear in two rows with different load IDs. What is the correct exposure, and what would the unfiltered sum be? (Answer: Correct exposure \(4/12=0.3333\) exposure-years; unfiltered sum \(8/12=0.6667\) exposure-years.)
- A hypothetical policy covers two distinct vehicles for nine months each, with one record per vehicle and no repeated rows. The basis is vehicle-years. What is the total? (Answer: \(9/12+9/12=1.5\) vehicle-years.)
- For one hypothetical building and coverage, records describe January–April and April–October, and a second copy of the January–April row is present. Under half-open intervals, what is the exposure after duplicate removal? (Answer: \(3/12+6/12=0.75\) building-years.)
- A hypothetical policy has two records with the same policy, coverage, and dates, but one is for Location A and the other is for Location B. Each location is covered for a full year, and the basis is location-years. Should one be removed as a duplicate? Give the total. (Answer: No; the locations are distinct exposure units, for a total of 2 location-years.)
- Two records have the same policy, item, coverage label, and interval, but their stated coverage limits differ. Is it safe to remove one solely because the other fields match? (Answer: No; investigate whether the limits represent distinct coverage components or a repeated record before deciding. The matching fields alone do not establish duplicate exposure.)