Chapter 014: Financial Data Models

Section 3: Finance Systems and Accounting Architecture · Chapter 014 of 100

The ECL engine is only as honest as its inputs: a missing financial-difficulty/concession record can distort credit-risk assessment, staging and reporting; a stale rating freezes provisions; an unmapped product dimension corrupts disclosures. Credit data — borrowers, facilities, collateral, guarantees, ratings, cash flows — modelled with dimensions, lineage and controls is what makes Block 7 auditable. This chapter designs the credit data model and the control/audit wrapper around it.

1. Chapter opening

A credit data model links group hierarchies (obligor → facility → collateral → guarantee → cash flow) with dimensions (product, segment, geography, currency, stage, rating) and history (point-in-time snapshots for back-testing and reproducibility). Controls validate completeness, accuracy and timeliness at each hop; internal audit and supervisors test the model against BCBS 239-style expectations (accuracy, integrity, completeness, timeliness, adaptability). This chapter builds entities, lineage, quality rules and the audit evidence pack.

The data model is not an IT diagram — it is the conceptual architecture that determines whether every downstream calculation (ECL, capital, disclosures, stress tests) is trustworthy. A facility record must retain the inputs needed for staging and the resulting assessment; stage is an output of that assessment, not its only input; a collateral record without a valuation date cannot support LGD; an obligor record without group linkage cannot feed concentration reports. Each missing attribute creates a cascade: the ECL engine either rejects the exposure (reducing coverage), defaults to a conservative assumption (inflating provisions), or silently excludes it (understating risk). The significance depends on the population, amount and treatment; a visible controlled exception is different from a silently omitted exposure.

The history requirement is equally critical. ECL at 31 December reflects conditions at that date, including relevant later evidence of those conditions under IAS 10. Retain both the original snapshot and approved corrections; do not confuse reproducibility with ignoring an adjusting event. Point-in-time snapshots preserve the data exactly as it was when the ECL calculation ran, enabling reproducibility years later. Without snapshots, a supervisor asking "why was this exposure in Stage 1 at year-end?" cannot receive a reliable answer — the data has since been overwritten. Snapshot discipline is the difference between an auditable model and an unchallengeable assertion.

2. Learning objectives

1. Draw the core credit entities and their relationships with key attributes.
2. Explain slowly-changing dimensions (ratings history, limit changes) and snapshot discipline.
3. Define data-quality rules (completeness, validity, consistency, timeliness) per entity.
4. Trace lineage from disclosure cell to source contract.
5. List audit/supervisory expectations for credit data (BCBS 239-aligned).

3. Business context

Financial data models determine the grain and relationship of every accounting and risk measure. A loan can have principal, integral fees, accrued interest and allowance in different records; a party can hold several products across legal entities. If those keys and measures are confused, an apparently clean total can conceal duplicated exposures or missing offsets.

Assess remediation benefits from actual labour, error rates and reporting speed. Faster close does not accelerate contractual interest receipt, create profit or release cash; it makes decisions and reporting available earlier.

4. Finance and accounting view

4.1 Dimensions and history

Dimensions attach to every posting and exposure: product (term loan, revolving facility, trade finance, overdraft), branch/channel (retail branch, corporate relationship, digital origination), currency (domestic, foreign — with mismatch flags for currency mismatches between loan and collateral), segment (retail/SME/corporate/sovereign/financial-institution — each with distinct PD/LGD/EAD models), stage (1/2/3 — the highest-impact dimension in ECL), rating grade (internal rating and external rating retained as distinct attributes, with migration history), forbearance status, concession reason and financial-difficulty evidence (a significant credit-risk indicator requiring assessment, not a universal mechanical IFRS stage assignment), country of risk (for concentration and sovereign exposure tracking). History preserved as effective-dated records plus period snapshots — reproduction requires the snapshot, model/parameter versions, runtime, calendars, precision and controlled deterministic calculations; investigate numerical differences rather than assuming two version labels alone guarantee identical bytes.

Slowly-changing dimensions require specific handling. Ratings change over time: a borrower rated BBB at origination, BBB+ at the last review, and BBB- today carries three rating records with effective dates. The ECL engine at any point-in-time reads the rating effective as of that date. If the rating record is overwritten (only the latest rating stored), the ECL engine at a prior date would read today's rating — a classic look-ahead bias that supervisors detect by comparing vintage ECL outputs to current rating distributions. The fix: type-2 slowly-changing dimensions (SCD-2) that store every historical rating with effective-start and effective-end dates, enabling point-in-time extraction.

Group structures maintained with ultimate-parent linkage and change history (restructurings must not break concentration views). A corporate group restructuring that moves a subsidiary from parent A to parent B must preserve the exposure history under both parents and link the new structure without orphaning exposures. The group master must support: ownership and control linkage, including connected counterparties through control or economic interdependence under the applicable prudential rules, circular ownership detection (A owns 40% of B, B owns 30% of A — which is the parent?), and management-control flags (where ownership is below 50% but control exists through board composition or contractual rights).

4.2 Quality rules and lineage

