Centipede Fact Tables
A centipede fact table is an anti-pattern in which a fact table carries far too many foreign keys, each pointing to its own small dimension table. Drawn out, the schema looks like a centipede: a narrow body with dozens of thin legs.
Most well-designed fact tables need somewhere between 5 and 15 dimensions. Once you're past roughly 20, it's worth asking whether the dimensions have been split more finely than the business actually requires.
The cause is usually over-normalization: the designer treats each attribute, or each level of a hierarchy, as a dimension in its own right instead of grouping attributes that naturally belong together.
Example (Centipede Anti-Pattern):
| date_key | customer_key | product_key | brand_key | category_key | city_key | state_key | country_key | is_active_key | payment_status_key | order_type_key | … (40+ more) | sales_amount |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 20240101 | C-105 | P-99 | BR-07 | CAT-03 | CT-12 | ST-05 | CO-01 | A-1 | PS-2 | OT-3 | … | 150.00 |
Three separate keys describe one geography, two more describe attributes of the product already identified by product_key, and three low-cardinality flags each get a dimension of their own.
Why Centipedes Are a Problem
- Query performance: Every query joins the fact table to a long list of dimensions. Large join counts make it harder for the optimizer to find a good plan, and star-join optimizations that assume a modest number of dimensions stop being effective.
- Usability: Analysts and BI tools face a sprawl of tiny, fragmented tables. Finding the right attribute means knowing which of 50 lookup tables it lives in, and simple questions turn into multi-table joins.
- Row width: Fact tables hold the overwhelming majority of rows in a warehouse, and in a centipede design the foreign keys make up most of the width of each row. Fifty 4-byte keys add roughly 200 GB per billion rows before you've stored a single measure.
Avoiding the Anti-Pattern
1. Collapse Hierarchies into a Single Dimension
Replace City, State, and Country with one Location dimension that holds all three as attributes. The same applies to Brand and Category, which are attributes of a product and belong in the Product dimension. Dimensional models favor denormalized dimensions, so levels of a hierarchy should sit side by side in one table rather than in a chain of joined ones.
2. Use Junk Dimensions
When a process has a handful of low-cardinality flags and indicators (is_active, payment_status, order_type), don't give each one a dimension. Combine their distinct combinations into a single Junk Dimension, turning several keys into one.
3. Keep Degenerate Dimensions
Transaction identifiers such as order numbers, invoice numbers, and bills of lading are sometimes pulled out into their own tables. Because these have no descriptive attributes of their own, that table holds nothing but the key. Leave the identifier in the fact table as a Degenerate Dimension.
The same fact table, cleaned up:
| date_key | customer_key | product_key | location_key | order_junk_key | order_number | sales_amount |
|---|---|---|---|---|---|---|
| 20240101 | C-105 | P-99 | LOC-88 | JNK-14 | ORD-20481 | 150.00 |
Every attribute from the original is still available. Brand and category moved into Product, the geography keys collapsed into Location, the three flags became one junk dimension, and the order number stayed in the fact table as a degenerate dimension.
Many dimensions aren't always wrong
A high dimension count is a signal to review the design, not an automatic defect. Some business processes genuinely involve many independent dimensions. The question to ask is whether each key represents a distinct piece of context, or whether it's a fragment of context that already has a home elsewhere.