Skip to content

# Making test data for an LDM‐shaped database

Jump to bottom
RBirdwatcher edited this page Aug 2, 2026 · 1 revision

Making test data for an LDM-shaped database

What the LDM-to-Django mapping means in practice when you sit down to write test data — and why subtypes are the part that catches people out.

Background reading, not repeated here:

The one sentence version

The LDM keeps BIRD's taxonomy, Django implements a taxonomy as multi-table inheritance, and multi-table inheritance means one business thing is stored as several rows in several tables — so test data has to be written that way too.

Three features of the mapping you inherit

Feature What it means for a row of test data
Surrogate key on root classes only Only the top of a hierarchy has a <CLASS>_uniqueID. Subtypes are keyed by their link to the parent.
Multi-table inheritance Each class in the chain has its own table. A leaf instance needs a row in every table from the root down.
Arcs and delegates A thing classified on more than one axis needs a _delegate foreign key pointed at a row in an arc-member table — itself a chain of rows.

Scale, for a full BIRD LDM: roughly 646 classes, of which 199 are roots and 447 are subclasses, plus 46 arc classes. Most classes you would want to create data for are subtypes.

What one "thing" actually looks like

Take the example from the LDM mapping doc: a balance-sheet-recognised financial asset instrument, under IFRS.

class Financial_asset_instrument_type(models.Model):
    test_id = models.CharField(...)
    Financial_asset_instrument_type_uniqueID = models.CharField(..., primary_key=True)
    ...

class BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT(Financial_asset_instrument_type):
    Balance_sheet_recognised_financial_asset_instrument_type_delegate = models.ForeignKey(
        "Balance_sheet_recognised_financial_asset_instrument_type", models.SET_NULL, ...)
    ...

class BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT_IFRS(Balance_sheet_recognised_financial_asset_instrument_type):
    ...

Storing one of these touches four tables:

flowchart TD
  R["Financial_asset_instrument_type<br/>uniqueID = INST_1<br/>(root: carries the key and test_id)"]
  C["BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT<br/>financial_asset_instrument_type_ptr_id = INST_1"]
  A["Balance_sheet_recognised_financial_asset_instrument_type<br/>arc class, uniqueID = INST_1_IFRS"]
  M["BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT_IFRS<br/>..._type_ptr_id = INST_1_IFRS"]

  R -->|"inheritance"| C
  C -->|"..._type_delegate"| A
  A -->|"inheritance"| M
Loading

Two vertical links are inheritance (same thing, more specific) and one horizontal link is a delegate (a classification on a second axis). Both are foreign keys in the database, but they mean different things and you fill them in differently.

The key cascades down the chain

A subtype has no _uniqueID of its own. Django gives it a primary key that is also the link to its parent:

financial_asset_instrument_type_ptr = models.OneToOneField(
    "pybirdai.financial_asset_instrument_type",
    on_delete=models.CASCADE, parent_link=True, primary_key=True, ...)

The column is <parent class name, lowercased>_ptr_id, and its value must equal the root's _uniqueID. In a fixture CSV that means the same identifier string appears once per table in the chain:

File Key column Value
financial_asset_instrument_type.csv Financial_asset_instrument_type_uniqueID INST_1
blnc_sht_rcgnsd_fnncl_asst_instrmnt.csv financial_asset_instrument_type_ptr_id INST_1

That repetition is the point: it is what makes the four rows one thing.

Which columns go in which file

Attributes are declared exactly once, on the class that owns them — remove_duplicate_attributes_in_subclasses deletes the copies SQL Developer repeats on every subtype. So each table stores its own columns and nothing else.

Fixture CSVs follow the tables, not the classes:

A fixture CSV holds a table's local columns only: its link to the parent, plus the fields declared on that class. Inherited values belong in the parent's own CSV. — get_local_field_export_map() in test_data_template_utils.py

The Excel template follows the same rule. A worksheet for a subtype offers its link to the parent plus the fields declared on that class, and nothing else — so what you can type into a sheet is exactly what the fixture CSV for that table will hold.

Because a subtype's sheet does not offer its inherited fields, the parent tables come with it: ask the template for a subtype and its ancestors are added to the workbook automatically, listed on _Table_Index as parent table rather than selected. Fill inherited values there.

