How to Create and Use Surrogate Keys in a Data Warehouse

In a Data Warehouse, linking fact tables to dimensions through the original source-system key (for example, a customer code) causes problems: codes can change, be reused, or come from several systems. The solution is a surrogate key — an artificial, numeric key with no business meaning, generated by the Data Warehouse itself. Here you will learn, step by step, how to create and use surrogate keys in T-SQL.

Prerequisites

• A SQL Server (or Azure SQL) instance with permissions to create tables.
• A query tool such as SQL Server Management Studio or Azure Data Studio.
• A basic understanding of dimension and fact tables.
• A staging table with source data (we use stg_Cliente and stg_Vendas as an example).

Step 1: Understand the difference between business key and surrogate key

The business key (or natural key) is the identifier that comes from the source, such as CodigoCliente = 'CLI-045'. The surrogate key is a sequential integer (1, 2, 3, …) that only exists inside the Data Warehouse. We store both in the dimension: the surrogate key acts as the primary key and the link to the facts; the business key is used to recognise the row during loading.

Step 2: Create the dimension with a surrogate key

In SQL Server, the simplest way to generate surrogate keys is the IDENTITY property, which assigns a new number to every inserted row.

CREATE TABLE DimCliente (
    ClienteSK     INT IDENTITY(1,1) PRIMARY KEY,  -- surrogate key
    CodigoCliente VARCHAR(20) NOT NULL,           -- natural key
    Nome          NVARCHAR(100),
    Cidade        NVARCHAR(60)
);

IDENTITY(1,1) starts at 1 and increments by 1 for each row. The ClienteSK column is the primary key; CodigoCliente keeps the original source value. Notice that the surrogate key has no business meaning: it is just a counter. That is what makes it stable — even if the customer code changes at the source, ClienteSK stays the same.

Step 3: Insert the "unknown" member and load the dimension

Before loading real data, create a special row with surrogate key -1 to represent missing or unknown values. This prevents facts without an associated dimension.

SET IDENTITY_INSERT DimCliente ON;
INSERT INTO DimCliente (ClienteSK, CodigoCliente, Nome, Cidade)
VALUES (-1, 'N/A', 'N/A', 'N/A');
SET IDENTITY_INSERT DimCliente OFF;

Then load the customers from staging, inserting only those that do not exist yet. SQL Server generates the surrogate key automatically.

INSERT INTO DimCliente (CodigoCliente, Nome, Cidade)
SELECT s.CodigoCliente, s.Nome, s.Cidade
FROM stg_Cliente AS s
WHERE NOT EXISTS (
    SELECT 1 FROM DimCliente AS d
    WHERE d.CodigoCliente = s.CodigoCliente
);

Step 4: Use the surrogate key in the fact table

In the fact table we store the surrogate key, never the business key. During loading, we do a lookup against the dimension to translate the source code into the matching surrogate key.

INSERT INTO FactVendas (ClienteSK, DataSK, Valor)
SELECT
    COALESCE(d.ClienteSK, -1) AS ClienteSK,   -- -1 = unknown member
    v.DataSK,
    v.Valor
FROM stg_Vendas AS v
LEFT JOIN DimCliente AS d
    ON d.CodigoCliente = v.CodigoCliente;

The LEFT JOIN with COALESCE(..., -1) ensures that a sale with an unknown customer is linked to the -1 member instead of losing the row.

Best practice: never show the surrogate key to the end user or use it in business rules. It exists only to join tables inside the model.

Step 5: Alternative with SEQUENCE

When you need to control numbering outside the table (for example, sharing the same sequence across processes), use a SEQUENCE object instead of IDENTITY.

CREATE SEQUENCE seq_ProdutoSK START WITH 1 INCREMENT BY 1;

CREATE TABLE DimProduto (
    ProdutoSK     INT PRIMARY KEY
                  DEFAULT (NEXT VALUE FOR seq_ProdutoSK),
    CodigoProduto VARCHAR(20) NOT NULL,
    Descricao     NVARCHAR(200)
);

Check the result

Confirm that the dimension has sequential surrogate keys and the -1 member:

SELECT ClienteSK, CodigoCliente, Nome
FROM DimCliente
ORDER BY ClienteSK;

Then check that no fact row is left without a key. The result should be 0:

SELECT COUNT(*) AS FactosSemChave
FROM FactVendas
WHERE ClienteSK IS NULL;

Conclusion

With surrogate keys, your Data Warehouse is protected from changes to source keys, combines data from several systems without collisions, and sets the stage for SCD Type 2 dimensions, where the same business key can have several versions. The natural next step is to apply this pattern to every dimension and create indexes on the surrogate key columns of the facts. Have you thought about how you will handle the keys when two source systems use the same code for different customers?