Module 7: Messy Domains And Medallion At Depth

Junk dimensions: bundling low-cardinality flags

Next →

Description

Starting this week, Kiosko's point of sale begins capturing two new attributes for every order: payment_method (how the customer paid: cash, card, or wallet) and channel (which channel the purchase happened through: in_store or app). Neither existed in fact_orders until now — this guide introduces them in this module, as fixed, deterministic attributes of each order, exactly as this guide's design declared. Lesson 3 already established the criterion: an attribute with information of its own can't stay "degenerate" as untreated free text. This lesson solves the question that follows: two loose text columns inside fact_orders, or a small, shared dimension, with a single flag_key? You're going to build dim_order_flags for real — the complete cartesian product of the three payment methods and the two channels, six rows — and measure the difference against the loose-columns alternative.

Connection to the module. This is the module's central lesson, the one this guide's design explicitly names as the executable result: dim_order_flags with columns flag_key, payment_method, channel, joined by flag_key instead of loose columns. Together with lesson 3's degenerate dimension, it completes the second kind of "non-standard" dimension — beyond the star and snowflake already seen in modules 2 and 3 — a real domain needs.

An analogy: the miscellaneous drawer, not one drawer per loose item

Think about how you organize small, low-variety objects at home: batteries, rubber bands, paper clips, a spare pair of keys. Nobody dedicates a whole drawer to each of those objects — that would be too many drawers for things that almost never change and are almost never looked for separately. The practical solution is a single miscellaneous drawer, organized into small compartments: one for batteries, one for rubber bands, one for paper clips. When you need a rubber band, you open one drawer, not five. The miscellaneous drawer isn't carelessness — it's the correct way to store several small, low-variety things without multiplying the whole piece of furniture.

A junk dimension is that drawer. payment_method (3 possible values) and channel (2 possible values) are, each on its own, too small and too low-variety to justify their own complete dimension table — that would be like dedicating a whole piece of furniture to rubber bands. But they also can't stay loose as free text inside the fact, because they do describe something real about each order. The solution: a single small drawer, dim_order_flags, with one compartment per possible combination of the two attributes, and a single key — flag_key — to find it.

The material you need

You need, in the same folder: kiosko.py and raw_orders.py (identical to modules 1, 2, and 4). You don't need any additional file — payment_method and channel aren't in any data file: this lesson declares them as a fixed mapping, exactly as module 6 declared the session-store mapping.

Worked example: dim_order_flags, end to end

Part 1 — Declare the fixed domains and assign each order

payment_method and channel don't exist in any Kiosko source file — not in raw_orders.py, not in any foundations CSV. This guide introduces them here as fixed, deterministic attributes: each order gets its payment_method and channel based on its position within the fixed forty-order week (RAW_ORDERS, in the same Monday-through-Sunday order this guide has used since module 1), rotating over the two domains. No random: the same order always produces the same pair of values.

# order_flags.py
from datetime import datetime

import duckdb

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

PAYMENT_METHODS = ["cash", "card", "wallet"]
CHANNELS = ["in_store", "app"]


def payment_method_for_order(order_index: int) -> str:
    """order_index: 1-based position of the order within RAW_ORDERS (fixed order,
    Monday to Sunday). Deterministic mapping, no random or hashing -- the same
    position always produces the same payment_method."""
    return PAYMENT_METHODS[(order_index - 1) % len(PAYMENT_METHODS)]


def channel_for_order(order_index: int) -> str:
    return CHANNELS[(order_index - 1) % len(CHANNELS)]


order_flags_rows = [
    (r[0], payment_method_for_order(i), channel_for_order(i))
    for i, r in enumerate(RAW_ORDERS, start=1)
]

con = duckdb.connect()
con.execute("CREATE TABLE order_flags_staging (order_id VARCHAR, payment_method VARCHAR, channel VARCHAR)")
con.executemany("INSERT INTO order_flags_staging VALUES (?, ?, ?)", order_flags_rows)

print("=== order_flags_staging, first 6 rows ===")
print(con.sql("SELECT * FROM order_flags_staging ORDER BY order_id LIMIT 6"))

What to expect.

