How to Create a Conformed Dimension in Data Modeling (Kimball)

João Barros 08 de August de 2026 5 min read

This tutorial explains how to create a Conformed Dimension in Data Modeling (Kimball) to share attributes across multiple fact tables. Having conformed dimensions makes reports consistent and reduces redundancy when multiple fact tables use the same entity descriptions.

Prerequisites

Basic knowledge of SQL (SELECT, JOIN, INSERT)
Environment with a relational database (e.g., SQL Server, PostgreSQL)
Examples of fact tables that share the same entity (e.g., Sales and Returns)

Step 1: Identify the entity to conform

Explain why: choosing the correct entity avoids duplication. Look for dimensions with the same concepts (e.g., Customer, Product, Store) used by multiple fact tables. Check for identical or very similar attributes and business rules.

Step 2: Define the Conformed Dimension structure

Create a simple design: surrogate keys, natural key, attributes that describe the entity and useful flags. The Conformed Dimension should contain all common attributes required by each fact table and preserve the necessary granularity.

-- Exemplo: criar a dimensão Cliente_conformed
CREATE TABLE dim_cliente_conformed (
  cliente_sk BIGINT IDENTITY PRIMARY KEY,
  cliente_nk VARCHAR(50) NOT NULL, -- natural key (ex.: customer_id)
  nome VARCHAR(200),
  segmento VARCHAR(50),
  pais VARCHAR(50),
  data_registo DATE,
  CURRENT_FLAG CHAR(1) DEFAULT 'Y'
);

Step 3: Map and migrate data from sources

Explain why: ensuring each source uses the same natural key (or a mapping) is essential. Typically an ETL/ELT process consolidates natural keys and cleans attributes before populating the conformed dimension.

-- Exemplo de MERGE para popular e actualizar dim_cliente_conformed (SQL Server syntax)
MERGE dim_cliente_conformed AS target
USING (
  SELECT DISTINCT customer_id AS cliente_nk, name AS nome, segmento, country AS pais, signup_date AS data_registo
  FROM stg_vendas_customers
  UNION
  SELECT DISTINCT customer_id, name, segment, country, signup
  FROM stg_devolucoes_customers
) AS src
ON target.cliente_nk = src.cliente_nk
WHEN MATCHED AND (target.nome <> src.nome OR target.pais <> src.pais) THEN
  UPDATE SET nome = src.nome, segmento = src.segmento, pais = src.pais, data_registo = src.data_registo
WHEN NOT MATCHED BY TARGET THEN
  INSERT (cliente_nk, nome, segmento, pais, data_registo)
  VALUES (src.cliente_nk, src.nome, src.segmento, src.pais, src.data_registo);

Step 4: Manage surrogate key and mappings in fact tables

Explain why: fact tables should reference the conformed dimension via the surrogate key (cliente_sk). Create a lookup to translate the natural key from sources to the surrogate during the ETL/ELT.

-- Exemplo: transformar fact_vendas para usar cliente_sk
INSERT INTO fact_vendas (venda_id, data_id, cliente_sk, produto_sk, quantidade, valor)
SELECT
  fv.venda_id,
  fv.data_id,
  dc.cliente_sk,
  fv.produto_sk,
  fv.quantidade,
  fv.valor
FROM stg_vendas fv
LEFT JOIN dim_cliente_conformed dc
  ON fv.customer_id = dc.cliente_nk;

Step 5: Handle differences and attribute versioning

Explain why: when sources disagree on attributes, define precedence rules and keep history when needed. To keep history consider Slowly Changing Dimension Type 2 or store previous attributes in a history table.

-- Exemplo simplificado de SCD Type 2 (apenas lógica de marcação)
-- Assumindo colunas: effective_date, end_date, current_flag
UPDATE dim_cliente_conformed
SET current_flag = 'N', end_date = GETDATE()
WHERE cliente_nk = @cliente_nk AND current_flag = 'Y' AND (
  nome <> @nome OR pais <> @pais OR segmento <> @segmento
);

INSERT INTO dim_cliente_conformed (cliente_nk, nome, segmento, pais, data_registo, effective_date, current_flag)
VALUES (@cliente_nk, @nome, @segmento, @pais, @data_registo, GETDATE(), 'Y');

Verify the result

Validate that the fact tables reference the same conformed dimension and that reports show consistent values. Example checks: count unique customers by fact table vs dimension, confirm there are no NULL cliente_sk in the fact tables, and compare attributes between sources and the dimension.

-- Verificações úteis
-- 1. Clientes únicos na dimensão
SELECT COUNT(*) AS total_clientes FROM dim_cliente_conformed;

-- 2. Clientes usados nas fact tables sem correspondência
SELECT fv.customer_id, COUNT(*)
FROM stg_vendas fv
LEFT JOIN dim_cliente_conformed dc ON fv.customer_id = dc.cliente_nk
WHERE dc.cliente_sk IS NULL
GROUP BY fv.customer_id;

-- 3. Conferir consistência de atributo (ex.: segmento)
SELECT dc.cliente_nk, dc.segmento AS segmento_dim, fv.segmento AS segmento_fonte
FROM stg_vendas fv
JOIN dim_cliente_conformed dc ON fv.customer_id = dc.cliente_nk
WHERE dc.segmento <> fv.segmento;

Conclusion

A Conformed Dimension reduces duplication, ensures report consistency and simplifies data warehouse maintenance. Next steps: automate the process in ETL/ELT, apply SCD correctly and document precedence rules. Tip: start with a small conformed dimension (e.g., Customer) and expand when rules are stable — which dimension would make sense to conform first in your project?