Validity windows pattern

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

Range relationship between orders and price_bands

[HTML node] — orders: highlighted columns take part in the relationship
[HTML node] — price_bands: highlighted columns take part in the relationship

orders → price-bands (range currency = currency valid_from <= ordered_at < valid_to)

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

Which price band each order lands in

Date
USD bands
USD orders
EUR bands
EUR orders
1 Dec
1 Jan
1 Feb
1 Mar
1 Apr
1 May
1 Jun
1 Jul
1 Aug
1 Sep
USD 1.00 — valid_from 2024-01-01, valid_to 2024-03-01
USD 1.10 — valid_from 2024-03-01, valid_to 2024-06-01
USD 1.20 (open-ended) — valid_from 2024-06-01, valid_to NULL
EUR 0.90 (open-ended) — valid_from 2024-02-01, valid_to NULL
o3 — ordered_at 2023-12-31: before every USD band, reports under NULL
o1 — ordered_at 2024-02-15
o2 — ordered_at 2024-03-01: >= valid_from of 1.10, not < valid_to of 1.00
o5 — ordered_at 2024-07-01
o4 — ordered_at 2024-05-01

o1 → usd-100
o2 → usd-110
o5 → usd-120
o4 → eur-090

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

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);;
  }
}

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

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

finance.dataset.aml
Dataset finance {
  data_source_name: 'your_datasource_name'

  models: [orders, price_bands]

  relationships: [
    relationship(order_price_band, true)
  ]
}

Step 4: Query

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

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

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:

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 (`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.

What is a validity window?​

Solution: a range relationship​

Step 1: Define models​

Step 2: Define the relationship​

Step 3: Add it to a dataset​

Step 4: Query​

SCD Type 2: current version only​

Validity windows or interval overlap?​

Key points​

Additional resources​