Data Modeling Conceptual

The reasoning

A grain is a sentence, not a column list. "One row per line on an order", "one row per account per day", "one row per shipment status change". Once it is written down, every subsequent question has a mechanical answer: can this dimension join at this grain, is this measure additive at this grain, does this column belong here at all.

The reason it comes first is that it is the only decision you cannot change cheaply. Adding a dimension later is a column; changing the grain later means rewriting the load, restating every measure, and invalidating every report built on the old meaning. Kimball puts grain declaration second in the four-step process for exactly this reason — after choosing the business process and before anything else.

It also gives you a test. A declared grain implies a unique key — order plus line number, here — so "is my grain intact?" becomes a query anyone can run: count the rows and count the distinct key. When those two numbers diverge, the table is no longer what it claims to be, and every SUM over it is inflated by the difference. The snippet shows exactly that: three rows loaded, two distinct at the declared grain, and revenue reporting 250 instead of 150.

That test belongs in the pipeline, not in someone's head. A uniqueness assertion on the grain key is the single highest-value data test you can write, because a broken grain does not fail — it silently multiplies money.

The reasoning

A **transaction** fact records something that happened: a payment, a click, an order line. It is insert-only, it grows with activity, and it is the natural grain for anything you want to sum. What it cannot answer cheaply is "what was the balance on the 14th" — that requires summing every event since the beginning of time.

A **periodic snapshot** records state at a regular interval: one row per account per day, per product per week. It answers status questions directly, and crucially it has a row even when nothing happened — which is what makes "accounts with zero activity" and "average balance" straightforward. The cost is that it grows with entities × periods rather than with activity, so it is often the largest table in the warehouse. Its measures are usually semi-additive.

An **accumulating snapshot** records a process with a known set of milestones: one row per order, with columns for ordered, picked, shipped, delivered. The row is *updated* as the process advances, which is unusual in a warehouse, and it earns that by making lag questions trivial — "average days from order to ship" is one subtraction rather than a self-join over an event stream.

The three are not alternatives so much as complements, and a mature model often has all three over the same process. The interview signal is being able to say which question sent you to which: sums go to the transaction fact, states and averages to the snapshot, durations to the accumulating one.

The reasoning

**Additive** measures sum across every dimension: revenue, quantity, cost. They are the easy case and the reason people stop thinking about additivity. **Semi-additive** measures sum across everything *except* time — an account balance summed across accounts is meaningful, summed across days it is nonsense. **Non-additive** measures cannot be summed across anything: unit price, margin percentage, conversion rate, any ratio.

The snippet shows the semi-additive failure with two accounts over three days. Summing the balance column gives 480. The actual money is 160 — the balance on the last day. The 480 is not a rounding error or an edge case; it is three days of the same money counted three times, and nothing about the query looks wrong.

For semi-additive measures the fix is to specify the time behaviour explicitly: last value, first value, or average over the period. Warehouses and semantic layers have direct support for this — a `LAST_VALUE` window, or a measure defined in the semantic layer with an explicit non-additive time dimension — and that is where the rule belongs, so that every consumer gets it right rather than each analyst rediscovering it.

For non-additive measures the rule is stronger: store the numerator and the denominator, never the ratio. `SUM(margin) / SUM(revenue)` is correct at any grain; `AVG(margin_pct)` is wrong at every grain above the one it was computed at. Storing a percentage in a fact table is a design decision to be wrong later.

The reasoning

A column stored at a coarser grain than the table is repeated, not duplicated by accident — it is repeated *by design*, because the row is a line and the value belongs to the order. Every one of those repetitions is a real row, so `SUM(shipping)` counts a two-line order's shipping twice. The snippet shows 25 where the true figure is 15.

The reason it survives review is that the table is not corrupt and no test fails. The grain is intact, the line measures are correct, and only the borrowed column is wrong — and only when summed. `AVG` and `MAX` on it look fine, which makes the defect intermittent from the reader's point of view.

Fix one: a separate fact at order grain, holding order-level measures, joined to the line fact by `order_id`. This is the honest answer and the usual right one — two processes, two grains, two tables, and drill-across when you need both. Nothing is repeated and nothing needs a special rule.

