Documentation Index
Fetch the complete documentation index at: https://docs.sqlbuild.com/llms.txt
Use this file to discover all available pages before exploring further.
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. |
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")
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")
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.
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")
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")
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")
updated_at determines version ordering. observed_at is arrival/load time, not validity time.
| 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 | - | - |
observed_at require historical_input to be set explicitly.
Hard deletes
invalidate_hard_deletes true,
- 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")
