Star Schema Data Model: How to Design One from Scratch

Every star schema models one business process. Not a department, not a report, not a dashboard. A business process is a measurable activity that generates data: sales transactions, website sessions, shipments, support tickets, ad impressions. The business process determines what your fact table records. If you skip this step, you end up with a vague 'everything table' that serves nobody well.

Start by asking: what is the event we want to analyze? An e-commerce company might have several business processes: orders, returns, page views, and inventory movements. Each gets its own star schema. Do not try to combine orders and returns into one fact table. They have different grains, different measures, and different dimension relationships. One business process, one star schema.

The grain defines what one row in the fact table represents. This is the single most important decision in the entire design. Get it wrong and every downstream query produces incorrect aggregations. The grain must be stated in plain language before you write a single column name.

Common grains include: one row per order line item, one row per daily session per user, one row per ad impression, one row per shipment. The grain determines the level of detail. A grain of 'one row per order' means you cannot answer 'which products were in each order' without a separate line-item fact table. A grain of 'one row per order line item' answers both order-level and product-level questions through aggregation.

Dimensions provide the descriptive context for each fact row: the who, what, where, when, and why. They are the axes along which analysts slice and filter data. For every fact row, ask: what descriptive information would an analyst need to filter or group by?

Start with the obvious dimensions: date (almost always present), customer, product. Then think about the business process more carefully. Does the order have a promotion applied? Add dim_promotion. Does it have a payment method? Add dim_payment_method. Was it fulfilled from a specific warehouse? Add dim_warehouse. Each dimension becomes a foreign key in the fact table and a separate dimension table with rich attributes.

Facts are the numeric, measurable values in each row. They represent the quantitative aspect of the business event. Revenue, quantity, duration, cost, count. Every fact should be classifiable as additive, semi-additive, or non-additive. This classification determines how the measure can be aggregated in reports.

Additive facts can be summed across every dimension: revenue, quantity, discount_amount. These are the most useful because analysts can aggregate freely. Semi-additive facts can be summed across some dimensions but not others: account_balance makes sense summed across customers but not across dates (you would not add Monday's balance to Tuesday's). Non-additive facts cannot be meaningfully summed: unit_price, conversion_rate, temperature. They must be averaged, weighted, or used in ratios.

Each dimension table is a denormalized, flat table with a surrogate key primary key and many descriptive attributes. Do not normalize dimensions into sub-tables. In a star schema, the product dimension includes category_name directly, not a foreign key to a separate categories table. This denormalization is intentional and is what makes the star schema efficient for analytics.

Rich dimension tables are the key to flexible analytics. A thin product dimension with only product_id and product_name forces analysts to join to additional tables for category, brand, and supplier information. A rich product dimension with 20+ attributes lets analysts slice by any attribute with a single join. Err toward more columns in dimensions, not fewer. Storage is cheap. Analytical flexibility is expensive to add later.

CREATE TABLE dim_product (
  product_key    INT PRIMARY KEY,  -- surrogate key
  product_id     VARCHAR(50),      -- natural key from source
  product_name   VARCHAR(200),
  category       VARCHAR(100),
  subcategory    VARCHAR(100),
  brand          VARCHAR(100),
  supplier       VARCHAR(200),
  price_tier     VARCHAR(20),
  is_active      BOOLEAN,
  effective_date DATE,
  expiry_date    DATE
);

The fact table contains foreign keys to every dimension table plus the numeric measures. The primary key is typically the combination of all foreign keys (the composite key) or a dedicated surrogate fact key. Every column in the fact table is either a foreign key or a measure. No descriptive text belongs in the fact table.

Keep the fact table narrow. Dimension keys are integers (4 bytes each). Measures are numeric types. A fact table with 6 dimension keys and 4 measures has only 10 columns. This narrow, tall structure is what columnar databases like Snowflake and BigQuery optimize for. Compression works best on columns with low cardinality (like dimension keys) and numeric columns with patterns (like prices).

CREATE TABLE fact_order_line (
  order_line_key   INT PRIMARY KEY,
  date_key         INT REFERENCES dim_date(date_key),
  customer_key     INT REFERENCES dim_customer(customer_key),
  product_key      INT REFERENCES dim_product(product_key),
  store_key        INT REFERENCES dim_store(store_key),
  promotion_key    INT REFERENCES dim_promotion(promotion_key),
  quantity         INT,
  unit_price       DECIMAL(10,2),
  discount_amount  DECIMAL(10,2),
  line_total       DECIMAL(10,2)
);

Skipping the grain declaration

Starting to list columns without first stating what one row represents. This leads to ambiguous fact tables where the same event might be counted multiple times. Always state the grain in one sentence before naming any columns.

Putting descriptive text in the fact table

Adding product_name or customer_email directly to the fact table instead of referencing dimension tables. This bloats the fact table, creates redundancy, and defeats the purpose of dimensional modeling. Only foreign keys and numeric measures belong in fact tables.

Using natural keys instead of surrogate keys

Referencing dimensions by business keys (customer_email, product_sku) instead of integer surrogate keys. Natural keys change (email addresses get updated, SKU formats change). Surrogate keys are stable. They also enable slowly changing dimensions and handle the 'unknown member' case.

Normalizing dimension tables

Creating a separate categories table referenced by the product dimension. This turns the star schema into a snowflake schema, adding joins and complexity. In a star schema, category_name belongs directly in the product dimension table. Denormalization is the point.