Facts, Dimensions, and Grain

Two kinds of table, one shape

Full 3NF is great for safe writes but painful for analytics: a "revenue by category by month" report might join six tables. The star schema (Ralph Kimball's dimensional model) is the disciplined denormalization that BI runs on. It has exactly two kinds of table:

• Fact table: narrow and tall. Holds the measures (numeric, additive things you sum: revenue, quantity) plus foreign keys to dimensions. One fact table, millions of rows, few columns. fact_sales(customer_sk, product_sk, date_sk, quantity, revenue_cents).
• Dimension tables: wide and short. Hold the descriptive context you filter and group by: dim_customer(customer_sk, name, country, segment), dim_product(product_sk, name, category, brand), dim_date(date_sk, date, month, year, weekday).

Drawn out, the fact sits in the center with dimensions radiating around it, hence the name star schema:

Schema

• customer_sk
• name
• country

• date_sk
• year_month

• sale_sk
• customer_sk
• date_sk
• product_sk
• revenue_cents

• product_sk
• category

• fact_salesn-1dim_customer— customer_sk
• fact_salesn-1dim_date— date_sk
• fact_salesn-1dim_product— product_sk

Grain: declare it first, always

The grain is the precise meaning of one fact row: "one row per order line item," or "one row per order," or "one row per customer per day." Everything depends on getting this right: a measure only makes sense at a stated grain, and mixing grains double-counts. Kimball's first rule: declare the grain before you add a single column. Our fact's grain: one row per order line item.

Worked example, a minimal star:

-- Dimensions: wide, descriptive, surrogate-keyed
CREATE TABLE dim_product (
    product_sk   INTEGER PRIMARY KEY,   -- surrogate
    product_id   INTEGER,               -- natural/business key
    product_name TEXT,
    category     TEXT
);
CREATE TABLE dim_date (
    date_sk INTEGER PRIMARY KEY,        -- e.g. 20260105
    date    TEXT,
    year_month TEXT                     -- 'YYYY-MM'
);
-- Fact: narrow, measures + FKs to dims, at line-item grain
CREATE TABLE fact_sales (
    sale_sk    INTEGER PRIMARY KEY,
    product_sk INTEGER REFERENCES dim_product(product_sk),
    date_sk    INTEGER REFERENCES dim_date(date_sk),
    quantity   INTEGER,
    revenue_cents INTEGER
);

A BI query is now a couple of joins from the fact out to the dims:

SELECT p.category, d.year_month, SUM(f.revenue_cents) AS revenue
FROM fact_sales f
JOIN dim_product p ON p.product_sk = f.product_sk
JOIN dim_date    d ON d.date_sk    = f.date_sk
GROUP BY p.category, d.year_month;

Anatomy of the star:

          dim_customer
                │
 dim_date ── fact_sales ── dim_product
                │
          (measures: quantity, revenue_cents; FKs: *_sk)

  fact  = numeric measures + dimension FKs, at ONE declared grain
  dim   = descriptive attributes you filter/group by, surrogate-keyed

Star vs snowflake

A star keeps each dimension flat (denormalized): dim_product holds category right on it. A snowflake normalizes dimensions further (dim_product → dim_category), saving space but re-introducing joins. Kimball's default is star: flatter dims, fewer joins, faster and simpler for analysts. Snowflake only when a dimension is huge and its sub-attributes are heavily reused.

Surrogate keys everywhere. Dimensions use surrogate *_sk keys, and the fact references those, not the business keys. This is what makes slowly-changing dimensions possible later: the surrogate can point at the version of a dimension valid when the fact happened.

In the warehouse this differs. This is the warehouse's home turf. Star schemas are the native modeling pattern of Snowflake, BigQuery, Redshift, and dbt projects. The SQL you write here is exactly what you'd write there (minus the un-enforced FKs). dim_date in particular is a warehouse staple: a pre-built calendar dimension every fact joins to.

Keep it readable / common pitfall. The #1 pitfall is not declaring the grain and then mixing grains in one fact (e.g. line-item rows and order-total rows). Every sum double-counts. State the grain in a comment at the top of the fact DDL. Second pitfall: stuffing descriptive text into the fact ("just this once"). Descriptions belong in dimensions; the fact stays numeric and narrow.

Recap: a star schema is one narrow measure-and-FK fact surrounded by wide descriptive dimensions, all surrogate-keyed, around a single declared grain: the model BI queries are built for; default to star (flat dims) over snowflake.

Execution mode: you write a multi-statement DDL+DML script. It runs against a fresh in-memory SQLite DB, then hidden assertion queries check the star's shape, grain, and that every fact FK resolves.