In a data warehouse, a surrogate key is a necessary generalization of the natural production key and is one of the basic elements of data warehouse design. Let’s be very clear: Every join between dimension tables and fact tables in a data warehouse environment should be based on surrogate keys, not natural keys. It is up to the data extract. Apr 20, 2006 The first problem is inherently caused by inserting meaningless data, and is always a problem, even with the built-in surrogate keys where the RDBMS provides a mechanism to retrieve the value. Sequences: a better surrogate key. Surrogate keys are often considered very bad practice, for a variety of good reasons I won’t discuss here. Oct 14, 2017 Why Surrogate Keys are used in Data Warehouse aroundBI. Unsubscribe from aroundBI? Database Design 25 - Surrogate Key and Natural Key - Duration: 7:34. A surrogate key is a unique primary key that is not derived from the data that it represents, therefore changes to the data will not change the primary key. The new key will be populated by incrementing the old maximum key by 1. Surrogate key is term mostly used in data warehouse environments. We might not come by this term in application production databases simply because there is no need to generate these keys. We can do just fine with primary keys. It is much needed to create surrogate keys in data warehouses for many reasons, read further.
-->Recommendations and examples for using the IDENTITY property to create surrogate keys on tables in Synapse SQL pool.
What is a surrogate key
A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.
Creating a table with an IDENTITY column
The IDENTITY property is designed to scale out across all the distributions in the Synapse SQL pool without affecting load performance. Therefore, the implementation of IDENTITY is oriented toward achieving these goals.
You can define a table as having the IDENTITY property when you first create the table by using syntax that is similar to the following statement:
You can then use INSERT.SELECT to populate the table.
So my brother gave me a pretty dead machine - there are serious issues with the data on the hard-disk. CHKDSK can't repair it and all of my other attempts to raise it from the dead have failed. It originally was a XP Media Center Edition and I've onlygot an XP Home disc. 
This remainder of this section highlights the nuances of the implementation to help you understand them more fully.
Allocation of values
The IDENTITY property doesn't guarantee the order in which the surrogate values are allocated, which reflects the behavior of SQL Server and Azure SQL Database. However, in Synapse SQL pool, the absence of a guarantee is more pronounced.
The following example is an illustration:
In the preceding example, two rows landed in distribution 1. The first row has the surrogate value of 1 in column C1, and the second row has the surrogate value of 61. Both of these values were generated by the IDENTITY property. However, the allocation of the values is not contiguous. This behavior is by design.
Skewed data
The range of values for the data type are spread evenly across the distributions. If a distributed table suffers from skewed data, then the range of values available to the datatype can be exhausted prematurely. For example, if all the data ends up in a single distribution, then effectively the table has access to only one-sixtieth of the values of the data type. For this reason, the IDENTITY property is limited to INT and BIGINT data types only.
SELECT.INTO
When an existing IDENTITY column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
- The SELECT statement contains a join.
- Multiple SELECT statements are joined by using UNION.
- The IDENTITY column is listed more than one time in the SELECT list.
- The IDENTITY column is part of an expression.
If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.
Defeat a new army of evil - You have put an end to Diablo’s vile reign, but now a final challenge arises. Both new characters can be played through the original four acts of Diablo II as well as the new fifth act. License key generator.
CREATE TABLE AS SELECT
CREATE TABLE AS SELECT (CTAS) follows the same SQL Server behavior that's documented for SELECT.INTO. However, you can't specify an IDENTITY property in the column definition of the CREATE TABLE part of the statement. You also can't use the IDENTITY function in the SELECT part of the CTAS. To populate a table, you need to use CREATE TABLE to define the table followed by INSERT.SELECT to populate it.
Explicitly inserting values into an IDENTITY column
Synapse SQL pool supports SET IDENTITY_INSERT <your table> ON OFF syntax. You can use this syntax to explicitly insert values into the IDENTITY column.
Many data modelers like to use predefined negative values for certain rows in their dimensions. An example is the -1 or 'unknown member' row.
The next script shows how to explicitly add this row by using SET IDENTITY_INSERT:
Loading data
The presence of the IDENTITY property has some implications to yourt be used:
- When the column data type is not INT or BIGINT
- When the column is also the distribution key
- When the table is an external table
The following related functions are not supported in Synapse SQL pool:
Common tasks
This section provides some sample code you can use to perform common tasks when you work with IDENTITY columns.
Column C1 is the IDENTITY in all the following tasks.
Find the highest allocated value for a table
Use the MAX() function to determine the highest value allocated for a distributed table:
Find the seed and increment for the IDENTITY property
You can use the catalog views to discover the identity increment and seed configuration values for a table by using the following query:
Next steps
Vast is an Ocean,So is vast the World of Knowledge. With my diving suit packed, loaded with imaginative visions, and lots of curiosity, started diving deep into the world of BODS.Lots of work is going on. Got attracted towards the “Key_Generation” transform and was fascinated at its features.Now it was time for me to fuse and adapt myself into its world.
THE KEY_GENERATION TRANSFORM:-
This transform is categorized under the “Data Integrator Transforms”. This generates new keys for source data, starting from a value based on existing keys in the table we specify.
If needed to generate Artificial keys in a table, the Key_Generation transform looks up the maximum existing key value from a table and uses it as the starting value to generate new keys.
The transform expects the generated key column to be part of the input schema.
STEPS TO USE KEY GENERATION TRANSFORM:-
Scenario:- Here the target data source for which the keys is needed to be added, have certain newly added rows without a Customer_ID. This could be easily understood in the following snap:-
Our aim here is to automatically generate the keys(Customer_ID) in this case , for the newly inserted records which have no Customer_Id. Accordingly we have taken the following as our input (the modified data without Customer_ID)
INPUT DATA (to be staged in the db):-
TARGET TABLE(which contains the data initially contained in the source table before the entry of new records in the database):-
THE GENERATED DATA FLOW:-
CONTENT OF SOURCE DATA:- (containing the modified entry alone)
CONTENT OF QUERY_TRANSFORM:-
Data Warehouse Surrogate Keys
CONTENT OF THE KEY_GENERATION TRANSFORM:-
THE CONTENTS OF THE TARGET TABLE PRIOR JOB EXECUTION:-
The JOB_EXECUTION:-
THE OUTPUT AFTER THE JOB EXECUTION:-
Database Surrogate Key Definition
We can now see from the output how Keys have been generated automatically to those records which did not have the Customer_ID initially.
What Is Surrogate Key
I explored this little process of the Key_Generation transform, and it seems a savior at times when huge amount of data have the missing entries(wrt to the keys or any sequential column fields).
Surrogate Key
Now its time to go back to the surface of waters…….