> ## Documentation Index
> Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Slowly Changing Dimensions

## Introduction

**Slowly Changing Dimensions** (**SCDs**) are a common data modeling technique used to manage
historical changes in dimension data over time.
This enables more accurate time-based analysis and reporting,
such as understanding how KPIs were affected under previous attribute values.

SCDs are categorized into different types based on how they handle changes to dimension data:

#### SCD Type 0 - Fixed Dimensions

* No changes allowed. The data remains as it was when first inserted.
* Useful when historical accuracy is critical and the value should never change.
* Example: A product's original launch date.

#### SCD Type 1 - Overwrite

* Changes overwrite existing data. No history is preserved.
* Simple to implement but loses historical context.
* Example: If a customer changes their email, the old one is replaced.

#### SCD Type 2 - Historical Tracking

* Each change creates a new record, often with start/end timestamps or versioning.
* Preserves full history of changes.
* Example: Tracking changes to a customer's loyalty tier over time.

#### SCD Type 3 – Previous Value

* Stores only the previous value alongside the current one.
* Limited history, useful when only one change needs to be tracked.
* Example: Keeping a “current region” and a “previous region” field for a customer.

<Tip>
  SCD0 and SCD3 are rarely used.
</Tip>

## Modeling SCDs in Honeydew

In Honeydew, the modeling of SCDs of Types 0, 1, and 3 is straightforward.
Joins between entities are defined using the [standard foreign key relationships](/docs/modeling/relations#connecting-entities-using-attributes).

For SCD Type 2, Honeydew supports modeling SCDs using a combination of foreign keys and date ranges.
Joins between entities are defined using a [custom SQL expression](/docs/modeling/relations#connecting-entities-using-a-custom-sql-expression)
that includes the date range logic.

### Example: Fact and a Slowly Changing Dimension (SCD2)

Given two tables:

* `fact_sales`: a **fact** table tracking order transactions that has a foreign key `customer_id`
* `dim_customer`: a **dimension** table tracking customer information over time (SCD Type 2). It has multiple entries
  per `customer_id` with validity ranges.

Would want to connect the sales data with the correct historical customer information **as it was at the time of the order**.

#### Sample data

`fact_sales` (Fact Table)

| order\_id | customer\_id | order\_date | amount |
| --------- | ------------ | ----------- | ------ |
| 5001      | 101          | 2021-06-10  | 250    |
| 5002      | 101          | 2023-01-12  | 300    |
| 5003      | 102          | 2022-07-22  | 450    |

`dim_customer` (SCD2 Dimension Table)

| customer\_id | customer\_sk | name        | region | valid\_from | valid\_to  |
| ------------ | ------------ | ----------- | ------ | ----------- | ---------- |
| 101          | 1            | Alice Smith | East   | 2021-01-01  | 2022-03-01 |
| 101          | 2            | Alice Smith | West   | 2022-03-01  | 9999-12-31 |
| 102          | 3            | Bob Johnson | North  | 2021-05-15  | 9999-12-31 |

<Note>
  The key for `dim_customer` is not `customer_id` (which is repeating across ranges), but rather a surrogate key (`customer_sk`)
  `valid_from` / `valid_to` define the row's effective period.

  Also note that valid\_to here is an infinity date (9999-12-31). In some settings it is used as NULL instead, in which case can
  adjust the join condition accordingly.
</Note>

#### Relations

To associate each order with the **correct customer version at that point in time**, use a custom SQL expression on `valid_from` and `valid_to`:

```sql theme={null}
fact_sales.customer_id = dim_customer.customer_id
AND fact_sales.order_date >= dim_customer.valid_from
AND fact_sales.order_date < dim_customer.valid_to
```

And set a "many-to-one" relationship from `fact_sales` to `dim_customer`

#### Example query

Result of a query on both:

| order\_id | customer\_id | order\_date | amount | name        | region |
| --------- | ------------ | ----------- | ------ | ----------- | ------ |
| 5001      | 101          | 2021-06-10  | 250    | Alice Smith | East   |
| 5002      | 101          | 2023-01-12  | 300    | Alice Smith | West   |
| 5003      | 102          | 2022-07-22  | 450    | Bob Johnson | North  |

### Advanced: Multiple SCD2 (Fact and Dimension) + Point-in-Time Reference point

Advanced use cases for slowly changing dimensions allow to inspect the state of the world at any point in time (including "now"), while
every data table has slowly changing dimension fields.

Here, the previous example is extended to support of consistent **point-in-time queries** on historical data where:

* `fact_sales`: a **fact** table with changing business logic over time (e.g. updated amount, revised status). It has multiple versions per `order_id`, each valid over a time range.
* `dim_customer`: a **dimension** table with customer history over time (e.g. changed region), also with validity ranges.

A central `dim_date` or `dim_point_in_time` table is used to filter everything **as of a specific point**.

<Warning>
  Since data is duplicated in multiple versions, users **must** filter on `dim_point_in_time` to get correct results (whether for "today" or for any historical point of reference).

  See [Conditional Filtering](/docs/advanced-modeling/conditional-filtering) on how to set an automatic filter in a domain, and an example below.

  You can also ensure a filter is always applied by configuring it directly within user-facing tools, such as BI dashboards.
</Warning>

This structure is used in **auditable data models**, financial snapshots, and analytics platforms.

#### Sample data

`fact_sales` sample data:

| order\_sk | order\_id | customer\_id | order\_date | amount | status  | valid\_from | valid\_to  |
| --------- | --------- | ------------ | ----------- | ------ | ------- | ----------- | ---------- |
| 9001      | 5001      | 101          | 2021-06-10  | 250    | Pending | 2021-06-10  | 2021-07-01 |
| 9002      | 5001      | 101          | 2021-06-10  | 300    | Shipped | 2021-07-01  | 9999-12-31 |
| 9003      | 5002      | 102          | 2022-01-15  | 300    | Pending | 2022-01-15  | 9999-12-31 |

`dim_customer` sample data:

| customer\_sk | customer\_id | name        | region | valid\_from | valid\_to  |
| ------------ | ------------ | ----------- | ------ | ----------- | ---------- |
| 1            | 101          | Alice Smith | East   | 2020-01-01  | 2022-03-01 |
| 2            | 101          | Alice Smith | West   | 2022-03-01  | 9999-12-31 |
| 3            | 102          | Bob Johnson | North  | 2021-05-01  | 9999-12-31 |

`dim_point_in_time`: A joint reference point for all data

| snapshot\_date |
| -------------- |
| 2021-06-15     |
| 2022-01-01     |
| 2023-03-31     |

This is used to **filter time** centrally, so other joins respect that single reference point.

The three rows above are an excerpt. The table holds one row per date, and must contain every date a
user may select as a reference point.

<Tip>
  Same approach can be extended for any type of data versioning - not only for point in time.
</Tip>

#### Relations

**Fact to customers**

This relation decides which version of a customer an order version is attached to. There are two
useful answers, and they need different relations - see
[which version of a dimension a fact sees](#which-version-of-a-dimension-a-fact-sees). The relation
below attaches the customer as they were when the order version was written.

1. Join on customer key and validity ranges
2. Direction: Many to one (from `fact_sales` to `dim_customer`)
3. Cross-filtering is as needed (one-to-many or bi-directional), unless the domain holds more than
   one versioned fact - see [cross-filtering](#cross-filtering-between-versioned-entities) below

Relation:

```sql theme={null}
fact_sales.customer_id = dim_customer.customer_id
AND fact_sales.valid_from >= dim_customer.valid_from
AND fact_sales.valid_from <  dim_customer.valid_to
```

The result of that relation is that customers are resolved
to the right appropriate customer to the time of the order,
while keeping multiple versions of the order.

<Note>
  If a customer has multiple versions within the validity time of an order,
  it will not be resolved (i.e. will be resolved to NULL).
</Note>

**Entities to point in time reference**

1. `fact_sales` to `dim_point_in_time`:

2. Many to one (from `fact_sales` to point in time)

3. Cross-filtering is one-to-many (`dim_point_in_time` can filter the fact, but not vice versa)

Relation:

```sql theme={null}
dim_point_in_time.snapshot_date >= fact_sales.valid_from
dim_point_in_time.snapshot_date <  fact_sales.valid_to
```

2. `dim_customer` to `dim_point_in_time`:

3. Many to one (from `dim_customer` to point in time)

4. Cross-filtering is one-to-many (`dim_point_in_time` can filter `dim_customer`, but not vice versa)

Relation:

```sql theme={null}
dim_point_in_time.snapshot_date >= dim_customer.valid_from
dim_point_in_time.snapshot_date <  dim_customer.valid_to
```

<Note>
  The `dim_point_in_time` is a [shared dimension](/docs/advanced-modeling/shared-dimensions.mdx) that can filter all associated entities.

  Using [cross-filtering](/docs/modeling/relations#cross-filtering) one-to-many ensures that it will filter the entities, but will not be filtered by them.
</Note>

#### Ensuring Filtering for a Point in Time

When using SCD with multiple versions, data is duplicated for each snapshot. The semantic modeler must ensure that only one snapshot is selected to prevent double-counting.

<Tip>
  Automatic filtering can be done at the dashboard or BI report level. However, a semantic layer allows to enforce automatic
  filtering across all tools using the same semantics.
</Tip>

To ensure consistency at the semantic layer, model the reference point as two entities over the
same table:

| Entity                     | Relations                                          | Role                                                                                                          |
| -------------------------- | -------------------------------------------------- | ------------------------------------------------------------------------------------------------------------- |
| `dim_point_in_time`        | Shared dimension, joined to every versioned entity | A filter on it selects the reference point. Group by it for a series, with the scope selector described below |
| `dim_point_in_time_choice` | **None** - a disconnected entity                   | Offers the value list a user picks from. Nothing filters it, so it always lists every date                    |

Both read the same table. Neither holds the pin itself.

Then pin the reference point with a [source filter](/docs/domains#source-filters) - a domain filter
applied to a source table as it is read - on each versioned entity's own validity columns. This is
the same idiom as
[domain-level deduplication](/docs/advanced-modeling/multi-grain-tables#domain-level-deduplication)
for multi-grain data, applied to a validity range instead of a grain column:

```yaml theme={null}
type: domain
name: all

entities:
  # All entities accessible by the user
  - name: fact_sales
  - name: dim_customer
  - name: dim_point_in_time
  # An entity to choose the point in time snapshot
  - name: dim_point_in_time_choice

# Keep only the version of each row that was valid at the reference point.
# `dim_customer` is resolved through its join to the fact - see below.
source_filters:
  - name: fact_sales_as_of
    sql: |-
      fact_sales.valid_from <= COALESCE(
          GET_FIELD_SELECTION(dim_point_in_time.snapshot_date),
          GET_FIELD_SELECTION(dim_point_in_time_choice.snapshot_date),
          CURRENT_DATE)
      AND fact_sales.valid_to > COALESCE(
          GET_FIELD_SELECTION(dim_point_in_time.snapshot_date),
          GET_FIELD_SELECTION(dim_point_in_time_choice.snapshot_date),
          CURRENT_DATE)
```

The [`COALESCE` chain](/docs/advanced-modeling/conditional-filtering#multiple-fallback-tiers) resolves
the reference point in three tiers: a direct filter on `dim_point_in_time.snapshot_date` wins;
otherwise the value picked on `dim_point_in_time_choice`; otherwise today.

Having no relations, `dim_point_in_time_choice` cannot filter anything through a join.
`GET_FIELD_SELECTION` inspects the query's filters rather than following a join, which is how a
choice made on a disconnected entity reaches the pin at all, and how the first tier detects
`dim_point_in_time` even when that entity is not part of the query.

Detection does not replace a filter's ordinary effect. A filter on
`dim_point_in_time.snapshot_date` is read by the first tier **and** still filters that dimension as
any filter would, joining it into the query.

<Warning>
  Write the pin on the **fact's own** validity columns, not on `dim_point_in_time`.

  A source filter applies wherever its entity's data is read, so a filter on `fact_sales.valid_from`
  covers every query that reads `fact_sales`. A filter written on `dim_point_in_time.snapshot_date`
  instead is skipped entirely by a query that does not reach that dimension - and a query on
  `fact_sales` alone then returns every version, double-counting with nothing to indicate it.
</Warning>

A pin covers only the entity whose columns it names - these filters do not propagate. Above,
`dim_customer` carries no pin because the join resolves it per order version, which is the
as-written reading described below.

<Warning>
  A query on `dim_customer` alone is not covered by the fact's pin, and sees every version - three rows
  for two customers in the sample data. Pin each versioned entity that users query on its own, and use
  the as-of reading when you do, since a pinned dimension contradicts a join anchored on the fact.
</Warning>

<Note>
  Source filters are supported on attributes that come from an entity
  [source table](/docs/modeling/entities#source-table). Validity columns read from the table qualify; a
  validity range built as a calculated attribute does not.
</Note>

The last tier is a policy choice: `CURRENT_DATE` makes an unfiltered query mean "as of today",
while no default returns nothing until a reference point is chosen. With no default, a user who
forgot to filter gets an empty report rather than an error.

The default is compared directly against the validity columns, so `dim_point_in_time` needs no row
for it. A date a user filters on does need to be there, since that filter joins the dimension.

<Warning>
  `GET_FIELD_SELECTION` detects equality filters only. A query filtering the reference point to
  several dates or to a range is not detected, and resolves to the default - indistinguishable from an
  unfiltered query.

  So a series grouped by `dim_point_in_time.snapshot_date` over three dates is pinned to today, and
  returns only the dates at which today's versions were already valid. Earlier dates disappear
  entirely: no error, and no zero row.
</Warning>

To let a query widen the pin, add a two-value scope selector as a disconnected entity and branch on
it, as in [performance filtering](/docs/performance/filtering-and-sampling#force-performance-filters-with-conditional-filtering).
The second argument to `GET_FIELD_SELECTION` is the value to assume when the user picked nothing:

```yaml theme={null}
source_filters:
  - name: fact_sales_as_of
    sql: |-
      CASE GET_FIELD_SELECTION(dim_scope.scope, 'as_of')
        WHEN 'all_data' THEN TRUE
        -- the same two bounds as above
        ELSE fact_sales.valid_from <= COALESCE(
                 GET_FIELD_SELECTION(dim_point_in_time.snapshot_date),
                 GET_FIELD_SELECTION(dim_point_in_time_choice.snapshot_date),
                 CURRENT_DATE)
             AND fact_sales.valid_to > COALESCE(
                 GET_FIELD_SELECTION(dim_point_in_time.snapshot_date),
                 GET_FIELD_SELECTION(dim_point_in_time_choice.snapshot_date),
                 CURRENT_DATE)
      END
```

A query that sets `dim_scope.scope = 'all_data'` leaves every version in place. Grouped by
`dim_point_in_time.snapshot_date`, the range relation then resolves each date on its own and the
series is correct at every point.

<Tip>
  Point BI selectors at `dim_point_in_time_choice`, not at `dim_point_in_time`. A BI tool listing the
  values of a connected dimension receives only what the query left of it, while the disconnected copy
  always offers every date.
</Tip>

#### Which version of a dimension a fact sees

When both a fact and its dimension are versioned, "as of 2022-05-01" has two readings, and both are
worth having. Take order `5001`, whose current version was written on 2021-07-01, for a customer who
moved from East to West on 2022-03-01:

| Reading                                    | Fact to dimension relation                                               | Entities pinned        | Region for `5001` |
| ------------------------------------------ | ------------------------------------------------------------------------ | ---------------------- | ----------------- |
| The customer **as the order was written**  | Join on the key and the fact's `valid_from` within the dimension's range | The fact only          | `East`            |
| The customer **as of the reference point** | Join on the business key alone                                           | Every versioned entity | `West`            |

Pick per relation, by what the question means. An order's own history reads naturally as written -
the address a parcel actually went to. A current-state report reads as of the reference point - where
that customer is now.

<Warning>
  The relation and the pins must express the same reading. Anchoring the join on the fact's
  `valid_from` while also resolving the dimension to the reference point asks for two different
  versions at once: the join looks for the version valid when the fact was written, the dimension no
  longer offers it, and the dimension's attributes come back as `NULL` - or the fact row disappears
  altogether.

  For the as-written reading, pin only the fact, and do not let `dim_point_in_time` cross-filter the
  dimension. For the as-of reading, pin every versioned entity and join on the business key alone.
</Warning>

<Note>
  An SCD2 dimension's key is its surrogate key, so a business-key join is written as a
  [custom expression](/docs/modeling/relations#connecting-entities-using-a-custom-sql-expression)
  (`fact_sales.customer_id = dim_customer.customer_id`) rather than a field connection. Cardinality is
  not validated for expression joins - the pin is what leaves one version per key, so the pin is doing
  the work that makes the join safe.
</Note>

#### Cross-filtering between versioned entities

`dim_point_in_time` is a shared dimension that filters data, so set its relations to
[cross-filter](/docs/modeling/relations#cross-filtering) one-to-many: the reference dimension filters the
versioned entities and is never filtered back by them. Set this explicitly - the default is `both` -
and set it on every relation into the dimension. That holds for any dimension used to filter data.
`dim_point_in_time_choice` has no relations, so cross-filtering does not apply to it.

<Warning>
  With bi-directional cross-filtering, one versioned entity's filters reach `dim_point_in_time`, and
  through it every other entity joined to it. A filter on one fact then drops rows from another that
  are valid at the reference point, and the range of dates it leaves behind duplicates the rows that
  remain - the double-counting this section exists to prevent.

  It surfaces only for particular filter combinations, so a domain can test clean and still break
  later.
</Warning>

The same applies to any other dimension shared between versioned entities. Facts pinned on their own
columns do not filter each other. A shared dimension whose cross-filtering is bi-directional does
connect them: a filter on one fact then drops rows from the other wherever that dimension is part of
the query.

#### Example query

Status of all orders given reference point of `2021-06-15`

| order\_id | status  | amount | name        | region |
| --------- | ------- | ------ | ----------- | ------ |
| 5001      | Pending | 250    | Alice Smith | East   |

> * Only one valid version per `order_id` and `customer_id` is active per point-in-time
> * Any rows not yet valid are **excluded** (e.g. 5002 is not visible on 2021-06-15)

Status of all orders given reference point of `2022-05-01`

| order\_id | status  | customer\_id | order\_date | amount | name        | region |
| --------- | ------- | ------------ | ----------- | ------ | ----------- | ------ |
| 5001      | Shipped | 101          | 2021-06-10  | 300    | Alice Smith | East   |
| 5002      | Pending | 102          | 2022-01-15  | 300    | Bob Johnson | North  |

Summing `amount` over the same data:

| Reference point | Versions | Sum of `amount` |
| --------------- | -------- | --------------- |
| `2021-06-15`    | 1        | 250             |
| `2022-05-01`    | 2        | 600             |

In a domain with no pin at all, every version is counted and the sum is 850 - the double-counting the
pin prevents. Under the domain above, a user who filters nothing still gets a pinned result, not 850.

<Tip>
  * Use `dim_point_in_time` to **anchor the reference date**
  * Join facts and dimensions based on **SCD2 validity ranges**
  * Works seamlessly for time travel, reproducible snapshots, or data backfills
</Tip>