Jan 28, 2016  As a final note, I also decided to use a surrogate key for the date dimension. In practice, many data marts use the actual date values as the key, rather than generating surrogate keys for the date dimension. This is one of the most common exceptions to the rule of always using surrogate keys. Feb 28, 2011 Identity columns make it easy to have surrogate key columns that are automatically populated. Having a column be populated by the identity property also makes it easy to create unique identity column values for each row. Next time you want a surrogate key when you design a table, consider creating the key as an identity column.


APPLIES TO: Azure Data Factory Azure Synapse Analytics (Preview)

Use the surrogate key transformation to add an incrementing key value to each row of data. This is useful when designing dimension tables in a star schema analytical data model. In a star schema, each member in your dimension tables requires a unique key that is a non-business key.


Key column: The name of the generated surrogate key column.

Start value: The lowest key value that will be generated.

Increment keys from existing sources


To start your sequence from a value that exists in a source, use a derived column transformation following your surrogate key transformation to add the two values together:

Increment from existing maximum value

To seed the key value with the previous max, there are two techniques that you can use based on where your source data is.

Database sources

Use a SQL query option to select MAX() from your source. For example, Select MAX(<surrogateKeyName>) as maxval from <sourceTable>/

File sources

Surrogate key generation in informatica 2

If your previous max value is in a file, use the max() function in the aggregate transformation to get the previous max value:

In both cases, you must join your incoming new data together with your source that contains the previous max value.

Data flow script



Surrogate Key Generation In Informatica 2

The data flow script for the above surrogate key configuration is in the code snippet below.

Next steps

Key Generator

These examples use the Join and Derived Column transformations.

Coments are closed
Scroll to top