How to declare the event data warehouse grain before you build tables

Six people, a whiteboard, and somebody has drawn a single table called event_activity with a type column. Registrations go in it. Badge scans go in it. Meeting bookings go in it. One table, one loader, one place for everybody to look.

It is the most common opening proposal in an event data warehouse grain discussion, and it collapses about two months later when somebody asks for average scans per attendee and discovers that the denominator and the numerator are rows in the same table distinguished only by a string.

Adding columns does nothing for it. The fix is a decision made earlier, written in one sentence and then enforced.

What does declaring the grain actually decide?

The Kimball Group's published summary of dimensional modelling techniques, drawn from the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), sets out four design steps in a fixed order: select the business process, declare the grain, identify the dimensions, identify the facts. Grain is step two, ahead of any conversation about columns.

Their definition is worth reading slowly. The grain "establishes exactly what a single fact table row represents", and the same page calls the grain declaration "a binding contract on the design". Two sentences later comes the instruction that most event warehouses break: "Different grains must not be mixed in the same fact table."

So the grain is a sentence about rows, written in the language the show team already uses. One row per completed registration. One row per badge scan event. One row per confirmed meeting between two parties.

Once you have that sentence, three things stop being arguments. Whether an attribute belongs on the fact or on a dimension is decided by whether it varies at the grain. Whether a measure can be summed is decided by whether it is stated at the grain. And whether a new source can be loaded into an existing table is decided by whether its rows mean the same thing.

Skip the sentence and each of those becomes a matter of opinion, settled repeatedly, differently, by whoever is closest to the ticket.

One edition, three processes, three grain statements

Take a single edition of a mid-sized B2B show. The registration platform produces 12,400 completed registrations. The access control and lead retrieval systems together produce 38,900 badge scans across the three days, counting hall entry and exhibitor stand captures. The matchmaking tool produces 2,100 confirmed meetings.

Three numbers, three processes, and they are not commensurable. Divide 38,900 by 12,400 and you get 3.1 scans per registration, which is a real and useful ratio precisely because the two figures come from tables whose rows mean different things.

Now suppose you had loaded all three into one table. The row count is 12,400 plus 38,900 plus 2,100, which is 53,400. That number describes nothing. No budget line is denominated in it and no operational decision turns on it, and every query against the table has to open with a filter on the type column before it can say anything true. Forget the filter once, in one dashboard tile, and you publish 53,400 as a headline.

The meetings table shows why the grain sentence has to be specific rather than merely present. "One row per meeting" gives 2,100 rows. "One row per meeting participant" gives 4,200, because every meeting has two sides. Both are defensible designs. They answer different questions, they need different dimension keys, and a report that assumes one while the table implements the other is wrong by a factor of exactly two, silently, forever.

I would take the participant grain for matchmaking, because the questions organisers actually ask are about people and exhibitors rather than about meeting objects, and because the meeting-level count is recoverable from the participant-level table by a distinct count while the reverse is impossible. That is the general test, and it is the one thing about grain worth memorising: prefer the grain from which the others can be derived.

Why does the single activity table keep getting proposed?

Because it solves a real problem, badly.

The problem is that an event business gets its data from four or five vendor systems with different export formats, different identifiers and different release schedules, and somebody has to make them land somewhere. A single wide table with a type column is the shortest path from five files to one place.

The cost arrives later, and it arrives as ambiguity rather than as an error message. Columns that apply to registrations sit null on 41,000 rows. Columns that apply to scans sit null on 14,500. Anyone writing a query has to know which combinations of type and column are legal, and that knowledge lives in people rather than in the schema.

There is a version of the instinct that is correct, and it belongs one layer earlier. Land the raw vendor extracts as they arrive, untouched, in a staging area where the only rule is fidelity to the source. Then model the star schema from that. The single table is a fine landing zone and a poor reporting model, and the confusion between those two jobs is what puts it on the whiteboard in the first place.

The Kimball Group's technique page on transaction fact tables gives the model to aim at instead: a row "corresponds to a measurement event at a point in space and time". A badge scan is exactly that. So is a registration. So is a meeting. Three measurement events, three tables.

Write the grain statement where people will read it

A grain declared in a modelling workshop and recorded nowhere lasts about one staff change.

Put it in three places. In the table comment in the database, so anyone inspecting the schema sees it. In the model file in version control, next to the transformation that builds the table, so it appears in the diff when somebody changes the transformation. And in the data dictionary the reporting team actually opens, which in most event businesses is a spreadsheet, and that is fine.

Write it as a sentence with a verb, in the show team's vocabulary. "One row per badge scan event, including repeat scans of the same badge at the same stand." The clause after the comma is doing the real work, because it settles the argument that would otherwise surface in month four when an exhibitor asks why their lead count is higher than their unique visitor count.

Add the count of rows the last edition produced. A grain statement with a number beside it lets the next person sanity check a load in ten seconds: if the scan fact for this edition holds 4,100 rows against a stated 38,900 last time, something broke, and they will notice before the report goes out rather than after.

The same discipline applies to a lead retrieval capture table, where the row is one capture action and the exhibitor-facing consequences of getting it wrong are immediate.

What the grain does to everything downstream

Two decisions follow from the grain and they are worth naming so nobody thinks they are separate projects.

The first is whether you keep rows at the level the business process emits them. A registration process emits one row per registration, and you can store that or you can store a daily count by ticket type. The Kimball grain page is direct about the trade, encouraging designers "to start by focusing on atomic-grained data because it withstands the assault of unpredictable user queries", and the arithmetic on what you give up by summarising early is worth doing on your own scan file.

The second is what happens when two facts at different grains meet in one query. Joining a registration fact at person grain to a scan fact at scan grain multiplies rows and inflates every sum on the registration side, which is the mechanism behind two reports that disagree while both being computed correctly.

Neither is avoidable by being careful at query time. Both are decided by the grain statement, months earlier, by whoever was holding the marker.

Where this stops

Declaring the grain does not tell you which processes to model first, and getting that order wrong is expensive in a different way. A team that models registration, badge scan, meeting, contract and sponsorship in parallel builds five versions of the company dimension and discovers the problem at integration. Build order comes from a matrix of processes against dimensions, and that matrix belongs on the wall before the first grain statement is written.

The grain also cannot fix a source system that does not emit the event you want to count. If your access control vendor returns a daily aggregate per hall and refuses to expose the underlying scan records, no grain statement recovers the row you never received. What you can do is write the grain you want, note that the current source cannot supply it, and make that gap a line in the vendor conversation at renewal rather than a footnote in a report.

And a grain statement is only as stable as the process it describes. Move from a single badge per person to a reprint-on-demand model, and the meaning of a scan row changes without anyone editing the schema. That is a reason to date the grain statement and treat a change to it as a change to the data platform, with the same review any other breaking change gets.

Take the last edition you closed and write one sentence for each fact table you already have, describing what a row means, and then run a count of rows against each. Where you cannot write the sentence in under a minute, or where the count surprises you, you have found the table that will produce the next disputed number.