The Struggle of Data Modeling (Facts vs Dimensions)

Data modeling is the area data teams struggle with most, and the sticking point is usually the line between facts and dimensions in a star schema. A fact table records a business action, like a sale, an order or an invoice, and a dimension table holds the context around that action: the customer, the product, the date. Think of the fact as the verb and the dimensions as the nouns and adjectives describing it. In practice a fact table holds only keys to the dimensions and numeric measures, a dimension holds a surrogate key and descriptive attributes, and keeping that line strict is what makes the model flexible later.

Why this is still the hard part

If I had to pick one area that data teams struggle with the most, it would be data modeling. That's despite everything working in its favor:

• It has been around for many years.
• There's a ton of content about it.
• There are even AI tools for it now.

It still gives teams trouble.

That's understandable, because it's one of the most custom aspects of data engineering. Textbook solutions can only get you so far before you hit your own company's data.

So rather than walk through modeling tactics, I want to give you a few different ways to think about the distinction that matters most in a star schema: facts versus dimensions. Sometimes hearing a thing put slightly differently is what unlocks it.

Purpose: the action and the context

The first way to compare facts and dimensions is by purpose. What is the table for, and what are you actually doing with it?

The fact: what happened

A fact table is a business action. It sits at the center of the star schema. The common examples are sales, transactions, orders and invoices: things that happened.

For your company the action might be something totally different, and I think this is where a lot of teams get tripped up. They see the typical orders and transactions examples and think, we don't have anything like that, it's not that simple for us.

What I'd encourage you to do is ask what the actual action is. What is taking place? Whatever that is, that's what gets tracked in the fact table, and that's what you build around.

The dimension: who, what, when

A dimension table is the context around that action. You almost have to separate the two concepts in your head. There's the action, an order or a sale, which is something that happened. Then there's the context:

• Who was the customer?
• What was the product?
• What was the date?

At a baseline level, that's the split.

Comparing to words: verbs, nouns and adjectives

If the purpose framing didn't unlock anything, here's another way I think about it. Compare the tables to the parts of a sentence.

A fact is a verb

A fact table is a verb. It's an action, something that happened or is happening:

• Making a sale.
• Sending an invoice.
• Logging an activity.
• Documenting something.

Each of those is doing something, and that's what goes in a fact table.

A dimension is a noun or an adjective

A dimension table is an adjective or a noun. You're describing the action:

• Type
• Detail
• Color
• Location
• Name

These describe something, and what they describe is the verb in the middle.

I recognize this isn't a perfectly technical definition. It's what has always worked for me, and it's how I explain it to the teams I work with.

The fact is the business action, the verb. The dimensions around it give context, the nouns and adjectives.

Table content: what goes in each

The last comparison is the actual content of the tables. We've covered how to think about them. Now what's included.

What goes in a fact table

For a fact table, I recommend a pretty traditional approach. It holds two things:

• Keys to the dimensions, which is how all the context gets attached.
• Numeric metrics: counts, quantities, amounts, anything you'll aggregate in a report or run a calculation on.

That's it. You're not storing descriptions or type columns in the fact. You're tracking the numbers, and when you want context you follow the keys out to the dimensions.

That's what lets you slice and dice the measures any way the business asks. If the fact table isn't designed this way, you can only cut it in so many ways, and you've limited yourself before you've written a single report.

The one nuance: dates

A create date or a truly unique timestamp on the record can live on the fact. Other dates typically get offloaded to a date dimension, which I covered in a separate post linked below.

What goes in a dimension table

A dimension holds a unique key, and I like to use surrogate keys, followed by attributes and descriptions. This is where the descriptive columns live:

• Strings: name, type, detail, color, size.
• Boolean values.
• Date values.

Both facts and dimensions tend to be wider tables, and both are denormalized, meaning several joins have already been done to produce them. The difference is what the columns are for.

Why I'm a stickler about it

The line blurs one column at a time. As the model grows, it gets very tempting, and very easy, to add just one description to a fact table because it's right there and it's convenient.

After working with dozens and dozens of teams, I've seen what happens when you give yourself that bit of leeway. It gets bigger. The line gets more blurred. Eventually nobody is quite sure what belongs where.

That's why I'm a stickler about it. Being strict now protects the model from fanning out over time.

Putting it back together

So again:

• Fact tables are numbers you aggregate, plus the keys that point to the dimensions.
• Dimension tables add the context.
• The next layer joins them back together, whether that's a marts layer or a direct connection to a reporting tool, and mixes and matches the metrics against every attribute the business wants.

Build it this way, resist the urge to blur the lines, and you end up with genuinely dynamic data.

It makes your life easier, it makes the business happier, and it's much easier to explain to the next engineer who builds on top of it.

Why do this at all?

Why go through the trouble? Isn't it easier to write custom queries or build one big table? To me there are three reasons, and I have a separate post that goes deeper on them.

1. Organization and structure

A model gives the warehouse a shape. That has enormous value on any data team, and the value grows as the team does, because new people can find their way around without a tour.

2. Control of the logic

You're determining the relationships and the definitions. You're not relying solely on what the source system says, and you're not winging it every time someone asks a question.

3. Scalability

You're establishing conventions and following a clear strategy, on purpose. That's what a well-functioning data team requires. It may not feel like the most fun thing to do in the moment, but it's worth it.

Related reading

• The 4 Types of Dimensions
• The 3 Types of Facts
• Do You Need a Date Dimension?
• 3 Reasons Data Modeling Gets So Much Attention

Final takeaway

The verb and noun framing is how I explain facts and dimensions on the first modeling call with most client teams, and the stickler rule about fact table content comes from seeing what happens on teams that didn't have one.