Degenerate dimensions: order_id, in depth

Description

Module 1 already named order_id as a degenerate dimension, in passing, without developing it — barely a row in a column-classification table. This lesson fulfills the promise that module left pending: Kimball's full definition, the precise criterion for deciding when an identifier stays as a degenerate dimension and when it needs to become a real table, and an executed demonstration of why building a dim_order table for Kiosko today would be, literally, work with no benefit at all.

Connection to the module. This lesson closes, with evidence, the definition module 1 (lesson 6) left open: "this guide names the concept here, in passing, but develops it in depth — with its full justification and use cases — in module 7." It's also direct preparation for lesson 4: in this lesson you're going to see exactly what order_id would be missing to justify its own table — and in the next one, Kiosko starts capturing two new attributes (payment_method, channel) that would raise the same question if they lived loose inside fact_orders.

An analogy: the invoice number, written on the line itself

Think about a physical purchase invoice, the kind any store still prints: at the top it has an invoice number — Invoice No. 4821 — and that number appears, again, on every line of the purchase detail, next to the product, the quantity, and the price. Nobody files, in a separate folder, a single-line record saying "Invoice 4821 exists" — the invoice number doesn't need its own file, because it doesn't describe anything beyond itself: it has no date of its own different from the sale's, no customer of its own different from the one appearing on the same invoice, no additional attribute worth storing anywhere else. The invoice number lives, correctly, written directly on the same line where it's needed — never in a separate file.

order_id is exactly that invoice number. It has no date of its own (it uses order_ts, already in the same row), no store of its own (it uses store_id, already in the same row), no descriptive attribute that isn't already captured in another fact_orders column. That's why it lives directly inside the fact, with no table of its own — the precise definition of what Kimball calls a degenerate dimension.

Worked example: using order_id with no dim_order table at all

With fact_orders rebuilt, any business question that uses order_id as a grouping unit gets answered directly, with no additional JOIN — exactly the behavior that makes a degenerate dimension precisely "degenerate": it acts as a dimension (groups, identifies), without needing its own table.

# degenerate_in_practice.py
from datetime import datetime

import duckdb

from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS

orders = [
    Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
          unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
    for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)

con = duckdb.connect()
con.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany("INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
      r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders])

print("=== order_id used directly: the 5 highest-revenue orders, with no dim_order table at all ===")
print(con.sql("""
    SELECT order_id, store_id, COUNT(*) AS line_items, ROUND(SUM(revenue), 2) AS order_total
    FROM fact_orders
    GROUP BY order_id, store_id
    ORDER BY order_total DESC, order_id ASC
    LIMIT 5
"""))

Notice the explicit tiebreak, order_id ASC after order_total DESC: without it, SQL guarantees no stable order between rows with the same order_total — and several Kiosko orders do tie at 4.5 — so the result could change from one run to the next over the same data. A second, explicit sort criterion is the only way for a LIMIT over a tie to be byte-for-byte reproducible, the same discipline this guide requires in every "What to expect" block.

What to expect.

=== order_id used directly: the 5 highest-revenue orders, with no dim_order table at all ===
┌──────────┬──────────┬────────────┬─────────────┐
│ order_id │ store_id │ line_items │ order_total │
│ varchar  │ varchar  │   int64    │   double    │
├──────────┼──────────┼────────────┼─────────────┤
│ ORD-3001 │ S02      │          1 │         9.0 │
│ ORD-6004 │ S01      │          1 │         9.0 │
│ ORD-1004 │ S01      │          1 │         4.5 │
│ ORD-2003 │ S03      │          1 │         4.5 │
│ ORD-4004 │ S01      │          1 │         4.5 │
└──────────┴──────────┴────────────┴─────────────┘

GROUP BY order_id works exactly like grouping by any foreign key toward a real dimension — store_id, product_id — without order_id needing any table behind it. Notice line_items: the five highest-revenue orders all have exactly one line — confirm it over the complete domain:

print("\n=== Confirming every Kiosko order has exactly 1 line (grain already declared in M1) ===")
print(con.sql("""
    SELECT COUNT(*) AS total_orders, MAX(line_items) AS max_lines_per_order, MIN(line_items) AS min_lines_per_order
    FROM (SELECT order_id, COUNT(*) AS line_items FROM fact_orders GROUP BY order_id)
"""))

What to expect.

=== Confirming every Kiosko order has exactly 1 line (grain already declared in M1) ===
┌──────────────┬─────────────────────┬─────────────────────┐
│ total_orders │ max_lines_per_order │ min_lines_per_order │
│    int64     │        int64        │        int64        │
├──────────────┼─────────────────────┼─────────────────────┤
│           40 │                   1 │                   1 │
└──────────────┴─────────────────────┴─────────────────────┘

