A customer moved house, and last year’s revenue moved with her.

First, the two kinds of table

Things that happen, and things that just are.

Every warehouse table is one of two kinds, and once you see it you cannot unsee it.

order 8841
2026-01-14 19:22
customer C-77
₹1,200

customer C-77
Ananya
Bengaluru
joined 2024

Almost every question a business asks is the same shape: add up a fact, split by a dimension. Revenue by city. Orders by month. Refunds by product category. Once you notice that, the reason for splitting tables this way stops being academic.

And the trouble in this whole case comes from one line above: facts are finished, dimensions are not.

The question before all others

“One row means what, exactly?”

Before you design anything, you answer that sentence for the fact table, out loud, in one line. One row per order line. One row per shipment. One row per day per product per store.

That sentence is called the grain, and getting it wrong is the most expensive mistake on this page, because everything you build afterwards inherits it.

If you take one habit from this case, take this one: write the grain down in a comment at the top of the model, in plain words, before writing any SQL. Half of all modelling arguments end the moment somebody does that.

Why it is worth the trouble

Type 1 does not lose her address. It rewrites the past.

That is the part that catches people. Overwriting looks harmless — the customer table is more up to date than it was. Her address is correct. Nothing is missing.

But the orders from January and March do not carry a city of their own. They carry her id, and they look the city up when the report runs. So changing one field in one row silently reached back and moved ₹2,000 of finished history into a city it never happened in.

Now scale it. A customer who changes from the free plan to paid, a product that moves category, a salesperson who changes region, a store that gets reassigned to a new area manager. Every one of those, stored as Type 1, quietly rewrites every report that ever touched it.

This is also why last quarter’s numbers sometimes refuse to match the deck from last quarter, and why nobody can work out who changed them. Nobody did.

How Type 2 actually works

Three columns and a new row. That is the whole mechanism.

Type 2 does not update anything. When something changes, it closes the old row and opens a new one.

customer_key   9102      ← new each version
customer_id    C-77      ← stays the same
city           Pune
valid_from     2026-05-04
valid_to       9999-12-31
is_current     true

join customers d
  on f.customer_id = d.customer_id
 and f.ordered_at >= d.valid_from
 and f.ordered_at <  d.valid_to

Two details that trip people up. The customer_key is new for every version and means nothing to anyone — it exists only so each version has its own identity; people call it a surrogate key. And the is_current flag is a convenience, not the truth: it makes “who are they now” easy, but if you use it for historical reporting you have quietly rebuilt Type 1 with extra steps.

So use Type 2 everywhere?

No. It costs, and most fields do not deserve it.

Every Type 2 field means the dimension grows a row every time anything about that customer changes. Track ten volatile fields on a ten-million-customer table and you have built something enormous, slow, and unpleasant to query.

So decide column by column, and the test is simple:

Most real dimension tables are a mix — a few Type 2 columns that matter for history, and Type 1 for everything else. That is normal and correct, not a compromise.

The mistake worth avoiding: choosing Type 1 because it is easier, and finding out eighteen months later that the history you needed was never kept. You cannot backfill a change you did not record. That one is genuinely unrecoverable.

The shape it makes

One fact in the middle, dimensions around it.

Draw a fact table with its dimensions hanging off it and you get something that looks like a star, which is why everyone calls it that.

The reason to split them is not tidiness. It is that facts and dimensions have opposite lives. Facts are enormous and never change. Dimensions are small and change constantly. Keeping them apart means a customer changing address touches one row, not forty million.

You will also hear about a snowflake, which is the same thing with the dimensions themselves split further — city pulled out of customer, category pulled out of product. It saves space and costs joins. On today’s warehouses that trade has mostly stopped being worth it, for a reason you already know from Case 01: repeated values compress to almost nothing, so the space you save was never really being used.

Three things people get wrong

Including one that sounds like good practice.

If you get asked

“How would you model orders for an e-commerce company?”

Say the grain first. “One row per order line, because people will want revenue by product.” One sentence, and you have already sounded like somebody who has done this.

Then name the dimensions and what changes in each. Customer, product, store, date. Then say which fields need history — city and plan for the customer, category for the product — and which you would happily overwrite.

Then bring up the thing they are hoping for. That if you overwrite the customer’s city, last year’s revenue by city changes, so those columns are Type 2 and the fact joins on the date range rather than just the id. That one sentence covers modelling, history and correctness together.

Then mention what you would not do. Not Type 2 on everything, because the table explodes. Not a private customer table for each team, because the numbers stop matching.

If they push on one big table, do not pick a side. Say the flat table is a serving layer and the star is what you keep, and explain why in terms of what happens when a definition changes.

Two questions

Did it land?