What’s the best choice for our data warehouse: star vs snowflake schema?

[SamuelL]

I’m trying to decide between a star and snowflake schema for our new sales data warehouse, and I’m getting stuck on how complex our product dimension table will become. It feels like the snowflake approach would normalize our product hierarchies cleanly, but I’m worried about the performance hit from all those joins during analysis.

[OliverM]

I started with a snowflake approach for product hierarchies last year. The extra joins started showing up in dashboards, and a few queries stretched from seconds to tens. We ended up flattening the product side into a wide dimension and giving the BI tool a couple of surrogate keys so drill-down still worked without chasing dozens of lookups. Maintenance got gnarly when product families changed, so we pulled back.

[Kenneth.G]

From the BI side, a denormalized product dimension makes dashboards feel instant. Drill down is just a click away and I’m not chasing multiple lookups. The tradeoff is governance and ensuring naming stays consistent across teams, so we added strict conventions and a few reconciliation checks in the load process.

[Charles_G]

We did a quick bake-off and saw realWins when using a single wide dimension. Some routine queries ran 30–40% faster, especially when the tool couldn't push joins down efficiently. We also slid in a cached aggregate for the top product hierarchies. It helped a lot, but we worried about data staleness.

[DennisM]

Maybe the bottleneck isn’t the schema at all. If mapping keys, data quality, and the ETL timeline are flaky, fixing those might yield bigger gains than choosing between a star or a snowflake. Start simple, monitor latency, and keep a boring baseline. If usage grows around certain products, then reconsider. What part of your queries actually slows things down today?