Placeholder dimension keys keep facts loadable when the company is unknown

A badge scan comes off a lead retrieval device with a company string that matches nothing in your company dimension. The exhibitor typed it themselves at the stand, or the attendee did, or the registration platform passed through a free-text field nobody validates. The scan is real. The visit happened. The fact row has a measurement, a timestamp and a booth, and no dimension key.

Placeholder dimension keys are how you load it anyway. Create a stub row in the company dimension carrying the unresolved natural key, point the fact at that stub's surrogate key, and overwrite the stub with real attributes when the real record turns up. The alternative approaches, dropping the fact or holding it in quarantine, both trade a measurement for tidiness, and a measurement is harder to get back than a tidy table.

The scan with nowhere to point

The situation has a name and a prescribed treatment, and both are older than most event data teams.

The Kimball Group's technique for late arriving dimensions, drawn from the third edition of The Data Warehouse Toolkit in 2013, opens with the case exactly: "Sometimes the facts from an operational business process arrive minutes, hours, days, or weeks before the associated dimension context." An exhibition compresses that into a single week. Scans arrive live on the floor. The exhibitor's confirmed company record arrives when contracting finishes reconciling, which can be a fortnight later.

Bob Becker, writing for the Kimball Group in October 2007, gives the machinery a place in the architecture. His revisited list of the 34 ETL subsystems names a Late Arriving Data Handler as one of the thirteen delivery subsystems, sitting alongside the surrogate key pipeline and the slowly changing dimension manager. It is a named component with a job, and event pipelines routinely ship without one because the problem looks like a data quality nuisance instead of a design gap.

Why not send unknown companies to a single unknown row?

Most warehouses already have an unknown member: one row, surrogate key of minus one or zero, company name recorded as "Unknown". Every unresolvable fact points at it. It keeps the join working and the counts consistent, and for some dimensions it is the right answer.

For company on an event fact table it is the wrong answer, and the reason is that it destroys information you were handed for free.

Suppose 340 distinct company strings in one edition match nothing in the dimension, and between them they account for 1,870 badge scans out of 38,900, which is 4.8 per cent of the edition. Send all 1,870 to a single unknown row and you have one bucket of 1,870 scans that can never be split again. The strings are gone. Nobody can tell whether those scans belonged to 340 small firms or to four large ones spelled 85 different ways. Any later attempt to fix it has to go back to the raw file, because the fact table no longer distinguishes them.

Create 340 stubs instead and you have 340 identities. Each carries the string that arrived. Each can be resolved on its own schedule. The scans stay attached to something that means one thing, and the count of unresolved companies becomes a number you can watch fall.

The single unknown member still earns its place. Use it for the case where there genuinely is no natural key: a scan with the company field empty, a badge with no registration behind it. An absent key and an unrecognised key are different conditions and they deserve different rows.

What the technique actually prescribes

The Kimball Group's description of the mechanism is three sentences long and worth following precisely.

"Special dimension rows are created with the unresolved natural keys as attributes", holding "generic unknown values for most of the descriptive columns". Then, when the real context arrives, "the placeholder dimension rows are updated with type 1 overwrites."

The type 1 detail is the load bearing part. Because the stub is overwritten in place, its surrogate key never changes. Every fact row that already points at it inherits the corrected company name, the industry code and the country the moment the overwrite lands, and not one fact row has to be touched. That property is what makes the whole approach cheap: resolving 328 companies is 328 dimension updates and zero fact updates, against a restatement job that would otherwise rewrite thousands of fact rows.

It also puts a constraint on the design that people miss. If the company dimension tracks history with type 2 rows, resolving a stub by inserting a new version and expiring the old one will leave the facts pointing at the expired stub. The resolution has to be a type 1 overwrite of the stub itself, with type 2 versioning starting from the resolved state onward. Where the boundary between those two behaviours sits on your company dimension is its own argument, made in slowly changing company dimension.