Fix two: allocate the order-level value down to line grain, typically in proportion to line amount, and store the allocated figure. Now it is genuinely additive and a plain `SUM` is correct at every level. The cost is that the allocation rule is a business decision baked into the data — and it must be documented, because 30 divided across three lines does not come back as an exact per-line figure without a rounding policy.

What is *not* a fix is telling analysts to write `SUM(DISTINCT shipping)` or to divide by the line count. Both put the correction in every query, which means it will be missing from one of them.

The reasoning

Conformance is agreement, not a table. Two fact tables share a conformed dimension when a given surrogate key means the same entity in both, and when the attributes mean the same thing: the same category hierarchy, the same definitions, the same effective dates. It can be one physical table used by both, or two tables kept in sync — the requirement is the agreement, not the storage.

The capability it buys is **drill-across**: query each fact separately at the common grain, then join the two result sets on the shared dimension attributes. That is how the snippet reports sold and returned by category side by side. It works because "tools" means the same thing in both queries. If the two marts had built their own product dimensions, the categories would not line up and the report would be arithmetic performed on two different definitions.

Note what drill-across is *not*: it is not joining two fact tables together. Joining facts directly multiplies their rows — a classic fan-out — so the discipline is to aggregate each fact to the common grain first and join the aggregates. This is why the pattern needs a name; the intuitive approach is wrong.

The organisational version of conformance is the bus matrix: business processes down the side, conformed dimensions across the top, ticks where they intersect. It is a planning artefact rather than a technical one, and its value is that it makes the agreement visible before two teams have shipped two incompatible definitions of "customer".

The reasoning

A dimension earns its table by holding descriptive attributes you want to filter and group by. An order number has none: everything you would want to know about the order — the customer, the date, the channel — is already on the fact row as its own dimension key. What is left is the identifier itself, so it is stored as a plain column on the fact.

It is still a dimension in every way that matters. It is not a measure, you never sum it, and it does real work: it groups the lines of one order together and it is what makes `COUNT(DISTINCT order_id)` meaningful. The snippet is the whole use case — three lines, two orders, one revenue figure — and that count is impossible if the identifier was discarded as "not a measure".

The distinction from a **junk dimension** is worth being precise about, because they are often confused. Degenerate means there are no attributes at all. Junk means there are several low-cardinality attributes — flags, small codes — that do not deserve a dimension each, so they are collapsed into one. Different problem, different solution.

The practical rule: if a candidate dimension table would end up with exactly one column, and that column is its own key, you have found a degenerate dimension. Leave it on the fact and note it in the model, since the alternative is a join that returns the value you already had.

The reasoning

The alternatives are all worse in obvious ways: a dimension per flag means six joins for six yes/no answers; leaving them on the fact means six columns of very low information density, and no place to put a readable label for the code values. A junk dimension gives you one key on the fact and one small table holding every combination in use, with proper descriptions.

The sizing calculation is what makes it a judgement rather than a rule, and the important part is that you size by *observed* combinations rather than by the cross product. Four booleans and two five-value codes is theoretically 400 rows, but real data is heavily skewed — the snippet has three flag combinations across five orders — so the dimension is usually far smaller than the arithmetic suggests. Build it from the distinct combinations present, and add rows as new ones appear.

It stops being worth it when the attributes are not actually low-cardinality, when they change over time in a way you need to track (that is an SCD question, not a junk question), or when the combinations grow toward the fact's row count, at which point you have added a join and saved nothing.

The name is unhelpful and worth setting aside in an interview. "Junk" describes the modeller's feeling about the columns, not the technique; the technique is simply grouping several small unrelated attributes into one dimension so the fact stays narrow.

The reasoning

Physically there is one date dimension and three foreign keys on the fact. The join is straightforward — alias the dimension once per role, as the snippet does — and the calendar attributes, fiscal periods and holiday flags are maintained in exactly one place, which is the whole point.

The problem is naming rather than joining. A raw three-way self-join gives you three columns called `year` and three called `is_weekend`, which is unusable in a BI tool and confusing in SQL. The standard solution is a view per role — `dim_ordered_date`, `dim_shipped_date` — that selects the same table with prefixed column names. The storage is shared; the semantics are separated.

