The Importance of Teradata Surrogate Keys

What are Teradata Surrogate Keys?

A Teradata Surrogate Key is an artificial key that maps to a natural key. It is usually of the data type INTEGER or BIGINT and is represented by a single column. The natural key can consist of multiple columns. The surrogate key is generated automatically and is represented by an ascending number.

We can use the ROW_NUM() function in Teradata to programmatically generate the surrogate key or an IDENTITY column for which Teradata automatically generates numbers. What possibilities we have to generate numbers using IDENTITY columns are described in this article:

The IDENTITY column surpasses the programmatic approach in producing consistent numbers. The programmatic approach is vulnerable to errors, which is particularly risky for surrogate keys.

Correctly defining the IDENTITY column is crucial in preventing duplicate creations. To achieve this, two parameters, GENERATED ALWAYS and NO CYCLE, play a vital role. The GENERATED ALWAYS parameter prohibits the insertion of custom values into the IDENTITY column. In contrast, the NO CYCLE parameter prevents the counter from restarting at the lowest value after generating the maximum allowed number. As a result, Teradata generates an error message when attempts are made to insert additional rows beyond the limit.

For comprehensive details on the present IDENTITY columns in use, refer to the DBC.IdCol table.

Disaster Reload of Key Tables with IDENTITY columns

To avoid creating duplicates, ensure the following steps are taken when reloading a key table:

• The key table needs to be re-created with new START and MINVALUE definitions to exclude already existing numbers to avoid the same number being generated again.
• The IDENTITY column definition needs to be changed from GENERATED ALWAYS to GENERATED BY DEFAULT to be able to insert the keys that Teradata generated earlier.
• New surrogate keys will be generated only when NULL values are passed to the IDENTITY column.

The IDENTITY column has a maximum value of 200,000 in the following example:

CREATE MULTISET TABLE TheKeys 
(
   NATURAL_KEY VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
   SURROGATE_KEY BIGINT GENERATED ALWAYS AS IDENTITY
   (START WITH 1 INCREMENT BY 1 
    MINVALUE -999999999999999 
    MAXVALUE 999999999999999 
    NO CYCLE),
)
UNIQUE PRIMARY INDEX (NATURAL_KEY);

To reload the key table, execute the following DDL statement:

CREATE MULTISET TABLE TheKeys 
(
   NATURAL_KEY VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
   SURROGATE_KEY BIGINT GENERATED BY DEFAULT AS IDENTITY
   (START WITH 1 INCREMENT BY 1 
    MINVALUE 200000 
    MAXVALUE 999999999999999 
    NO CYCLE),
)
UNIQUE PRIMARY INDEX (NATURAL_KEY);

Why should we use Surrogate Keys?

There are multiple benefits to substituting Teradata surrogate keys for natural keys.

Different source systems deliver the same natural key for different information.

If multiple source systems provide identical values for a specific target table’s natural key but with distinct meanings, surrogate keys become necessary to differentiate between objects. Retaining the natural key from both sources would not suffice.

Different source systems deliver different natural keys for the same information.

Sometimes, identical data can originate from separate source systems that utilize distinct natural keys. A prime example of this is with banks, where customer data can be stored across multiple sources. Surrogate keys are particularly useful for consolidating this information into a singular customer entity within the data warehouse.

Many Teradata data warehouse implementations refrain from using surrogate keys, which leads to the burden of integrating data cleanly being shifted to the back-end (data marts, reports). This approach increases costs in the long run, as it requires duplicating the process of combining object information in every data mart or report. It is important to note that implementing the right surrogate key design from the beginning can prevent these issues.

The Exchange of Source System leads to the Delivery of new Natural Keys.

Using the source system’s natural keys while replacing the original system can result in a problematic situation.

The mapping table (Natural Keys -> Surrogate Keys) requires adjustment only if surrogate keys are present. The new natural keys must be assigned surrogate keys only once.

We must replace the old natural keys with the new ones throughout our data warehouse if we only possess the natural keys.

Our data model should not be closely linked to the structures of the source system. It is appropriate to name an invoice table “Invoice” rather than “SAP-Invoice”. Data Warehousing does not involve storing operational data in a one-to-one manner.

At project initiation, it is advisable to eschew surrogate keys and instead integrate source system tables one-to-one, as this fosters agility. Nonetheless, deferring this approach incurs greater costs toward the end of the development cycle, with subsequent changes becoming significantly more expensive.

Teradata Surrogates Keys and Performance

Surrogate keys can enhance performance by replacing multiple natural key columns with a single integer column. However, this benefit only applies if the character set conversions occur during joins, as the hashing algorithm’s benefit in other cases would be negligible. Nevertheless, switching from character column natural keys to surrogate keys could result in a significant performance advantage, as outlined here: