Point-in-time snapshots with parameters

Sometimes you need to answer “as of” questions like Which reservations were active last Tuesday? or What did our pipeline look like at quarter-end? This guide shows how to build a parameterized query view that filters data to a specific point in time — and allows users to pick that date themselves.

Requirements

To follow the steps in this guide, you’ll need:

A table with start and end date (or datetime) fields — for example, check_in_date and check_out_date
Access to the model in your Omni instance

How it works

A query view is a SQL view defined in the model that can accept parameter values at query time. Instead of hardcoding a date in SQL, the selected date is injected directly into the WHERE clause. When the user changes the parameter, the query view regenerates automatically.

The core pattern

WHERE {{filters.your_view_name.snapshot_date.value}}::DATE
      >= your_table.start_date::DATE
  AND {{filters.your_view_name.snapshot_date.value}}::DATE
      < your_table.end_date::DATE

In the above example, only the records that were “active” on the selected date will be returned, meaning they had started but not yet ended.

The ::DATE cast strips time information from your timestamps. This prevents mismatches when your datetime fields include time components like 2024-02-15 14:30:00

Set up the query view

Define the query view

In the workbook, create the query from a new SQL tab. Once the query is created, save as query view. Note: This particular pattern focuses on SQL based query views
Define a filter parameter for the snapshot date. The syntax for injecting parameter values follows this pattern:
{{ filters.<view_name>.<parameter_name>.value }}
For example, if the view is named snapshots and the parameter is snapshot_date:
{{ filters.snapshots.snapshot_date.value }}::DATE
Verify that the filter parameter syntax is correct, including that the view name and parameter name match exactly. Incorrect syntax is the most common cause for issues.

Using the above-mentioned syntax, add the newly created parameter to the query view’s SQL to inject the parameter’s value. When building the WHERE clause, use < (not <=) for the end date comparison. For example, a reservation that checks out on Feb 17th shouldn’t appear in a Feb 17th snapshot.

snapshots.query.view

sql: |
  SELECT *
  FROM your_schema.your_table
  WHERE {{filters.snapshots.snapshot_date.value}}::DATE
        >= your_table.start_date::DATE
    AND {{filters.snapshots.snapshot_date.value}}::DATE
        < your_table.end_date::DATE

dimensions:
  reservation_date: {}
  check_in_datetime: {}
  check_out_datetime: {}
## etc...
filters:
  snapshot_date:
    type: timestamp
    description: "The date to snapshot active records"

You can also suggest from a list of timestamps using suggestion_list or optionally set a value as the default filter, for example:

filters:
  snapshot_date:
    type: string
    suggestion_list:
      - value: 2024-02-01 00:00:00 -00:00
    default_filter:
      is: 2024-02-01 00:00:00 -00:00

Which would then look like this in the workbook:

Add the view to your topic

In the topic file, add the query view as a join (as shown) or include it as a standalone view so users can access it in workbooks:

Test the snapshot

Open a workbook and navigate to your topic.
Add the snapshot_date parameter filter to the query.
Enter a date into the filter and run the query. You should see only the records that were active on that date:

If counts look off, check that you’re using count_distinct on your ID field — without it, records that span multiple days can be counted more than once.

Bonus: Populate the date picker with a list of dates

By default, the snapshot_date parameter accepts free-text input. If you want users to select from a list of valid dates instead, you can use suggest_field to populate the dropdown from a calendar view.

Create a calendar view

A calendar view is a query view that generates a list of dates. Most databases support a recursive CTE approach:

The SQL syntax for recursive CTEs varies by database. Click the tab for your database to view the correct SQL.

# calendar.query.view
sql: |
  WITH RECURSIVE dates AS (
    SELECT '2020-01-01'::TIMESTAMP AS date
    UNION ALL
    SELECT DATEADD(day, 1, date) AS date
    FROM dates
    WHERE date < '2029-12-31'::TIMESTAMP
  )
  SELECT date FROM dates

dimensions:
  date:
    sql: ${date}
    type: date

# calendar.query.view
sql: |
  WITH RECURSIVE rec_cte AS (
    SELECT '2020-01-01'::TIMESTAMP AS DATE
    UNION ALL
    SELECT DATE + INTERVAL '1 day'
    FROM rec_cte
    WHERE DATE < '2029-12-31'::TIMESTAMP
  )
  SELECT DATE FROM rec_cte

dimensions:
  date:
    sql: '"DATE"'
    type: date

# calendar.query.view
sql: |
  SELECT date
  FROM UNNEST(
    GENERATE_DATE_ARRAY('2020-01-01', '2029-12-31', INTERVAL 1 DAY)
  ) AS date

dimensions:
  date:
    sql: "'date'"
    type: date

# calendar.query.view
sql: |
  SELECT generate_series::DATE AS DATE
  FROM generate_series(
    '2020-01-01'::TIMESTAMP,
    '2029-12-31'::TIMESTAMP,
    INTERVAL '1 day'
  )

dimensions:
  date:
    sql: "'DATE'"
    type: date

Point parameter at the calendar

Update the snapshot parameter to pull suggestions from the calendar view:

filters:
  snapshot_date:
    type: timestamp
    description: "The date to snapshot active records"
    suggest_field: calendar.date

Now when users click the snapshot_date parameter, they’ll see a dropdown populated with dates from your calendar view instead of a blank text field or suggestions from the specific suggestion_list.