# Validity windows pattern

> Learn how to match each fact row to the dimension row whose validity window contains it, such as price bands and SCD Type 2 history

## What is a validity window?

A validity window is a dimension row that is only true for a period of time. Each row stores both ends of that period, a `valid_from` and a `valid_to`.

For example, finance keeps a `price_bands` table. Each band has a rate per currency that applies from one date until the next band starts. Every order should pick up the one band it falls in.

The same shape appears in many places:

- Price lists, tax rates and FX rates with an effective date range
- Slowly changing dimensions (SCD Type 2), where each version of a customer row has `valid_from` and `valid_to`
- Contract terms, territory assignments and plan tiers

<ReactFlowDiagram id="erd-validity-windows" height={240} fixed />

**Sample data:**

`price_bands`

| currency | valid_from | valid_to | rate |
|----------|------------|----------|------|
| USD | 2024-01-01 | 2024-03-01 | 1.00 |
| USD | 2024-03-01 | 2024-06-01 | 1.10 |
| USD | 2024-06-01 | NULL | 1.20 |
| EUR | 2024-02-01 | NULL | 0.90 |

`orders`

| id | currency | ordered_at |
|----|----------|------------|
| o1 | USD | 2024-02-15 |
| o2 | USD | 2024-03-01 |
| o3 | USD | 2023-12-31 |
| o4 | EUR | 2024-05-01 |
| o5 | USD | 2024-07-01 |

<ReactFlowDiagram id="validity-windows-price-bands" height={360} fixed />

`o2` falls exactly on the 1 March boundary. `o3` is earlier than every USD band.

The question: **how many orders were placed at each rate?**

## Solution: a `range` relationship

Declare a `range` relationship. The `on {}` block holds the equality (currency), and `match` holds the window test.

### Step 1: Define models

```aml title="orders.model.aml"
Model orders {
  type: 'table'
  table_name: 'ecommerce.orders'
  data_source_name: 'your_datasource_name'

  dimension id {
    type: 'text'
    primary_key: true
  }

  dimension currency { type: 'text' }
  dimension ordered_at { type: 'date' }

  measure order_count {
    label: 'Order Count'
    type: 'number'
    definition: @aql count(orders.id);;
  }
}
```

```aml title="price_bands.model.aml"
Model price_bands {
  type: 'table'
  table_name: 'finance.price_bands'
  data_source_name: 'your_datasource_name'

  dimension currency { type: 'text' }
  dimension valid_from { type: 'date' }
  dimension valid_to { type: 'date' }
  dimension rate { type: 'number' }
}
```

### Step 2: Define the relationship

```aml title="order_price_band.relationship.aml"
Relationship order_price_band {
  type: 'range'
  on { from: r(orders.currency) to: r(price_bands.currency) }
  match: @aql
    // The order date must fall within the validity window
    orders.ordered_at >= price_bands.valid_from
      // The end of the window is exclusive (half-open interval)
      and orders.ordered_at < coalesce(price_bands.valid_to, @9999-12-31)
  ;;
}
```

- The window is half-open: `>=` at the start, `<` at the end. Two adjacent bands can share a boundary date without overlapping, and an order on that date goes to the later band.
- `coalesce` turns an open-ended band (`valid_to` is `NULL`) into one that runs forever. Without it, the comparison against `NULL` matches nothing.
- `range` claims each order matches at most one band, the same as `many_to_one`. That claim holds because bands within one currency do not overlap.

### Step 3: Add it to a dataset

```aml title="finance.dataset.aml"
Dataset finance {
  data_source_name: 'your_datasource_name'

  models: [orders, price_bands]

  relationships: [
    relationship(order_price_band, true)
  ]
}
```

### Step 4: Query

```aml
explore {
  dimensions { rate: price_bands.rate }
  measures { orders: count(orders.id) }
}
```

Holistics puts the equality and the window test in the `ON` clause:

```sql
LEFT JOIN price_bands
  ON  orders.currency   = price_bands.currency
  AND orders.ordered_at >= price_bands.valid_from
  AND orders.ordered_at <  coalesce(price_bands.valid_to, DATE '9999-12-31')
```

**Result:**

| rate | orders | which orders |
|------|--------|--------------|
| 0.90 | 1 | o4 |
| 1.00 | 1 | o1 |
| 1.10 | 1 | o2, on the boundary, goes to the later band |
| 1.20 | 1 | o5 |
| NULL | 1 | o3, earlier than every USD band |

Five orders, five rows. Each order lands in exactly one band, and the total matches the `orders` table.

## SCD Type 2: current version only

An SCD Type 2 dimension keeps every version of a row. Use the `range` relationship above when facts need the attributes **as of** their own date.

When a report only needs the **current** version, filter the `to` side instead. This is an ordinary `many_to_one` with a `where {}` block:

```aml title="order_customer_current.relationship.aml"
Relationship order_customer_current {
  type: 'many_to_one'
  on { from: r(orders.customer_id) to: r(dim_customers.customer_id) }
  where { to: @aql dim_customers.is_current == true ;; }
}
```

The filter sits on the relationship, not the model. A second, point-in-time relationship into `dim_customers` can still see every version.

## Validity windows or interval overlap?

Both patterns use a date inequality. The difference is how many dimension rows one fact row can match.

| Each fact row matches... | Example | Pattern |
|--------------------------|---------|---------|
| at most one dimension row | an order and its price band | Validity windows (`range`) |
| any number of dimension rows | an order and every day it is open | [Interval overlap](/docs/modeling/modeling-patterns/interval-overlap) (`many_to_many`) |

## Key points

- **Non-overlap is your claim.** Holistics does not check that windows within a partition do not overlap. Overlapping windows fan out and return wrong numbers, the same as a false `many_to_one`.
- **Handle open ends explicitly.** Wrap a nullable end in `coalesce`, or test it with `is null`. A bare comparison against `NULL` drops the row.
- **Unmatched rows are kept.** A fact row that falls in no window reports under `NULL` instead of disappearing.
- **Works on every database.** The emitted SQL is a plain range join.

## Additional resources

- [Back to overview](/docs/modeling-patterns)
- [Polymorphic associations pattern](/docs/modeling/modeling-patterns/polymorphic-associations)
- [Relationships overview](/docs/relationships)
- [AML Relationship reference](/reference/aml/relationship)