The Starter Guide for Data Modeling

If I had to pick one area data teams struggle with the most, it would be data modeling. It's where we spend a lot of our time, and it can make or break the success of a team and an architecture.

This guide pulls together the core ideas I share with teams: why modeling matters, how to think about facts and dimensions, the common table types you'll run into, and the mistakes that derail a model.

Let's begin.

Part 1Why data modeling matters

Organization, control and scalability.

▶ Watch · 6:43 3 Reasons Data Modeling Gets So Much Attention

Reason 1Organization

The best way to see why this matters is to look at what happens when there's no model. You end up with a random assortment of business logic in queries. Lots of joins and custom queries in the reporting tool. No structure for how to manage any of it.

As the team grows and the requests pile up, that logic keeps spreading with no consistency. It gets messy and chaotic until you have to start over and rebuild.

When you commit to a modeling approach, you establish relationships and granularity on purpose. You know where things are and why they're there, and you can explain it to somebody else. It takes effort up front, but it's worth it.

Reason 2Control

A lot of teams fall into the trap of thinking they have to match whatever the source system tells them. But your analytics database isn't the back end of the application that gave you the data. It's your place to structure the data for the business and for reporting.

For example, a business application might have a table called users. If every one of those users is actually an employee, and that's what your business calls them, that's an opportunity to create a dim_employees table sourced from users.

That doesn't mean you ignore the source system. This is where modeling becomes a bit of an art and a science. You'll also likely be combining multiple sources, so you need to take control of the logic and decide what takes precedence.

Reason 3Scalability

With a clear, well-structured model, adding a new source gets easier because you know, at least directionally, where things will go.

On the reporting side, new presentation tables and new reports become easy. The granularity is right and the relationships are set, so you're mostly picking and choosing what to display.

Part 2Facts vs dimensions

The action, and the context around it.

▶ Watch · 6:49 The Struggle of Data Modeling (Facts vs Dimensions)

A fact table sits at the center of a star schema and is focused on a business action. Common examples are sales, transactions, orders or invoices. These are things that happened.

This is where a lot of teams get tripped up. They see the typical examples and think they don't have anything like that. Instead, ask what action is actually taking place. That's what gets tracked in a fact table, and that's what you build around.

A dimension table represents the context around that action. Who was the customer? What was the product? What was the date?

TipThink of it like a sentence

A fact table is like a verb. Something happened: you made a sale, sent an invoice, logged an activity.

A dimension is like a noun or an adjective. It describes the action: type, detail, color, location, name.

TipBe clear on what goes where

A fact table can also hold a create date or a truly unique timestamp. Other dates typically go to a dimension.

Once they're separated, you join them back together in the next layer (I call it the marts layer) and slice and dice the metrics in all the ways the business asks for.

Part 3The 3 types of fact tables

Transaction, periodic snapshot and accumulating snapshot.

▶ Watch · 9:39 The 3 Common Types of Fact Tables | Data Modeling 101

The three types differ in what one row represents.

• Transaction is the most common type and the default starting point. Get as granular as possible, because you can always roll it up later.
• Periodic snapshot is for metrics that only make sense as a state, like an account balance. You could also roll up a transaction fact in your marts layer. Neither is better. It depends on what's easier for your team to manage.
• Accumulating snapshot tracks something like order, ship and delivery dates on one row. It only works when the steps are fixed, and it needs update logic and update permissions.

The mental shortcut: match the type to the question. Do you need every event (transaction), the state at regular intervals (periodic), or where something is in a fixed process (accumulating)? These are complementary, not competing. Most warehouses use a few or all of them.

Part 4The 4 types of dimensions

Role-playing, conformed, junk and degenerate.

▶ Watch · 11:13 The 4 Common Types of Dimensions | Data Modeling 101

Beyond a regular dimension like dim_customer, these four patterns are what you reach for when the simple model isn't enough.

Role-playing: three roles, one dim_dateSELECT
  od.full_date AS order_date,
  sd.full_date AS ship_date,
  dd.full_date AS delivery_date
FROM fct_orders f
JOIN dim_date od ON f.order_date_key = od.date_key
JOIN dim_date sd ON f.ship_date_key = sd.date_key
JOIN dim_date dd ON f.delivery_date_key = dd.date_key

The mental shortcut: role-playing and conformed are about reusing a dimension, either inside one fact or across many. Junk and degenerate are about what to do when a dimension is too small to deserve its own table.

All of this is a thinking exercise. Understand the concepts, talk it through with your team and your business, and model around what makes sense.

Part 53 mistakes that derail a model

No team does this on purpose, but it happens all the time.

▶ Watch · 8:14 3 Data Modeling Mistakes That Can Derail a Team

Mistake 1Blending facts and dimensions

Most teams understand the goal of a star schema. The problem comes when what's a fact and what's a dimension start to mix. Over time you end up with a model that's not really a model. The tables are called facts and dimensions, but they don't function that way.

The root cause is usually the lack of a clear definition. Get clear on the definitions from Part 2, and more importantly, stick with them even when it seems complicated. If you want one big table for reporting (and you eventually will), build it in the marts layer, not in the model.

Mistake 2The wrong granularity

Say you build fct_orders. Then someone wants to see individual line items. Line items are a lower granularity than the order, and transactions per line item may be lower still.

The lower you go, the more options you have. You can sum the line items to get the order total, and you can join to a product dimension, which you can't do at the order level. Otherwise you end up with workarounds and more complexity because it wasn't done right the first time.

Mistake 3No clear flow of logic

I like to follow staging to warehouse to marts, moving data left to right like a pipeline. A lot of teams end up going in circles instead, with nested logic, layers that are mixed up, names that don't match, or queries straight off source tables inside the report.

I'm not judging here. I've absolutely been in that position. But think about what happens when you bring someone else on to help, or when you leave the company. The more nested it gets, the harder it is for anyone else to follow without coming to you.

If you don't have one already, start with a staging layer: one clean model on top of each source table. That alone forces you to think about how your project is designed, because you'll see where things are referenced multiple times.

The Modern Data Checklist walks through the full staging, warehouse and marts setup.

Common questions

What is data modeling?

Data modeling is how you structure the data in your warehouse around the business: the core actions you want to report on, the context around them, and how they relate. The most common approach is a star schema, with fact tables for the actions and dimension tables for the context.

Why is data modeling important?

It gives you three things: organization and structure as your team grows, control of the logic instead of matching whatever the source system says, and scalability, so new sources and new reports are easier to add.

What's the difference between a fact and a dimension?

A fact table tracks a business action, like an order or a sale, and holds keys plus numeric metrics. A dimension holds the context around that action: who, what, where and when.

Isn't it easier to just make one big table?

In the moment, maybe. Keep facts and dimensions separate in the model, then build wide tables for reporting in the marts layer on top of it. You get the convenience without losing the structure.

What grain should a fact table be?

As low as you can go while still getting the same result. A lower grain can always be rolled up. A higher grain can't be broken back down.

Recap

▶ Watch · 5:38 How Would You Model This Data? (Example)

1. Why it matters→Organization, control and scalability
2. Facts vs dimensions→The action vs the context around it
3. Fact table types→Match the type to the question
4. Dimension types→Reuse them, or handle the small ones
5. Common mistakes→Blending, wrong grain, no clear flow

Cheers,
Michael