A slowly changing company dimension for exhibitors that merge and rebrand

A renewal manager opens the 2023 space contract history to build a call list and finds a name she has never seen. The system says the 2023 contract belongs to Nordkit Group. The person she spoke to in 2023 worked for Nordic Kitchen Systems, and the two are the same firm under a name that changed in March 2024.

She now cannot search her own history by the name written on the contract she is holding. A slowly changing company dimension is what stops that happening, and it costs about five columns and a load-time comparison.

What breaks when you overwrite the exhibitor's name

The default behaviour of most loaders is to update the company record when the source changes. The Kimball Group's technique page on slowly changing dimension type 1 is blunt about the consequence: "the old attribute value in the dimension row is overwritten with the new value; type 1 attributes always reflects the most recent assignment, and therefore this technique destroys history."

Applied to a company dimension in an event business, that destruction has a specific shape.

Every historical contract restates under the current name, so the archive no longer matches the paper. Retention analysis breaks in a subtle direction, because a firm that rebranded looks continuous while a firm that was acquired and reassigned looks like a new customer. Category analysis shifts, because a company that moved from one industry category to another takes all its history with it, and a five-year sector trend changes without any transaction changing.

The version that causes arguments is the parent company attribute. A firm acquired by a group in 2025 gets the group's name written over its 2021 to 2024 rows, and suddenly the group appears to have been the portfolio's third largest customer for five years. Nobody entered a false number. The dimension simply answered a question about 2021 using 2025's facts.

The five columns a type 2 row needs

The Kimball Group's page on type 2 sets out the mechanism: "Slowly changing dimension type 2 changes add a new row in the dimension with the updated attribute values." Doing that requires "generalizing the primary key of the dimension beyond the natural or durable key because there will potentially be multiple rows describing each member", and the page names "a minimum of three additional columns": a "row effective date or date/time stamp", a "row expiration date or date/time stamp" and a "current row indicator".

So five columns, counting the two keys.

The surrogate key is per version. Every time a tracked attribute changes, a new surrogate key is assigned, and the page is explicit that it is "used as a foreign key in all fact tables from the moment of the update until a subsequent change creates a new dimension key and updated dimension row".

The durable company key is per firm and never changes. The Kimball Group's page on natural, durable and supernatural keys explains why it cannot be the source system's identifier: "Natural keys created by operational source systems are subject to business rules outside the control of the DW/BI system." Your CRM will renumber, your registration platform will be replaced, and the durable key has to outlive both.

The effective and expiry timestamps bound the version. The current row indicator saves every query from a date comparison it will eventually get wrong.

Two rows for one company, worked through

Nordic Kitchen Systems AB has exhibited since 2019 and renamed on 15 March 2024.

The dimension holds two rows. Row one carries surrogate key 4417, durable company key 812, name Nordic Kitchen Systems AB, effective 2019-01-01, expiry 2024-03-14, current flag N. Row two carries surrogate key 6103, durable company key 812, name Nordkit Group AB, effective 2024-03-15, expiry 9999-12-31, current flag Y.

Now the facts. The 2023 space contract was loaded with company key 4417, because that was the version in force when it was signed. It still reports under Nordic Kitchen Systems AB, which matches the contract in the renewal manager's hand. The 2025 contract carries 6103 and reports under Nordkit Group AB.

Both perspectives are available and neither needs a restatement.

Group by surrogate key and the two names appear separately: 42,000 of space revenue in 2023 under the old name, 51,000 in 2025 under the new one.

Group by durable company key 812 and the firm's five-year value is 93,000 across both names, computed by adding 42,000 and 51,000, with the current name available from the row where the current flag is Y.

That dual perspective is the point of holding both keys. The Kimball Group's own technique summary describes a dual type 1 and type 2 arrangement, where the durable key supports the current-value view and the surrogate key supports the as-at-the-time view, and it costs one extra integer column on the fact table.

The expiry sentinel of 9999-12-31 is a convention worth adopting rather than leaving the column null, because a between comparison over a null returns nothing and somebody will write one.

Which attributes deserve type 2 and which do not?

The test is whether the old value was true at the time.

Trading name, parent company, country of registration, industry category and employee size band all describe a real state of the world that later changed. The 2023 value was correct in 2023, so it earns a new row.

