Dimensional Modeling: Star and Snowflake Schema Diagrams

Dimensional modeling is the way analytical databases and data warehouses are designed, and it deliberately breaks the normalization rules that govern transactional schemas. Where an operational database normalizes to store each fact once and support constant updates, an analytical database optimizes for reading and aggregating enormous volumes of history. That different goal produces a different shape: a central fact table holding the measurements, surrounded by dimension tables holding the context, arranged in the star that gives the star schema its name.

This guide explains dimensional modeling for anyone building or documenting analytics: what fact and dimension tables are, how the star and snowflake schemas differ, how grain and keys work, and how to diagram it all. You can build these diagrams in Atlas Diagram Studio at /diagrams with the ERD tool at /diagram-tools/erd-tool, using the same crow's foot notation you already know. Dimensional models look different from the normalized schemas in the database design guide at /guides/database-design, and understanding why is the key to reading and building them well.

Fact tables and dimension tables

A dimensional model divides the world into two kinds of table. A fact table holds the measurements of a business process - the numbers you want to aggregate, like sales amount, quantity, or duration - one row per event, such as one row per line item sold. Fact tables are long and narrow: they can hold billions of rows but few columns, mostly numeric measures plus foreign keys pointing to the dimensions. They are where the quantitative substance of the analytics lives.

Dimension tables hold the descriptive context that gives the facts meaning - the who, what, where, and when. A sales fact might connect to a date dimension, a product dimension, a customer dimension, and a store dimension, each answering a question about the sale. Dimensions are wide and short: many descriptive columns but comparatively few rows. Slicing a metric by these dimensions - sales by product by month by region - is exactly what analytics is, and the fact-plus-dimension structure is built to make those slices fast and intuitive.

Star versus snowflake schemas

The star schema is the classic dimensional shape: one fact table in the center, connected by foreign keys directly to each dimension table, forming a star. The dimensions are deliberately denormalized - a product dimension holds the product's category and subcategory as plain columns rather than splitting them into separate tables. This denormalization is intentional: it keeps queries simple, needing only one join from the fact to each dimension, and analysts find the flat dimensions easy to understand and query.

The snowflake schema normalizes the dimensions instead, splitting each into a hierarchy of related tables - product connects to category, which connects to department - so the diagram branches out like a snowflake. It saves some storage and can enforce consistency in the dimension hierarchy, but at the cost of more joins and more complexity for the analyst. Most modern warehouses favor the star schema, accepting the redundancy in exchange for query simplicity and performance, and reserve snowflaking for dimensions where the hierarchy genuinely needs its own structure.

Grain, keys, and slowly changing dimensions

A few concepts are essential to modeling dimensions correctly, and each shows up on the diagram.

• Grain: the exact meaning of one fact row - one sale, one daily balance, one shipment - decided first, because everything else depends on it.
• Surrogate keys: dimensions use their own system-generated keys rather than source-system natural keys, insulating the warehouse from operational changes.
• Foreign keys: each fact row carries a foreign key to every dimension, and those keys plus the measures are essentially all a fact table contains.
• Measures: the numeric, additive values in the fact table that you sum, average, or count across dimensions.
• Conformed dimensions: shared dimensions like date or customer reused across multiple fact tables, so metrics can be compared consistently.
• Slowly changing dimensions: patterns for handling a dimension attribute that changes over time, such as keeping history by adding a new dimension row.
• Date dimension: an almost-universal dimension with one row per day and rich attributes like month, quarter, and holiday flags.

Diagramming a dimensional model

Diagramming a dimensional model uses the same entity relationship notation as any other database, but the layout should make the star structure obvious. Place the fact table in the center and arrange its dimensions around it, so the one-to-many relationships - each dimension row relating to many fact rows - radiate outward with crow's foot connectors pointing at the fact. This deliberate layout communicates the model's nature at a glance: a reader instantly sees which table is the fact and which are the dimensions, which a scattered arrangement would hide.

Label the fact table's grain prominently, since the grain is the single most important thing to understand about the model, and distinguish fact from dimension tables with consistent color or styling. For a snowflake schema, show the dimension hierarchies branching outward so the extra normalization is visible. Build these in the editor at /diagrams, and because a warehouse schema can be generated from its DDL, keep the diagram in sync using the approach in the guide on generating diagrams from code at /guides/how-to-generate-diagrams-from-code. The normalization guide explains why transactional schemas differ, which sharpens the understanding of why analytical ones look the way they do.