You have chosen the shape; this is how a star is actually put together. Grain before anything else, then the two kinds of table, then the dimension patterns whose names get dropped in meetings without being explained.
A star has exactly two kinds of table, and one rule that outranks both of them:
Facts
Hold measurements and foreign keys. Nothing else.
Dimensions
Hold the descriptive attributes you filter and group by.
The rule that outranks both: declare the grain first
In one sentence, before a single column is chosen — because every later question, including whether a column is even allowed on the table, is answered by it.
This warehouse's grain sentence is one row per order line. Everything below follows from that sentence:
Why
An undeclared grain is the single most expensive omission in a warehouse, because it does not fail — it fans out. One column true at order level, joined to lines, and revenue is overstated by the average line count. Every total is wrong in the same direction, which is exactly the error nobody catches: the numbers are plausible, they move correctly month on month, and they reconcile with nothing.
The declaration also settles arguments cheaply and permanently. "Can we add order status to the fact?" is a design debate without a grain sentence and a thirty-second answer with one. That is worth more over a project than any diagram.
How
sqlThe grain decides what may live on the fact
-- gold.fct_order_line -- GRAIN: one row per order line.
-- ALLOWED: measures true at this grain
quantity, unit_price, fx_rate, amount_eur
-- ALLOWED: foreign keys to dimensions
customer_sk, product_sk, date_sk, region_sk
-- ALLOWED: degenerate keys - identifiers with no dimension behind them
order_id, line_number
-- NOT ALLOWED: an attribute of the customer, not of the line
customer_name -- lives on gold.dim_customer
-- NOT ALLOWED: a measure true at a COARSER grain
order_total_eur -- true per ORDER; summing it over lines
-- multiplies the order total by its line count
That last one is the whole reason the grain sentence exists. order_total_eur is not wrong as a number — it is wrong at this grain, and it fails silently, because a three-line order simply reports triple. The column looks correct in every spot check that examines one row.
Measures then divide by how they may be added up, which is the second thing to settle at design time:
Additivity — the property that decides whether SUM is a lie
Kind
Adds up across
In this warehouse
The trap
Additive
every dimension
quantity, amount_eur
none — this is what a fact is for
Semi-additive
some dimensions, not time
a stock level or account balance
SUM over months instead of a period-end pick
Non-additive
nothing
unit_price, fx_rate
SUM(unit_price) runs, returns a number, and means nothing
Facts also come in three shapes, and naming the shape stops an argument about whether a table is missing rows:
Transaction fact — one row per event
The default. gold.fct_order_line is one.
Periodic snapshot — one row per entity per period
Written whether or not anything happened, which is how you answer “what was the balance in March” without replaying every event.
Accumulating snapshot — one row per process instance
Updated in place as the process moves: ordered, picked, shipped, invoiced. The right shape for lead-time questions and the wrong shape for everything else, because it is the one fact table that mutates.
Doing it
Write the grain as a sentence in the table comment, not in a documentOne row per order line. It has to be where the next engineer looks, which is the DDL, not a wiki nobody has opened since kickoff.
Build facts at the finest grain the source delivers, then derive aggregatesgold.agg_revenue_month comes from gold.fct_order_line, never from silver. Two independent paths to one number is two numbers.
Classify every measure as additive, semi-additive or non-additive when you add itIt takes a second at design time and it is the difference between a metric view that is safe to expose and one that is not.
Put non-additive measures behind a metric view rather than on a dashboardA weighted average has to be a defined expression. Left as a raw column, someone drops it on a visual and Power BI happily sums it.
Test the grain, do not assume itA uniqueness assertion on (order_id, line_number) is one query and it is the only thing standing between you and a silent fan-out.
Embedding it
Open every model review by having someone read the grain sentence aloudIf they cannot, the review stops there. It sounds theatrical and it catches real problems in the first two minutes.
Make "which grain is this true at?" the standard question about any new columnIt transfers the discipline without a training session, because the team hears it in every review until they ask it themselves.
Have a new joiner add one measure end to end, additivity classification includedSmall, safe, and it exercises the one habit that prevents the expensive failure.
Defaults
Grain declared in a sentence, in the table comment, before columns
Facts hold measures, foreign keys and degenerate keys — nothing descriptive
Transaction facts by default; snapshots only for a question that needs them
Every measure classified for additivity at the moment it is added
A uniqueness test on the grain columns, running in CI
Gotchas
A measure at a coarser grain than the factorder_total_eur on an order-line fact multiplies by the line count on every SUM. It never errors, it moves correctly month on month, and it is only ever caught by a reconciliation against the source — which is why that reconciliation is not optional.
SUM over a non-additive measureSUM(unit_price) is valid SQL and meaningless output. Nothing in the platform will stop it, no test fails, and it reaches a board pack looking exactly like a number.
Summing a semi-additive measure across timeStock and balances add across products and regions but not across months — the March answer is a period-end pick, not a sum. Totalling a year of month-end balances produces a figure roughly twelve times too large that still charts smoothly.
Descriptive attributes creeping onto the fact for conveniencecustomer_name on the fact freezes the name at load time. The dimension then updates, the two disagree, and the fact quietly wins because it is the table the report reads.
An accumulating snapshot built without anyone noticing it mutatesIt is the one fact shape that updates rows in place, which breaks append-only assumptions in downstream streaming reads, CDC feeds and anything that trusted the fact to be immutable.
Four or five named patterns cover almost every dimension that is not a plain descriptive table. They are worth knowing by name because they are the standard answers to questions that otherwise get solved badly and privately — and because saying "that is a junk dimension" ends a design discussion that would otherwise run twenty minutes.
The patterns, and what each one is the answer to
Pattern
The question it answers
Here
Degenerate
An identifier with no attributes — where does it go?
order_id, line_number sit ON the fact. No dim_order exists.
Junk
Several low-cardinality flags cluttering the fact
status + currency, if a third and fourth flag ever arrive
Role-playing
One dimension used in several roles
dim_date as order date, ship date, invoice date
Mini-dimension
One attribute changes far faster than the rest
customer_segment, if it started churning monthly
Bridge
A genuine many-to-many
brg_product_group — a product in several groups
Conformed
Two facts that must be grouped the same way
dim_customer, shared by orders and anything added later
Two of those are already in this warehouse and worth reading in the DDL rather than in prose:
Why
Without the vocabulary, each of these gets reinvented, worse. The team that has not heard "degenerate dimension" builds dim_order holding nothing but order_id, joins it on every query, and has a table whose only function is to be joined. The team that has not heard "role-playing" copies dim_date three times and then maintains three calendars until two of them disagree about which days are holidays.
The names also protect against the opposite failure, which is applying all of them because they are in the book. A junk dimension over two flags and a mini-dimension over an attribute that changes twice a year are both extra joins bought with no benefit — and unlike a missing pattern, an unnecessary one is never removed.
How
sqlDegenerate keys, and role-playing done with views
-- DEGENERATE: order_id has no attributes of its own, so there is no
-- dim_order. It lives on the fact, and it is still perfectly groupable.
SELECT order_id, COUNT(*) AS lines, SUM(amount_eur) AS order_value
FROM gold.fct_order_line
WHERE status <> 'CANCELLED'
GROUP BY order_id;
-- ROLE-PLAYING: one dim_date, several roles. Views, not copies -
-- a second physical date dimension is a second calendar to maintain.
CREATE OR REPLACE VIEW gold.dim_order_date AS SELECT * FROM gold.dim_date;
CREATE OR REPLACE VIEW gold.dim_ship_date AS SELECT * FROM gold.dim_date;
-- the BI tool then joins each role to its own view, and a report can
-- say "ordered in March, shipped in April" without aliasing by hand.
A junk dimension is the one most often applied too early. It collapses several low-cardinality flags into one small table holding every combination that actually occurs, replacing three or four columns on the fact with one key. With this warehouse's two flags — status and currency — it is not worth it, and the honest answer to "should we junk these?" today is no:
sqlWhat a junk dimension would look like, and the threshold for wanting one
-- Worth doing at four or more flags. With two, it buys a join.
CREATE TABLE gold.dim_order_flags (
order_flags_sk STRING NOT NULL, -- sha2 of the combination
status STRING, -- OPEN | SHIPPED | CANCELLED
currency STRING, -- EUR | CHF
is_backorder BOOLEAN, -- the flags that would justify it
channel STRING -- SAP | SHOP
);
-- rows = combinations that OCCUR, not the cartesian product:
-- 3 x 2 x 2 x 2 = 24 possible, and typically far fewer in reality.
-- the fact then carries one key instead of four columns
-- fct_order_line.order_flags_sk -> dim_order_flags.order_flags_sk
A mini-dimension answers a different problem: one attribute on a large SCD2 dimension changing so often that it drives the version count on its own. Splitting customer_segment into its own small dimension, referenced from the fact, means a segment change no longer creates a new customer version. It is the right move at scale and a premature one here, where segment changes are the reason the dimension is SCD2 at all — business rule 4 says so explicitly.
Doing it
Leave identifiers with no attributes on the fact and say the word degenerate out loudIt stops dim_order from being proposed, which it will be, in the first design review.
Implement role-playing as views over one dimension, never as copiesOne calendar, one holiday list, one place a fiscal-year change is applied.
Hold the junk dimension until the fourth flagTwo flags on the fact are readable and free. Four are clutter, and that is the point at which the join starts paying for itself.
Reach for a mini-dimension only when version counts prove you need oneCount versions per customer first. If the rate is fine, splitting the attribute costs a join and buys nothing.
Conform a dimension the first time a second fact needs it, not beforeConformance is what makes two facts comparable; doing it speculatively is how a dimension acquires columns no fact uses.
Embedding it
Teach the six names in one session with this warehouse as the worked exampleThey are recognition patterns, not theory — an hour against tables the team already knows transfers all of them.
Ask which pattern applies before accepting any new dimensionMost proposed dimensions are a degenerate key or a junk candidate, and the question surfaces that before the table exists.
Have the team justify each pattern by the join it savesIt keeps the vocabulary as a tool rather than a checklist to complete.
Defaults
Degenerate keys on the fact; no dimension for an attribute-free identifier
Role-playing via views over one physical dimension
Junk dimension from four flags upward, holding occurring combinations only
Mini-dimension only when measured version counts justify it
Bridge tables carry no measures — they resolve many-to-many and nothing else
Gotchas
Building dim_order for a degenerate keyA dimension whose only column is the key it is joined on. It costs a join on every query and returns nothing, and once reports depend on it, removing it is a migration rather than a deletion.
Physically copying a dimension for each roleThree dim_date tables means three calendars. They agree until the first fiscal-year or holiday change is applied to two of them, and the report that spans roles then disagrees with itself.
Building the junk dimension as a cartesian productGenerating every combination rather than the ones that occur produces a dimension mostly full of rows no fact points at, and the first analyst to browse it concludes the data is broken.
Summing a measure through a bridge tableA product in three groups joins three times, and revenue triples. Bridges need an allocation factor or a discipline about which questions may be asked through them — and the fan-out looks exactly like growth.
Calling a dimension conformed when only its name is sharedTwo teams with a customer dimension each is not conformance. Conformance is the same keys and the same attribute meanings, and discovering the difference happens when the two facts are finally compared and disagree.