Checking Data Completeness And Duplicates

Why Completeness Checks Need More Than a Total

A loss development triangle is only as reliable as the claim records used to construct it. A missing record can make a cell too small; a repeated transaction can make it too large. Yet neither a row count nor a matching grand total proves that the underlying records are complete and correct. A missing claim and an unexpected claim can offset in the count, while different claim amounts can offset in the total.

A useful check starts by specifying what one row represents and which identifier uniquely identifies that kind of record. A claim-level snapshot and a transaction-level extract have different grains: a claim may legitimately have several payment transactions, so repeated claim identifiers do not by themselves indicate duplicate transactions. Checks should also use an agreed snapshot and consistent valuation basis; otherwise, differences may reflect different data definitions rather than missing or repeated records.

Definition: The record grain is the unit represented by one row, such as one claim at a valuation date or one claim transaction. A key is the identifier, or combination of fields, that should identify one record at that grain. An expected roster is the independently defined set of keys expected in the extract. A key is missing if it is on the expected roster but absent from the extract; it is unexpected if it appears in the extract but not on the roster. A repeated occurrence of a key is an excess row occurrence, but it is a duplicate record only if the key and record definition establish that the repeated row is not a legitimate separate record.

A Structured Check

For a claim-level snapshot, a claim identifier may be the key. For a transaction-level extract, the key generally needs to identify the transaction, often using a transaction identifier together with a claim identifier. The exact key depends on the source system and the intended record grain. Before counting anything, confirm that the chosen key is populated and has the expected uniqueness properties.

Fix the comparison basis.
Identify the valuation snapshot, coverage, loss or expense basis, origin-period assignment, and record grain. Compare like with like.

Compare keys with an expected roster.
List expected keys absent from the extract, keys in the extract but not expected, and keys occurring more than once.

Reconcile counts and amounts.
Compare row counts, distinct-key counts, and monetary totals. Where useful, perform the comparisons by triangle cell as well as in aggregate.

Investigate the exceptions.
Trace unmatched or repeated keys to source records. Do not label an exception as an omission or duplicate until the record grain and source evidence support that conclusion.

If all keys are populated, the number of excess occurrences is the row count minus the distinct-key count. This is a count of repeated key occurrences, not automatically a count of erroneous records. For example, a source may legitimately have one row per payment transaction, even though several rows share the same claim identifier.

Worked Example: Finding an Omission and Repeated Claim Rows

A hypothetical claim-level snapshot is expected to contain six claims. Its independently checked roster and amounts are A: $1,200, B: $800, C: $1,500, D: $500, E: $1,000, and F: $2,000. The extract contains A: $1,200, B: $800, C: $1,500, C: $1,500, E: $1,000, F: $2,000, and F: $2,000. Assume the roster is complete and the extract contains no unexpected identifiers. Diagnose the key and amount differences.

Step 1: Count rows and distinct claim keys. There are 7 extract rows. The distinct keys are A, B, C, E, and F, so there are 5 distinct expected claims present. Two excess occurrences are present because $7-5=2$: one extra C row and one extra F row.

Step 2: Identify the missing key. Comparing the distinct extract keys with the six-key expected roster shows that D is absent. Thus there is 1 missing expected claim. The conclusion follows from the roster comparison, not from the distinct-key count by itself.

Step 3: Calculate the expected total. The roster total is $1{,}200+800+1{,}500+500+1{,}000+2{,}000=\$7{,}000$.

Step 4: Calculate the observed total. The extract total is $1{,}200+800+1{,}500+1{,}500+1{,}000+2{,}000+2{,}000=\$10{,}000$.

Step 5: Reconcile the difference. Under the stated assumption, the extract omits D, worth $500, and has one extra C occurrence worth $1,500 and one extra F occurrence worth $2,000. Therefore, $\$7{,}000-\$500+\$1{,}500+\$2{,}000=\$10{,}000$, matching the extract. The check confirms the arithmetic, while the key comparison identifies the specific exceptions.

