3 Data Modeling Mistakes That Derail A Team

The three data modeling mistakes that most often derail a team are blending facts and dimensions into each other, building fact tables at the wrong granularity, and letting transformation logic lose any clear flow from source to report. None of them are deliberate. They happen because putting things together is easier in the moment, because a fact table gets built quickly to close out a request, and because one more nested query feels faster than designing a layer. Each one compounds over time until the model is a model in name only. The fix is the same in every case: clear definitions you stick to, the lowest useful grain on every fact, and a left to right pipeline of staging, warehouse and marts that someone other than you could follow.

Mistake 1: Blending facts and dimensions

How it happens

Most of the teams I work with are aiming for a star schema: fact tables in the middle, dimension tables around them, and a well structured model built on that shape. Most of them understand the goal.

The problem shows up when what counts as a fact and what counts as a dimension start to mix:

• A column that belongs on a dimension lands on the fact because it was convenient.
• A calculated metric ends up on a dimension because that's where someone was already working.
• It seems to make sense at the time, so it stays.

The missing definition

The root cause is almost always a missing definition. If a team has never written down what a fact is and what a dimension is, nothing stops the two from blending, and blending is the easier option.

Over time you end up with tables called facts and tables called dimensions that aren't functioning as either. It's one of the first things I notice when I join a team that's struggling.

The definitions I use

A fact is the business action: the core thing you're trying to report on and model around, the event the business cares about.

Inside a fact table you have two kinds of columns, keys to dimensions and numeric, aggregable values. Numbers and keys, and that's pretty much it.

A dimension adds context around that action. It holds the things that describe rather than measure:

• Descriptions and names
• Booleans and flags
• Dates
• String values and categories

A dimension isn't meant to hold calculated metrics, and a fact isn't meant to hold descriptions of its metrics.

Where the wide table belongs

It's tempting to blend them because you eventually want a wide, denormalized table for reporting. That is the one big table idea, and it has a place.

The place is the presentation layer, which I call the marts layer. Build the wide table there, on top of a warehouse layer where facts and dimensions stay separate.

That separation is what gives you flexibility and structure. The harder part is staying consistent with the definitions even when a particular case feels complicated.

Mistake 2: The wrong granularity

Facts are verbs

The second mistake is related to the first: not establishing the right granularity, especially on fact tables. Granularity is the level of detail one row represents.

Since a fact table is the business action, think of it as a verb. The question is which event one row stands for.

A sale, an invoice and a transaction are all events, and some of them nest inside each other.

The orders example

Take orders. You build fct_orders with one row per order. Later someone wants to see individual line items on an order.

Line items are a lower granularity than orders, because there are several per order. If you only built the order level you can't get there from the table you have.

Go the other direction and it works. With fct_order_line_items you keep every option the order grain had and gain the ones it didn't:

• Roll up: sum the line item metrics and they equal the order totals.
• Join to dim_orders for order level attributes.
• Join to a line item dimension for more detail.
• Join to dim_products, which is only possible at the line item grain because a product belongs to a line, not to an order.

Lower still might be transactions, if there are several per line item. This isn't absolute. Sometimes you keep both grains as separate fact tables, and you can always add another fact later.

Don't rush fact tables

The usual failure is a workaround, not a redesign. I see teams discover they can't report on something because the first fact table wasn't low enough, and the response is a patch that adds complexity.

Often it's also where blending concepts starts, because you end up mixing things to compensate.

My main advice is to not rush fact tables. When requests are piling up it's tempting to build one quickly and move on, but moving fast without thinking through the scenarios is what leaves you exposed.

As a rule of thumb, if a lower granularity still gives you the same result, go lower.

Mistake 3: No clear flow of transformation logic

What a clear flow looks like

The third mistake is not being able to track the data from one layer to the next. I follow a staging to warehouse to marts structure, a left to right movement you can read like a pipeline.

It sounds simple. In practice a lot of teams end up going in circles with nested logic that hops between models that aren't really connected. The usual symptoms:

• Something is called an intermediate model or a reporting model, but the layers are mixed up.
• The naming doesn't match the layer a model actually sits in.
• There are no layers at all, and reports query source tables directly.

I'm not judging. I've been in that position and built some of it myself.

But the more projects I see, the more often this is the issue, and the way out is biting the bullet and creating the core data model.

The handoff test

Ask what happens when someone else needs to work on this. You hire, you bring in help, or you leave the company.

Logic with no clear flow is hard to follow, and it gets harder the more nested it becomes. Another stored procedure here, another nested select there, each one quick in the moment, and together they form a web nobody can untangle.

A new person can't contribute without coming to you to ask what something means. The time they spend deciphering the flow is time not spent building something useful for stakeholders.

Where to start

Group your tables or logic into layers so you can think about it as a flow. Doing that alone will show you where things hop around, going from one layer back to a previous one and forward again.

That hopping is exactly what makes a project hard to explain to a stakeholder or a teammate.

Start with staging

If you have no layers at all, start with staging. A staging layer is one to one on top of your source tables, a cleaned up version of each source that the rest of the project references.

It is a forcing function. Once every source has one staging model, you'll notice which sources are referenced in multiple places.

Then you can make the project modular by handling the cleanup once, there, instead of repeating it downstream.

The three mistakes in one place

To recap, the three mistakes are:

• Blending facts and dimensions.
• Building facts at the wrong grain.
• Losing the flow of transformation logic.

I've done all three myself at some point, so if you're in the middle of one, you're in common company. The point is to recognize it and take steps to get out.

Related reading

• The Struggle of Data Modeling (Facts vs Dimensions)
• The 3 Types of Facts
• The 4 Types of Dimensions
• How to Create a 3 Layer Data Model Pipeline

Final takeaway

These three are the patterns I check for first when I start working with a data team, because they explain most of the pain a team describes on a first call. Blended tables, a fact built too high, and logic nobody can trace are rarely carelessness. They're the result of moving fast without a model to move within.