Data Modeling Model Design

The reasoning

Start from the questions. Net new, expansion and churn are all *changes*, so they want an event grain — one row per subscription change, with a signed MRR delta. Summing that column over any period gives the net movement, and splitting by event type gives the components.

MRR itself is a *state*, and deriving it from events means summing every delta since the beginning of time for every subscription. A monthly snapshot answers it in one filter, and — more importantly — it has a row for a subscription that did not change this month, which the event fact does not. That is what makes 'MRR by month' and 'accounts with no activity' straightforward.

The snapshot measure is semi-additive: it sums across subscriptions and must not be summed across months. That belongs in the model as an annotation and in the semantic layer as a metric definition, because a raw column named `mrr` will otherwise be summed by someone.

The verification below is the reconciliation you would build as a test: the events sum to 150 of net change, and the March snapshot shows MRR of 150. When those two disagree, one of the two loads is wrong, and having both makes that detectable.

The reasoning

The rule is to model at the finest grain the business process produces, unless there is a specific reason not to. A finer grain can always be rolled up; a coarser one has thrown information away permanently, and the request for product-level revenue arrives about a week after you ship.

The metrics all survive. Baskets are `COUNT(DISTINCT basket_id)`, attach rate is lines per basket, and average basket value is total revenue over distinct baskets — the query below computes all three from the line fact. The only cost is that basket-level questions need a `DISTINCT`, which is a small price for keeping the option.

Basket-level measures that genuinely exist at that grain — a basket-wide discount, delivery fee, or the payment method — go in an order-grain fact or a dimension, not onto the line rows. That is the mixed-grain trap, and this is the design-time moment to avoid it.

The reasons to go coarser are real but specific: extreme volume where line detail is unaffordable, or a privacy constraint that forbids item-level retention. Both are decisions to state out loud, with the cost named, rather than defaults.

The reasoning

Events are additive and cheap: a row per thing that happened, insert-only, growing with activity. Any 'how much' question is a `SUM` and any period is a filter. What they cannot answer cheaply is 'what was it on the 14th', which requires replaying every event from the start of time.

State is a snapshot: a row per entity per period, whether or not anything happened. That last part is what makes it worth its size — questions about inactivity, averages over a population, and point-in-time balances all need the rows where nothing occurred, and an event table simply has none.

The tell for needing both is a business that asks 'how much changed' and 'what is it now' in the same conversation. Build the event fact as the source of truth, derive the snapshot from it, and reconcile: the snapshot at period end should equal the opening balance plus the events in between. That reconciliation is one of the most valuable tests in the warehouse.

A third case is worth naming: when the question is 'how long between steps', neither shape is convenient, and an accumulating snapshot — one row per process instance with a column per milestone, updated in place — answers it directly.

The reasoning

A factless fact records that a combination of dimensions occurred. Attendance is exactly that: student, class, day, and nothing to add up. `COUNT(*)` is the measure, and grouping by any dimension gives attendance counts without a numeric column existing anywhere.

That alone cannot answer the absence question, because a row that does not exist cannot be counted. The second factless fact — coverage — records what *should* have happened: one row per student enrolled in a class. Absence is then the difference between the two, which the query below computes as 2 enrolled and 1 attended in physics, so one absence.

The pattern generalises well beyond attendance: promotions that ran against products that did not sell, eligible customers who did not claim, contracted SLAs against tickets that met them. In every case the coverage fact enumerates the possible, the event fact records the actual, and the interesting number is the gap.

The design decision worth flagging is the grain of the coverage fact. Enrolment per student per class gives absence per class; enrolment per student per class per scheduled day gives absence per session, which is bigger and usually what is actually wanted. Choosing that grain is the real work.

The reasoning

The process has a known, fixed set of milestones, which is the precondition for this shape. One row per order carries `ordered_day`, `picked_day`, `shipped_day` and `delivered_day`, and the row is updated as each happens. Days-to-ship is one subtraction; orders in flight are rows with a NULL delivery date, as the query shows for SO-2.

The alternative — an event fact with one row per status change — is the more natural instinct and makes every duration a self-join between two filtered copies of the table, per pair of stages. It also makes 'how many are stuck' a correlated not-exists rather than a NULL test. Both are writable and neither is pleasant at scale.

The cost is the update. Warehouses handle it, and append-only lakehouse tables do not — so on Iceberg or Delta this becomes a MERGE, and on plain Parquet it becomes a partition rewrite. That is worth raising unprompted, because it is the reason some teams avoid the shape and rebuild it daily from the event fact instead, which is also a legitimate answer.

