The star schema for engineers

Gold is where we model. The star schema is the simplest shape that answers most business questions: one fact table in the middle, one dimension per descriptive axis around it. Each question becomes one join, which is why BI tools were built for this shape.

A star schema for bug reports

One fact at the center. Four dimensions, each joinable with a single foreign key. Notice there are no chains of joins between dimensions.

Fact in the center, dimensions around. One hop per business axis.

Every dimension has a surrogate key, usually called something like dim_team_sk. The upstream identifier lives alongside as a natural key column. The fact table joins on surrogate keys only. This matters because upstream ids can change, be reused, or collide across sources. Surrogate keys stay stable within your warehouse.

data_engineering.ipynb
sql
CREATE OR REPLACE TABLE dim_team AS
SELECT
  ROW_NUMBER() OVER (ORDER BY team_id) AS dim_team_sk,
  team_id                              AS team_natural_key,
  ANY_VALUE(team_handle)               AS team_handle,
  MIN(submitted_at)                    AS first_seen_at,
  MAX(submitted_at)                    AS last_seen_at
FROM silver
WHERE team_id IS NOT NULL
GROUP BY team_id;

A dimension table built from silver with ROW_NUMBER generating the surrogate key. The natural key is preserved so we can still trace back to source. The dimension has one row per distinct business entity, not one row per event.

Because team_id comes from upstream and is not yours. Upstream can merge two teams and reuse an id, split a team and invent a new one, or deprecate ids and reissue them. A surrogate key you mint in your warehouse is immune to those games. Your dashboards stay stable across source-system migrations.

dim_reporter and dim_weakness follow the exact shape you just wrote for dim_team: swap in reporter_id or weakness_id as the natural key and let ROW_NUMBER mint the surrogate. Write those in your notebook now. The date dimension is the only one with a twist, because it is distilled from the calendar in silver rather than from a business entity.

data_engineering.ipynb
sql
CREATE OR REPLACE TABLE dim_date AS
SELECT
  ROW_NUMBER() OVER (ORDER BY d.date_day) AS dim_date_sk,
  d.date_day,
  EXTRACT(year FROM d.date_day)    AS year,
  EXTRACT(quarter FROM d.date_day) AS quarter,
  EXTRACT(week FROM d.date_day)    AS week
FROM (
  SELECT DISTINCT CAST(submitted_at AS DATE) AS date_day
  FROM silver
) d;

The date dimension. Same ROW_NUMBER surrogate-key move, but the distinct days in silver are the entity. The extracted year, quarter, and week columns are what make time-bucketed questions one join instead of a pile of date math.

data_engineering.ipynb
sql
CREATE OR REPLACE TABLE fact_report AS
SELECT
  s.id                                          AS report_natural_key,
  dt.dim_team_sk,
  dr.dim_reporter_sk,
  dw.dim_weakness_sk,
  dd.dim_date_sk,
  s.severity,
  s.state,
  s.bounty_amount
FROM silver s
LEFT JOIN dim_team     dt ON dt.team_natural_key     = s.team_id
LEFT JOIN dim_reporter dr ON dr.reporter_natural_key = s.reporter_id
LEFT JOIN dim_weakness dw ON dw.weakness_natural_key = s.weakness_id
LEFT JOIN dim_date     dd ON dd.date_day             = CAST(s.submitted_at AS DATE);

The fact table. One row per report event. Every descriptive axis is a surrogate-key foreign key. Numeric measures like bounty_amount live on the fact directly because they are per-event truths.

data_engineering.ipynb
python
CHECKS.append((
    'no-orphaned-fact-keys',
    '''SELECT * FROM fact_report
       WHERE dim_team_sk IS NULL
          OR dim_reporter_sk IS NULL
          OR dim_weakness_sk IS NULL
          OR dim_date_sk IS NULL
       LIMIT 5''',
))

failures = run_checks(con)
assert not failures, 'referential integrity check failed'

The referential check we promised back at the quality gate can finally run, because the fact table now exists. Append it to the same CHECKS list: a fact row whose foreign key never resolved to a dimension is an orphan, and orphans fail the build.

Quiz: Quiz

Loading practice…

AI prompt: Try it: design a star for your own data

Loading practice…