This generalises past dates. A payment fact with a payer and a payee both pointing at `dim_party`, a transfer with a source and destination account, a ticket with a reporter and an assignee — all role-playing. The test is whether the two keys reference the same *set of entities* with the same meaning; if so, one dimension and two roles.

Two details worth having ready. A date dimension should be a real table rather than computed from the timestamp, because it carries fiscal calendars, holidays and business-day flags that no date function knows. And each role can carry a different **grain of time semantics** — an ordered date is when the customer acted, a shipped date is a warehouse event — so the roles are business-distinct even though the attributes are identical.

The reasoning

The decisive reason is Type 2 history. If a customer moves region and you keep both versions, `customer_id` appears twice in the dimension. It can no longer be the primary key, and a fact row joining on it would match both versions and double. The surrogate key identifies the row — this customer, during this period — which is precisely what a fact row needs to point at.

Three further reasons stand on their own. Source keys change: systems get migrated, ids get reissued, a vendor switches from numeric to UUID, and every fact row referencing them would have to be rewritten. Multiple sources collide: two CRMs both have a customer 1001, and a surrogate lets you integrate them behind a single dimension. And type: a compact integer key joins faster and stores smaller than a 40-character composite string, across billions of fact rows.

What the surrogate does not do is remove the need for the natural key. It stays on the dimension as an attribute, because it is how you match incoming source records to existing dimension rows during the load, and how a human traces a warehouse row back to the system it came from. Dropping it is a common and painful mistake.

The modern variant worth mentioning is the **hash key** — a deterministic hash of the natural key and, for Type 2, the effective date. It buys you keys that can be computed independently in parallel loads without a central sequence, which matters in a lakehouse. It costs you a wider key and no ordering, and it does not change any of the reasoning above.

The reasoning

A surrogate key is a sequence position, and a sequence position depends on the order rows happened to arrive. Reload the dimension from a source that sorted differently, restore from a backup, rebuild after a schema change, and the same customer can end up with a different key. The snippet shows the consequence at its starkest: identical fact rows, an identical query, and EU revenue and US revenue have swapped places.

That is why it must not leave the warehouse. Printed on a report, a user quotes it back next quarter and it means someone else. Stored in another system, that system now holds a pointer into a table it does not control. Joined across two warehouses, you are joining two unrelated sequences that happen to be integers, which will match and be meaningless.

The protection is procedural, and it is worth stating: dimension loads must be **incremental and key-preserving** — look up the natural key, reuse the existing surrogate if present, assign a new one only for genuinely new entities. A full truncate-and-reload of a dimension referenced by facts is the operation that causes this, and it should be treated as a migration rather than a refresh.

When a stable identifier genuinely has to travel, send the natural key, or a hash of it. Both are derived from the business, so both are reproducible by anyone with the same input — which is the property a surrogate specifically lacks.

The reasoning

The mechanics are ordinary SQL. `JOIN dim_customer ON d.customer_sk = f.customer_sk` with a NULL on the left compares to UNKNOWN, never TRUE, so the row is dropped. Every measure on that row goes with it. The snippet loses one order and 50 of revenue, and nothing in the query looks wrong — the only symptom is that the total does not tie to the source.

You can defend against it with a LEFT JOIN at every call site, and that is exactly the problem: every call site. One analyst writing an inner join re-creates the bug, and inner joins are what BI tools generate by default. Putting the fix in the data means it cannot be forgotten.

The unknown member is a real dimension row with a reserved key — conventionally -1 or 0 — and readable attribute values ("Unknown", "Not applicable", "Late arriving"). Facts that cannot be matched point at it. Now the join is an inner join, the totals tie, and the unmatched volume shows up as a visible bucket that someone can be asked about.

It is worth distinguishing the reasons for a miss, because they call for different responses. "Not applicable" — a return with no salesperson — is permanent and fine. "Not yet arrived" — a late-arriving dimension — should be repaired when the record shows up. Some models use different reserved keys for the two so that the second can be monitored and re-processed rather than quietly accepted.

