what will be benefit of surrogate key in data warehouse layer?
404rajneesh4u
posted onEnjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
With Modern cloud data warehouse platforms having natural keys are as good as surrogate keys. So if you are designing a system from scratch, stay away from generating surrogate keys. Use Natural keys where ever possible |
Hello Npack, Thanks for your input, it helps !!! May I know a bit more- 1) Why in Modern cloud data warehouse platforms having natural keys are as good as surrogate keys? 2) Surrogate key will allow me to join tables using numeric keys (than alphanumeric natural keys) , hence better performance? 3) For Type-2 SCD , I must have surrogate key to uniquely identify historical records? Thanks, Rajneesh |
1) Legacy datawarehouse systems relied heavily on numeric keys, as they had better performance over varchar columns. Numeric keys takes lesser memory and therefore easier and faster to traverse through. All Modern Data warehouses today use a variety of data pruning mechanisms to quickly retrieve the data, They do have clever metadata layers which is widely used to prune the partitions. Using Numeric keys is a thing of the past, Its painful to generate and maintain these keys, Its of no business value to the end users and often confuses them 2) I think i answered this one too as part of the above reply 3) Instead of manufacturing a numeric key, you can always concat the natural key values. like accountnumber|eff_dt |
Post Comment