Method and trap avoided: This is a roster-and-total reconciliation. A common mistake is to infer the number of missing claims from the expected count minus the distinct-key count without checking whether all observed keys belong to the roster. That inference is valid here only because the problem states that there are no unexpected identifiers.

Why the Total Reconciliation Formula Has Conditions

Let $E$ be the expected set of records, with expected amount $x_j$ for record $j$, and let $T_{\mathrm{expected}}=\sum_{j\in E}x_j$. Suppose the observed extract differs from the expected population only by omitted expected records and extra copies of expected records, and every copied amount is unchanged. Then the observed total can be derived by starting with the expected total, removing each omitted amount, and adding each extra occurrence.

T_{\mathrm{observed}} = T_{\mathrm{expected}} - \sum_{\text{omitted records }j}x_j + \sum_{\text{extra copy occurrences }k}x_k

To justify the equation, let $S$ be the expected records that appear at least once, and let $M$ be the expected records that are omitted. Then $S$ and $M$ partition $E$. The extract contains one copy of each record in $S$, plus the extra copies. Consequently,

T_{\mathrm{observed}} = \sum_{j\in S}x_j+\sum_{\text{extra copy occurrences }k}x_k = \sum_{j\in E}x_j-\sum_{j\in M}x_j +\sum_{\text{extra copy occurrences }k}x_k.

If the extract also contains unexpected records, changed amounts, or other differences, this equation is not a complete reconciliation. Those differences need their own terms. In particular, a total discrepancy cannot be attributed solely to omissions and duplicate copies unless the stated conditions have been checked.

Worked Example: Distinguishing Repeated Claims From Repeated Transactions

A hypothetical transaction extract is expected to contain three transactions for claim X: transaction T1 for $1,000, T2 for $300, and T3 for −$200. It also contains transaction U1 for claim Y for $700. The observed rows are T1, T2, T2, T3, and U1, with the amounts shown above for each occurrence. Use the transaction identifier as the transaction-level key and assume the expected transaction roster is complete.

Step 1: Establish the expected transaction population. There are four expected transaction keys: T1, T2, T3, and U1. The expected total is $1{,}000+300-200+700=\$1{,}800$.

Step 2: Count observed rows and distinct transaction keys. There are 5 rows and 4 distinct transaction keys. Thus $5-4=1$ excess occurrence: T2 appears twice.

Step 3: Compare the claim-level and transaction-level views. The extract has two claim identifiers, X and Y, and both are expected. That claim count does not reveal the repeated T2 row: X legitimately has three different transactions. At the transaction level, T1, T2, T3, and U1 are the four distinct expected transactions, with one extra occurrence of T2.

Step 4: Reconcile amounts. The observed total is $1{,}000+300+300-200+700=\$2{,}100$. The difference is $\$2{,}100-\$1{,}800=\$300$, equal to the extra T2 amount. This agrees with the key-level finding.

Method and trap avoided: This is a transaction-key check followed by an amount reconciliation. Treating every repeated claim identifier as a duplicate would incorrectly discard valid transactions T1, T2, and T3. Conversely, checking claim counts alone would miss the repeated transaction.

Triangle Totals Are a Diagnostic, Not Proof

A source-to-triangle comparison is useful when records have been assigned to origin periods and development ages using a consistent basis. Compare the source amount and triangle amount at the cell level where practical, then compare row or diagonal totals as appropriate to the reconciliation question. Aggregate totals can expose a problem, but agreement does not establish that the correct keys are present.

For example, an omitted claim and an unexpected claim of the same amount in the same cell leave that cell’s total unchanged. Likewise, an omitted amount can be offset by an extra repeated amount. The roster comparison tests which records are present; the monetary comparison tests whether amounts reconcile. Neither replaces the other.

Worked Example: Matching Triangle Totals With the Wrong Claim Keys