The reasoning

A bridge resolves many-to-many by sitting between the fact and the dimension, one row per pairing. The join is correct in the sense that it produces exactly the rows you asked for, and it is a fan-out: the fact row is repeated once per associated campaign, and any measure summed after the join is multiplied by the number of associations.

The snippet makes the size of it visible. Summed through the bridge, email reports 160 and paid reports 100, which totals 260 — against a genuine total of 160. Nobody would notice from the query, and the per-campaign figures individually look plausible.

The allocation factor is a weight on the bridge row, with the weights for one fact summing to 1. Multiplying the measure by it before summing gives email 110 and paid 50, which totals exactly 160. The measure is additive again at every level, and the model has absorbed the correction so that no analyst has to know about it.

The hard part is not the technique, it is that the weights are a business decision — even split, first touch, last touch, time-decayed — and different departments will want different ones. Two defensible responses: pick one, name it in the column (`even_split_allocation`), and document it; or store several weighting columns on the bridge so a report can choose, at the cost of explaining why two dashboards disagree.

Where allocation cannot be agreed, the honest alternative is to keep the bridge un-weighted and forbid summing through it — expose counts and distinct-order metrics instead of revenue, and put the un-allocated revenue on a report that does not join the bridge at all.

The reasoning

**Type 0** — retain original, never update. Right for attributes that must not change: original credit score, date of birth, the value at signup. Query cost: none. Risk: the attribute silently drifts from reality and nobody notices, so it must be a deliberate choice rather than a load that forgot to update.

**Type 1** — overwrite in place. One row per entity, joins are simple, storage is minimal. The cost is total and irreversible: yesterday's report cannot be reproduced, because the value it used no longer exists anywhere. Right for corrections — a misspelled name — and wrong for anything a report groups by.

**Type 2** — a new row per version, with `valid_from`, `valid_to` and usually `is_current`. History is complete and this is the default for a reason. The cost lands entirely on queries: every fact join needs an as-of predicate, "current" queries need the flag, counting customers needs `COUNT(DISTINCT natural_key)` rather than `COUNT(*)`, and the dimension grows with change rather than with entities. Almost every SCD bug is someone forgetting one of those.

**Type 3** — add a `previous_region` column beside `region`. Cheap to query and only remembers one change, so it suits a single planned re-organisation ("this year's territory and last year's") rather than open-ended history.

**Type 4** — move the fast-changing attributes into a separate mini-dimension with its own key on the fact, leaving the main dimension stable. This is the answer when Type 2 on a volatile attribute would explode the row count — age bands, credit score bands, behavioural segments. The cost is one more join and a second thing to keep conformed.

**Type 6** — the union of 1, 2 and 3 (and the reason for the name: 1+2+3). Type 2 rows for history, plus a "current" column on every row updated Type 1 style. That lets one query ask "revenue by the region they were in at the time" and another ask "by the region they are in now", without a second join. It is the most useful of the hybrids and the most work to load correctly.

The framing worth offering unprompted: the choice is per attribute, not per dimension. One customer dimension can reasonably hold a Type 0 signup date, Type 1 corrections to a name, and Type 2 on region — and that mixture is normal rather than a compromise.

The reasoning

The overwrite is retroactive by nature. The January order still has the same date and the same amount; what changed is the attribute it is grouped by. Because the dimension holds one row per customer, the join returns today's region for a transaction that happened in January, and the entire history of that customer is restated.

The snippet is deliberately stark. Before the change the report reads EU 100, US 50. After it reads US 150 — and there is no EU row at all. A stakeholder comparing the two would conclude that EU revenue was deleted, and every explanation they reach for first will be wrong.

The distinction to name is **as-was versus as-is**. "Revenue by the region the customer was in when they ordered" is as-was and needs Type 2. "Revenue by the region they are in now" is as-is and is what Type 1 gives you. Both are legitimate questions; the failure is having a model that can only answer one while people assume it answers the other.

This is also why Type 1 changes should be treated as a data event rather than a routine update. If the attribute is one that reports group by, an overwrite invalidates published numbers, and at minimum it should be logged — many teams keep an audit table of Type 1 changes precisely so that "why did last quarter move?" is answerable.