A corrected postcode, a fixed spelling, a stripped legal suffix and a de-duplicated address line are repairs to values that were never right. Those should overwrite, because keeping a history of your own data entry errors makes the dimension larger and answers no business question.

The awkward middle is anything derived. An account tier or a health score computed by your own model is a judgement, and if you type 2 it, the dimension starts recording model versions as though they were events in the exhibitor's history. Keep derived attributes in their own table keyed on the durable company key and the date, and leave the dimension for facts about the firm.

Category is the attribute where this decision has the most reach, because it is also mapped. A local category value that changes for a firm should create a new row, while a change to the master list that value maps to is a change to the mapping table and not an event in the company's life at all. Confusing those two is how a sector trend moves for no reason.

What happens when two exhibitors merge?

Type 2 handles change over time and it does not handle two entities becoming one. That needs a decision, and the decision should be recorded in the data.

Say durable keys 812 and 1190 merge in 2025 under 812's brand. Three arrangements are possible and each answers a different question.

Expire 1190's current row, open a new row under 812 for the combined entity, and add a predecessor durable key column recording 1190. History stays where it was, and a query can follow the chain forward when it wants the combined view.

Or retire both and open a new durable key for the merged firm, with both predecessors recorded. This is the honest choice when the merged entity is genuinely new, and it costs you the continuity of both trend lines.

Or leave both open and treat the merger as a group relationship, which works if you already hold a parent company attribute and if the two continue to contract separately, which they often do for two more editions.

I would take the first, because in an exhibitions context one brand almost always continues and the sales team already thinks of the account as continuing. What matters more than the choice is the predecessor column, because without it the merger is invisible and a five-year revenue chart drops a customer with no explanation.

The world outside has the same problem and has standardised part of it. The Legal Entity Identifier, defined by ISO 17442 with Part 1 published in 2012 and operated through GLEIF, is a 20-character code whose Level 1 reference data holds the entity's legal name as recorded in official registers, its registered address, its country of formation and a set of dates including last update and expiry. That gives you a stable external identifier to attach to your durable key where your exhibitors have one, which is common in regulated industries and rare in consumer goods.

What the dimension costs in rows

Type 2 makes people nervous about growth, and the arithmetic settles it quickly for a company dimension.

Take 3,400 exhibitor companies across the portfolio. Suppose 6 per cent of them change a tracked attribute in a given year, which is roughly 204 firms.

Over five editions that adds 1,020 rows, taking the dimension from 3,400 to about 4,420. The increase is 30 per cent, on a table that fits in a spreadsheet.

Compare that with the fact tables it serves, which run to well over a million scan rows across the same period, and the storage argument disappears. The real cost of type 2 is the load logic: detecting a change, expiring the old row, assigning a new surrogate key and pointing new facts at it. That is an hour of work per dimension and a test that checks no durable key has two rows with the current flag set.

Run that test every load. Two current rows for one firm is the failure mode that produces double counting everywhere at once, and it is trivial to detect and invisible in a report. Company is shaded in almost every row of an event bus matrix, so a defect here reaches further through the data platform than a defect in any other dimension.

Where this stops

A type 2 dimension records changes it is told about. It cannot see a change nobody sent.

If your exhibitor renames in March and your CRM is updated in November, the effective date on the new row is November, and eight months of contracts report under the old name when they should not. Sourcing the effective date from the source system's own change date, where it has one, is better than using the load date, and where neither exists the honest move is to record which you used.

The harder limit is that type 2 assumes you know which firm a record belongs to. Two records for the same company under different spellings will produce two durable keys and two independent histories, and no amount of type 2 machinery merges them. That is a matching problem with its own methods, and it belongs with the identity resolution work rather than here. Getting it wrong in the merging direction is worse, because a false merge writes two firms' contract history into one durable key and there is no undo without a rebuild.

There is also a reporting cost people underestimate. Once the dimension holds versions, every query has to say which version it wants, and an analyst who joins on the durable key without filtering the current flag will fan out rows the same way a grain mismatch does. Views that expose the current-only slice by default, with the full history available under a different name, prevent most of it.

Run one query on your company table this week: count rows, then count distinct durable company keys. If those two numbers are the same, your dimension has no history in it, and the next rename in your exhibitor base will quietly rewrite the archive. Then check that the contract facts hanging off your edition rows and venue records point at the version keys rather than at the durable key, because that is the join that decides which name appears on the report.