Como criar e usar surrogate keys num Data Warehouse

Num Data Warehouse, ligar as tabelas de factos às dimensões pela chave original do sistema de origem (por exemplo, um código de cliente) traz problemas: os códigos podem mudar, ser reutilizados ou vir de vários sistemas. A solução é usar uma surrogate key — uma chave artificial, numérica e sem significado de negócio, gerada pelo próprio Data Warehouse. Aqui vais aprender, passo a passo, como criar e usar surrogate keys em T-SQL.

Pré-requisitos

• Uma instância de SQL Server (ou Azure SQL) com permissões para criar tabelas.
• Uma ferramenta de consulta como o SQL Server Management Studio ou o Azure Data Studio.
• Noções básicas de tabelas de dimensão e de factos.
• Uma tabela de staging com dados de origem (usamos stg_Cliente e stg_Vendas como exemplo).

Passo 1: Perceber a diferença entre chave de negócio e surrogate key

A chave de negócio (ou natural key) é o identificador que vem da origem, como CodigoCliente = 'CLI-045'. A surrogate key é um número inteiro sequencial (1, 2, 3, …) que só existe dentro do Data Warehouse. Guardamos as duas na dimensão: a surrogate key serve de chave primária e de ligação às factos; a chave de negócio serve para reconhecer a linha durante o carregamento.

Passo 2: Criar a dimensão com uma surrogate key

Em SQL Server, a forma mais simples de gerar surrogate keys é a propriedade IDENTITY, que atribui um número novo a cada linha inserida.

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

O IDENTITY(1,1) começa em 1 e incrementa 1 a cada linha. A coluna ClienteSK é a chave primária; a CodigoCliente mantém o valor original da origem. Repara que a surrogate key não tem qualquer significado de negócio: é apenas um contador. É isso que a torna estável — mesmo que o código do cliente mude na origem, a ClienteSK permanece igual.

Passo 3: Inserir o membro "desconhecido" e carregar a dimensão

Antes de carregar dados reais, cria uma linha especial com a surrogate key -1 para representar valores em falta ou desconhecidos. Isto evita factos sem dimensão associada.

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;

Depois, carrega os clientes a partir da staging, inserindo apenas os que ainda não existem. O SQL Server gera a surrogate key automaticamente.

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

Passo 4: Usar a surrogate key na tabela de factos

Na tabela de factos guardamos a surrogate key, nunca a chave de negócio. Durante o carregamento, fazemos um lookup à dimensão para traduzir o código de origem na surrogate key correspondente.

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;

O LEFT JOIN com COALESCE(..., -1) garante que uma venda com um cliente desconhecido fica ligada ao membro -1, em vez de perder a linha.

Boa prática: nunca mostres a surrogate key ao utilizador final nem a uses em regras de negócio. Ela serve apenas para ligar tabelas dentro do modelo.

Passo 5: Alternativa com SEQUENCE

Quando precisas de controlar a numeração fora da tabela (por exemplo, partilhar a mesma sequência entre processos), usa um objeto SEQUENCE em vez 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)
);

Verificar o resultado

Confirma que a dimensão tem surrogate keys sequenciais e o membro -1:

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

Depois, verifica que nenhuma linha de factos ficou sem chave. O resultado deve ser 0:

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

Conclusão

Com surrogate keys, o teu Data Warehouse fica protegido de alterações nas chaves de origem, junta dados de vários sistemas sem colisões e prepara o terreno para dimensões de tipo SCD Tipo 2, onde a mesma chave de negócio pode ter várias versões. O próximo passo natural é aplicar este padrão a todas as dimensões e criar índices nas colunas de surrogate key das factos. Já pensaste em como vais tratar as chaves quando dois sistemas de origem usam o mesmo código para clientes diferentes?