Skip to main content

Database change management: evolutionary design in regulated environments

Database changes are often the deployments engineers fear most in banking. An organisation may deploy application code twenty times a day yet change its schema once a quarter, and the quarterly release becomes a multi-week exercise involving a Change Advisory Board, a five-page impact assessment, three reviewers, and a 2am Sunday maintenance window. The concern is rational: a bad migration can corrupt data, break referential integrity, or produce incorrect calculations that flow into regulatory reports. The usual response is counterproductive, because making database changes rare and large makes each one more dangerous.

Leading engineering teams at a Tier-1 bank taught me that the path to safe database changes is the same one that makes application deployments safe: make them small, frequent, automated, and reversible. Scott Ambler and Pramod Sadalage set this out in Refactoring Databases (2006), arguing that schemas should evolve incrementally through small, well-tested migrations rather than through large, infrequent releases. Martin Fowler made the same case in his essay on Evolutionary Database Design. The principle is straightforward: treat your database schema as code, version it, test it, and deploy it through the same CI/CD pipeline as your application. In regulated environments this strengthens your controls, because every change is versioned, traceable, and automatically validated.

Why database change management matters​

Database changes must be safe, repeatable, and recoverable. Financial services adds four hard constraints:

  • Data integrity: financial data must be accurate to the cent. A migration that silently truncates a decimal column can produce incorrect balances across millions of accounts.
  • Regulatory compliance: regulators require audit trails for data changes. You must be able to show who changed the schema, when, why, and what review process was followed.
  • System availability: in a 24/7 banking environment, downtime for database changes is not an option. Migrations must run online, without service interruption.
  • Rollback capability: when a migration fails, you need to be able to roll back safely. In banking, "roll forward and fix it later" is often not an acceptable strategy.

Benefits​

  • Small, well-tested migrations are far less likely to corrupt or lose data than large, manually executed schema changes.
  • Automated validation checks that migrations maintain referential integrity and data constraints.
  • When database changes are versioned, tested, and automated, deployments become routine rather than high-risk events.
  • Every migration is a versioned artefact with a clear author, timestamp, and approval record, so the change history is auditable.

Evolutionary database design​

Martin Fowler's concept of Evolutionary Database Design challenges the traditional practice of designing a schema up front and treating it as immutable. Instead, the schema evolves alongside the application through a series of small, incremental migrations.

The core principles:

  1. All schema changes are migrations. No manual DDL execution against production databases; every change is expressed as a versioned migration script.
  2. Migrations are applied in order. Each migration has a version number, and the migration tool applies them in sequence, so any environment can be reconstructed from scratch by running all migrations in order.
  3. Migrations are idempotent or versioned. Either the scripts can be safely re-run (idempotent) or the tooling tracks which migrations have been applied (versioned). Flyway uses the versioned approach; some teams prefer idempotent scripts for operational flexibility.
  4. Schema and data changes are separated. Structural changes (adding columns, creating indexes) and data changes (backfilling values, migrating data formats) go in separate migrations, for clarity and reversibility.
  5. Backward compatibility is maintained during transitions. When renaming a column, the old and new names coexist for a period, so the application can be deployed independently of the migration.

The expand-contract pattern​

The expand-contract pattern (sometimes called parallel change) makes zero-downtime migrations possible:

  1. Expand: add the new column or table alongside the existing one. Write to both. Read from the old.
  2. Migrate: backfill the new column or table with data from the old one.
  3. Transition: switch the application to read from the new column or table. Continue writing to both.
  4. Contract: once all consumers are reading from the new column or table, remove the old one.

The pattern lets you deploy database changes independently of application changes, instead of coordinating a "big bang" release.

Example: renaming a column in a live banking system​

Renaming a column in a production banking database sounds trivial. It is not. If you rename acct_bal to account_balance in a single migration, every application, report, ETL job, and downstream system that references acct_bal breaks at once.

Using the expand-contract pattern:

