Snapshot Measures

Capture point-in-time values for inventory and balance analysis.

Overview

Snapshot measures capture values at specific points in time (beginning or ending of a period). They’re useful for stateful quantities like inventory, account balances, or membership counts, cases where summing across days is not meaningful, and you care about the value at the boundary of a period.

Requirements

Table must set snapshot – Names the date dimension that drives snapshot behavior.
Measure must specify snapshot – ending or beginning.

The table’s snapshot dimension can:

Reference a date dimension defined on the same table, or
Refer to a date dimension in the table’s universe, as long as the association is unambiguous (for example, a single “As Of Date” dimension that always joins to this fact).

Table Configuration

Set the snapshot date dimension on the table:

name: Inventory
physical_name: inventory
datasource: warehouse
cost: 100
snapshot: Date  # Required for snapshot measures: the date dimension the table is snapped by

fields:
  - type: dimension
    name: Date
    data_type: date
    expression:
      sql: snapshot_date

Measure Configuration

Ending Snapshot

Capture value at the end of the period:

- type: measure
  name: Ending Inventory
  data_type: integer
  snapshot: ending
  expression:
    sql: sum(quantity)

Beginning Snapshot

Capture value at the beginning of the period:

- type: measure
  name: Beginning Inventory
  data_type: integer
  snapshot: beginning
  expression:
    sql: sum(quantity)

How It Works

When a spec projects a snapshot measure:

The planner identifies the snapshot date dimension for the table.
It filters rows to the requested time period (for example, a specific month or week).
It chooses the appropriate snapshot within each bucket:
- snapshot: ending → the last snapshot in the bucket.
- snapshot: beginning → the first snapshot in the bucket.
It generates SQL that aggregates the underlying values at those boundary timestamps only, rather than summing every row in the period.

This means that:

Grouping by a period (day/week/month/etc.) yields “as of end of period” or “as of start of period” values.
Grouping by non-time dimensions (for example, Product or Store) still respects the snapshot semantics as long as the query includes a time filter or grain.

Use Cases

Inventory Analysis

name: Inventory
snapshot: Date

fields:
  - type: measure
    name: Ending Inventory
    snapshot: ending
    expression:
      sql: sum(quantity)

Account Balances

name: Accounts
snapshot: Date

fields:
  - type: measure
    name: Ending Balance
    snapshot: ending
    expression:
      sql: sum(balance)

Complete Example

name: Inventory
physical_name: inventory
datasource: warehouse
cost: 100
snapshot: Date

fields:
  - type: dimension
    name: Date
    data_type: date
    expression:
      sql: snapshot_date

  - type: dimension
    name: Product ID
    data_type: integer
    expression:
      sql: product_id

  - type: measure
    name: Beginning Inventory
    data_type: integer
    snapshot: beginning
    expression:
      sql: sum(quantity)

  - type: measure
    name: Ending Inventory
    data_type: integer
    snapshot: ending
    expression:
      sql: sum(quantity)

Best Practices

Set snapshot on the table – Required; keep the association with the driving date dimension clear.
Use descriptive names – e.g. “Ending Inventory” not just “Inventory”.
Document purpose – Note that the measure is point-in-time and should not be summed across long ranges.
Test the SQL – Add a tests/*.yml projecting the snapshot measure by month and assert the boundary-date subquery is there; then compare daily snapshots against month-end values in your warehouse.