Three people once quoted me three different revenue figures for the same month, in the same meeting, inside about ninety seconds. Finance said 2.41 million. Marketing said 2.68 million. Product said 2.19 million. Nobody had made an arithmetic mistake and nobody was inventing numbers. They had each written a reasonable query against a pile of tables that had never been organised into a shape where the same question could only have one answer.

Data modeling is the work that prevents that meeting. It is the layer between raw source tables and the dashboards you built in Part 14, and it decides whether the metric definitions you wrote in Part 17 can actually be computed the same way twice.

Three teams, three revenue numbers

Here is what had happened in that meeting. Finance queried the payments table and counted money that had actually settled. Marketing queried an orders table joined to a campaign attribution table, which had more than one row per order whenever an order touched two campaigns, so a chunk of revenue got counted twice. Product queried orders directly but filtered to orders with a completed status, which excluded partial refunds that finance still counted as revenue.

Three defensible queries, three answers, and no way to tell from the outside which was right. The fix was not a better query. It was building one table that everybody queries, where one row means one thing, and where the revenue column has exactly one definition attached to it. That is a model.

Source systems are not designed for you. An application database is organised so the app can write a single order quickly and safely, which means the data is spread across many small tables with no repetition. That shape is excellent for writing and painful for asking questions across millions of rows. Analytics models reorganise the same facts into a shape built for reading.

Facts and dimensions

Dimensional modeling, the approach behind almost every analytics warehouse you will encounter, sorts every table into one of two buckets. Microsoft describes it plainly in its Power BI guidance: dimension tables enable filtering and grouping, fact tables enable summarization. That one sentence carries more weight than it looks.

A fact table stores events or observations. A sale, a click, a support ticket, a daily account balance. It contains numeric measure columns, which are the things you sum or average, plus key columns that point at the dimensions. Fact tables are the tall ones, millions of rows, growing every day.

A dimension table describes a business entity: a customer, a product, a store, a date. It has one row per thing, a key column that uniquely identifies each row, and a spread of descriptive columns you filter and group by. Dimension tables are the short wide ones, thousands of rows, changing slowly.

The date dimension deserves a special mention because beginners always ask why it exists. Your fact table already has a date column, so why store a table of dates? Because a date column tells you nothing about fiscal quarters, public holidays, week numbers, or whether a day was a weekend. A date dimension holds one row per calendar day with all of that precomputed, and it turns awkward questions into a simple filter. Build it once, use it in every model you ever touch.

A small retail model. Note the shape: one enormous fact table, four small dimensions. That ratio is normal and it is what makes these models fast.

flowchart LR
  A[Orders table in the app database] --> T[Transform step]
  B[Customer records from the CRM] --> T
  C[Product catalogue spreadsheet] --> T
  T --> F[fact sales, one row per order line]
  T --> D1[dim customer]
  T --> D2[dim product]
  T --> D3[dim date]
  F --> R[Dashboards, queries, reports]
  D1 --> R
  D2 --> R
  D3 --> R

How do you pick the grain?

Grain is the answer to one question: what does exactly one row of this fact table represent? Write it as a sentence before you write any SQL. One row per order line. One row per customer per day. One row per support ticket status change. If you cannot say it in a sentence, the table is not designed yet.

The rule of thumb is to model at the finest grain the source data supports. It is trivial to roll a fine grain up to a coarse one with a GROUP BY, and impossible to break a coarse grain back down. A daily summary table cannot answer a question about the hour of day. The storage you save by pre-aggregating is rarely worth the questions you permanently lose the ability to ask.

Grain also has a sharp edge, and it is the single most common bug I see in analyst work. If you join a fact table to something that has more than one matching row per key, the fact rows multiply, and every SUM after that join is inflated. This is called fan-out, and it is what happened to Marketing in the opening story. Here is the shape of it.

-- Correct: fact joined to a dimension with one row per key
SELECT d.category, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
GROUP BY d.category;

-- Inflated: campaign_attribution has many rows per order
SELECT SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN campaign_attribution c ON c.order_id = f.order_id;

Expected output: the first query returns one row per category and totals 2,410,000. The second returns 2,680,000 from the same underlying revenue, because orders touching two campaigns are counted twice. Common failure: nobody notices, because the inflated number is plausible.

The habit that saves you is to check the row count before and after every join you add. If the count changed and you did not intend it to, stop and aggregate the other table down to one row per key first. This takes fifteen seconds and it has caught more of my own mistakes than any other check.

Star schema and snowflake schema

Draw a fact table in the middle of a page and its dimensions around the edge, join each dimension to the fact with a line, and you have drawn a star. That is the whole origin of the name, and it is also a useful test: if your diagram looks like a star, the model is probably fine. If it looks like a plate of spaghetti, something needs work.

The alternative is a snowflake schema, where dimensions are split into further normalized tables. Instead of one dim_product table holding product, subcategory and category, you get three tables chained together. It saves a little storage by not repeating the word Electronics forty thousand times. It costs you an extra join on every query and a model that is harder for a report author to understand.

