Should you snowflake this dimension?

Question

Accepted Answer

Keep it flat. Two reasons, and the second matters more than the first. Compression. VertiPaq dictionary-encodes columns, so a Category column with 12 distinct values across 50,000 rows stores 12 values and a compact index. The storage saving from normalising is close to zero. Query cost. Every relationship traversed at query time costs work. A snowflaked model makes the engine hop Product → Subcategory → Category to apply a filter that a flat model applies directly. Relational normalisation is the correct instinct for a transactional database and the wrong one for a columnar analytical model. This is one of the clearest cases where a habit that serves you well in one context actively hurts in another.