An independent study reference written by Dr Phuc V. Nguyen. It is not official subject material — for assessment requirements always follow your subject outline and vUWS.

Data Foundationsintermediate

The star schema

Q: A fact table has a grain of one row per order line. Which measure can safely live in it?

The quantity on the line. Only a measure that is true at the declared grain belongs in the table. Quantity is a property of the line. The delivery fee belongs to the order and would be multiplied by the number of lines when summed, and lifetime value and rent belong to entirely different grains again.

Q: A retailer needs last year's regional reports to stay unchanged even when customers relocate. Which dimension treatment does that require?

Add a new dimension row with a new surrogate key and a date range, leaving old fact rows pointing at the old row. That is a type 2 slowly changing dimension. Existing facts keep pointing at the version current when the sale occurred, so historical reports stay stable while new sales attach to the new address.

A star schema organises analytical tables around one central fact table surrounded by dimension tables. The fact table holds the numbers you measure plus one foreign key per dimension, and it has a single declared grain, meaning what one row represents. Dimensions hold the descriptive attributes you filter and group by, kept deliberately wide and denormalised so a query joins once instead of walking a chain of lookup tables. The design trades some storage and some redundancy for query speed and, more importantly, for readability. A business user can see which table holds products and which holds dates without reading a data model.

Why it matters

Picture a receipt in the middle of a table with reference cards fanned out around it. The receipt is the fact: what was bought, how many, how much. Each card describes one thing the receipt points at, the product, the store, the customer, the date. To answer a question you pick up the receipt and one or two cards. You never have to trace a chain of cards to find out what a product category is called, because the card already says it.

Before you read on — recall

A fact table has a grain of one row per order line. Which measure can safely live in it?

Formulas

Populated combinations against the full grid

N_{\text{distinct}} \;\le\; \prod_{i=1}^{k} |D_i|

With

k

dimensions of sizes

|D_i|

, the number of possible combinations is their product, and only combinations that actually occurred appear in the fact table, so the count of distinct combinations

N_{\text{distinct}}

can never exceed that grid. How far below it sits is a question about the business rather than a rule. A schema with 12,000 products, 400 stores and 1,095 days allows about 5.3 billion combinations, and a chain whose average store carries a few thousand of those products populates a small share of them, while a schema whose dimensions are all exercised every day sits much closer to the grid. The row count is a separate question again. Several events can share one combination unless every key that distinguishes them sits in the schema, so a fact table can hold more rows than it has distinct combinations.

Grain decides what a row count means

N_{\text{rows}} = \sum_{o=1}^{O} L_o

At a grain of one row per product line, the table holds the total number of lines across all

O

orders, where

L_o

is the number of lines on order

o

. Counting rows therefore counts lines, not orders. To count orders you count distinct order keys, and confusing the two is one of the most common reporting errors in practice.

Worked examples

Scenario

A team builds a sales fact table at a grain of one row per order line, and includes the order-level shipping charge on every row.

Solution

Shipping is charged once per order, so summing it over lines multiplies it by the number of lines. An order with four lines and eight dollars of shipping reports thirty-two dollars. Two fixes work. Allocate the shipping charge across the lines so it sums correctly, or hold it in a separate fact table at order grain. The second fix only holds if each table is aggregated on its own grain first and the two results are then combined on the order key. Joining the order-grain table straight onto the line-grain table repeats the charge on every line again, which is the original fault in a new place. The rule that catches this before it ships is simple: every measure in a fact table must be true at the declared grain.

Scenario

A customer moves from Parramatta to Newcastle. The team overwrites the suburb in the customer dimension, and last year's regional sales report changes.

Solution

Overwriting is a type 1 change. It keeps only the current truth, so history is restated as though the customer had always lived in Newcastle. If last year's report must stay stable, the dimension needs a type 2 treatment: close the old row with an end date, insert a new row with a new surrogate key, and let each fact row point at the version that was current when the sale happened. The choice is a business decision about whether history is allowed to move.

Common mistakes

✗Denormalising dimensions is bad design because it repeats data. Normalisation protects update integrity where rows change constantly, and a warehouse dimension is written by one controlled pipeline, so read speed and readability are worth the redundancy.
✗The grain can be settled later. Grain determines which measures are valid and what a row count means, so choosing it after the table exists usually means rebuilding the table.
✗A snowflake schema is always better because it is more normalised. Snowflaking saves space on very large dimensions and helps with shared hierarchies, and it adds joins and makes the model harder for business users to read.
✗Facts are always individual transactions. Periodic snapshot facts, such as end-of-day inventory, and accumulating snapshot facts, which track a process through milestones, are also facts, and they behave differently when summed over time.

Revision bullets

•Fact table in the centre: measures plus one foreign key per dimension
•Dimensions hold descriptive attributes, wide and denormalised
•Grain is declared first: what one fact row represents
•Every measure in the table must be true at the declared grain
•Type 2 slowly changing dimensions preserve history, type 1 overwrites it
•Snowflaking normalises dimensions at the cost of extra joins and readability

Quick check

A fact table has a grain of one row per order line. Which measure can safely live in it?

A retailer needs last year's regional reports to stay unchanged even when customers relocate. Which dimension treatment does that require?

Connected topics

Data Warehouse ETL and ELT Metadata Measures & Dimensions Dashboards

More in Data Foundations

What Big Data Means Forms of Data Cloud for Analytics Fitness for Use Quality Dimensions Why Data Goes Bad Data Cleansing Sampling Bias

Sources

Kimball & Ross (2013)
Kimball, R., & Ross, M. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd ed. Wiley, 2013.
The standard treatment of grain, fact and dimension tables, and slowly changing dimension types.
Codd (1970)
Codd, E. F. "A Relational Model of Data for Large Shared Data Banks." Communications of the ACM, 13(6), 377-387, 1970.
The relational foundation whose normalisation goals a dimensional model deliberately relaxes for read performance.

How to cite this page

Dr. Phil's Quant Lab. (2026). The star schema. Derivatives Atlas. https://phucnguyenvan.com/concept/ba-star-schema