-- Migration V1: Expand - Add new column
ALTER TABLE accounts ADD COLUMN account_balance DECIMAL(19,4);

-- Migration V2: Backfill - Copy data
UPDATE accounts SET account_balance = acct_bal WHERE account_balance IS NULL;

-- Migration V3: Sync - Add trigger to keep both columns in sync
CREATE TRIGGER sync_account_balance
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION sync_balance_columns();

-- Migration V4: Contract - Remove old column (after all consumers migrated)
ALTER TABLE accounts DROP COLUMN acct_bal;

Each migration is deployed independently, tested independently, and can be rolled back independently. At no point is the system in an inconsistent state.

Version control for database schemas​

Version control here means tracking and managing changes to database schemas the way you track code: multiple developers can collaborate, history is preserved, and previous versions can be restored when needed.

  • Git for migration scripts: migration scripts live in the same repository as the application code, so application changes and the database changes they require are reviewed, approved, and deployed together.
  • Liquibase: a schema change management tool that uses XML, YAML, JSON, or SQL changelog files to define database changes. Liquibase tracks which changesets have been applied and supports rollback generation.
  • Flyway: a migration tool that uses versioned SQL scripts (V1__create_accounts.sql, V2__add_balance_column.sql). Flyway is simple, convention-driven, and fits cleanly into CI/CD pipelines.

Example: using Flyway for version control​

A typical Flyway migration directory structure:

db/migration/
  V1__create_accounts_table.sql
  V2__add_customer_reference.sql
  V3__create_transactions_table.sql
  V4__add_index_on_transaction_date.sql
  V5__add_account_balance_column.sql

Flyway maintains a flyway_schema_history table that records which migrations have been applied, when, by whom, and the checksum of each script. In banking, this table is the audit trail: evidence you can put in front of auditors showing the complete history of schema evolution.

Automated testing for database changes​

Automated testing verifies database changes before they reach production. In financial services, database testing is a control.

  • Unit tests cover individual database functions, stored procedures, or triggers in isolation. pgTAP (for PostgreSQL) and tSQLt (for SQL Server) are test frameworks built specifically for database code.
  • Integration tests cover the interaction between the application and the database, confirming that migrations do not break existing queries, ORM mappings, or API contracts.
  • Migration tests run the full migration sequence from scratch in a disposable environment (Docker containers or cloud-provisioned databases) to confirm that all migrations apply cleanly and in order.
  • Data validation tests run after a migration that modifies data. Validation queries confirm the transformation was correct; for financial data, that typically means checking that aggregate balances match before and after the migration.

Example: migration testing in CI/CD​

In our CI/CD pipeline at the bank, every pull request that included database migration scripts triggered five automated checks:

  1. Clean build: spin up a fresh PostgreSQL container and apply all migrations from V1 to the latest. Verify there are no errors.
  2. Incremental build: apply only the new migrations against a database that already has all previous migrations. Verify there are no errors.
  3. Rollback: apply the new migrations, then roll them back. Verify the database returns to its previous state.
  4. Application compatibility: run the full application test suite against the migrated database. Verify there are no regressions.
  5. Data integrity: for data migrations, run assertion queries that verify row counts, aggregate sums, and referential integrity constraints.

This pipeline meant that any migration reaching production had already been validated in four environments (developer machine, CI, staging, pre-production).

Example: Claude Code as a migration reviewer​

The five automated checks above catch whether a migration runs. They do not catch whether it is safe to run against a live, large table: an unguarded ALTER TABLE ... ADD COLUMN NOT NULL that locks for the duration of a rewrite, a DROP COLUMN whose read paths are still in use, an index added without CONCURRENTLY. Those are pattern-matching problems, and the answer depends on reading the migration alongside the schema history and the application code that queries the table, not on the migration file in isolation. A Claude Code review step, scoped with read-only tools and given the expand-contract pattern as the standard to check against, flags migrations that skip a step (a drop with no prior deprecation window, a rename with no dual-write period) before a human reviewer even opens the PR. It does not replace the automated checks; it catches the class of mistake those checks are structurally unable to see.

