A snowflake schema normalizes a dimension's hierarchy into secondary tables: book to genre, genre to genre group. BookNest's mart.genre_tree (Recursive CTE Rollups) is such a table, so both shapes can be compared:
-- Snowflaked: the genre's group lives in a separate, normalized table
SELECT g.parent AS genre_group, sum(f.gross_amount) AS gross
FROM mart.fact_sales f JOIN mart.dim_book b USING (book_key)
JOIN mart.genre_tree g ON g.node = b.genre GROUP BY 1
EXCEPT ALL -- Star: the group is a column of dim_book, one join fewer
SELECT b.genre_group, sum(f.gross_amount)
FROM mart.fact_sales f JOIN mart.dim_book b USING (book_key) GROUP BY 1;Output
genre_group | gross -------------+------- (0 rows)
No row differs, so the extra join buys nothing. The Kimball Group advises against snowflakes: users find them harder to navigate, and they can slow queries. Normalize only for a reason: a dimension of many millions of rows with a large repeated subgroup, a hierarchy shared by several dimensions (an outrigger table referenced from each), or a ragged hierarchy that flattening cannot express.