The 340 stubs, worked through

Run it through one edition.

The scan file for the edition carries 38,900 rows and roughly 3,180 distinct company strings once trimmed and case folded. The company dimension, built from registration and contracting, resolves 2,840 of them. That leaves 340 strings, 10.7 per cent of the distinct values, with nowhere to point.

The loader creates 340 stub rows. Each gets a surrogate key from the normal sequence, the trimmed string in the natural key column, the raw string in a source value column, "Unknown" in company name, and a flag saying the row is unresolved. The 1,870 scans load with valid foreign keys and the referential test passes.

Eleven days later the exhibitor contract file lands and the resolution pass runs. Matching on the natural key resolves 328 of the 340 outright. Twelve do not match anything, which is 3.5 per cent of the stubs and 61 of the 1,870 scans, or 0.16 per cent of the edition. Those twelve go to a steward's queue with the scan count next to each one, so the person working the queue can see that eight of them are worth two minutes and four of them are worth none.

The number to publish internally is the 12, because 340 measures how much work arrived and 12 measures how much is left. A pipeline that creates 340 stubs and resolves 328 of them within a fortnight is working. A pipeline that creates 340 stubs and still has 300 in March has a broken resolution pass, and the only way to tell those two situations apart is to count them on a schedule.

What the stub row must carry

A stub with only a name in it cannot be resolved by anything except a human, so the row needs enough to make automatic resolution possible.

• The natural key, exactly as it arrived. Trimmed and case folded for matching, and also kept raw in a separate column so nobody has to guess what the source actually sent.
• The source that created it. A stub born from a lead retrieval file and a stub born from a registration file are different problems with different owners.
• The edition it first appeared in. This is what lets you tell a new company from a resolution failure that has been rolling forward for three years.
• An unresolved flag and the timestamp it was created. Reports that need to exclude unresolved companies then have one predicate to write, and the age of the oldest unresolved stub becomes a single number worth putting on an operations dashboard.

The flag matters more than it looks. Without it, every downstream consumer detects stubs by testing whether the company name equals "Unknown", which is a string comparison against a value somebody will eventually change.

How do you stop stubs becoming permanent?

By making the resolution pass a scheduled job with a target, and by counting the stubs where people can see them.

The pass itself is a match between unresolved natural keys and every dimension source that has landed since the last run. Run it after each load into the badge scan fact and again on a weekly cadence for the six weeks following a show, which is the period when late arriving files are still turning up.

Two failure modes are worth watching for. Stubs that resolve to each other, when the same company arrived twice under two spellings and both became stubs, which needs the match to run stub against stub as well as stub against dimension. And stubs that resolve to the wrong record, which is the expensive one, because a wrong overwrite silently reassigns every fact row already pointing at the stub. Anything below a confident match threshold belongs in the steward queue and not in an automatic overwrite.

Where this stops

A placeholder key makes the fact loadable. It does not make the fact meaningful, and there is a temptation to treat the first as if it were the second.

While 340 companies are stubs, every report that groups by industry, country or company size is wrong by whatever those 1,870 scans would have contributed. The report will not say so. It will show a total that looks complete because the join succeeded, and the missing detail is hiding inside a category called Unknown that most readers skim past. Any dashboard built over a dimension with stubs in it needs the unresolved count displayed next to the totals, or it is quietly overstating the resolved segments.

The deeper limit is that a stub is an admission that two systems disagree about who a company is. Creating stubs at load time is the right operational response and a poor long term strategy. If a source produces 340 unresolvable strings every edition, the fix lives in that source, in the form of a picklist at the point of capture or an identity service the device calls, and no amount of downstream stub handling in the data platform substitutes for it.

Start by counting. Run one query against your last edition that groups scans by company natural key and left joins the company dimension, then count the keys that come back null and the scans behind them. That pair of numbers tells you whether you need this mechanism or already have a quieter version of the problem.