How to model a snowflake schema in a Data Warehouse

A snowflake schema organizes a Data Warehouse by normalizing dimensions into smaller, linked tables. The result is less redundancy and dimensions that are easier to maintain — useful when a dimension has many repeated attributes, such as product categories or geographic locations.

Prerequisites

• A Data Warehouse or relational database (SQL Server, Azure SQL, PostgreSQL, etc.).
• Familiarity with the star schema concept, with a fact table and dimensions.
• Basic SQL knowledge (CREATE TABLE, JOIN, primary and foreign keys).

Step 1: Understand the difference between star and snowflake

In a star schema, each dimension is a single "flattened" (denormalized) table that repeats values. In a snowflake schema, that dimension is split into several related tables, following normalization rules. The fact table stays in the center; the difference is in how the dimensions branch out.

Imagine a product dimension that includes category and subcategory. In the star model, the category name repeats on every product row. In the snowflake, the category moves to its own table and the product keeps only a key.

Rule of thumb: use the snowflake for large, highly repetitive dimensions; keep the star schema for small dimensions where query speed is the priority.

Step 2: Identify the dimension to normalize

Pick a dimension with clearly hierarchical or highly repeated attributes. A good candidate is Dim_Produto, which usually contains product, subcategory, and category — a natural three-level hierarchy. Separating these levels avoids updating dozens or hundreds of rows when a category name changes, because that name now lives in a single place.

Step 3: Create the normalized tables

We will split the product dimension into three tables linked by foreign keys: category, subcategory, and product. Each table uses a surrogate key (a numeric key owned by the Data Warehouse) as its primary key, which keeps the links stable even if the source identifiers change.

CREATE TABLE Dim_Categoria (
    categoria_key INT PRIMARY KEY,
    nome_categoria VARCHAR(100)
);

CREATE TABLE Dim_Subcategoria (
    subcategoria_key INT PRIMARY KEY,
    nome_subcategoria VARCHAR(100),
    categoria_key INT REFERENCES Dim_Categoria(categoria_key)
);

CREATE TABLE Dim_Produto (
    produto_key INT PRIMARY KEY,
    nome_produto VARCHAR(150),
    subcategoria_key INT REFERENCES Dim_Subcategoria(subcategoria_key)
);

Notice the chain: Dim_Produto points to Dim_Subcategoria, which in turn points to Dim_Categoria. This branching is what gives the snowflake shape.

Step 4: Connect the fact table

The fact table connects only to the most detailed level of the dimension — in this case, Dim_Produto. The higher levels are reached through the intermediate tables, following the chain of keys.

CREATE TABLE Fact_Vendas (
    venda_key BIGINT PRIMARY KEY,
    produto_key INT REFERENCES Dim_Produto(produto_key),
    data_key INT,
    quantidade INT,
    valor_total DECIMAL(12,2)
);

Step 5: Query the data with JOINs

To get sales by category, walk the chain of tables with JOIN until you reach the category:

SELECT c.nome_categoria,
       SUM(f.valor_total) AS total_vendas
FROM Fact_Vendas AS f
JOIN Dim_Produto AS p       ON f.produto_key = p.produto_key
JOIN Dim_Subcategoria AS s  ON p.subcategoria_key = s.subcategoria_key
JOIN Dim_Categoria AS c     ON s.categoria_key = c.categoria_key
GROUP BY c.nome_categoria
ORDER BY total_vendas DESC;

Notice that the snowflake requires more JOINs than the star schema. In exchange, each category name exists only once, which saves space and simplifies updates. On modern analytical databases the cost of these JOINs is usually low, but it is worth testing with your real data volume.

Verify the result

To confirm the model is correct, validate three things:

• Each subcategoria_key in Dim_Produto exists in Dim_Subcategoria (referential integrity).
• The Step 5 query returns one row per category, with no duplicates.
• There are no repeated category names in Dim_Categoria.

You can run this quick check to detect duplicate categories:

SELECT nome_categoria, COUNT(*) AS repeticoes
FROM Dim_Categoria
GROUP BY nome_categoria
HAVING COUNT(*) > 1;

If the query returns no rows, there are no repeated categories and the normalization is correct.

Conclusion

You now know how to build a snowflake schema: you normalize a dimension into tables linked by foreign keys and adjust your queries with extra JOINs. Use this approach when the space savings and easier maintenance outweigh the extra joins; otherwise, the star schema may be faster and simpler. Which of your dimensions has the most repeated values and would be the first candidate to normalize?