Dimensional Modelling for Interviews: Facts, Dimensions and Choosing the Grain
A grocery app order looks simple on your phone: one cart, one payment, one delivery. Inside the analytics warehouse, that same order can become many rows - one per item, one per payment attempt, one per delivery event - and choosing the wrong row meaning can quietly break every dashboard built on it.
- Dimensional modelling designs analytics data around business processes, measurable facts and descriptive dimensions.
- A fact is a measurable business event, such as sales amount, quantity, discount or delivery time.
- A dimension gives context to facts, such as customer, product, date, city, store, campaign or payment method.
- Grain is the exact meaning of one row in a fact table. Say it as: “one row per...”
- The safest design rule: declare grain first, then choose dimensions, then choose facts.
- Never mix order-level and order-item-level measures in the same fact table unless you know exactly how aggregation will work.
- The interview-winning phrase: “I would keep the fact table at the lowest useful atomic grain and roll up through dimensions.”
Big Picture: Dimensional Modelling Turns Events into Decisions
Dimensional modelling is the bridge between raw operational systems and business dashboards. It asks: what process are we measuring, what does one row mean, what numbers sit in that row, and which lenses will leaders use to slice those numbers?
Core Explanation: Facts, Dimensions and Grain
The simplest way to think about a dimensional model is: facts are the numbers, dimensions are the labels, and grain is the contract. A sales dashboard may show revenue by city, category and month. Revenue is the fact. City, category and month are dimensions. “One row per order line item” is the grain.
1. Fact Tables: Where the Measurements Live
A fact table stores measurable events from a business process. It usually contains foreign keys to dimension tables and numeric measures that can be aggregated.
Most interview cases expect you to start with a transaction fact because it preserves the atomic event. You can roll it up later, but you cannot recover detail that was never stored.
2. Dimension Tables: The Business Lenses
A dimension table stores descriptive attributes used to filter, group and explain facts. Good dimensions sound like the way managers ask questions: by date, customer segment, product category, store, channel, campaign, city or payment method.
3. Grain: The Row-Level Promise
Grain is the exact business meaning of one row in a fact table. It must be written before you list facts or dimensions. A good grain statement is short and testable: “one row per payment attempt,” “one row per order item,” or “one row per SKU per warehouse per day.”
4. The Correct Sequence for Designing a Fact Table
5. Additive, Semi-Additive and Non-Additive Facts
Not every metric behaves the same way. A candidate who says “we will just sum everything” signals weak data judgement.
A UPI payments company such as PhonePe observes payment attempts, success or failure outcomes, banks, merchants, devices and timestamps. A strong dimensional model would keep a transaction-attempt fact at atomic grain so success rate can be diagnosed by bank, hour, merchant category and failure reason. The primary driver of useful analysis is the atomic transaction grain, supported by clean conformed dimensions for merchant, bank, customer and date. So what: dimensional modelling turns operational events into debuggable management metrics.
6. How to Check Whether Your Dimensional Model Is Healthy
Use these checks when you are asked how you would validate a dimensional model. The goal is not just a neat schema - it is trustworthy analytics.
Definitions You Can Say in One Breath
- Dimensional modelling: An analytics design approach that organizes business measurements into fact tables and descriptive context into dimension tables.
- Fact: A numeric measurement of a business event at a declared grain.
- Dimension: Descriptive context used to filter, group or explain facts.
- Grain: The exact business meaning of one row in a fact table.
- Star schema: A dimensional design with one central fact table linked directly to denormalized dimension tables.
The Kimball Group's widely taught design principle is simple: declare the grain before selecting dimensions and facts. In interviews, that principle is often more important than remembering every table type.
Myntra: Choosing the Right Grain for Fashion Commerce Analytics
Myntra is a useful Indian example because fashion commerce generates order, item, discount, return and delivery questions that break if the grain is chosen carelessly.
Situation: A fashion marketplace has questions at multiple levels. Leadership wants revenue by category, brand and sale event. Category managers want discount depth by style and size. Operations wants delivery delays and returns by city. Finance wants net revenue after discounts and returns.
The modelling move: The tempting model is “one row per order” because customers see one order confirmation. But fashion orders often contain multiple items, sizes, prices, discounts and return outcomes. A stronger analytical design keeps the core sales fact at order-item grain: one row per product item in an order. Order-level attributes such as order number can be carried as a degenerate dimension, while item-level facts such as selling price, discount and quantity stay accurate.
Outcome or lesson: The primary driver of reliable analytics is the order-item grain. Supporting drivers are conformed product and date dimensions, separate handling of returns, and clear additivity rules for discounts and revenue. The “so what” is powerful: the same business can answer CEO-level revenue questions and category-manager-level merchandising questions without rebuilding the data model.
How AI Changes Dimensional Modelling in 2026
AI does not remove the need for dimensional modelling. It raises the cost of getting it wrong, because more users now query data through natural language and expect the system to understand business meaning.
- Natural-language BI depends on semantic clarity: Tools that convert questions into SQL need clear grain, metric definitions and relationships. If “orders” sometimes means order header and sometimes order item, an AI assistant will produce confident but wrong answers.
- AI helps detect grain and data-quality issues: Machine learning can flag sudden changes in unknown dimension rate, duplicate grain keys, reconciliation variance or unusual fact distributions before dashboards mislead users.
- LLMs accelerate documentation, not judgement: An LLM can draft table descriptions, join paths and metric glossaries, but the analyst must still decide the business grain and additivity rules.
Load the company's annual report, a sample dashboard screenshot and your schema notes into NotebookLM. Ask: “List likely interview questions about the grain of this model, possible double-counting risks and which dimensions should be conformed.” Then rehearse a 90-second answer using the grain-first structure.
Interview Relevance
“Suppose you are designing a data mart for an e-commerce company. How would you model orders, products, customers and revenue? What grain would you choose?”
Use the phrase “lowest useful atomic grain.” It signals that you understand both flexibility and practicality: keep enough detail to roll up, but not irrelevant technical noise.
Common Mistake
The mistake: candidates list facts and dimensions before declaring grain. This costs them because the same metric can mean different things at order, order-item, daily snapshot or customer levels. One-line fix: always say “my grain is one row per...” before naming facts or dimensions.
What to Revise Next
Once facts, dimensions and grain are clear, move to the physical schema choices and history-handling problems that interviewers often ask next.