Completeness (every exposure has rating/stage/collateral-link or explicit exemption — no "unknown" defaults that the ECL engine treats as low-risk), validity (ratings within scale, dates sane, amounts reconcile — a rating of "Z" on a 1–20 scale is invalid, a collateral valuation dated 2030 is invalid, a drawn amount exceeding the limit is an exception that may represent a genuine unauthorised overdraft), consistency (risk vs finance exposure totals tie within tolerance — the risk exposure feeding ECL should match the finance exposure in the general ledger with an explicit bridge for scope, accrued interest, fee adjustments, ECL and other measurement differences), timeliness (feeds within SLA, staging inputs nightly — a rating feed delayed by 3 days means 3 days of exposures with stale staging). Lineage: disclosure cell → ECL run → parameters → source contracts, queryable by audit — the Finance Data Governance and BCBS 239 discipline applied to credit data.

Lineage must be queryable at the individual exposure level. An auditor examining a 500m Stage 2 provision should be able to select any facility within that portfolio and trace: the disclosure line it feeds, the ECL run that produced its provision, the PD/LGD/EAD parameters applied, the rating used, the collateral value applied, the cash-flow schedule projected, and the source contract that originated the exposure. Exposure-level lineage supports reproducible IFRS 7 disclosures, model validation and applicable BCBS 239 principles. Neither reference prescribes this exact database or query architecture; the bank must demonstrate the required accuracy and explain its aggregation and scope.

4.3 Deep dive: group linkage, MDM survivorship and the BCBS 239 examination

Model relationships rather than a single obligor → facility → collateral chain. Facilities can have several borrowers; collateral or guarantees can support several exposures. Allocation tables need amounts, dates and priority rules so security value is not counted repeatedly. Ownership hierarchies do not alone determine connected-counterparty groups: applicable rules may also use economic interdependence.

Master data management (MDM) must retain source identity. An internal rating and an agency rating are different measures, not conflicting versions of the same field. Survivorship rules apply to competing records of the same defined attribute. Preserve overrides and the evidence used to approve them.

BCBS 239 sets principles, not universal four-hour response times, 0.1% tolerances or fixed examination sample sizes. Set bank-specific service levels proportionate to risk, demonstrate accurate aggregation and document exclusions. Preserve accounting/reporting bridges, not only a statement that risk and finance numbers agree.

5. Product and customer impact

Clean obligor data prevents duplicate onboarding, conflicting limits and contradictory decisions across products; accurate collateral data speeds approvals and releases; correct group linkage gives corporate clients single-view service (and the bank single-view risk). Data errors surface as wrong declines, wrong prices and repeated KYC asks — customer pain with data roots.

The customer experience of poor data is concrete: a corporate client with three subsidiaries applies for a facility at each subsidiary. Without group linkage, each application is assessed independently — the combined exposure exceeds the group limit but no single application detects it. The client receives three approvals, then the group-limit breach is discovered post-disbursement, triggering a forced reduction, relationship damage, and potential legal dispute. With accurate group linkage, the first application triggers a group-level assessment, the combined exposure is approved within group limits, and the client experiences seamless service. The data model determines whether the bank sees the client as three unrelated borrowers or one integrated relationship.

6. Regulatory and supervisory view

BCBS 239 principles (governance, architecture, accuracy, completeness, timeliness, adaptability) are examined via risk-data aggregation assessments; credit-data findings feature in SREP and targeted reviews. Large-exposure, concentration, NPE and disclosure returns all consume this model; supervisors sample lineage live in examinations. Audit committees track data-remediation programmes as standing agenda items.

Supervisory examination can trace a risk-based sample from contracts through source attributes, models and reported outputs; sample selection and size are determined by the review, not a universal 50–100-exposure rule. Potential findings include stale ratings, expired collateral valuations, missing group links and unrecorded concessions to borrowers in financial difficulty. Those concessions inform a separate SICR/impairment assessment rather than automatically requiring Stage 2. Remediation, deadlines and supervisory escalation depend on the applicable powers and findings.

7. Systems and data view

Credit data platform: golden sources per entity (rating system, collateral register, loan book, group master), MDM with survivorship rules, snapshot store (immutable, versioned, queryable), DQ engine with rule library and quarantine (rules executed daily, results logged, exceptions tracked to resolution), lineage catalogue (every disclosure cell mapped to its source chain), and consumption APIs for ECL/risk/finance (read-only, versioned, with audit trail of who queried what when). Controls: source certification (each golden source has an owner who certifies completeness monthly), DQ SLAs with break queues (illustrative internal SLAs per rule type, for example 24 hours for blocking and five business days for warning exceptions; actual deadlines depend on impact, reporting cut-off and applicable obligations), change management on schemas (schema changes require impact assessment on downstream consumers — ECL engine, disclosures, capital calculations), access controls with audit trails (who can modify golden records, with dual control for material changes).

8. End to end process

