Designing the Schema: Star vs. Snowflake
A normalized, transactional schema (Part 1) and an analytical schema built for a warehouse look different on purpose. This part is the how-do-I-write-the-DDL companion to the site's Dimensional Modeling & the Star Schema page.
Two table types, one pattern:
- Fact table — one row per event; mostly foreign keys and measures.
- Dimension table — the descriptive angles you slice by: customer, product, date.
- Surrogate key — a dimension's own auto-incrementing key, decoupled from the source system's ID.
- Snowflaking — splitting a dimension further when a sub-attribute is reused and changes independently.
- Star is the default. Snowflake only when there's a concrete reason.
Fact and Dimension Tables
Put one fact table in the middle with dimensions around it, and it looks like a star: fact_sales joined to dim_customer, dim_product, dim_date. A fact table records something that happened — one row per event or transaction (an order line, a page view) — and is mostly foreign keys pointing at dimensions plus numeric measures to aggregate (quantity, revenue). A dimension table holds the descriptive angles you slice and filter by — who, what, when, where — and is comparatively wide, with rich text columns, but far fewer rows than the fact table it supports. This split exists because an analytical query almost never needs "everything about a customer" and "everything about an order" tangled into one flat table — it needs to filter/group by a handful of dimension attributes while summing a handful of fact measures, and keeping the two shapes separate is what makes that fast and simple to write.
SQL · star schema DDL
-- DIMENSION: denormalized — city lives directly on the customer row
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY, -- surrogate key
customer_id INTEGER NOT NULL, -- source system's natural key
name VARCHAR(100) NOT NULL,
city VARCHAR(50),
membership_level VARCHAR(10)
);
-- DIMENSION: a pre-built date spine, standard in every star schema
CREATE TABLE dim_date (
date_key INTEGER PRIMARY KEY, -- YYYYMMDD as an integer, e.g. 20230115
full_date DATE NOT NULL,
year INTEGER NOT NULL,
month_name VARCHAR(10) NOT NULL,
quarter INTEGER NOT NULL
);
-- FACT: one row per order line item; mostly keys + measures
CREATE TABLE fact_sales (
sales_key INTEGER PRIMARY KEY,
customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),
product_key INTEGER NOT NULL REFERENCES dim_product(product_key),
date_key INTEGER NOT NULL REFERENCES dim_date(date_key),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
revenue DECIMAL(10,2) NOT NULL
);Surrogate Keys Matter More Than They Look
A surrogate key is a key the warehouse invents for itself — usually a simple auto-incrementing integer (customer_key) — instead of reusing the source system's own identifier (customer_id). It looks redundant at first (why not just use the ID that's already there?), but it decouples the warehouse from the source system: the same source customer_id can now correspond to multiple dimension rows over time, each with its own surrogate key, each representing what that customer looked like during a specific period. Give every dimension its own surrogate key, separate from the source system's natural key. This single decision is the prerequisite for Slowly Changing Dimensions (Part 8) — you cannot version a row's history if its key is the natural key, since a natural key by definition can only ever point at one row at a time.
SQL · a star-schema query
-- Join the fact to every dimension it needs; nothing more
SELECT c.city, p.category, d.month_name, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY c.city, p.category, d.month_name;When to Snowflake a Dimension
Snowflaking means normalizing a dimension one level further — pulling a repeated attribute (city, and everything that depends on it: province, country) out into its own sub-table, the same instinct that drives normalization in an OLTP schema (Part 1). The result, drawn out, looks like a snowflake instead of a plain star: the dimension branches into further dimensions. Split a dimension into a sub-table (e.g. dim_city → dim_customer) only when the sub-attribute is reused by many rows and changes independently of the parent dimension — a city's province practically never changes, but hundreds of customers all share the same one, so keeping it in exactly one place avoids updating it in hundreds of rows if it ever does. Most modern column-oriented warehouses compress repeated text well enough that the storage savings from snowflaking rarely justify the extra JOIN it costs on every query — star is still the default, and snowflaking should be a deliberate exception, not a habit carried over from OLTP design.
SQL · snowflaked dimension
CREATE TABLE dim_city (
city_key INTEGER PRIMARY KEY,
city VARCHAR(50) NOT NULL,
province VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL DEFAULT 'Canada'
);
CREATE TABLE dim_customer_snowflaked (
customer_key INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
city_key INTEGER REFERENCES dim_city(city_key),
membership_level VARCHAR(10)
);
-- Same question as the star version, one more JOIN required:
SELECT cust.name, city.city, city.province
FROM dim_customer_snowflaked cust
JOIN dim_city city ON city.city_key = cust.city_key;Star keeps city inline (one JOIN); snowflake splits it into dim_city (two JOINs) — only worth it when city's own attributes are reused and change independently.
One fact table, dimensions around it — that's the whole shape.
The takeaway: A star schema isn't more normalized or less normalized than an OLTP schema — it's shaped for a different job (fast aggregation across many rows, not fast single-row writes).
Runnable code for this part: sql-for-data-platforms on GitHub — Module 2 of 14.
← Back to Publications