The practical rule for deciding: if any report groups or filters by the attribute, and anyone would care about the historical view, it is Type 2. Type 1 is for correcting values that were wrong, not for recording values that changed.

The reasoning

Joining on `is_current` restricts the dimension to one row per customer, so it behaves precisely like a Type 1 dimension while paying all of Type 2's storage and load cost. The snippet shows the damage: all 190 of revenue lands in US, because C1 is in US today — even though 100 of it was earned while C1 was in EU. The as-of join splits it correctly into EU 100 and US 90.

The correct predicate is `f.order_day >= d.valid_from AND f.order_day < d.valid_to`. The asymmetry matters: `>=` on the lower bound and strict `<` on the upper means the intervals are half-open, so a fact falling exactly on a change boundary matches exactly one version. Using `BETWEEN` — inclusive at both ends — makes the boundary date match two rows and duplicates the fact, which is a fan-out that only appears on the days something changed.

The `9999-12-31` sentinel in `valid_to` is doing real work here. A NULL would be more honest about "no end yet", and it would make the predicate fail for every current row unless every query remembers to add `OR d.valid_to IS NULL`. A far-future date keeps the comparison simple and uniform, which is why it is the convention despite looking like a hack.

The better structural answer, where the model allows it, is to resolve the version at load time: the ETL looks up the correct `customer_sk` for the fact's date and stores that key on the fact. Then the query is an ordinary equality join on the surrogate key, the as-of logic exists in exactly one place, and no analyst can get it wrong. That is what surrogate keys on Type 2 dimensions are for, and it is why facts should reference `customer_sk` rather than `customer_id`.

Both forms remain useful, so be explicit about which question is being asked: as-of for "how the business looked at the time", `is_current` for "everything this customer has ever bought, grouped by who they are now". Naming them in the semantic layer stops the choice being accidental.

The reasoning

**Drop** is what an inner join does by default, and it is the option nobody chooses deliberately. The fact is simply absent, totals do not tie to source, and there is no error to investigate. The snippet shows one order and 50 of revenue disappearing this way.

**Hold** — quarantine unmatched facts and retry next cycle — keeps correctness at the cost of latency and a growing backlog. It is defensible when the dimension feed is reliably minutes behind, and it becomes a liability when one broken feed holds an entire day of facts hostage.

**Inferred member** is the standard answer. On a miss, insert a dimension row immediately with the natural key, a fresh surrogate key, and attributes marked unknown. The fact joins to it right away, so nothing is lost and nothing waits. When the real record arrives, the row is *updated in place* — same surrogate key — and every fact already pointing at it is enriched retroactively with no fact rewriting at all. That is the property that makes it work, and it is why the surrogate must be assigned by you rather than derived from attributes you do not yet have.

The distinction from the **unknown member** matters and is often blurred. The unknown member is one shared row for "we will never know". An inferred member is one row per genuinely-new entity, expected to be filled in. Pointing every late arrival at the shared unknown row loses the ability to repair it later, because you no longer know which facts belonged to which customer.

Operationally, inferred members need monitoring: a count of dimension rows still marked inferred, alerting if it grows or if rows stay inferred beyond an expected window. Without that, a permanently broken dimension feed looks exactly like a healthy pipeline whose "Unknown" bucket happens to be large.

The reasoning

**1NF** — each cell holds a single atomic value and there are no repeating groups. A `phone_numbers` column containing "555-1234, 555-9876" violates it, as does `product_1, product_2, product_3`. The consequence is that you cannot query it without string manipulation, and you cannot constrain or index it.

**2NF** — 1NF, plus no non-key column depends on only *part* of a composite key. In an order-line table keyed by (order_id, line_no), a `customer_name` column depends only on the order, not on the line, so it is repeated on every line of that order. That is a partial dependency, and it is the same defect as the mixed-grain fact from the grain cluster, described in relational language.

**3NF** — 2NF, plus no non-key column depends on another non-key column. If a table has `zip_code` and `city`, city is determined by zip rather than by the key. Storing both means the same zip can appear with two different cities.

