How Should Policy-Level and Location-Level Bordereaux Data Be Transformed?

One row in a risk bordereau does not always represent one policy.

A policy covering several properties may appear once per location. Policy dates, premium and overall limits can be repeated alongside location-specific addresses and insured values. If every repeated value is treated as a separate fact, totals can be multiplied without producing an obvious error.

Transformation should therefore define the grain of the data before mapping or aggregation. AI can help detect likely relationships, while business owners remain responsible for the intended meaning of financial and exposure values.

Mixed granularity creates hidden duplication

Data grain describes what one record represents. It might be a policy, a policy transaction, an insured location, a claim movement or another defined business event.

A location-level row can contain policy-level values for convenience. Five rows for one policy may repeat the same gross premium and overall limit, while each row has a different address and declared value. Summing every numeric column then produces correct location values but five times the policy premium.

The reverse problem also occurs. A policy-level row may contain a combined exposure amount that downstream users assume is location-specific. Changing grain without understanding the source meaning can lose detail, duplicate facts or create allocations that never existed.

Data modelling makes the intended grain explicit

Document the grain of each source region and each target table. Identify the natural or supplied key that distinguishes policies, transactions and locations. If a source lacks a stable location identifier, define how provisional records will be distinguished without pretending that an unreliable key is permanent.

Where appropriate, separate policy facts and location facts. A policy table can hold inception, expiry and policy-level premium, while a related location table holds address, occupancy and location-level exposure. Preserve the parent-child key so the records can be joined deliberately.

Financial allocation needs a business rule. Premium or overall limit should not be divided equally across locations simply because there are several rows. If no approved allocation basis exists, retain the amount at policy level and make the lack of location allocation clear.

AI can detect patterns that indicate record grain

AI can analyse repeated identifiers and values to propose that several rows belong to one policy. It can compare headings and value patterns to distinguish likely policy attributes from location attributes, even when source terminology varies.

It can also flag suspicious repetition, such as a full policy premium appearing on every location row, or detect a supposed policy key associated with conflicting inception dates. These suggestions help analysts focus on structurally uncertain groups.

The model cannot decide the commercial meaning of an amount from repetition alone. A value repeated across rows may be deliberate, and two similar locations may genuinely be separate risks. Proposed grouping evidence, confidence and exceptions should be available to the reviewer.

Controls protect totals and relationships

Reconcile measures at the grain where they are valid. Compare distinct policy counts, location counts, policy-level premium and location-level insured values separately. A single grand total can conceal duplication in one measure and omission in another.

Test joins between policy and location tables. Every child record should link to the intended parent, and joining data back together should not unexpectedly multiply policy-level facts. Retain source row lineage for each entity and record any approved allocation rule and version.

Review exceptions such as missing parent identifiers, conflicting repeated values and locations linked to more than one apparent policy. Human ownership is particularly important when transformed data supports accumulation management or underwriting decisions.

Example

A hypothetical property risk bordereau has five rows for one policy. Gross premium and the overall policy limit repeat on every row, while address and insured value differ by location.

AI identifies the repeated policy reference and proposes one policy record linked to five location records. The exposure analyst confirms that gross premium and the overall limit are policy-level values. No location-level premium allocation is created because the source and contract provide no approved basis.

The output preserves the original rows, reconciles one policy and five locations, and prevents downstream reports from multiplying the policy premium.

FAQs

• What does data grain mean in a bordereau?

  Grain is the business entity or event represented by one record, such as a policy, transaction or insured location. It determines which keys, values and aggregations are valid.

• Should policy premium be divided across locations?

  Only when an approved business basis for allocation exists. Equal division or another convenient assumption can create misleading location-level values. Retain the amount at policy level if necessary.

• Can AI identify policy-to-location relationships?

  AI can propose relationships from repeated identifiers, values and record patterns. Uncertain groupings and any material allocation still require review by an accountable data owner.