How to create a Degenerate Dimension in Data Modeling (Kimball)

João Barros 09 de September de 2026 4 min read

This tutorial shows how to create a Degenerate Dimension following Kimball principles: when and why to keep transaction attributes directly in the fact table instead of creating a separate dimension. Learning to model degenerate dimensions helps simplify queries and reduce unnecessary joins in invoice, order, or single-transaction scenarios.

Prerequisites

Basic knowledge of SQL (SELECT, JOIN, INSERT).
Understanding of the star schema concept and fact/dimension.
A test database (for example SQL Server, PostgreSQL, or MySQL).

Step 1: Identify when to use a Degenerate Dimension

A Degenerate Dimension is an attribute tied to the transaction that does not require an identity or additional attributes that would justify a separate dimension. Typical examples: invoice number, transaction code, external reference. Use it when the attribute is unique per fact row and will not be shared across fact tables.

Step 2: Check query requirements and granularity

Before deciding to keep the field in the fact table, confirm that reports do not need additional attributes, history, or compliance that would require a dimension. Consider performance: if many reports only filter by that field, a Degenerate Dimension (in the fact) reduces joins.

Step 3: Define the fact table structure with the Degenerate Dimension

Create the fact table including the fact key (surrogate key or natural), measures, and the degenerate field as a column not referenced to a dimension. Ensure the appropriate data type and indexing when needed for frequent filters.

-- Exemplo em SQL (SQL Server sintaxe genérica)  CREATE TABLE FactSales (
  SaleID BIGINT PRIMARY KEY,       -- surrogate key da fact
  InvoiceNumber VARCHAR(50) NOT NULL, -- Degenerate Dimension
  CustomerKey INT NOT NULL,         -- FK para Dimension Customer
  ProductKey INT NOT NULL,          -- FK para Dimension Product
  SaleDateKey INT NOT NULL,         -- FK para Dimension Date
  Quantity INT,
  Amount DECIMAL(18,2)
);

-- Índice para procurar por InvoiceNumber rapidamente
CREATE INDEX IX_FactSales_InvoiceNumber ON FactSales(InvoiceNumber);

Step 4: ETL/ELT load: populate the Degenerate Dimension

In the ETL/ELT process, simply map the transaction field value directly to the fact column. Do not attempt to create a separate surrogate key. Handle duplicates and normalization only if necessary to ensure integrity (e.g., trimming spaces, consistent formatting).

-- Exemplo simples de INSERT durante ETL  INSERT INTO FactSales (SaleID, InvoiceNumber, CustomerKey, ProductKey, SaleDateKey, Quantity, Amount)
SELECT
  s.SourceSaleID, -- pode ser surrogate gerado
  TRIM(s.InvoiceNo),
  d.CustomerKey,
  p.ProductKey,
  dd.DateKey,
  s.Quantity,
  s.TotalAmount
FROM StagingSales s
JOIN DimCustomer d ON s.CustomerID = d.CustomerID
JOIN DimProduct p ON s.ProductCode = p.ProductCode
JOIN DimDate dd ON CAST(s.SaleDate AS DATE) = dd.FullDate
WHERE s.IsActive = 1;

Step 5: Handle common errors

Common errors: 1) Treating InvoiceNumber as a dimension and then discovering it is unique per row — creates redundancy. 2) Not indexing InvoiceNumber when used in filters — slow queries. 3) Assuming immutability when numbers can change (e.g., invoice correction) — define a policy: update the fact or create a correction event.

Verify the result

Confirm that the Degenerate Dimension works by running real queries: filtering by InvoiceNumber, aggregations by Customer, and checking execution plans. Tests to perform:

SELECT by InvoiceNumber — should return the correct row and be fast.
Counts of distinct invoices — compare with the source.
Reports that aggregate by Customer/Date — ensure there are no excessive joins.

-- Exemplos de verificação
-- 1) Procurar fatura específica
SELECT * FROM FactSales WHERE InvoiceNumber = 'INV-2026-0001';

-- 2) Contar invoices distintos por dia
SELECT SaleDateKey, COUNT(DISTINCT InvoiceNumber) AS NumInvoices
FROM FactSales
GROUP BY SaleDateKey;

Conclusion

Keeping a Degenerate Dimension in the fact table simplifies the model when the attribute is unique per transaction and has no attributes of its own. Next steps: assess indexing, update policies, and reporting impact. Tip: always document why a field is degenerate — this will save the team time when optimizing queries.