DATA ENGINEERING · QUESTION 91
What are fact and dimension tables in dimensional modeling?
On this page
A fact table records measurable events at a defined grain, such as one row per order line, and links to descriptive dimension tables such as customer, product, or date. Dimensional modeling makes common analytical questions easier to query. Define grain first, then choose measures and dimensions; unclear grain causes double counting and inconsistent reports.
LET'S MAKE IT SIMPLE
First, the idea in plain English
Analysts often ask questions such as sales by product, region, and month. A fact table stores the events and numeric measures; dimension tables provide the descriptive context used to group or filter them. The grain states exactly what one fact row represents and determines which totals are valid.
A USEFUL COMPARISON
Picture it this way
A receipt line is an event with a quantity and price, while product, store, and date records describe that event. If the receipt’s grain is unclear, adding up rows can count the same purchase more than once.
FOLLOW THE JOURNEY
Connect measurable events to descriptive dimensions
Explain fact-table grain, measures, keys, and dimension relationships.
This simplified visual highlights the main relationships. In practice, the exact design depends on the requirements; Many-to-many joins can silently multiply measures.
A fact table with one row per order line references customer, product, store, and date dimension tables through keys.
How to read this visual
This simplified visual highlights the main relationships. In practice, the exact design depends on the requirements; Many-to-many joins can silently multiply measures.
UNDER THE HOOD
How it works, step by step
Declare grain
Write what one fact row represents.
Why it matters: Every measure and join depends on this contract.
Record events
Store measures and dimension keys for each event.
Why it matters: Facts support aggregation across business activity.
Describe context
Keep reusable attributes in dimension tables.
Why it matters: Dimensions make filtering and grouping consistent.
Validate joins
Check key uniqueness and aggregate behavior.
Why it matters: Many-to-many joins can silently multiply measures.
OUTSIDE THE TEXTBOOK
A practical example
A retail sales fact table might contain one row per product on an order, with quantity and net amount plus keys for order date, store, and product. To report monthly revenue by category, the query groups facts by the date and product dimensions. Summing order-level totals after joining at line grain could double count.
GO A LITTLE DEEPER
Technical details that matter
- A star schema denormalizes dimension attributes around a central fact for simpler analytics.
- A fact table’s grain should be stated in business terms before selecting columns.
- Conformed dimensions provide consistent definitions across multiple fact tables.
MAKE A GOOD CHOICE
When is this useful?
- BI dashboards and recurring business metrics.
- Combining events with shared customer, product, and date attributes.
- Making aggregate queries understandable to analysts.
Normalized operational schemas suit transactional updates; analytical models may use different layouts for specialized workloads.
WATCH OUT FOR
Common misunderstandings
- Defining facts before deciding their grain.
- Mixing different grains in one table without clear semantics.
- Joining dimensions with duplicate keys and multiplying measures.
REAL-WORLD TRADE-OFFS
Limitations and failure cases
- Denormalized dimensions can repeat attributes.
- Slowly changing attributes need explicit historical rules.
- A dimensional schema may not fit every exploratory or graph workload.
WHAT TO REMEMBER
Key takeaways
- 1Facts record measurable events; dimensions describe their context.
- 2Define grain first.
- 3Validate joins and aggregation behavior to prevent double counting.
PRACTICE THE FOLLOW-UP
What might an interviewer ask next?
What is grain?
Grain is the precise business meaning of one row in a fact table, such as one product line per order.
What is a conformed dimension?
A dimension shared with consistent definitions and keys across multiple fact tables, enabling comparable analysis.
SAY IT OUT LOUD
How to explain this in an interview
“A fact table stores measurable events at a clearly defined grain, and dimensions provide descriptive context such as product, customer, or date. I define the grain first, then choose measures and keys. That prevents ambiguous rows and double counting when analysts join tables and aggregate results.”
Connect this question to the roadmap topic it supports.