A hypothetical source roster has four claim records. In cell A, claims P and Q each have an amount of $1,000. In cell B, claims R and S have amounts of $600 and $900. The extract has four rows: P for $1,000 and an unexpected identifier Z for $1,000 in cell A, plus R for $600 and S for $900 in cell B. Assume the cell assignments and amounts are as stated. Determine whether the extract is complete.

Step 1: Compare row counts. The expected roster has 4 records, and the extract has 4 rows. Equal counts alone do not show that the same records are present.

Step 2: Compare keys. The expected keys are P, Q, R, and S; the extract keys are P, Z, R, and S. Q is missing, and Z is unexpected. The key sets do not match, so the extract is not complete and roster-consistent.

Step 3: Compare cell totals. Expected cell A is $\$1{,}000+\$1{,}000=\$2{,}000$; observed cell A is also $\$1{,}000+\$1{,}000=\$2{,}000$. Expected and observed cell B are each $\$600+\$900=\$1{,}500$.

Step 4: Compare grand totals. Both totals are $\$2{,}000+\$1{,}500=\$3{,}500$. Despite matching row counts, cell totals, and grand totals, the identifiers show that Q was replaced by Z.

Method and trap avoided: This is a joint key-and-total check. Concluding that the extract is correct because its triangle totals agree would miss the missing expected claim and unexpected record.

Practical Interpretation and Common Mistakes

A discrepancy is a signal to investigate, not a diagnosis by itself. A row-count difference may arise from missing records, extra records, repeated keys, or a different record grain. A monetary difference may reflect the same issues, an amount mismatch, or a comparison made on inconsistent snapshot or coverage bases. Use source evidence and the expected roster to determine which explanation applies.

Do not equate repeated claim IDs with duplicate rows. At a transaction grain, several legitimate transactions can share one claim ID. Test the transaction-level key.
Do not infer missing claims from counts alone. Distinct observed keys may include unexpected identifiers. Compare the actual key sets.
Do not treat matching totals as proof of completeness. Missing and unexpected records can offset, even within one triangle cell.
Do not use the omission-and-copy formula without its assumptions. Unexpected records or altered amounts require additional reconciliation terms.
Do not ignore the snapshot definition. Source and triangle comparisons must refer to the intended valuation and consistent record and amount bases.

Key takeaway: A defensible completeness check combines a clearly defined record grain, a suitable key, comparison with an expected roster, and amount reconciliation at an appropriate level. Counts and totals are valuable diagnostics, but the roster comparison is what identifies missing and unexpected records.

Check Your Understanding

For each problem, distinguish what counts and totals show from what can be concluded by comparing keys.

A hypothetical claim roster contains A, B, C, and D. The extract has rows A, B, B, and D, and all observed identifiers are on the roster. How many expected claims are missing, and how many excess row occurrences are present? (Answer: C is the one missing expected claim; there is 1 excess occurrence of B.)
A transaction roster contains T1 for $400, T2 for $250, and T3 for $150. The extract contains T1, T2, T2, and T3 with unchanged amounts. Find the expected total, observed total, and number of excess transaction occurrences. (Answer: Expected total $800; observed total $1,050; 1 excess occurrence, T2.)
A roster contains three claim keys J, K, and L, each with $500 in the same cell. An extract contains J, K, and M, also each with $500 in that cell. Do the row count and cell total establish completeness? (Answer: No; the count is 3 and both totals are $1,500, but L is missing and M is unexpected.)
An expected population has records A and B for $900 and $600. An extract contains A for $900, B for $600, and an additional unexpected record C for $200. Why can the omission-and-extra-copy equation not explain the observed total using only the expected roster? (Answer: The observed total is $1,700, but C is an unexpected record, not an extra copy of an expected record; the equation’s assumptions fail.)
A claim has expected transactions V1 for $700 and V2 for $300. The extract has V1 twice and V2 once, with unchanged amounts. A claim-level report shows one claim in both the expected and observed data. State the transaction-level finding and the observed total. (Answer: There is 1 excess occurrence of transaction V1; the observed total is $1,700, compared with the expected $1,000.)

What You'll Learn