My recommendation is flat: build the star, flatten your dimensions, and accept the repeated text. Storage is cheap and analyst time is not. Microsoft says much the same in its guidance, noting that the benefits of a single denormalized dimension table generally outweigh multiple normalized ones. Keep the snowflake only when a dimension is genuinely enormous and the repetition is measured in gigabytes rather than megabytes.

There is a third option you will meet in the wild, usually called one big table: everything flattened into a single wide table with no joins at all. It is fast and easy to query, and it works well for a single focused dashboard. It falls apart as soon as two teams need slightly different versions of it, which is roughly month three of any real project.

Keys that survive a reload

A key is the column that links a fact row to a dimension row. There are two kinds and the distinction matters more than it sounds. A natural key, sometimes called a business key, comes from the source system: an employee ID, a SKU, an email address. A surrogate key is a meaningless number the warehouse invents, usually just a counter, and by definition it does not exist in the source data.

Beginners reasonably ask why you would invent a key when the source already has one. Three reasons. Source keys change, and when a company migrates its CRM every customer gets a new ID while your history still refers to the old one. Source keys collide, because two acquired systems both start their customer IDs at 1. And most importantly, once you start tracking history, one real customer needs several dimension rows, so the business key stops being unique and something else has to take that job.

So keep both. The dimension table gets a surrogate key as its unique identifier and keeps the natural key as an ordinary column, which lets you trace any row back to the source system when someone disputes a number. The fact table stores only the surrogate key. That one convention removes an entire category of future pain.

Handling change over time

Dimension attributes change. Customers move city, products get recategorised, salespeople switch region. A slowly changing dimension, usually shortened to SCD, is a dimension table designed to handle that change deliberately rather than by accident. The word slowly matters: it applies to attributes that shift occasionally and unpredictably, not to things that move constantly like a stock price, which belong in a fact table as a measure.

There are two you need on day one. Type 1 overwrites the old value, so the dimension always shows the current state and history is silently rewritten. Type 2 adds a new row with a new surrogate key, plus a start date, an end date and usually a current flag, so every fact stays attached to the version of the entity that was true when the event happened.

The same worked example under each approach. The third row is not a design, it is the default you inherit when nobody chose.

Type 2 is not free. The dimension grows, the joins get fiddlier because you have to match on the version that was valid at the fact date, and report authors need to understand the difference between filtering to a customer and filtering to a version of a customer. Microsoft is direct about this in its guidance: the version label should be unambiguous, something like Anita Rao from January 2024 to June 2026, so nobody picks the wrong one from a dropdown.

My practical rule is to apply Type 2 only to the attributes you actually slice reports by, and Type 1 to everything else. Segment, region, plan tier and sales territory get versioned. Phone number, email and a corrected spelling of a surname get overwritten. Versioning every column because it feels safer produces a dimension nobody can query and a load job that takes an hour.

Read the model before you query it

Most of your first two years will be spent querying a model somebody else built, often without documentation and often without the person who built it. Ten minutes of reading before you write anything will save you a wrong number in front of an audience, which is the situation Part 19 spent 2,000 words trying to help you avoid.

Find the fact table and work out its grain by counting: if the row count matches the number of orders, the grain is the order, and if it is several times larger, the grain is the order line. Check for a date dimension, because its absence usually signals a model that was assembled in a hurry. List which dimensions join to the fact and on what key. Then test whether any of those joins multiply rows.

Finally, take one row and verify it by hand against the source system. One order, one customer, one amount, traced end to end. It feels slow and it is the fastest way to find out that the revenue column excludes tax, or that cancelled orders are still in there, or that the load job has been failing silently since Tuesday.

flowchart TD
  S[Open an unfamiliar model] --> G[Count rows and state the grain in a sentence]
  G --> K{Is there a date dimension?}
  K -->|No| W[Expect painful date filtering, ask who owns this]
  K -->|Yes| J[List the dimensions and their join keys]
  W --> J
  J --> C{Does any join change the fact row count?}
  C -->|Yes| X[Aggregate that table to one row per key first]
  C -->|No| Q[Write the query]
  X --> Q
  Q --> V[Trace one row back to the source by hand]

Write the grain sentence and build a date dimension

If you take two habits from this part, take these. Before you build or query any fact table, write the sentence that says what one row means, and put it somewhere other people can read. And whatever else your model has or lacks, give it a proper date dimension with one row per calendar day. Those two things fix more real reporting problems than any amount of clever SQL, and both cost under an hour.

Everything else here is a refinement of the same idea: decide what a row means, protect that meaning with keys, and decide on purpose what happens when the world changes. Models fail when nobody made those decisions, not when somebody made them differently from Kimball.

Part 21 turns to ethics, privacy and bias, which is the other half of doing this responsibly: a technically correct model can still hold data you should not be storing, or encode a bias that quietly shapes every report built on it. Keep the Data Analyst guide as your map, and revisit Part 7 if the join behaviour behind fan-out still feels slippery.

This week, open the table your team reports from most often, write its grain in one sentence, and check whether a join in your most-used query changes the row count. If it does, you have just found a number somebody is currently trusting.

References

• Understand star schema and the importance for Power BI, Microsoft Learn
• Dimensional modeling in Microsoft Fabric Warehouse, Microsoft Learn
• Building a Kimball dimensional model with dbt, dbt Developer Blog

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts