Understand the grain before you model anything

If I could keep one habit from physics and from analytics engineering, it would be this: name the unit before you write the transformation. In a lab, mixing “per sample” with “per batch” ruins an experiment. In a warehouse, mixing “per event” with “per customer-day” ruins a metric — quietly, for months.

Grain is not a dbt style preference. It is the contract that says what one row means. Until that sentence is boringly precise, joins will look correct and still be wrong.

A row is a claim

Every table claims something like: this row is one conversation, or one account as of the operating day, or one payment promise. If two people on the same team would draw different primary keys, the table does not have a grain. It has a spreadsheet shape.

I write the claim at the top of the model file, in language a product manager could challenge:

One row = one outbound conversation, identified by conversation_id, valid for the timestamp closed_at.

Not: “conversations plus some user fields.” The extra fields are attributes of that grain, or they belong in another table.

Fan-out is how metrics inflate

The classic failure is joining a fact at event grain to a dimension that is actually a fact at a coarser grain, or the reverse. Payment promises attached to a conversation, then joined to every message in the thread: promised amount appears five times. A dashboard “total promised” looks healthy. Operations cannot reconcile it to cash.

I treat unexpected row counts after a join as a stop-the-line signal. Reliability over cleverness: a distinct in the BI tool is not a model. It is an apology.

When I need both grains, I keep two tables. A conversation mart answers “what happened in this dialogue.” An account-day mart answers “who should we call today.” Rolling them into one “wide everything” table feels efficient and becomes a source of silent fan-out.

Time is part of the grain

“Customer” is not a grain. “Customer as of date” is. Slowly changing attributes (segment, assigned agent, product tier) mean that a join without time is a lucky guess.

Point-in-time correctness does not require a textbook Kimball implementation on day one. It does require that you not join today’s dimension version to last month’s events and call it history. If the decision is retrospective (“what did we believe when we promised?”), the model needs validity windows or snapshots. If the decision is only operational today, say so — and do not reuse the table for last quarter’s review.

Late-arriving facts make this worse. An event that lands on Wednesday with a Monday event_at does not belong in Tuesday’s incremental partition just because that is when you ingested it. Arrival time and business time are different grains. Mixing them produces numbers that cannot be reproduced on a second run.

Tests that actually protect grain

I care less about a wall of schema tests than about a few that encode the claim:

• Unique key = the grain you wrote in the comment.
• Not null on that key.
• A row-count relationship to a known upstream (one conversation has N messages; promises should not exceed conversations unless you designed it that way).
• A reconciliation query a human can run: warehouse total versus the operational system for one day.

If those fail, I do not add a new mart. I fix the claim.

Dashboards cannot rescue a fuzzy grain

A filter, a rolling average, or a prettier chart will not repair double-counting. Build for decisions, not dashboards: if the decision is “stop outreach after a promise,” the table must be able to say whether this account, today, has an open promise — one row, one answer.

That is why I would rather delay a dashboard than publish a metric whose grain I cannot defend. Systems > tools: Metabase, Tableau, and a SQL notebook will all look convincing on a wrong join.

How this shows up in practice

In collections-style data, I have found it useful to separate at least:

• Event / message grain — debugging, conversation design, model features.
• Conversation grain — outcome of a dialogue (promise, already paid, refuse).
• Account-day grain — who is in the book and what we should do this morning.

Each layer can be incremental. Each has a different cadence. None of them should pretend to be the others.

The same split appears in scientific work: a formulation is not a lab run; a lab run is not a recommended candidate. Polymer-ml-lab is a small version of that discipline — constraints first, then a unit you can score, then a decision about what deserves the next experiment.

If a model file cannot state its grain, I do not trust its ref(). Everything downstream is commentary on a sentence we never wrote.