A data warehouse modeling technique that organizes data into a central fact table surrounded by dimension tables, optimized for fast analytical queries and BI reporting.
Star schema is the default pattern for analytical data warehouses and it earns that status. One central fact table (one row per business event: a sale, a click, a payment), surrounded by denormalized dimension tables (customer, product, date, store, channel). Analysts can pick up any BI tool and navigate the star without understanding complex joins or normalization theory. The fact-dimension vocabulary matches how business users think: "show me revenue by product category and region for Q3."
The classic data-warehouse debate was star vs. snowflake-schema (don't confuse this with Snowflake the company). Snowflake-schema normalizes dimensions into sub-tables to save storage. On 1990s on-prem hardware where storage was expensive, this mattered. On Snowflake/BigQuery/Databricks with columnar compression and cheap storage, the tradeoff flips: denormalized dimensions are faster to query, easier to understand, cheaper to operate. A flat dim_product with category, subcategory, brand, color, size joined once is faster than a normalized five-table snowflake that needs four joins per query. Default to star; go snowflake only for specific reasons (very large dimensions with low cardinality groups, or when dimension attributes change at very different rates).
A retailer I worked with had a 70-table normalized 3NF warehouse that the OLTP team loved. Analysts hated it — every query required knowing which six tables to join. After introducing a star-schema mart layer (15 fact tables, 20 dimension tables, all denormalized) built on top of the 3NF with dbt, analyst self-service exploded. Dashboards that previously required a ticket to a data engineer were now built by analysts in an afternoon. The 3NF still exists as the raw-integrated layer (it's good for maintaining source-of-truth relationships); the star schema is what analysts actually query. Lesson: match model shape to consumer needs. Analysts want stars; pipelines want 3NF or Data Vault; transactional systems want fully normalized. You can have all three in the same warehouse — that's what the medallion/staging/marts pattern is for.
The Star Schema is the most widely used data modeling pattern in data warehousing. Named for its star-like shape, it places a central fact table (containing measurable events) surrounded by dimension tables (containing descriptive attributes).
┌──────────┐
│ dim_date │
└────┬─────┘
│
┌──────────┐ │ ┌─────────────┐
│dim_product├──→ fact_sales ←──┤ dim_customer │
└──────────┘ │ └─────────────┘
│
┌────┴──────┐
│ dim_store │
└───────────┘fact_sales: revenue, quantity, discountfact_orders: order_amount, shipping_costfact_web_events: page_views, session_durationdim_customer: name, email, segment, regiondim_product: name, category, brand, price_tierdim_date: date, month, quarter, year, is_holiday-- "Total revenue by product category and quarter"
SELECT
d.quarter,
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.quarter, p.category
ORDER BY d.quarter, total_revenue DESC;| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Dimension Tables | Denormalized (flat) | Normalized (split into sub-tables) |
| Query Performance | Faster (fewer joins) | Slower (more joins) |
| Storage | More (redundant data) | Less (normalized) |
| Complexity | Simple to understand | More complex relationships |
| Best For | BI/Analytics queries | Storage-constrained environments |
Star schema mistakes I see in nearly every project:
dim_order_id table that exists only to hold the ID; that's a degenerate dimension, and it belongs inline.dim_date with pre-computed attributes: year, quarter, month, day-of-week, fiscal_period, is_weekend, is_holiday. Analysts will ask for every one of these; pre-computing is cheaper than doing date math in every query.dim_customer shared across many facts) are the power of dimensional modeling. When teams start building dim_customer_for_sales and dim_customer_for_support, you've lost consistency. Fix it with governance; don't let variants proliferate.A star schema is a dimensional data warehouse design with a central fact table (one row per business event — a sale, a click, a payment) connected to multiple denormalized dimension tables (descriptive context — customer, product, date, store). The diagram looks like a star: fact in the middle, dimensions radiating out. It's the most common data warehouse pattern because queries need few joins, grain is clear, analysts can self-serve in BI tools, and business users understand the vocabulary (facts = measurements, dimensions = context). Used with Snowflake, BigQuery, Redshift, Databricks, and virtually every BI tool.
In star schema, dimensions are denormalized (flat) — `dim_product` holds category, subcategory, brand, all in one table. In snowflake schema, dimensions are normalized — `dim_product` references `dim_category`, which references `dim_department`. Star is faster (fewer joins), simpler (easier to understand), and uses slightly more storage. Snowflake saves storage and better handles attributes that change at different rates. On cloud columnar warehouses, storage is cheap and compute is the cost driver, so denormalized star schema almost always wins. Go snowflake only for specific reasons (very large dimensions, drastically different change rates).
Use star schema for: analytical workloads (BI dashboards, self-service analytics, ad-hoc exploration), tables consumed by business users and analysts, workloads where query performance matters more than storage cost, and any time you're building a consumption-layer mart in dbt on Snowflake/BigQuery/Databricks. Don't use it for: OLTP systems (use 3NF normalized), source-integration layers (use 3NF or Data Vault), single-use reports (OBT may be simpler). The pattern has held up for 30+ years across warehouse platforms because it matches how business users naturally think about data.
A fact table stores quantitative, measurable data (revenue, quantity sold, clicks, session duration) — one row per business event. Fact rows contain foreign keys to dimensions plus numeric measures. A dimension table stores descriptive, categorical context (product name, customer region, date attributes, store location) — one row per business entity. Facts are typically long and narrow (billions of rows, few columns); dimensions are shorter and wider (thousands-to-millions of rows, many attributes). Queries join facts to dimensions via keys and aggregate measures sliced by dimension attributes.
Three main strategies: Type 1 (overwrite) — just update the dimension, losing history. Type 2 (history) — insert a new row with an effective-date range and a flag for the current version; historical facts still join to the version that was current at the time. Type 3 (previous value) — add a column that preserves the prior value, good for one change back only. Type 2 is most common for analytics, though it adds join complexity (you must match fact date to dimension effective range). Decide per dimension upfront; retrofitting Type 2 is extremely painful. `dbt_utils` and `dbt-snapshot` automate Type 2 tracking.
Conformed dimensions are dimension tables that are shared across multiple fact tables. One `dim_customer` serves `fact_sales`, `fact_returns`, `fact_support_tickets`, etc. This is the magic that lets you do cross-process analysis — "what's the refund rate by customer segment?" — by joining multiple facts to the same conformed dimension. It's the backbone of Kimball's dimensional modeling. When teams allow dimension variants to proliferate (`dim_customer_for_sales`, `dim_customer_for_marketing`), they lose this power and end up with inconsistent analytics.
Yes, arguably more relevant than ever. Cloud columnar warehouses (Snowflake, BigQuery, Databricks) love star schemas — denormalized dimensions compress well, joins on surrogate keys are fast, and the pattern maps directly to dbt's model-layering discipline (staging → intermediate → dim/fact marts). The one modern competitor is OBT (One Big Table) — pre-joined wide denormalized tables — which works well for specific use cases (ML feature tables, high-concurrency dashboards). But for general analytics and self-service BI, star schema remains the default and most mature modern dbt projects still use it.