Data Modeling Overview
The reusable shape between raw data and answers
A BI data model is the bridge between raw warehouse tables and answers: facts, dimensions, relationships and a defined grain. Every dashboard inherits the model's logic.
What you will learn
- Explain the fact/dimension split and the grain
- Describe how relationships make downstream reporting possible
- Frame models as the shared logic layer for reports
What is this?
A data model defines how tables relate and what they mean for analysis. It is built from facts (measured events: sales, orders, logins) and dimensions (the 'who/what/when/where': customer, product, date, region). The grain declares exactly what one row represents. Reports query the model — not raw tables — so every dashboard inherits the same relationships, measures and definitions.
Why do you need it?
- A well-modeled layer means dashboards are consistent, not bespoke.
- Filtering and aggregation depend entirely on the model's relationships.
- Model fixes are the cheapest ways to fix a whole report family.
- Interview brain-filters: 'model first, tool second' is the accepted modern take.
Mental model
A card catalog in a library
A data model
The catalog describes how books (facts) sit under authors, subjects and shelves (dimensions). You never re-inventory the library to answer 'which shelves has this author filled?' — the catalog answers it. The model is the catalog: relations described once, every question served.
How it works
- Facts store measures at a declared grain (one row per order, per click, per day).
- Dimensions store descriptive attributes and are joined to facts by keys.
- Relationships connect dims to facts; filter propagation makes slicing work.
- The grain is the contract: mis-graining duplicates or loses rows.
- Models sit above the warehouse (often a star schema) and feed many reports.
Raw tables → model → reports.
Examples
A minimal fact/dimension model
fact_orders (grain: one row per order line) order_id, line_no, cust_id, prod_id, date_id, amount, qty dim_customer (grain: one row per customer) cust_id, name, region, segment dim_product (grain: one row per product) prod_id, name, category, price Relationships: cust_id, prod_id, date_id link dims to facts. Report: Revenue by region, by category, by month —all from ONE fan-out-free fact.A single star of facts and dims answers a whole report family without reworking anything.
In production
- A sales model feeds revenue, AOV and margin dashboards — all slicing by the same dims.
- An ad-ops model defines clicks/impressions per ad dim so every campaign report agrees.
- A churn model keeps customer dim + monthly facts, letting exec and ops dashboards reconcile.
Best practices
- Declare the grain on every fact table (and every model diagram).
- Keep dimensions conformed (shared) where the business shares the entity.
- Define measures in the model, not inside individual reports.
- Test the model with one min/max slice before wiring dashboards.
Common mistakes
- Modeling silently at report grain, so two reports disagree.
- Shoving everything into one wide 'master' table.
- Missing relationships — then filters just don't work.
- Forgetting the grain contract — a schema change quietly doubles rows.
Debugging tips
- Two dashboards disagree → compare which grain/model each queries.
- Slicer does nothing → the relationship path is missing or wrong.
- Counts double after refresh → the grain changed; verify one-row-per-unit on the fact.
Interview checkpoint
Hands-on lab
- Construct fact + dim tables in SQLite
- Verify relationships and grain
- Prove one-dashboard answers over the model
Build and Probe a Fact/Dimension Model
45 min · beginner
Quick quiz
Which table holds the additive measures and the foreign keys?
The grain is only a documentation nicety; it doesn't affect totals.