Snapshots (SCD Type 2)

Snapshot models maintain historical row versions with validity windows. They answer questions like: what does this entity look like now, what did it look like before, when did it change, and was it absent during a period.

How snapshots work

SQLBuild adds two generated columns to the target table:

Column	Meaning
`valid_from`	When this version became valid (inclusive)
`valid_to`	When this version stopped being valid (exclusive). `NULL` means currently active.

A point-in-time query uses the interval valid_from <= point_in_time < valid_to.

Change detection strategies

Timestamp strategy

Use when your source has a reliable column recording when the entity changed.

MODEL (
  materialized snapshot,
  unique_key [customer_id],
  snapshot_strategy timestamp,
  updated_at updated_at,
);

SELECT
  customer_id,
  name,
  plan,
  status,
  updated_at
FROM __source("customers")

If the source updated_at is newer than the active target row’s updated_at, SQLBuild closes the old version and inserts the new one. If updated_at is unchanged or older, nothing happens.

Check strategy

Use when the source does not have a reliable update timestamp.

MODEL (
  materialized snapshot,
  unique_key [customer_id],
  snapshot_strategy check,
  check_columns [name, plan, status],
);

SELECT
  customer_id,
  name,
  plan,
  status
FROM __source("customers")

SQLBuild compares check_columns between source and active target rows. If any checked value differs, a new version is created. Changes to unchecked columns are ignored. check_columns [*] checks all output columns except unique_key and the generated validity columns. Explicit columns are recommended for important models to avoid noisy history from volatile metadata columns.

Historical input

By default, SQLBuild treats the model query as returning the current state of each entity (one row per unique_key). When your source contains historical observations over time, add observed_at to switch to historical mode.

Historical check snapshot

Use for daily full exports or periodic snapshots without a business update timestamp.

MODEL (
  materialized snapshot,
  unique_key [customer_id],
  snapshot_strategy check,
  check_columns [plan, status],
  observed_at snapshot_date,
);

SELECT
  customer_id,
  plan,
  status,
  snapshot_date
FROM __source("customers_daily_snapshot")

Each observed_at group is treated as a complete picture of the source at that time. Consecutive unchanged observations are collapsed into a single version.

Historical timestamp snapshot

Use for historical observations that include a business update timestamp.

MODEL (
  materialized snapshot,
  unique_key [customer_id],
  snapshot_strategy timestamp,
  updated_at updated_at,
  observed_at extract_date,
  historical_input snapshot,
);

SELECT
  customer_id,
  plan,
  status,
  updated_at,
  extract_date
FROM __source("customers_historical_extracts")

Each row means: “at extract_date, the source’s current state for this key had this updated_at.” Validity windows use updated_at, not observed_at.

Historical change records

Use for CDC tables, audit logs, or historical backfills where rows are individual version records.

MODEL (
  materialized snapshot,
  unique_key [customer_id],
  snapshot_strategy timestamp,
  updated_at updated_at,
  observed_at loaded_at,
  historical_input changes,
);

SELECT
  customer_id,
  plan,
  status,
  updated_at,
  loaded_at
FROM __source("customers_cdc")

Multiple changes for the same key in one batch are allowed. updated_at determines version ordering. observed_at is arrival/load time, not validity time.

Historical input rules

Strategy	`historical_input`	Source shape	Uniqueness	Hard deletes
`check`	`snapshot` (default)	Complete observations over time	`unique_key + observed_at`	Allowed
`timestamp`	`snapshot`	Complete observations with update timestamp	`unique_key + observed_at`	Allowed
`timestamp`	`changes`	Individual change/version records	`unique_key + updated_at`	Not allowed
`check`	`changes`	Not supported	-	-

Timestamp snapshots with observed_at require historical_input to be set explicitly.

Hard deletes

invalidate_hard_deletes true,

When enabled, active target rows whose keys are missing from the source are closed:

Current-state input: closed at execution time
Historical input (historical_input snapshot): closed at the observed_at time of the group where the key is missing

Hard deletes are not allowed with historical_input changes because change-record batches are not complete source snapshots - a missing key just means no change, not deletion. Reappearing keys create a new active version.

Configuration reference

Field	Required	Description
`materialized snapshot`	Yes	Enables snapshot lifecycle
`unique_key`	Yes	Column(s) identifying one entity
`snapshot_strategy`	Yes	`timestamp` or `check`
`updated_at`	Timestamp only	Source column with business update time
`check_columns`	Check only	Columns compared to detect changes. Use `[*]` for all non-key columns.
`observed_at`	No	Source column with observation/extract time. Presence enables historical mode.
`historical_input`	Conditional	`snapshot` or `changes`. Required for timestamp with `observed_at`. Defaults to `snapshot` for check with `observed_at`.
`invalidate_hard_deletes`	No	Close active rows missing from source. Default `false`.
`valid_from_column`	No	Override generated column name. Default `valid_from`.
`valid_to_column`	No	Override generated column name. Default `valid_to`.
`initial_valid_from`	No	First-version start time: `updated_at`, `observed_at`, or `execution_time`. See defaults below.
`snapshot_full_refresh`	No	Model-level full-refresh safety: `deny`, `require_confirmation`, or `allow`.

