Most data modeling tutorials stop at the same place: here's a fact table, here's a dimension table, join them, done. And that's genuinely enough to get a warehouse working. It's also enough to make you think you're finished learning.
Then two things happen in the same month. A sales deal gets credit split between three reps instead of one, and your fact table — built for one rep per deal — has no clean way to hold that. And finance asks for "account balance on the last day of every month," and you realize your fact table only stores balance-change events, not balances. Neither of these is a bug. They're just the next layer of modeling that nobody mentioned in the "intro to star schemas" post.
Here's that next layer, in plain words, with a running example: an online furniture store.
When a dimension needs its own dimensions: snowflake schema
In a basic star schema, dim_product might hold everything about a product in one flat row — including its category name and department name, repeated for every single product in that category.
-- dim_product (star schema — flat, some repetition)
product_key | product_name | category_name | department_name
201 | Oak Desk | Desks | Furniture
202 | Pine Desk | Desks | Furniture
If "Desks" ever gets renamed to "Work Desks," you're updating that string in every row that mentions it. A snowflake schema splits the dimension further, so category and department live in their own tables:
-- dim_product (snowflaked)
product_key | product_name | category_key
201 | Oak Desk | 55
-- dim_category
category_key | category_name | department_key
55 | Desks | 9
Now a rename touches one row. The cost is that a query which used to need one join now needs two or three. Neither version is "correct" — a snowflake schema wins when a dimension's attributes actually change independently and often; a flat star schema wins when the query speed matters more than that theoretical tidiness. Most teams default to star and only snowflake the one or two dimensions that genuinely need it.
Not every fact table looks the same
The sales-and-refunds fact table from a basic tutorial — one row per event — is called a transaction fact table. It's the most common type, but it's not the only shape a fact table can take.
A periodic snapshot fact table captures a measurement at a regular interval, whether or not anything happened. Think of a warehouse's inventory count, taken every night at midnight:
-- fact_inventory_daily
snapshot_date | product_key | warehouse_key | units_on_hand
2025-09-14 | 201 | 3 | 42
2025-09-15 | 201 | 3 | 39
Nobody "did" anything to generate the September 15th row — it's just a photograph of the state of things that day. This is the shape you want for "what was our balance on the last day of the month," because a transaction table only tells you about changes, not states.
An accumulating snapshot fact table is for tracking something with a defined start and end, where you update the same row as it moves through stages — an order going from placed to packed to shipped to delivered:
-- fact_order_fulfillment
order_id | placed_date | packed_date | shipped_date | delivered_date | days_to_deliver
5001 | 2025-09-01 | 2025-09-01 | 2025-09-02 | 2025-09-05 | 4
5002 | 2025-09-03 | 2025-09-03 | NULL | NULL | NULL
Instead of one row per status change, there's one row per order, and columns get filled in as milestones happen. This is the natural shape for pipeline and fulfillment reporting — you can instantly see how many orders are stuck between "packed" and "shipped" without scanning an event log.
When there's nothing to measure: factless fact tables
A fact table doesn't have to have a number in it. Say you want to track which students attended which class sessions. There's no "amount," no "quantity" — just the fact that an event happened.
-- fact_attendance
student_key | class_session_key | date_key
77 | 1204 | 20250915
You get your measure from COUNT(*), not from a column. This shows up more than people expect — eligibility events, promotions applied, security check-ins. If you catch yourself adding a fake count = 1 column just so the table "feels like" a proper fact table, that's usually a sign it's a factless fact table and that's fine.
Too many tiny dimensions: junk dimensions
Say your orders have a handful of small yes/no or short-code attributes: is it gift-wrapped, what payment method was used, was it a rush order. Giving each one its own dimension table is technically correct and practically annoying — five tiny tables, five extra keys cluttering the fact table.
A junk dimension bundles them into one table, with one row per real combination that shows up in your data:
-- dim_order_attributes (junk dimension)
attributes_key | is_gift_wrapped | payment_method | is_rush_order
1 | true | credit_card | false
2 | false | paypal | true
3 | true | credit_card | true
The fact table holds one foreign key instead of three or four. It's not elegant in the textbook sense — it's a deliberate tradeoff of purity for a cleaner schema.
Metadata that doesn't need a dimension: degenerate dimensions
Every order has an order number. It's not really "context" the way a customer or product is — it doesn't have its own attributes, it's just an identifier from the source system. Rather than build a dim_order table with one column in it, you store the order number directly in the fact table:
-- fact_sales
order_line_id | order_number | date_key | customer_key | revenue
9001 | ORD-58291 | 20250915 | 55 | 39.98
That's a degenerate dimension — a dimension-like attribute that lives in the fact table because building a separate table for it would be pure overhead.
When one fact needs many dimension values: bridge tables
Here's the many-to-many problem from the start of this post. A deal in your CRM can have three sales reps sharing credit for it. A fact table row expects one key per dimension, so fact_deals can't just hold three rep_key columns without breaking the model the moment a fourth rep gets added.
A bridge table sits between the fact and the dimension, and carries a weighting factor so the numbers don't double- or triple-count:
-- bridge_deal_reps
deal_id | rep_key | credit_weight
5001 | 12 | 0.5
5001 | 19 | 0.3
5001 | 24 | 0.2
Multiply revenue by credit_weight when reporting by rep, and the totals still add up correctly across the team even though three people are attached to one deal. This same pattern handles patients with multiple diagnoses, students with multiple majors — anywhere the real world refuses to be one-to-one.
Keeping fact tables speaking the same language: conformed dimensions
Once you have more than one fact table — fact_sales and fact_support_tickets, say — someone will eventually ask "which customers bought furniture and also filed a support ticket last quarter?" That question only works cleanly if both fact tables point to the same dim_customer and dim_date tables, with the same keys and the same definitions.
That shared table is a conformed dimension. It's less a technique and more a rule: build dim_date once, build dim_customer once, and make every fact table in the warehouse reference those same tables instead of each team quietly building their own slightly-different version. Skip this, and you're back to the finance-vs-marketing mismatch problem, just one layer deeper.
One step further: Data Vault, briefly
If you ever land somewhere with heavy audit or compliance requirements — banking, insurance, healthcare — you might run into Data Vault modeling instead of star schemas. It splits everything into three table types: hubs (just the business keys, like customer IDs), links (relationships between hubs), and satellites (the descriptive attributes, each with a timestamp). It's more rigid and more verbose than a star schema, but it makes "what did we know, and when did we know it" fully traceable. You probably won't build one as an early-career engineer, but knowing the name and the shape means you won't be lost the first time you see one in a legacy system.
The actual lesson here
None of these are things to use everywhere, all the time. A junk dimension on a table with one boolean column is overkill. A bridge table where the relationship really is one-to-one is overkill in the other direction. The skill isn't memorizing all seven patterns — it's recognizing which real-world shape you're looking at (a snapshot, a many-to-many, an event with no number attached) and reaching for the model built for that shape instead of forcing everything into the one you learned first.
Which one of these have you had to reach for on the job, and what broke that made you go looking for it?
Top comments (0)