Schema migrations, source control, reviewable SQL scripts, and release safety Interview Questions

Overview

Schema migrations are controlled changes to a database structure over time. They add, alter, or remove tables, columns, indexes, constraints, views, procedures, functions, triggers, permissions, and sometimes data needed by a release. Source control and reviewable SQL scripts make those changes visible, repeatable, auditable, and safer to deploy.

This topic matters because database changes are stateful. Application code can often be rolled back by redeploying a previous artifact, but a database migration may have changed live data, dropped columns, locked large tables, or introduced compatibility problems between old and new application versions. Release safety means designing database changes so they can be reviewed, tested, deployed, monitored, and recovered from with minimal production risk.

For interviews, this topic tests whether you think beyond ALTER TABLE. Strong candidates can explain migration files, idempotent scripts, source-controlled database projects, DACPACs, EF Core migrations, review workflows, expand-and-contract deployments, transactional safety, data backfills, rollback planning, and zero-downtime compatibility.

Core Concepts

What A Schema Migration Is

A schema migration is a versioned change to the database schema.

Examples:

Add a table.
Add a nullable column.
Backfill data.
Add a constraint.
Create or alter a stored procedure.
Create an index.
Rename or drop a column.
Split a table.
Add permissions.

Migrations should be repeatable in lower environments and traceable in production.

Why Source Control Matters

Database changes should be versioned like application code.

Source control provides:

Review history.
Ownership and accountability.
Reproducible deployments.
Pull request review.
CI validation.
Diff visibility.
A way to coordinate database and application changes.
Disaster recovery context.

If production schema changes happen manually with no script in source control, the team loses the ability to reliably recreate or review the system.

Migration-Based Vs State-Based Approaches

There are two common styles:

Approach	Description
Migration-based	Store ordered change scripts or migration classes
State-based	Store desired final schema and generate a deployment plan

Migration-based tools track each change step. EF Core migrations are an example: model changes generate migration files, and applied migrations are tracked in a history table.

State-based tools store object definitions as the source of truth. SQL database projects can build a DACPAC that represents desired schema state; deployment tools compare that artifact with the target database and generate a plan.

Both approaches need review, testing, and production safety.

Reviewable SQL Scripts

Reviewable SQL scripts let humans inspect exactly what will run.

Good review scripts show:

Objects being created, altered, or dropped.
Data movement.
Backfill logic.
Index build options.
Constraint validation.
Permissions.
Potential data loss.
Transaction boundaries.
Rollback or roll-forward plan.

For production, directly applying generated changes without review is risky. Generated scripts can misunderstand intent, especially for renames, column splits, destructive changes, and data migrations.

Idempotent Scripts

An idempotent script can be run safely even if part or all of the change has already been applied. This is useful when deploying to multiple databases or when the exact migration state may vary.

Example:

Code

IF COL_LENGTH('sales.Orders', 'ExternalReference') IS NULL
BEGIN
    ALTER TABLE sales.Orders
    ADD ExternalReference varchar(100) NULL;
END;

Idempotency is useful, but it is not a license to skip review. A script can be idempotent and still unsafe.

Transactional Deployment

Some schema changes can be wrapped in a transaction. If a step fails, the transaction can roll back. This is useful for many metadata changes and small data changes.

However, not every change should be wrapped in one large transaction:

Large backfills can create huge logs.
Long index operations can block.
Some operations have special transaction restrictions.
Rolling back a huge transaction can take a long time.
Long transactions can block application traffic.

Release safety requires understanding the operational effect, not just the syntax.

Expand And Contract

Expand-and-contract is a safe deployment pattern for changes that must remain compatible with old and new application versions.

Example column replacement:

Expand: add the new nullable column.
Deploy app version that writes both old and new columns.
Backfill existing data.
Deploy app version that reads the new column.
Contract: stop writing old column.
Later drop the old column.

