How the BIRD model becomes a working database

How the BIRD LDM model becomes a working database

A short, non-technical overview. For the full detail, see From SQL Developer LDM to bird_data_model.py.

---

What we start with

BIRD describes, in one place, the things banks have to report on: instruments, parties, collateral, securities, and how they relate to each other. That description is drawn as a diagram in a data modelling tool, and exported as a set of spreadsheets - one listing the concepts, one listing their properties, one listing the relationships between them, and so on.

Those spreadsheets are a description of a database. They are not a database.

What we end up with

A working database, plus the code that reads and writes it - built automatically from those spreadsheets, with nothing typed by hand in between.

flowchart LR
  A["BIRD_model<br/>drawn_in_a_modelling_tool"]
  B["Spreadsheet_export"]
  C["Generated_model_file"]
  D[("Working_database")]
  A --> B --> C --> D

Loading

Why generate it instead of building it by hand?

The BIRD model changes. Every release adds concepts, renames things, and adjusts relationships. Anything built by hand from it would drift out of step, quietly, and the errors would only surface much later - in a report.

So the rule is: the model is the source of truth, and everything downstream is regenerated from it. Re-running the process after a BIRD update takes minutes and produces something guaranteed to match.

The one idea worth understanding: the family tree

BIRD organises its concepts as a family tree. An instrument may be more specifically a financial asset, which may be more specifically a debt security, which may be more specifically a covered bond.

This matters because the reporting rules are written at different levels of that tree. "Every instrument reports a reference date" applies to the whole tree. "Covered bonds report a cover pool identifier" applies to one branch.

flowchart TD
  I["Instrument<br/>reports_a_reference_date"]
  F["Financial_asset"]
  D["Debt_security"]
  C["Covered_bond<br/>also_reports_a_cover_pool_identifier"]
  I --> F --> D --> C

Loading

It would be simpler, technically, to flatten all of this into a few very wide tables. We deliberately don't. Flattening throws away the very structure the rules are written against, and every later step would have to work out again, from the raw values, whether a given row is a covered bond.

By keeping the family tree, the database ends up organised the same way the regulation is.

A wrinkle: things can be classified several ways at once

A single position can be classified by what kind of instrument it is, and by which accounting standard applies to it, and by whether it has been taken into possession - all at the same time. A family tree only handles one classification at a time.

Where BIRD classifies something in more than one way, the extra classifications are recorded as links to a separate small set of options, rather than as extra branches of the tree. The end result behaves the same way; it just needs a slightly different shape underneath.

How the process runs: understand, then write

flowchart LR
  S["Spreadsheets"]
  U["Step_1<br/>Understand"]
  W["Step_2<br/>Write_it_out"]
  D[("Database")]
  S --> U --> W --> D

Loading

Step 1 - understand. Read every spreadsheet and assemble the complete picture: which concepts exist, what properties they have, how they relate, and what the family tree looks like. Nothing is written out yet.

Step 2 - write it out. Turn that finished picture into a single file that describes the model in a form the software can run. The database then builds itself from that file.

The two steps are separate for a practical reason: the spreadsheets are in no particular order. A relationship can mention a concept that only appears further down the list. You cannot write a coherent description until you have read everything - so the first step is allowed to be messy and incomplete, and the second step is responsible for producing something correct and tidy.

What this gives us

One source of truth	The BIRD model. Everything else is regenerated from it.
Fast updates	A new BIRD release is re-imported, not re-implemented.
Structure preserved	The database is organised the way the regulation is.
Traceable	Every generated concept can be traced back to its BIRD origin.

Roughly how big is it?

A full BIRD import produces around 650 concepts, about 1,500 lists of permitted values, and several hundred relationships between them - all generated, none of it written by hand.