The normalized relational model is perfect for a system that processes orders one at a time. But when a manager asks "what's the revenue by region, by month, by product category, over the last three years?", that same model chokes. Dimensional modeling was born to solve exactly this kind of analytical question.

OLTP vs OLAP: two different worlds

Transactional systems (OLTP) are optimized for many small, consistent writes — inserting an order, updating stock. That's why they're normalized. Analysis (OLAP), on the other hand, runs few queries, but huge ones, scanning millions of rows and cutting across many dimensions.

The normalized model, with its dozens of tables, requires very expensive JOINs for these questions. Dimensional modeling deliberately reorganizes the data for analytical reads — a purposeful denormalization, as we anticipated in normalization.

Fact table: what happened

The fact table stores the measurable events of the business — a sale, a click, a shipment. Its columns are of two kinds: measures (numeric values you sum and compute on, like quantity and amount) and the foreign keys that point to the dimensions. The fact table tends to be long and narrow: a huge number of rows, few columns.

Dimension table: the context

Dimensions give context to the measures: who, what, when, where. Customer dimension, product, time, store. They're short and wide tables: relatively few rows, many descriptive columns. They're what you filter and group by ("sales by product category" uses the product dimension).

Star schema: the classic design

When you put the fact table in the center and the dimensions around it, linked directly to it, the drawing looks like a star — hence star schema:

      dim_time       dim_product
          \             /
           \           /
            +---------------+
            |  fact_sales   |
            | (qty, amount) |
            +---------------+
           /           \
          /             \
     dim_customer      dim_store

Each dimension connects to the fact table through a key. Since dimensions are denormalized (no sub-levels), you need just one JOIN per dimension — fast and simple to query.

Snowflake: when the dimension branches out

In a snowflake schema, dimensions are normalized into sub-tables: the product dimension splits into category, which splits into department. It saves space and reduces redundancy, but adds JOINs and complicates the query.

In practice, the star schema usually wins in analytics, because there fast reads matter more than saving space.

Grain: the most important decision

The grain defines what one row of the fact table represents. One row per sale? Per sale item? Per aggregated day? This is the first decision of any dimensional model, because it determines the level of detail possible. Fine grain gives flexibility; coarse grain saves space but loses detail. Mixing grains in the same fact table is a classic mistake.

OLTP optimizes for writing one fact at a time; dimensional optimizes for reading millions of facts at once. It's not that one is better — they solve opposite problems, and you need the right model for the right question.

In interviews it's common to be asked to draw a star schema from a scenario ("model the sales of an e-commerce store") and justify the grain you chose. Day to day, this shows up when you design a data warehouse or try to understand why a dashboard is slow to load.

Conclusion

Dimensional modeling separates facts (measurable metrics) from dimensions (context), organizes everything in a star schema optimized for reading, and revolves around the choice of grain. Understanding why it differs from the normalized OLTP model is the step that opens the door to the analytical world and the data warehouse.