Data Modeling

Data Modeling

A model is a claim about shape: one row per this, keyed by that, meaning exactly this. Most modelling bugs are a grain nobody wrote down, discovered later as a number that is quietly double what it should be.

Standing reference: references/data-quality-checklist.md.

Process

1. Declare the grain in the first line of the model. "One row per subscription per billing period." Every column decision follows from it, and the uniqueness test that guards it comes free once it is written.

2. Search for the metric before defining it. Grep the transformation project, then the dashboards. If "active user" already exists, use it or replace it. Forking it produces two numbers that both cite the warehouse and disagree in a meeting.

3. Keep the layers honest.

   • Staging: rename, cast, deduplicate. One staging model per source table. No joins, no business logic, because logic here leaks into every consumer that follows.
   • Intermediate: the joins and the reshaping, where fan-out is possible and therefore tested.
   • Mart: what people query, named in their language, documented.

4. Choose the history behaviour deliberately. Type 1 overwrites, and silently rewrites every historical report that reads the dimension by date. Type 2 keeps a row per version with valid-from and valid-to. Pick one, write which, and be able to say why.

5. Test the key before anything else. Uniqueness and not-null on the declared grain. Then referential integrity on every join key, accepted values on every enum, and a not-null on the columns people filter by.

6. Check fan-out at every join. Row count before and after, with the expected relationship stated. A join that silently multiplies rows is the most common way a correct-looking model doubles revenue.

7. Reconcile against something already believed. The source table total, the previous dashboard, the billing system. Quote both numbers. An unreconciled model is a hypothesis with a schedule.

8. Document the columns that will be misread. Not all of them: the ones whose name promises something the definition does not deliver.

Metric definitions

One definition, one place, and it includes the denominator and the exclusions. A metric definition is a contract with everyone who quotes the number, so:

• Write the numerator, the denominator, the time grain, the time zone, and what is excluded: internal accounts, bots, refunds, test records.
• Changing a published definition is a breaking change. Version it, announce it, and give the old number a name rather than silently rewriting history.
• If two teams need different definitions, they need two names. Never one name and a footnote.

Output

## Model: <name>

- **Grain:** one row per <X>
- **Key:** <cols> — uniqueness test result quoted
- **Layer:** staging | intermediate | mart · depends on <models>
- **Metrics defined:** <name> = <numerator> / <denominator>, excluding <list>
- **History:** type 1 | type 2 on <cols>, valid-from/valid-to <cols>
- **Fan-out check:** <n> rows in, <n> rows out, expected <relationship>
- **Reconciled against:** <source> — <both totals>
- **Tests:** <list>, output quoted

Verification

• The uniqueness test on the declared grain ran and passed; quote it.
• Row count and one summed measure reconcile with the source; both numbers quoted.
• Every join key has a referential integrity test.
• The whole project builds, not only the changed model.
• No metric defined here exists elsewhere under a different definition. Say where you looked.

Red flags