_Table_Index
  BIRD model/table                            Worksheet     Included as
  BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT         blnc_sht_...  selected
  Financial_asset_instrument_type             financial...  parent table

Older workbooks. Templates generated before this change do show inherited columns on subtype sheets, and values entered there are dropped on conversion — reported back as ignored_columns, in the JSON response when you import into a scenario or in _conversion_report.json inside the downloaded zip. If a value you entered vanishes, check that report, then re-export the template.

Classifying on the second, third and fourth axis

Inheritance gives you one axis. Every other axis is an arc, and choosing a value on it means pointing the _delegate foreign key at a row in the arc-member table.

Practical consequences:

  • The delegate target is a chain too. Before you can point at ..._IFRS, that arc-member row must exist — which means a row in the arc class table and a row in the member table, sharing a key, exactly as above.
  • The delegate stores the arc class's key, since that is what the foreign key points at. Under multi-table inheritance the member and the arc class share a key value, so the value you write is the arc member's identifier.
  • A null delegate is legal and silent. Delegates are emitted models.SET_NULL, blank=True, null=True, so a thing left unclassified on an axis loads without complaint and then fails to match any filter written against that axis.
  • _delegate is a naming contract. Forward engineering, join generation and the ETL filters find these fields by suffix. Don't rename the columns in a fixture header to something tidier.

Deciding which things to create

With 646 classes and several orthogonal axes, "make me a test instrument" is not a well-formed request until you have chosen a leaf on every axis. That combinatorial question is precisely what the SubtypeExploder answers: it walks the hierarchy and every delegate and writes the valid combinations to results/csv/<entity>_discrimitor_combinations_summary.csv (and a _full variant that also lists the columns each combination needs).

Use it as the menu:

  • A row of that CSV is one useful concrete thing — "a credit card debt acting in the role of an on-balance-sheet instrument" — and names every class in its chain.
  • The _full variant tells you which columns that combination actually needs, so you fill in a handful of fields rather than every field of every table.
  • Its own docstring is honest about the limitation: the LDM permits some combinations that are not real-world things (a loan in the role of an off-balance-sheet item). The CSV lists what is structurally valid; you still choose what is meaningful.

Two classes are not things to instantiate on their own:

  • Arc classes exist so a delegate has something to point at. They are marked abstract in the blueprint and skipped by the exploder, even though they get a real table.
  • A bare parent row — a row in a root table with no subtype row beneath it — is an instrument that is of no particular kind. Occasionally that is what you want; usually it is a mistake. See below.

The silent failure modes

Nothing here raises an error at load time. Fixtures are inserted with connection.constraint_checks_disabled(), so the database will accept all of it and the damage shows up as a report line that is empty or wrong.

1. A child row without its parent row disappears

Querying a subtype makes Django join to the parent table. If the parent row is missing, the join finds nothing and the row you inserted is simply not returned — it is in the table, invisible to every query, including the ones your transformation runs.

The loader sorts fixture files so parents load first (parent links are foreign keys, so they are part of the dependency sort in _sort_files_by_model_dependencies). Ordering is handled; existence is your job.

2. A bare parent row plus a subtype row is double counting

Querying a parent class returns all subtype rows too — that is the payoff of multi-table inheritance. So if you create a root row for "instrument 1" and, separately, a leaf row with a different key for the same conceptual instrument, a filter over the root class sees two instruments. Amounts double. One thing means one key, used all the way down the chain.

3. A dangling delegate or the… foreign key

Same as the input layer: the value must match an existing key exactly, nothing checks it, and the consequence is a join that quietly returns nothing.

4. Inherited columns filled in on the child sheet

Only possible with a workbook generated before subtype sheets were trimmed to their own columns, or with one you assembled by hand. The values are dropped on conversion and listed as ignored_columns — check that report, and re-export the template.

5. Deletes behave differently depending on the link

Link on_delete Effect of deleting the target
Parent link (inheritance) CASCADE Deleting the root row deletes the whole chain
Delegate SET_NULL Arc-member row goes; the thing survives, unclassified on that axis
Ordinary relationship SET_NULL Related row goes; the link is emptied

