AdvancedData Modeling · 25 min · 3 challenges

Junk and Degenerate Dimensions: Advanced

Every fact table has orphan attributes that do not belong in any dimension: boolean flags, status codes, transaction IDs, invoice numbers. Leave them in the fact table and it grows wide. Create a dimension for each one and you get dimension explosion. Junk dimensions and degenerate dimensions are the two patterns for handling these orphans. They rarely appear as standalone interview questions, but they appear constantly as design decisions within larger modeling questions. Knowing them unprompted signals Kimball-level fluency that most candidates lack.

What you will be able to do

Recognize orphan attributes that need junk or degenerate treatment

Build a junk dimension that consolidates flags without creating a combinatorial explosion

Know when to leave a transaction ID in the fact table as a degenerate dimension

Orphan Attributes with No Natural Home

Daily Life

Interviews

You need junk or degenerate dimensions when you see:

▸Boolean flags: is_gift, is_prime, is_expedited, is_taxable
▸Low-cardinality codes: payment_method (4 values), shipping_class (3 values)
▸Status flags: is_returned, is_canceled, is_fraud
▸Transaction identifiers: invoice_number, receipt_id, confirmation_code
▸Any column in the fact table that is neither a measure nor a dimension FK

The Problem

Set up the scenario: 'The fact table has is_gift, is_prime, is_expedited, payment_method, and invoice_number. Where do these go? Leaving all five on the fact adds columns that every scan reads even when unused. Creating a dimension for each one is dimension explosion. The answer: consolidate the flags into a junk dimension, keep invoice_number as a degenerate dimension.' Deliver this in 15 seconds. It shows you know both patterns.

Option	Approach	Problem
Bad #1	Leave all five columns in fact table	Fact table grows wide. Each boolean adds a column to every scan. Five orphans become fifteen as the business grows.
Bad #2	Create dim_is_gift, dim_is_prime, dim_is_expedited, dim_payment_method	Four new dimensions, each with 2 to 4 rows. Dimension explosion. The star schema becomes unreadable.
Good #1	Junk dimension for the flags	Combine booleans and low-cardinality codes into one dim_order_flags table.
Good #2	Degenerate dimension for invoice_number	Keep invoice_number in the fact table. It is unique per row and has no attributes worth dimensionalizing.

What They're Really Testing

The hidden rubric: does this candidate know what to do with the attributes that don't fit neatly into the star schema? Most candidates either ignore these columns or stuff them into the fact table without thinking. Naming 'junk dimension' or 'degenerate dimension' unprompted signals that you have studied Kimball and applied it in practice.

These patterns are rarely asked as standalone questions. They come up when you are designing a fact table and the interviewer watches what you do with the leftover attributes. Having the vocabulary ready is the difference between fumbling and flowing.

Building a Junk Dimension

Daily Life

Interviews

When the interviewer points to five boolean flags on your fact table and asks 'where do these go?', they are testing whether you know the consolidation pattern. Saying 'leave them on the fact table' is a weak answer. Saying 'create five separate dimensions' is worse. The strong answer names the junk dimension pattern and designs one in 30 seconds.

The Schema You Should Be Able to Write in 60 Seconds

CREATE TABLE dim_order_flags(order_flags_sk INT PRIMARY KEY,  is_gift BOOLEAN NOT NULL,  is_prime BOOLEAN NOT NULL,  is_expedited BOOLEAN NOT NULL,  payment_method VARCHAR(20) NOT NULL,  UNIQUE(is_gift,  is_prime,  is_expedited,  payment_method)) ; CREATE TABLE fact_orders(order_sk BIGINT PRIMARY KEY,  customer_sk BIGINT NOT NULL,  product_sk BIGINT NOT NULL,  date_sk INT NOT NULL,  order_flags_sk INT NOT NULL,  invoice_number VARCHAR(50),  amount DECIMAL(12,  2)) ;

dim_order_flags

order_flags_skPKINT

is_primeBOOLEAN

is_giftBOOLEAN

payment_typeVARCHAR

fact_orders

order_skPKBIGINT

order_numberVARCHAR

customer_skFKBIGINT

order_flags_skFKINT

amountNUMERIC

Junk dimension: low-cardinality orphan flags (is_prime, is_gift, payment_type) collapse into ONE dim_order_flags row per distinct combination. order_number stays on the fact as a degenerate dimension (no lookup table).

How You Load It: The Detail Interviewers Probe

Generate all possible combinations upfront. For 3 booleans and a 4-value enum, that is 32 rows. The dimension is static. No ETL needed. Fact loading does a simple lookup.
When a new combination appears in the source data, insert it into the junk dimension and assign an SK. Necessary when the attribute space is too large to enumerate upfront (e.g., status codes that expand over time).

