Analysis warehouse briefing — a living document, expanded as the work continues
A second, dedicated copy of the tournament data, reshaped specifically for analysis — kept separate from the live app so exploration never risks it. This page covers the schema, the vocabulary behind it, the tools used to query it, and what's actually been found so far.
The live app has one job: process what's happening right now — confirm a pick, score a game, update the leaderboard — quickly and reliably. It isn't built, and shouldn't be repurposed, to sit there while someone runs a heavy, exploratory question against it.
Concretely, that means a separate DuckDB file (datawarehouse/warehouse.duckdb), rebuilt from MySQL by a standalone script (datawarehouse/etl.py) whenever it needs refreshing. It holds six completed tournament seasons (2018–2019, 2022–2025) — the current, in-progress season is deliberately excluded, so every finding here is based on confirmed, final outcomes.
Three terms, each with a one-question test that tells them apart. Get the first one right and the other two mostly fall out of it.
The test: can you finish the sentence "one row of this table = ___" without using the word "and"? Decided first — everything else derives from it. fact_player_points's grain: one row = one player's points, in one tournament game.
The test: would you ever write WHERE or GROUP BY on this column? The nouns — who, what, when, where. dim_team.seed, dim_player.playerName, dim_year.gameYear.
The test: would you ever write SUM(), AVG(), or COUNT() on this column? The numbers a business process actually produced. fact_player_points.pts — meaningless without the dimensions around it saying whose points, in which game.
Three dimensions, four fact tables, all meeting at the same shared hub. This is a galaxy schema (or fact constellation) — several stars sharing points, rather than one isolated star.
erDiagram
dim_player {
varchar espnPlayerId PK
int gameYear PK
varchar playerName
varchar espnTeamId
decimal ppg
}
dim_team {
varchar espnTeamId PK
int gameYear PK
varchar teamName
varchar region
int seed
varchar conference
varchar conference_tier
}
dim_year {
int gameYear PK
}
fact_player_points {
varchar espnPlayerId FK
varchar espnTeamId FK
int gameYear FK
int round
int pts
varchar result
}
fact_player_points_season {
varchar espnPlayerId FK
varchar espnTeamId FK
int gameYear FK
varchar opponentEspnTeamId
int pts
varchar opponent_conference_tier
}
fact_team_games {
varchar espnTeamId FK
varchar opponentEspnTeamId FK
int gameYear FK
boolean won
boolean is_upset
}
fact_odds {
varchar espnTeamId FK
varchar opponentEspnTeamId FK
int gameYear FK
float spread
int moneyline
}
dim_player ||--o{ fact_player_points : "scored"
dim_team ||--o{ fact_player_points : "for"
dim_year ||--o{ fact_player_points : "in"
dim_player ||--o{ fact_player_points_season : "scored"
dim_team ||--o{ fact_player_points_season : "for / against"
dim_year ||--o{ fact_player_points_season : "in"
dim_team ||--o{ fact_team_games : "team / opponent"
dim_year ||--o{ fact_team_games : "in"
dim_team ||--o{ fact_odds : "team / opponent"
dim_year ||--o{ fact_odds : "in"
The bus matrix view — facts down the side, shared dimensions across the top, real row counts as of the last load:
| Fact table | Grain | Rows | dim_player | dim_team | dim_year |
|---|---|---|---|---|---|
fact_player_points | player × tournament game | 7,461 | ● | ● | ● |
fact_player_points_season | player × regular-season game | 125,350 | ● | ● | ● |
fact_team_games (tournament, despite the name) | team × tournament game | 756 | — | ● ×2, role-playing | ● |
fact_odds | team × round-of-64 odds | 376 | — | ● ×2, role-playing | ● |
dim_team sits behind 2,398 rows (not 384) because it covers every regular-season opponent too, not just tournament participants — seed and region are simply NULL for teams that never made the field. dim_player holds 5,633 rows, including one coach entity per team per year (see §5).
espnTeamId alone identifies the physical program — Duke is Duke, forever. But seed and region aren't properties of the program; they're properties of that team's participation in one specific tournament. Duke can be a 2-seed in the East one year and a 4-seed in the West the next. So dim_team's real key is the pair (espnTeamId, gameYear), not espnTeamId alone.
dim_team on espnTeamId alone, and if Duke has 6 rows (one per year they made the field), it matches all 6 at once — not one. The report silently sextuples that row, each copy carrying a different (wrong, except one) seed and region.The real test isn't "can this value change" — it's when. Three tiers, three different homes:
| Changes row-to-row, within a year | Settled once, changes year-to-year | Effectively permanent |
|---|---|---|
| Opponent record, streak entering a game. Lives on the fact row itself. | Seed, region, season-final ppg. Lives on the dimension, year-keyed. | playerName, the school a team ID refers to. Year-keyed anyway, for a consistent join shape. |
Not everything that looks like a dimension deserves its own table. dim_year, as built, has exactly one column: gameYear. Nothing to look up. A value like this — behaves like a dimension, carries no attributes — is a degenerate dimension: it could just live as a plain column on the fact table, no join required. It would only earn a real table if it grew attributes (had_first_four, champion_espnTeamId).
player_pts.coach ('Y'/'N') in the source MySQL is the same shape, for a related reason: a coach's row uses espnPlayerId = espnTeamId — a borrowed team ID, not an independent person being referenced.
player_pts, and at the time, zero had a matching row in players — nothing ever inserted a coach entity upstream. Any report inner-joining fact_player_points to dim_player silently dropped all 756 of them. Fixed by _ensure_coach_player() in app/blueprints/admin.py, wired into the Games/Players import step so every future year gets it automatically, plus a one-off backfill for years already loaded — matching the app's own original convention exactly (playerName='Coach', ppg=10).The warehouse's fact_player_points still excludes coach='Y' rows on purpose — that fact table is real players only. Coach scoring is tracked separately at team grain via fact_team_games. The deeper identity gap — no coach exists independent of their team, so "how does this coach do across every school they've coached at" isn't answerable — is unchanged. ESPN's roster API does expose a real, per-person coach ID distinct from the team; that would be the actual fix if that question ever gets prioritized.
"Who scored the most in the tournament, then how did they do in the season" is exactly the move a galaxy is for — but fact_player_points and fact_player_points_season should never be joined together before aggregating. Both are at the same grain (one row per player per game), so a raw join multiplies rows for anyone who played more than one game in either fact.
The fix is drill-across: collapse each fact to the grain actually being compared at — player, per year — independently, then join those two already-summarized result sets through the dimension keys they share.
WITH tourney AS (
SELECT espnPlayerId, gameYear, SUM(pts) AS tourney_pts
FROM fact_player_points GROUP BY espnPlayerId, gameYear
),
season AS (
SELECT espnPlayerId, gameYear, SUM(pts)/COUNT(*) AS season_ppg
FROM fact_player_points_season GROUP BY espnPlayerId, gameYear
)
SELECT p.playerName, t.tourney_pts, s.season_ppg
FROM tourney t
JOIN season s ON s.espnPlayerId = t.espnPlayerId AND s.gameYear = t.gameYear
JOIN dim_player p ON p.espnPlayerId = t.espnPlayerId AND p.gameYear = t.gameYear
ORDER BY t.tourney_pts DESCBy the time the two CTEs meet, neither is a raw fact table anymore — each is already one row per player, so the join can't fan anything out. dim_player only gets pulled in at the end, purely to attach a name to the ID.
The core question — out of season scoring average, seed, opponent strength, which factors actually predict tournament scoring — takes three distinct steps, each doing a different job.
Questions already known how to ask — a chart, a table. Available the moment data is loaded.
Tests every candidate factor against real results to find what actually moves the needle, and by how much.
Takes a confirmed finding and explains it in plain English. Communicates a result — doesn't discover it.
flowchart LR
A["DuckDB
the warehouse"] --> B["pandas
the table in memory"]
B --> C["matplotlib / plotly
explore"]
C --> B
B --> D["scikit-learn
the model"]
D --> E["matplotlib / plotly
explain"]
style A fill:transparent,stroke:#c65a2e,stroke-width:2px
style D fill:transparent,stroke:#c65a2e,stroke-width:2px
Visualization shows up twice, not once at the end: an exploratory pass right after pandas (plot the raw relationship, sanity-check the data, see if a pattern even looks plausible before building a model), and an explanatory pass after scikit-learn (visualize what the model actually found). A DuckDB table is persistent — still there tomorrow. A pandas DataFrame is a temporary snapshot of one query's result, held in memory only for as long as the analysis needs it.
app/blueprints/analytics.py is a complete, separate pipeline built earlier that pulls nine years of ESPN box scores and trains a scikit-learn GradientBoostingRegressor on a similar feature set. It's fully built but currently unregistered (no reachable routes) — kept as reference for feature ideas rather than revived, since this warehouse gives a cleaner, more reproducible foundation to build the same answer on.The first real report — total tournament points by player, broken out by seed — is already running against the warehouse:
| Player | Year | Seed | Tournament pts |
|---|---|---|---|
| Zach Edey | 2024 | 1 | 177 |
| Carsen Edwards | 2019 | 3 | 139 |
| Walter Clayton Jr. | 2025 | 1 | 134 |
| Mark Sears | 2024 | 4 | 121 |
| Adama Sanogo | 2023 | 4 | 118 |
Seeds 3, 4, and 8 all crack the top 10 alongside the 1-seeds — an early, informal signal that seed alone won't fully explain tournament scoring, which is exactly the kind of thing worth confirming statistically rather than eyeballing.
Known, deliberate gaps — not oversights, just work not yet prioritized: