← Showroom PodForge demo: one real notebook, read-only. Chat answers, scripts and audio were made by the live app; writes are disabled.
PodForge
← Dimensional modeling

Episode 1

Star Schemas: Why Data Warehouses Break Every SQL Rule You Know

rendered Maya & Theo · 2,057 words · ~13 min · script v1 (approved)

Audio

done
13:03 · kokoro_onnx Download MP3
Why are some engines unavailable?
  • kokoro: extra not installed (import 'kokoro'); uv sync --extra kokoro
  • chatterbox_nano: extra not installed (import 'chatterbox'); uv sync --extra chatterbox
  • pocket: extra not installed (import 'pocket_tts'); uv sync --extra pocket

Series memory

Transcript

cold open

Theo Okay, so I'm reading Kimball's dimensional modeling guidelines, and there's this section where he lists five explicit reasons you should not normalize your dimension tables. Five. Added complexity, slower joins, minimal space savings, you lose bitmap indexes, and your aggregation performance tanks.

Maya Wait, five reasons against normalizing? The thing every SQL course tells you is sacred?

Theo That's what I'm saying. And this isn't some rogue blog post. This is the foundation of the star schema, which is the most widely used data warehouse pattern out there. Denormalization isn't a shortcut or a hack. It's the founding principle.

Maya So every data warehouse you've ever queried was probably designed by someone who looked at third normal form and said, nah, on purpose.

Theo On purpose. With a list.

framing

Maya Okay, so here's the so what. If you learned SQL through transactional databases, your whole mental model is about eliminating redundancy. Normalize everything, reduce duplication, keep it clean. But dimensional modeling flips that. It's a bottom-up approach where you organize data around business processes, not around stomping out repeated values.

Theo Give me a concrete example of what that looks like.

Maya Sure. The whole vocabulary boils down to two concepts. You've got facts, which are the numeric things you aggregate, like a sales amount or a quantity sold. And you've got dimensions, which are the context around those numbers. The timestamp, the product, the store number. That's it. Facts and dimensions.

Theo So the fact is the what happened and the dimension is the who, where, when.

Maya That's a clean way to put it. And the star schema is literally just a fact table sitting in the middle with dimension tables radiating out from it. It looks like a star when you draw it. That's the name.

Theo Sounds almost too simple. Like there has to be a catch.

Maya Oh, there are catches. And we're going to hit them. But here's what I want us to walk through today. First, how do you actually design a star schema from scratch? Second, why does denormalization help analytical queries, like Theo's five reasons? And third, the messy real-world problem: what happens when your dimensions change over time? Because a customer moves, a product gets renamed, a store closes.

Theo That third one's the one that bites you, isn't it.

Maya Every single time. But let's start at the beginning, because the design method is actually really satisfying once you see how it works.

core 1

Maya So Kimball lays out four steps. Step one: choose the business process. You're not modeling the whole company. You pick one thing—retail store sales, inventory, insurance claims.

Theo Scope it down first.

Maya Right. Step two is the one people skip and then regret. You declare the grain. And grain means you write one sentence—literally one sentence—that describes the most atomic event your fact table will capture. Something like "an individual line item on a customer slip from a retail store."

Theo Why does that matter so much?

Maya Because everything else hangs off it. If your grain is too coarse—say, daily totals instead of individual transactions—you've already lost the ability to answer half your future questions. Steps three and four flow directly from the grain. Three: identify the dimensions. These are the nouns. Date, store, product, employee. Four: identify the facts—the numbers you can add up. Units sold, cost, revenue.

Theo And the fact table sits in the middle with foreign keys pointing out to each dimension.

Maya The fact table is this narrow, tall thing. Millions or billions of rows, but mostly just foreign keys and numeric measures. The dimension tables are the opposite—relatively few rows but wide. Tons of descriptive columns. A product dimension might have brand, category, color, size, manufacturer, all in one row.

Theo Give me a concrete query. What does this look like when you actually write the S-Q-L?

Maya Classic example. You want to know how many TV sets were sold per brand per country in 1997. You've got Fact_Sales. You join it to Dim_Date to filter on the year, Dim_Store to get the country, Dim_Product to get the brand and filter on TV category. Three inner joins, a WHERE clause, a GROUP BY. Done.

Theo That is clean. In a normalized schema that same question might touch a dozen tables.

Maya One more thing worth landing. Not all fact tables are the same. There are three types. Transaction fact tables capture a specific event—one sale, one click. Periodic snapshots capture state at a fixed point in time—like your account balance at month-end. And accumulating snapshots track cumulative totals, like month-to-date sales for a product.

Theo Huh. So the grain shifts depending on which type you're building.

Maya It does. But the four-step method still applies to all three.

analogy

Maya Okay, I want to make the fact-versus-dimension thing really stick. Think about a grocery receipt.

Theo Alright.

Maya The numbers you can add up—price, quantity, tax—those are your facts. Everything printed around them—store address, cashier name, the date, the product description—those are dimensions. You would never redesign the receipt to save ink by not printing the store name at the top.

Theo Because then you can't read it at a glance.

Maya That's the whole philosophy. Star schema makes the same trade-off. You repeat descriptive data across dimension rows so every query reads like a receipt—flat, obvious—instead of chasing foreign keys across a dozen normalized tables to figure out what you're even looking at.

Theo I like it. But where does the receipt thing fall apart?

Maya On a receipt you have one copy. One piece of paper, one transaction. In a warehouse, dimension tables are shared—what Kimball calls conformed dimensions. The same Dim_Date, the same Dim_Product, gets joined to multiple different fact tables across totally different business processes. A receipt can't do that.

Theo So the receipt is the view. The conformed dimension is the reusable building block underneath.

Maya That's a nice way to extend it, yeah.

pushback

Theo Okay, but hold on. This all sounds a little too neat. You're telling me we deliberately duplicate data everywhere—repeat the store name, repeat the product description across dimension rows—and there's no downside?

Maya I didn't say no downside.

Theo Because in the transactional world, we call that an update anomaly. You change a product name and now you have to push that change into how many places? And storage—you're just burning disk to avoid a join?

Maya The storage argument is actually the weakest one. The source says the space savings from normalizing dimensions are minimal. Dimension tables are small compared to fact tables. The fact table is where all the volume lives.

Theo Fine, I'll give you storage. But here's the other thing that bugs me. There's a claim that dimensional models still give you all the same benefits on Hadoop and big data frameworks. That claim literally has a "citation needed" flag on it. Nobody's backed it up.

Maya That's fair. I can't defend that one. The source doesn't support it.

Theo And what about when a dimension gets really big? Like a customer dimension with fifty million rows and a deep geographic hierarchy. You're flattening all of that into one wide table?

Maya No, and this is where Kimball himself makes the exception. He says snowflaking—normalizing the dimension hierarchies back out into sub-tables—has a place. Specifically when a dimension is extremely large, or when part of the hierarchy is shared across multiple dimensions. Like a geography table that both your customer dimension and your supplier dimension need to reference.

Theo So denormalize everything... except when you shouldn't. What's the threshold? When does "large" become "extremely large"?

Maya Honestly? It's never defined. It's a judgment call.

Theo [chuckle] So the rule is: break normalization rules, except sometimes don't, and you'll know when based on vibes.

Maya It's more experienced than vibes, but... yeah, there's no bright line. And there's a real risk on the other side too. If you go overboard creating tons of tiny dimensions, you end up with what's called a centipede schema. So many small tables hanging off the fact table that you're right back to excessive joins anyway.

Theo So you can over-normalize and you can over-denormalize.

Maya The sweet spot is somewhere in the middle, and finding it is the craft part of this work.

Theo Alright, I'll accept that tension. But the thing that really gets messy is when the dimensions themselves change over time, right? A customer moves, a product gets rebranded—

Maya And that is exactly where we're going next. Because slowly changing dimensions are the thing that separates a textbook star schema from one that actually survives in production.

core 2

Maya So here's the classic scenario. You've got a fact table tracking sales, linked to a dimension with salespeople and their regional offices. One day a rep transfers from the California office to Illinois. Now every historical sales report still needs to show that rep under California for the deals they closed there—without breaking your fact-dimension joins.

Theo Because if you just update their office, last quarter's California numbers drop and Illinois numbers spike even though nothing actually happened in Illinois last quarter.

Maya Right. And the way the field handles this has been categorized into types. Kimball's framework goes from Type 0 all the way up to Type 6, but honestly you need to know three: zero, one, and two.

Theo Start with zero.

Maya Type 0 is the simplest. The attribute just doesn't change. Date of birth, original credit score. Most date dimension attributes are Type 0. You set it once and it's done.

Theo So it's not really handling change. It's declaring that change doesn't apply.

Maya It's a conscious decision to say this value is durable. Now Type 1 is where you actually do something—you overwrite the old value with the new one.

Theo So the California row just becomes Illinois.

Maya Yep. Easy to maintain. But you lose all history. And here's the gotcha—if you had aggregate tables that summarized sales by supplier state, those need to be fully recalculated now.

Theo Because the old state is just gone. No record it was ever California.

Maya It's like editing a photo and saving over the original. Quick, but you can never undo it.

Theo Okay, so Type 2 is the one that keeps history?

Maya Type 2 adds a whole new row. Same natural key—same supplier code—but you get either a version number or effective date columns. Unlimited history, every change preserved.

Theo That sounds like the obvious winner. Why wouldn't you always do that?

Maya Two real costs. First, you can no longer enforce referential integrity with a unique constraint on the natural key. You've got multiple rows sharing that key now, so the database can't enforce the relationship for you. You're on your own.

Theo Oh. So you're giving up a safety net.

Maya A real one. And second—if you ever need to make retroactive changes, or you add a new attribute with different effective dates, you might have to update existing transactions to match the new reality. That gets expensive fast.

Theo So Type 2 is powerful but brittle if things keep shifting underneath you.

Maya That's a good way to put it. It works great when changes are genuinely slow—a rep moves offices once every few years. But if the dimension changes frequently, maintaining all those versioned rows adds up.

Theo [chuckle] So the "slowly" in slowly changing dimensions is doing real work.

Maya It really is.

summary

Theo Alright. We covered a lot of ground today. What should someone actually carry out of here?

Maya Okay. First thing—every star schema design starts the same way. Declare the grain in one sentence, then build dimensions and facts around it. If you can't state the grain, you're not ready to model.

Theo And the denormalization we spent all that time on—it's not being sloppy. You're deliberately trading storage for query simplicity and performance. But keep snowflaking in your back pocket for those big shared hierarchies.

Maya And the last one: dimensions change, so pick your SCD type on purpose. Type 1 when you don't need history, Type 2 when you do—and know going in that Type 2 costs you referential integrity.

Theo Don't pick one accidentally by just not thinking about it.

Maya That's the real danger. Alright, that is our episode. If you're building your first warehouse or refactoring one that's been around a while, I hope this gives you a framework to start from. Thanks for spending your commute with us.

Theo See you next time.