Introduction

Level 1 introduced the idea of treating database changes as changesets: deliberate units of work designed together with their rollback. The focus was placed on intent and behavior—what a change is meant to do, how failure should be interpreted, and how responsibility is embedded in the scripts themselves—rather than on execution mechanics.

In this level, we move one step closer to daily practice by clarifying what it means, in practical terms, for a changeset to be deployable and reversible, and by introducing the idea of a deployment contract. The deployment contract does not describe tools, automation, or environments. Instead, it defines the minimum behavioral guarantees that a changeset must provide so it can be executed, rolled back, and trusted with predictable results. A deployment contract is an agreement between the author of a changeset and whoever will execute it later—whether that is the same developer, another team member, a DBA, or an automated process. Its purpose is to make expectations explicit and reduce reliance on implicit knowledge or contextual assumptions.

Deployment within structured, multi-environment architectures is introduced starting from Level 3. For now, the focus remains on a method—an approach that can support complex deployment scenarios, but that is equally applicable to simpler contexts such as personal databases or small teams working on a single instance. Even in these scenarios, adopting a clear deployment contract can serve as a way to make changes more consistent, traceable, and repeatable.

At a minimum, the contract guarantees that:

• applying the changeset produces a known and expected structural result;
• rolling it back restores the database schema to a known and consistent structural state;
• both operations can be executed safely and repeatedly.

This contract is not enforced by tooling; it is enforced by how the Create and Rollback scripts are designed and written.

Changeset Scope and Semantics

Regardless of whether they are implemented as a single script or as a more structured set of scripts, changesets follow the same basic criteria. In Level 1, the focus was on how Create and Rollback scripts differ in intent and behavior in order to support distinct responsibilities. Here, the focus shifts to what they have in common: the structural semantics of a changeset. Despite their different roles, Create and Rollback operate within the same boundaries: they define, evolve and restore database structure by creating, altering, or removing schema objects such as tables, views, constraints, and indexes.

Other elements are generally kept outside the scope of Create and Rollback at this level, in particular objects that are strongly tied to a specific environment, such as users, roles, permissions, credentials, or other security-related settings. These concerns introduce variability that is difficult to reconcile across multiple environments and therefore require separate treatment. Of course this boundary primarily applies to multi-environment projects. In simpler contexts—such as a single-instance database or a basic infrastructure—these elements may be managed together with structural changes without undermining the overall approach.

Data changes deserve specific consideration. Although they are not excluded from a changeset, they should never be part of a Create script. Instead, they are treated separately within an extended version of the basic changeset folder structure, which includes a dedicated Update folder. This will be discussed in detail later.

Introducing Checkpoints

At the core of changeset development is the idea that each Create / Rollback script pair represents a checkpoint, defining a boundary between two known structural states. Applying Create scripts moves the database forward from one checkpoint to the next, while executing Rollback scripts moves it backward, step by step, restoring previous checkpoints in reverse order. Rollback is therefore not conceived as a single operation that undoes an entire changeset at once; instead, it is defined at the same granularity as change.

The following example illustrates this concept through a simple schema evolution involving two consecutive Create scripts and their corresponding Rollback scripts. The key point is not the specific objects involved, but how each rollback action restores the database to the immediately preceding checkpoint, rather than reverting all changes at once.

The first Create script introduces two tables: a reference table (dim.Country) and a dependent table (dim.Customer), linked by a foreign key. When this script completes successfully, the database reaches the first checkpoint, where customers are directly associated with a country.

CREATE TABLE dim.Country
(
    ISOCode nchar(2) NOT NULL CONSTRAINT PK_Country PRIMARY KEY,
    CountryName nvarchar(80)
);
GO

CREATE TABLE dim.Customer
(
    CustomerCode nchar(10) NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
    CompanyName nvarchar(100) NOT NULL,
    CountryID nchar(2) NOT NULL
        CONSTRAINT FK_Customer_Country
        FOREIGN KEY REFERENCES dim.Country(ISOCode)
);
GO

The corresponding Rollback script reverses exactly this transition, removing the tables introduced by the script and restoring the database to the state that existed before the first checkpoint.

DROP TABLE IF EXISTS dim.Customer;
DROP TABLE IF EXISTS dim.Country;

A second Create script then evolves the model. It introduces a new table (dim.StateProvince) and refactors the existing relationship: instead of linking customers directly to countries, customers are now linked to a state or province, which in turn references a country. When this script completes, the database reaches a second checkpoint, representing a new stable structural state.

CREATE TABLE dim.StateProvince
(
    StateProvinceID int NOT NULL CONSTRAINT PK_StateProvince PRIMARY KEY,
    CountryID nchar(2) NOT NULL
        CONSTRAINT FK_StateProvince
        FOREIGN KEY REFERENCES dim.Country(ISOCode),
    StateProvince nvarchar(80)
);
GO

ALTER TABLE dim.Customer DROP CONSTRAINT FK_Customer_Country;
ALTER TABLE dim.Customer DROP COLUMN CountryID;

ALTER TABLE dim.Customer
    ADD StateProvinceID int
        CONSTRAINT FK_Customer_StateProvince
        FOREIGN KEY REFERENCES dim.StateProvince(StateProvinceID);
GO

At this point, a rollback of the second script must restore the database to the first checkpoint, not to the original baseline. This means undoing only the changes introduced by the second Create script, while leaving intact the structures created by the first.

An incorrect rollback that drops all related tables may appear to work, but it violates the checkpoint model by skipping an intermediate stable state.

