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

This tutorial shows how to create a Degenerate Dimension that supports rolling attributes in Data Modeling (Kimball), useful when a transaction identifier carries small attributes that change frequently and do not justify a separate dimension. The technique reduces joins and maintains performance without losing traceability.

Prerequisites

• Basic knowledge of Data Modeling (Kimball) and star schema.
• Access to SQL (e.g.: SQL Server, PostgreSQL or equivalent) for testing.
• Examples of transaction data with small, mutable attributes (e.g.: short state, quick codes).

Step 1: Understand when to use a Degenerate Dimension with rolling attributes

A Degenerate Dimension (DD) is a set of attributes stored in the fact table or linked to the fact by a degenerate identifier. Here we want to support "rolling attributes": small values that change frequently per transaction (e.g.: short state, flags, external codes) that do not justify a wide dimension. We use the DD to avoid creating many small dimensions and to speed up aggregate queries.

Step 2: Choose attributes and define retention rules

Identify which attributes are candidates: they are typically short (varchar/char), have a low number of distinct values and high volatility. Define rules: which attributes go into the DD, the temporal granularity (latest version per transaction, history per event) and the retention period (e.g.: 90 days).

Step 3: Model the structure in the star schema

There are two common approaches: (A) store the attributes directly in the fact table as degenerate columns; (B) create a separate Degenerate Dimension table with degenerate_id key and link to the fact via that key. Option B keeps the fact more compact if there are multiple rolling attributes and multiple versions per transaction.

-- Exemplo: criar Dimension Degenerate separada (PostgreSQL/SQL Server sintaxe genérica)
CREATE TABLE dim_degenerate (
  degenerate_id BIGINT IDENTITY PRIMARY KEY,
  transaction_id VARCHAR(50) NOT NULL,
  attribute_key VARCHAR(50) NOT NULL,
  attribute_value VARCHAR(200),
  valid_from TIMESTAMP NOT NULL,
  valid_to TIMESTAMP NULL
);

CREATE INDEX idx_deg_txn_key ON dim_degenerate(transaction_id, attribute_key, valid_from);

-- Fact table guarda degenerate_id (última versão ou referência por evento)
CREATE TABLE fact_sales (
  sale_key BIGINT IDENTITY PRIMARY KEY,
  sale_date DATE NOT NULL,
  transaction_id VARCHAR(50) NOT NULL,
  degenerate_id BIGINT NULL,
  amount DECIMAL(18,2),
  FOREIGN KEY (degenerate_id) REFERENCES dim_degenerate(degenerate_id)
);

Step 4: Define ETL/ELT process for rolling attributes

The process should insert new versions into dim_degenerate and update links in the fact according to the rule (e.g.: always link to the most recent version when closing the transaction). Use merge/upsert to keep history with valid_from/valid_to or overwrite when history is not required.

-- Exemplo MERGE (SQL Server) para inserir nova versão e fechar anterior
MERGE INTO dim_degenerate AS target
USING (VALUES (@transaction_id, @attribute_key, @attribute_value, @now))
       AS source(transaction_id, attribute_key, attribute_value, valid_from)
ON target.transaction_id = source.transaction_id
   AND target.attribute_key = source.attribute_key
   AND target.valid_to IS NULL
WHEN MATCHED AND target.attribute_value <> source.attribute_value
  THEN UPDATE SET valid_to = source.valid_from
WHEN NOT MATCHED BY TARGET
  THEN INSERT (transaction_id, attribute_key, attribute_value, valid_from)
       VALUES (source.transaction_id, source.attribute_key, source.attribute_value, source.valid_from);

-- Depois, ajustar fact_sales.degenerate_id para apontar para a nova versão (poderá usar SELECT para obter degenerate_id recém-criada)

Step 5: Queries and aggregations: use the DD efficiently

For reports, combine the fact with dim_degenerate filtering by attribute_key and time window. For performance, prefer linking via degenerate_id in the fact when possible; otherwise, join by transaction_id + validity.

-- Exemplo: obter último attribute_value por venda
SELECT f.sale_key, f.amount, d.attribute_value
FROM fact_sales f
LEFT JOIN dim_degenerate d
  ON f.degenerate_id = d.degenerate_id;

-- Ou, se não tiver degenerate_id, buscar por transaction_id e data
SELECT f.sale_key, f.amount, d.attribute_value
FROM fact_sales f
LEFT JOIN dim_degenerate d
  ON d.transaction_id = f.transaction_id
  AND d.valid_from <= f.sale_date
  AND (d.valid_to IS NULL OR d.valid_to > f.sale_date)
  AND d.attribute_key = 'status';

Verify the result

Validate that: (1) each transaction has the correct version of the attribute (see examples with dates); (2) aggregate queries use indexes and are fast; (3) the volume of versions in dim_degenerate matches expectations (e.g.: there are no duplicate versions). Simple tests: compare counts between the event source and dim_degenerate, and verify that fact_sales points to the correct degenerate_id.

Conclusion

A Degenerate Dimension with rolling attributes reduces complexity when there are many short, volatile attributes, preserving performance and history when needed. Next steps: define purge policies, monitor cardinality and consider compression/partitioning. Tip: start with a few keys and validate retention rules before generalizing to all transactions.