The unifying idea is worth stating instead of the definitions: **every fact should be stored exactly once**. Where it is stored twice, the two copies can disagree — which is the update anomaly the snippet shows, one customer with two different email addresses across three rows and nothing in the schema able to prevent it. Insert and delete anomalies are the same defect from other directions: you cannot record a customer with no orders, and deleting their last order erases them.

The reason this matters for a data engineer is that it is where OLTP and analytics genuinely diverge. Normalize the operational system, because it is written concurrently and correctness under update is everything. Denormalize the analytical model deliberately, because it is written once by a controlled process and read constantly — the redundancy that is dangerous in OLTP is safe when a single pipeline owns it.

The reasoning

The snippet is the whole shape of it: the star answers "revenue by department" with one join, the snowflake needs three, and both return exactly the same numbers. That is the trade in its simplest form — identical answers, different amounts of work to ask for them.

The historical case for snowflaking was storage: repeating "hardware" on every product row wasted space on row-oriented systems with expensive disk. Columnar warehouses compress a low-cardinality repeated string to almost nothing, so the saving is now measured in a rounding error while the extra joins are still real.

What survives is maintenance and reuse. A hierarchy that changes independently, or one that several dimensions share — a geography hierarchy used by customer, supplier and store — has a genuine argument for its own table, because otherwise the same hierarchy is maintained in three places and can disagree. Very large dimensions with a genuinely repetitive attribute set are the other real case.

Against that, a star is simply easier to use. Fewer joins, obvious query paths, and BI tools that generate SQL work better against it — several assume a star and will produce awkward or wrong SQL against a snowflake without careful configuration. Given that the point of a dimensional model is to be queried by people who did not build it, legibility is not a soft consideration.

The pragmatic position to state: star by default, snowflake the specific hierarchy where maintenance genuinely demands it, and expose a flattened view over the snowflake so consumers still see a star. That gets both properties, and it is what most mature warehouses actually do.

The reasoning

Normalization protects against update anomalies, and an update anomaly requires updates from more than one place. An operational database has thousands of concurrent writers touching the same rows, so storing a customer's email twice means two code paths can update one and miss the other. That is why 3NF is the right default there, and why constraints and transactions matter so much.

A warehouse has one writer: the load. If `region` appears on a million fact rows, it was put there by one pipeline reading one source, and it is refreshed by re-running that pipeline. The two copies cannot disagree, because there are not two writers — the redundancy is real and the *anomaly* is not. This is the actual argument, and it is stronger than the usual "joins are slow".

The benefits you buy with it are legibility and stability as much as speed. Fewer joins means query paths a business user can follow; a wide dimension is self-describing; and a denormalized fact row is a stable record of what was true, immune to a later dimension change — which is closely related to why Type 2 exists.

The costs are real and worth naming: storage, and the fact that a change to an attribute means rewriting many rows rather than one. That second cost is what an incremental model has to plan for, and it is why a full-refresh dimension and an incremental fact are loaded differently.

The line to draw: denormalize *attributes* onto dimensions freely, denormalize dimensions onto facts sparingly, and never denormalize something whose history you have not decided about. Copying `region` onto the fact is a decision to freeze it at load time — which is sometimes exactly right, and should be a decision rather than a side effect.

The reasoning

The case for it is real. Columnar engines read only the columns a query touches, so a 200-column table costs the same as a narrow one for a query using four columns. No joins means no join mistakes, no fan-out, and no as-of predicate to forget — which for a consumer-facing serving table is a genuine correctness argument, not just a convenience.

The case against is maintenance and meaning. A customer changing region means rewriting every row that customer appears on rather than one dimension row. Adding an attribute means backfilling the whole table. Two teams building their own OBTs end up with two definitions of "active customer" and no conformed dimension to reconcile them. And the grain becomes easy to lose, because there is nothing structural stopping someone adding a column at a different level.

The resolution most mature platforms reach is that these are layers, not alternatives: a dimensional core — conformed dimensions, facts at declared grain, history where it is needed — and OBTs *generated from it* for specific consumers. The wide table becomes a materialised, disposable projection rather than the source of truth, and can be rebuilt when a definition changes.

