Reading the Schema

The nbadb warehouse follows a star schema design. Every table has a prefix that tells you its role. Once you can read the prefix, you can navigate the entire warehouse without memorizing individual table names.

The warehouse table families

How star schema joins work

Dimensions sit at the tips of a star; facts sit in the centre. You always join from a fact outward to dimensions using surrogate keys like player_id or team_id.

SELECT p.full_name, t.abbreviation, f.pts, f.reb, f.ast
FROM   fact_player_game_traditional f
JOIN   dim_player p ON f.player_id = p.player_id
JOIN   dim_team   t ON f.team_id   = t.team_id;

Bridges resolve many-to-many relationships -- for example, bridge_game_official connects games to their three referees without duplicating rows in either table.

Navigating the docs pages

Reading the ER diagram

The auto-generated ER diagram uses standard notation:

• Boxes are tables. The header shows the table name; rows list columns.
• Lines show foreign-key joins. Follow them to see which dimension a fact references.
• Crow's-foot marks (the fork at one end) indicate a one-to-many relationship: one dimension row maps to many fact rows.

Start from a fact table you care about and follow every line outward to discover the dimensions you can join.

Column naming conventions

Every column name in the warehouse encodes its purpose through a suffix. Learn the suffixes and you can read any table without checking the data dictionary first.

Stat abbreviations in measure columns

Fact and aggregate tables use NBA-standard stat abbreviations as column names. Here are the most common ones:

Common pitfalls

Even experienced analysts trip over these four patterns. Tape-study them before writing your first query.

1. Joining through bridge tables

Bridge tables resolve many-to-many relationships. Always join dimension to bridge to fact -- not the other way around.

-- Correct: dim → bridge → fact
SELECT o.official_name, COUNT(*) AS games_reffed
FROM   dim_official o
JOIN   bridge_game_official bgo ON o.official_id = bgo.official_id
JOIN   fact_player_game_traditional f ON bgo.game_id = f.game_id
GROUP  BY o.official_name;

If you skip the bridge and try to join a dimension directly to a fact that lacks the foreign key, you will get zero rows or a silent cartesian product.

2. SCD2 temporal filters

Two dimensions use Slowly Changing Dimension Type 2 tracking: dim_player and dim_team_history. Each entity can have multiple rows -- one per historical version. The is_current flag marks the active row.

-- Always filter unless you want historical versions
SELECT full_name, team_id, position
FROM   dim_player
WHERE  is_current = true;

If you omit the is_current filter, you will double- or triple-count players who changed teams mid-career. Only drop the filter when you intentionally need the change history (for example, tracking when a player was traded).

3. Surrogate keys vs natural keys

The warehouse mints its own integer surrogate keys (player_id, team_id, game_id). The NBA API also assigns natural keys (person_id for players, team_id from the API). They are not the same value.

4. Season year format

Seasons are always stored as a hyphenated string, never an integer.

-- Correct
WHERE season = '2024-25'

-- Wrong -- this filters on the integer 1999, not a season
WHERE season = 2024

The format follows the NBA convention: the first year is when the season starts (October) and the second is when it ends (June). If you need to do arithmetic, extract the leading four digits and cast: CAST(LEFT(season, 4) AS INTEGER).

Suggested learning path

If you are new to nbadb, walk through these five stops in order. Each one builds on the last.

Stop 1 -- Read a dimension: dim_player

Open the schema page and scan the columns. Identify the surrogate key (player_id), the SCD2 versioning columns (player_sk, valid_from, valid_to, is_current), and the descriptive fields (full_name, position, is_active). This is your template for every dimension in the warehouse.

Stop 2 -- Read a fact: fact_player_game_traditional

This is the core box-score fact table. Find the foreign keys (player_id, team_id, game_id) and compare them to the dimension you just read. Then scan the measure columns (pts, reb, ast, fg_pct). Notice the grain: one row per player per game.

Stop 3 -- Join them together

Write a two-table join in the SQL Playground:

SELECT p.full_name, g.game_date, f.pts, f.reb, f.ast
FROM   fact_player_game_traditional f
JOIN   dim_player p ON f.player_id = p.player_id
JOIN   dim_game   g ON f.game_id   = g.game_id
WHERE  p.is_current = true
  AND  p.full_name = 'LeBron James'
ORDER  BY g.game_date DESC
LIMIT  10;

Pay attention to the is_current = true filter -- without it you would get duplicate rows from old SCD2 versions.

Stop 4 -- Read an aggregate: agg_player_season

This table rolls the per-game fact into season totals. Compare its grain (one row per player-season) to the fact's grain (one row per player-game). Aggregates are pre-computed so you do not need to write GROUP BY for common roll-ups.

Stop 5 -- Explore a bridge: bridge_game_official

Look at the columns: game_id and official_id. This table exists because each game has three referees -- a one-to-many relationship that cannot live on the fact without row duplication. Try joining dim_official through the bridge to fact_player_game_traditional to answer "how many games has each referee worked?"

After that progression, pick any analytics view (e.g., analytics_player_game_complete) and compare its wide, query-ready shape to the normalized tables you just explored. Analytics views are the "fast break" option -- pre-joined and ready for dashboards.

Where to go next