> ## 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.

# Building

> Virtual builds, VDE creation, partial builds, and seeded incrementals.

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.

<Warning>
  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.
</Warning>

## Default VDE

When you run `sqb build` in virtual mode without `--virtual-env`, the VDE name defaults to the active physical target name:

```bash theme={null}
sqb build
```

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:

```bash theme={null}
sqb build --virtual-env pr_123
```

If VDE `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>
```

For example: `dev__sqb_physical.fact_orders__v_8f3a9c12`

Logical VDE views are created in a VDE-suffixed schema:

```
<base_schema>__<vde_name>.<model_name>
```

For example: `dev__pr_123.fact_orders`

When `unsuffixed_virtual_env` is configured, the named VDE uses the base schema directly:

```
dev.fact_orders
```

## 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`:

```bash theme={null}
sqb build --virtual-env pr_123 --select fact_orders
```

Partial builds leave the VDE in `active` (working) status if downstream models remain stale. A follow-up `sqb build --virtual-env pr_123 --changes-only` (without `--select`) builds the remaining stale models to finalize the VDE; a plain `sqb build --virtual-env pr_123` rebuilds the whole selection.

### Stale upstream coherence

If a selected model has stale required upstream models, the build blocks by default:

```bash theme={null}
sqb build --virtual-env pr_123 --select fact_orders
# error: selected models have stale required upstreams: stg_orders
```

Pass `--include-stale-upstreams` to expand the selection to the minimal set of stale ancestor models needed to make the selected scope coherent:

```bash theme={null}
sqb build --virtual-env pr_123 --select fact_orders --include-stale-upstreams
```

### Stale-driven selection

Virtual environment builds run the full selection by default, like standard mode. Add `--changes-only` to intersect the selection with the stale-driven set, so only models that are both selected and stale are built:

```bash theme={null}
sqb build --virtual-env pr_123 --select path:models/marts --changes-only
```

This is useful when the stale cascade is large and you want to build a coherent subgraph without running unchanged models. Without `--changes-only`, every selected model is built regardless of state.

Change-aware pruning is opt-in in both standard mode and virtual environments. See [Planning and Change Detection](/concepts/planning) 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:

1. **Expected local hash** - derived from the node's query SQL (for models), content hash (for seeds), version-identity config, and source freshness hashes
2. **Expected version hash** - local hash propagated through upstream dependencies (upstream hash changes cascade downstream)
3. **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](/concepts/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       |

For append models with bounded replay (`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`:

```python theme={null}
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."""
    ...
```

If `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:

```bash theme={null}
sqb plan
sqb plan --virtual-env pr_123
sqb plan --select fact_orders
```

Virtual plan output shows:

* 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