Joining Policy And Claim Data Without Duplication

Why Join Design Matters

Ratemaking data often come from separate policy and claim records. The policy data describe the exposure being priced; the claim data describe losses or counts associated with that exposure. Joining them makes it possible to analyze both together, but the result is only reliable if each record is matched at the right level. A join can silently repeat a claim, an exposure, or both, even when every individual source record is valid.

The first question is not “Which columns should be combined?” It is what does one row represent in each table? A policy table might contain one row per policy term, while a claim table contains one row per claim. If one term has several claims, matching those tables naturally produces several rows for that term. That is appropriate for claim-level analysis, but it repeats the term’s exposure on each matched row. Summing that repeated exposure as though it were still one row per term will overstate the denominator.

Definition: The grain of a table is the unit represented by one row, such as one policy term, one claim, or one coverage segment. A join key is the field, or combination of fields, used to match rows across tables. A join preserves the intended data meaning only when its key identifies the correct match and the resulting grain is understood.

A policy number alone may not identify a unique term: the same policy can renew, or it can have multiple terms in the data. A suitable key might therefore be a policy-term identifier, or a combination such as policy number and term start date. Use the key that identifies the actual insured term associated with each claim, rather than the shortest available field name.

What a One-to-Many Join Produces

Suppose the policy-term table has exactly one row for each term $j$, with exposure $e_j$, and the claim table has one row for each claim. Let $m_j$ be the number of claim rows matched to term $j$. With a left join from policy terms to claims, a term with claims produces one joined row per matched claim. A term with no claims still produces one row, with missing claim fields.

\text{claim-bearing rows}=\sum_j m_j,\qquad \text{all left-join rows}=\sum_j \max(1,m_j)

These are different totals whenever at least one term has no claims. The first counts rows that contain a claim; the second counts every physical row returned by the left join, including the null-claim row for each zero-claim term. For an inner join, which excludes unmatched policy terms, the joined row count is $\sum_j m_j$, assuming the key matches each claim to exactly one policy-term row.

When a policy term with exposure $e_j$ matches $m_j$ claims, the join copies $e_j$ into each of those rows. A zero-claim term still contributes one exposure-bearing row to a left join. Thus the sum of the exposure column after a left join is $\sum_j e_j\max(1,m_j)$, not generally the original total $\sum_j e_j$. The claim-bearing row count and the left-join row count are useful diagnostics, but neither makes a repeated exposure safe to sum.

Worked Example: A Left Join With a Zero-Claim Term

A hypothetical insurer has three policy terms. Term A has exposure 1.2 and two claims of $1,000 and $500. Term B has exposure 0.5 and no claims. Term C has exposure 0.8 and one claim of $700. The policy table has one row per term, and the claim table has one row per claim. Use a left join from terms to claims. Find the joined row count, claim count, total loss, and exposure total before and after joining. Then calculate the aggregate claim frequency using the correct exposure.

Method. Match using a unique policy-term key. Count claim IDs for claim count, sum the loss once per claim, and retain the original one-row-per-term exposure total as the frequency denominator.

Step 1: Determine rows by term. Term A matches two claims, so it contributes two joined rows. Term B has no match, so the left join contributes one row with missing claim fields. Term C matches one claim, so it contributes one row. The physical joined row count is $2+1+1=4$. Of those, three rows contain a claim: $2+0+1=3$.

Step 2: Calculate claim count and loss. There are $2+0+1=3$ claim IDs. Total loss is $$1{,}000+$500+$700=$2{,}200$. Each claim appears once because each claim matches exactly one term row.

Step 3: Compare exposure totals. Before joining, exposure is $1.2+0.5+0.8=2.5$. After the join, the exposure values on the four rows are $1.2, 1.2, 0.5, 0.8$, which sum to $3.7$. Term A’s exposure is repeated because it has two claims; Term B’s exposure remains present on its null-claim row.

Step 4: Calculate frequency. The correct aggregate frequency is $3/2.5=1.2$ claims per exposure unit. Dividing by the post-join exposure sum gives $3/3.7=0.8108$ claims per exposure unit, which is not the correct frequency for the original portfolio.

Check. The left join has four physical rows but only three claim-bearing rows; the claim count and loss agree with the three source claims and their $2,200 total. The common mistake this example avoids is treating every left-join row as a claim or summing exposure after it has been repeated.

