Star, snowflake and fact constellation schemas
Clear study notes on star, snowflake and fact-constellation (galaxy) schemas: definitions, design rules, storage/performance trade-offs, worked design and cost examples, exam tips and quick checks.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
Two or three sentences: A data warehouse engineer designs schemas to store and query large analytic datasets efficiently. Star, snowflake and fact constellation schemas are standard multidimensional layouts used in OLAP and BI to support fast aggregation, reporting and maintenance; knowing when and how to use each affects query speed, storage use and ETL complexity.
Key ideas
Start from first principles and simple definitions.
- Data warehouse: a centralized repository for integrated, subject-oriented, time-variant, non-volatile data used for decision support. (Used here as the environment where schemas live.)
- Fact table: A relation that records measurable events (the facts) at a defined grain (the atomic level of detail). Each row is a measurement and contains foreign keys to dimensions plus numeric measures. Example measures: units sold (integer), revenue (currency). Measures can be additive, semi-additive (e.g., account balance additive across some dimensions but not time), or non-additive (e.g., ratios), which affects how you aggregate. Fact tables come in three common types: transaction (one row per event), periodic snapshot (regular time snapshots), and accumulating snapshot (rows that get updated across a defined process with milestones).
- Dimension table: A relation that stores contextual attributes used to slice and dice facts (e.g., Product, Time, Store, Customer). Dimensions are typically wide (many attributes) and relatively smaller than facts.
- Granularity / Grain: Exact statement: The grain of a fact table is the description of the single row in natural language that must be true for each row; e.g., "one row = sales of one product in one store on one date". Assumption: grain must be fixed and documented before schema design.
- Surrogate key: A database-generated simple integer key (often sequential) used as the primary key for dimension tables. Defined to avoid reliance on natural keys that may change. Best practice: use a surrogate key for each dimension and use it as the FK in the fact table.
Definitions of the three schemas (exact statements universities expect):
- Star schema: A schema where a single central fact table connects directly to multiple denormalized dimension tables; dimensions are typically not normalized and each dimension is one table. Assumes dimensions are denormalized for query simplicity; draw a central box (label FACT) with straight lines radiating to surrounding boxes (label DIM_PRODUCT, DIM_TIME, DIM_STORE, ...). Each arrow points from FACT to DIM.
- Snowflake schema: A schema derived from a star where some dimension tables are normalized into multiple related tables (a tree). Exact statement: A snowflake schema is a star schema in which one or more dimension tables have been normalized into two or more related tables, forming branches (sub-dimensions). Draw: central FACT box connected to a DIM_PRODUCT table, which in turn points to PRODUCT_SUBCATEGORY, and PRODUCT_SUBCATEGORY points to PRODUCT_CATEGORY.
- Fact constellation (galaxy) schema: A schema with multiple fact tables that may share dimension tables. Exact statement: Two or more fact tables (each with its own grain) coexist and reference common dimension tables. Draw: two FACT boxes (e.g., SALES_FACT, RETURNS_FACT) both pointing to shared DIM_PRODUCT and DIM_TIME.
Design rules and assumptions commonly tested:
- State the grain first. Without grain you cannot design correct facts and dimensions. (Assumption: grain determines candidate keys and aggregation semantics.)
- Use surrogate keys in dimensions; natural keys may be kept as attributes for lookups. (Rationale: stability and performance.)
- Keep slowly changing dimensions (SCD) strategy in mind; SCD types (Type 1, Type 2, Type 3) affect schema content but not basic shape.
- Denormalize dimensions (star) for query speed; normalize (snowflake) for reduced storage and cleaner hierarchies. Trade-off: extra joins in snowflake vs larger dimension rows in star.
- Use fact constellation when multiple processes produce related facts and you want shared dimensions to reduce duplication and simplify cross-fact analysis. Ensure shared dimensions are conformed (same surrogate keys and consistent attribute definitions) to support correct cross-fact joins and comparisons.
What to draw in a typical exam diagram (exactly):
- For star: draw a central rectangle labeled SALES_FACT with arrows to four rectangles around it labeled DIM_PRODUCT, DIM_CUSTOMER, DIM_STORE, DIM_TIME. Inside SALES_FACT list PK (FactID or composite key of the FKs at the chosen grain), FK_Product, FK_Customer, FK_Store, FK_Time, Measures: UnitsSold, Revenue.
- For snowflake: draw as star, but expand DIM_PRODUCT into DIM_PRODUCT -> DIM_PRODUCT_CATEGORY (arrow from product to category). Label attributes: ProductID, ProductName, CategoryID in product; CategoryID, CategoryName in product_category.
- For fact constellation: draw two central rectangles SALES_FACT and INVENTORY_FACT, both pointing to DIM_PRODUCT and DIM_TIME. Clearly label grains for each fact table.
Formulas
Write simple cost/size formulas and syntax to remember.
Storage_fact = N_rows_fact × RowSize_fact (bytes) — N_rows_fact: number of fact rows; RowSize_fact: average bytes per row (includes FK sizes + measures). Unit: bytes. Applies to rough storage estimation.
RowSize_fact = Σ size(FK_i) + Σ size(measure_j) + overhead — overhead: row header, null bitmaps. Unit: bytes.
Redundancy_factor = 1 - (Normalized_storage / Denormalized_storage) — fraction saved when normalizing. Unitless. Useful when comparing star vs snowflake storage.
SQL syntax to remember for analytic queries on these schemas:
SELECT d.attr, SUM(f.measure) FROM fact f JOIN dim d ON f.dim_id = d.id GROUP BY d.attr; — typical aggregation pattern.
SELECT <dims>, SUM(f.measure) FROM fact f JOIN dim1 ... GROUP BY CUBE(dim1.attr, dim2.attr); — GROUP BY CUBE syntax (if DB supports it) for multi-dimensional aggregates.
Worked examples
Example 1 — Star schema design and storage estimate (numerical): Given: A retail warehouse records 10 million sales transactions per month. Each sale row grain = one product sold in one store on one date. Facts per month N = 10,000,000. Dimensions: Product (50,000 rows), Store (5,000 rows), Customer (1,000,000 rows), Time (365 rows per year; store daily).
Step 1 — Define fact table columns: sale_id (surrogate PK, 4 bytes int), prod_key (4 bytes), store_key (4 bytes), cust_key (4 bytes), time_key (4 bytes), units_sold (2 bytes int), revenue (8 bytes float).
Step 2 — Compute RowSize_fact (approx): 4+4+4+4+4+2+8 = 30 bytes. Add overhead ~ 14 bytes => 44 bytes per row.
Step 3 — Storage_fact = N_rows × RowSize = 10,000,000 × 44 bytes = 440,000,000 bytes ≈ 420 MiB (≈ 440 MB). Answer: 440,000,000 bytes (~420 MiB, ~440 MB).
(Notes: overhead and types vary by DB — check DB-specific row header sizes.)
Example 2 — Converting star to snowflake and compare storage qualitatively (design exercise): Given the same Product dimension with attributes: ProductID, Name, Brand, Subcategory, Category, Supplier. In a star we store all attributes in DIM_PRODUCT. In a snowflake we split DIM_PRODUCT into DIM_PRODUCT, DIM_SUBCATEGORY, DIM_CATEGORY, DIM_SUPPLIER.
Step-by-step: list attributes in each table, set PKs and FKs (e.g., DIM_PRODUCT.subcat_id -> DIM_SUBCATEGORY.subcat_id). Explain benefits: normalized categories avoid repeating CategoryName per product, saving space if many products share categories. Explain cost: queries for CategoryName require two joins instead of one (more CPU and IO).
Example 3 — Fact constellation (model answer to typical exam question): Question: Draw and explain a schema with two facts: SALES (grain: one product sold per invoice line) and RETURNS (grain: one product returned per return receipt), sharing Product and Time dimensions.
Answer (draw and explain): draw SALES_FACT and RETURNS_FACT, both with FK product_key, time_key. Show measures: SALES: units_sold, revenue; RETURNS: units_returned, refund_amount. Explain: shared DIM_PRODUCT and DIM_TIME reduces duplicate dimension data; cross-fact queries (e.g., net sales = SUM(sales.revenue) - SUM(returns.refund)) are easy because both facts use same product and time keys. Mention caution: grains differ can complicate direct row-level joins — aggregate to common grain (e.g., product-day) before joining.
Common mistakes
- Normalizing dimensions blindly: students normalize everything thinking normalization is always good — this harms OLAP query performance.
- Not accounting for slowly changing dimensions (SCD) — forgetting to specify SCD type leads to incorrect historical queries.
- Confusing fact tables and dimension tables: facts store measures; dimensions store descriptive attributes.
- Not documenting degenerate dimensions (e.g., invoice number stored in the fact table without a separate dimension table — a degenerate dimension).
For your exam
Kinds of questions: definition questions (define star/snowflake/fact constellation), short notes (advantages/disadvantages), diagram drawing (draw star, snowflake, galaxy), derivations (space calculation), design questions (given requirements, design schema and explain grain), SQL aggregation queries on schema, short programs or DDL to create tables.
What full-marks answers include:
- Clear statement of grain first.
- Diagram labeled exactly as requested (show PKs and FKs, measures and attributes). If converting star ↔ snowflake, show affected tables and FKs.
- Advantages and disadvantages bullet list with reasons (storage, query performance, ETL complexity).
- If asked for cost estimate, show formulas, list assumptions (data types, overhead), show step-by-step calculation and final numeric answer with units.
- For fact constellation, mention potential problems (incompatible grains, need to aggregate) and show an example cross-fact query.
Quick check
- What is the grain of a fact table and why is it important?
- Give two reasons to use a star schema instead of a snowflake schema.
- In a fact constellation with SALES and RETURNS facts sharing DIM_PRODUCT, how do you compute "net sold" per product per day?
Answers: 1) The grain is the single-row level of detail (e.g., one sale of one product in one store on one date); it's important because it defines keys, aggregation semantics and ETL requirements. 2) Star schema offers fewer joins (faster query performance) and simpler SQL for analysts; it also simplifies BI tool metadata. 3) Aggregate SALES.units_sold and RETURNS.units_returned to the common grain (product-day) then compute net = SUM(sales) - SUM(returns) (or use a JOIN on product and date after aggregation if grains differ).
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?