> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/build/recipes/track-dimension-history-with-scd-type-2.md).

# Track dimension history with SCD Type 2

Convert an overwrite-style dimension model to SCD\_TYPE\_2 so changes are versioned with valid\_from/valid\_to instead of lost on the next run.

An `INCREMENTAL_BY_UNIQUE_KEY` or `FULL` dimension model keeps only the current value of a row. The moment a customer's plan tier changes, the old value is gone, overwritten by the next merge or rebuild. That's fine when only "what is true right now" matters. It breaks the moment someone asks "what was this customer's plan tier last quarter," or a downstream report needs to attribute revenue to the tier that was active at the time, not the tier that's active today.

Use this recipe when a dimension changes over time and something downstream needs the history, not just the latest snapshot: a customer profile, a subscription plan, a product price; anywhere "what changed and when" matters as much as "what is it now."

{% hint style="warning" %}
SCD Type 2 costs more storage and compute than an overwrite-style model, and it doesn't support partial restatement: restating an SCD2 model always recreates the entire table. Because full-table restatement risks losing history, Vulcan disables restatement by default for SCD Type 2 models; you have to set `disable_restatement false` explicitly on the model's `kind` block to allow a restatement to run at all. Reach for SCD2 only when the history is actually needed downstream.
{% endhint %}

## Before you begin

{% hint style="info" %}

* An existing dimension model (`FULL` or `INCREMENTAL_BY_UNIQUE_KEY`) you want to convert, or a source table you're modeling for the first time.
* Know whether the source table has a reliable "last changed" timestamp column (`updated_at`, `modified_at`, or similar). This decides which SCD2 variant you use.
* A unique key for the dimension (e.g. `customer_id`). SCD2 needs one, same as `INCREMENTAL_BY_UNIQUE_KEY` does.
  {% endhint %}

## Steps

### 1. Confirm SCD2 is the right kind

Three kinds compete for the same "dimension table" job. Pick based on whether you need history, not just habit:

| Kind                        | Behavior                                                                     | Use when                                                                         |
| --------------------------- | ---------------------------------------------------------------------------- | -------------------------------------------------------------------------------- |
| `FULL`                      | Rebuilds the whole table every run                                           | Table is small and cheap to rebuild; you only ever need "now"                    |
| `INCREMENTAL_BY_UNIQUE_KEY` | Upserts on a key; old value is gone once updated                             | You need "now" only, but the table is too large to rebuild fully each run        |
| `SCD_TYPE_2`                | Upserts on a key, but keeps every prior version with `valid_from`/`valid_to` | Something downstream needs "what was true at time X," not just "what's true now" |

If nothing downstream ever queries a historical value, `INCREMENTAL_BY_UNIQUE_KEY` is simpler and cheaper. Don't reach for SCD2 by default.

### 2. Pick a change-detection strategy: by time or by column

Vulcan detects changes two ways:

* **`SCD_TYPE_2_BY_TIME`** (recommended): reads an "updated at" column from the source and uses it as the real change timestamp. `valid_from` reflects when the source system actually changed the record.
* **`SCD_TYPE_2_BY_COLUMN`**: watches a list of columns for value changes between runs when the source has no update timestamp. `valid_from` reflects when Vulcan *detected* the change, not when it actually happened.

If the source has any kind of "last modified" column, use `BY_TIME`. Only fall back to `BY_COLUMN` when the source genuinely can't tell you when a value changed.

### 3. Convert the model definition

Starting point. An overwrite-style customer dimension using `INCREMENTAL_BY_UNIQUE_KEY`:

```sql
MODEL (
  name warehouse.dim_customer_profile,
  kind INCREMENTAL_BY_UNIQUE_KEY (
    unique_key customer_id
  ),
  grains (customer_id),
  cron '@daily'
);

SELECT
  customer_id,
  name AS customer_name,
  email,
  plan_tier,
  updated_at
FROM raw.customers
```

Convert it to `SCD_TYPE_2_BY_TIME`. Notice the query doesn't change at all. Only the `kind` block does:

```sql
MODEL (
  name warehouse.dim_customer_profile,
  kind SCD_TYPE_2_BY_TIME (
    unique_key customer_id
  ),
  grains (customer_id),
  cron '@daily'
);

SELECT
  customer_id,
  name AS customer_name,
  email,
  plan_tier,
  updated_at
FROM raw.customers
```

If `raw.customers` has no `updated_at` column, use `SCD_TYPE_2_BY_COLUMN` instead and list the columns to watch explicitly:

```sql
MODEL (
  name warehouse.dim_customer_profile,
  kind SCD_TYPE_2_BY_COLUMN (
    unique_key ARRAY[customer_id],
    columns ARRAY[customer_name, email, plan_tier]
  ),
  grains (customer_id),
  cron '@daily'
);

SELECT
  customer_id,
  name AS customer_name,
  email,
  plan_tier
FROM raw.customers
```

Either variant adds `valid_from` and `valid_to` columns to the materialized table automatically. Don't declare them yourself.

### 4. Run `vulcan plan` and expect a full backfill