Deleting a root is therefore the clean way to remove a thing; deleting from the middle leaves orphans that queries will not return but that still occupy the table.

6. test_id only exists on root tables

The emitter adds test_id to classes with no superclass. Subtype tables do not have the column, so you tag a scenario at the root and rely on the chain — and on the CASCADE above — for the rest. Do not expect to filter a subtype table by test_id directly.

Two ways to make the data, and when to use each

Through the ORM — Django does the work

BLNC_SHT_RCGNSD_FNNCL_ASST_INSTRMNT.objects.create(
    Financial_asset_instrument_type_uniqueID="INST_1",
    test_id="scenario_1",
    ...,
    Balance_sheet_recognised_financial_asset_instrument_type_delegate=ifrs_row,
)

One call, and Django inserts into every table in the chain with a consistent key. You still have to create ifrs_row first, but the inheritance bookkeeping is free. This is the right route for scripted setup and for anything you would otherwise get wrong by hand.

Through Excel or CSV fixtures — you do the work

You are writing table rows, so every point on this page applies: one file per table in the chain, the key repeated in each, local columns only, delegates pointing at rows that exist. In exchange you get files that can be reviewed, diffed, and re-loaded, which is why the shipped test suites are CSV.

LDM test data is not input layer test data

Worth being explicit about, because it determines what you should be writing in the first place.

The transformations and datapoint tests run against the input layer. Forward engineering folds an LDM subtype chain into an input layer table by reducing discriminators: what the LDM stores as four rows across four tables, the input layer stores as one row with a type column set to a code. The entity_member annotation on each subtype class records exactly that mapping — discriminator_field, member_code, member_label (see specs/BIRD_LDM_ANNOTATIONS_SPEC.md).

flowchart LR
  L["LDM<br/>four rows, four tables<br/>identity by inheritance"]
  F["Forward engineering<br/>reduce discriminators"]
  I["Input layer<br/>one row<br/>identity by a TYP code"]
  L --> F --> I
Loading

So:

  • Test data written at LDM level is for exercising the model itself and for driving ETL generation.
  • Test data for a datapoint test belongs in the input layer, where a subtype is a coded value rather than a chain — the shape described in Unique IDs and the… foreign keys.
  • The discriminator combination CSVs are the bridge: they enumerate LDM subtype combinations and the input layer columns they correspond to, which is what GenerateETL consumes.

If you find yourself hand-writing a four-table chain in order to test a FINREP figure, you are probably working at the wrong level.

Checklist for one LDM test thing

  • Pick a combination from the discriminator combination CSV, not from imagination
  • Identify the root class of the chain — it owns the _uniqueID
  • Choose one identifier and use it as the key in every table of the chain
  • One CSV per table; each carries its parent link plus its own columns only
  • Inherited values go in the parent's file or sheet, never the child's
  • For each additional axis: create the arc-member chain, then point the _delegate at it
  • No bare parent row left over for a thing that also has a subtype row
  • test_id set on the root row
  • After loading, query the leaf class — if it returns nothing, a parent row is missing

Reference

What Where
LDM import (keys, arcs, delegates, subtypes) birds_nest/pybirdai/process_steps/sqldeveloper_import/import_sqldev_ldm_to_blueprint.py
Django emitter (test_id, inheritance order) birds_nest/pybirdai/process_steps/sqldeveloper_import/emit_django_from_blueprint.py
Parent-link generation in migrations birds_nest/pybirdai/process_steps/database_setup/migration_generator.py
Subtype explosion / valid combinations birds_nest/pybirdai/process_steps/generate_test_data/traverser.py
Local-columns-only fixture rule birds_nest/pybirdai/utils/datapoint_test_run/test_data_template_utils.py
Workbook → CSV conversion, ignored columns birds_nest/pybirdai/utils/datapoint_test_run/excel_to_csv_converter.py
Fixture loading and dependency ordering birds_nest/pybirdai/utils/datapoint_test_run/csv_fixture_loader.py
Subtype ↔ discriminator contract specs/BIRD_LDM_ANNOTATIONS_SPEC.md

Clone this wiki locally