Pre-population is the strong answer. It makes the fact load a pure lookup with no dimension-side writes, which simplifies concurrency and makes the pipeline idempotent.

✓Do

Pre-populate junk dimensions with all valid combinations
Add a UNIQUE constraint on the combination of flag columns
Name junk dimensions descriptively: dim_order_flags, not dim_junk_1

✗Don't

Put free text or timestamps in a junk dimension
Let the junk dimension grow beyond ~1,000 rows without splitting
Create separate tiny dimensions for each boolean flag

How You Query It: Show the Interviewer It Works

	/* Revenue from Prime gift orders paid by card */
	SELECT
	SUM(f.amount)
	FROM fact_orders AS f
	INNER JOIN dim_order_flags AS jd
	ON f.order_flags_sk = jd.order_flags_sk
	WHERE jd.is_prime = TRUE
	AND jd.is_gift = TRUE
	AND jd.payment_method = 'card'

Your query answer: 'The query reads exactly like it would if the flags were on the fact table. JOIN dim_order_flags, filter on is_prime and is_gift. The junk dimension is 32 rows. It fits in cache. The join cost is negligible. The benefit: the fact table is 4 columns narrower and scans faster for queries that never touch flags.' Say 'fits in cache.' That shows you think about physical performance, not just logical design.

•Without Junk Dim

Fact table: 8 columns
5 flag columns on every row
Every scan reads all flags even when unused
Adding a new flag adds a column to the fact

•With Junk Dim

Fact table: 4 columns (3 fewer)
1 FK replaces 5 flag columns
Flag-free queries skip the junk dim join entirely
Adding a new flag expands the junk dim, not the fact

Degenerate Dimensions in the Fact Table

Daily Life

Interviews

Your degenerate dimension answer: 'A degenerate dimension stays in the fact table. No separate table. No surrogate key. invoice_number is unique per row with no additional attributes worth storing. Creating dim_invoice with one column and a surrogate key doubles storage for zero analytical benefit.' The key phrase is 'zero analytical benefit.' That is the Kimball justification for degeneration.

The Decision: Which Attributes Stay on the Fact Table

Attribute	Degenerate?	Why
invoice_number	Yes	Unique per transaction. No additional attributes. Creating dim_invoice with one column is pointless.
confirmation_code	Yes	Unique identifier for lookup. No descriptive attributes.
order_id	Yes	Natural key of the business event. Used for lineage tracing, not for grouping or filtering.
payment_method	No	Has descriptive attributes (provider, category, fee_pct). Belongs in a dimension or junk dim.
customer_email	No	Has related attributes (name, region). Belongs in dim_customer.

State the rule: 'If the attribute is unique per fact row and has no additional columns that would join to it, it is degenerate. A one-to-one dimension with the same cardinality as the fact table provides no analytical value.' Name the anti-pattern: 'one-to-one dimension.' The interviewer rarely hears candidates name it.

The Interview Trap

Some interviewers will push back: 'Isn't that breaking the star schema pattern?' This is a test. The correct response:

If the interviewer pushes back, say: 'A degenerate dimension is a recognized Kimball pattern, not a shortcut. The star schema principle is that descriptive attributes live in dimensions and measures live in facts. A transaction identifier has no descriptive attributes. Forcing it into a dimension creates a one-to-one dimension with the same row count as the fact, which is worse.' Defend confidently.

Naming 'one-to-one dimension anti-pattern' in your defense is a strong-hire signal. It shows you understand that a dimension with the same cardinality as the fact it references provides no analytical value and wastes storage on a redundant SK.

The Follow-Up: Indexing Strategy by Platform

Your indexing answer: 'For point lookups on invoice_number in Postgres, I add a B-tree index. In BigQuery or Snowflake, B-tree indexes do not exist, so I use clustering keys or partition filters. I only index degenerate columns that appear in WHERE clauses for drill-through queries.' Show platform awareness. The interviewer is checking whether you know indexing differs by engine.

In OLTP-style databases (Postgres): create a B-tree index on the degenerate column.

In columnar warehouses (BigQuery, Snowflake): use clustering keys or partition filters. B-tree indexes do not exist.

Do not index degenerate columns that are never used in WHERE clauses. invoice_number used only for drill-through does not need an index if drill-through always goes through order_id first.

When Junk Dimensions Grow

Daily Life

Interviews

The follow-up the interviewer uses to probe depth: 'What happens when the business adds three more flags next quarter?' This tests whether you have thought about the combinatorial growth of junk dimensions and know when to split them.

The Math the Interviewer Will Make You Do

