Skip to content

Data dictionary

The published mlb-research distribution ships the grain-complete statistic backbone: classical batting and pitching statistics at every grain a sabermetric researcher expects — game, season (per stint and combined), team season, and career — event-derived from Retrosheet.

The section below is included verbatim from the repository's canonical catalog, docs/DATA_DICTIONARY.md (section 3). The full catalog also documents the internal raw, core, meta, and serve layers, which are not part of the public distribution.

Published tables only

The browser Run SQL page exposes eight of these as queryable relations. player_season and team_season are built but held back from public distribution on source-rights grounds.

The complement to gold.game_feature: where game_feature is what was knowable before a game, the backbone is what actually happened, at every grain a sabermetric researcher expects (game → season → career; player and team). Built by mlb report. Game-grain rows are 1910–2025 from raw.retrosheet_event (matching the event-flag handling of the already-tied-out team stats, sql/team_woba_retrosheet_update.sql, ADR-034) and 2026 onward from MLB's own official per-game box score (raw.mlb_boxscore_batting / raw.mlb_boxscore_pitching) — Retrosheet publishes no event file for the in-progress season (ADR-289). Each game row carries a source marker (retrosheet_event / mlb_boxscore); the two builders never write the same (game, player, team) key. The season / team / career roll-ups aggregate the game grain and are source-agnostic.

Authoritative contract: openspec/specs/statistic-backbone/spec.md (grain set, source fidelity, null policy, roll-up direction, validation, export profile). Staged build plan: docs/superpowers/specs/2026-09-01-grain-complete-stat-backbone-design.md.

Two season lines, parallel (ADR-281): gold.batting_season / gold.pitching_season here are the event-derived line (Retrosheet, 1910+, per-team stint rows + a combined line, ra9 not era). gold.player_season / gold.team_season (§ elsewhere) are the Baseball-Reference / Lahman "official" line (2008+, carries era and other provider fields). They are distinct sources for distinct purposes — neither is a view over or a writer into the other.

Regular season only. gold.player_season, gold.team_season, gold.batting_season / gold.pitching_season, and every grain below them count regular-season games only — including the Game 163 tiebreaker, which counts as regular season (baseball.computer convention; ADR-283). Postseason batting and pitching live in their own relations, gold.batting_postseason / gold.pitching_postseason (§ 3.10). This is enforced by an mlb doctor envelope check (no season row over 163 games).

