Bordereaux ingestion: why a new delegate's first file is the hard one

Bordereaux ingestion is the first stage of bordereaux processing: reading a delegate's file, finding where the data starts, placing each of its columns on the carrier's standard layout and converting its dates, amounts and codes, so that every row can be checked against the binder. It is hard because no two delegates send the same layout, and because a layout that worked last month can change without notice. A new delegate's first file is the hardest, because every column must be decided; every file after it should be easy, if the decisions were remembered. Botminds' Bordereaux Management is built around that idea: it maps each file from the delegate's remembered mapping and asks a person only about what changed.

The examples come from the solution's fictional sample carrier, Pellworth Specialty Insurance Company, and its 14 delegates, whose files arrive as spreadsheets and CSVs in 14 different layouts.

What is bordereaux ingestion?

Bordereaux ingestion is everything between "a file has arrived" and "every row is in our layout": receiving and logging the file, reading its structure, mapping its columns, standardising its values and confirming that nothing was lost on the way. It ends where checking begins. A file is ingested correctly when every row the delegate sent is present, every field the binder needs is in the right column, and every value is in the format the checks expect.

The last condition is the one that fails silently. A sum insured read from the wrong column, a date read month-first when the delegate switched to day-first, a code from the delegate's own list passed through unconverted: each produces rows that look complete and check wrongly. Ingestion is where most of the errors in bordereaux data management are either caught or created.

Why do bordereaux formats differ from delegate to delegate?

Bordereaux formats differ because each delegate produces its file from its own systems, and binders usually accept the delegate's format once it is agreed rather than imposing one. The binder wording quoted in XL Insurance Company SE v IPORS shows how open that can be: the coverholder had to produce premium and claims bordereaux "in a format(s) agreed in advance by the Underwriters", and a later wording let it report "in a manner or format(s) agreed by the Insurers" or by "making the accounting information available to Insurers in an alternative manner agreed in advance by the Insurers" ([2022] EWHC 2093 (Comm), paragraphs 9–10).

