Data Modeling for Analytics: Star Schemas, Facts and Dimensions

A data warehouse full of raw tables isn't an analytics platform. Analysts end up writing 200-line queries with a dozen joins, getting slightly different answers each time. Data modeling turns raw data into structures that are intuitive to query, fast to aggregate and consistent across every dashboard.

The most widely used approach for analytics is dimensional modeling, built around star schemas. Here's how it works.

#Key takeaways

• A star schema has a central fact table (measurable events) surrounded by dimension tables (descriptive context).
• Always define the grain, what one row in the fact table represents, before anything else.
• Facts hold numbers you aggregate; dimensions hold attributes you filter and group by.
• Slowly changing dimensions (SCD) preserve history when attributes change.
• Good models make the right answer the easy answer for analysts and BI tools.

#Facts and dimensions

Fact tables record business events or measurements:

• Order line items, payments, page views, support tickets, shipments.
• Contain foreign keys to dimensions and numeric measures (quantity, revenue, duration).
• Usually long and narrow: many rows, relatively few columns.

Dimension tables describe the context of those events:

• Customers, products, dates, stores, campaigns, employees.
• Contain descriptive attributes (name, category, region, segment).
• Usually shorter and wider.

#A star schema example

text                  dim_date
                     │
dim_customer ── fct_order_lines ── dim_product
                     │
                  dim_store

sqlCREATE TABLE fct_order_lines (
  order_line_id   BIGINT PRIMARY KEY,
  order_id        BIGINT,
  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),
  quantity        INT,
  unit_price      NUMERIC(12,2),
  discount_amount NUMERIC(12,2),
  net_revenue     NUMERIC(12,2)
);

CREATE TABLE dim_product (
  product_key   INT PRIMARY KEY,     -- surrogate key
  product_id    TEXT,                -- natural/business key
  product_name  TEXT,
  category      TEXT,
  subcategory   TEXT,
  brand         TEXT
);

A typical question becomes a simple, fast query:

sqlSELECT d.year_month, p.category, SUM(f.net_revenue) AS revenue
FROM fct_order_lines f
JOIN dim_date d    ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.year = 2026
GROUP BY 1, 2
ORDER BY 1, 2;

It's called a star because the diagram looks like one: the fact in the middle, dimensions radiating out.

#Step 1: Declare the grain

The grain is the precise meaning of one fact row: "one row per order line item", "one row per daily account balance", "one row per page view".

Get the grain wrong and everything downstream breaks. Mixing order-level shipping fees into a line-item fact table, for example, double-counts shipping when you sum. Rules:

• Choose the most atomic grain practical. You can always aggregate up, but never drill down below the grain.
• Every measure must be true at that grain.
• Different grains mean different fact tables (fct_orders vs fct_order_lines).

#Step 2: Identify dimensions

Ask: how do people describe, filter and group this event? By when (date), who (customer), what (product), where (store/region), how (channel, payment method), why (campaign).

The date dimension deserves special mention. Pre-computing attributes like day of week, fiscal quarter, holiday flags and ISO week makes time-based analysis trivial and consistent.

#Step 3: Identify measures

Measures come in three flavors:

Store the components (revenue and cost) rather than derived ratios (margin %), and compute ratios at query time.

#Star vs snowflake schema

In a snowflake schema, dimensions are normalized into sub-tables (dim_product → dim_category → dim_department).

Modern columnar warehouses compress repeated values efficiently, so the storage argument for snowflaking is weak. Star schemas are the default for analytics.

#Slowly changing dimensions (SCD)

Customers move, products get recategorized, sales reps change territories. How should history behave?

• Type 1: Overwrite. Keep only the current value. Simple, but history is rewritten: last year's revenue by region shifts when a customer moves.
• Type 2: Add a new row. Keep full history with validity dates:

textcustomer_key | customer_id | region | valid_from | valid_to   | is_current
101          | C-42        | EMEA   | 2024-01-01 | 2026-03-31 | false
187          | C-42        | APAC   | 2026-04-01 | 9999-12-31 | true

Facts link to the surrogate key that was current when the event happened, so historical reports stay accurate.

• Type 3: Add a "previous value" column. Tracks one level of history; rarely used.

Use Type 2 for attributes where historical accuracy matters (region, segment, pricing tier) and Type 1 for corrections (typos).

#Surrogate keys vs natural keys

• Natural keys come from source systems (customer_id = "C-42").
• Surrogate keys are warehouse-generated identifiers (customer_key = 187).

Surrogate keys enable SCD Type 2, insulate the warehouse from source-system key changes, and let you integrate multiple sources with conflicting IDs.

#Modeling layers in practice

With ELT tools like dbt (dbt explained), a common structure is:

textstaging/        stg_shop__orders, stg_crm__customers   (clean, rename, cast)
intermediate/   int_orders_enriched                    (joins, business logic)
marts/          fct_order_lines, dim_customer, dim_product   (star schemas)

Some teams also build wide, denormalized tables ("one big table") on top of the star schema for specific dashboards. That's fine as a serving layer, as long as it's derived from the conformed model.

#Conformed dimensions

When multiple fact tables (orders, returns, support tickets) share the same customer and date dimensions, analysts can combine them consistently: "support tickets per customer segment vs revenue per segment". Conformed dimensions are what turn separate reports into an integrated platform.

#Common mistakes

1. Undefined grain, leading to double-counting.
2. Measures at the wrong grain, like order-level fees on line items.
3. Storing ratios instead of components.
4. Ignoring history: Type 1 everywhere, then historical reports silently change.
5. Business logic duplicated in every dashboard instead of in the model.
6. No tests on keys and relationships (data quality guide).

#FAQ

#Is dimensional modeling still relevant with modern cloud warehouses?

Yes. Warehouses make joins and scans fast, but the main benefits of star schemas are clarity, consistency and reusable definitions. Those matter regardless of engine performance.

#What's the difference between a star schema and a normalized OLTP schema?

OLTP schemas (third normal form) minimize redundancy for fast, consistent transactional writes. Star schemas denormalize descriptive data for simple, fast analytical reads. Different workloads, different designs.

#What is "One Big Table" modeling?

A wide, denormalized table combining facts and dimension attributes for easy consumption. It's convenient for specific use cases, but it's best built from a conformed dimensional model rather than replacing it.

#How do I handle many-to-many relationships?

Use a bridge table between the fact (or dimension) and the multi-valued dimension, such as orders with multiple promotions. Be careful with allocation and double-counting when aggregating across the bridge.