Switching a model's kind (from `INCREMENTAL_BY_UNIQUE_KEY` to `SCD_TYPE_2_BY_TIME`, for instance) is a breaking change. `vulcan plan` recreates the physical table from scratch and backfills it in full, adding `valid_from`/`valid_to` for every existing row (rows present before the switch get `valid_from` set to the epoch, not their real creation time, since that information doesn't exist yet).

```bash
vulcan plan --select-model "warehouse.dim_customer_profile"
```

Review the plan output before applying: confirm it's showing a full rebuild of this one model, not an unexpected ripple into unrelated downstream models. This is also why SCD2 doesn't support partial restatement afterward: any future restatement of this model recreates the whole table again, the same way the initial conversion does. Vulcan disables restatement by default for exactly this reason; a future restatement on this model only runs after you explicitly add `disable_restatement false` to its `kind` block.

### 5. Verify history with a query

Say `raw.customers` changes over three runs:

| Run date   | `plan_tier` |
| ---------- | ----------- |
| 2026-01-01 | free        |
| 2026-01-02 | pro         |
| 2026-01-03 | enterprise  |

After the third run, `warehouse.dim_customer_profile` for `customer_id = 42` looks like this:

| customer\_id | plan\_tier | updated\_at | valid\_from         | valid\_to           |
| ------------ | ---------- | ----------- | ------------------- | ------------------- |
| 42           | free       | 2026-01-01  | 1970-01-01 00:00:00 | 2026-01-02 00:00:00 |
| 42           | pro        | 2026-01-02  | 2026-01-02 00:00:00 | 2026-01-03 00:00:00 |
| 42           | enterprise | 2026-01-03  | 2026-01-03 00:00:00 | NULL                |

The current row (`valid_to IS NULL`):

```sql
SELECT * FROM warehouse.dim_customer_profile
WHERE customer_id = 42
  AND valid_to IS NULL;
```

What the customer's tier was at a point in the past. This is the query overwrite-style models can never answer:

```sql
SELECT * FROM warehouse.dim_customer_profile
WHERE customer_id = 42
  AND valid_from <= '2026-01-02 15:00:00'
  AND (valid_to IS NULL OR valid_to > '2026-01-02 15:00:00');
-- returns the 'pro' row
```

### 6. Decide how hard deletes should behave (optional)

If a row disappears from the source entirely, `invalidate_hard_deletes` controls whether that counts as "deleted at this timestamp" (`true`) or "still valid, no gap in history" (`false`, the default). Set it explicitly rather than relying on the default if deletion timing matters to your use case:

```sql
MODEL (
  name warehouse.dim_customer_profile,
  kind SCD_TYPE_2_BY_TIME (
    unique_key customer_id,
    invalidate_hard_deletes true
  ),
  grains (customer_id),
  cron '@daily'
);
```

## Best practices

* Prefer `BY_TIME` whenever the source has any reliable "last changed" column. `valid_from` is only as accurate as the signal you give it.
* Set `grains` on the unique key so downstream models and the catalog know the row identity, same as any other dimension model.
* Expect a full backfill the first time you switch a model to SCD2. Don't schedule the switch during a window when a large rebuild would collide with other production runs.
* Never treat `valid_to = NULL` as "row deleted." It means "still current" (the opposite).
* Bound point-in-time queries on both `valid_from` and `valid_to`. Filtering only on `valid_to IS NULL` silently drops into "current state only," which defeats the purpose of using SCD2 in the first place.

## Troubleshooting

| Symptom                                                                         | Likely cause                                                                                                               | Fix                                                                                                                               |
| ------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------- |
| Every plan fully rebuilds the table, even for a tiny data change                | Expected behavior for SCD2: no partial restatement is supported                                                            | Confirm this cost is acceptable before adopting SCD2 for very large or fast-changing sources                                      |
| `valid_from` doesn't match when the change "really" happened                    | Model uses `BY_COLUMN`, which stamps `valid_from` at detection time, not source change time                                | Switch to `BY_TIME` if the source has (or can add) an `updated_at`-style column                                                   |
| A deleted-then-reinserted record shows an unexpected gap, or no gap at all      | `invalidate_hard_deletes` doesn't match your expectation                                                                   | Set `invalidate_hard_deletes` explicitly instead of relying on the default                                                        |
| A query returns more than one "current" row for the same key                    | `unique_key` doesn't actually identify one row per key                                                                     | Re-check `unique_key` against the source data and the model's `grains`                                                            |
| `vulcan plan` triggers a much bigger rebuild than expected when switching kinds | Switching to (or between) SCD2 variants is inherently a full-table operation                                               | Expect and schedule for the full rebuild: this isn't a partial-migration path                                                     |
| A restatement on an SCD2 model is refused or has no effect                      | Vulcan disables restatement by default for SCD2 models, since a restatement recreates the whole table and can lose history | Add `disable_restatement false` to the model's `kind` block only when you intentionally want to restate, then remove it afterward |

## References

* [Model kinds](https://v2.dataos.info/references/resources/vulcan/models/data-models/model-kinds) - full `SCD_TYPE_2_BY_TIME` / `SCD_TYPE_2_BY_COLUMN` reference, including deletes and column-renaming options.
* [Properties](https://v2.dataos.info/references/resources/vulcan/models/data-models/properties) - the general `MODEL` DDL property reference.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/build/recipes/track-dimension-history-with-scd-type-2.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
