Data validation tools tested: plant eight errors, count the catches

Data validation tools fall into a few kinds: control totals that compare an extract with its source, field rules that test formats, ranges and duplicates, reconciliations to the general ledger, consistency checks over time, and claim-by-claim comparison with the last extract. The fastest way to choose between them for reserving data is to plant errors you already understand in a copy of a claims extract and count what each kind of check catches. On the eight errors below, totals catch two, field rules three, a general ledger tie three, and roll-forward and prior-diagonal checks five, and a claim-by-claim comparison four, but only that comparison names the claims behind a recode or a migration. No single kind catches all eight. Botminds' Reserving Data Validation runs all five kinds on every extract before it becomes a loss triangle, and this page shows how to test it, or anything else, on your own data.

The extract is fictional: a mutual insurer's third-quarter 2026 claim and payment extract, 486,215 claims in nine reserving segments, as it runs through the solution. The scores are a design exercise, counted by what each kind of check can see, not a measured benchmark; six of the eight errors occur in the sample insurer's own history.

What are data validation tools, and which kinds matter for reserving?

A data validation tool tests whether data is fit for a stated use before anyone relies on it. In general-purpose software that usually means rules on fields and tables: is the value present, of the right type, within range, unique. Reserving data needs those rules, but it needs two more questions answered, and both are about time. Does this extract agree with the money finance has booked? And does it agree with the extract the triangles were built from last quarter?

The actuarial texts have set that out for decades. Friedland's study text for the Casualty Actuarial Society lists the components of a data review, including:

• "Consistency with financial statement data – Can the actuary reconcile the data with financial statement data (that may be subject to some form of external audit)?"
• "Consistency with prior data – Is the current data consistent with the data used in the prior analysis? If not, why?"
• "Data reasonableness – Are there certain values that appear questionable, such as large negative paid claims or apparent inconsistencies between data elements?"

(Friedland, Estimating Unpaid Claims Using Basic Techniques, CAS, 2010.) The five kinds of check in the chart map onto those questions. Field rules answer reasonableness. The general ledger tie answers consistency with financial statements. Control totals, roll-forwards and prior-diagonal checks answer consistency at the level of aggregates. Only the claim-by-claim comparison answers "if not, why?" for the claims themselves.

How do you test a data validation tool? Plant errors you already understand

Seed a copy of a real extract with errors whose effect you can compute, run the tool, and count. A seeded test beats a demonstration because you know the answer before the tool does: how many claims were changed, in which segments, with what effect on the triangle. It beats waiting for a real error because real errors arrive one at a time and without a key.

Choose errors from your own history, not from a vendor's list. The eight below come from the ways extracts break in practice: a coding-table change, a payment batch dated into a closed quarter, a filter that drops closed claims, a migration that renumbers claims, a module that posts recoveries into case reserves, a date entered wrong at first notice, a duplicated load, and a conversion that defaults missing accident dates. Each one leaves the extract loadable and the company total close, which is exactly why each one is dangerous.

What errors do totals checks miss?

Anything that moves claims or money between cells without changing the total. A recode moves incurred from one line to another; a defaulted accident date moves a claim from one accident year to another; a renumbering swaps one claim for an identical twin. In each case the extract ties to its source system at the company level, because the source system holds exactly the same wrong data.

This is the most common blind spot in validation built for data pipelines. A control total answers "did everything arrive?" and nothing else. In the sample quarter, the company totals were close enough to pass any sensible tolerance while one coding change moved $38.6 million of incurred between two lines.

How do back-dated payments affect triangles?

They rewrite a diagonal that has already been used. A payment made in July but dated 30 June belongs, by its date, to the second-quarter valuation. Last quarter's triangle was built without it; this quarter's extract puts it there. The locked second-quarter cell grows, the third-quarter increment shrinks by the same amount, and the age-to-age factor between them understates development.

In the sample quarter, 418 such payments totalled $2.3 million in Personal Auto. Two checks caught them independently. The general ledger tie showed Personal Auto's third-quarter paid at $118.6 million in the extract against $120.9 million booked for the September close, a difference of exactly $2.3 million. The prior-diagonal check compares every earlier cell with the locked second-quarter triangle and flags any change above 0.25%. The reserving lead re-dated the batch to its booking dates for reserving, and every difference to the general ledger was explained.

