overview

BreedingInfill Overview

Version 3 · Repository documentation is no longer stale - HANDOVER 3.0.0, RUNBOOK 2.0.0 and DECISIONS.md landed 7 September. Replace the drift warning with a pointer to the repository document set.

BreedingInfill

Last reviewed: 2026-09-07 Repository: https://github.com/RQDG/BreedingInFill.git — working copy C:\Users\Robert\Projects\BreedingInfill Schema: breeding v4 (post-key_name drop, 27 August 2026)

What "infill" means here

BreedingInfill builds one table — breeding — and its job is to answer two questions about any thoroughbred:

Who is this horse? Identified as the triple (publish_name, country_id, year_of_foal).
Who are its parents? As sire_id and dam_id, self-referencing links back into the same table.

Because the parent links point at rows in the same table, ancestry is a recursive walk rather than a stored pedigree, and depth is measurable rather than assumed. There is no separate pedigree table, no generation columns, and no stored damsire — damsire is dam.sire_id, one join away.

"Infill" is the operating principle rather than a one-off import: the table is filled from whatever sources exist, blanks are filled by later sources, and nothing already present is ever overwritten by an automated load. A horse loaded today that turns out to be somebody's dam next month is picked up by re-running the link pass; nothing has to be reloaded. See Breeding Rules for the exact statement of that behaviour.

Where the data comes from

Two independent sources, loaded in this order:

Source	What it is	Contributes
PedigreeQuery	~2.97 M saved horse pages, scraped and parsed into `rs.parsed_horses` (71.8 M mentions)	Global coverage, five generations deep, year-only foaling data
Australian Studbook	134 `horses_*.jsonl.gz` files (~58 MB gzipped, 1.17 M records)	Australian and New Zealand depth, exact foaling dates, registered sex

They are deliberately independent — assembled from different origins — so where they agree that is evidence, and where they disagree it identifies which side is wrong. Full detail in Data Sources.

The pipeline

rs.parsed_horses ──[1. load_pq]──────┐
                                     ├──> rs.breeding ──[3. link_parents]──> sire_id / dam_id
Studbook/newAUData/*.jsonl.gz ─[2]───┘                        │
                                                              ├──[4. reports]──> CSV review lists
                                                              ├──[dedup_adjacent_years.sql]
                                                              ├──[reconcile_sources]──> is_checked / is_locked
                                                              └──[ship_to_qdb]──> qdb.breeding (id-for-id)

Steps 0–4 are driven by run_breeding_rebuild.bat, which is a dry run unless you pass apply. Every step logs to its own file under logs\rebuild_<timestamp>\ and nothing continues past a failed step. See Pipeline and Operations.

This replaced an eight-phase staging pipeline that needed 226 GB of intermediate tables (parsed_horses 162 GB + import_decisions 64 GB) to produce a ~2 GB result — a ratio of roughly 550:1. The full build under the old design was never completed. The current pipeline builds the whole table in about 31 minutes.

Current state of the table

Measured against the live databases on 7 September 2026.

Measure	`rs.breeding`
Rows (one per identity)	3,106,726
Sire named	3,036,399 — linked 3,031,580 (99.84 %)
Dam named	3,036,442 — linked 2,981,831 (98.20 %)
Both parents linked	2,977,656 (95.85 % of rows)
Complete five-generation pedigree (all 62 ancestors)	2,895,470 (93.20 %)
Exact foaling date present	776,711
`is_locked` / `is_checked`	0 / 0
Year-of-foal range	182 (a known truncation artefact) to 2026

Row provenance by first source to claim the identity: PedigreeQuery 2,495,266; Australian Studbook 611,460. A further 387,165 studbook records merged into identities PedigreeQuery had already created — those rows are dual-sourced but the table itself does not record that, which is the problem rebuild/reconcile_sources.py exists to solve.

Largest populations by country of origin: USA 838,055 · AUS 756,439 · ARG 246,111 · GB 196,957 · IRE 167,129 · NZ 165,548 · BRZ 126,636 · FR 101,743.

qdb.breeding on ODIN holds the same 3,106,726 rows with identical ids, sire_id and dam_id — verified id-for-id. Ids are shipped verbatim and never regenerated, because qdb.runner.breeding_id references them.

Independent validation

The rebuilt table was tested against race-day runners for 1, 2, 8 and 9 August 2026 — 2,963 runners and 5,926 stated parents, data assembled independently of the pedigree sources. No error was identified in breeding on any of the four days. Of twelve parent-match failures, eleven traced to a wrong year in the race data and were confirmed by the race data's own damsire field; one remains open. See August 2026 Validation.

Environment

	Source	Destination
Host	`localhost` (DESKTOP-7UDSBA1)	ODIN
Database	`rs`	`qdb`
MySQL	8.4.9	8.4.7

Bulk data (PQ pages, studbook dump) is read from %BREEDING_DATA%, defaulting to Z:\Breedinginfill. There is deliberately no silent fallback to the project directory — if the share is not mounted the run stops rather than quietly reading a stale local copy.

Repository status

This repository is the single source of truth. A second repository, RQDG/BreedingInFill2, held three days of work (23–25 August 2026) on an earlier two-pass loader design and was retired on 6 September 2026; its entire contents are preserved under archive/breedinginfill2/ with a README mapping each file to what superseded it. Do not commit new work there.

The repository's own document set was brought up to the rebuilt pipeline on 7 September and now agrees with these pages:

File	Version	For
`HANDOVER.md`	3.0.0	Current state and outstanding work — the file to open to resume
`RUNBOOK_BREEDING_BUILD.md`	2.0.0	Stage-by-stage build and ship procedure
`DECISIONS.md`	1.0.0	Every rule and why — D1–D18, E1–E7, R1–R11
`README.md`	1.2.0	Entry point