Badge scan fact table design that survives five editions of questions

Year one, the exhibitor report says how many scans each stand took. Year two, somebody asks for unique visitors per stand and the answer needs a badge identifier that the table happens to hold. Year three, the operations lead asks which entrance was busiest at 09:30 on the Tuesday and the answer needs an entry point that the table happens not to hold, because the loader dropped it as noise. Now the question costs a reload of five years of raw files, assuming those files still exist.

Badge scan fact table design is mostly the decision about which context to keep, made once, before anybody knows what will be asked. That is an uncomfortable way to design anything, and it has a good answer.

The column list, and why it is mostly keys

The Kimball Group's technique page on transaction fact tables, drawn from the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), gives the definition to hold on to: "a row in a transaction fact table corresponds to a measurement event at a point in space and time."

A badge scan is a clean example. Somebody presented a badge, at a place, at a second, and a device recorded it. The design job is to name every axis of that sentence and give each one a key.

Eight axes cover the questions an exhibition actually gets asked. Date, so the row joins to the calendar. Time of day, held separately from the date so that hour-level analysis does not need date arithmetic in every query. Person, resolved to the contact dimension. Exhibitor, for stand captures. Edition, so the row knows which running of which show it belongs to. Device, because a scanner is a physical object that can be misconfigured. Scan type, distinguishing hall entry from stand capture from session entry. And hall or entry point, which is the one teams most often leave out and most often want back.

Eight foreign keys, one grain statement, no other columns except the vendor scan identifier and the event timestamp.

Does a badge scan fact need a measure?

No, and adding one causes a specific bug.

The instinct is to add a scan_count column holding the value 1, so that reports can sum a measure the way they sum revenue. The Kimball page states the rule the instinct breaks: "the measured numeric facts must be consistent with the transaction grain." At one row per scan there is no quantity to measure. The scan has no amount, no duration and no value. What you have is an occurrence, and occurrences are counted.

The bug arrives when a report joins the scan fact to something else, fans the rows out, and sums the count column. Summing a column of ones after a fan-out gives a plausible larger number with no error. Counting rows after the same fan-out gives the same wrong answer, but a reviewer looking at a count is far more likely to ask what is being counted than a reviewer looking at a sum of a column called scan_count.

There is one measure worth considering later, which is a dwell duration on session entries, and it belongs on a different table because it is stated at a different grain. That table and its capacity denominator are a separate design problem.

What eight keys actually buy you

Count the questions rather than the columns.

With eight dimension keys, the number of distinct ways to group the fact table is two to the power of eight minus the empty grouping, which is 255. Every one of those is a report somebody could ask for, from scans by day, through scans by exhibitor and scan type, to scans by hall and hour and device.

Ship the same table with five keys, dropping time of day, device and hall as unnecessary, and the number of groupings falls to two to the power of five minus one, which is 31. Divide 255 by 31 and the three columns that looked like noise multiply the answerable question space by roughly 8.2.

That arithmetic overstates the practical difference, because plenty of those 255 combinations are meaningless. It understates something else. The three dropped keys are exactly the ones that answer operational questions, and operational questions are the ones that arrive unannounced during show week, when reloading five years of history is not available to you.

Adding a key later is possible when the raw extracts survive. It costs a reload of every edition and a re-publication of any figure that the new key changes, which is a week of work and a conversation about why last year's number moved. Adding a key at design time costs four bytes.

The scan identifier and the degenerate dimension

The vendor's own scan identifier goes on the fact and gets no dimension table.

The Kimball Group calls this a degenerate dimension, describing the case where "a dimension is defined that has no content except for its primary key", and instructing that the key sits in the fact table "with the explicit acknowledgment that there is no associated dimension table". An invoice number on a line item fact is their example. A scan identifier on a scan fact is the same shape.

It earns its four to eight bytes twice. It is the natural key for deduplication, which matters because access control vendors resend files and a load that runs twice on the same export has to be able to recognise the rows it already has. And it is the thread back to the source when somebody disputes a specific capture, which happens every year, usually from an exhibitor who believes a lead was recorded against the wrong stand.

Keep the raw device timestamp on the row as well as the date and time of day keys. The keys are for grouping and the timestamp is for forensics, and the two do different jobs often enough that storing both is worth the eight bytes.

Which questions arrive in year three?

The pattern is consistent enough across shows to plan for.

Year one asks about volume: scans per exhibitor, scans per day, total captures. Every design answers those, which is why a thin table survives its first edition without complaint.

Year two asks about uniqueness. Unique visitors per stand, repeat visits, the ratio between total scans and distinct badges. Those need the person key on every row, and a summary table cannot produce them, which is the argument for keeping the atomic rows.

Year three asks about time and place. Which entrance was busiest and when, how long after doors open the first stand capture happens, whether hall 3 fills before hall 1. Those need time of day and hall, and this is where the thin table starts failing.

Year four asks comparative questions across editions, which need the edition key to be a real dimension with its own attributes.

Year five asks about the things that did not happen. Which exhibitors took no scans at all, which sessions nobody entered. A transaction fact cannot answer those by construction, because rows only exist where activity existed, and the answer needs a coverage table holding every eligible pairing.

Knowing that sequence is the cheapest planning available. It says which keys to include now and which structures to expect to add.

What this costs to store

Eight integer keys at four bytes each is 32 bytes a row.

One edition producing 38,900 scans holds 1,244,800 bytes of keys, so under 1.3 megabytes. Add the scan identifier and the timestamp and the payload roughly doubles. A portfolio of eight shows with five years of history is 40 editions and about 50 megabytes of key data.

Real storage is larger than payload because of row headers, alignment and indexes, and a sensible planning figure is two to three times the payload. The whole thing still fits comfortably inside the space one show's photography takes.

The reason to compute this at all is that the argument against wide fact tables is nearly always made on cost, and at exhibition volumes the cost is not a real constraint. Where the argument has force is query performance on very wide tables with many low value keys, and eight is nowhere near that boundary.

Where this stops

The keys are only as good as the resolution behind them.

The person key is the fragile one. It is produced by matching a badge to a contact record, and where a badge was issued at the door to a walk-in with a name and nothing else, the match will fail or land on the wrong person. A scan fact with a person key on every row implies a confidence in identity that the badge issuing process may not support, and the honest treatment is a placeholder key for unresolved people plus a reported share of rows sitting on it.

Device clocks are the second limit. Time of day analysis assumes the scanners agree on what time it is, and handheld units that lose network during setup do not always. A scan fact records that error at full precision, so an hourly chart will show a spike at the wrong hour rather than a shrug. Checking clock offsets against a known event, such as the door opening, is a five minute job on the first morning and nobody does it.

And the table says what a device recorded, which is not the same as what a person did. A badge scanned twice at a stand because the first attempt did not beep is two rows. A badge lent to a colleague is a scan attributed to the wrong human. These are measurement limits that no schema fixes, and the right response is to publish scan counts and unique badge counts side by side so the gap between them is visible.

Open your scan table this week and list the columns the loader currently drops from the vendor export. For each one, ask whether a question about entrances, hours or devices would need it. Whatever you cannot rule out is worth adding to the load now, while the raw files for the last edition are still where you left them, and worth writing into the data platform conventions before the next show opens.