Dimensional Modeling: Star Schema, Snowflake Schema and Facts

Dimensional modeling is a way of organising analytical data into fact tables, which record measurable events such as orders, payments or page views, and dimension tables, which describe the who, what, where and when of those events, such as customers, products, stores and dates. Arranged as a star schema, it makes analytical queries simple and fast, and it gives every report the same definitions. It was popularised by Ralph Kimball and is still the backbone of most warehouses.

Facts, dimensions and grain

• Grain is the single most important decision: what does one row of the fact table represent? "One row per order line" is a grain. "Daily sales" is a different, coarser one. Declare it before choosing columns.
• Facts are the numeric measures at that grain: quantity, amount, discount. They're usually additive (you can sum them), semi-additive (balances: sum across accounts, not across days) or non-additive (ratios: compute from additive parts).
• Dimensions are the descriptive context you filter and group by: customer segment, product category, store region, calendar month. They're wide and change slowly.
• Foreign keys in the fact table point at each dimension's surrogate key, a meaningless integer the warehouse assigns, rather than the source system's ID.

The star schema

Queries become a fact table joined to a few dimensions, filtered and grouped by dimension attributes. That shape is easy for analysts to write, easy for BI tools to generate, and easy for warehouse engines to optimise.

-- Revenue by product category and month for one region
select d.month, p.category, sum(f.amount) as revenue
from fact_order_line f
join dim_date d    on f.date_key = d.date_key
join dim_product p on f.product_key = p.product_key
join dim_store s   on f.store_key = s.store_key
where s.region = 'South'
group by d.month, p.category;

Star schema vs snowflake schema

Columnar warehouses compress repeated values well, so the storage argument for snowflaking is weaker than it used to be. Most teams default to stars and snowflake only where a hierarchy is genuinely shared or huge.

Patterns you'll need

• Conformed dimensions: one dim_customer shared by the sales, support and marketing facts, so "active customer" means the same thing everywhere.
• Date dimension: a pre-built table with one row per day and attributes like weekday, fiscal period and holiday flags, so reports never compute calendars inline.
• Slowly changing dimensions: how a dimension keeps history when a customer moves city. See slowly changing dimensions.
• Fact table types: transaction facts (one row per event), periodic snapshots (one row per account per day) and accumulating snapshots (one row per order, updated as it moves through stages).
• Degenerate dimensions: identifiers like an order number kept on the fact table without their own dimension.
• Junk dimensions: low-cardinality flags (gift-wrapped, express shipping) bundled into one small dimension instead of cluttering the fact table.

In an interview, say the grain out loud before drawing any table. Most modelling mistakes, such as double counting and mismatched totals, come from mixing grains in one fact table.

Star schema vs one big table (OBT)

Interviewers often push on this: why not just flatten everything? Take orders, customers and products.

Where OBT shines: twenty people query "revenue by region and product category over the last 30 days" every day. Rather than each of them running a five-table join, the pipeline pre-joins once and they all scan one flat table.

Where it hurts: the business renames the category "Clothes" to "Apparel". In a star schema that's one row in dim_product. In OBT the category is copied onto every order, so millions of rows must be rewritten, and the same happens for every other attribute that changes (product names, regions). The update pipeline becomes a burden.

A common compromise: keep the star schema in silver as the source of truth and build a few OBT-style tables in gold for the hottest dashboards, rebuilt from silver so a rename is just a rerun. See medallion architecture and normalization vs denormalization.

Dimensional modeling vs other approaches

Third normal form models (sometimes the Inmon approach) keep an integrated, normalised enterprise layer and build marts from it. Data vault models separate hubs, links and satellites to absorb change from many sources. One big table (OBT) flattens everything into a single wide table for a specific use. They aren't exclusive: many platforms use a normalised or vault-style integration layer and serve dimensional marts on top, or derive wide tables from a star for dashboards.

Going deeper in this section

• The four-step design process: business process, grain, dimensions, facts.
• Fact table types: transaction, periodic and accumulating snapshots, factless facts, and additivity.
• Dimension table techniques: surrogate keys, role-playing, junk, mini and audit dimensions, late-arriving data.
• Hierarchies and bridge tables and conformed dimensions and the bus matrix.
• Two complete models, e-commerce and ride-hailing, and the interview guide.