Keeping the Old Chart on File: Slowly Changing Dimensions, Type 2

Opening Scene

A star gets reclassified into a genuinely different category — not a correction of a past mistake, but an actual, real change astronomers observed happening over time. This time, the observatory doesn’t erase the old entry. It files a new chart entry alongside the old one, marks exactly when the change took effect, and keeps both on record permanently. Any observation made before the change correctly shows the star under its old classification; any observation made after correctly shows it under the new one. Nothing about the past gets silently rewritten.

Slowly Changing Dimension Type 2 is built precisely to preserve this kind of genuine history.

In Plain English

A Type 2 Slowly Changing Dimension (SCD) handles a change to a dimension’s attribute by adding a brand-new row rather than overwriting the old one, typically with effective-date and end-date columns (or a current-flag) marking exactly which version was valid when. This lets historical facts stay correctly associated with the dimension values that were actually true at the time they occurred, rather than having history retroactively rewritten by a later change.

The Old Way

Implementing Type 2 correctly has always required more deliberate design than Type 1, in direct exchange for genuinely preserving history:

• A new surrogate key is generated for each new version of a dimension row, so that historical fact table rows can continue pointing to the specific version of the dimension that was actually true when that fact occurred.
• Effective-date and end-date columns (or an equivalent current-flag pattern) make it possible to determine which version of a dimension row was active at any specific point in the past, supporting genuinely accurate point-in-time analysis.
• Type 2 is the right choice whenever an attribute’s history is genuinely meaningful to preserve — a customer’s changing address for territory analysis, a product’s changing category for trend analysis — in clear contrast to Type 1’s appropriate use for simple corrections.

Getting this right has always meant genuinely understanding which specific attributes deserve this treatment, since applying Type 2 indiscriminately to every attribute produces real, unnecessary table growth and complexity where a simpler Type 1 approach would have been entirely sufficient.

What’s Changing (and Why AI Is the Reason)

1. AI-assisted point-in-time query generation can correctly navigate a Type 2 dimension’s effective-dating logic on behalf of a user, reducing a genuinely common source of subtle analytical error. Manually writing correct point-in-time joins against a Type 2 dimension is notoriously easy to get wrong; AI-assisted query generation that correctly understands effective-dating conventions can produce reliably accurate historical queries without requiring every analyst to master the pattern by hand.
2. AI-assisted change tracking can automatically detect when a source system’s data represents a genuine historical change worth capturing as a new Type 2 version, versus a correction better handled as Type 1. Rather than a modeler manually classifying every incoming change, AI-assisted analysis of a change’s context and pattern can inform which SCD strategy actually applies, and route accordingly.
3. AI agents answering “what was true at the time” questions depend entirely on Type 2 history actually existing and being queried correctly, making this pattern more valuable, not less, as agents take on more historical and trend analysis directly. An agent answering a question like “what territory was this customer in when they made that purchase” can only do so correctly if the underlying dimension genuinely preserved that history and the agent’s generated query correctly respects the effective-dating logic.

The Metaphor, Fully Extended

Observatory Element	SCD Type 2 Concept
Filing a brand-new chart entry for a star’s genuine reclassification, alongside the old one	Adding a new row for a changed dimension attribute, rather than overwriting the old one
A marked date showing exactly when the new classification took effect	Effective-date and end-date columns marking which version was valid when
An observation from before the change correctly showing the star’s old classification	A historical fact table row correctly pointing to the dimension version that was true at the time
An astronomer weighing which star attributes genuinely deserve this full historical treatment	Deliberately choosing which specific attributes warrant Type 2, rather than applying it universally
A cataloguing assistant correctly retrieving the classification that was actually true on a specific past date	AI-assisted point-in-time query generation correctly navigating Type 2 effective-dating logic

Quick Recap

• Type 2 Slowly Changing Dimensions preserve genuine history by adding a new row for each change, with effective-date and end-date columns marking which version was valid when.
• It’s the right choice for attributes whose history is genuinely meaningful to analysis, in clear contrast to Type 1’s simple overwrite for corrections.
• AI-assisted point-in-time query generation can reduce the risk of subtly incorrect historical queries, and AI-assisted change tracking can help classify which SCD strategy actually applies.
• AI agents answering historical “what was true at the time” questions depend entirely on Type 2 history existing and being queried correctly.

Where This Fits in the Series

Article 9 covered simply correcting the chart and moving on. This article covered keeping the old chart on file too. Article 11 looks at sweeping up the loose stars — junk dimensions.