In practice you often keep both: the event fact as the immutable record of what happened, and the accumulating snapshot derived from it as a serving table. That gives auditability and convenient duration queries, at the cost of a rebuild.

The reasoning

Allocation makes a coarse measure additive at the finer grain, which is what lets a plain `SUM` be correct at every level. The alternative — leaving it at order grain and drilling across — is often better and is not always available, because the business may genuinely want shipping cost attributed per product line.

The arithmetic will not divide evenly, and that is the part to have an answer for. Naive rounding gives three lines of 3.33 totalling 9.99, so the allocated total no longer ties to the order total and the discrepancy grows with volume. Largest remainder assigns the leftover cent to the line with the largest fractional part, so the parts always sum to the whole — the query below shows a difference of exactly 0.

The proportion is a business rule, not a technical one. By line amount, by quantity, by weight, by volume — each gives a different answer and each is defensible for a different business. Name it in the column (`allocated_shipping_by_amount`) so a reader can see which was used, and get it agreed rather than chosen by whoever wrote the load.

Store the allocated figure rather than computing it in the query. Recomputing means every consumer must know the rule, and any change to it silently restates history — the same argument as storing the price charged rather than deriving it from a price list.

The reasoning

Take the dimensions of the fact one at a time and ask whether a `SUM` over that dimension means anything. Revenue across customers, products and days: yes on all three, so it is additive and needs no rule. Account balance across accounts: yes. Across days: no — and that single 'no' makes it semi-additive.

Semi-additive measures need the replacement operation stated at design time: last value, first value, or average over the period. Where it lives matters — a column note is a start, a semantic-layer metric definition is the real answer, because it is the only place that every consumer inherits.

Non-additive measures should not be in the fact at all. A margin percentage cannot be summed *or* averaged correctly above the grain it was computed at, so store `margin` and `revenue` and derive the ratio after aggregation. The same applies to unit price, conversion rate and any per-something figure.

The output of the audit is worth keeping: a table of measure, additivity class, and the rule if it has one. It becomes the specification for the semantic layer, and it is the artefact that stops the next person adding `avg_order_value` as a stored column.

The reasoning

Two ids for one person is an identity-resolution problem, and the temptation is to merge the records on load. Do that and you lose the ability to trace a warehouse row back to the system it came from, and — more painfully — the ability to undo a match that turns out to be two different people who shared an email.

Keeping one dimension row per source record and adding a `master_id` gives you both. Facts continue to reference the record they actually came from, so lineage is exact; reports group by `master_id`, so a person is counted once. The query shows M-1 with two orders and 160 of revenue across two source systems.

The matching itself is a separate concern with its own machinery — deterministic rules on email or tax id, probabilistic scoring, or a full MDM tool — and it belongs outside the model. What the model owes it is a stable place to record the outcome and the confidence, so that a match can be reviewed and reversed.

The failure to avoid is letting one system win. Picking the CRM id as the key means the billing system's records are either dropped or forced into an id space they do not belong to, and every fact from billing then depends on a matching step having succeeded.

The reasoning

The grain sentence translates directly into a candidate key. 'One row per line on an order' means (order_id, line_no) must be unique; 'one row per account per day' means (account_sk, day_key). Writing that down turns the grain from documentation into something a test can assert, which is the difference between believing the grain holds and knowing it.

The practical payoff is idempotent loading. With a key you can MERGE on it, so a re-run replaces rather than appends and a retry after a partial failure is safe. Without one, re-running the load is how a fact table doubles, which is the single most common cause of an overnight revenue jump.

A surrogate key on the fact — a single generated integer — is a separate and lesser thing. It makes individual rows addressable, which is useful for corrections and for referencing a specific row in a support conversation, and it enforces nothing about the grain because it is unique by construction. Have both if you like, but the composite key is the one doing the work.

Where a natural composite key genuinely does not exist — a clickstream with no event id and no distinguishing attributes — that is a finding rather than a licence to skip the key. Either the source should supply an event id, or you need a deterministic hash of the payload, and either way the ambiguity should be raised rather than absorbed.

The reasoning

The test for a dimension table is whether it would have a second column. Before, `dim_order` would have held only `order_id`, so the join would return no information and the id belonged on the fact. Now there are two attributes that reports want to filter and group by, and they have to live somewhere.

Putting them on the fact instead is the tempting shortcut and it repeats them on every line of the order — which is the mixed-grain trap wearing a different hat. Channel is a property of the order, not of the line, so denormalising it downward is only safe if you never sum it, and the flag is exactly the kind of column someone eventually counts.

