Skip to main content

​
Model migrations

Renaming an incremental or snapshot model normally means rebuilding it from scratch under the new name, which loses any history the source data can no longer reproduce. A model migration moves the existing relation’s data to the new name instead, then continues building incrementally. Migrations apply to incremental and snapshot models in direct mode. Virtual-environment builds reject migrate_from.

​
Declaring a migration

Add migrate_from to the renamed model’s header, naming the old model:
Use schema.name when the old relation is in a different schema of the same database. On the next build, SQLBuild copies the old relation’s data to the new name through a staging table (see How a move runs), records the move, and builds the model incrementally from the migrated state. There is no first-run rebuild, and no replay_on_change replay caused by the rename. The old relation is never modified or dropped. Once it is no longer part of the project, janitor archives it like any other stale relation. After the move is recorded, migrate_from has no further effect, and SQLBuild tells you it can be removed. Keep it until every target that needs the move has built.

​
Decisions

sqb plan shows the decision for every migration: The origin and destination must be compatible under the model’s normal on_schema_change rules; otherwise the build stops (M104). migrate_force true only affects the conflict case and is safe to leave in the header.

​
Automatic discovery

If a selected model has never been built, SQLBuild compares it with models that were removed from the project but whose relations still exist. When exactly one removed model has equivalent logic, it is migrated automatically. Equivalent means the same query and configuration, ignoring the model’s own name, CTE and table alias names, comments, formatting, and storage-only settings. Renamed upstream models are matched first, so a renamed chain of models is migrated together. Removed models whose data was already moved on by a recorded migration are not candidates, so a model renamed several times matches its latest table. Renamed tables and views are matched the same way. They are still rebuilt under the new name, since they hold no history, but they keep their identity for change detection, so incremental models downstream of a renamed view are not replayed or rebuilt. These renames are recorded as renamed events. Automatic discovery never guesses. If a match is ambiguous, SQLBuild warns (M107) and builds from scratch; declare migrate_from to choose. An explicit migrate_from always wins. Automatic discovery covers renames within the project’s schemas; use migrate_from to move a model to another schema. Relations last built before this feature carry no stored fingerprint and aren’t matched automatically.

​
How a move runs

A move never overwrites the destination directly:
  1. The old relation is cloned or copied into a new staging table named _sqb_archive__<UTC timestamp>__migration_stage__<name>, with the destination’s configured table type.
  2. The staging table is checked: it must exist, have the expected columns and table type, and, for a physical copy, the same row count as the old relation.
  3. The staging table is swapped in as the destination. If a destination already existed, for example after superseded replace or forced replace, it is kept as _sqb_archive__<UTC timestamp>__migration_previous__<name> rather than dropped.
  4. The move is recorded, then the model builds as usual.
The old relation is never modified. Staging and replaced tables use the janitor archive format, so an abandoned staging table or a replaced destination is deleted by janitor after archive_retention_days. A staging table left by an interrupted run is never reused; the next run stages again. Don’t run janitor at the same time as a build in the same schema. sqb plan shows how each move will run, for example physical copy, transient -> permanent, promote by swap.

​
State

Each move is recorded in an append-only _sqlbuild_migrations table in the destination schema. A missing record means completion is unrecorded; staging or promotion may already have happened. Re-running after an interruption recovers through the migration decisions above.

​
Adapter support

On PostgreSQL, views that depend on the destination are re-pointed to the new table in the same transaction, keeping their options, owner and grants. Materialized views are not re-pointed.

​
Previewing another target

sqb plan --as <target> compiles the project for another target, for example production, and shows its migration decisions using your current connection. It only inspects; nothing is written.