Choosing type 2 versus type 1 attributes on your contact dimension

Somebody in exhibition sales opens the 2024 lead file to work out who a lapsed exhibitor actually met at that edition. One of the best names comes back as a graduate analyst at a firm the exhibitor has never sold to. Two years earlier that same person was a purchasing director at a national distributor, which is why the lead scored the way it did and part of why the exhibitor renewed.

Nothing about 2024 changed. The contact dimension was updated in place when the person moved job, and now every report that joins to it describes 2024 using 2026 employers. Type 2 versus type 1 attributes is the decision that produced that, and in most event warehouses nobody made it on purpose.

The two techniques and what each one costs

The Kimball Group publishes the technique definitions drawn from the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), and they are short enough to quote.

Type 1 is the overwrite. The old attribute value in the dimension row, in the published wording, "is overwritten with the new value", and the page states without hedging that "this technique destroys history". It also carries a warning most teams skip: "you must be careful that aggregate fact tables and OLAP cubes affected by this change are recomputed."

Type 2 adds a row. The published definition is that "slowly changing dimension type 2 changes add a new row in the dimension with the updated attribute values", and the technique needs three columns at minimum: a "row effective date or date/time stamp", a "row expiration date or date/time stamp" and a "current row indicator". The old row keeps its surrogate key. Every fact row already loaded against that key keeps pointing at the description that was true when the badge was scanned.

Margy Ross of the Kimball Group set out the remaining variants in her design tip of 5 February 2013, including type 0, where "the dimension attribute value never changes, so facts are always grouped by this original value". Type 0 is worth knowing about because a contact dimension has at least one of them, which is the durable key that survives every other change.

So the choice per column is a question about meaning. Does a change in this value mean the world changed, or does it mean the record was wrong?

Which contact attributes earn a type 2 row?

Start with the columns whose old values explain something you already reported.

Job title earns type 2. So does employer, and so does the employer's country if you report international mix by attendee rather than by registration address. Each of those was an input to how the person was qualified, routed, priced and matched at a specific edition, and a report about that edition is wrong if it uses today's value.

Department and function usually earn it too, though the argument is weaker and depends on whether your qualification questions use them.

Now the other group. A corrected email address is a typo fix. A phone number reformatted from a local dialling pattern to international. A first name written in capitals and then written properly. A surname with the accent restored. None of those represent a change in the world, and giving them type 2 treatment produces a row that means nothing except that somebody in data stewardship did their job.

Marketing consent is the interesting edge, and I would put it in neither group. Consent state has to be current at the moment you send, and it also has to be provable for a date in the past, which makes it a fact with a timestamp rather than a dimension attribute at all. Model it as its own table of consent events and read the current state from the latest one.

The test I use is short. If a colleague could reasonably ask "what did this say when the show ran", the column is type 2. If the only honest answer is "it said something wrong", overwrite it.

Working the row count on a 60,000 contact dimension

The objection to type 2 is always size, so do the arithmetic before conceding it.

Take a contact dimension holding 60,000 people across a portfolio, with four type 2 attributes: job title, employer, employer country and department. Assume that between one edition and the next, 7.5 per cent of those contacts change at least one of the four. That is 4,500 people, and each of them gains exactly one new row, no matter how many of the four attributes moved at once.

Five editions means four transitions. Four times 4,500 is 18,000 new rows, so the dimension arrives at 78,000 rows. Divide 78,000 by 60,000 and the average contact carries 1.3 rows. Growth over the whole five year history is 30 per cent, which is a number no query planner will notice and no storage bill will show.

Now run the same arithmetic on the mistake. Suppose email had been made type 2 as well, and suppose 3 per cent of contacts have an address corrected in a given cycle. That is 1,800 rows per transition, 7,200 over four, taking the dimension to 85,200. Those 7,200 rows carry no analytical content at all. They exist because a stewardship queue was worked, and every one of them makes a distinct count of contacts slightly harder to write correctly.

The 7.5 per cent is an assumption, and it is the one number here you should replace with your own. Take two consecutive editions of one show, join the registration files on a durable person key, and count how many people show a different employer string. Whatever that share is, it is the growth rate of your contact dimension under type 2 and the size of the reporting error you are currently absorbing under type 1.

Reporting consequences you will actually be asked about

Type 2 changes what a join means, and two questions come up within a month of switching.

The first is how to count people. A distinct count of the dimension's primary key returns 78,000 in the example above, which is not the number of humans. Counting humans means a distinct count of the durable key, the identifier that stays constant across every version of the same person. Every query that reports audience size has to use that column, and the fastest way to enforce it is to build the reporting view so the surrogate key is not exposed at all.

The second is which version to filter on. Ask "how many senior buyers attended the 2024 edition" and you want the row that was current at that edition, which comes for free by joining through the fact. Ask "how many of the people who attended in 2024 are senior buyers today" and you want the current row indicator, which is a different query with a different answer. Both are legitimate, they are the two ends of what practitioners call the as-was and as-is view, and a reporting layer that offers only one of them will be quietly used for the other.

A third consequence lands on the loader. Facts have to be assigned the dimension key that was current at the time the fact occurred, so the badge scan fact table needs its contact key resolved against the effective and expiry dates on the dimension row. For a show that loads its scans during and just after the event, that lookup is trivial, because current is also correct. It stops being trivial the moment you backfill.

Why does the overwrite keep winning by default?

Because the source system does it, and the pipeline copies the source system.

Registration platforms hold one current record per contact. When the person registers for the 2026 edition with a new employer, the platform updates the record, and a load that mirrors the platform inherits its behaviour without anybody choosing it. The decision gets made in the absence of a decision, which is the same mechanism that puts a single activity table on the whiteboard when the grain has not been declared.

There is a second reason, which is that the damage is invisible for about eighteen months. In year one, current and historical are the same thing. The reports agree. The first person to notice is whoever compares a figure they published last year against the same figure recomputed today and finds it has moved, at which point the dimension has been overwriting for five cycles and there is no record of what it said.

That asymmetry is the whole argument. Turning a type 1 attribute into type 2 is easy and starts working immediately. Recovering the history a type 1 attribute discarded needs a source system that kept it, and registration platforms mostly do not.

Where this stops

Type 2 preserves the values you decided to track, on the schedule you happened to load. It does not give you a continuous record of a person's career.

If a contact changes employer in September and you next load the dimension in February, your effective date says February. Every fact between those two points is attributed to the old employer, and the dimension will state that confidently, with a date stamp that looks authoritative. For an annual show that loads around the edition, the effective dates in a type 2 contact dimension are closer to observation dates than to change dates, and anyone using them for tenure analysis will be wrong by up to a year.

The row count also stops being trivial when the volatile attributes are banded ones that move every cycle. Seniority band, buying role and company size band can each change without the job title changing, and tracking those as type 2 on the main dimension multiplies rows for values that exist mainly to group people. Those belong in a small separate table keyed from the fact, which keeps the contact dimension growing at the rate calculated above.

And type 2 says nothing about which edition a fact belongs to. That comes from the edition row the fact points at, and getting the two confused produces a report that filters on dimension effective dates when it means to filter on show dates. Keeping those separate is a modelling rule worth writing down in the same place as the rest of the data platform conventions.

Open your contact dimension this week and list its columns in two groups on one page: the ones where an old value would still be worth reading, and the ones where an old value would just be a mistake you have already fixed. If any column in the first group is currently overwritten, you have found the report that is going to be wrong the next time somebody looks back two years.