The migration is small and worth describing precisely: create the dimension keyed on the natural order number, populate it from the existing distinct values, add `order_sk` to the fact, and keep `order_id` on the dimension as the natural key so lineage back to the source survives. Existing queries counting `COUNT(DISTINCT order_id)` keep working until they are moved.

If the attributes were high in number and low in cardinality — half a dozen flags — the alternative is a junk dimension rather than an order dimension. The deciding question is whether the attributes describe the *order* or merely accompany it.

The reasoning

Flattening a hierarchy into `level_1 ... level_5` columns works when the depth is fixed and known, which organisational and account hierarchies rarely are. Add a level and every table and every query changes; have a branch that stops at depth two and the lower columns are NULL, which breaks grouping.

The closure table sidesteps depth entirely. For every node, store a row for each node beneath it, plus a row for itself at depth 0. Joining the fact on `descendant_sk` and grouping by `ancestor_sk` then gives the rolled-up total for every node at once, which is exactly what the query below returns.

The costs are worth naming. The bridge is O(nodes × average depth) rows, which is fine for thousands of accounts and needs thought at millions. It must be rebuilt when the hierarchy changes — usually a full rebuild, since a single re-parenting affects every pair below it. And the `depth` column matters: without filtering on it you cannot ask 'direct children only', and joining without care double-counts if you forget that self-rows exist.

The alternative for engines that support it is a recursive CTE against a simple parent-child table, which needs no bridge and re-walks the tree on every query. That is often the right answer for a small hierarchy queried occasionally, and the wrong one for a dashboard hitting it constantly.

The reasoning

The default is a star, and the usual argument for snowflaking — saving storage on a repeated string — has been dead since columnar compression. So the case has to be made on something else, and here it is: the hierarchy is shared and it changes.

Maintained in three denormalised dimensions, a re-organisation is three loads that must agree. They will not, eventually, and the failure is a report where product revenue by division does not match supplier spend by division for reasons nobody can find. One hierarchy table makes that class of disagreement impossible.

The cost is joins and legibility, and it is real: BI tools generate better SQL against a star, and query paths get longer. The resolution is to keep the normalised hierarchy as the maintained object and publish a flattened view over it for consumers — one place to maintain, a star-shaped thing to query. Most mature warehouses do exactly this.

The remaining question is history. If reports need 'revenue by the division it was in at the time', the hierarchy needs Type 2 versioning, and that decision is independent of the star/snowflake one — it just becomes much easier to implement once when the hierarchy is a table of its own.

The reasoning

The question that decides it is 'would anyone want to see last year's numbers by the old value?'. If yes, the attribute needs Type 2 and an as-of join. If no — a typo fix, a formatting change, an internal note — Type 1 is correct and cheaper, and pretending otherwise adds rows nobody will ever query.

The second question is change rate, and it is what stops Type 2 being the universal answer. An attribute that changes monthly for every customer multiplies the dimension by twelve a year, and one that changes daily makes it unusable. Those belong in a Type 4 mini-dimension keyed from the fact, which captures the value at transaction time without versioning the customer.

Mixing types within one dimension is normal and worth saying explicitly, because candidates often assume a dimension has one type. A single customer dimension can hold a Type 0 signup date, Type 1 corrections to a name, Type 2 on region, and a Type 4 key to a behavioural band — each chosen for that attribute's question.

The last consideration is who is asking. Finance and regulatory reporting usually need as-was and will say so; product analytics often prefers as-is. Where both are needed on the same attribute, Type 6 gives you both views from one row set — a Type 2 history plus a current column updated in place.

The reasoning

**Which business process.** Not which tables the source has — which real-world activity is being measured. Orders placed, shipments despatched, tickets closed. Getting this wrong produces a model shaped like the source system, which is the root cause of the mixed-grain and two-processes-in-one-table defects.

**What does one row mean.** The grain, as a sentence. This is the decision that cannot be changed cheaply later, and it determines whether every subsequent column belongs. Ask for the finest grain the process produces, and require a reason for anything coarser.

**Which measures are additive.** Per measure, per dimension. Anything semi-additive needs a stated time rule; anything non-additive gets split into numerator and denominator. This is a five-minute audit that prevents a whole class of reporting error.

**Which attributes need history.** Per attribute: would anyone ever group last year's numbers by the old value? That decides Type 1 against Type 2, and the change rate decides whether Type 2 or a mini-dimension.

Then the questions that shape the physical model rather than the logical one: who queries this and with what tool, what the largest expected volume is, what latency the business needs, and what this must reconcile against. That last one is the most under-asked — knowing the warehouse must tie to the finance ledger to the penny changes the design of the fact and forces the currency and allocation questions early rather than after launch.

The reasoning