Initial valid_from defaults

Case	Default
Timestamp, no `observed_at`	`updated_at`
Timestamp, with `observed_at`	`updated_at`
Check, no `observed_at`	`execution_time`
Check, with `observed_at`	`observed_at`

Full refresh safety

Snapshot full refresh can permanently discard history that cannot be reconstructed from the source. SQLBuild guards against this with configurable safety policies.

Project config

[snapshots]
current_state_full_refresh = "deny"
historical_full_refresh = "require_confirmation"

Policy	Behavior
`deny`	Full refresh is blocked regardless of CLI flags
`require_confirmation`	Requires `--allow-snapshot-full-refresh` flag or interactive confirmation
`allow`	No snapshot-specific confirmation required

Defaults

Snapshot type	Default policy	Reason
Current-state (no `observed_at`)	`deny`	Cannot reconstruct older history from current-state source
Historical (with `observed_at`)	`require_confirmation`	Can reconstruct if query returns full history, but SQLBuild cannot prove that

Model override

The model snapshot_full_refresh field can only make the policy stricter than the project setting. A model cannot weaken deny to allow.

MODEL (
  materialized snapshot,
  ...
  snapshot_full_refresh deny,
);

CLI usage

# Fails if any selected snapshot has effective policy 'deny'
sqb build --full-refresh

# Satisfies 'require_confirmation' policy (cannot override 'deny')
sqb build --full-refresh --allow-snapshot-full-refresh

Audits

Snapshot models support the same audit system as other materializations. Audits with delta_and_final run scope execute against the snapshot delta relation before target mutation, blocking promotion if an error-severity audit fails. Final audits run after target mutation.

MODEL (
  materialized snapshot,
  unique_key [customer_id],
  snapshot_strategy timestamp,
  updated_at updated_at,
  columns (
    customer_id (audits [not_null (run_scope delta_and_final)]),
  ),
);

Duplicate handling

SQLBuild fails with an actionable error if the source query produces duplicate rows at the snapshot identity grain:

Current-state: duplicate unique_key
Historical snapshot: duplicate unique_key + observed_at
Historical changes: duplicate unique_key + updated_at

Deduplicate in your model SQL:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id, snapshot_date
      ORDER BY loaded_at DESC
    ) AS rn
  FROM __source("customer_daily")
)

SELECT customer_id, plan, status, snapshot_date
FROM ranked
WHERE rn = 1

Querying snapshots

Current rows

SELECT * FROM customer_snapshot WHERE valid_to IS NULL

Point-in-time

SELECT *
FROM customer_snapshot
WHERE customer_id = 1
  AND TIMESTAMP '2026-02-15' >= valid_from
  AND (valid_to IS NULL OR TIMESTAMP '2026-02-15' < valid_to)

Fact-to-dimension historical join

SELECT
  o.order_id,
  o.ordered_at,
  c.plan
FROM orders o
JOIN customer_snapshot c
  ON o.customer_id = c.customer_id
 AND o.ordered_at >= c.valid_from
 AND (c.valid_to IS NULL OR o.ordered_at < c.valid_to)

Examples

Composite key

MODEL (
  materialized snapshot,
  unique_key [user_id, role_id],
  snapshot_strategy check,
  check_columns [role_name, role_status],
  observed_at snapshot_date,
  invalidate_hard_deletes true,
);

SELECT
  user_id, role_id, role_name, role_status, snapshot_date
FROM __source("user_role_daily")

Custom validity column names

MODEL (
  materialized snapshot,
  unique_key [product_id],
  snapshot_strategy timestamp,
  updated_at modified_at,
  valid_from_column effective_from,
  valid_to_column effective_to,
);

SELECT
  product_id, name, price, modified_at
FROM __source("products")

​How snapshots work

​Change detection strategies

​Timestamp strategy

​Check strategy

​Historical input

​Historical check snapshot

​Historical timestamp snapshot

​Historical change records

​Historical input rules

​Hard deletes

​Configuration reference

​Initial valid_from defaults

​Full refresh safety

​Project config

​Defaults

​Model override

​CLI usage

​Audits

​Duplicate handling

​Querying snapshots

​Current rows

​Point-in-time

​Fact-to-dimension historical join

​Examples

​Composite key

​Custom validity column names