What is the role of surrogate keys in data warehouse and how will u generate them?
A surrogate key is a simple Primary key which maps one to one with a Natural compound Primary key. The reason for using them is to alleviate the need for the query writer to know the full compound key and also to speed query processing by removing the need for the RDBMS to process the full compound key when considering a join.
For example, an shipment could have a natural key of ORDER + ITEM + SHIPMENT_SEQ. By giving it a unique SHIPMENT_ID, subordinate tables can access it with a single attribute, rather than 3. However, it’s important to create a unique index on the natural key as well.