1. Originate: golden records created with mandatory attributes (every new exposure must have all required fields populated before booking — no "fill later" workarounds).
2. Maintain: ratings, values, limits updated with effective dating (changes logged with timestamp, user, source, and old/new values).
3. Snapshot: period-end freeze with version stamp (snapshot taken at reporting-date close, immutable thereafter — corrections require a new snapshot with justification).
4. Consume: ECL/risk runs read snapshots only (no live data access during calculation — reproducibility depends on snapshot isolation).
5. Validate: DQ rules fire, quarantine worked (rules execute on snapshot, exceptions logged, blocking exceptions resolved before ECL run proceeds).
6. Evidence: lineage packs filed for audit and examination (pack includes: snapshot version, model version, DQ results, lineage traces for sampled exposures).

9. Controls and risks

10. Practical examples

A: an unlinked group. In a fictional case, three subsidiaries have 400 million each of drawn exposure. The aggregate is 1,200 million; whether they form one connected group depends on control/economic-interdependence rules. A credit limit and ECL assessment remain distinct from the legal large-exposure calculation.

B: snapshot correction. A later rating review provides evidence of deterioration that existed at year-end. Preserve snapshot v1 and the approved adjusting evidence; rerun as v2 and document the delta. Reproducing v1 is useful for the audit trail but does not make an incorrect v1 the right published estimate.

11. Diagrams

Figure 1. Financial data model and lineage. Figure 2. Keys, grain and dimensional controls. Figure 3. A controlled data-model change.

12. Tables

Table 1 — DQ rule examples

Table 2 — Audit evidence pack contents

13. Illustrative bank case study

A measurable data-control improvement. In a fictional case, a bank retires 200 spreadsheets, each consuming 10 hours per month. Gross annual effort is 200 × 10 × 12 = 24,000 hours before deducting replacement-system operation, assurance and maintenance. At an assumed 100 currency units/hour, the gross labour equivalent is 2.4 million, not a guaranteed cash saving. A four-day faster close creates earlier insight; it neither releases accrued interest nor changes cash-flow timing.

14. BA, developer, tester and operations guidance

• BA: Model entities, attributes, dimensions and history rules before any ECL or reporting requirement.
• Developer: Effective-date everything; snapshot immutably; quarantine, never silently drop, bad records.
• Tester: Lineage sampling (cell → source), DQ rule firing, snapshot rerun equality, hierarchy edge cases (loops, orphans).
• Operations: Work DQ quarantine like break queues; attest data quality per cycle with metrics.

For BA teams: the entity model must be designed bottom-up from the ECL requirements, not top-down from the existing system. The ECL engine requires: PD (needs rating + migration history), LGD (needs collateral + guarantee + recovery history), EAD (needs drawn amount + undrawn commitment + prepayment assumptions), stage (needs DPD + SICR indicators + forbearance flags), and scenario conditioning (needs segment + geography + product dimensions). Every attribute the ECL engine consumes must be present in the data model, with a golden source, an owner, and a DQ rule. For developers: snapshot immutability means the snapshot store must be write-once — no updates, no deletes, only appends with version stamps. For testers: the most critical test is the "rerun equality test" — take a snapshot from 12 months ago, re-run the ECL engine with the same model version, and verify the output matches what was actually reported. For operations: DQ quarantine is not a cleanup exercise — it is a risk-management process that must be reported to ALCO with escalation for unresolvable breaks.

15. Common mistakes

1. Ratings without history (SICR needs origination comparison).
2. Forbearance as free text instead of a governed flag.
3. Snapshots overwritten instead of versioned.
4. DQ warnings nobody owns.
5. Lineage documented in slides, not queryable in systems.
6. Group linkage updated ad-hoc instead of through MDM.
7. DQ rules calibrated once and never recalibrated.
8. Snapshot taken at close-of-business instead of reporting-date cutoff.

16. Key takeaways

1. Model borrower, facility, collateral, guarantee and cash-flow relationships with allocation tables, dimensions and history; they are not always a simple linear hierarchy.
2. DQ rules with quarantine make quality operational, not aspirational.
3. Lineage must be queryable from disclosure to contract.
4. Snapshots + versions make ECL reproducible years later.
5. Use the same governed exposure data for Expected Credit Loss Foundations, Significant Increase in Credit Risk and Default, PD, LGD and EAD and Impairment Accounting in Practice, with explicit bridges to accounting and prudential measures.

17. References and verification notes

• IFRS Foundation: IFRS 9: classification depends on business model and contractual cash flows; initial recognition and directly attributable costs follow IFRS 9. This is the IFRS track, not US GAAP CECL.

• IFRS Foundation: IAS 10: adjusting events evidence conditions at the reporting date; non-adjusting events concern later conditions and material ones require disclosure.

• BCBS 239: risk data aggregation principles: governance, architecture, accuracy, completeness, timeliness and adaptability underpin risk-data aggregation; scope and supervisory application vary. It prescribes neither one warehouse architecture nor universal numeric reconciliation tolerances.

• BCBS 239 principles for risk-data aggregation; IFRS 7 disclosure lineage needs; group/large-exposure linkage concepts per applicable prudential rules — verify current supervisory guidance locally.

• All structures and metrics are illustrative training designs.