Point-in-time views

A point-in-time view answers a single question: what did this row look like at a chosen instant? That instant is the as-of instant, and the source is a history table that keeps every version as its own insert-only row, such as a satellite or a persistent staging table. The view designer resolves the join for you and shows exactly the SQL it will save.

Why a plain date filter is not enough

The obvious first attempt is a filter like load_date <= as-of. It looks right and it is wrong: it returns every version loaded at or before the as-of instant, not the latest one. Join a hub to a satellite with three historical versions and that filter returns three rows for one key, a fan-out that inflates every downstream count and silently breaks any query expecting one row per key.

The view designer never emits that filter. It picks the latest version as of the instant, using one of two shapes depending on what exists in your catalog.

What the designer emits, and when

When a companion end-dated view exists

If your organization manages a current-and-end-dated view pair for the history table, the designer joins the end-dated companion instead of the raw table. That companion view already carries a never-null effective-to column, so the join uses a simple range test: the row’s effective-from is at or before the as-of instant, and its effective-to is either null or after it. No extra machinery is needed because the companion view already did the work of stamping each version with its valid range.

When it does not: the ranked subquery

Most satellites and persistent staging tables carry only a load timestamp, no end date. For those, the designer builds a portable ranked subquery: filter to rows loaded at or before the as-of instant, number them per join key from most recent to oldest using a window function, and keep only the row numbered first. The helper column that carries the row number is named dv_row_number.

The designer avoids QUALIFY for this filter. QUALIFY reads cleanly, but Fabric does not support it, and the same generated SQL needs to run unchanged on Databricks, Snowflake, and Fabric. A nested subquery with an outer WHERE dv_row_number = 1 does the same job everywhere.

Here is the SELECT at the heart of the CREATE VIEW statement the designer shows in Preview for a hub joined to one satellite with no companion view:

SELECT
  hub.customer_bk AS customer_bk,
  sat.first_name AS first_name
FROM dv.silver.hub_customer hub
LEFT JOIN (
  SELECT ranked.*
  FROM (
    SELECT s.*,
      ROW_NUMBER() OVER (
        PARTITION BY s.hk_customer
        ORDER BY s.dv_row_load_ts DESC
      ) AS dv_row_number
    FROM dv.silver.sat_customer_details s
    WHERE s.dv_row_load_ts <= current_timestamp
  ) ranked
  WHERE ranked.dv_row_number = 1
) sat
  ON hub.hk_customer = sat.hk_customer
WHERE hub.is_active = 1

Read it from the inside out: the innermost query numbers every version of a customer’s satellite row by load timestamp, the middle query keeps only the row numbered first, and the outer join attaches that single current-as-of-now row to the hub on the hash key. Change the as-of instant and the same shape picks a different row, never more than one.

Control the as-of instant

The As of field in the View section of the designer’s Details tab sets the instant every temporal join in the view resolves against. It defaults to Current timestamp, which evaluates the view fresh at query time, but you can pin it to a specific date and time or supply a custom SQL expression such as a column reference. See Name the view and set the basics for the full set of options.

Related pages

• Create a view from the canvas: the designer this page documents a piece of, including how related tables and joins are added.
• Point in time tables: a physical table with one row per key per snapshot date, built once and queried with a plain join. A point-in-time view computes the as-of row at query time instead, with no separate object to maintain.