Virtual builds create versioned physical relations and update VDE pointer sets. The build lifecycle is the same as standard mode (seeds, tests, models, audits), but model outputs are written to versioned physical tables and exposed through logical VDE views.
Virtual builds run ingress (loaders and Python nodes that feed sources) as a separate phase before SQL model execution. This means independent SQL models that do not depend on loaders will wait for all ingress to complete before starting. Standard mode does not have this limitation. This keeps VDE state persistence simpler and safer but may add wall time when ingress is slow and independent SQL work is available. A future optimization may allow overlapping ingress with independent SQL execution.
Default VDE
When you run sqb build in virtual mode without --virtual-env, the VDE name defaults to the active physical target name:
If the active environment is dev, this builds into VDE dev. On first run, it creates physical version relations and logical VDE views for all models.
Explicit VDE
Use --virtual-env to build into a named VDE:
sqb build --virtual-env pr_123
pr_123 does not exist, SQLBuild creates it by inheriting refs from the baseline VDE (the default VDE for the active physical target). Unchanged models share the same physical relations as the baseline - no data is copied.
Only models with changed code, config, or upstream versions get new physical version relations.
Physical naming
Physical version relations are stored in a dedicated schema:
<base_schema>__sqb_physical.<model_name>__v_<hash>
dev__sqb_physical.fact_orders__v_8f3a9c12
Logical VDE views are created in a VDE-suffixed schema:
<base_schema>__<vde_name>.<model_name>
dev__pr_123.fact_orders
When unsuffixed_virtual_env is configured, the named VDE uses the base schema directly:
VDE status
| Status | Meaning |
|---|
finalized | All models match their expected version hashes. The VDE is coherent and eligible for promotion. |
active | Some models are stale (partial build or pending changes). The VDE is a work in progress. |
detached | The VDE has been detached via sqb state detach. Build, promote, and rollback are blocked. |
Partial builds
Build a subset of models with --select:
sqb build --virtual-env pr_123 --select fact_orders
active (working) status if downstream models remain stale. A follow-up sqb build --virtual-env pr_123 (without --select) builds the remaining stale models to finalize the VDE.
Stale upstream coherence
If a selected model has stale required upstream models, the build blocks by default:
sqb build --virtual-env pr_123 --select fact_orders
# error: selected models have stale required upstreams: stg_orders
--include-stale-upstreams to expand the selection to the minimal set of stale ancestor models needed to make the selected scope coherent:
sqb build --virtual-env pr_123 --select fact_orders --include-stale-upstreams
Stale-driven selection
Virtual environment builds are change-aware by default. When combined with --select, only models that are both selected and stale are built:
sqb build --virtual-env pr_123 --select path:marts
--force to override and build all selected models regardless of state.
Standard mode is change-aware by default. See Planning and Change Detection for how fingerprints, source freshness, and identity tracking determine what gets built.
Stale detection
SQLBuild determines which models and seeds need rebuilding by comparing expected version hashes against bound version hashes in the VDE refs:
- Expected local hash - derived from the node’s query SQL (for models), content hash (for seeds), version-identity config, and source freshness hashes
- Expected version hash - local hash propagated through upstream dependencies (upstream hash changes cascade downstream)
- Bound version hash - the hash currently stored in the VDE refs from the last successful build
A node is stale when bound != expected. Stale nodes whose own local hash changed are roots (query changed, config changed, function changed). Others are stale due to upstream changed.
Seeds participate in version identity the same way as models. They are loaded into versioned physical tables with logical VDE views on top, and their version hashes and refs are tracked per virtual environment in the state backend. Unchanged seeds are not reloaded.
Source freshness observations and Python node identities are also persisted per virtual environment and included in version hash computation. When a source’s observed data version changes, its downstream models become stale. See Sources: Source freshness for configuration.
Seeded incremental builds
When an incremental model’s version hash changes, SQLBuild seeds the new physical version from the prior physical version before running the incremental delta. This avoids full rebuilds of large incremental tables.
| Adapter | Seed strategy |
|---|
| Snowflake | Zero-copy clone |
| BigQuery | Table clone |
| Databricks | Deep clone |
| DuckDB, Postgres, SQL Server | CTAS copy |
replay_on_change bounded-7d), the seed copies only rows before the replay window cutoff. The incremental delta then appends the bounded range without duplicating rows.
Custom materializations
Custom materializations are supported in virtual mode. By default, SQLBuild seeds new physical versions using the standard clone/copy strategy before calling the custom materialize function.
For custom materializations that need different seeding behavior, define a prepare_version function alongside materialize:
from sqlbuild.virtual.executor.models import VersionPrepareContext
from sqlbuild.executor.custom.models import MaterializationContext, MaterializationResult
def prepare_version(ctx: VersionPrepareContext) -> None:
"""Prepare the new physical destination from the prior version."""
ctx.execute_sql(f"CREATE TABLE {ctx.destination} AS SELECT * FROM {ctx.prior_relation}")
def materialize(ctx: MaterializationContext) -> MaterializationResult:
"""Run the custom materialization logic against the prepared destination."""
...
prepare_version is not defined, the framework uses the default clone/copy. Most custom materializations do not need to define it.
VersionPrepareContext provides prior_relation (the source physical version), destination (the new physical destination relation), adapter, connection, execute_sql(), config, and vars.
Custom materializations in virtual mode must write only to ctx.destination. Side-effect writes to other relations are not tracked by virtual state, not cleaned up by janitor, and not restored by rollback.
Functions
Functions are published into the logical VDE schema, not the physical layer. Each VDE has its own copy of function definitions. Function versions are tracked in state and participate in promotion and rollback.
Plan
Use sqb plan to preview what a virtual build would do without executing:
sqb plan
sqb plan --virtual-env pr_123
sqb plan --select fact_orders
- Virtual environment name and status (finalized/working)
- Stale root count and root set
- Stale model count
- Query diffs from prior bound versions
- Remaining stale models after partial selection