The failure this prevents is two teams shipping two definitions of customer, product or date, after which no report can combine their facts and reconciling them is a project rather than a fix. It costs nothing to check first and a great deal to undo later.

The bus matrix is the artefact: business processes as rows, conformed dimensions as columns, a tick where a process uses a dimension. It is a planning tool rather than a technical one, and its value is that it makes the overlap obvious to people who do not read DDL — including the stakeholders who have to agree that 'active customer' means one thing.

Conforming does not mean identical. A dimension can conform at a coarser grain — a sales fact at product level and a forecast fact at category level can share a conformed product dimension if the category attributes agree. That is a *conformed rollup*, and being able to name it is what lets you integrate processes that genuinely operate at different levels.

What you owe the existing dimensions is a check that they cover your needs, and a negotiation where they do not. Adding an attribute to a shared dimension affects everyone using it, so it is a change with an owner and a review — which is precisely the discipline that keeps it conformed rather than quietly forked.

The reasoning

Ad-hoc analysis wants a star: conformed dimensions, declared grain, atomic facts, and the freedom to ask questions nobody anticipated. A minute-refresh dashboard wants the opposite — a small number of pre-shaped rows it can read without joining, refreshed incrementally.

Trying to satisfy both with one table produces something that serves neither: too wide and pre-aggregated for analysts to answer new questions, too general for the dashboard to hit its latency. The resolution is layering — one modelled core, and derived serving objects per consumer, each rebuilt from the core rather than loaded separately.

The rule that keeps it honest is that serving objects are *derived and disposable*. If the dashboard aggregate is built by its own pipeline reading the source, you have two definitions of the metric and they will diverge. If it is a materialisation of a query over the core, it cannot disagree, and it can be dropped and rebuilt when a definition changes.

The one thing that genuinely constrains the core design here is latency. If the dashboard needs minute-level freshness, the core fact must be incrementally loadable — which means a reliable event-time watermark and an idempotent merge key — and that is a design decision to take up front rather than retrofit.

The reasoning

Starting with what does not change is the point. Grain, conformed dimensions, additivity and history requirements are properties of the business, not of the volume, and a model that changes shape at scale usually had the wrong grain to begin with.

Partitioning is the first physical decision and it follows the query pattern: partition by the date column that filters are written against — usually event date, not load date — so that a query for one day reads one partition. Clustering or sorting within the partition then targets the next most common filter, typically a high-cardinality key such as customer or product.

At 100 million a day the loading strategy stops being free. Dimensions can no longer be truncate-and-reload, because that reassigns surrogate keys; facts must be incremental with an idempotent merge key so a re-run is safe; and late-arriving data needs a defined window rather than an unbounded backfill. All of these are the same decisions at the smaller volume, where getting them wrong is merely survivable.

The last differences are about what you can afford to ask. A Type 2 dimension whose attribute changes weekly is fine at 10,000 customers and a problem at 100 million, pushing you to a mini-dimension. And pre-aggregates go from an optimisation to a requirement, with the caveat that distinct counts cannot live in them.

The reasoning

The deciding question is whether a return has attributes a sale does not. It usually does — a return date distinct from the sale date, a reason code, a condition, an approver — and none of those have anywhere to live on a sales row. That alone points to a separate fact.

Negative rows in the sales fact are appealing because net revenue becomes a plain `SUM` with no join. The cost is that gross revenue becomes hard: every query wanting sales-before-returns needs a filter that people forget, unit counts go negative, and 'orders' counted from the fact now includes return rows. The convenience is bought with ambiguity in every other metric.

Two conformed facts — sales and returns, sharing product, customer and date dimensions — let each carry its own attributes, and combining them is drill-across: aggregate each to a common grain and subtract. That gives gross, returns and net as three explicit metrics rather than one implicit one.

The case for negative rows is genuinely strong in one situation: a ledger-style fact where the business thinks in postings and corrections, and where every row is already signed. There, a return is just another posting, and forcing it into a second table would break the model's own logic.

The reasoning

Calendar arithmetic is the easy part and the part functions handle. What they cannot tell you is whether 3 March is a working day in the region concerned, which fiscal quarter your company puts it in, whether it fell in the retail week that anchors last year's comparison, or whether it was a promotional period. Those are business facts and they have to be stored.

Consistency is the second reason and often the bigger one. If fiscal-year logic lives in a `CASE` expression, it lives in a hundred `CASE` expressions, and they will not all agree — particularly around year boundaries. A dimension makes it one column, defined once, and a correction fixes every report at the same time.

It also enables things that are awkward otherwise: role-playing the same calendar as ordered, shipped and delivered dates; joining a fact to a full date range so that days with no activity still appear in a time series; and pre-computed flags like `is_last_day_of_month` that turn a common filter into an equality.

Practical construction notes worth having: generate it well beyond the current date so future-dated facts join; make the key a readable integer such as 20260301 rather than a surrogate sequence, since it is the one dimension where a smart key is conventional and harmless; and add a separate time-of-day dimension rather than putting 86,400 rows per day into this one.

The reasoning

The reason three definitions exist is usually that each team needed a number quickly and the model gave them no shared place to put the answer. The fix is not to pick a winner but to create that place — a semantic layer, metrics store or a governed dbt metric — where `active_customer` is written once, reviewed, and consumed by every tool.

What the dimensional model owes it is the atomic detail. A definition of 'active' as 'ordered in the last 90 days' needs order-level facts with dates; if the warehouse only stores monthly aggregates, the definition cannot be expressed and each team will re-derive it from whatever they have. Modelling at the finest defensible grain is what keeps definitions changeable.

Materialising the metric as a table is where it goes wrong. A `dim_active_customer` freezes one definition into the model, and when marketing needs a 30-day window a second table appears. Keep metrics as definitions over the model and materialise them only as disposable serving objects, rebuilt from the definition.

The governance half matters as much as the technical half: the definition needs an owner, a review process for changes, and a visible history — because a metric that silently changes meaning is worse than three that openly disagree. This is where data contracts and the semantic layer meet.

The reasoning

**The grain is the entry, not the transaction.** A withdrawal is not one row — it is a credit against the customer account and a debit against the settlement account, tied together by a transaction id. Choosing the transaction as the grain forces you to invent `from_account` and `to_account` columns, which works until a transaction has three legs (amount, fee, tax) and then the model has to change.

**Double-entry is the invariant, and the point is that it is checkable.** Every `SUM(amount)` grouped by transaction id must be zero. That is a single assertion covering an entire class of bugs — a fee posted without a counterpart, a partial write, a reversal that reversed one leg. A model where entries are signed and grouped by transaction makes the check trivial; a model with a single `amount` and a direction flag makes it a case statement.

**Entries are immutable and corrections are new rows.** You never update a ledger entry. A mistake is fixed by posting a reversal and then the correct entry, so the history shows what happened and what was done about it. This is not fussiness — it is what makes the ledger auditable, and any model that allows an `UPDATE` on an entry has lost the property the ledger exists for.

**Balance is derived, and then cached deliberately.** The true balance is `SUM(amount)` over an account's entries. Computing that over years of history for every query does not scale, so you add a periodic balance snapshot — one row per account per day — and read the snapshot plus the entries since. The snapshot is an optimisation that must be reproducible from the entries, and saying so is what distinguishes it from storing a mutable balance column, which is the design that eventually disagrees with its own history.

**Around that core:** `dim_account` with its type and owner, `dim_asset` or currency (with the amount stored in minor units as an integer, never a float), and `dim_date`. The transaction id is a degenerate dimension on the fact — it groups the legs and there is nothing else to say about it.

The reasoning

**The mechanism.** Modern warehouses apply a masking policy to a column: the same `SELECT` returns the real value to one role and a redacted, hashed or tokenised value to another. Snowflake calls it a masking policy, BigQuery does it with policy tags and column-level access, Redshift with dynamic data masking. The important property is that **the policy is attached to the column, not to a view** — so there is one table, one query, and no risk of someone finding the unmasked copy.

**Why views are the weaker answer.** Building `customer_masked` alongside `customer` means two objects to keep in sync, two sets of grants, and a permanent risk that a new pipeline reads the base table. The masked view also drifts: someone adds a column to the base table and it is not in the view, or worse, it is.

**Where modelling comes in.** Masking is cheap when PII is concentrated and expensive when it is smeared across the schema. **Keep identifying attributes in one dimension, or in a separate vault table keyed by the surrogate**, so that most joins and most marts never touch a sensitive column at all. Then the analyst working on order volumes is not querying a table with masked columns; they are querying a table that has none.

**Choose the masking function for the use case, and know what each one costs.** Redaction (`***`) is safest and breaks joins and grouping. Deterministic tokenisation preserves joinability and grouping — the same email always maps to the same token — at the cost of being vulnerable to a dictionary attack on a low-cardinality column. Hashing with a salt is one-way, which is right for a value nobody needs to reverse. Partial masking (last four digits) is what support teams actually need.

**And say the limit out loud:** masking controls the read path. Deletion under GDPR or CCPA is a different problem — a masked row is still a row, and "right to erasure" means the data has to go, which is why lakehouse formats with delete support matter for compliance in a way masking does not address.