US law points the same way without fixing a layout. The NAIC model act requires records the insurer can use and, where electronic claims files exist, says "the contract must address the timely transmission of the data" (Model #225 §4J), but it does not say what the data looks like. The result, for a carrier with a dozen delegates, is a dozen layouts, each agreed once and each liable to drift whenever the delegate changes a system or a person.

A delegate's first file is a test of the carrier as much as of the delegate: whether it can take data in the shape it arrives.

The FCA found in 2025 that some insurers did not receive management information from their outsourced claims handlers consistently, or went long periods without the full data, and so could not compare figures or see trends emerging (FCA, home and travel claims handling arrangements, 2025; quoted in MGA data platforms). It was writing about claims handling, not bordereaux, but it names the cost of unmanaged formats exactly: data that is not consistent and comparable cannot show a trend. Ingestion is where comparability is made or lost.

How do you map a new bordereau the first time?

You map a new bordereau by working from the binder to the file, not from the file to the binder: start with the fields the binder's checks need, find each one in the delegate's layout, and only then deal with the columns left over. The first file sets the mapping every later file will be read with, so it deserves a person's full attention and a record of every decision.

After the first file: what has to be remembered

After the first file, the ingestion problem changes from deciding to remembering. Every confirmed decision, this column is the sum insured, these codes mean renewal, these dates are day-first, should apply again next month without being asked, and should stop applying the moment the file no longer matches it. A process that re-asks every month wastes the analyst's time; one that never re-checks loads changed files wrongly.

The sample carrier's year shows the scale. Across its 14 delegates the solution held 1,912 remembered mappings, and the delegates made 31 column changes during the year, 27 of them settled with a single confirm. The remaining work was the four changes that needed more than that, and the first files of new delegates.

Renamed, moved, new, dropped: four column changes and how to spot each

A delegate's file can change in four ways at the column level, and a fifth at the value level. Each needs a different response, and only one of them, the dropped optional column, is safe to handle without a person.

The renamed column is the one that justifies the whole design. A name-matching import sees a missing column and an unknown one; depending on how it was built, it either rejects the file or loads it without the missing field. In the sample month, the missing field was the sum insured, and until the rename was confirmed the per-location limit check could not run. Once it was, five locations above the $5,000,000 limit appeared.

Bordereaux Management's mapping memory: each delegate's headers month by month, and every change with who confirmed it

Dates, codes and amounts: standardising bordereau values without guessing

Standardising values means converting each delegate's dates, amounts, codes and flags to one format and one code list, and the rule that matters is never to guess. A date like 04/05/2026 is April or May; picking one moves the risk between reporting months and, near a renewal, between binders. An amount with no currency in a delegate that writes in two currencies is not a number yet. A construction code from the delegate's own list means nothing until it is mapped to yours.

Most ambiguity can be settled from the file itself. A date column in which any value has a day above 12 is settled for the whole column; an amount column whose total ties to the cover sheet in one currency is in that currency. What cannot be settled should stop and ask, and a value that cannot be read at all should be left blank and the row returned, not filled with something plausible. In the sample carrier's settings, optional fields with no source, such as producer code or number of stories, are left blank rather than guessed.

What is a bordereau template, and should delegates fill yours?

A bordereau template is a fixed layout a carrier asks its delegates to fill, so that every file arrives the same way. It is the obvious answer to format variety, and it works well for new delegates who have no system yet. It works less well for established delegates, because filling a carrier's template usually means re-keying or exporting from their own systems into someone else's columns, which is a new source of errors at the delegate's end.

The practical answer for most carriers is both: offer a template, accept the delegate's agreed format where it differs, and map every format to one internal standard layout. The binder fixes the agreed format; the mapping memory turns it into the standard one; and a change in either is noticed and confirmed rather than absorbed.

How do you standardise bordereaux across delegates?

You standardise bordereaux across delegates by mapping every delegate's file, whatever its layout, to one standard layout with one set of field definitions and code lists, owned by one person. The standard can be a market layout or the carrier's own; what matters is that every delegate's sum insured, inception date and commission mean the same thing once mapped, so that a loss ratio or a premium trend compares like with like.

Ownership is the part carriers skip. Someone, usually the data and MI lead, must own the standard layout and approve changes to it, because a field redefined for one delegate is redefined for all. In the sample carrier, every delegate is mapped to the same 39-field layout, and a change to the layout is a draft until approved.

One confirm in Bordereaux Management places a renamed column for this month and every month after

Bordereaux ingestion with Bordereaux Management

Bordereaux Management ingests any delegate's spreadsheet or CSV through three agents and one person. The file reader finds the header and ties the totals; the column mapper places the remembered columns and proposes what changed, with evidence; the analyst confirms; and the value standardiser converts the values, stopping on anything ambiguous. The first file from a new delegate is mapped with the analyst column by column; every file after it starts from that mapping.

What the analyst sees in a normal month is a short list, not a file: in the sample delegate's September, 36 of 38 columns placed from memory, one rename to confirm and one new column to map. To see it on the sample carrier, start from Bordereaux Management; which bordereaux tasks move to agents sets ingestion in the full division of work.

Questions people ask

What is a bordereaux ingestion tool?

Software that reads delegates' bordereaux files and loads them into a carrier's standard layout. The useful test of one is a file with a renamed column: a good tool notices the change from the column's values and asks; a weak one rejects the file or loads it without the field.

What is a bordereaux mapping?

The set of decisions that say which of a delegate's columns becomes which field of the carrier's layout, how its dates are written and what its codes mean. A mapping is made once, on the delegate's first file, and should be remembered and reused every month, with each change confirmed by a person.

What happens to rows the ingestion cannot read?

They should be flagged and returned, never dropped. A dropped row is a risk the carrier does not know it wrote. In Bordereaux Management the file reader never drops a row it cannot read, and mapping does not start until the file's totals tie.

How long does it take to onboard a new delegate's bordereaux?

No published benchmark exists; it depends on the layout and on how complete the binder's field list is. Measure it on your own delegates: the time to map the first file, and the number of questions that had to go back to the delegate. The second file is the real test of whether the first mapping was remembered.

Does ingestion check the binder?

Not by itself. Ingestion makes the rows readable; the binder checks run after it. The link between them matters: a check whose column has not been mapped yet must report that it could not run, so an ingestion gap never looks like a clean file.