=== order_flags_staging, first 6 rows ===
┌──────────┬────────────────┬──────────┐
│ order_id │ payment_method │ channel  │
│ varchar  │    varchar     │ varchar  │
├──────────┼────────────────┼──────────┤
│ ORD-1001 │ cash           │ in_store │
│ ORD-1002 │ card           │ app      │
│ ORD-1003 │ wallet         │ in_store │
│ ORD-1004 │ cash           │ app      │
│ ORD-1005 │ card           │ in_store │
│ ORD-1006 │ wallet         │ app      │
└──────────┴────────────────┴──────────┘

ORD-1001 (the week's first order, order_index = 1) gets cash (position 0 of PAYMENT_METHODS) and in_store (position 0 of CHANNELS). ORD-1002 (order_index = 2) advances both rotations: card, app. The payment_method rotation (3 values) and the channel rotation (2 values) advance at different rhythms — every six consecutive orders, exactly lcm(3, 2) = 6, a full cycle of both rotations completes at once — so across Kiosko's forty orders, each of the six possible combinations is going to show up several times.

Part 2 — Build dim_order_flags: the precomputed cartesian product

# dim_order_flags.py -- continues on top of con and order_flags_staging from Part 1
DIM_ORDER_FLAGS_ROWS = []
next_flag_key = 1
for payment_method in PAYMENT_METHODS:
    for channel in CHANNELS:
        DIM_ORDER_FLAGS_ROWS.append((next_flag_key, payment_method, channel))
        next_flag_key += 1

con.execute("CREATE TABLE dim_order_flags (flag_key INTEGER, payment_method VARCHAR, channel VARCHAR)")
con.executemany("INSERT INTO dim_order_flags VALUES (?, ?, ?)", DIM_ORDER_FLAGS_ROWS)

print("=== dim_order_flags: the complete cartesian product (3 payment_method x 2 channel) ===")
print(con.sql("SELECT * FROM dim_order_flags ORDER BY flag_key"))

flag_count = con.sql("SELECT COUNT(*) FROM dim_order_flags").fetchone()[0]
assert flag_count == len(PAYMENT_METHODS) * len(CHANNELS), "dim_order_flags doesn't have the complete cartesian product"
print(f"Verification: dim_order_flags has {flag_count} rows == 3 payment_method x 2 channel -- OK")

What to expect.

=== dim_order_flags: the complete cartesian product (3 payment_method x 2 channel) ===
┌──────────┬────────────────┬──────────┐
│ flag_key │ payment_method │ channel  │
│  int32   │    varchar     │ varchar  │
├──────────┼────────────────┼──────────┤
│        1 │ cash           │ in_store │
│        2 │ cash           │ app      │
│        3 │ card           │ in_store │
│        4 │ card           │ app      │
│        5 │ wallet         │ in_store │
│        6 │ wallet         │ app      │
└──────────┴────────────────┴──────────┘

Verification: dim_order_flags has 6 rows == 3 payment_method x 2 channel -- OK

This is, precisely, the operational definition of a junk dimension: precompute every possible combination of the low-cardinality attributes — not only the ones showing up in today's data, but the complete cartesian product — all at once, before any order ever queries it. With just two attributes of cardinality 3 and 2, six rows cover any combination Kiosko might ever need, forever — adding a new order never requires adding a new row to dim_order_flags, because all six possible combinations already exist.

Part 3 — Resolve flag_key per order, and use it instead of loose columns

# resolve_flags.py -- continues on top of con, order_flags_staging, and dim_order_flags
con.execute("""
    CREATE TABLE order_flags_resolved AS
    SELECT s.order_id, f.flag_key, s.payment_method, s.channel
    FROM order_flags_staging s
    JOIN dim_order_flags f ON s.payment_method = f.payment_method AND s.channel = f.channel
""")
resolved_count = con.sql("SELECT COUNT(*) FROM order_flags_resolved").fetchone()[0]
print(f"Verification: order_flags_resolved has {resolved_count} rows == 40 orders, each with its flag_key -- OK")
assert resolved_count == 40

print("\n=== How many orders use each of the 6 combinations ===")
print(con.sql("""
    SELECT f.flag_key, f.payment_method, f.channel, COUNT(*) AS orders_using_this_flag
    FROM order_flags_resolved r JOIN dim_order_flags f ON r.flag_key = f.flag_key
    GROUP BY f.flag_key, f.payment_method, f.channel
    ORDER BY f.flag_key
"""))

distinct_flags_used = con.sql("SELECT COUNT(DISTINCT flag_key) FROM order_flags_resolved").fetchone()[0]
print(f"\nDistinct combinations actually used by the 40 orders: {distinct_flags_used} of {flag_count} possible")

What to expect.

Verification: order_flags_resolved has 40 rows == 40 orders, each with its flag_key -- OK

=== How many orders use each of the 6 combinations ===
┌──────────┬────────────────┬──────────┬────────────────────────┐
│ flag_key │ payment_method │ channel  │ orders_using_this_flag │
│  int32   │    varchar     │ varchar  │         int64          │
├──────────┼────────────────┼──────────┼────────────────────────┤
│        1 │ cash           │ in_store │                      7 │
│        2 │ cash           │ app      │                      7 │
│        3 │ card           │ in_store │                      6 │
│        4 │ card           │ app      │                      7 │
│        5 │ wallet         │ in_store │                      7 │
│        6 │ wallet         │ app      │                      6 │
└──────────┴────────────────┴──────────┴────────────────────────┘

Distinct combinations actually used by the 40 orders: 6 of 6 possible

7 + 7 + 6 + 7 + 7 + 6 = 40 — each of the forty orders resolved to exactly one flag_key, and all six possible combinations show up, with an almost even split (six or seven orders each). Now, the business reason this is worth it: analyzing revenue by payment method and channel, joining fact_orders against order_flags_resolved and dim_order_flags — never against loose text columns inside the fact itself.

# revenue_by_flag.py -- continues on top of con, with fact_orders already rebuilt
print("=== revenue by payment_method and channel, via flag_key (no loose columns in fact_orders) ===")
print(con.sql("""
    SELECT f.flag_key, f.payment_method, f.channel, COUNT(*) AS orders, ROUND(SUM(o.revenue), 2) AS revenue
    FROM fact_orders o
    JOIN order_flags_resolved r ON o.order_id = r.order_id
    JOIN dim_order_flags f ON r.flag_key = f.flag_key
    GROUP BY f.flag_key, f.payment_method, f.channel
    ORDER BY f.flag_key
"""))

What to expect.

=== revenue by payment_method and channel, via flag_key (no loose columns in fact_orders) ===
┌──────────┬────────────────┬──────────┬────────┬─────────┐
│ flag_key │ payment_method │ channel  │ orders │ revenue │
│  int32   │    varchar     │ varchar  │ int64  │ double  │
├──────────┼────────────────┼──────────┼────────┼─────────┤
│        1 │ cash           │ in_store │      7 │    14.2 │
│        2 │ cash           │ app      │      7 │    14.8 │
│        3 │ card           │ in_store │      6 │    15.7 │
│        4 │ card           │ app      │      7 │    23.8 │
│        5 │ wallet         │ in_store │      7 │   19.55 │
│        6 │ wallet         │ app      │      6 │    18.1 │
└──────────┴────────────────┴──────────┴────────┴─────────┘

14.2 + 14.8 + 15.7 + 23.8 + 19.55 + 18.1 = 106.15 — the same total revenue you already know from module 1, this time broken down by payment method and channel, without fact_orders needing any additional text column. The only cost was two extra JOINs (order_flags_resolved, dim_order_flags), in exchange for never repeating the text "wallet" or "in_store" forty times inside the main fact.

Diagram: two loose columns vs one junk dimension

flowchart LR
    subgraph Bad["Alternative: loose columns"]
        FO1["fact_orders\n+ payment_method (text)\n+ channel (text)\n40 rows x 2 repeated text columns"]
    end

    subgraph Good["dim_order_flags: the junk dimension"]
        FO2["fact_orders\n+ flag_key (integer)"] --> DOF["dim_order_flags\n6 rows: flag_key, payment_method, channel"]
    end

Going deeper: measuring the cost, not just describing it

Module 3 of this guide already taught you to measure, not just argue, the difference between shapes of a model. Apply the same criterion here: if Kiosko had added payment_method and channel as two loose text columns, directly inside fact_orders, how much repeated text would it have stored, compared to the junk dimension?

# junk_vs_loose_columns.py
loose_columns_text_values = 40 * 2   # 40 orders x 2 loose text columns
junk_dimension_text_values = 6 * 2   # 6 rows of dim_order_flags x 2 text columns

JUNK_VS_LOOSE = {
    "loose_columns": {
        "new_columns_in_fact_orders": 2,
        "column_types": ["VARCHAR", "VARCHAR"],
        "text_values_stored": loose_columns_text_values,
    },
    "junk_dimension": {
        "new_columns_in_fact_orders": 0,
        "new_table": "dim_order_flags (6 rows, 3 columns)",
        "text_values_stored": junk_dimension_text_values,
        "extra_join_cost": 1,
    },
}
print("=== loose columns vs junk dimension ===")
for approach, data in JUNK_VS_LOOSE.items():
    print(f"{approach}: {data}")

reduction_pct = round(100 * (1 - junk_dimension_text_values / loose_columns_text_values), 1)
print(f"\nReduction in repeated text values: {reduction_pct}%")

What to expect.

=== loose columns vs junk dimension ===
loose_columns: {'new_columns_in_fact_orders': 2, 'column_types': ['VARCHAR', 'VARCHAR'], 'text_values_stored': 80}
junk_dimension: {'new_columns_in_fact_orders': 0, 'new_table': 'dim_order_flags (6 rows, 3 columns)', 'text_values_stored': 12, 'extra_join_cost': 1}

Reduction in repeated text values: 85.0%

With loose columns, fact_orders would store eighty text values — "cash", "card", "wallet", "in_store", "app", each combination of two repeated forty times. With the junk dimension, those same six distinct text values (three payment methods, two channels) get stored once each, twelve in total, and fact_orders has, in their place, a single integer per row. This difference grows, it doesn't stay fixed, with data volume: in a production warehouse with millions of orders, the difference between eighty thousand repeated text values and twelve unique text values is the difference between gigabytes and kilobytes in the fact table — the same space-vs-normalization argument module 3 already measured for dim_category, now applied to attributes of even lower cardinality.

Common mistakes

Creating a complete dimension for each flag, instead of a single junk dimension. What happens: someone, following the dim_store or dim_product pattern, creates dim_payment_method (3 rows) and dim_channel (2 rows) separately, each with its own surrogate key, and adds two foreign keys to fact_orders. Why it happens: "one table per dimension" is the pattern that dominates the rest of this guide, so it feels like the consistent answer. How to spot it: if your design has two new tables with a single descriptive column each (dim_payment_method with only payment_method, dim_channel with only channel), you're paying the cost of two additional JOINs for two attributes that, together, only have six possible combinations. How to fix it: when several attributes are, each, very low-cardinality and have no hierarchical relationship among themselves (unlike category inside dim_product, which does describe the product), group them into a single junk dimension with the precomputed cartesian product — exactly what this lesson did with dim_order_flags.

Building dim_order_flags with only the combinations that appear in today's data, instead of the complete cartesian product. What happens: someone, instead of generating the six possible combinations with the double for, builds dim_order_flags with SELECT DISTINCT payment_method, channel FROM order_flags_staging — which also gives six rows today, by coincidence, but for a different reason. Why it happens: it seems more "efficient" to only store what's actually used. How to spot it: if Kiosko adds an order tomorrow with a combination that doesn't exist today in this week's data — for example, if this week's forty orders never had wallet+app together — a dim_order_flags built with DISTINCT over today's data would have fewer than six rows, and that combination would fail to resolve its flag_key. How to fix it: a well-built junk dimension precomputes every possible combination of the declared domains — PAYMENT_METHODS x CHANNELS — not only the ones today's data uses. This is only feasible, precisely, because the cardinality is low: with two attributes of 3 and 2 values, six rows cover any future with no additional cost.

Confusing dim_order_flags with a conformed dimension like dim_store or dim_date. What happens: someone, seeing dim_order_flags has a surrogate key (flag_key) just like the star's dimensions, expects it to serve more than one fact, as dim_store demonstrated in lesson 2. Why it happens: the physical shape — surrogate key, small table, joined via JOIN — is identical to any other dimension in this guide. How to spot it: if you try to join fact_sessions or fact_store_activity against dim_order_flags, you're not going to find any real column connecting them — payment_method and channel are attributes specific to an order (a completed purchase event), not to a browsing session nor to a store's daily activity. How to fix it: not every dimension needs to be conformed to be correct — dim_order_flags, just like dim_product (as you saw in this module's lesson 2), is a legitimate single-fact dimension, and that doesn't take away any of its value.

