Cómo crear y usar surrogate keys en un Data Warehouse

En un Data Warehouse, enlazar las tablas de hechos con las dimensiones mediante la clave original del sistema de origen (por ejemplo, un código de cliente) provoca problemas: los códigos pueden cambiar, reutilizarse o venir de varios sistemas. La solución es una surrogate key — una clave artificial, numérica y sin significado de negocio, generada por el propio Data Warehouse. Aquí aprenderás, paso a paso, cómo crear y usar surrogate keys en T-SQL.

Requisitos previos

• Una instancia de SQL Server (o Azure SQL) con permisos para crear tablas.
• Una herramienta de consulta como SQL Server Management Studio o Azure Data Studio.
• Nociones básicas de tablas de dimensión y de hechos.
• Una tabla de staging con datos de origen (usamos stg_Cliente y stg_Vendas como ejemplo).

Paso 1: Entender la diferencia entre clave de negocio y surrogate key

La clave de negocio (o natural key) es el identificador que viene del origen, como CodigoCliente = 'CLI-045'. La surrogate key es un número entero secuencial (1, 2, 3, …) que solo existe dentro del Data Warehouse. Guardamos las dos en la dimensión: la surrogate key actúa como clave primaria y como enlace con los hechos; la clave de negocio sirve para reconocer la fila durante la carga.

Paso 2: Crear la dimensión con una surrogate key

En SQL Server, la forma más sencilla de generar surrogate keys es la propiedad IDENTITY, que asigna un número nuevo a cada fila insertada.

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) empieza en 1 e incrementa 1 por cada fila. La columna ClienteSK es la clave primaria; CodigoCliente mantiene el valor original del origen. Fíjate en que la surrogate key no tiene ningún significado de negocio: es solo un contador. Eso es lo que la hace estable — aunque el código del cliente cambie en el origen, ClienteSK permanece igual.

Paso 3: Insertar el miembro "desconocido" y cargar la dimensión

Antes de cargar datos reales, crea una fila especial con la surrogate key -1 para representar valores ausentes o desconocidos. Esto evita hechos sin dimensión asociada.

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;

Después, carga los clientes desde staging, insertando solo los que aún no existen. SQL Server genera la surrogate key automáticamente.

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
);

Paso 4: Usar la surrogate key en la tabla de hechos

En la tabla de hechos guardamos la surrogate key, nunca la clave de negocio. Durante la carga, hacemos un lookup contra la dimensión para traducir el código de origen en la surrogate key correspondiente.

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;

El LEFT JOIN con COALESCE(..., -1) garantiza que una venta con un cliente desconocido queda enlazada al miembro -1, en lugar de perder la fila.

Buena práctica: nunca muestres la surrogate key al usuario final ni la uses en reglas de negocio. Solo sirve para enlazar tablas dentro del modelo.

Paso 5: Alternativa con SEQUENCE

Cuando necesitas controlar la numeración fuera de la tabla (por ejemplo, compartir la misma secuencia entre procesos), usa un objeto SEQUENCE en lugar de 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)
);

Comprobar el resultado

Confirma que la dimensión tiene surrogate keys secuenciales y el miembro -1:

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

Luego comprueba que ninguna fila de hechos se quedó sin clave. El resultado debe ser 0:

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

Conclusión

Con surrogate keys, tu Data Warehouse queda protegido frente a cambios en las claves de origen, combina datos de varios sistemas sin colisiones y prepara el terreno para dimensiones de tipo SCD Tipo 2, donde la misma clave de negocio puede tener varias versiones. El siguiente paso natural es aplicar este patrón a todas las dimensiones y crear índices en las columnas de surrogate key de los hechos. ¿Has pensado en cómo vas a tratar las claves cuando dos sistemas de origen usan el mismo código para clientes diferentes?