This avoids breaking old code while a rolling deployment or rollback is possible.

Backfills

A backfill updates existing data to match a new schema or rule.

Backfill safety considerations:

Batch large updates.
Use predictable ordering.
Avoid long transactions.
Monitor log growth.
Avoid blocking hot tables.
Make the script restartable.
Validate row counts.
Consider off-peak execution.

Example batched backfill:

Code

WHILE 1 = 1
BEGIN
    UPDATE TOP (1000) sales.Orders
    SET ExternalReference = CONVERT(varchar(100), OrderId)
    WHERE ExternalReference IS NULL;

    IF @@ROWCOUNT = 0
        BREAK;
END;

Destructive Changes

Destructive changes include dropping columns, dropping tables, changing data types, making nullable columns non-nullable, and shrinking column lengths.

Treat destructive changes as high risk:

Confirm no callers use the old object.
Keep backups or recovery options.
Prefer staged removal.
Deploy observability before deletion.
Use feature flags or compatibility windows.
Review generated scripts carefully.

Renames are also risky because tools may interpret a rename as drop-and-create unless told otherwise.

Database Projects And DACPACs

SQL database projects store database object definitions in files and can build a DACPAC artifact. A deployment tool can compare the DACPAC to a target database and generate a plan to update the target.

Benefits include:

Source-controlled object definitions.
Build-time validation.
CI integration.
Repeatable deployment artifact.
Deployment scripts and reports.
Reviewable differences.

Risks include:

Generated plans still need review.
Data movement may need custom scripts.
Some changes require careful sequencing with application deployments.

EF Core Migrations

EF Core migrations track model changes in source-controlled migration files. A history table records which migrations have been applied.

For production, generated SQL scripts are usually safer than letting application startup or a command-line tool directly apply migrations without review. Scripts can be reviewed, adjusted, tested, approved, archived, and run through deployment automation.

Review Checklist

A database migration review should ask:

Is this change backward compatible?
Does it support rolling deployment?
Is data loss possible?
Are there long locks?
Are there large log writes?
Is the script idempotent or state-aware?
Is there a rollback or roll-forward plan?
Have dependencies been checked?
Are permissions included?
Is the script tested on production-like data volume?
Does the application deploy depend on this change?

Release Safety

Release-safe migrations are designed around production behavior.

Important practices include:

Deploy schema expansions before code that depends on them.
Avoid dropping objects in the same release that stops using them.
Use small steps for risky changes.
Keep old and new app versions compatible during rolling deploys.
Test against production-like data.
Generate reviewable SQL.
Monitor after deployment.
Have a rollback or roll-forward plan.

Rollback Vs Roll-Forward

Rollback means undoing the change. Roll-forward means applying a new fix that moves the system to a safe state.

For databases, roll-forward is often safer than rollback when live data may have changed after the migration. Dropping a new column might be easy. Reconstructing dropped data may not be.

Plan this before release. Do not invent the recovery strategy during the incident.

Common Mistakes

Common mistakes include:

Making manual production changes that are not in source control.
Applying generated migrations without review.
Dropping or renaming columns in the same release that changes application code.
Running large backfills in one transaction.
Forgetting permissions, views, procedures, or reporting dependencies.
Assuming rollback is easy after data changes.
Deploying code before required schema expansion.
Not testing scripts on realistic data volume.
Ignoring locks, log growth, and index build impact.
Treating migration files as generated noise instead of production code.

Best Practices

Best practices include:

Keep all schema changes in source control.
Use pull requests for SQL changes.
Generate reviewable SQL scripts for production.
Test migrations in CI and staging.
Prefer expand-and-contract for breaking changes.
Make long-running data changes batchable and restartable.
Review execution plans for backfills and validation queries.
Include permissions and dependent objects.
Use deployment reports or dry runs where available.
Monitor locks, errors, duration, and application health after deployment.

Schema migrations, source control, reviewable SQL scripts, and release safety