Skip to main content

Star Schemas

A star schema places a fact table at the center and joins it directly to descriptive dimensions. The shape makes common analytical queries legible: filter and group by dimensions, aggregate facts. A conformed dimension can support more than one process when its keys, attributes, and history policy share the same meaning.

Prerequisites​

Complete Facts, Dimensions, and Grain and Window Functions.

Learning goals​

  • Read a star query from filters through joins to aggregation grain.
  • Distinguish conformed dimensions from merely similar tables.
  • Prevent fanout and mixed-grain errors.
  • Decide when a small snowflake or bridge table is justified.

Worked example​

The event fact joins one customer dimension row through customer_key. The output grain is one date and region, not one event.

SELECT
  CAST(f.occurred_at AS DATE)::VARCHAR AS event_date,
  d.region,
  COUNT(*) AS event_count,
  SUM(f.amount_cents)::INTEGER AS amount_cents
FROM fct_event AS f
JOIN dim_customer AS d USING (customer_key)
GROUP BY event_date, d.region
ORDER BY event_date, d.region;

The four rows preserve both dates and both regions. Starting from the fact constrains the population; joining only one row per dimension key prevents fanout. Validate those cardinalities before trusting totals.

A date dimension can add fiscal periods and holiday labels, but generated attributes still need a business owner. Degenerate dimensions such as an order number can remain on the fact when they have no useful descriptive table. Many-to-many relationships, such as an event with several campaigns, need a bridge and an allocation rule if measures are summed.

Limitations and common mistakes​

  • A conformed dimension is a semantic agreement, not just a shared column name.
  • Flattening every hierarchy can increase maintenance without helping queries.
  • Role-playing dates need clear aliases such as occurred_date and arrived_date.
  • A many-to-many bridge can double count facts unless weighting or distinct population rules are explicit.
  • Current dimension attributes can rewrite historical groupings; choose the history requirement deliberately.

Practice​

Sketch a star for support tickets. Declare the fact grain, three dimensions, one degenerate dimension, and the treatment of tickets tagged with multiple topics.

Next steps​

Continue with Slowly Changing Dimensions to preserve or overwrite changing descriptions deliberately.

Primary sources​

Checked 2026-10-15: Kimball Group basic dimensional modeling techniques, conformed dimensions, and multivalued dimensions and bridge tables.