Junk dimension

Example

Copy code
-- Instead of three separate dimension tables for three flags,
-- combine every combination that occurs into one junk dimension
CREATE TABLE dim_order_flags (
    order_flags_key INTEGER PRIMARY KEY,
    is_gift_wrapped BOOLEAN,
    payment_method  VARCHAR,
    order_priority  VARCHAR
);

INSERT INTO dim_order_flags VALUES
    (1, true,  'credit_card', 'standard'),
    (2, false, 'credit_card', 'standard'),
    (3, false, 'paypal',      'expedited');

CREATE TABLE fact_orders (
    order_id INTEGER PRIMARY KEY,
    order_flags_key INTEGER REFERENCES dim_order_flags(order_flags_key),
    revenue DECIMAL(10,2)
);

Because the attributes are low-cardinality, the junk dimension itself stays small — a handful to a few hundred rows covering every combination actually seen — even though it replaces what would otherwise be three or more separate single-attribute dimension tables and foreign keys on the fact table.

When to use one

Junk dimensions make sense when the flags are genuinely unrelated to each other and to any existing dimension, and low enough cardinality that the cross-product of their values is manageable. If an attribute is naturally part of an existing dimension (e.g., a product flag that belongs with dim_product), put it there instead of in a junk dimension.

DuckDB angle

Building a junk dimension is a straightforward SELECT DISTINCT over the flag columns you want to combine, which DuckDB executes quickly even over large fact tables:

Copy code
CREATE TABLE dim_order_flags AS
SELECT DISTINCT is_gift_wrapped, payment_method, order_priority
FROM staging_orders;