Exercises

Exercise 1 — Verify no order_id was left unresolved. Using order_flags_staging and order_flags_resolved, write a query with LEFT JOIN and WHERE ... IS NULL that confirms all forty orders from order_flags_staging found their corresponding flag_key.

See solution 
unresolved = con.sql("""
    SELECT s.order_id
    FROM order_flags_staging s
    LEFT JOIN order_flags_resolved r ON s.order_id = r.order_id
    WHERE r.flag_key IS NULL
""").fetchall()
print(f"Unresolved orders: {len(unresolved)}")
assert len(unresolved) == 0

Expected output:

Unresolved orders: 0

Zero unresolved orders — confirming that dim_order_flags's six precomputed combinations cover, without exception, any payment_method/channel combination Kiosko's forty orders produced.

Exercise 2 — Calculate which channel generated more revenue: in_store or app. Using order_flags_resolved, dim_order_flags, and fact_orders, aggregate by channel (without payment_method) and compare each channel's total revenue.

See solution 
print(con.sql("""
    SELECT f.channel, COUNT(*) AS orders, ROUND(SUM(o.revenue), 2) AS revenue
    FROM fact_orders o
    JOIN order_flags_resolved r ON o.order_id = r.order_id
    JOIN dim_order_flags f ON r.flag_key = f.flag_key
    GROUP BY f.channel
    ORDER BY f.channel
"""))