Choose a Key That Identifies the Correct Term

A join can produce plausible-looking rows and still be wrong if its key is too broad. If a policy number appears on multiple terms, matching claims to that number alone can connect each claim to every term for that policy. This creates duplicated claim rows and can attach claims to the wrong exposure period. The remedy is to use a key that uniquely identifies the term and to confirm that each claim matches exactly one such term.

A useful check is to examine the policy table’s key before joining. If the intended key is policy number plus term date, verify that this combination is unique in the policy table. Then check whether each claim has one matching term. Unmatched claims and ambiguous matches should be investigated; they should not be silently resolved by counting the repeated rows.

Worked Example: A Policy Number Is Not a Term Key

A hypothetical commercial policy, number 481, has two terms in a data extract: a 2024 term with exposure 0.4 and a 2025 term with exposure 0.6. One claim of $900 is assigned to the 2024 term, and one claim of $1,500 is assigned to the 2025 term. Calculate the correct total exposure, claim count, and loss. Then determine what happens if the tables are joined using policy number alone.

Method. Use the policy-term identifier, not policy number alone. The correct matching key distinguishes the 2024 and 2025 terms.

Step 1: Calculate the correct totals. Total exposure is $0.4+0.6=1.0$. There are two claims, and total loss is $$900+$1{,}500=$2{,}400$. With the term-specific key, each claim matches one term, producing two claim-bearing rows.

Step 2: Trace the policy-number-only join. Both term rows have policy number 481, and both claim rows have policy number 481. Matching on that field alone pairs each of the two term rows with each of the two claims. The result has $2\times2=4$ rows, rather than two correctly matched claim rows.

Step 3: Quantify the distortion. Each claim is now present twice, so counting claim rows gives 4 instead of 2. Summing the repeated losses gives $2($900+$1{,}500)=$4{,}800$, twice the correct loss. The exposure values are also repeated once for each claim: $2(0.4+0.6)=2.0$, twice the correct exposure.

Check. The correct totals are 1.0 exposure unit, 2 claims, and $2,400 loss. The policy-number-only join doubles all three totals in this example because the match is many-to-many. The common mistake is assuming that a familiar identifier must also be a unique join key.

Aggregate to Compatible Grains Before Joining

A second frequent source of row multiplication occurs when both tables have multiple rows per join key. For example, exposure may be stored by monthly segment while claims are stored by claim. If a term has two exposure segments and two claims, joining both raw tables on term ID creates four combinations. Each segment appears twice, and each claim appears twice.

The safe approach depends on the intended analysis. For term-level frequency or aggregate loss, first reduce exposure to one row per term by summing its segments, and reduce claim records to the required term-level measures, such as claim count and total loss. Then join the one-row-per-term summaries. For claim-level severity analysis, keep one row per claim, but attach term attributes without treating repeated term exposure as claim-level exposure. If a source contains multiple payment rows for one claim, aggregate payments to the claim before counting claim IDs or joining to other claim-level fields.

Aggregation must preserve the quantity needed for the analysis. Summing segment exposures to a term total is appropriate when those segments partition that term’s earned exposure. Summing claim losses to a term total is appropriate when each claim’s loss is included once on the chosen loss basis. Do not aggregate away distinctions that the target requires; first state the intended response and its unit.

Worked Example: Two Multirow Tables for One Term

A hypothetical policy term has two nonoverlapping exposure segments: 0.25 and 0.75 exposure units. It also has two claims, with losses of $400 and $600. Both source tables use the same term ID, but the exposure table has one row per segment and the claim table has one row per claim. Find the correct term-level exposure, claim count, and total loss, and show how a direct join distorts them.

Method. Aggregate each source to one row per term before joining: sum the exposure segments, and separately count claim IDs and sum claim losses.

Step 1: Aggregate exposure. The term exposure is $0.25+0.75=1.00$ exposure unit.

Step 2: Aggregate claims. Claim count is 2. Total loss is $$400+$600=$1{,}000$.

Step 3: Examine the direct raw-table join. Each of the two exposure segments matches both claims. The join therefore creates $2\times2=4$ rows. Each segment value appears twice, so the joined exposure sum is $2(0.25+0.75)=2.00$. Each claim appears twice, so counting rows with a claim ID gives 4, and the joined loss sum is $2($400+$600)=$2{,}000$.

