as-of is not the same as current

quick answer

#

current means “what is true right now.” as-of means “what was true at a specific timestamp or calendar date.” if you filter to the current row when the business question is historical, you will ship wrong metrics that still pass compile and sometimes still pass naive row-count tests. fix the time question first, then pick one of three patterns: filter on a validity interval, pass an as-of parameter into the query, or pre-resolve the correct row in a cte before you join.

who this is for

#

• audience: analysts, analytics engineers, and backend developers who build reports or models over changing attributes
• prerequisites: comfort with select, join, and basic slowly changing data (old value replaced by new value over time)
• when to use this guide: when someone asks for a report “as of last quarter close,” “at time of order,” or “what tier were they on that day”

why this matters

#

the distinction can be subtle. the query runs, the dashboard loads, and the numbers look plausible because most rows are unchanged. only the rows that crossed a boundary on the wrong side of your filter are wrong, and those errors show up late, in a reconciliation, or in an audit.

grain is the other half of the story. an as-of join that matches more than one historical version for the same key does not always error. it multiplies rows and downstream distinct counts drift in ways that are hard to spot without a keyed duplicate check.

step-by-step

#

1) decide the event you are anchoring to

#

every as-of query needs an anchor:

• order placed at order_ts: what was the customer’s discount tier when they checked out?
• invoice dated invoice_date: what list price applied on that calendar day?
• status on report_date: how many accounts were active at month end, not how many are active today?

write the anchor down in plain language before you write sql. if you cannot state it in one sentence, the model is not ready.

2) pick a pattern

#

pattern a: validity interval on the history table

#

when history is stored as versions with valid_from and valid_to (or dbt_valid_from / dbt_valid_to on a snapshot), the as-of predicate belongs on that table:

select
    o.order_id,
    h.tier_code
from orders o
inner join customer_tier_history h
    on h.customer_id = o.customer_id
   and o.order_ts >= h.valid_from
   and o.order_ts < h.valid_to;

this is the same idea i use when i talk about point-in-time filters in keep your snapshots simple, without turning every problem into a snapshot.

pattern b: pass the as-of timestamp as a parameter

#

when many reports share one close date, define the as-of once (a reporting parameter, a macro variable, or a single bind variable) and reuse it everywhere that report touches history:

-- :as_of_ts supplied by the job or the app
select account_id, status_code
from account_status_history
where valid_from <= :as_of_ts
  and valid_to > :as_of_ts;

consistency matters more than cleverness. one close date used two different ways in two models is a common source of “the dashboards disagree.”

pattern c: resolve as-of rows in a cte, then join

#

when the join tree is wide or optional paths exist, pre-filter history to one row per business key at the as-of moment in a cte, then join the cte. that is the same structural move as left join an effective satellite without duplicating rows, even if you are not using data vault vocabulary.

with customer_tier_as_of as (
    select
        customer_id,
        tier_code
    from customer_tier_history
    where valid_from <= :as_of_ts
      and valid_to > :as_of_ts
)
select
    o.order_id,
    t.tier_code
from orders o
left join customer_tier_as_of t
    on t.customer_id = o.customer_id;

use pattern c when a flat join list keeps matching multiple versions or when optional relationships make grain hard to reason about.

3) validate the result

#

run checks that fail on the mistakes as-of sql hides:

• keyed duplicate count: for the grain you are verifying (for example one row per order_id), count(*) - count(distinct order_id) should be zero
• spot cases on boundaries: pick rows where the anchor timestamp sits exactly on valid_from or one second before valid_to
• compare current vs as-of on a fixed date: for a frozen test date, hand-count a handful of keys and compare to the query

if validation only checks “non-empty result,” you have not tested the time logic.

faq

#

what is the most important caveat?

#

open-ended intervals need a project rule. some teams use valid_to = '9999-12-31' for the current row, others use null. the predicate must match every other query that reads the same history, or as-of and current reports will diverge for the same key.

what should i do first?

#

write the anchor sentence, then grep the codebase for is_current, current_row, or valid_to is null on the same entity. if the report question contains “at time of” or “as of,” those filters are often the wrong tool.

related reading

#

• what is sql and why it still works
• keep your snapshots simple
• left join an effective satellite without duplicating rows (use a cte)
• sql series