Expected output:

┌──────────┬────────┬─────────┐
│ channel  │ orders │ revenue │
│ varchar  │ int64  │ double  │
├──────────┼────────┼─────────┤
│ app      │     20 │    56.7 │
│ in_store │     20 │   49.45 │
└──────────┴────────┴─────────┘

Twenty orders for each channel — an exactly even split, a consequence of CHANNELS having only two values and forty being a multiple of two — but with different revenue: app generated 56.7 against in_store's 49.45. 56.7 + 49.45 = 106.15, the same total as always. This question — which channel generates more revenue? — is exactly the kind of analysis dim_order_flags enables without fact_orders ever having needed to load any additional text column.

Exercise 3 — Explain, from memory, why adding a third low-cardinality attribute (for example, is_first_purchase, boolean) wouldn't duplicate the design work. In 2-3 sentences, explain how dim_order_flags would change if Kiosko added a third boolean attribute, and how many rows the resulting dimension would have.

See solution

Adding a third low-cardinality attribute to an existing junk dimension requires no redesign at all — it just extends the cartesian product: with payment_method (3 values), channel (2 values), and is_first_purchase (2 values, true/false), dim_order_flags would go from six to 3 x 2 x 2 = 12 rows, each still identified by a single flag_key. The pattern — precomputing every possible combination of the declared domains — is exactly the same no matter how many low-cardinality attributes get added, as long as the product of their cardinalities stays small; if that combined cardinality grew a lot (for example, adding an attribute with a hundred possible values), it would stop making sense as a junk dimension and would need reconsidering.

Summary and next step

This lesson built dim_order_flags end to end: six rows — the complete cartesian product of three payment methods and two channels — precomputed all at once, with flag_key as its surrogate key. Kiosko's forty orders all resolved, without exception, to one of those six combinations, and the revenue analysis by payment method and channel — 106.15 broken down into six rows — happened without fact_orders needing any additional text column. Measured in numbers: eighty repeated text values with the loose-columns alternative, against twelve with the junk dimension — an 85% reduction.

Before moving on you should be able to: build the cartesian product of two low-cardinality domains from memory with a double for; explain why a junk dimension precomputes every possible combination, not only the observed ones; and justify, with numbers, why grouping low-cardinality attributes into a small table beats multiplying loose columns or multiplying single-column dimensions.

Lesson 5 steps back from pure dimensional modeling and formalizes something this guide — and foundations, before it — has used from the start without ever having put it in writing: the contract between bronze, silver, and gold. validate_gold_schema() is the function that confirms, with evidence rather than manual review, that each Kiosko gold table — including dim_order_flags, which you just built — maintains exactly the columns the rest of the warehouse expects.

Resources

← PreviousDegenerate dimensions: order_id, in depthNext →Medallion contracts between bronze, silver, and gold