Atomic grain versus aggregates when storing badge scans and registrations

The question that starts the atomic grain versus aggregates argument is usually about cost, and it usually arrives from someone sensible. We have 38,900 scans from one edition and eight shows in the portfolio. Do we really need to keep every row, or can we store counts by exhibitor and day and move on?

The honest answer is that the counts are cheaper by a factor of thirty-five and cost you about six questions you have not been asked yet.

What does an aggregate actually throw away?

Take the scan file from one edition. 38,900 rows, each carrying a badge identifier, an exhibitor identifier, a device, a scan type and a timestamp to the second.

Now collapse it to one row per exhibitor per day. On this edition, 380 exhibiting companies had at least one scanner in operation, and across three show days that is 1,140 possible combinations, of which 1,096 have at least one scan. Divide 38,900 by 1,096 and the summary table is 35.5 times smaller.

Here is what left the building with those rows.

Repeat visits. The atomic table can tell you that 2,140 badges appear more than once against the same stand, because it holds the badge identifier on every row. The summary table holds a count, and a count of 84 scans at stand H41 on Wednesday is consistent with 84 distinct visitors and with 42 people who came back, and there is no arithmetic that separates them afterwards.

First scan hour. The atomic table holds a timestamp, so you can say that the median first scan of the day happens at 10:40 and that stands in hall 2 see their first capture 50 minutes later than stands in hall 1. The daily summary knows only the day.

Unique visitors per stand. Distinct badge count is a query on the atomic table and an unanswerable question on the summary, which is awkward because it is the number exhibitors ask for most.

Cross-stand behaviour. Whether the 84 people at H41 also visited the three stands either side of it needs badge identifiers on both sets of rows.

The pattern across all four is the same. Aggregation is a one-way projection, and the questions it removes are disproportionately the interesting ones, because interesting questions tend to be about individual people and individual stands.

What the atomic table costs to keep

The storage argument deserves actual numbers rather than an instinct about scale.

A badge scan fact row at atomic grain needs foreign keys to date, time of day, person, exhibitor, edition, device, scan type and hall. Eight integer keys at four bytes each is 32 bytes. Add an eight-byte event timestamp and an eight-byte scan identifier and the payload is 48 bytes per row.

At 38,900 scans, one edition is 1,867,200 bytes, call it 1.9 megabytes. A portfolio of eight shows with five years of history is 40 editions, so 1,556,000 rows and about 75 megabytes of payload.

Real databases add per-row overhead and indexes, so double or triple that and the whole five-year atomic scan history for eight shows sits somewhere around 150 to 250 megabytes. That is smaller than the deck the post-show pack ships in.

Registrations are smaller again. 12,400 completed registrations an edition, 40 editions, is under 500,000 rows.

I have watched teams spend a fortnight designing an aggregation strategy to avoid a table that would have fitted on a memory stick in 2009. The reflex comes from experience with clickstream and telemetry data, where atomic grain genuinely does mean billions of rows a month. Event data has a different shape. One edition produces four days of activity and then eleven months of silence, and the total volume of a whole portfolio's history is a rounding error against a single day of web analytics.

When the aggregate is the right answer

There are cases, and being clear about them is the only way the argument stops being religious.

The first is where the source will not give you the detail. Some access control vendors return hall entry counts by hour and hold the underlying records themselves. You model what you receive, note the grain you wanted, and take it to the renewal conversation.

The second is a genuine performance layer. The Kimball Group's technique page on aggregate fact tables describes these as numeric rollups of atomic fact data, built solely to accelerate query performance, carrying foreign keys to shrunken conformed dimensions and measures summed up from the more granular table. The design requirement is that they get selected automatically, and the page states the standard directly: a properly designed set of aggregates "should behave like database indexes, which accelerate query performance but are not encountered directly by the BI applications or business users."

That is a precise standard and most event reporting stacks fail it. If your analysts have to know that scan_daily exists and choose it by hand, you have built a second reporting table that happens to be smaller, and within two editions somebody will compute a figure from the summary that contradicts the same figure computed from the detail.

The third case is the published post-show number, which is a different thing again. A frozen table of the figures you actually published, per edition, is worth keeping precisely because it is an aggregate and precisely because it should never change.

Why does the summary always drift from the detail?

Because the filter set gets baked in and then forgotten.

Suppose the summary is built with staff badges excluded, which is correct for attendance reporting. Six months later somebody uses it to compute scans per exhibitor and gets a number 4 per cent below the same computation on the atomic table, because stand staff scanning each other's badges during setup were dropped by a rule nobody remembered was there.

The atomic table has the same problem in a much better form. The filter lives in the query, visible, in one line, next to the person who is going to defend the number. Where two teams reach different answers, the disagreement shows up as two readable predicates that can be diffed in a minute. That is why a grain mismatch between two facts is easier to diagnose than a mismatch between a fact and a summary built on a separate afternoon.

Keeping the atomic rows does not prevent drift on its own. It makes drift explainable, which in practice is what ends the meeting.

The argument from the design literature

Kimball and Ross put this decision in the grain step of the four-step design process, and their guidance is unambiguous. The Kimball Group's published technique summary, drawn from the third edition of The Data Warehouse Toolkit (Wiley, 2013), defines atomic grain as "the lowest level at which data is captured by a given business process" and then encourages designers "to start by focusing on atomic-grained data because it withstands the assault of unpredictable user queries."

The word doing the work there is unpredictable. The case for atomic grain rests on an admission that nobody can anticipate the questions, and on the asymmetry of being wrong: an aggregate you did not build can be built in an afternoon, and detail you did not keep is gone.

Event businesses feel that asymmetry harder than most, because the questions change with the commercial model. A show that adds a hosted buyer programme in 2027 will want to know how hosted buyers behaved in 2025 and 2026 as a baseline. That analysis is a filter on an atomic table and an impossibility on a daily summary. Nobody in 2025 knew to build the aggregate, because the programme did not exist. The same logic governs the row you keep for a lead retrieval capture, where exhibitor-facing counts depend on rows nobody thought to preserve.

The limit

Atomic grain is a storage decision and it does not make the rows true.

A scan fact holding 38,900 rows at capture grain still inherits every defect in how those captures were produced. Devices with clocks set to the wrong time zone put scans in the wrong hour, and the atomic table records that error with more precision than the daily summary would have. Stands that share a scanner attribute captures to one exhibitor. A badge lent to a colleague creates a repeat visit that never happened.

Detail also raises the stakes on access. A daily count by exhibitor is commercially dull. A table of which named individual visited which stand and when is personal data with a retention obligation attached, and the right answer is a retention rule and a role that cannot read the person key, both decided as part of the data platform before the first analyst query lands.

There is one more limit worth stating plainly. Atomic rows are only atomic relative to what the source emitted. If your registration platform overwrites a registration record when someone upgrades their ticket, your atomic registration fact holds the final state and no history, and no amount of grain discipline in the warehouse recovers what the source discarded. Writing the grain statement, as the second step of the design process, is what surfaces that gap early enough to ask the vendor about it.

Run one query on your last edition: count rows in your scan table, then count the distinct combinations of exhibitor and day. Divide the first by the second. If the ratio is above about ten, you are holding roughly ten times more information than any daily summary can express, and the question of whether to keep it answers itself.