Attributes	Cardinalities	Junk Dim Rows	Verdict
3 booleans + payment_method	2 x 2 x 2 x 4	32	Perfect. Pre-populate.
+ shipping_class (3 values)	32 x 3	96	Still fine.
+ reason_code (50 values)	96 x 50	4,800	Getting large. Consider splitting.
+ free-text notes	Unbounded	Infinity	Never put free text in a junk dimension.

When to Split: The Threshold You Should Name

Rule of ThumbNever IncludeWatch For

Rule of Thumb

Split when the junk dimension exceeds ~1,000 rows

Beyond 1,000 rows, the junk dimension is no longer 'junk.' It is carrying meaningful attribute combinations. Split it into two junk dimensions: one for the stable, low-cardinality flags (pre-populated), and one for the higher-cardinality codes (insert on demand).

Never Include

Free text, timestamps, or high-cardinality natural keys

These are not junk dimension material. Free text has unbounded cardinality. Timestamps are measures or degenerate dimensions. High-cardinality keys belong in their own dimension or as degenerate.

Watch For

Business teams adding new flag columns

Every new boolean doubles the junk dimension size. If the business adds is_b2b, is_wholesale, is_subscription, and is_trial in one quarter, your 32-row junk dim becomes 512. Build the governance process that reviews new flags before they enter the junk dim.

The Follow-Up Trap

Follow-Up #1Follow-Up #2

Follow-Up #1

"What if not all combinations are valid?"

Strong answer: 'Only pre-populate valid combinations. If is_gift and is_expedited are mutually exclusive, the junk dimension has 2 x 2 x 4 = 16 rows, not 32. Add a CHECK constraint or business rule validation.'

Follow-Up #2

"A new boolean flag is added. What happens?"

Strong answer: 'The junk dimension doubles in size. If it was 32 rows, it becomes 64. This is fine. If it was already at 5,000 rows and doubles to 10,000, it is time to split the junk dimension.'

The interview-winning answer for explosion: 'Junk dimensions work when the product of cardinalities stays under about 1,000. Beyond that, I would split into multiple junk dimensions grouped by domain: dim_order_flags for shipping/gift booleans, dim_payment_flags for payment-related codes. Each stays small.'

Defending Your Junk/Degenerate Design

Daily Life

Interviews

The interview signal for junk and degenerate dimensions is not drawing the schema. It is explaining why this design is correct. The interviewer will challenge your choices. Having the rationale ready is what separates pattern-appliers from pattern-defenders.

Defense Playbook

Challenge	Strong Response
"Why not just leave the flags on the fact?"	"Five boolean columns add 5 bytes per row. At 1B rows, that is 5 GB of data scanned on every query, even queries that never filter on flags. The junk dim FK is 4 bytes for one column instead of 5 bytes for five."
"Isn't a junk dimension confusing?"	"The name is unfortunate. It is really a 'flag consolidation dimension.' The value is schema simplicity: one FK replaces many columns, and adding new flags does not alter the fact table."
"Why keep invoice_number on the fact?"	"It is unique per row with no descriptive attributes. A dimension with one column per row is worse than degeneration: it doubles storage for no analytical benefit."
"What about filtering performance?"	"The junk dimension is tiny (32 to 1,000 rows). It fits entirely in memory. The join cost is negligible. The scan savings from a narrower fact table usually outweigh the join cost."

Vocabulary That Signals Seniority

Junior Phrasing	Senior Phrasing
"I'd add is_gift to the fact table"	"is_gift is a low-cardinality flag. I'd consolidate it with other flags into a junk dimension to keep the fact table narrow."
"invoice_number goes in a dimension"	"invoice_number is a degenerate dimension: unique per fact row, no descriptive attributes, stays on the fact."
"I'm not sure where to put these"	"These orphan attributes split into two groups: low-cardinality flags for a junk dimension, and unique identifiers for degenerate dimensions."
"What's a junk dimension?"	"A junk dimension consolidates low-cardinality flags and codes into a single table with a surrogate key, keeping the fact table lean while preserving filterability."

The Bridge Move

After handling junk and degenerate dimensions, bridge to the broader design: 'So now the fact table has: dimension FKs (customer_sk, product_sk, date_sk), one junk dim FK (order_flags_sk), one degenerate dim (invoice_number), and the additive measures (quantity, amount). Every column has a clear role. Nothing is orphaned.' This summary statement shows the interviewer you are tracking the full schema, not just the piece they asked about.

Red Flag Phrases

"I'd create a separate dimension for each flag" - Dimension explosion. Shows you do not know the consolidation pattern.

"Just add columns to the fact table" - Works but shows you have not considered scan cost at scale.

