Modelling for analysis

you stopped copying the application's tables into the warehouse and started shaping it around what happened and what it happened to.

Dimensional modelling

the table has one row per order line, a handful of numbers you can add up, and the rest is foreign keys.

Fact table

everything you would ever put in a group-by or a filter — country, product name, channel — lives in one wide, short table.

Dimension table

someone asked what one row means and three people in the room gave three different answers.

Grain

you joined two fact tables at different grains and every revenue figure quietly doubled.

Fan trap

every dimension is one join away from the fact, so no query needs more than a single hop to get a label.

Star schema

getting the product's category name means joining the fact to product, then product to subcategory, then subcategory to category.

Snowflake schema

marketing and finance both group by the same customer dimension, so their two reports can finally be put side by side.

Conformed dimension

the dimension row has a meaningless integer id of your own invention, which is why the source renumbering its customers did not break anything.

Surrogate key

you joined on the email address, and then two people changed their email and one of them was reused by a new account.

Natural key

a customer moved from Leeds to Bristol, and now you have to decide whether their orders from last year happened in Leeds or in Bristol.

Slowly changing dimension

you overwrite the old value, and last year's report now shows the customer as having always been in Bristol.

SCD type 1

the change closes the old dimension row with an end date and opens a new one, so the customer now has three rows and only one is current.

SCD type 2

the row has both current_region and previous_region, and the region before that is gone.

SCD type 3

the order arrived before the customer record did, so the fact points at a placeholder row labelled unknown.

Late-arriving dimension

the order number sits on the fact table with no dimension table behind it, because there is nothing else to say about an order number.

Degenerate dimension

eight unrelated yes/no flags were collapsed into one small dimension of every combination, instead of eight columns on the fact.

Junk dimension

the same date dimension is joined three times as order date, ship date and return date, and the report is unreadable until you alias them.

Role-playing dimension

a bank account has three owners, so you cannot put an owner key on the fact without triple-counting the balance.

Bridge table

one row per thing that happened, inserted and never touched again, and the table only ever grows.

Transaction fact table

you write one row per account per day whether anything happened or not, so 'what was the balance on the fourteenth' is a lookup rather than a replay.

Periodic snapshot

the row for an order is updated as it moves through picked, packed and shipped, so each stage's date is a column.

Accumulating snapshot

the table records that a student attended a class, with no numbers on it at all, and the only measure is counting rows.

Factless fact table

you summed the daily account balances across the month and got a number thirty times too big.

Semi-additive measure

every attribute anyone might filter on is pre-joined onto the fact, so the analyst's query has no joins in it at all.

One big table

the table has four hundred columns, most queries read six of them, and nobody can tell you what the other three hundred and ninety-four are for.

Wide table

the finance team has its own set of tables, shaped for their questions, built from the shared ones.

Data mart

the same data exists three times: exactly as it arrived, cleaned and typed, and shaped for a specific report.

Medallion architecture

the raw layer is split into keys, relationships and attributes as three kinds of table, and nothing is ever updated or deleted.

Data vault