I moved seven tables of the warehouse behind my MLB model from SQLite into Snowflake, rebuilt them as a star schema with dbt, and checked every table against the original before trusting a single row. Getting there turned up a full season of game results the old database had never pulled. I backfilled it, re-ran everything, and it all ties out.
The MLB model runs on a 21-table SQLite warehouse fed by a nightly pipeline. I took the seven tables that form a clean star: one batting line per batter per game, pointing at the player, team, game, park and season. I did it to learn Snowflake and dbt properly, on data I know well enough to notice when a number is wrong.
COPY INTO,
so a rerun replaces data instead of doubling it.
| Table | Rows | Role |
|---|---|---|
batter_game_stats | 157,853 | fact: one batting line per batter per game |
games | 7,832 | schedule, park, weather |
players | 6,919 | player dimension |
game_results | 7,831 | final scores, joined onto games |
parks | 65 | park dimension |
teams | 30 | team dimension |
seasons | 12 | season dimension, rule-change flags |
Twenty-six checks, run identically against SQLite and Snowflake: row counts and distinct keys for all seven tables, nine column totals, and hits, runs and RBI season by season. All twenty-six match.
| Season | Batting lines | Hits | Runs | RBI | H+R+RBI | Result |
|---|---|---|---|---|---|---|
| 2024 | 48,595 | 39,543 | 20,933 | 20,213 | 80,689 | PASS |
| 2025 | 65,549 | 48,988 | 26,551 | 25,645 | 101,184 | PASS |
| 2026 | 43,709 | 35,136 | 19,017 | 18,342 | 72,495 | PASS |
The season check is the one I care about most. Its Snowflake side reads from the finished dbt fact table, not the raw copy, so it checks the transformation as well as the load. H+R+RBI was a generated column in SQLite that I deliberately didn’t copy. dbt rebuilds it, and it lands on the same totals.
Does the check have teeth? Before the real run I tested the script against a mock built from the same table definitions and deleted one row out of 720. Seven checks failed at once: row count, distinct keys, four column totals, and that season’s line. One dropped row can’t hide. Full reconciliation output →
Snowflake accepts primary and foreign key syntax but only enforces NOT NULL,
so in the new warehouse dbt tests do that job. Between those tests and one follow-up
query, four problems surfaced. None came from the migration. All four were already
true in the source, and SQLite never checked any of them.
The first build showed 7,832 games but only 5,101 final scores. Broken down by season and status, 2,730 of the gaps were 2025 games marked Final with no result row. The batting lines for those games existed, because a separate script had backfilled them. The step that writes final scores had simply never been run for that season.
It didn’t touch the hitter model, which scores against batting lines. It did mean anything joining to final scores for 2025, mainly the game-totals backtest, was coming up empty. One run of the pipeline’s existing catch-up mode pulled 2,844 box scores in about 56 minutes. Then I re-exported, reloaded, rebuilt and reconciled. Final scores now cover 7,831 of 7,832 games. The one left is a 2026 game that was never played.
| Values found | Dates | What they are |
|---|---|---|
OAK | 77 home games, 2024 | the A’s, before the ATH rename |
AL NL · IDs 159, 160 | 2024-07-16, 2025-07-15, 2026-07-14 | All-Star Games |
YOM HAN | 2025-03-15 to 16 | Tokyo exhibitions: Yomiuri, Hanshin |
MEM MTY | 2025-03-24 | exhibitions: Memphis, Monterrey |
A rename the dimension can’t represent. The A’s became
ATH for 2025. The team table stores current abbreviations only, so 77
Oakland home games from 2024 point at a code that no longer exists. That’s a
textbook slowly changing dimension. The fix is to keep the history, each name valid
for a date range.
Games that aren’t regular season. All-Star rosters and spring exhibitions account for 276 batting lines, and their 147 players were never in an MLB player table. That’s 0.17% of 157,853 lines, too few to move the backtest. But the table has no game-type field to filter them on, and it should.
Land raw, transform in the warehouse. RAW keeps the source exactly as it was. If a cast goes wrong in staging, the original value is still one layer down.
Don’t migrate derived values. Rebuild them, then check. Copying
total_runs and hrr would only prove the copy worked.
Rebuilding them and reconciling proves the logic matches too.
Constraints become tests. That’s not a workaround. It’s where the findings in Part 03 came from.
A key pair, not a password. Snowflake is retiring password-only sign-ins for scripts. The scripts and dbt log in as a service user that has no password at all.
The bug. The first export let pandas infer column types from the data. In testing, a column that was entirely NULL came out as text, which would have broken the load into a numeric column. The export now takes every type from SQLite’s declared schema and fails loudly if a value doesn’t fit.