What is the difference between fact and dimension tables?

The data modeling pop quiz: which table gets the numbers? You have a fact_orders with amount, quantity and discount, a dim_customer with name, city and segment, and a column like unit_price circling both tables looking for a home. Eight columns to sort, and most people misplace at least two.

The two-question test

Any column, sorted in five seconds:

• Measured? Recorded per event, and you would SUM or AVG it: fact.
• Describes? A property of a person, product or place you GROUP BY: dimension.

Two rules keep the test honest:

• The grain: one fact row = one event. Say it out loud: "one row per order line".
• The keys: facts carry foreign keys to every dimension that gives them context.

The quiz, answered

Eight columns from an orders warehouse. Sort before you peek.

The last three rows are where the points are lost, and each one is a trap covered below.

The join pattern

Facts bring the numbers, dimensions bring the words. Every BI query is this:

SELECT d.city,
       SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_customer d
  ON f.customer_key = d.customer_key
GROUP BY d.city
ORDER BY revenue DESC;

Numbers from the fact, labels from the dimension. If you are summing a dimension column, stop: either the column is in the wrong table or the query is.

The trap: numbers in dimensions

Two famous ones. Both look reasonable, both bite later.

Price in dim_product. Prices change. Last year's orders now report this year's price the moment the catalog updates. Copy price into the fact at sale time, so each order remembers what was actually paid.

Age in dim_customer. Ages itself stale in twelve months. Store birth_date, compute age in the query.

The smell to memorize: a numeric column in a dimension that changes over time is a slowly changing fact in disguise.

Where these tables live

Sheet 007 (star vs snowflake) covers how fact and dimension tables are arranged into a schema; this one covers what goes inside them. Get the inside right first: a star schema full of misplaced columns is still a broken model.

The takeaway

Measured per event: fact, sum it. Describes a thing: dimension, group by it. State the grain out loud, keep the numbers that change over time in the fact, and every BI query reduces to the same join pattern. The two-question test, the full quiz and the join pattern fit on the print-ready PDF above.