Ask a candidate to model daily sales for a BI dashboard and within two or three minutes you know whether they’ve shipped a warehouse or only read about one. The tell is grain. People who’ve done the work state the grain of the fact table before they draw a single box: one row per line item per order. People who haven’t start sketching tables and only discover halfway through that they can’t say whether a row is an order, an order line, or a daily rollup, and by then the model is already wrong.
Grain is the most useful word you can say early in a data modeling interview, and it’s the one most candidates skip. Declare it, defend it, and let the rest of the design fall out of it.
Grain first, everything else second
The grain is what a single row in the fact table represents. Pick it too coarse and you can never drill down; pick it too fine and you carry rows nobody queries. For an online order, “one row per order line” is usually right: it lets you sum revenue by product, by order, by customer, and by day without ever reaching for a second fact table. If someone answers “one row per order,” ask how they’d break revenue down by product category. Watch them realize they threw away the line items.
Once grain is fixed, the fact table is mostly foreign keys and numbers. The keys point at dimensions (product, customer, store, date). The numbers are the measures you aggregate: quantity, unit price, discount, extended amount. A rule interviewers like to hear stated plainly: facts hold what you sum or average, dimensions hold what you filter and group by. Revenue is a fact. Product color is a dimension attribute. If you catch yourself putting a text label in the fact table, that’s a signal it belongs in a dimension.
The measures question that separates levels is whether your facts are additive. Revenue sums cleanly across every dimension. An account balance or an inventory count does not; you can add balances across accounts but not across days, because a balance at month end isn’t the sum of the daily balances. Those are semi-additive, and interviewers at fintech and retail shops watch for whether you know the difference before you build a dashboard that reports a bank’s total assets as the sum of every daily snapshot.
Why star usually beats snowflake on a columnar warehouse
A star schema keeps each dimension in one flat table, even when that means repeating “Electronics > Phones > Android” across thousands of product rows. A snowflake schema normalizes those hierarchies into their own tables: product joins to subcategory joins to category. The snowflake saves storage and keeps one place to update a category name. It also adds joins to every query and makes the model harder to read.
On the warehouses people actually run in 2025 (Snowflake, BigQuery, Redshift, Databricks), the star wins most of the time. These are columnar engines with cheap storage and expensive joins, and dimension tables are usually small enough to broadcast to every node. Repeating a category string a million times costs almost nothing after compression, while the extra joins in a snowflake cost real query time. The exception worth naming is a genuinely large, reusable hierarchy, such as geography with millions of postal codes shared across several fact tables, where normalizing pays off. Say that out loud and you’ve shown you’re weighing the tradeoff instead of reciting a rule.
Slowly changing dimensions, where most of the real questions live
A customer moves from Chicago to Austin. A product gets recategorized. A sales rep changes territory. What happens to the dimension row, and what happens to the history that pointed at it? That’s the slowly changing dimension problem, and dimensional modeling interviews spend most of their time here because it’s where production systems actually break.
| SCD type | Behavior when an attribute changes | History preserved | When to use it | Main cost |
|---|---|---|---|---|
| Type 0 | Value is fixed at creation and never updated | Not applicable | Truly immutable attributes, like original signup date | Wrong if the attribute ever really changes |
| Type 1 | Overwrite the old value in place | None | Corrections and attributes nobody reports on over time | Silently rewrites the past |
| Type 2 | Close the old row with an end date, insert a new row with a new surrogate key, start date, and is_current flag | Full | Anything customer- or money-facing where “as of that date” matters | Dimension grows; every query must filter on is_current or a date |
| Type 3 | Add a “previous value” column alongside the current one | One prior value | Rare; a single before/after comparison, like last vs current region | Only one step of history, awkward past that |
Type 2 is the one that matters. You reach for it whenever anyone will ask “what did this look like at the time,” which for anything customer- or money-facing is always. The mechanics are worth being able to sketch: when an attribute changes, close the current row by stamping an end date and setting its is_current flag to false, then insert a new row with the new value, a fresh surrogate key, a start date, and is_current true. Your fact rows join on the surrogate key, so a sale from last March still points at the customer’s March address, not today’s.
That is exactly why dimensions use surrogate keys, an integer that means nothing outside the warehouse, instead of the natural business key like a customer ID. Under Type 2, one customer ID maps to many dimension rows over time, so the fact table needs a key that identifies a specific version of the customer, not the customer in general. If a candidate proposes joining facts directly on customer_id, ask what happens after that customer moves. The strong ones catch it before you finish the sentence.
Questions that actually get asked
Phrasings vary by company, but the shape is consistent. A handful come up again and again for data engineering and analytics engineering roles:
- “Model a schema for an e-commerce store’s orders so analysts can report revenue by product, region, and month.” They want grain, a fact table, and shared dimensions.
- “A customer changes their address. How do you keep their historical orders tied to the old one?” This is Type 2 and surrogate keys.
- “Why not run these reports against the production database directly?” Normalized OLTP versus denormalized OLAP, read patterns, and keeping analytics load off the transactional system.
- “When would you snowflake a dimension instead of keeping it flat?” Large shared hierarchies and the storage-versus-join tradeoff.
None of these reward a memorized definition. They reward showing your reasoning: name the grain, name the tradeoff, name the failure mode you’re avoiding.
Working through a coffee-chain example out loud
Say the prompt is a coffee chain that wants to analyze sales. Start with grain: one row per item sold per transaction. The fact table, call it fct_sales, holds surrogate keys for date, store, product, and customer, plus quantity, unit_price, and discount_amount. Four dimensions hang off it. dim_date is the standard calendar table everyone precomputes. dim_store carries region and store format. dim_product carries category and size. dim_customer is where the loyalty program lives, and it’s Type 2 because a customer’s tier and home store change over time and marketing will absolutely ask how sales split across tiers as of the purchase date.
Now push on it the way an interviewer will. How do you handle a transaction from a guest with no loyalty account? Use a dedicated “unknown customer” row in the dimension with a reserved surrogate key, so the foreign key is never null and your joins stay inner. How do you report daily inventory next to sales? A second fact table at a different grain, one row per product per store per day, sharing dim_date, dim_store, and dim_product. Those shared dimensions are what “conformed” means, and being able to point out that one customer dimension and one date dimension serve every fact table in the warehouse is the detail that reads as real experience rather than textbook recall.
If you take one habit into these interviews, make it stating the grain before you draw anything and refusing to move on until it’s pinned down. Nearly every wrong turn in a data model traces back to a fuzzy answer to “what does one row mean,” and the candidates who close that question first rarely paint themselves into a corner later.
Keep sharpening your system design:
