Surrogate keys in star schema hierarchy dimension

Question

Is it necessary to have surrogate keys for each hierarchy level above the lowest level in a dimension table?

Row  City_Key  City_Name    State
1     1234      Chicago    Illinois
2     3245      Dallas      Texas
3     4563      Huston      Texas
4     3457      Seattle    Washington

vs

Row  City_Key  City_Name  State_Key  State
1     1234      Chicago      535    Illinois
2     3245      Dallas       659     Texas
3     4563      Huston       659     Texas
4     3457      Seattle      912   Washington

If so, how would I go about generating surrogate keys for levels in the hierarchy with SQL if it would not suffice to have an auto-incrementing key which would change per row like the lowest level key?

Would it be better to use a snowflake schema with normalized hierarchy dimensions or perhaps create/manage a denormalized hierarchy dimension table through joining a normalized hierarchy?

score 0 · Answer 1 · answered Sep 13 '22 at 19:06

Is it necessary to have surrogate keys for each hierarchy level above the lowest level in a dimension table?

No. In a star schema there is no need, as the attribute hierarchies are modeled as non-key columns of a single dimension table.

In a snowflake design, where each level of the hierarchy is modeled as a separate table, such keys would of course be required.

score 0 · Answer 2 · answered Jul 11 '23 at 20:37

You only need a surrogate key for the thing which uniquely distinguishes a row, in this case city. You wouldn’t need a surrogate for state as no facts will join to this table at state level. If you did, you’d need a separate state dimension as well.

Surrogate keys in star schema hierarchy dimension

2 Answers2