Identifying and Classifying Degenerate and Junk Dimensions

Hello! In the previous lesson, you gave order date and requested ship date separate roles while keeping one physical calendar. Your order-line fact also retained an order number. That number raises a different modeling question: if analysts need it, but it has no descriptive attributes of its own, should it have a dimension table?

This lesson covers two ways to place analytical context correctly. A degenerate dimension keeps a transaction identifier in the fact table without a corresponding dimension table. A junk dimension collects several small, otherwise unrelated classifications into one dimension table. You’ll use both patterns on the order-line grain established previously. Estimated study time: about 40 minutes.

A transaction identifier without a dimension table

Recall the grain: one fact row represents one order line. An order may have several such rows. Its number is useful for finding all the lines on the same order, even though it cannot be summed like quantity or net amount.

Watch Degenerate Dimension from LeapFrogBI for the distinction between an identifier and a measure. The order-line example is particularly close to the fact table you already have.

Here is another star-schema depiction. Its TransactionID sits in Fact Sales, while product, promotion, and date have their own dimension tables.

The diagram establishes where the transaction identifier goes, but it does not establish the grain or a unique key for every possible sales implementation. If a transaction contains several lines, its TransactionID repeats. For your order-line fact, order_number plays the same grouping role, while order_line_number distinguishes lines within an order:

The first two rows belong to one order with a net amount of 65. Their order number is not a foreign key to a dimension table, and neither the text O17 nor a numeric transaction ID is a measure to SUM. It is useful as a grouping and tracing identifier:

SELECT order_number, SUM(net_amount) AS order_net_amount
FROM fact_order_line
GROUP BY order_number;

The identifier may also let you reconcile warehouse lines against source transactions. Keep its scope of uniqueness explicit: if two source systems can both issue O17, an order number alone cannot identify the same business transaction across them. You need a source-qualified identifier or another reliable mapping. Preserve leading zeros if they are part of an operational identifier.

The absence of a dimension table is the deciding feature—not the spelling “order number.” If an order header has independently useful descriptive attributes that analysts must filter or group by, examine where those attributes belong rather than declaring them all “degenerate.” Conversely, do not create a table containing only one row per order and little besides the order number merely to give that number a foreign key. Table growth similar to fact growth is a warning to investigate, not proof: a legitimate descriptive dimension can also be large.

Several small classifications with one shared home

Now suppose each order records one payment method and whether the sale was assisted, while each line records whether that item was gift-wrapped. Analysts want to break down net amount by those properties. None is a measure, and creating three tiny dimension tables would add three foreign keys to every line.

A junk dimension groups such low-cardinality, analytically useful classifications into a single row representing their combination. The IBM Redbook also calls this a garbage dimension; both names refer to this design pattern.

Watch the central example in Why do we need Junk Dimension from aroundBI. Treat its proposed fields as candidates, not a rule that every low-cardinality field belongs in the same table.

That last qualification prevents a common mistake. Low cardinality alone does not make an attribute “junk.” If every store has a stable store_type, it describes the store and usually belongs in dim_store. If an order’s payment method can differ from one purchase to the next, it does not describe a particular store or product. It is a sensible candidate for transaction context. The same placement test applies to each proposed attribute: determine what it describes and whether it has one value for each fact row.

For this example, assume one payment method and one assistance status per order, and one gift-wrap status per order line. Every line consequently has one value of each. A possible dim_transaction_context is:

With three payment methods and two states each for wrapping and assistance, there are at most $3\times 2\times 2=12$ combinations of the stated known values. You could create all 12 in advance, or store only combinations observed in source data. The fact contains one context_key, not three copied labels or three separate foreign keys.

This design does not require the three attributes to form a hierarchy or to be causally related. It provides a compact place to expose their readable values. If the number of possible combinations becomes very large, however, combining every classification into one table may be counterproductive; reconsider which attributes belong together. Nor is a junk dimension mandatory for every lone Boolean: a few directly stored flags may be simpler, depending on the reporting interface and workload.

Assigning the combination during a fact load

Kimball Group’s Design Tip #113: Creating, Using, and Maintaining Junk Dimensions adds the ETL detail that a schema drawing can conceal: each incoming fact row must resolve its whole combination to the correct dimension key.

In PostgreSQL, the illustrative dimension can enforce one key per distinct combination:

CREATE TABLE dim_transaction_context (
    context_key        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_method     text NOT NULL,
    gift_wrap_status   text NOT NULL,
    assistance_status  text NOT NULL,
    UNIQUE (payment_method, gift_wrap_status, assistance_status)
);

INSERT INTO dim_transaction_context (
    payment_method, gift_wrap_status, assistance_status
)
VALUES
    ('Card', 'No', 'Yes'),
    ('Cash', 'Yes', 'No'),
    ('Card', 'Yes', 'Yes');

The previous lesson’s fact_order_line already contains the dates, order-line identifier, and measures. You can extend it with a reference to this dimension:

ALTER TABLE fact_order_line
ADD COLUMN context_key bigint
REFERENCES dim_transaction_context (context_key);

For an existing populated fact table, adding the column nullable first allows you to backfill its keys. After every applicable row has a valid key, you can make it NOT NULL. During a load, match all three source classifications to a dimension row; matching payment method alone would select the wrong context when wrapping or assistance differs.

Unknown values need an explicit policy. “Payment method unknown” is not the same as “cash,” and “gift wrapping not applicable” is not automatically “no.” Create appropriate descriptive combinations for genuine unknown or not-applicable states, or quarantine unexpected source values for resolution. Ensure a needed combination exists before loading the fact that references it; do not silently assign an unrelated combination just to satisfy the foreign key.

Once resolved, a payment-method breakdown has the familiar star-schema shape:

SELECT
    c.payment_method,
    SUM(f.net_amount) AS net_amount
FROM fact_order_line AS f
JOIN dim_transaction_context AS c
  ON f.context_key = c.context_key
GROUP BY c.payment_method;

Each fact row must match exactly one context row, so this join preserves line-level amounts. By contrast, the number of orders requires COUNT(DISTINCT f.order_number), not COUNT(*): the latter counts order lines. That distinction comes from the grain, regardless of whether you use a junk dimension.

Conclusion

Use a degenerate dimension when a transaction identifier belongs with fact rows but has no separate descriptive dimension to join. Use a junk dimension when several small classifications need readable analytical context and can be represented by a manageable set of combinations. In both cases, start with the fact grain: an order number can repeat across lines, while each context key must identify exactly one combination for each line.

Next, you’ll decide what to do when a descriptive attribute changes over time—whether to overwrite it, retain a new dimension row, or keep a limited previous value.