A deployed changeset is not the end of its lifecycle. Once validated and promoted to production, it becomes part of a growing collection whose organization gradually acquires value beyond deployment itself. This level explores how changesets accumulate over time and how, when managed consistently, they evolve into a structured and replayable representation of the database.

Building and Leveraging the Changeset Library

Before moving forward, let’s revisit the database model introduced in Level 2 , this time placing it in a concurrent development scenario similar to the one discussed in Level 5. Assume that a dim.Customer table already exists as part of the common baseline from which both changesets evolve.

CREATE TABLE dim.Customer
(
 CustomerID int NOT NULL CONSTRAINT PK_Customer PRIMARY KEY
,CustomerName nvarchar(100) NOT NULL
)
GO

Neither changeset creates dim.Customer; both independently extend this existing table while introducing their own geographical model.

The first changeset extends the baseline by introducing a simple geographical model. It creates a new dim.Country table and adds a foreign key to dim.Customer so that each customer is associated directly with a country. The remaining statements, represented here by a placeholder, are unrelated to the geographical model and are included only to illustrate that most of the changeset remains unaffected by the concurrency resolution.

-- Changeset 1 — Create

CREATE TABLE dim.Country
(
 CountryID smallint NOT NULL CONSTRAINT PK_Country PRIMARY KEY
,CountryName nvarchar(80)
)
GO

ALTER TABLE dim.Customer
ADD CountryID smallint NOT NULL 
         CONSTRAINT FK_Customer_Country 
         FOREIGN KEY REFERENCES dim.Country(CountryID)

-- Other unrelated statements
-- (new dimensions, views, stored procedures, etc.)

The corresponding Rollback script simply applies the same logic in reverse, first undoing the subsequent unrelated changes and then reversing the geographical changes introduced by the Create script.

-- Changeset 1 – Rollback

-- Rollback of subsequent unrelated changes
-- (new dimensions, views, stored procedures, etc.)

ALTER TABLE dim.Customer
DROP CONSTRAINT IF EXISTS FK_Customer_Country
GO

ALTER TABLE dim.Customer
DROP COLUMN IF EXISTS CountryID
GO

DROP TABLE IF EXISTS dim.Country
GO

As in the original Level 2 example, the second changeset introduces a more normalized geographical model in which dim.Customer references a state or province rather than a country directly. Notice that, unlike the first changeset, it also reflects a different modeling choice, identifying countries by their ISO code rather than by a surrogate key. Since the changeset is developed independently, however, it is unaware that a similar geographical hierarchy has already been introduced elsewhere. Instead, it defines its own dim.Country table and builds the revised model upon it.

-- Changeset 2 – Create

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

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

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

The corresponding Rollback script simply restores the original baseline, removing the foreign key, the newly introduced column, and the two tables introduced by the Create script

-- Changeset 2 — Rollback

ALTER TABLE dim.Customer
DROP CONSTRAINT IF EXISTS FK_Customer_StateProvince
GO

ALTER TABLE dim.Customer
DROP COLUMN IF EXISTS StateProvinceID
GO

DROP TABLE IF EXISTS dim.StateProvince
GO

DROP TABLE IF EXISTS dim.Country
GO

Both changesets remain valid as complete units. In the first changeset, the placeholder comments represent unrelated work that remains perfectly valid regardless of the concurrency resolution. The only overlap concerns the independent definitions of dim.Country, introduced using different modeling approaches.

In practice, situations of this kind are often identified only towards the end of the deployment process, after both changesets have successfully passed through multiple rehearsal cycles across the deployment environments. By that point, each changeset is already complete, and the concurrency issue is typically limited to one or a few statements, while the remainder of the work has already been validated. Resolving the conflict therefore does not require discarding an entire changeset. It requires only deciding which implementation should become part of the shared baseline.

Assume that the second changeset is selected as the winning implementation. The CREATE TABLE dim.Country statement in the first changeset is therefore commented out or removed, together with the corresponding ALTER TABLE dim.Customer statement that reflects the alternative modeling approach. The remaining statements in the changeset continue through the deployment pipeline unaffected. The corresponding Rollback script, however, would no longer complete successfully if executed without adjustment. It would eventually attempt to drop dim.Country, which is now referenced by the dim.StateProvince table introduced by the winning changeset. At this point, however, it may still seem unnecessary to address the issue. The deployment has already been validated through repeated rehearsal, production has been reached successfully, and no operational rollback of this changeset is expected. From that perspective, leaving the original Rollback unchanged may appear both harmless and, ultimately, understandable.

The Changeset Library Over Time

At the beginning of this Stairway, particularly in the first two levels, we spent some time describing the internal organization of folders and scripts within a single changeset. By contrast, we said very little about the organization of changesets as a whole, which, as the concurrency scenario has just hinted at, can become equally important over time.

At first glance, each changeset appears to be an atomic, deterministic, and self-contained deployment unit. This is precisely why I described the decision to ignore a final post-production adjustment of the Rollback script as 'understandable'. As long as a changeset is viewed solely as a deployment artifact, maintaining its Rollback after a successful final release offers little apparent benefit and hardly seems worth the effort.

