Validity windows pattern

What is a validity window?Copy Anchor Link

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

Sample data:

price_bands

orders

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 relationshipCopy Anchor Link

Declare a range relationship. from and to name a unique column on each side. match holds the whole join condition: the currency equality and the window test.

Step 1: Define modelsCopy Anchor Link

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

Model price_bands {
  type: 'table'
  table_name: 'finance.price_bands'
  data_source_name: 'your_datasource_name'

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

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

Step 2: Define the relationshipCopy Anchor Link

Relationship order_price_band {
  type: 'range'
  from: r(orders.id)
  to: r(price_bands.id)
  match: @aql
    // The order and the band must use the same currency
    orders.currency == price_bands.currency
      // The order date must fall within the validity window
      and 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 datasetCopy Anchor Link

Dataset finance {
  data_source_name: 'your_datasource_name'

  models: [orders, price_bands]

  relationships: [
    relationship(order_price_band, true)
  ]
}

Step 4: QueryCopy Anchor Link

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

AMQL 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:

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

SCD Type 2: current version onlyCopy Anchor Link

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:

Relationship order_customer_current {
  type: 'many_to_one'
  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?Copy Anchor Link

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

Key pointsCopy Anchor Link

• Non-overlap is your claim. AMQL 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 resourcesCopy Anchor Link

• Back to overview
• Polymorphic associations pattern
• How joins work
• AML Relationship reference