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

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.