Forty orders, all with exactly one line — the same fact module 1 already verified with COUNT(*) == COUNT(DISTINCT order_id || '-' || product_id), now confirmed from a different angle: MAX(line_items) == MIN(line_items) == 1.

The bad alternative, built on purpose to measure its cost

So this lesson's argument doesn't rest on theory alone, build it: a dim_order_bad table, with the only column that would make any sense — order_id — and nothing else, because Kiosko has no additional attribute to add to it.

# dim_order_bad.py -- continues on top of con and fact_orders from the worked example
con.execute("CREATE TABLE dim_order_bad AS SELECT DISTINCT order_id FROM fact_orders")

print("=== dim_order_bad: how many rows and columns it has ===")
print(con.sql("SELECT COUNT(*) AS total_rows FROM dim_order_bad"))
print(con.sql("DESCRIBE dim_order_bad"))

print("=== Joining against dim_order_bad: same result, one extra JOIN ===")
direct = con.sql("SELECT COUNT(*) FROM fact_orders").fetchone()[0]
joined = con.sql("SELECT COUNT(*) FROM fact_orders f JOIN dim_order_bad d ON f.order_id = d.order_id").fetchone()[0]
print(f"direct (no JOIN): {direct} rows")
print(f"with JOIN against dim_order_bad: {joined} rows")
print(f"identical: {direct == joined}")

What to expect.

=== dim_order_bad: how many rows and columns it has ===
┌────────────┐
│ total_rows │
│   int64    │
├────────────┤
│         40 │
└────────────┘

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ order_id    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

=== Joining against dim_order_bad: same result, one extra JOIN ===
direct (no JOIN): 40 rows
with JOIN against dim_order_bad: 40 rows
identical: True

dim_order_bad: forty rows, a single column, and that column is exactly the same key that already lives in fact_orders. Joining against it adds not a single new piece of data — the row count before and after the JOIN is identical, 40 == 40 — so its only real effect is adding an unnecessary JOIN hop to any query that uses it. This is, in concrete numbers, exactly why Kimball recommends leaving an identifier as a degenerate dimension when it has no attribute of its own: creating the table isn't a catastrophic mistake, but it's work — and query complexity — with no benefit in return.

Diagram: when an identifier stays degenerate, and when it becomes a table

┌────────────────────────────────────────────────────────────────────┐
│  Does the identifier have ATTRIBUTES OF ITS OWN beyond itself?     │
│  (a different date, a status, an address, a note...)                │
└────────────────────────────────────────────────────────────────────┘
                    │                              │
                    NO                             YES
                    │                              │
                    v                              v
    ┌───────────────────────────────┐  ┌───────────────────────────────┐
    │  DEGENERATE DIMENSION          │  │  REAL DIMENSION, own table    │
    │  Lives as a column inside the  │  │  order_id + its own           │
    │  fact. No table, no JOIN.      │  │  attributes, in its own table.│
    │  Kiosko today: order_id        │  │  Example: if Kiosko added a   │
    │                                │  │  "delivery address" and a     │
    │                                │  │  "customer note" per order    │
    └───────────────────────────────┘  └───────────────────────────────┘

Going deeper: what WOULD turn order_id into a real dimension

It's worth being concrete about the boundary, because it isn't an absolute rule — it's a question you ask again every time the business changes. If Kiosko, at some future point, started capturing a delivery address per order (for the delivery channel), or a customer note ("no bag, please"), those two attributes would describe something about the order that no other fact_orders column already captures — and at that point, order_id would stop being degenerate: it would become the primary key of a real dim_order table, with delivery_address and customer_note as its own columns.

This question — does it have attributes of its own, beyond itself? — is exactly the same one you're going to use in lesson 4 to decide what to do with payment_method and channel, the two new attributes Kiosko starts capturing per order. The key difference, which lesson 4 develops: payment_method and channel do describe something new about each order — how it was paid, through which channel it happened — so they can't stay "degenerate" as untreated free text. But they also don't each need their own complete dimension table — that would be over-normalizing two low-cardinality attributes. The middle-ground solution, which no lesson in this guide has used yet, is the junk dimension: grouping several low-cardinality attributes into a single small table, with one flag_key.

Common mistakes

Creating a dim_order table "just in case" it's needed in the future. What happens: someone, anticipating that Kiosko might need order attributes down the road, creates dim_order today, with only order_id as a column, to "be prepared." Why it happens: it seems like a prudent decision, the "better safe than sorry" kind. How to spot it: if your dim_order has a single column and no real consumer needs it today, you're paying the cost of an extra JOIN on every query in exchange for flexibility that doesn't yet exist — exactly what this lesson measured with dim_order_bad. How to fix it: add the table when the real attribute shows up, not before — a single-column dim_order isn't more flexible than order_id living inside fact_orders; it's exactly the same thing, with one extra JOIN.

