Why Audit the Table Before Modeling?
A modeling table can look tidy and still represent the wrong observation unit, count exposure twice, or include a predictor that was recorded only after the outcome occurred. Those defects can change both the apparent amount of risk in the data and the relationships a model is asked to learn. A pre-model audit is a set of explicit checks against the table’s intended structure, business rules, and data lineage—not a guarantee that every value is correct.
This tutorial uses a hypothetical renewal-risk dataset. Each row is intended to represent one policy for one policy year. The target is whether the policy had at least one claim during that year. The checks are aligned with the SOA Exam PA (Predictive Analytics) syllabus and focus on finding issues before fitting a model.
The key is valid only if it matches the chosen observation unit. Here, the intended design permits one record per policy per year, so policy ID plus year should be unique. If the source legitimately contains separate coverages or locations within a policy-year, those fields may need to be part of the key. Do not remove rows merely because a key repeats: first establish whether the repeated row is an error or reflects a different unit.
A Practical Audit Sequence
Start by writing down what one row means, what identifies it, which fields are outcomes, and when each predictor is available. Then check key uniqueness, field-level rules, grouped summaries, and consistency between the outcome and its definition. Keep a record of flagged rows and their resolution; a flag is evidence to investigate, not permission to alter data automatically.
For this case, one row is one policy-year and the key is policy ID plus policy year.
Count rows by key. Inspect repeated keys and compare the rows, rather than assuming every repeat is an exact copy.
Check plausible ranges, allowed values, and internal relationships using rules defined for this table.
Summarize exposure and outcomes by appropriate groups, then investigate unexpected totals or concentrations of flags.
Recalculate derived outcomes from their source definition and verify that proposed predictors existed at the prediction date.
In R, a grouped row count can be produced with dplyr. For example, use dat |> dplyr::count(policy_id, policy_year, name = "n_rows") |> dplyr::filter(n_rows > 1) to return repeated keys. The pipe passes the result of one operation to the next. A count of identical full rows is a different diagnostic: it can reveal exact repeated records, but it will not find two rows with the same key and conflicting values.
Duplicate Keys and Exposure Reconciliation
Suppose each valid policy-year contributes an exposure fraction \(e\), with one row per key. In this example, a policy covered for half the year contributes \(0.50\) exposure. If an identical row is accidentally repeated \(m\) times, the raw table contributes \(me\) instead of \(e\). The excess is \((m-1)e\). This explains why duplicate rows can inflate exposure as well as outcomes.
Here, \(j\) indexes keys that are repeated, \(m_j\) is the number of rows for key \(j\), and \(e_j\) is the exposure on each identical repeated row. The identity applies when the repeated rows have the same exposure. If their exposure values differ, compare the rows and source records; the simple excess formula no longer describes the discrepancy.
Worked Example: Find a Repeated Policy-Year
A hypothetical table has five rows. Exposure is the fraction of a year covered, and claim count is the number of claims reported for that policy-year. The intended key is policy ID plus year.
| Policy ID | Year | Exposure | Claim count |
|---|---|---|---|
| P01 | 2025 | 1.00 | 0 |
| P02 | 2025 | 0.50 | 1 |
| P02 | 2025 | 0.50 | 1 |
| P03 | 2025 | 1.00 | 0 |
| P04 | 2025 | 1.00 | 2 |
Method. Count rows per key, then compare the raw totals with totals after retaining one row per distinct key for diagnostic purposes. The R grouped count is dat |> dplyr::count(policy_id, year, name = "n_rows") |> dplyr::filter(n_rows > 1). It identifies P02 with two rows.
There are five raw rows but only four distinct keys. Raw exposure is \(1.00+0.50+0.50+1.00+1.00=4.00\). Retaining one row for each distinct key gives \(1.00+0.50+1.00+1.00=3.50\). The repeated row adds \(0.50\) exposure. Raw claim count is \(0+1+1+0+2=4\); one row per key gives \(0+1+0+2=3\), so the repeated row also adds one claim.
Check. P02’s two rows match in every displayed field, so they are exact duplicates in this example. The reconciliation agrees with the identity: \((2-1)\times0.50=0.50\) excess exposure. The common mistake is to delete every repeated key without checking whether its rows represent legitimate records; here, source confirmation is still needed before correction.
Exposure Rules and Grouped Checks
An exposure rule must be stated for the table being audited. For the next example, the dataset includes only active policy-years with positive earned coverage, and exposure is recorded as a fraction of one year. Therefore the allowed range is \(0 < e \le 1\). This is not a universal rule for all actuarial datasets: a different exposure convention or observation unit could allow other values.
A useful grouped summary reports the number of rows, total exposure, and total claims by a meaningful grouping field. In dplyr, a pattern is dat |> dplyr::group_by(product) |> dplyr::summarise(rows = dplyr::n(), exposure = sum(exposure), claims = sum(claim_count)). Grouping helps locate where an anomaly occurs; it does not establish that a group’s values are valid.
Worked Example: Flag Exposure Outside the Stated Range
A hypothetical portfolio table contains the following distinct policy-years. Policies in product A and B are all expected to have positive exposure no greater than one year.
| Policy ID | Product | Exposure |
|---|---|---|
| Q01 | A | 0.50 |
| Q02 | A | 1.00 |
| Q03 | B | 1.12 |
| Q04 | B | -0.10 |
| Q05 | B | 0.00 |
Method. Test each row against \(0 < e \le 1\). In R, the condition can be written as dplyr::filter(dat, exposure <= 0 | exposure > 1). This returns Q03, Q04, and Q05: \(1.12\) is above the maximum, \(-0.10\) is negative, and \(0.00\) violates the strictly positive lower bound.
The valid exposure total for A is \(0.50+1.00=1.50\), with zero flagged rows. The raw total for B is \(1.12+(-0.10)+0.00=1.02\), with three flagged rows. Across all rows, the raw total is \(1.50+1.02=2.52\). The three invalid values contribute \(1.12-0.10+0.00=1.02\); therefore the exposure from valid rows is \(1.50\).
Check. There are two valid rows and three flagged rows, matching the five records. Do not replace an invalid exposure with a convenient value, or silently discard it. Investigate the source and the applicable exposure definition first. The grouped result tells the reviewer that the anomaly is concentrated in product B, but not why it occurred.
Check Outcome Definitions and Predictor Timing
A target field may be derived from other fields. If the defined target is “at least one claim,” then its value must agree row by row with whether claim count is positive. A mismatch can survive aggregate checks: the total number of recorded positive target values can equal the expected total even when the positive rows are different. Recalculate the relationship at the row level.
The indicator \(\mathbb{1}(\cdot)\) equals one when its condition is true and zero otherwise. A separate timing check asks whether each proposed predictor was known at the time the prediction would have been made. An internally consistent field can still be unsuitable if it was created using information from after the prediction date. This is a data-lineage question: compare field definitions and timestamps with the intended prediction point.
Worked Example: Reconcile a Target and Review Predictor Timing
A hypothetical renewal table records claim count during the policy year and an outcome field called any_claim, intended to equal one when that count is positive. It also contains claim_review_flag, which the data dictionary says is assigned during claim adjudication after a claim is reported.
| Policy | Claim count | Recorded any_claim | Claim review flag |
|---|---|---|---|
| T01 | 0 | 0 | 0 |
| T02 | 2 | 1 | 1 |
| T03 | 0 | 1 | 0 |
| T04 | 1 | 0 | 1 |
Method. Recompute the target row by row using the stated definition. In R, create an expected target with dplyr::mutate(dat, expected_any = as.integer(claim_count > 0)), then select rows where any_claim != expected_any. The expected sequence for T01 through T04 is \(0,1,0,1\). Comparing it with the recorded sequence \(0,1,1,0\) identifies T03 and T04 as mismatches.
The recorded target sums to \(0+1+1+0=2\). The expected target also sums to \(0+1+0+1=2\). Equal totals therefore do not clear the target: two records are still assigned the wrong outcome. Claim counts sum to \(0+2+0+1=3\), which is consistent with two policy-years having a positive count, but the row-level comparison remains essential.
The timing review finds a different issue. The data dictionary says claim_review_flag is assigned after a claim is reported, during adjudication. It is therefore unavailable at renewal prediction time and must not be treated as a renewal predictor. This is not fixed by changing its values; the field’s timing makes it unsuitable for that prediction task. The common mistake is to check only whether the target’s overall total looks plausible, or to include every available column as a predictor. The row-level reconciliation and timing check address those different risks.
Check. The two target mismatches are T03 and T04, despite matching aggregate target totals. Claim_review_flag fails the timing requirement because it is assigned after the prediction point. Confirm the target against source records and exclude or separately handle the post-outcome field according to the modeling objective.
Interpreting Audit Results
An audit is strongest when it separates three questions: what is structurally unexpected, what violates a stated rule, and what needs human or source-system confirmation. A duplicate key is a structural flag. An exposure outside the permitted range violates the rule specified for that dataset. A predictor recorded after the outcome is a timing problem. These findings call for different resolutions.
Use grouped checks to prioritize investigation, not to label whole groups as erroneous. For example, unusually high average exposure in one product could reflect a legitimate difference in the business, a data-load issue, or both. Compare against the table’s definitions and source records. Likewise, impossible values should be assessed against explicit field meaning and permitted units; a numeric range alone cannot validate a field.
- Preserve the evidence. Keep the original values and identify flagged keys or rows so corrections can be traced.
- Do not conflate flags. Duplicate keys, invalid exposure, inconsistent targets, and late predictors are separate diagnostics.
- Re-run checks after a correction. Confirm that keys, totals, and row-level relationships now satisfy the stated rules.
- Record unresolved issues. A modeling decision should reflect known limitations rather than imply that the table passed checks it did not pass.
Check Your Understanding
For each problem, apply only the stated table definition and rules. The answer is provided at the end of each question for checking.
- A policy-year table has keys A, B, B, and C, with exposures \(0.25, 0.50, 0.50,\) and \(1.00\). Assume the two B rows are identical accidental repeats. How many rows and distinct keys are there, and what are raw exposure and exposure after retaining one row per key? (Answer: 4 rows, 3 distinct keys, raw exposure \(2.25\), and one-row-per-key exposure \(1.75\).)
- A table permits exposure only when \(0 < e \le 1\). The four values are \(0.20, 1.00, 1.03,\) and \(-0.03\). How many are invalid, and what is the total exposure among valid rows? (Answer: 2 invalid values; valid exposure total \(1.20\).)
- Claim count is \(0,1,0,2\), while the recorded any_claim values are \(0,1,1,0\). Under the definition any_claim equals one exactly when claim count is positive, how many rows mismatch, and which rows in order? (Answer: 2 mismatches, the third and fourth rows.)
- Product A has exposures \(0.40\) and \(0.60\); product B has exposures \(1.00\), \(0.90\), and \(1.10\). Under the rule \(0 < e \le 1\), what is each product’s raw exposure total, and which product has a flagged value? (Answer: A total \(1.00\); B total \(3.00\); product B has one flagged value, \(1.10\).)
- A proposed renewal predictor is internally consistent but its data dictionary says it is populated only after a claim is reported during the policy year. Is it available for renewal-time prediction, and what should the audit conclude? (Answer: No. Flag it as unavailable at the prediction point; do not use it as a renewal predictor without a defensible change to the prediction task and data timing.)