Why Data Grain Comes First
Before fitting a ratemaking model, an actuary needs to know what one row in each source table represents. A table may have one row per policy term, one row per coverage within a term, one row per claim, or one row per financial transaction associated with a claim. These are different grains. If the grain is misunderstood, a join can duplicate records, a target can be attached to the wrong unit, or information from the wrong time period can be used.
The observation unit is the entity represented by one row in the modeling data. It depends on the target: a target defined for a policy term calls for one observation per policy term, while a target defined for a claim calls for one observation per claim. Choosing a convenient source table does not determine the correct unit; the modeling question does.
These labels must be interpreted with the data system's definitions in mind. For example, a claim file may involve more than one coverage, and a system may record multiple transactions for one claim. Therefore, a claim identifier alone may not uniquely identify a coverage-level record, and a policy identifier alone may not uniquely identify a policy-term record.
Identify the Unit Before Choosing or Joining Tables
A reliable starting point is to write the intended unit in plain language, then identify the fields that uniquely identify one unit. For policy-term records, this usually requires a policy identifier and a term identifier or term dates. A coverage-level unit generally also needs a coverage identifier. Claim and transaction records need their own identifiers, with the parent identifiers retained so they can be linked to the relevant policy and coverage.
| Record grain | One row represents | Typical identifying fields |
|---|---|---|
| Policy term | One policy for one specified term | Policy ID and term ID or term dates |
| Coverage | One coverage within one policy term | Policy ID, term ID, and coverage ID |
| Claim | One claim under the system's claim rules | Claim ID, often with policy and coverage references |
| Transaction | One recorded event associated with a claim | Transaction ID and claim ID |
A field is not a reliable key just because it appears unique in a small sample. Check uniqueness at the intended grain across the full data set, and check that linked records use compatible identifiers. Where identifiers can be reused, combine them with the relevant policy, term, coverage, or system fields.
The target determines which grain is appropriate. A policy-term target calls for a policy-term observation; a coverage-specific target calls for a coverage observation; a claim-level target calls for one observation per claim. A transaction-level target instead treats each transaction as a row, but that is appropriate only when the outcome itself is defined for transactions. The choice does not, by itself, specify how to construct a frequency or severity response.
Worked Example: One Policy Term, Several Coverage Rows
A hypothetical insurer has a policy-term table with one row for policy P501, term T7. A related coverage table has three rows: P501/T7/C-A, P501/T7/C-B, and P501/T7/C-C. A proposed modeling target is defined once for each policy term. Determine the correct observation count and explain what happens if the tables are joined. This is a deterministic record-design problem; no interest rate, mortality assumption, probability distribution, or distribution parameters are used.
Step 1: Identify the target unit. The target is defined for each policy term, so one observation represents P501 for T7. The relevant identifying fields are policy ID P501 and term ID T7.
Step 2: Count the source rows at each grain. The policy-term table contains 1 row for P501/T7. The coverage table contains 3 rows for that same policy term, one for each of C-A, C-B, and C-C.
Step 3: Check the joined grain. Joining on both policy ID and term ID attaches each of the 3 matching coverage rows to the single policy-term row. The joined table therefore has 3 rows for P501/T7, not 1. The policy-term attributes are repeated on all 3 joined rows.
Step 4: State the result at the target grain. The correct number of policy-term observations is 1. If the target instead were defined separately for each coverage, 3 observations would be appropriate. The number of joined rows cannot decide between those targets; the target definition does.
Check. There are 3 joined rows because there are 3 matching coverage records, but all 3 share the same policy-term key P501/T7. Counting distinct policy-term keys gives 1, as required. The common mistake is to treat the 3 joined rows as 3 policy terms.
Joins Must Respect Grain
A join matches rows using specified keys. When one row on one side matches several rows on the other, the one row is repeated in the joined result. This is not necessarily a database error: it is the expected result of combining different grains. It becomes a modeling error when the repeated rows are mistaken for distinct observations at a coarser grain.
The basic row-multiplication effect can be derived directly. Suppose one policy-term row matches \(a\) coverage rows and \(b\) claim rows. If the coverage rows and claim rows are joined together through that policy term, each coverage row can pair with each claim row. The resulting number of combinations is \(a \times b\), unless additional keys restrict the matches. Thus, 3 coverages and 2 claims can produce \(3 \times 2 = 6\) joined rows. A row count after the join is not automatically a count of policy terms, coverages, or claims.
This relationship explains why matching only on a broad parent key can create a many-to-many join. To avoid that, identify the intended grain on both sides and use the keys that identify the relationship. If a target is at policy-term grain, first ensure that every attached data item is represented once per policy term, or deliberately summarize lower-grain data to that unit using a defined rule. If the target is at a lower grain, retain the identifiers required to keep those lower-grain records distinct.
A second alignment requirement is time. A policy identifier may appear in several terms, and coverage can differ between terms. A claim or other record must be connected to the relevant policy and coverage for the time period that applies to the record. Using the current policy attributes for a historical record can silently attach the wrong information.
Worked Example: Claim Rows and Transaction Rows
A hypothetical claim table contains claim IDs CL71 and CL72. A transaction table contains three transactions linked to CL71 and one transaction linked to CL72. The requested modeling unit is one claim. Determine how many observations there should be and why the transaction table cannot be counted directly. This is a deterministic record-design problem; no interest rate, mortality assumption, probability distribution, or distribution parameters are used.
Step 1: State the grain of each table. The claim table has 2 rows, one per claim ID. The transaction table has 4 rows: 3 with claim ID CL71 and 1 with claim ID CL72.
Step 2: Identify the target unit. The target is defined once per claim, so the observation unit is the claim. CL71 and CL72 are two distinct claim identifiers.
Step 3: Determine the correct observation count. There are 2 claim observations. The 4 transaction rows describe events associated with those claims; they do not identify 4 claims.
Step 4: Examine the join. Joining the claim table to the transaction table on claim ID produces 3 rows for CL71 and 1 row for CL72, for 4 joined rows total. CL71's claim-level fields are repeated three times. If the modeling target remains claim-level, those four rows cannot be treated as four independent claim observations.
Check. Counting distinct claim IDs in the joined table gives 2: CL71 and CL72. That agrees with the 2 rows in the claim table. The common mistake is to count transaction rows as claim rows, thereby turning one claim with three transactions into three apparent claims.
Align Records by Policy, Coverage, and Time
A valid match requires both the right identifiers and the right time relationship. A general policy ID may connect many terms; a term ID may connect several coverages; and a claim may refer to a coverage that is not present in every term. Before attaching attributes, verify the relationship at the intended grain. For time-dependent data, specify which date controls the match, such as the date of an event, and define how period boundaries are treated.
The following example uses half-open policy periods: the start date is included and the end date is excluded. This is a stated convention for the hypothetical data, not a universal rule. A production analysis must use the applicable contract and system conventions.
Worked Example: Matching a Claim to the Applicable Term and Coverage
A hypothetical policy P88 has term T1 covering the interval [January 1, July 1) and term T2 covering [July 1, January 1 of the next year). Coverage C-B is recorded in T1 but not T2; coverage C-A is recorded in both terms. Three claim records have these occurrence dates and coverage references: K1, June 30, C-B; K2, July 1, C-B; K3, December 31, C-A. Assign each claim to a policy term and decide whether its coverage is present in that term. This is a deterministic record-alignment problem; no interest rate, mortality assumption, probability distribution, or distribution parameters are used.
Step 1: Apply the stated date intervals. June 30 is on or after January 1 and before July 1, so it belongs to T1. July 1 is excluded from T1 and included in T2. December 31 is also within T2.
Step 2: Check each coverage within the assigned term. K1 refers to C-B in T1, where C-B is recorded, so its policy-term and coverage match is valid. K2 refers to C-B in T2, where C-B is not recorded, so the date assigns it to T2 but the coverage reference does not match that term. K3 refers to C-A in T2, where C-A is recorded, so its match is valid.
Step 3: Summarize the alignment. Two records have both a matching term and a matching coverage: K1/T1/C-B and K3/T2/C-A. One record, K2, has a term assignment but no matching C-B coverage record in that term.
Check. The result is 2 complete matches and 1 coverage mismatch. The date boundary matters: assigning July 1 to T1 would contradict the stated half-open intervals. The mismatch should be investigated rather than silently resolved by using a coverage row from T1.
Common Mistakes and a Practical Review
- Equating rows with units. A row count is a count of records at that table's grain, not necessarily a count of policies, claims, or observations.
- Joining on policy ID alone. If a policy has multiple terms, this can connect a record to the wrong term or to several terms.
- Ignoring coverage identifiers. A policy-term match does not establish that the particular coverage is present in that term.
- Using a transaction as a claim. Multiple transactions can belong to one claim, so transaction rows do not automatically represent distinct claims.
- Ignoring time boundaries. State which date governs the match and whether boundary dates belong to the interval before or after them.
- Assuming a successful join proves a correct match. A join can return rows even when its keys are too broad; check uniqueness, unmatched records, and repeated identifiers at the intended grain.
State what one final modeling row is intended to represent.
Determine whether each source row represents a policy term, coverage, claim, transaction, or another unit.
Use identifiers sufficient to distinguish the relevant policy, term, coverage, and claim, and state the date rule for matching.
Look for duplicated units, unexpected many-to-many matches, and records that fail to match.
Getting the grain right is a prerequisite for interpreting any later modeling data. A duplicate row may look like additional experience, while a missing or mismatched row may omit relevant information. The essential discipline is to decide what one observation means before counting, joining, or modeling records. This data-design foundation is aligned with the CAS Exam 8 (Advanced Ratemaking) syllabus.
Check Your Understanding
For each problem, identify the appropriate grain or alignment and check the final answer against the stated records.
- A table has one row per policy ID and term ID. Policy Q20 has term rows T1 and T2. How many policy-term observations for Q20 are represented? (Answer: 2.)
- A coverage table has two rows for policy-term R4/T9, one for C-A and one for C-B. A target is defined once per policy term. How many target observations does R4/T9 represent, even if both coverage rows are joined to it? (Answer: 1.)
- One claim ID has five linked transaction rows; a second claim ID has two. If the target is defined once per claim, how many observations are represented? (Answer: 2.)
- A policy has term U1 on [March 1, September 1) and term U2 on [September 1, March 1 of the next year). Under this convention, which term contains an event dated September 1? (Answer: U2.)
- One policy-term row matches 4 coverage rows and 3 claim rows when the two lower-grain tables are joined only through the policy-term key. If every coverage row can pair with every claim row, how many joined combinations result for that policy term? (Answer: 12.)