DROP TABLE IF EXISTS dim.Customer;
DROP TABLE IF EXISTS dim.StateProvince;
DROP TABLE IF EXISTS dim.Country;

The correct rollback restores the schema to the exact structure that existed after the first Create script.

ALTER TABLE dim.Customer DROP CONSTRAINT FK_Customer_StateProvince;
ALTER TABLE dim.Customer DROP COLUMN StateProvinceID;

ALTER TABLE dim.Customer
    ADD CountryID nchar(2) NOT NULL
        CONSTRAINT FK_Customer_Country
        FOREIGN KEY REFERENCES dim.Country(ISOCode);

DROP TABLE IF EXISTS dim.StateProvince;

This rollback removes only the objects and relationships introduced by the second Create script and reinstates the previous foreign key, restoring the database to the first checkpoint.

In practice, rollback scripts should be written defensively to account for partial execution or unexpected states. For clarity, the example above keeps the rollback logic intentionally simple and focuses only on illustrating checkpoint semantics.

This incremental execution model has several important consequences:

• execution order becomes explicit and predictable;
• partial execution becomes manageable;
• development and testing benefit directly from applying and reverting changes incrementally.

Specialized Extensions: _schema and Update

While Create and Rollback define the core of the deployment contract, some changesets require handling concerns that do not fit cleanly into those two categories. For this reason, the model may include two additional folders that extend the deployment contract to cover responsibilities that fall outside Create and Rollback: _schema and Update.

These folders are optional but first-class, and exist at the same structural level as Create and Rollback. Their naming and relative position are intentional: _schema precedes Create and Rollback, while Update follows them. This reflects their specific role and position within execution orchestration, which will be addressed in the next level of the Stairway.

The _schema Folder

Schemas are structural prerequisites. Keeping their creation separate avoids mixing foundational concerns with object evolution and makes execution order explicit. This folder is especially useful in the first changeset of a new project, but it may also appear later if new schemas are added.

As with all structural changes, schema creation must be fully reversible. Nonetheless, since schema management usually does not require a granular sequence of steps, the _schema folder follows a deliberately simplified convention and typically contains only one forward script and one rollback script, for example:

+- _schema
   +- 001_newSchemas.sql
   +- rb001_newSchemas.sql

If a changeset requires introducing multiple schemas that need to be logically separated, additional scripts may be added. When this happens, the same principles apply: explicit ordering and one-to-one forward/rollback correspondence.

Forward scripts in _schema usually contain only schema creation statements, with ownership defined explicitly:

CREATE SCHEMA [config] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [dim] AUTHORIZATION [dbo];
GO

Rollback scripts mirror this intent by removing the same schemas defensively and in reverse dependency order:

DROP SCHEMA IF EXISTS [dim];
GO
DROP SCHEMA IF EXISTS [config];
GO

This approach keeps schema concerns isolated, explicit, and safely reversible.

The Update Folder

As mentioned earlier, changesets are primarily meant to apply structural changes to a database. This boundary keeps the focus on changes that define the shape of the database, while data changes operate on its state. Mixing these responsibilities within the same scripts would blur the intent of a changeset, making it harder to reason about correctness, rollback behavior, and execution safety. Nonetheless, while complex data migrations or large-scale transformations are not intended to be addressed by this model, in practice it is not always possible to limit a change strictly to structure.

A typical occurrence is the introduction of a new configuration table that requires a fixed, deterministic set of rows to function correctly, such as:

INSERT config.Status ([StatusID], [Status]) SELECT 0, 'Completed';
INSERT config.Status ([StatusID], [Status]) SELECT 1, 'Running';
INSERT config.Status ([StatusID], [Status]) SELECT 2, 'Error';

This is a type of change that commonly accompanies the creation of a new configuration table. Excluding such operations from the model would weaken it, since these data adjustments must be addressed as part of deploying a database change. By isolating data-related operations in a dedicated Update folder, the model accommodates these needs while avoiding the introduction of stateful assumptions into schema evolution.

Like the Create folder, the Update folder may contain multiple scripts and follows the same incremental model, allowing data changes to be applied in a well-defined order when dependencies exist. Nonetheless, because Update scripts operate on data rather than structure, they should be governed by different assumptions than Create and Rollback. Data changes do not generally admit a symmetric or deterministic reversal at the same granularity: once data has been modified, its previous state may no longer be known, reconstructable, or even meaningful. For this reason, Update scripts deliberately do not mirror the forward/rollback pattern of Create and Rollback. Instead, the deployment contract requires Update scripts to be applied only after structural changes have completed successfully, and to be written in a way that makes their effects predictable and controlled. When these scripts apply data changes to tables introduced by the same changeset, they can assume a known and controlled starting state—for example, inserting an initial set of rows into a newly created configuration table, as illustrated earlier.

When operating on tables that already exist, data changes must instead take into account existing rows or prior values, such as avoiding duplicate inserts or updating data conditionally:

INSERT OR UPDATE config.Status VALUES (3, 'StandBy');

So, as a recap, the coding style should be more flexible compared to the Create scripts:

• forward-only when applying data to tables introduced by the same changeset, where a known starting state can be assumed;
• defensive when operating on pre-existing tables, where scripts must account for existing rows or prior values.

This treatment defines the baseline rules and guarantees of the deployment contract. In the next level, the focus shifts to applying these rules in practice, following a changeset as it moves across environments and verifying that the contract holds consistently throughout the deployment pipeline.