Task: Model the hand-written artefact tables

Table of Contents

This page documents a task in the Bring every DQ artefact table onto the canonical model story. It captures the goal, current status, acceptance, and any notes or results.

1. Goal

None of the seven artefact tables is hand-written SQL.

2. Status

Field Value
State DONE
Parent story Bring every DQ artefact table onto the canonical model
Now Nothing.
Waiting on Nothing.
Next Nothing.
Last touched 2026-10-13

3. Acceptance

  • Each table is generated from its owning model and its hand-written create and drop scripts are gone.
  • The datasets that stage into them publish as before.

4. Plan

  1. Find each table's owning model. Five have one: crm_topology_config, market_observation, gmm_component, ir_curve_generation_config and ir_curve_template_entry. Two do not: ip2country publishes into a geo store that has no model, and lei_bic is GLEIF data whose owner the DQ-only task decides. Move those two to the tasks that own the question.
  2. Write an * Artefact columns section on each owner from the current table, with its key and extra indexes, so the columns do not change.
  3. The CRM table has six check constraints, and the artefact template had no way to state one. Add an expression table to the section, render it in the template, and refuse it on the junction and lookup archetypes, whose templates would drop it.
  4. Regenerate, diff each table against the old one, and follow the table names through the hand-written SQL.
  5. Recreate the database, run pgTAP, and provision Acme.

5. Measurements

Every number this task claims about a tree, with the command that produced it and the commit it was measured at. A number without both is not a measurement, because a clean-up record describes a tree and trunk moves under a branch: a consumer count, a gate's component count and a suite's size all change with no code changing, and the reader of a bare number cannot tell which tree it came from. Where the raw output matters, put it in an evidence/ file beside the task and name that file in the Command column.

What Value Command Commit
Branch diff 47 files, 1,086 insertions, 574 deletions git diff --shortstat origin/main...HEAD 8b5d378f56
Table change none: same columns, types and defaults; the CRM table keeps its six checks diff of each old and generated table body 8aa5c8bc41
Staging rows after the populates market observations 339, GMM components 91, IR curves 53, template entries 311, CRM rows 49 compass db sql 8aa5c8bc41
Published rows after an Acme provision CRM configs 15, driver pairs 75, derived pairs 170, market observations 6,388, GMM components 78, IR curves 33, template entries 251, feed bindings 275; no failed step compass db sql 8aa5c8bc41
pgTAP 46 files, 449 tests, PASS compass check --only pgtap 8aa5c8bc41
Codegen suite 1,404 passed compass check --only codegen-tests 8b5d378f56

6. Notes

7. Test Scenarios

Manual QA scenarios (scaffolded via compass add test_scenario) that verify this task. Link new ones here as they're created; the scenario doc itself links back via its "Verifies task" field.

Scenario State Notes
     

8. PRs

PR Title
#3237 [dq] Generate five hand-written artefact tables from their owning models

9. Review

# Comment summary File Decision Notes
1 The GMM table gets a key index on base_currency_code that it did not have ores.synthetic.gmm_component.org Decline The template always builds a key index. One extra index on a small staging table costs nothing.
2 Renamed tables and indexes reach only a recreated database the moved tables Decline Databases are recreated from the schema; there is no migration path to keep.
3 entity-writes, generated-column-refs and er-diagram fail various Decline All three come from main: two compute writers, a reporting index expression and the ER overview.
4 PR review: no test for the junction refusal test_junction_artefact.py Accept Added test_checks_on_a_junction_are_refused.
5 PR review: a check table under another header drops its checks without a word org_loader.py Accept The loader refuses a table with no expression column; a test covers it.
6 PR review: the refusal message does not name the archetype org_loader.py Accept The message names the junction or the lookup entity.
7 PR review: the domain entity path is safe next to the insert helper guard org_loader.py No change A note, not a defect.
8 PR review: the external/fed_h10/methodology.txt change looks unrelated methodology.txt Decline It names the renamed staging table, so the rename belongs in it.

10. Result

Five hand-written staging tables now come from their owning models: the CRM topology (ores_dq_crm_topology_configs_artefact_tbl), the market data observations (ores_dq_market_observations_artefact_tbl) and the three synthetic feed tables (ores_dq_gmm_components_artefact_tbl, ores_dq_ir_curve_generation_configs_artefact_tbl and ores_dq_ir_curve_template_entries_artefact_tbl). Each owning model carries an * Artefact columns section that states the staging shape, and the tables keep their columns. The populates, publish functions, artefact types and drop scripts follow the new names. The hand-written create scripts are gone; the drop scripts stay hand-written, as for every artefact table.

The artefact template now renders check constraints from an expression table in the section, so the CRM table keeps its six checks.

Two tables moved out of the task. ip2country needs a model of its geo store first, which Model the geo ip2country store covers. lei_bic is GLEIF data like lei_entities and lei_relationships, so Settle the owner of the DQ-only models decides its owner with theirs.

Acceptance: the five tables are generated, and an Acme provision publishes every dataset that stages into them with no failed step.

Emacs 29.3 (Org mode 9.6.15)