Login

I’m trying to decide between a star and snowflake schema for our new sales data warehouse, and I keep going in circles. My team wants the simpler joins of a star schema, but our product dimension has so many slowly changing attributes that a snowflake might handle the historical tracking better. I’m just not sure if the query complexity trade-off is worth it for our reporting loads.

We ran into the same decision last quarter. We went with the simpler join model at first and added a single slowly changing dimension bridge. It let our daily dashboards load in seconds and kept ETL simple.

I tried a more normalized approach once and it felt like chasing little details in the BI tool. The reports took longer and the team forgot which key linked to what.

We actually ended up with a hybrid approach. Keeps the fact table light and lets us store historical bits in separate dimensions that we can evolve.

If your reporting loads are the bottleneck right now rather than the warehouse design maybe start with the simplest path and measure. We did and saw gains by caching hot dims.

I tried to enforce tight governance on the dims and that slowed things down more than the schema complexity.

Do you have a sense of how big the historical attribute set is and how often it updates?

One pilot action we took was to isolate the product dimension for a two week run and compare query times for the top reports. We saw about a 40 percent drop in latency on product lookups.

Login
Username:
Password:	Lost Password?
	Remember me