Step 4: Compare after the safe aggregation. The term-level summaries join as one row containing exposure 1.00, claim count 2, and loss $1,000. These match the independently calculated source totals.

Check. The direct join inflated each measure by a factor of two; aggregation at the common term grain restores the correct values. The common mistake is to assume that a shared key guarantees one-to-one matching. A key can identify the same term on both sides while still matching multiple rows on both sides.

A Practical Audit Before Using Joined Data

A join should be treated as a data transformation that needs explicit checks, not as a neutral formatting step. The checks below make the intended grain and the effect of matching visible before the joined records become a ratemaking target.

State the grain of each source.
Write down what one row represents in the policy and claim tables. Identify whether either table has multiple records per planned join key.

Specify a unique matching key.
Use the fields needed to identify the correct policy term. Check that the policy-side key is unique if the intended relationship is many claims to one term.

Predict join cardinality.
For each policy term, determine whether it should match zero, one, or several claim rows. If both sides have multiple rows per key, aggregate to compatible grains or model the intended relationship explicitly.

Compare counts and totals before and after.
Check distinct claim IDs, total loss, and total exposure against their source-table totals. For a left join, distinguish claim-bearing rows from null-claim rows.

Investigate exceptions.
Review unmatched claims, duplicate policy keys, and unexpected changes in totals. Do not remove or duplicate records merely to force totals to agree; identify the data or key issue first.

A simple derivation explains the row-count check. Under the assumptions that the policy table has one row per term and every claim matches at most one term, term $j$ contributes $m_j$ claim-bearing rows. If $m_j=0$, a left join nevertheless retains one term row with missing claim fields. Thus its total contribution is $m_j$ when $m_j\geq1$, and 1 when $m_j=0$, which is exactly $\max(1,m_j)$. Summing over terms gives $\sum_j\max(1,m_j)$ physical left-join rows, while summing only claim-bearing rows gives $\sum_jm_j$. This distinction is why a zero-claim term must not be mistaken for an additional claim.

Common Mistakes and Their Corrections

Using the left-join row count as the claim count. A zero-claim term still contributes one row. Count nonmissing claim IDs, or count distinct claim IDs when the claim grain is intended to be unique.
Summing a repeated exposure column. In a one-to-many join, a term’s exposure appears once for each matched claim. Calculate exposure at its original grain or aggregate it to the intended analysis grain before joining.
Joining on an identifier that is not unique. A policy number can identify several terms. Use a term-specific key and test its uniqueness on the policy side.
Joining two raw one-to-many tables and assuming records stay unique. Multiple exposure rows and multiple claim rows for the same key create combinations. Aggregate each side to a compatible grain when that preserves the needed measures.
Checking only the final row count. A plausible number of rows does not prove that claim IDs or dollars were not duplicated. Compare distinct claim counts and totals to the source data as well.

Key takeaway: A join is safe only when its key, row grain, and resulting multiplicity are understood. Count claim IDs rather than physical left-join rows, keep exposure totals at the exposure grain, and verify that claim counts and losses have not been repeated.

Check Your Understanding

For each problem, identify the relevant grain and check whether the stated join preserves the quantities being measured.

A policy-term table has one row for each of three terms. They have 0, 1, and 3 claims, respectively. How many rows does a left join produce, and how many contain a claim? (Answer: 5 total rows; 4 claim-bearing rows.)
A term has exposure 0.6 and two claims. A left join repeats the exposure on both claim rows. What exposure total is obtained by summing those joined rows, and what is the correct term exposure? (Answer: 1.2 joined-row exposure; 0.6 correct term exposure.)
One policy number appears on three term rows and has two claims. If both tables are joined using only that policy number, how many rows result, assuming every claim row has that number? (Answer: 6 rows.)
A term has three monthly exposure segments of 0.2, 0.3, and 0.5, plus two claim records with losses of $250 and $750. State the correct term exposure, claim count, and total loss after aggregation to term. (Answer: 1.0 exposure unit, 2 claims, and $1,000 loss.)
A policy table has one row per term, but the claim extract has two payment rows for each of two claims. The payment amounts are $300 and $200 for the first claim, and $400 and $100 for the second. What should be done before joining if the target is one row per claim with total claim loss, and what are the resulting claim count and total loss? (Answer: Aggregate payments by claim ID before joining; 2 claims and $1,000 total loss.)

What You'll Learn