Paid by segment against the general ledger, every difference named

Can data validation catch claim system migration errors?

Yes, but not with rules alone, because migrated data is usually valid. A conversion that renumbers claims produces unique, well-formed claim numbers; a conversion that defaults missing accident dates produces real dates. What changes is the relationship between this extract and the last one, so the check has to compare them. In the sample insurer's history, the first wave of a claims platform migration renumbered 3,880 personal auto claims, and it was the comparison with the locked prior extract that showed them as renumbered claims rather than 3,880 closures and 3,880 new reports; the two segments affected were held on the day the extract landed.

Migration quarters deserve their own plan, with freezes, mapping tables and restatement rules agreed before the first wave; the claims system migration guide for reserving sets one out.

What a catch must include to count

A flag is not a catch. For the test, score a check as having caught an error only if its output lets the reserving team act without further digging: the claims behind it, the amount, the likely cause, and the effect on the triangle. On that stricter score the bars change shape. Control totals, the general ledger tie and the roll-forward say that something is wrong in a segment; field rules name the failing claims for the errors they catch; the claim-by-claim comparison names the claims and, with the change log, the cause.

Effect matters most for deciding what to hold. In the sample quarter, the triangle builder priced the recode before any factor was selected: as extracted, Commercial Auto's indicated IBNR on all-year factors fell from $130.2 million to $99.8 million, and General Liability's rose from $138.6 million to $171.6 million. Those are comparison figures from a mechanical chain ladder, not reserve selections, but a $63 million swing between two lines is a reason to hold both.

The triangle as extracted, with the restated factors beneath it

How to run the test on your own extracts

Use two consecutive quarters you have already closed, so you know the answer key. Plant the errors in a copy of the later one, keep the earlier one locked, and give the tool everything it would have in production: the general ledger close, the coding tables and the change log. Then score.

What no data validation tool replaces

Judgment about whether a change is real. A recode can be a mistake or a deliberate product decision; a jump in reopened claims can be a data artefact or a change in claims practice that the reserving method must reflect. A tool can find, explain and price; it cannot decide which. That decision, and the case-level question of whether reserves on individual files are adequate, sit with people (case reserve adequacy has its own discussion).

Retrospective testing makes the same point about models. Meyers and Shi, testing stochastic reserve models against later Schedule P outcomes, concluded that some changes in the environment may be invisible to any single model, so the profession cannot manage reserve risk with stochastic models alone (Meyers and Shi, CAS E-Forum, Summer 2011; their conclusion is quoted in the CAS Schedule P back-test). A data change is one such environmental change, and the cheapest one to find, because it is in the extract. The standard asks for a review, not an audit, and it does not ask the actuary to detect deliberate falsification (ASOP No. 23, section 3.8); a seeded test is a fair way to show that the review was done. How the five kinds of check sit in front of a team's existing reserving software is covered in data validation software for actuaries, and the checks themselves on the Reserving Data Validation page.

Questions people ask

What is a seeded error test?

A seeded error test plants known errors in a copy of real data and measures how many a check or tool finds. It is used in software testing and audit sampling because the tester knows the answer in advance; for reserving data, two closed quarters with the released triangles as the answer key make a natural test bed.

What data checks do actuaries run on claims data?

The usual set is a reconciliation to financial statement data, a comparison with the data used in the prior analysis, reasonableness tests for questionable values such as negative paid amounts, and confirmation of how each field is defined. ASOP No. 23 frames them as a review, which it distinguishes from an audit.

Are free or open-source data validation tools enough for reserving?

They handle field rules, duplicates and control totals well, and a capable team can script a general ledger tie. The harder parts are the claim-level comparison with a locked prior extract, reading the change log for causes, and leaving adjustments and a data memo the appointed actuary can rely on.

How many errors should a good tool catch in a seeded test?

All of the planted errors that change a triangle, with the claims behind each, and no holds on changes that have a ticket. A tool that flags a segment without naming claims has done a third of the work; one that holds every expected change will be switched off by the second quarter.

Can data validation tools find errors in the latest diagonal only?

The latest diagonal is where most new errors land, but back-dated payments and conversions also rewrite earlier cells. A prior-diagonal check that compares every earlier cell with last quarter's locked triangle catches those; in the sample configuration it flags any change above 0.25%.