How to Create a Junk Dimension in a Data Warehouse

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

In a fact table, it is common to accumulate small indicators — yes/no, statuses, channel types — that take up space and make the model confusing. The word "junk" does not mean garbage: it refers to those scattered, low-cardinality flags that, on their own, do not justify a dimension. A junk dimension groups them all into a single dimension, keeping the fact table cleaner and the star schema easier to query.

Prerequisites

A data warehouse with a star schema already started.
Access to a SQL database (the examples use T-SQL syntax, easy to adapt to other engines).
A fact table with several columns of low-cardinality flags or indicators.
Basic knowledge of surrogate keys and ETL processes.

Step 1: Identify the low-cardinality flags

Start by listing the fact table columns that have few distinct values and do not belong to any existing dimension. Typical examples: an "is promotion" flag (Yes/No), the "sales channel" (Store/Online/Phone), or the "order status" (New/Shipped/Returned). Avoid including columns with many distinct values, such as a customer code — those deserve their own dimension.

A quick query confirms the cardinality of each candidate:

SELECT
    COUNT(DISTINCT is_promocao) AS n_promocao,
    COUNT(DISTINCT canal_venda) AS n_canal,
    COUNT(DISTINCT estado)      AS n_estado
FROM staging.vendas;

Step 2: Generate the combinations

The junk dimension stores one row for each unique combination of flags. Since the values are few, you have two options. The simplest is to insert only the combinations that actually occur in the data, with a SELECT DISTINCT over the three columns together. If you want to anticipate every possible combination (even those that have not appeared yet), use a CROSS JOIN between the lists of distinct values:

SELECT p.is_promocao, c.canal_venda, e.estado
FROM (SELECT DISTINCT is_promocao FROM staging.vendas) AS p
CROSS JOIN (SELECT DISTINCT canal_venda FROM staging.vendas) AS c
CROSS JOIN (SELECT DISTINCT estado FROM staging.vendas) AS e;

For most cases, inserting only the real combinations keeps the dimension small and efficient.

Step 3: Create the table with a surrogate key

Create the dimension with a numeric surrogate key and the descriptive columns. That key, generated automatically with IDENTITY, will be the only reference stored in the fact table:

CREATE TABLE dim_junk_venda (
    junk_key    INT IDENTITY(1,1) PRIMARY KEY,
    is_promocao VARCHAR(3),
    canal_venda VARCHAR(20),
    estado      VARCHAR(20)
);

INSERT INTO dim_junk_venda (is_promocao, canal_venda, estado)
SELECT DISTINCT is_promocao, canal_venda, estado
FROM staging.vendas;

Each row now has a unique junk_key that represents a combination of flags.

Tip: keep the junk dimension small. If a flag has hundreds of distinct values, it probably deserves its own dimension rather than belonging here.

Step 4: Link the junk dimension to the fact table

When loading the fact table, look up the junk_key matching each row and store only that key, replacing the several flag columns:

INSERT INTO fact_vendas (data_key, produto_key, junk_key, quantidade, valor)
SELECT
    v.data_key,
    v.produto_key,
    dj.junk_key,
    v.quantidade,
    v.valor
FROM staging.vendas AS v
JOIN dim_junk_venda AS dj
    ON  dj.is_promocao = v.is_promocao
    AND dj.canal_venda = v.canal_venda
    AND dj.estado      = v.estado;

The fact table ends up with a single column (junk_key) instead of three, which reduces the space used and simplifies user queries.

Verify the result

Confirm that every fact record has a valid junk_key. If this query returns zero, all rows were linked correctly:

SELECT COUNT(*) AS factos_sem_junk
FROM fact_vendas
WHERE junk_key IS NULL;

You can also review the most frequent combinations to validate that the data makes sense:

SELECT dj.canal_venda, dj.estado, COUNT(*) AS n
FROM fact_vendas AS f
JOIN dim_junk_venda AS dj ON dj.junk_key = f.junk_key
GROUP BY dj.canal_venda, dj.estado
ORDER BY n DESC;

Conclusion

With a junk dimension, you have turned several scattered flag columns into a compact, reusable dimension, keeping the fact table lighter and the star schema clearer. The next step is to review other fact tables looking for similar indicators that could share the same approach. Which low-cardinality flags do you have in your fact table today that would live better in a junk dimension?