"What's a degenerate dimension?" - Acceptable for mid-level. Not acceptable for senior. Know the term.

"Put the invoice number in dim_customer" - Invoice is per order, not per customer. Wrong grain.

The closing move that ties the whole fact table together: 'So the final schema is: dimension FKs (customer_sk, product_sk, date_sk), one junk dim FK (order_flags_sk), one degenerate dim (invoice_number), and the additive measures (quantity, amount). Every column has a clear role. Nothing is orphaned.' This three-sentence summary hits every rubric item.

❯❯❯PUTTING IT ALL TOGETHER

> You are designing a fact_transactions table in an interview. The interviewer points to is_fraud, is_disputed, payment_type, and receipt_id.

You say: 'is_fraud and is_disputed are booleans. payment_type is low-cardinality. I'd consolidate these into a junk dimension: dim_transaction_flags. 2 x 2 x 4 = 16 rows, pre-populated.'

For receipt_id: 'That is unique per transaction with no descriptive attributes. It stays on the fact table as a degenerate dimension.'

You summarize: 'The fact table now has: customer_sk, merchant_sk, date_sk, transaction_flags_sk, receipt_id (degenerate), amount, and fee. Every column has a role. The table is six columns wide instead of nine.'

KEY TAKEAWAYS

Junk dimension: consolidates low-cardinality flags into one table, one FK on the fact

Degenerate dimension: unique-per-row identifiers with no attributes stay directly on the fact

Pre-populate: generate all valid flag combinations upfront for a static, lookup-only junk dim

Split threshold: when the junk dim exceeds ~1,000 rows, split by domain into multiple smaller junk dims

The bridge: summarize the final fact table showing dimension FKs, junk dim FK, degenerate dims, and measures

Low-cardinality flags and transaction IDs need homes; junk and degenerate dims provide them

Category: Data Modeling
Difficulty: advanced
Duration: 25 minutes
Challenges: 3 hands-on challenges

Topics covered: Orphan Attributes with No Natural Home, Building a Junk Dimension, Degenerate Dimensions in the Fact Table, When Junk Dimensions Grow, Defending Your Junk/Degenerate Design

Lesson Sections

Orphan Attributes with No Natural Home (concepts: dmFactTables)
The Problem Set up the scenario: 'The fact table has is_gift, is_prime, is_expedited, payment_method, and invoice_number. Where do these go? Leaving all five on the fact adds columns that every scan reads even when unused. Creating a dimension for each one is dimension explosion. The answer: consolidate the flags into a junk dimension, keep invoice_number as a degenerate dimension.' Deliver this in 15 seconds. It shows you know both patterns. What They're Really Testing These patterns are rarely
Building a Junk Dimension (concepts: dmDimensionTables)
When the interviewer points to five boolean flags on your fact table and asks 'where do these go?', they are testing whether you know the consolidation pattern. Saying 'leave them on the fact table' is a weak answer. Saying 'create five separate dimensions' is worse. The strong answer names the junk dimension pattern and designs one in 30 seconds. The Schema You Should Be Able to Write in 60 Seconds 32 rows How You Load It: The Detail Interviewers Probe Pre-population is the strong answer. It ma
Degenerate Dimensions in the Fact Table (concepts: dmFactTables)
Your degenerate dimension answer: 'A degenerate dimension stays in the fact table. No separate table. No surrogate key. invoice_number is unique per row with no additional attributes worth storing. Creating dim_invoice with one column and a surrogate key doubles storage for zero analytical benefit.' The key phrase is 'zero analytical benefit.' That is the Kimball justification for degeneration. The Decision: Which Attributes Stay on the Fact Table State the rule: 'If the attribute is unique per
When Junk Dimensions Grow (concepts: dmDimensionTables)
The follow-up the interviewer uses to probe depth: 'What happens when the business adds three more flags next quarter?' This tests whether you have thought about the combinatorial growth of junk dimensions and know when to split them. The Math the Interviewer Will Make You Do When to Split: The Threshold You Should Name The Follow-Up Trap The interview-winning answer for explosion: 'Junk dimensions work when the product of cardinalities stays under about 1,000. Beyond that, I would split into mu
Defending Your Junk/Degenerate Design (concepts: dmFactTables)
The interview signal for junk and degenerate dimensions is not drawing the schema. It is explaining why this design is correct. The interviewer will challenge your choices. Having the rationale ready is what separates pattern-appliers from pattern-defenders. Defense Playbook Vocabulary That Signals Seniority The Bridge Move Red Flag Phrases The closing move that ties the whole fact table together: 'So the final schema is: dimension FKs (customer_sk, product_sk, date_sk), one junk dim FK (order_f