How to create a junk dimension in a Data Warehouse

João Barros 05 de July de 2026 4 min read

A junk dimension is a modelling technique that groups several low-cardinality indicators — statuses, flags and options — into a single Data Warehouse dimension. Instead of cluttering the fact table with loose columns or building a mini-dimension for every flag, you gather them all into one small, easy-to-query table. Let's walk through, step by step and with SQL examples, how to create a junk dimension and connect it to the fact table.

Prerequisites

Access to a SQL Data Warehouse (SQL Server, Microsoft Fabric or Azure Synapse).
Permissions to create tables and insert data.
Basic knowledge of the star schema: dimensions and fact tables.

Step 1: Identify the candidate attributes

The first step is to find columns with few distinct values and no descriptive attributes of their own. Picture a sales table with three flags: payment status (Paid or Pending), channel (Online or Store) and gift (Yes or No). Each one has just two values. On their own they don't justify a dimension, and if they stay in the fact table they fill it with repeated text columns. Bundling them into one dimension avoids both a wide fact table and a swarm of tiny dimensions that are hard to maintain.

Tip: a good junk dimension candidate has low cardinality and rarely changes. Dates or customers, for example, do not belong here.

Step 2: Create the junk dimension table

We create a table with a surrogate key and one column per flag. The key will be the only column the fact table needs to store.

CREATE TABLE DimTransacao (
    TransacaoKey    INT         NOT NULL,
    EstadoPagamento VARCHAR(20) NOT NULL,
    Canal           VARCHAR(20) NOT NULL,
    Oferta          VARCHAR(3)  NOT NULL
);

Notice that we store no metrics or dates here: the junk dimension holds only the flags and its key.

Step 3: Generate every possible combination

The core idea is to fill the dimension with the cartesian product of all values. With CROSS JOIN we combine the lists and, with ROW_NUMBER(), we generate the surrogate key automatically.

INSERT INTO DimTransacao (TransacaoKey, EstadoPagamento, Canal, Oferta)
SELECT
    ROW_NUMBER() OVER (ORDER BY p.EstadoPagamento, c.Canal, o.Oferta),
    p.EstadoPagamento,
    c.Canal,
    o.Oferta
FROM       (VALUES ('Pago'), ('Pendente')) AS p(EstadoPagamento)
CROSS JOIN (VALUES ('Online'), ('Loja'))   AS c(Canal)
CROSS JOIN (VALUES ('Sim'), ('Nao'))       AS o(Oferta);

Since we have 2 × 2 × 2 values, the table ends up with 8 rows — every possible combination. It is also good practice to add an "unknown" member for fact rows with no match:

INSERT INTO DimTransacao (TransacaoKey, EstadoPagamento, Canal, Oferta)
VALUES (-1, 'Desconhecido', 'Desconhecido', 'N/D');

Step 4: Connect the dimension to the fact table

When loading the fact table, we look up the TransacaoKey that matches the three flags and store only that key. The fact table no longer carries the text columns and makes a single small, fast join instead.

SELECT
    v.VendaID,
    dt.TransacaoKey,
    v.Valor
FROM   Staging_Vendas AS v
JOIN   DimTransacao   AS dt
       ON dt.EstadoPagamento = v.EstadoPagamento
      AND dt.Canal           = v.Canal
      AND dt.Oferta          = v.Oferta;

Verify the result

Confirm that the dimension has the expected combinations and try an analytical query grouping by the junk dimension attributes.

SELECT COUNT(*) AS TotalLinhas FROM DimTransacao;

SELECT dt.Canal, dt.EstadoPagamento, SUM(f.Valor) AS Total
FROM   FactVendas   AS f
JOIN   DimTransacao AS dt ON dt.TransacaoKey = f.TransacaoKey
GROUP BY dt.Canal, dt.EstadoPagamento;

If the first query returns 9 rows (8 combinations plus the unknown member) and the second shows totals by channel and status, everything is working.

Conclusion

With a junk dimension, the fact table becomes lighter, filters get simpler, and the model stays clean even when there are many small flags. The next step is to look at your own fact tables and spot scattered flags that could be gathered into a single dimension. How many flags are floating around in your model?