Continuous integration for database changes​

Continuous integration means merging database changes frequently and detecting problems early. The rule: database changes must flow through the same CI/CD pipeline as application code.

  • Automated builds include database migration execution, which catches syntax errors, constraint violations, and compatibility problems before they reach production.
  • Migration tools run as pipeline stages. Liquibase and Flyway execute automatically during deployment; nobody runs DDL manually against any environment.
  • Developers get fast feedback. If a migration breaks the build, the author knows within minutes, not days.

Example: database CI/CD in a regulated environment​

In the regulated banking environment, the CI/CD pipeline for database changes carried additional controls:

  • Four-eyes review: every migration script required approval from at least one other engineer before merging, enforced by Git branch protection rules.
  • Change classification: migrations were automatically classified as "structural" (DDL) or "data" (DML). Data migrations on tables containing personally identifiable information (PII) triggered additional review by the data governance team.
  • Execution logging: the pipeline recorded the exact SQL executed, the execution time, the number of rows affected, and the before/after state of affected objects. This log was immutable and retained for seven years.
  • Segregation of duties: the person who wrote the migration could not approve it, and the pipeline service account that executed it was separate from all human accounts.

Audit trails and regulatory compliance​

In regulated environments, database change management is a compliance obligation as well as an engineering practice. Regulators expect:

  • Traceability: the ability to trace any data value back to the code change, migration script, and approval that produced it.
  • Immutability: evidence that the audit trail cannot be tampered with after the fact.
  • Segregation of duties: proof that the person who authored a change is not the person who approved or deployed it.
  • Retention: audit records kept for the period regulation specifies (typically 5-7 years in banking).

Git (change authoring and review), Flyway or Liquibase (execution tracking), and CI/CD pipeline logs (deployment evidence) together produce an audit trail that satisfies these requirements without manual documentation.

Tools for database change management​

  • Liquibase: a schema change management tool that works with version control systems. Supports rollback generation, diff reports, and multiple changelog formats.
  • Flyway: a migration tool that integrates with version control systems, using a convention-over-configuration approach with versioned SQL scripts.
  • DBmaestro: a database DevOps platform that automates change management with built-in governance and compliance features.
  • Redgate SQL Change Automation: a tool for automating database deployments and version control, particularly strong in the SQL Server ecosystem.
  • SchemaHero: a Kubernetes-native schema management tool that uses declarative schema definitions.
  • Atlas: a schema management tool that supports declarative and versioned migrations with built-in linting.

References​

  1. Ambler, S.W. & Sadalage, P.J. (2006). Refactoring Databases: Evolutionary Database Design. Addison-Wesley. The foundational text on treating database schemas as evolvable artefacts, with a comprehensive catalogue of database refactoring patterns.

  2. Fowler, M. (2016). "Evolutionary Database Design." martinfowler.com. Available at martinfowler.com/articles/evodb.html. An accessible introduction to the principles of evolutionary database design, including the expand-contract pattern.

  3. Sadalage, P.J. & Fowler, M. (2012). NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence. Addison-Wesley. Extends the evolutionary design thinking to non-relational databases and polyglot persistence architectures.

  4. Flyway Documentation. Available at documentation.red-gate.com/flyway. Official documentation for Flyway, including migration conventions, configuration options, and CI/CD integration patterns.

  5. Liquibase Documentation. Available at docs.liquibase.com. Official documentation for Liquibase, including changelog formats, rollback strategies, and enterprise governance features.

  6. Humble, J. & Farley, D. (2010). Continuous Delivery: Reliable Software Releases through Build, Test, and Deployment Automation. Addison-Wesley. Chapter 12 ("Managing Data") provides essential guidance on integrating database changes into continuous delivery pipelines.