Dimensional Modeling: The Star Schema

An OLTP schema (like the normalized employees/departments tables from earlier lessons) is optimized for fast, safe transactional writes. An analytics schema is optimized for the opposite: fast aggregation across huge volumes of historical data. Dimensional modeling — specifically the star schema — is the standard pattern for the latter.

Fact Tables vs Dimension Tables

• A fact table holds the numeric measurements you want to analyze (sales amount, quantity, duration) plus foreign keys to the dimensions that describe when/who/what. It's typically the largest table, with millions or billions of rows.
• A dimension table holds the descriptive attributes for one "thing" — customers, products, dates, stores. Small relative to the fact table, and usually where a Slowly Changing Dimension pattern (see the SCD lesson) gets applied.

Example: A Sales Star Schema

CREATE TABLE dim_date (
  date_key INTEGER PRIMARY KEY,  -- e.g. 20240615
  full_date DATE,
  year INTEGER,
  month INTEGER,
  day_of_week TEXT
);

CREATE TABLE dim_product (
  product_key SERIAL PRIMARY KEY,
  product_name TEXT,
  category TEXT
);

CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  customer_name TEXT,
  city TEXT
);

CREATE TABLE fact_sales (
  sale_id BIGSERIAL PRIMARY KEY,
  date_key INTEGER REFERENCES dim_date(date_key),
  product_key INTEGER REFERENCES dim_product(product_key),
  customer_key INTEGER REFERENCES dim_customer(customer_key),
  quantity INTEGER,
  revenue NUMERIC(12, 2)
);

Drawn out, dim_date, dim_product, and dim_customer surround fact_sales like points of a star — hence the name.

Why This Shape Is Fast for Analytics

SELECT d.year, p.category, SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY d.year, p.category
ORDER BY d.year, total_revenue DESC;

• Every dimension join is a simple equality on a small table — cheap, and easily indexed.
• Analysts can slice the same fact table by any dimension (by date, by product, by customer) without redesigning the schema.
• It maps naturally onto BI tools (Tableau, Power BI, Looker), which expect exactly this fact/dimension shape.

Star Schema vs Normalized (OLTP) Schema

	Normalized (OLTP)	Star Schema (Analytics)
Optimized for	Safe, fast writes	Fast aggregation/reads
Redundancy	Minimized	Deliberately denormalized within dimensions
Typical table count	Many small tables	A few large fact tables, several dimension tables
Example query	"Update this customer's address"	"Total revenue by category, by quarter"

Common Errors & Troubleshooting

• Fact table rows not matching any dimension row — a foreign key from fact_sales pointing at a product_key that doesn't (yet) exist in dim_product is a common ETL ordering bug; always load dimension tables before the fact table that references them.
• dim_date missing a date the fact table actually has data for — a pre-populated date dimension needs to cover the full range of dates you'll ever load; extend it (or generate it dynamically with generate_series(), from the Date Functions lesson) before it becomes a problem.
• Treating a dimension table as if it needs full normalization — dimension tables are intentionally somewhat denormalized (e.g. dim_product might repeat category_name rather than joining to a separate categories table) — this is a deliberate trade-off for query simplicity, not an oversight.

Key Takeaways

• Fact tables hold the numbers you aggregate; dimension tables hold the descriptive context you group and filter by.
• The star schema trades some normalization for query simplicity and speed — a deliberate choice for an analytics workload.
• This is the schema shape almost every BI/reporting tool is built to consume directly.