The honest signals that OBT is the right call here: the consumer is a BI tool or an ML feature store with a fixed access pattern; the table is regenerated wholesale rather than updated in place; nobody needs as-was history from it; and there is a modelled layer underneath that it derives from. Missing that last one is what turns OBT from an optimisation into a decision to have no model.

Worth saying explicitly, since this bank verifies what it can: this question is judgement, not demonstration. There is no query I can run that proves the trade-off — it depends on your query pattern, your change rate and who maintains it.

The reasoning

**The definition, said as a sentence.** The grain is what a single row of the table means. Not "the level of detail" — a sentence: *one row per order line*, *one row per subscription per day*, *one row per patient per visit*. If you cannot finish the sentence "one row per ___", the table does not have a declared grain and everything built on it is guesswork.

**Why it comes first.** The grain decides what `COUNT(*)` means, what is safe to `SUM`, and which joins fan out. A join from a table at order grain to one at order-line grain multiplies the order rows, and a `SUM(order_total)` after it is silently inflated — **no error, no warning, just a bigger number that somebody reports.** That silence is the reason grain is declared before columns and not after.

**Where the sentence needs an "and".** "One row per subscription **and** per month" is telling you the key is composite. That is not a complication, it is the design being explicit — and it is exactly the rule a UNIQUE constraint should then enforce, so a re-run cannot write the same subscription-month twice.

**The follow-through most people skip.** State whether the grain is *atomic* — the finest event the source produces — or *aggregated*. Atomic is the default, because an aggregate can always be built from atomic rows and never the other way round. Pre-aggregating on load is a decision to permanently discard the ability to ask a question nobody has asked yet.

The reasoning

**Facts are events with measures.** A sale, a payment, a click, a shipment. They are narrow and very tall — millions of rows, few columns, mostly numbers and foreign keys. They are also **append-mostly**: you record what happened rather than updating what is.

**Dimensions are the context you slice by.** Customer, product, store, date. Wide and comparatively short — many descriptive columns, far fewer rows. They *do* change, which is exactly why slowly changing dimensions are a whole subject of their own.

**The deciding test, applied to a column rather than to a table.** Would you `SUM` it or `AVG` it? Fact. Would you `GROUP BY` it or filter on it? Dimension. That test resolves the awkward cases: an order *status* looks like a measure because it is on the order, but you group by it, so it is dimensional — and if it has no other attributes it is a **degenerate dimension** kept on the fact row rather than given a table of its own.

**The case worth naming unprompted.** A price can be both. `unit_price` on an order line is a fact — you multiply it by quantity and sum it. `list_price` on a product is a dimension attribute — it describes the product today. They are different columns with different lifecycles, and collapsing them into one is how a price change silently re-prices last year.

The reasoning

**Type 1 — overwrite.** The row is updated in place. Cheap, simple, and it silently rewrites the past: last quarter’s report re-run today will disagree with the copy in the board pack. Correct for genuine **corrections** — a misspelled name was always misspelled — and wrong for **changes of fact**, because a customer who moved city was in the old city then.

**Type 2 — a new row per version.** The open row is closed with a `valid_to` and a new one inserted with a fresh surrogate key. History is complete and as-of queries work. The cost is that **every fact must join to the version that was current when the fact happened**, which means joining on the surrogate key captured at load time, not on the business key. Joining on `is_current` instead is the single most common way a Type 2 dimension gets silently ruined — it re-attributes every historical fact to today’s version.

**Type 3 — a previous-value column.** `current_region` and `previous_region` side by side. It answers exactly one question — what changed most recently — in one join, and it cannot answer anything further back. Genuinely useful for a reorganisation where the business wants to report on both structures for a quarter; useless as a general history mechanism.

**How I would actually choose.** Ask whether anyone will ever need to reproduce a past report. If yes, Type 2, and accept the join discipline. If the attribute is descriptive and nobody reports on it historically, Type 1 is not laziness, it is the right cost. Type 3 is a targeted answer to a specific transitional question and I would not reach for it as a default.