What is a fact table, and how does it differ from a dimension table in a data warehouse?
Instruction: Define both fact and dimension tables and explain their differences.
Updated
Official Answer
A fact table records business events or measurements at a declared grain, such as one row per order line. It commonly contains dimension keys and measures such as quantity and sales amount. A dimension table supplies descriptive context used to group or filter those facts, such as product name, category or customer region.
Declare the grain before choosing measures. Mixing order totals with order-line rows can double-count revenue. Measure aggregation also matters: revenue may be additive across appropriate dimensions, while an account balance is not additive across time. A factless fact table can record an event or relationship without a numeric measurement.
For example, order-line facts can reference product and date dimensions, allowing sales to be summarized by product category and month. The model must define whether historical reports use the category recorded at the time or the product's current category.