Confusing "degenerate dimension" with "column that doesn't matter." What happens: someone, hearing "degenerate" — a word with a negative connotation in everyday language — assumes order_id is a second-class column, less important than store_id or product_id. Why it happens: the technical name sounds like a flaw, when it actually describes a valid, deliberate way of modeling. How to spot it: if you treat order_id as optional or discardable in any analysis, you lost sight of the fact that, together with product_id, it's the key that defines fact_orders's very grain — without it, you couldn't distinguish one order line from another. How to fix it: "degenerate" is a Kimball technical term with no quality connotation at all — it only describes that the identifier has no table of its own, not that it's less important than any other column of the fact.

Trying to historize order_id with SCD, as if it were a dimension with changing attributes. What happens: someone, after module 4 (SCD), wonders whether order_id should have valid_from/valid_to like dim_product_scd, assuming every dimension eventually needs to be historized. Why it happens: SCD was applied, in this guide, to the only dimension with attributes that change over time — it's easy to generalize that technique to anything labeled "dimension." How to spot it: if you ask yourself what would happen if order_id "changed value," the question itself doesn't make business sense — an order doesn't change identity, it's created once and never modified. How to fix it: SCD historizes attributes that describe something that can change (a product's price, its category) — order_id doesn't describe anything, it's the fact row's own identifier. There's nothing to historize in a degenerate dimension, because it has no attribute beyond its own identity.

Exercises

Exercise 1 — Calculate how many distinct orders each store had, using only order_id. With no dim_order table, write a query that counts COUNT(DISTINCT order_id) by store_id, and confirm it gives the same result you already know from module 1 (S01: 16, S02: 13, S03: 11).

print(con.sql("""
    SELECT store_id, COUNT(DISTINCT order_id) AS distinct_orders
    FROM fact_orders
    GROUP BY store_id
    ORDER BY store_id
"""))

┌──────────┬─────────────────┐
│ store_id │ distinct_orders │
│ varchar  │      int64      │
├──────────┼─────────────────┤
│ S01      │              16 │
│ S02      │              13 │
│ S03      │              11 │
└──────────┴─────────────────┘

Exercise 2 — Drop dim_order_bad and confirm no previous query stops working. Run DROP TABLE dim_order_bad, and rerun this lesson's Part 1 query (the 5 highest-revenue orders). Confirm the result is identical.

con.execute("DROP TABLE dim_order_bad")
print(con.sql("""
    SELECT order_id, store_id, COUNT(*) AS line_items, ROUND(SUM(revenue), 2) AS order_total
    FROM fact_orders
    GROUP BY order_id, store_id
    ORDER BY order_total DESC, order_id ASC
    LIMIT 5
"""))

Exercise 3 — Explain, from memory, what would turn store_id into a degenerate dimension (hypothetically). store_id today is a real foreign key toward dim_store, with its own attributes (store_name, city). In 2-3 sentences, describe what would have to be true about dim_store for it to instead make sense to treat store_id as a degenerate dimension inside fact_orders.

Summary and next step

This lesson developed in depth the degenerate dimension module 1 named in passing: order_id lives inside fact_orders, with no table of its own, because it has no descriptive attribute beyond itself — no date of its own, no store of its own, no data any other fact column doesn't already capture. You built, with evidence, the bad alternative — dim_order_bad, forty rows, a single column, zero new information — and confirmed that joining against it produces exactly the same result as querying fact_orders directly, with one extra JOIN and no benefit in return.

Before moving on you should be able to: recite Kimball's definition of a degenerate dimension; explain the exact criterion — does it have attributes of its own beyond itself? — that decides whether an identifier needs its own table; and anticipate why payment_method/channel (lesson 4) can't be treated the same as order_id, even though both start as low-cardinality attributes of an order.

Lesson 4 introduces the second kind of dimension this module formalizes: when Kiosko starts capturing payment_method and channel per order, does it store them as two loose columns inside fact_orders, or group them into a small junk dimension, with a single flag_key? You're going to build dim_order_flags for real, and measure the difference.

Resources

• Kimball Group — "Star Schema / OLAP Cube" — the source that defines the degenerate dimension as the transaction identifier that lives inside the fact with no table of its own, the full basis of this lesson. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
• "The Data Warehouse Toolkit," 3rd edition (Kimball & Ross, Wiley) — the chapter on transaction numbers (order numbers, invoice numbers) as the canonical case of a degenerate dimension. wiley.com/en-jp/The+Data+Warehouse+Toolkit. In English.
• DuckDB — official Python client documentation, the interface that runs every query in this lesson. duckdb.org/docs/current/clients/python/overview. In English.