Introduction

Database development has historically lacked a shared set of deployment rules and has therefore relied on software development paradigms, often with mixed results. As a surrogate for this missing discipline, those paradigms have typically been applied through practices borrowed from the software world: versioning rules, deployment pipelines, rollback strategies, and environment-based validation. However, while in software development these practices are well established and widely understood, their application has never worked smoothly in database development.

This is not accidental: due to its nature, database development must address continuous change, which does not always guarantee predictable outcomes. Database structures are designed to persist over time, retaining and accumulating data, while changes are applied incrementally rather than replaced wholesale. As a result, some practices that work well for software deployments become harder to apply consistently when the target is a database schema.

Database modifications tend to rely on scripts that act as small units of change, each affecting only a fragment of the overall structure. These scripts are often executed successfully in an initial run, which creates confidence that they will behave the same way in subsequent executions, or when executed again in a different context. However, that confidence is based on assumptions about the starting state of the database, which is often determined by data already present and not affected by the change script. That data context is transient and not governed by the script itself, which means a script may succeed in a specific context and fail when that context changes — which, in the end, is precisely what a database is meant to do.

Defining a Changeset as a Unit of Work

Database scripts are often treated as isolated solutions to immediate problems. Even when automation tools and deployment frameworks are available, many developers still prefer to retain direct control over the scripts they execute, especially when changes are complex or context-dependent. This Stairway does not challenge that choice. Instead, it focuses on establishing a way to reason about such scripts that makes them safer, more predictable, and easier to recover when something goes wrong.

To address this, I started treating database modifications as explicit units of work, referred to here as changesets. A changeset represents a coherent intent. It groups together all the structural modifications required to achieve a specific goal and defines, from the beginning, how those modifications can be undone. The emphasis is not on the number of objects involved, but on the fact that the changes belong together and are meant to be applied and reasoned about as a whole. In this approach, a database change is no longer treated as “a script”, but as a small unit composed of two scripts: a Create script, which applies the intended structural change, and a Rollback script, which restores the previous structural state.

Writing Create and Rollback Scripts in Practice

Consider the following Create script:

ALTER TABLE Sales.Orders
ADD DiscountAmount decimal(10,2);
GO

ALTER VIEW Sales.vOrders
AS
SELECT OrderId, CustomerId, DiscountAmount
FROM Sales.Orders;
GO

A first attempt at a rollback might look like this:

ALTER VIEW Sales.vOrders
AS
SELECT OrderId, CustomerId
FROM Sales.Orders;
GO

ALTER TABLE Sales.Orders
DROP COLUMN DiscountAmount;
GO

If execution completes successfully, this rollback restores the original structure. However, this version already embeds assumptions that are easy to overlook.

Now consider a slightly different version of the same change:

ALTER TABLE Sales.Orders
ADD DiscountAmount decimal(10,2) NOT NULL;
GO

ALTER VIEW Sales.vOrders
AS
SELECT OrderId, CustomerId, DiscountAmount
FROM Sales.Orders;
GO

If the Sales.Order table already contains rows, SQL Server will fail when attempting to add a NOT NULL column without a default value. In this case, the failure is caused by existing data, not by missing dependencies. This kind of error is highly context-dependent. For instance, in a development database, the table may be empty and the script may execute successfully. The same script, executed later in a different context where data is present, would fail. The difference is not visible in the script itself, but only in the state of the target database at execution time. But there is more. In this scenario, not only does the Create script fail, the Rollback script shown earlier would fail as well, because it assumes that the column was successfully added.

There are several ways to address situations like this. Defensive scripting and idempotent execution are commonly used techniques, but they come with both advantages and drawbacks: while they help tolerate partial execution, interruptions, and repeated runs, they also increase complexity and can hide problems that would be better surfaced early. When reasoning in terms of a changeset, I arrived at a clear distinction. Rollback scripts must be written to tolerate partial execution and failure. They may be executed after an interruption, after a failed Create, or even when no change has been applied at all. Their responsibility is to restore a known structural state safely and consistently, whatever the current state of the database.

A defensive Rollback for the previous change might look like this:

IF EXISTS
(
    SELECT 1
    FROM sys.columns c
    JOIN sys.tables t ON t.object_id = c.object_id
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = N'Sales'
      AND t.name = N'Orders'
      AND c.name = N'DiscountAmount'
)
BEGIN
    ALTER TABLE Sales.Orders
    DROP COLUMN DiscountAmount;
END
GO

ALTER VIEW Sales.vOrders
AS
SELECT OrderId, CustomerId
FROM Sales.Orders;
GO

Executing this rollback when the column is present restores the previous structure; executing it when the column is already absent produces no change and does not fail.

Create scripts serve a different purpose. They define intent and assume a known baseline. Introducing defensive checks into Create scripts may allow execution to continue, but it does so by hiding the fact that the database is not in the expected state. In this model, a failing Create script is not a problem to be masked, but a valuable signal that assumptions about the starting state are incorrect and that the change should not proceed until that difference is understood. This asymmetry is intentional. Create scripts define intent and must fail when that intent cannot be applied. Rollback scripts restore state and must tolerate failure.

Create and Rollback as Folders

A single Create script and a single Rollback script as a whole can be considered as the basic elementary unit already sufficient to design and deliver many real-world changesets, especially when the scope of change is limited and well contained.

As complexity increases, however, responsibility must move to a higher structural level that extends this basic pattern. The original one-to-one pairing between a Create script and a Rollback script is preserved, but expanded to deal with more complex modifications of the database structure. To address these needs, each changeset is organized around two folders—Create and Rollback—each containing an ordered set of scripts whose elements correspond biunivocally.

A typical structured changeset may look like this:

202602_dwhSalesSetup
|
+- Create
|  +- 005_configTables.sql
|  +- 008_salesDims.sql
|  +- 010_geoDims.sql
|  +- 013_facts.sql
|
+- Rollback
   +- rb005_configTables.sql
   +- rb008_salesDims.sql
   +- rb010_geoDims.sql
   +- rb013_facts.sql

Scripts in the Create folder are ordered using numeric prefixes, reflecting the intended execution sequence. Gaps between numbers are intentional; they allow future scripts to be inserted without renaming existing ones.

Each Create script has a corresponding Rollback script with the same name and a consistent prefix. This naming convention reinforces the biunivocal relationship between scripts and makes their pairing immediately recognizable, even when multiple files are open simultaneously.

This structure scales naturally from the simplest changesets, composed of a single script pair, to more complex ones that require multiple coordinated logical steps. Each Create script remains explicitly paired with a corresponding Rollback script, preserving a clear relationship between forward changes and their reversal.

The behavioral implications of the deployment task at the folder level extend the semantics already discussed at the single-script level. These implications will be addressed in Level 2, where we define the behavioral guarantees that make a changeset deployable and recoverable in a predictable way, and how these guarantees are formalized into a deployment contract.