Known limitations:

  • gold.player_season / gold.team_season included postseason games for 2021+ until the separate-postseason-stats change (ADR-282) — now RESOLVED. mlb_baseball/connectors/bref.py pulls a regular-season-only Baseball-Reference window and raw.bref_* was re-ingested. If a gold.player_season row still shows more than 163 games, the re-ingest + mlb report rebuild has not been run yet.
  • Exact tie-out to Baseball-Reference is not achievable at the career grain or for seasons much before 2000. Retrosheet's event record and Baseball-Reference's official record have each absorbed decades of independent scoring corrections; they differ by small amounts (typically one or two on a counting stat per older season). The tie-out gate (scripts/verify_baseball_reference_tie_out.py) validates 2008–2025 field-by-field (2020 COVID season excepted) and carries two cited modern cases.
  • 2026-onward game rows are MLB's scorer-assigned box-score line, not event-derived like 1910–2025. Baseball-Reference has no page for the in-progress season, so correctness is checked against an independent reconstruction from raw.mlb_playbyplay (scripts/verify_mlb_boxscore_tie_out.py) rather than against Baseball-Reference. A researcher comparing a 2025 season to a 2026 season is comparing two different pipelines.
  • era coverage cliff. gold.pitching_game.er / gold.pitching_season and gold.pitching_career era are NULL through 2025 (the event stream carries no earned-run data) and populated from 2026 (MLB's box score carries scorer-assigned earned runs). ra9 is the cross-era rate for every year; a naive average of era across the 2025/2026 boundary is meaningless.

3.1 gold.batting_game

  • Grain: one batting box-score line per (game_id, player_id, team_id), regular season only. team_id is in the key (not an inferred attribute) so the rare case of a player appearing for both clubs in one game_id (a suspended game resumed after a trade) gets two rows instead of colliding.
  • Temporal semantics: the actual game result — not point-in-time.
  • Coverage: regular season only. 1910–2025 from raw.retrosheet_event (source = 'retrosheet_event'), 2026 onward from raw.mlb_boxscore_batting joined to core.game on game_pk (source = 'mlb_boxscore', sql/batting_game_mlb_build.sql). The MLB builder maps pa ← plate_appearances, ab ← at_bats, b2 ← doubles, b3 ← triples, hr ← home_runs, tb ← total_bases, bb ← base_on_balls, so ← strike_outs, gidp ← ground_into_double_play, etc.; b1 = h − b2 − b3 − hr. ~215 2026 regular-season games have no box score yet (ingest freshness — an mlb doctor join-coverage check, not a builder defect). Postseason batting/pitching lives in gold.batting_postseason / gold.pitching_postseason (§ 3.10).
  • Counting stats only — rate stats (AVG/OBP/SLG/…) live in the season and career roll-ups where the denominators are meaningful.
Column Type Definition
game_id, player_id, team_id bigint FKs to core.game / core.player / core.team
season, game_date integer, date From core.game
pa integer Plate appearances (bat_event_fl = 'T')
ab integer At bats (ab_fl = 'T')
r integer Runs scored by this player, as batter or baserunner
h, b1, b2, b3, hr integer Hits and hit types (event_cd 20/21/22/23)
tb integer Total bases (b1 + 2·b2 + 3·b3 + 4·hr)
rbi integer Runs batted in (sum(rbi_ct))
bb, ibb integer Walks (event_cd 14–15); intentional (15)
hbp integer Hit by pitch (event_cd 16)
sf, sh integer Sacrifice flies (sf_fl); sac bunts (sh_fl)
so integer Strikeouts (event_cd 3)
gidp integer Grounded into DP (dp_fl = 'T' and grounder ≤ 2025; ground_into_double_play 2026+). Undercounts pre-1988 (sparse battedball_cd).
source text Which builder wrote the row — retrosheet_event (≤ 2025) or mlb_boxscore (≥ 2026)
_built_at timestamptz When mlb report last rebuilt this row

3.2 gold.pitching_game

  • Grain: one pitching box-score line per (game_id, player_id, team_id), regular season only (same key rationale as gold.batting_game above). A two-way player also gets a gold.batting_game row.
  • Temporal semantics: the actual game result — not point-in-time.
  • Coverage: regular season only. 1910–2025 from raw.retrosheet_event (source = 'retrosheet_event'), 2026 onward from raw.mlb_boxscore_pitching (source = 'mlb_boxscore', sql/pitching_game_mlb_build.sql): gs ← games_started, bf ← batters_faced, outs ← outs, er ← earned_runs, hbp ← hit_batsmen, wp ← wild_pitches, bk ← balks, w/l/sv ← wins/losses/saves (the per-game decision). Postseason lives in gold.pitching_postseason (§ 3.10).
  • er: NULL for 1910–2025 — earned runs need reconstructed-inning logic that cwevent does not emit; r (total runs allowed) and season RA9 are the honest event-derived figures. Populated from 2026 (MLB's scorer-assigned earned_runs). A documented coverage cliff — see § 3 known limitations.
Column Type Definition
game_id, player_id, team_id bigint FKs to core.game / core.player / core.team
season, game_date integer, date From core.game
gs integer 1 if this pitcher started the game
bf integer Batters faced (bat_event_fl = 'T', this pitcher charged via resp_pit_id)
outs integer Outs recorded (sum(event_outs_ct)); IP = outs / 3.0
h, hr integer Hits / home runs allowed (event_cd 20–23 / 23)
r integer Runs allowed — charged per responsible pitcher (resp_pit_id for the batter-runner, run{1,2,3}_resp_pit_id for inherited runners)
bb, ibb integer Walks allowed (event_cd 14–15); intentional (15)
so integer Strikeouts (event_cd 3)
hbp integer Hit batters (event_cd 16)
wp integer Wild pitches (wp_fl = 'T')
bk integer Balks (event_cd 11)
w, l, sv integer Win / loss / save — from core.game.{winning,losing,save}_pitcher_id (≤ 2025) or the box score's per-game decision (≥ 2026)
er integer Earned runs — NULL for 1910–2025 (event stream has none), earned_runs from 2026
source text Which builder wrote the row — retrosheet_event (≤ 2025) or mlb_boxscore (≥ 2026)
_built_at timestamptz When mlb report last rebuilt this row

3.3 gold.batting_season

  • Grain: one is_combined = false stint row per (player_id, season, team_id), plus one is_combined = true combined row per (player_id, season) with team_id NULL. For a one-team player the combined row equals the single stint, so WHERE is_combined always yields exactly one full-season line per player. Matches Baseball-Reference's per-team lines + "2TM"/"3TM" combined line.
  • Source: rolled up from gold.batting_game by mlb report.
  • Temporal semantics: the actual season result — not point-in-time.
  • Coverage: 1910 onward, regular season (inherits gold.batting_game; 1910–2025 event-derived, 2026+ from the MLB box score).
  • Counting stats are plain sums. Rate stats are computed from this grain's summed components — a season AVG is total H / total AB, never the mean of game AVGs. Every rate is NULL when its denominator is 0.
  • SB / CS / SB% absent: gold.batting_game has no steals (baserunning, deferred to a later gold.baserunning_game).
Column Type Definition
id bigserial Surrogate primary key
player_id, season bigint, integer Player + season
team_id bigint FK core.team; NULL iff is_combined
is_combined boolean true = the all-teams full-season line
g integer Games played (distinct game_id)
pa, ab, r, h, b1, b2, b3, hr, tb, rbi, bb, ibb, hbp, sf, sh, so, gidp integer Summed counting stats
avg numeric H / AB
obp numeric (H + BB + HBP) / (AB + BB + HBP + SF)
slg numeric TB / AB
ops numeric OBP + SLG
iso numeric SLG − AVG = (TB − H) / AB
babip numeric (H − HR) / (AB − SO − HR + SF)
bb_pct numeric BB / PA
k_pct numeric SO / PA
source text retrosheet_event today
_built_at timestamptz When mlb report last rebuilt this row

3.4 gold.batting_team

  • Grain: one row per (team_id, season), rolled up from gold.batting_game. Same columns and rate-stat definitions as gold.batting_season (minus player_id / is_combined); primary key is (team_id, season).
  • Coverage: 1910 onward, regular season (1910–2025 event-derived, 2026+ from the MLB box score — inherits the game grain, source-agnostic).

3.5 gold.pitching_season

  • Grain: same two-row-kind shape as gold.batting_season — a stint row per (player_id, season, team_id) plus one is_combined full-season row per (player_id, season) (team_id NULL). Rolled up from gold.pitching_game by mlb report.
  • Coverage: 1910 onward, regular season (1910–2025 event-derived, 2026+ from the MLB box score — inherits the game grain, source-agnostic).
  • Counting stats are plain sums; rate stats computed from this grain's summed components, NULL when the denominator is 0.
  • era coverage cliff — era is NULL through 2025 (gold.pitching_game carries no er for the event era) and populated from 2026 as er × 27 / outs, but only when every contributing game row has a non-NULL er (CASE WHEN count(*) = count(er) — a season straddling the boundary, which no real pitcher-season does, reports NULL rather than a partial sum). ra9 (runs allowed per 9) is the cross-era rate for every year; the Baseball-Reference "official" ERA remains available per player-season from gold.player_season.
Column Type Definition
id bigserial Surrogate primary key
player_id, season bigint, integer Player + season
team_id bigint FK core.team; NULL iff is_combined
is_combined boolean true = the all-teams full-season line
g, gs integer Games pitched (distinct game_id); games started
bf, outs, h, r, bb, ibb, so, hr, hbp, wp, bk, w, l, sv integer Summed counting stats (IP = outs / 3)
er integer Summed earned runs — NULL unless every contributing game row has er (so NULL through 2025, populated from 2026)
ra9 numeric R × 27 / outs — runs allowed per 9 IP, the cross-era rate
era numeric ER × 27 / outs — NULL through 2025, populated from 2026 (coverage cliff)
whip numeric (H + BB) × 3 / outs
k9, bb9, hr9 numeric SO / BB / HR × 27 / outs
k_bb numeric SO / BB (NULL when BB = 0)
source text retrosheet_event / mlb_boxscore (per contributing game grain)
_built_at timestamptz When mlb report last rebuilt this row

3.6 gold.pitching_team

  • Grain: one row per (team_id, season), rolled up from gold.pitching_game. Same columns and rate definitions as gold.pitching_season (minus player_id / is_combined), except er / era are not carried at the team grain — ra9 is the team run rate for every year; primary key (team_id, season).
  • Coverage: 1910 onward, regular season (1910–2025 event-derived, 2026+ from the MLB box score — inherits the game grain, source-agnostic).

3.7 gold.batting_career / gold.pitching_career

  • Grain: one row per (player_id), summed from each player's per-season combined rows (is_combined = true) in gold.batting_season / gold.pitching_season — so a traded season is counted once, not per stint.
  • Extra columns: seasons (distinct seasons played), first_season, last_season. All other counting and rate columns match the corresponding season table; rate stats are recomputed from the career-total components. gold.pitching_career carries ra9 for every career; its er / era follow the same coverage cliff as gold.pitching_season — era exists only for a wholly-2026+ career (NULL unless every contributing season has a non-NULL er).
  • Coverage: 1910 onward, regular season (1910–2025 event-derived, 2026+ from the MLB box score — inherits the game grain, source-agnostic).

3.10 gold.batting_postseason / gold.pitching_postseason

  • Purpose: postseason batting and pitching totals, kept entirely separate from the regular-season backbone above (ADR-282 / ADR-283). Built by mlb report from Lahman's own BattingPost / PitchingPost (raw.lahman_batting_post / raw.lahman_pitching_post, 1884+) — the direct parallel to how gold.player_season is built from Lahman. Player ids conform via core.player.bbref_id (Lahman playerID is the Baseball-Reference id), falling back to raw.lahman_people.retroid → core.player.retro_id (~99.9% resolve); team ids via raw.lahman_teams.
  • Grain: one table per stat type, three row kinds:
  • per-round — is_combined = false, is_career = false: one per (player_id, season, round, team_id). round is Lahman's raw value (WS, ALCS, NLDS1, ALWC4, NWS for a Negro League series, …).
  • combined — is_combined = true: one per (player_id, season), all that year's rounds summed; round and team_id NULL.
  • career — is_career = true: one per (player_id), every postseason season summed; season, round, team_id NULL.
  • Columns mirror gold.batting_season / gold.pitching_season (counting stats are plain sums, rate stats recomputed at each grain) plus sb / cs on the batting side. gold.pitching_postseason has a real era (Lahman PitchingPost carries earned runs, unlike the event-derived gold.pitching_season), alongside ra9.
  • Never contains a regular-season game — the entire source is postseason. An mlb doctor check verifies every round is a recognised postseason round.
  • No postseason team-total relation yet — it is a GROUP BY over the combined rows and a noted follow-up (Lahman and baseball.computer both ship only player-grain postseason data).
  • Coverage: 1884–2025.