However, if we shift the focus from the individual changeset to the library as a whole, a less obvious opportunity emerges. As changesets accumulate over time, the framework used to organize them begins to take on a role that goes beyond its original purpose. To fully leverage this evolution, however, they should remain within a coherent hierarchical structure rather than being moved to an anonymous archive folder. What initially serves to coordinate rehearsal and deployment gradually becomes the structural history of the database. Under this new perspective, the small effort required to keep each Rollback script aligned with the final baseline becomes surprisingly valuable, allowing the changeset library to evolve into a coherent and navigable record of the database history.

How can this be achieved in practice? The first step is the organization of the top-level folders. Rather than serving as arbitrary containers, they should represent meaningful stages in the project lifecycle. Depending on the team’s workflow, these stages may correspond to releases, sprints, quarters, or major functional milestones. As new changesets are introduced, the folder hierarchy gradually becomes a chronological map of the database history, making it easier to navigate, understand, and reconstruct the rationale behind previous design decisions.

Release 1.0
+-- Changeset 001
+-- Changeset 002

Release 1.1
+-- Changeset 003

=================

Sprint 18
+-- Changeset 057

Sprint 19
+-- Changeset 058
+-- Changeset 059

=================

+-- 2026Q1_SetupDB
+-- 2026Q2_ETLUpgrade
+-- 2026Q3_ReportingSupport

=================

+-- 001 Customer Migration
+-- 002 Pricing Refactoring
+-- 003 Security Hardening

Because every changeset remains internally coherent and every stage is retained in sequence, the library can be replayed consistently from the first changeset to the last.  Whatever naming convention is adopted, one principle should always remain: alphabetical ordering must correspond to the chronological execution sequence. As a result, it becomes possible to reconstruct the database schema from scratch—or from any desired checkpoint—simply by executing the appropriate sequence of changesets.

This should not be seen as a replacement for traditional backup and restore procedures, whose purpose is to preserve operational data. Rather, it provides an alternative mechanism for reconstructing the structural evolution of the database, without requiring direct DBA intervention or complex restore operations. Unlike restoring an older version of the database, replaying the changeset library reconstructs the complete structural history of the database without requiring subsequent changes to be reapplied.

A Practical Example

To conclude, let me share a personal experience that illustrates how this approach proved valuable in a real project.

Several years ago, just as this model was beginning to be adopted as a shared development approach, I was leading a small team working concurrently on the same database. The project was a typical enterprise data warehouse, with separate schemas dedicated to staging, ETL processing, the dimensional warehouse itself, and the analytical views feeding an OLAP cube. Each developer was usually responsible for a different area of the database, while all changes were coordinated through a common changeset library.

One day, quite unexpectedly, our project manager informed us that an external audit team required an artifact capable of recreating the complete database structure. Because the auditors were not authorized to access production data, restoring a database backup was not an appropriate solution. What they needed was the database itself, not its contents.

Fortunately, because every structural change had been captured in a coherent changeset—keeping both the Create and Rollback scripts aligned throughout the project—the solution was already available. I generated two simple scripts: one replayed the complete changeset library to build the database from scratch, while the other executed the corresponding Rollback scripts in reverse order, removing every object until the database returned to its initial empty state. Before delivering the scripts, I simply created a temporary database, replayed the complete Create script, and verified through a schema comparison that the reconstructed database matched the production one. I then executed the corresponding Rollback script, confirming that every object was removed successfully and the database returned to its original empty state, ready to be safely discarded.

An interesting consequence also became apparent. Although the implementation had been developed for SQL Server, it soon became clear that only a limited number of T-SQL-specific DDL statements would need to be adapted to target a different relational database platform. The model itself—its changeset organization, execution order, and reconstruction process—remained completely technology-agnostic.

Final Considerations

At this stage of the presentation, it is useful to introduce an additional condition for the model to remain reliable: the changeset library should operate within the scope of a single database. Each changeset contributes to the evolution of that database, and the sequence as a whole describes its structure over time. Introducing dependencies on other databases—such as through three-part names—can compromise this model. External references may not follow the same sequence, making reconstruction less reliable and reducing the overall reliability of the system.

For this reason, it is generally advisable to keep the changeset library as self-contained as possible, minimizing cross-database dependencies unless they are explicitly managed and aligned with the same deployment process. This may be the case, for example, when a separate database—such as one hosting OLAP views—is intentionally designed to reference a data warehouse database as part of the same model.

The model presented throughout this Stairway aims to offer more than a disciplined deployment process. By organizing changesets into a coherent, replayable hierarchy, it provides a practical way to understand and manage the structural evolution of a database over time. The deployment of a changeset may be completed in a matter of minutes, but the value of the changeset library continues to grow throughout the lifetime of the database.

The next level—the last of the Stairway—builds on this foundation, exploring a few advanced scenarios where these principles are applied in increasingly complex contexts.