Skip to content

khuynh22/agent-dev-team / skills/data-modeling

data-modeling

Verified

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

ThoughtReality
"The grain is obvious"Then it costs one line to write, and it prevents the duplicate nobody catches.
"I will add my own version of the metric"Two dashboards now disagree and both cite the warehouse.
"The join looked right in the preview"Twenty rows preview fine and fan out at scale. Test the key.
"It matches roughly"Roughly means it does not match. Find the difference first.
"I will just overwrite the dimension"Every historical report that reads it by date has now changed.
"Logic in the dashboard ships faster"And is invisible to everyone who queries the model instead.
"Tests slow the build"Less than explaining a wrong number to whoever acted on it.
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 197kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall