Best Practice for Dimension Tables in a Star Schema

Quick Answer

Giving each dimension table a unique key and descriptive attributes is the answer because that combination is what makes a star schema actually function the way it's meant to. The unique key, whether it's a natural key from the source system or a surrogate key generated during modeling, is what allows a clean relationship to form between the dimension table and the fact table, so every fact row can be reliably traced back to exactly one matching row in each dimension. The descriptive attributes are what make that relationship useful to a report author and end user in practice, since they're the fields people actually filter, group, and slice by, rather than raw identifiers. Without a unique key, relationships become ambiguous or duplicate rows on join; without descriptive attributes, a technically correct dimension table still can't support meaningful slicing and filtering in a report. This is really the definition of what separates a working dimension table from a table that merely sits near a fact table without earning the name. When you evaluate a proposed data model against star schema best practices, check both properties together: does every dimension have a dependable key for the relationship, and does it carry the attributes people will actually want to filter and group by.