A slowly changing dimension (SCD) is a dimension table in a data warehouse whose attributes change over time, but not often: a customer moves to another city, a product switches category, a sales rep is reassigned to a new region. The “slowly” part matters. These are not events you log every second; they are descriptive facts about a business entity that shift every few weeks or months.

Why it matters now: when a dashboard says “revenue by region” or an AI agent answers “how did our premium customers behave last quarter?”, the answer depends on which version of the customer or product record you join to. If the warehouse simply overwrites old values, history quietly rewrites itself. SCD techniques are how analytics teams decide, attribute by attribute, whether to keep that history or not.

Where slowly changing dimensions fit

In dimensional modeling, fact tables store measurable events (orders, payments, sessions) and dimension tables store the context used to slice them (customer, product, store, date). If that model is new to you, start with our guide on what a star schema is and why AI-ready analytics depend on dimensional modeling. SCDs live on the dimension side: they define what happens to a dimension row when the source system changes it.

The main SCD types

The vocabulary comes from Ralph Kimball’s dimensional modeling techniques, which number the approaches by type (Kimball Group: Type 2 — Add New Row). The three you will meet in almost every project are:

Type 0: retain the original

The value never changes after it is first loaded. Typical examples are an original signup date or the first acquisition channel. Even if the source changes, the warehouse keeps the first value on purpose.

Type 1: overwrite

The new value replaces the old one and no history is kept. This is right for corrections (a misspelled name, a fixed typo in an email) and for attributes where only the current value is meaningful. The trade-off is that every past report that used the old value will now show the new one (Kimball Group: Type 1 — Overwrite).

Type 2: add a new row

Each change creates a new version of the row, so the dimension keeps the full history. A Type 2 customer dimension usually has:

• A surrogate key (a warehouse-generated ID unique to each version), separate from the natural key that comes from the source system, such as the customer ID.

• Effective date and expiration date columns that bound when each version was valid.

• A current-row flag so queries can quickly pick the latest version.

Facts are joined to the surrogate key that was valid when the event happened. That way, an order placed while the customer lived in Chicago stays attributed to Chicago even after the customer moves to Denver.

Other types you may see

Type 3 adds a “previous value” column to keep one prior version side by side with the current one. Hybrid types (often called Type 4 or Type 6) combine a history table with current-value columns. They are useful in specific reporting cases, but most teams get far with a deliberate mix of Types 1 and 2.

A quick example

Imagine customer 1042 upgrades from the “Basic” plan to “Premium” on March 1. With Type 1, the customer row just says Premium, and every past order now looks like a Premium order. With Type 2, the dimension holds two rows for 1042: one with plan Basic valid until February 28, and one with plan Premium valid from March 1 and flagged as current. A report of revenue by plan now shows what customers were actually paying for at the time of each purchase.

How modern data stacks implement SCD Type 2

You rarely have to hand-write the merge logic anymore:

• Change data capture feeds stream inserts, updates, and deletes from operational databases, which gives the warehouse the raw signal that something changed. See our explainer on change data capture and why AI pipelines rely on it.

• Lakehouse pipelines offer built-in support. Databricks, for example, documents APIs that apply CDC events as either SCD Type 1 or Type 2 tables, handling out-of-order records for you (Databricks: The AUTO CDC APIs).

• Low-code data integration tools provide templates as well. Microsoft documents a Type 2 pattern in Fabric Data Factory dataflows (Microsoft Learn: Slowly changing dimension type 2).

• Transformation frameworks such as dbt include snapshot features that track row versions over time with valid-from and valid-to columns.

Why SCDs matter for AI and analytics

• Point-in-time correctness: machine learning features must reflect what was known when an event happened. Training a churn model on today’s customer attributes for last year’s behavior leaks future information into the model.

• Trustworthy answers from AI agents: text-to-SQL assistants and analytics copilots generate joins. If the dimension design is unclear, they will confidently join to the wrong version of a record.

• Auditability: finance, compliance, and pricing teams often need to reproduce a report exactly as it looked at quarter close.

Common mistakes

• Applying Type 2 to every column, which makes tables explode in size for attributes nobody analyzes historically.

• Joining facts on the natural key instead of the surrogate key, which multiplies rows when a customer has several versions.

• Forgetting late-arriving data, so events get matched to the wrong version.

• Not documenting which attributes are Type 1 and which are Type 2, leaving analysts to guess.

FAQ

Is SCD Type 2 always the best choice?

No. Use Type 2 only for attributes where history changes the meaning of past analysis, such as segment, plan, region, or price tier. Use Type 1 for corrections and attributes where only the current value matters.

How do I query only the current version?

Filter on the current-row flag or on an expiration date set to a far-future value. For historical analysis, join each fact to the version whose effective range contains the event date.

Do data analysts need to know SCDs, or is it only for data engineers?

Analysts need to know them. Even if engineers build the tables, analysts write the joins and explain the numbers, and SCD design is often the reason two reports disagree.

Want to model data that keeps its history straight and build AI tools that query it correctly? Explore these Coderhouse courses:

• AI Engineering Course — build AI applications and agents that reason over trusted, well-modeled company data.

• Data Analytics Course — master SQL, dimensional modeling, and dashboards that answer real business questions.