CODEWITHSUNDEEP
snowflake-cloud-data-platform
Bino Thomas
Bino Thomas

Reputation: 100

Snowflake data warehouse - Generate dimension id with alpha-numeric character instead of integer

Is it a good idea to generate dimension id with the alpha-numeric character combination instead of integer in Snowflake data warehouse? (https://www.snowflake.com/) For example: Let's say I have to build a dimension table from a source table with 3 key combinations. Normally we built incremental integer column surrogate key as dimension id. Instead, is it better to create a string column key1_key2_key3(concatenated source keys) as surrogate key for generating dimension id? Since snowflakes are distributed database and perform well, I feel this should okay. I'm trying to see any unforeseen impact?

Upvotes: 0

Views: 1498

Answers (2)

Simon D
Simon D

Reputation: 6229

What it seems like you are asking is: Should you use a surrogate key (a monotonically increasing integer) or a concatenation of the business key as a primary key in your dimension.

Apart from the storage and performance benefits of using a surrogate key you also need to consider the main reason for using surrogate keys - slowly changing dimensions. If you decide to track the changes to your dimension records at some point you'll want to use surrogate keys in your dimensions since the concatenation of your business keys will duplicate over time.

Upvotes: 2

demircioglu
demircioglu

Reputation: 3455

I would create the dimension id as integer and add another column as surrogate key. Thus you will follow the standards and have an integer key like all other dimension tables. If you think surrogate key will be meaningful and will be used in joins/filters feel free to add one.

My point is having the dimension id as integer in that particular dimension table will prevent you from deviating following the best practices.

This link explains when and where using a surrogate key makes sense.

https://www.kimballgroup.com/1998/05/surrogate-keys/

Upvotes: 0

Related Questions