Episode 2
Star Schemas: Why Warehouses Break Normalization on Purpose
rendered Maya & Theo · 1,928 words · ~12 min · script v1 (approved)
Audio
doneTranscript
Theo One row. That's all it takes to quietly break every report in a data warehouse. A supplier table — Acme Supply, headquarters in California. They move to Illinois, you run a simple UPDATE on the state column. Done.
Maya That's just keeping data current.
Theo Sure. Except every sales report you've ever built that grouped totals by supplier state just silently flipped to Illinois. Transactions from three years ago, when that supplier was sitting in California — they now show Illinois.
Maya Because the reports pull the current value, not what the state was at the time of each sale.
Theo No error, no warning. The database did exactly what you asked. In a transactional system you want the freshest data. But a warehouse is built for looking backward — and those sales rows are linked to the supplier record, so one change ripples through every report built on top of it.
Maya So your California revenue didn't get deleted — it just got relabeled.
Theo Silently. Which is worse.
Maya So how do you change a record like that without breaking every historical report tied to it?
Theo That is the question. It's the whole puzzle, and it's trickier than it sounds.
Maya This is exactly the problem dimensional modeling was built for. Ralph Kimball developed this approach — it's a bottom-up method where you pick one business process at a time, like sales or inventory or insurance claims, and model that before moving on.
Theo Bottom-up meaning you don't try to map the entire company on day one.
Maya You start with what people actually need to analyze. And Kimball laid out four steps. First, choose the business process. Second, declare the grain. Third, identify the dimensions. Fourth, identify the facts.
Theo Hang on — what's the grain?
Maya The grain is the exact level of detail each row represents. For a retail store, the grain might be an individual line item on a customer receipt — one row per item purchased.
Theo So if I set the grain too coarsely — say, daily totals instead of individual transactions — I can't drill into the detail later.
Maya It's worse than that. If the grain is wrong, every dimension and every fact you build on top of it is off. Kimball actually says you'll probably come back and revise the grain as you learn more about what the model needs to deliver.
Theo Process, grain, dimensions, facts. But what does this model actually look like? Because so far it could just be a normal normalized database.
Maya [pause] So, what does a dimensional model look like? It comes down to two kinds of tables — fact tables and dimension tables.
Theo What's a fact table?
Maya A fact table stores your measurements — the numbers you add up. Sales price, quantity, weight. The totals line on a receipt.
Theo And a dimension table?
Maya That's the context around those numbers. Who bought it, when, at which store, what product — date, store location, product details, geography.
Theo So the fact table keeps the score, and the dimension tables tell you what game was being played.
Maya Close enough. Concrete example — a Fact Sales table stores one number: units sold. It links to three dimension tables. Dim Date has year and month, Dim Store has country, Dim Product has brand and category.
Theo So every row in the fact table is one transaction, and it just points to those three dimension tables for the descriptive stuff.
Maya That's the shape. Dimension tables are wide — lots of columns, lots of attributes — but relatively few rows. The fact table is the opposite. Narrow, mostly foreign keys and a couple of numbers, but it piles up rows over time. One per transaction.
Theo The fact table is where the volume lives. And now the supplier thing from the top clicks — one change to a dimension table, and every row in the fact table that points to it inherits that change.
Maya Without anyone touching the fact table directly.
Theo [interjection] Mm. Three kinds of fact tables?
Maya Three flavors. Transaction fact tables capture individual events — a single sale. Snapshot fact tables capture state at a point in time — month-end account balance. And accumulating snapshots track aggregate totals — month-to-date sales for a product.
Theo But this is all deliberately denormalized. You're stuffing attributes into those wide dimension tables instead of breaking them into tidy linked tables. Isn't that just bad database design?
Maya Is it? There's a very deliberate reason warehouses play by different rules.
Maya So picture a bicycle wheel. The hub at the center — that's your fact table, holding all the measurements. Revenue, quantity, discount. And each spoke connects to a different dimension. One spoke to time, another to store, another to product, all radiating out from the same center.
Theo Okay, I can see the shape. Does this layout have a name, or are we just calling it the wheel thing?
Maya It's called a star schema.
Theo Hang on — star schema. Is that literally just fact table in the center, dimensions around it?
Maya That's it — one central fact table connected to multiple dimension tables, and when you diagram it the whole thing looks like a star. It's the most common way to structure a data warehouse.
Theo So each spoke — each point of the star — is an equal way into the data?
Maya Any dimension is an equal entry point into the facts. Start from the date, start from the product, start from the store — you always land on the same measurements in the middle. That symmetry is what makes complex queries predictable and controllable.
Theo Where does the bicycle wheel stop working?
Maya When you bolt on too many tiny spokes. If you keep splitting attributes into lots of small dimensions with only a few columns each, you get what Kimball calls a centipede schema. And here's the catch — thinner dimensions mean more joins to answer a single question, so the queries actually get harder, not easier.
Theo More dimensions, worse performance. That's counterintuitive.
Maya Only when they're too skinny. You want rich, meaningful dimensions — not dozens of little stubs.
Theo Alright, but let's come back to what I asked before the wheel analogy. This whole star schema is deliberately denormalized. You're duplicating data across those wide dimension tables. Every SQL course out there teaches normalization to protect data integrity. Isn't this just reckless?
Maya Reckless how, specifically?
Theo You've got the same product category string copied across thousands of rows in a dimension table. In a normalized design, that category lives in one place. One update, done. Here, you're practically inviting inconsistency.
Maya Okay, but Kimball gives five specific reasons developers should not normalize dimensions in a warehouse. Normalizing makes the structure more complex. Performance suffers from extra joins. The space savings are minimal. You lose the ability to use bitmap indexes. And aggregation queries slow down on normalized schemas.
Theo Three of those five are performance arguments. I'm asking about correctness. About data integrity.
Maya And that's where the workload matters. A transactional database handles lots of small writes — inserts, updates, deletes all day — and normalization protects you there. A warehouse is read-heavy. Data arrives in controlled batches, and then analysts run queries against it. The integrity risk is a different shape entirely.
Theo There is an alternative, though, right? Something that tries to normalize inside the star. What is it — a snowflake schema?
Maya Wait — snowflake schema? What does that actually mean?
Theo A snowflake schema is where you take those wide dimension tables and split their hierarchies into separate linked sub-tables instead of keeping everything flat. So instead of one big product dimension, you'd have product linked to category linked to department, each in its own little table.
Maya That does sound tidy.
Theo It sounds correct. Why not just do that?
Maya Because Kimball specifically recommends against snowflaking unless your dimensions are extremely large. It adds complexity for users and can hurt query performance.
Theo "Can" hurt it. Or does hurt it?
Maya The source says "can" — so it depends on the engine. But the broader pattern holds. For analytical workloads, denormalization gives you simpler queries, faster aggregations, and — this is the part I find most compelling — extensibility. You can bolt on a new dimension to a star schema and old queries keep running. They don't break, they don't return different results. Nothing upstream notices.
Theo [pause] Hm. So the answer to "is this bad design" is... it's a different game with different rules.
Maya Different game, different scoring.
Theo Fine. I'll give you that for read-heavy analytical work. But here's what's bugging me now — what happens when the data inside a dimension actually needs to change over time? A store relocates. A product gets rebranded. All that denormalized data is just sitting there with the old values.
Maya Hold that thought.
Maya So — what happens when the data inside a dimension changes. The warehouse world has a whole framework for this, called a slowly changing dimension.
Theo Hang on — slowly changing dimension?
Maya A dimension that holds data which is generally stable but shifts unpredictably. A supplier's address, which office a salesperson sits in, a customer's last name. Not the transactional stuff — the context around it.
Theo Okay. So how do you handle it when the context shifts?
Maya Three approaches — Type 0, Type 1, Type 2. Type 0 is the simplest. The attribute never changes. Date of birth, original credit score. You set it once and walk away.
Theo Fine, that's the boring one. What about the supplier who actually moves?
Maya Type 1. You overwrite the old value in place. Supplier was in California, now they're in Illinois — you update the state column and you're done. Easy to maintain.
Theo But you just destroyed the California record.
Maya Gone. No history anywhere. And any aggregate table that summarized sales by supplier state has to be recalculated from scratch.
Theo So that's how you break historical reports. The salesperson who transfers offices — Type 1 means all their old sales suddenly look like they happened at the new location.
Maya Exactly. And Type 2 is how you prevent that. Instead of overwriting, you add a new row. Same supplier code, new version number or effective dates. Old row says California, new row says Illinois. Past transactions stay linked to the California row.
Theo So historical reports just... keep working?
Maya No recalculation. The aggregates reflect where the supplier was at the time of each sale. You change the dimension without breaking a single report.
Theo So that's how you update a dimension without wrecking your old reports. What's the catch?
Maya Two rows sharing the same supplier code. You have to drop the unique constraint on the natural key, and once you do, the database can no longer enforce referential integrity. Your application code has to handle it.
Theo Hm. That's not trivial.
Maya And if the dimension changes often, Type 2 gets expensive. Every change spawns a new row, and existing transactions might need reprocessing. It's designed for slow, unpredictable changes — hence the name.
Theo So pick the wrong type and you're stuck.
Maya Worse. Pick Type 1 today, realize you need Type 2 a year later, and the history's already gone. Can't recover what you never kept.
Theo All right. Someone's staring at their first warehouse design — what do they actually need to remember?
Maya Three things. Start with a business process, define your grain — facts hold the numbers, dimensions hold the context, star schema puts facts in the center with dimensions radiating out.
Theo And the denormalization — not sloppiness.
Maya Deliberate trade. A little extra storage for dramatically simpler, faster analytical queries. Different game, different scoring.
Theo And plan for change before your first row of data loads.
Maya Pick your slowly-changing-dimension type up front. Retrofitting Type 2 history onto a Type 1 table is expensive, disruptive, and the data you needed is already gone.
Theo [pause] Different game. I'm finally on board with the rules.
Maya Glad to hear it. Thanks for listening, everyone. See you next time.