Database Schema Migrations: Flyway vs Liquibase vs Alembic for Data Teams

database schema migrations are the version-control discipline that decides whether your production database is a reproducible, reviewable, roll-forward artifact — or an untracked pile of hand-run ALTER TABLE statements that nobody can rebuild from scratch and everybody is afraid to touch. Every schema change your team ships — a new column, a renamed index, a widened VARCHAR, a not-null constraint, a lookup table backfill — has to reach dev, staging, and every production replica in the same order, with the same result, and without one engineer's laptop-run patch silently diverging from what the CI pipeline believes is deployed. The engineering problem is not "should we track schema changes in git" — every team past its second engineer needs that — but which migration tool you standardize on and what its versioning, rollback, and CI story costs you for the next five years.

This guide is the data-engineering walkthrough you wished existed the first time an interviewer asked "walk me through how Flyway, Liquibase, and Alembic differ and when you'd pick each," or "your team has schema drift between staging and prod — how did that happen and how do you prevent it," or "explain versioned migrations, repeatable migrations, and why a rollback script is not the same as a down-grade." It walks through the three dominant tools — flyway (versioned SQL-first migrations with baselining and repeatable scripts), liquibase (database-agnostic changelog/changeset files in XML, YAML, or SQL with first-class rollback and preconditions), and alembic (the SQLAlchemy-native tool with autogenerate, a revision graph, and up/down upgrade/downgrade functions) — the four axes that actually separate them (authoring model, rollback story, database portability, and CI/CD ergonomics), the canonical setup for each, and the decision matrix senior engineers use to pick one. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the database practice library →, rehearse on the design practice library →, and sharpen the pipeline axis with the ETL practice library →.

---

On this page

• Why schema changes need version control like code
• Flyway — versioned SQL migrations
• Liquibase — changelogs, changesets, rollback
• Alembic — SQLAlchemy autogenerate and the revision graph
• Decision matrix — pick the tool, wire the CI gate
• Cheat sheet — schema migration recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why schema changes need version control like code

Schema drift is the silent failure mode — migrations are the reproducibility contract that kills it

The one-sentence invariant: database schema migrations are an ordered, immutable, replayable sequence of change scripts checked into version control alongside the application, so that any environment — a fresh laptop, a CI container, a new production replica — can be rebuilt to a known schema version by replaying the same scripts in the same order, and so that every change is reviewed in a pull request instead of typed into a psql prompt at 2 AM. The alternative — hand-run DDL — produces schema drift: the slow, invisible divergence between what your code assumes the schema is, what staging actually has, and what production actually has. Drift is the root cause of the "it works in staging but the migration failed in prod" incident, and migrations are the discipline that makes it structurally impossible.

The three problems migrations solve.

• Drift. Without a migration tool, a schema change lives in exactly one place — the database it was run against. Someone adds an index on staging to fix a slow query, forgets to apply it to prod, and now the same query plan differs across environments. Migrations force every change through a file that must be applied to every environment, so drift cannot accumulate silently.
• Reproducibility. A new engineer should be able to run one command and get a byte-for-byte-correct empty schema. A disaster-recovery rebuild should reconstruct the schema from scripts, not from a stale pg_dump nobody trusts. Migrations make the schema a build artifact — deterministic, versioned, rebuildable.
• Reviewability. A schema change is a code change. It belongs in a pull request, next to the application code that depends on it, reviewed by a human who can catch the missing index, the blocking ALTER TABLE, the not-null column with no default. Migrations put DDL under the same review gate as everything else.

The migration mental model — a ledger plus ordered scripts.

• The scripts. Each change is a file (or a changeset block) with a version identifier — V3__add_orders_status.sql, a Liquibase changeSet id="3", an Alembic revision a1b2c3. Scripts are immutable once applied: you never edit an applied migration, you add a new one.
• The ledger. Every tool keeps a bookkeeping table in the target database — flyway_schema_history, databasechangelog, alembic_version — recording which scripts have already run. On each deploy the tool diffs the scripts on disk against the ledger and applies only the pending ones.
• The ordering. Migrations run in a total (or partially-ordered graph) sequence. Version numbers, timestamps, or a revision down_revision pointer define the order. The tool refuses to apply out of order or re-apply a completed script.
• The checksum. Every tool stores a hash of each applied script in the ledger. Editing an already-applied migration changes its checksum, and the tool aborts the next deploy — this is the guardrail that enforces immutability.

The four axes that separate Flyway, Liquibase, and Alembic.

• Authoring model. How do you write a migration? Flyway: raw SQL files (or Java). Liquibase: database-agnostic changesets in XML/YAML/JSON, or raw SQL. Alembic: Python functions that call a schema-operations API, optionally auto-generated by diffing SQLAlchemy models against the live DB.
• Rollback story. Can you go backwards? Alembic ships a downgrade() per revision by design. Liquibase auto-generates rollback for many change types and lets you hand-write it for the rest. Flyway (open-source) is forward-only by philosophy; paid Flyway supports U-prefixed undo scripts. The distinction between a reverse migration and a restore-from-backup is a senior interview probe.
• Database portability. Do the same scripts run on Postgres, MySQL, Oracle, SQL Server? Liquibase's abstracted changesets are the most portable (it generates dialect-specific SQL). Flyway SQL is portable only to the extent your SQL is. Alembic generates dialect-specific DDL through SQLAlchemy but you often hand-tune per backend.
• CI/CD ergonomics. How cleanly does it drop into a pipeline? All three run as a single CLI command with an exit code. Flyway and Liquibase ship Docker images, Gradle/Maven plugins, and migrate/validate verbs. Alembic is a Python entry point that fits naturally into Python data stacks (dbt-adjacent, Airflow, FastAPI).

What interviewers listen for.

• Do you name drift as the problem migrations solve, not just "tracking changes"? — senior signal.
• Do you say "you never edit an applied migration, you add a new one" unprompted? — required answer.
• Do you distinguish a reverse migration from a restore-from-backup? — senior signal.
• Do you name the ledger table (flyway_schema_history / databasechangelog / alembic_version) as the source of truth for "what's applied"? — required answer.
• Do you describe the CI gate — validate in the pipeline, migrate at deploy — rather than "run the SQL by hand"? — senior signal.

Worked example — the three-tool comparison table

Detailed explanation. The single most useful artifact for a schema-migration interview is a memorised 3×N comparison across Flyway, Liquibase, and Alembic. Every senior discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical team standardizing migrations for an orders service on Postgres with a growing analytics footprint.

• Stack. Postgres 16 primary, a Python service (SQLAlchemy ORM), an analytics warehouse fed by CDC.
• Constraint. Every environment (dev, CI, staging, prod) must reach the same schema version deterministically.
• Team profile. Backend engineers who write Python; a data team that also writes raw SQL; a compliance requirement for an auditable change log.

Question. Build the three-tool comparison for the orders service and note which axis each tool wins.

Input.

Axis	Flyway	Liquibase	Alembic
Authoring	raw SQL (V__, R__)	XML/YAML/SQL changesets	Python upgrade/downgrade
Rollback	forward-only (OSS); U__ undo (paid)	auto + hand-written rollback	downgrade() per revision
Portability	as portable as your SQL	dialect-agnostic changesets	dialect via SQLAlchemy
Autogenerate	no	limited (diff command, paid tiers)	yes (diff models vs DB)
Ledger table	flyway_schema_history	databasechangelog	alembic_version

Code.

-- The change all three tools will express: add a status column + index to orders
-- Flyway version (V3__add_orders_status.sql) — raw SQL, forward only
ALTER TABLE public.orders
    ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

CREATE INDEX idx_orders_status ON public.orders (status);

<!-- Liquibase version (changeset in db.changelog.xml) — database-agnostic -->
<changeSet id="3" author="data-team">
    <addColumn tableName="orders">
        <column name="status" type="TEXT" defaultValue="pending">
            <constraints nullable="false"/>
        </column>
    </addColumn>
    <createIndex indexName="idx_orders_status" tableName="orders">
        <column name="status"/>
    </createIndex>
    <rollback>
        <dropIndex indexName="idx_orders_status" tableName="orders"/>
        <dropColumn tableName="orders" columnName="status"/>
    </rollback>
</changeSet>

# Alembic version (revision file) — Python ops, reversible by construction
def upgrade() -> None:
    op.add_column(
        "orders",
        sa.Column("status", sa.Text(), nullable=False, server_default="pending"),
    )
    op.create_index("idx_orders_status", "orders", ["status"])


def downgrade() -> None:
    op.drop_index("idx_orders_status", table_name="orders")
    op.drop_column("orders", "status")

Step-by-step explanation.

1. The same logical change — add a status column and index — is expressed three ways. Flyway is the terse one: raw SQL, exactly what you'd type into psql, versioned by the V3__ filename prefix. There is no rollback block in open-source Flyway; the philosophy is roll-forward.
2. Liquibase describes the change abstractly (<addColumn>, <createIndex>) so it can emit Postgres, MySQL, or Oracle DDL from one file, and it carries an explicit <rollback> block that Liquibase runs on liquibase rollback.
3. Alembic writes the change as Python op.* calls and pairs every upgrade() with a hand-or-auto-written downgrade(). Because Alembic sits on SQLAlchemy, sa.Column/sa.Text reuse the exact type system your ORM models use.
4. All three write to a ledger table on apply. Flyway records V3 in flyway_schema_history with a checksum; Liquibase records the changeset id+author+filename in databasechangelog; Alembic stores the single current revision id in alembic_version.
5. The choice is team-driven, not tool-quality-driven. A raw-SQL data team leans Flyway; a multi-database enterprise leans Liquibase; a Python/SQLAlchemy shop leans Alembic. All three solve drift; they differ on authoring ergonomics and rollback philosophy.

Output.

Requirement	Best-fit tool	Why
Raw-SQL authoring, Postgres-only	Flyway	least ceremony; SQL is the source
Multi-database, auditable changelog	Liquibase	dialect-agnostic + rich databasechangelog
Python service on SQLAlchemy	Alembic	autogenerate from models; native downgrade
Strict roll-forward discipline	Flyway (OSS)	forward-only by design
Frequent reversible schema experiments	Alembic	downgrade() per revision

Rule of thumb. Never pick a migration tool by popularity. Pick it by (authoring model × rollback needs × database portability × your team's language) — the four axes. Write the comparison table on a whiteboard first; the tool falls out of the constraints.

Worked example — reconstructing how schema drift happens

Detailed explanation. Drift is easier to prevent than to explain after the fact, so senior interviewers often ask you to reconstruct a drift incident. The canonical story: a hotfix index added by hand to production, never captured in a migration, invisible until a migration later assumes it does not exist. Walk through the timeline and the fix.

• The setup. Migrations are tracked, but engineers have direct psql access to prod.
• The mistake. An on-call engineer runs CREATE INDEX CONCURRENTLY idx_hotfix ON orders (created_at) to fix a slow query, and never writes a migration for it.
• The detonation. Two weeks later a migration runs CREATE INDEX idx_hotfix ON orders (created_at) (someone finally wrote it) and it fails on prod with "relation already exists" — because prod, uniquely, already has the hand-run index.

Question. Reconstruct the drift timeline and design the guardrail that makes it structurally impossible.

Input.

Environment	Has idx_hotfix before migration?	Migration result
dev	no	success
CI	no	success
staging	no	success
prod	yes (hand-run)	FAILS — already exists

Code.

-- The guardrail: migrations are idempotent-safe AND direct DDL is revoked.
-- 1. Make the migration itself defensive (works whether or not the object exists)
CREATE INDEX IF NOT EXISTS idx_hotfix ON public.orders (created_at);

-- 2. Structurally prevent hand-run DDL: revoke DDL from human roles,
--    grant it only to the migration service account.
REVOKE CREATE ON SCHEMA public FROM app_engineers;
GRANT  CREATE ON SCHEMA public TO   migration_runner;

-- 3. A drift detector run in CI: dump the live schema and diff against
--    the schema that replaying all migrations produces.
--    (pseudo-pipeline; real tools: migra, apgdiff, or `flyway check`)
--    pg_dump --schema-only prod         > live_schema.sql
--    (replay all migrations on scratch) > expected_schema.sql
--    diff expected_schema.sql live_schema.sql   # must be empty

Step-by-step explanation.

1. The root cause is out-of-band DDL: a change reached prod without going through a migration file, so the migration ledger and the real schema diverged. Every environment except prod is in a clean state; prod is uniquely dirty.
2. CREATE INDEX IF NOT EXISTS makes the migration tolerant of a pre-existing object — but this is a band-aid, not a fix. It hides drift instead of preventing it, and it only works for objects that happen to match; a hand-run index with different columns would still cause silent divergence.
3. The real fix is removing the ability to run out-of-band DDL. Revoke CREATE (and ALTER/DROP) from human roles and grant it only to the migration service account. Now the only path to a schema change is a migration file — drift cannot originate from a psql prompt.
4. The belt-and-braces layer is a drift detector in CI: replay every migration onto a scratch database to produce the "expected" schema, dump the live schema, and diff them. A non-empty diff fails the build and surfaces drift the moment it appears, not two weeks later.
5. After the guardrails, the "relation already exists" class of failure disappears, because the only way an index reaches prod is through a reviewed migration that every environment applies identically.

Output.

Layer	Mechanism	Drift outcome
Migration file	idempotent DDL (IF NOT EXISTS)	tolerates existing objects (band-aid)
Permissions	revoke DDL from humans	drift cannot originate
CI detector	replay-and-diff schema	drift surfaces immediately
Review gate	migrations in pull requests	drift is caught pre-merge

Rule of thumb. Prevent drift at the permission layer, not the SQL layer. If humans can run DDL on prod, drift is inevitable no matter how disciplined the team is. Grant DDL only to the migration runner and detect drift in CI with a replay-and-diff check.

Worked example — the "pick the tool" decision tree

Detailed explanation. Given a new service, the senior engineer runs a short decision tree to pick a migration tool. Codifying the tree makes the interview answer reproducible: an interviewer can hand you a stack and you walk the tree out loud. Walk through the tree with three canonical scenarios — a raw-SQL Postgres data team, a multi-database Java enterprise, and a Python FastAPI service on SQLAlchemy.

• Q1. Is the application written in Python on SQLAlchemy? → yes = strongly consider Alembic (autogenerate from models is a huge win); no = go to Q2.
• Q2. Do you target more than one database engine from the same schema? → yes = Liquibase (dialect-agnostic changesets); no = go to Q3.
• Q3. Does the team author changes as raw SQL and prefer roll-forward discipline? → yes = Flyway; no = go to Q4.
• Q4. Do you need a rich, auditable changelog with contexts/labels for compliance? → yes = Liquibase; no = default to Flyway for simplicity.

Question. Walk the decision tree for the three scenarios and record the tool each ends up with.

Input.

Scenario	Q1 (Python/SQLAlchemy?)	Q2 (multi-DB?)	Q3 (raw SQL + roll-forward?)
Postgres data team	no	no	yes
Java multi-DB enterprise	no	yes	—
FastAPI + SQLAlchemy	yes	no	—

Code.

# Decision-tree helper (illustrative)
def pick_migration_tool(python_sqlalchemy: bool,
                        multi_database: bool,
                        raw_sql_roll_forward: bool,
                        needs_audit_changelog: bool) -> str:
    """Return the best-fit schema-migration tool for a service."""
    if python_sqlalchemy:
        return "alembic"          # autogenerate from models wins
    if multi_database:
        return "liquibase"        # dialect-agnostic changesets
    if raw_sql_roll_forward:
        return "flyway"           # least ceremony, forward-only
    if needs_audit_changelog:
        return "liquibase"        # rich databasechangelog + contexts
    return "flyway"               # simplest default


print(pick_migration_tool(False, False, True,  False))  # → 'flyway'
print(pick_migration_tool(False, True,  False, False))  # → 'liquibase'
print(pick_migration_tool(True,  False, False, False))  # → 'alembic'

Step-by-step explanation.

1. Scenario 1 — a Postgres-only data team that writes raw SQL and values strict roll-forward discipline. Q1 = no, Q2 = no, Q3 = yes → Flyway. The team gets versioned SQL files with the least ceremony and a forward-only philosophy that matches their operational culture.
2. Scenario 2 — a Java enterprise deploying the same schema to Postgres in the cloud and Oracle on-prem. Q1 = no, Q2 = yes → Liquibase. The abstracted changesets generate correct dialect-specific DDL for both engines from a single changelog.
3. Scenario 3 — a FastAPI service whose models are already SQLAlchemy classes. Q1 = yes → Alembic. alembic revision --autogenerate diffs the models against the live DB and writes most of the migration for you; the type system is shared with the ORM.
4. The tree is deliberately ordered by strongest signal first. SQLAlchemy is the most decisive input (Alembic is purpose-built for it); multi-database is next (only Liquibase truly abstracts dialects); everything else defaults to Flyway for simplicity.
5. The tree is not a religion. A Python team that writes raw SQL and dislikes autogenerate can still use Flyway; a data team that needs multi-database can use Liquibase even without Java. The tree gives a defensible default, which is exactly what an interviewer wants.

Output.

Scenario	Tool	Deciding axis
Postgres data team	Flyway	raw SQL + roll-forward
Java multi-DB enterprise	Liquibase	database portability
FastAPI + SQLAlchemy	Alembic	autogenerate from models

Rule of thumb. The decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any stack and get a tool name — with a reason — in under 60 seconds.

Senior interview question on schema-migration strategy

A senior interviewer often opens with: "You join a team that manages its Postgres schema by hand-running ALTER TABLE on each environment. Staging and prod have already drifted. Design the migration strategy you'd introduce — the tool-agnostic conventions, how you'd baseline the existing schema, how you'd stop drift from recurring, and how migrations would run in CI/CD."

Solution Using versioned forward-only migrations with a baseline and a CI gate

-- Step 1 — capture the CURRENT production schema as an immutable baseline.
-- (Tool-agnostic: this is the V1 that every environment starts from.)
-- Generated once with: pg_dump --schema-only --no-owner production > V1__baseline.sql
-- V1__baseline.sql (excerpt)
CREATE TABLE public.orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    total_cents BIGINT      NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Step 2 — every NEW change is a forward migration file, reviewed in a PR.
-- V2__add_orders_status.sql
ALTER TABLE public.orders
    ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';
CREATE INDEX idx_orders_status ON public.orders (status);

# Step 3 — the CI/CD gate (GitHub Actions). validate on PR; migrate on deploy.
name: db-migrations
on: [pull_request, push]
jobs:
  validate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_PASSWORD: ci }
        ports: ["5432:5432"]
    steps:
      - uses: actions/checkout@v4
      # Replay ALL migrations onto a scratch DB — fails if any script is broken
      - name: apply migrations to scratch db
        run: flyway -url=jdbc:postgresql://localhost:5432/postgres \
                    -user=postgres -password=ci -locations=filesystem:./sql migrate
      # Drift check: the replayed schema must match a committed snapshot
      - name: drift check
        run: |
          pg_dump --schema-only -h localhost -U postgres postgres > /tmp/built.sql
          diff -u expected_schema.sql /tmp/built.sql

Step-by-step trace.

Step	Before (hand-run DDL)	After (versioned migrations)
Source of truth	the database itself	the migration files in git
Baseline	none	V1__baseline.sql = current prod
New change	psql on each env by hand	one V2__ file, reviewed in a PR
Drift	accumulates silently	detected by replay-and-diff in CI
Apply mechanism	manual, error-prone	flyway migrate at deploy, one command
Rollback	restore from backup, hope	roll forward with a corrective V3__

After the migration, every environment starts from the V1 baseline and replays the same ordered files. The CI gate fails any PR whose migrations do not apply cleanly onto a fresh Postgres, and the drift check fails any PR that would leave the built schema diverging from the committed snapshot. Hand-run DDL is revoked; the schema is now a reproducible build artifact.

Output:

Metric	Before	After
Environments reproducible from scripts	no	yes
Drift detection	none	every PR (replay + diff)
Change review	none	pull request per migration
Time to rebuild a fresh schema	hours (guesswork)	one migrate command
Rollback story	restore from backup	forward-fix migration

Why this works — concept by concept:

• Baseline migration — capturing the current prod schema as V1__baseline.sql gives every environment a shared, immutable starting point. Without a baseline, an existing database cannot adopt migrations because the tool would try to re-create tables that already exist. The baseline is the "how do we start from where we already are" answer.
• Forward-only files — each change is a new, immutable file appended to the sequence. You never edit V1 or V2; you add V3. This makes the applied history append-only and reproducible, and it is enforced by the tool's checksum on each ledger row.
• CI replay-and-diff — replaying all migrations onto a throwaway Postgres proves the scripts are internally consistent and produce a known schema. Diffing against a committed snapshot catches drift the moment a PR introduces it, turning a 2-week-latent incident into a red build.
• Revoked human DDL — the only durable way to stop drift is to remove the ability to create it. Grant DDL to the migration runner alone; humans propose changes as files, not as live statements.
• Cost — one baseline (one-time), one CI job, and one file per change (ongoing). The eliminated cost is the entire class of "works in staging, breaks in prod" schema incidents plus the developer time spent reverse-engineering what prod's schema actually is. Net O(1) per change versus O(environments × manual-steps) per change under hand-run DDL.

Database
Topic — database
Database schema and DDL problems

Practice →

Design Topic — design Design problems on schema evolution

Practice →

---

2. Flyway — versioned SQL migrations

flyway is the least-ceremony tool — raw SQL files named V{n}__{desc}.sql, applied in order, tracked in flyway_schema_history, with repeatable migrations and baselining for legacy databases

The mental model in one line: flyway is the SQL-first migration tool where each change is a plain .sql file whose filename encodes its version (V2__add_status.sql), Flyway scans a directory, compares the files against the flyway_schema_history ledger, and applies the pending ones in version order inside a transaction — it adds repeatable migrations (re-run whenever their checksum changes, ideal for views and functions) and baselining (adopt an existing production database as version N without replaying earlier scripts), and its open-source edition is deliberately forward-only. Every data team that likes writing raw SQL and wants the thinnest possible layer over psql reaches for Flyway.

The four axes for Flyway.

• Authoring. Raw SQL files (or Java migrations for programmatic changes). The filename is the metadata: V2__add_orders_status.sql. No XML, no Python, no DSL — the SQL you'd run by hand, versioned.
• Rollback. Open-source Flyway is forward-only: there is no flyway undo. The paid tiers add U-prefixed undo scripts (U2__...sql) that mirror each versioned migration. The Flyway philosophy is that a corrective forward migration is safer and more auditable than a reverse one.
• Portability. Flyway runs against ~20 databases, but a Flyway SQL file is only as portable as the SQL inside it. SERIAL is Postgres, AUTO_INCREMENT is MySQL; Flyway does not translate. Portability is the author's responsibility.
• CI/CD. A single flyway migrate command with an exit code. Ships a Docker image, Maven/Gradle plugins, and validate/info/repair verbs. Drops into any pipeline in one step.

The three migration file types.

• Versioned (V). V{version}__{description}.sql. Applied exactly once, in version order, recorded in the ledger with a checksum. The version is a dotted number (V1, V2, V2.1, V20260803.1); Flyway sorts them numerically. This is 90% of your migrations.
• Repeatable (R). R__{description}.sql. No version. Re-applied every time its checksum changes, always after all pending versioned migrations. Perfect for objects you want to redefine idempotently — views, stored procedures, functions, materialized-view definitions — where "the latest definition wins" is the right semantic.
• Undo (U, paid). U{version}__{description}.sql. The reverse of the matching V. Applied by flyway undo, newest-first. Open-source users skip this and roll forward instead.

Baselining — adopting an existing database.

• The problem. You have a five-year-old production Postgres with no migration history. You cannot replay V1..V40 onto it — the tables already exist.
• The solution. flyway baseline -baselineVersion=1 marks the current database as being at version 1 without running any scripts. Flyway inserts a baseline row into flyway_schema_history and only applies migrations above the baseline version going forward.
• The convention. Ship a V1__baseline.sql (a pg_dump --schema-only) so that a fresh database can be built from scratch, while existing databases are baselined at V1. Both paths converge on the same schema.

The flyway_schema_history ledger.

• What it stores. One row per applied migration: installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success.
• The checksum guard. If you edit an already-applied V file, its on-disk checksum no longer matches the ledger, and flyway validate (and the next migrate) fails with a checksum mismatch. This enforces immutability — the guardrail against silently rewriting history.
• flyway repair. The escape hatch: recomputes checksums and cleans failed rows. Used sparingly, after a deliberate correction, never as a routine.

Common interview probes on Flyway.

• "What's a repeatable migration and when do you use one?" — re-run on checksum change; use for views/functions where latest-definition-wins.
• "How do you adopt Flyway on an existing production DB?" — baseline at the current version; ship a baseline script for fresh builds.
• "Does open-source Flyway support rollback?" — no; it's forward-only. Paid adds U__ undo. Roll forward with a corrective migration.
• "What happens if you edit an applied migration?" — checksum mismatch; validate fails; you must add a new migration instead.

Worked example — a versioned Flyway migration set

Detailed explanation. The canonical Flyway layout: a sql/ directory of V__ files applied in order, plus the config that points Flyway at a Postgres. Build the first three migrations for the orders service and show what lands in the ledger.

• Layout. sql/V1__baseline.sql, sql/V2__add_orders_status.sql, sql/V3__add_order_items.sql.
• Config. flyway.conf with the JDBC URL, user, and locations.
• Ledger. After migrate, three rows in flyway_schema_history.

Question. Write the three migration files, the config, and show the resulting ledger.

Input.

File	Version	Purpose
V1__baseline.sql	1	initial orders table
V2__add_orders_status.sql	2	status column + index
V3__add_order_items.sql	3	child order_items table + FK

Code.

-- sql/V1__baseline.sql
CREATE TABLE public.orders (
    id          BIGSERIAL   PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    total_cents BIGINT      NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- sql/V2__add_orders_status.sql
ALTER TABLE public.orders
    ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';
CREATE INDEX idx_orders_status ON public.orders (status);

-- sql/V3__add_order_items.sql
CREATE TABLE public.order_items (
    id          BIGSERIAL PRIMARY KEY,
    order_id    BIGINT    NOT NULL REFERENCES public.orders (id) ON DELETE CASCADE,
    sku         TEXT      NOT NULL,
    qty         INT       NOT NULL CHECK (qty > 0),
    price_cents BIGINT    NOT NULL
);
CREATE INDEX idx_order_items_order_id ON public.order_items (order_id);

# flyway.conf
flyway.url=jdbc:postgresql://db-primary.internal:5432/production
flyway.user=migration_runner
flyway.password=${FLYWAY_PASSWORD}
flyway.locations=filesystem:./sql
flyway.schemas=public
flyway.baselineOnMigrate=false

# Apply — one command, exits non-zero on any failure
flyway -configFiles=flyway.conf migrate

# Inspect what's applied
flyway -configFiles=flyway.conf info

Step-by-step explanation.

1. Each file's version comes entirely from its name: V1, V2, V3. Flyway sorts numerically and applies pending ones in that order. The double underscore __ separates the version from the human description; the description becomes a ledger column.
2. V1__baseline.sql creates the initial table. On a fresh database this runs; on an existing database you would instead flyway baseline -baselineVersion=1 so V1 is marked applied without executing (the tables already exist).
3. V2 and V3 are ordinary forward changes — a column here, a child table there. Each runs inside its own transaction (on Postgres, which supports transactional DDL), so a failure mid-file rolls the whole file back and the ledger is not advanced.
4. flyway.conf points at the target with flyway.locations=filesystem:./sql. The migration_runner role is the only role with DDL rights — humans cannot run schema changes directly.
5. flyway migrate applies the pending files and appends one ledger row each; flyway info prints the applied/pending table. A second migrate with no new files is a no-op — Flyway sees the ledger already contains V1–V3.

Output.

installed_rank	version	description	type	success
1	1	baseline	SQL	true
2	2	add orders status	SQL	true
3	3	add order items	SQL	true

Rule of thumb. Name every Flyway file V{n}__{snake_case_description}.sql, keep one logical change per file, and let the filename be the only place the version lives. On Postgres, rely on transactional DDL so a failed migration leaves the ledger un-advanced and the schema untouched.

Worked example — repeatable migrations for views

Detailed explanation. Views, functions, and stored procedures are painful as versioned migrations: every change would need a new V file with DROP ... CREATE .... Flyway's repeatable (R__) migrations solve this — Flyway re-runs the file whenever its checksum changes, always after the versioned ones, so the file holds the single canonical definition and "latest wins." Build a repeatable migration for an orders_summary view.

• The file. R__orders_summary_view.sql with CREATE OR REPLACE VIEW.
• The semantic. Edit the file → checksum changes → Flyway re-applies on next migrate.
• The ordering. Repeatable migrations always run after all pending versioned ones.

Question. Write the repeatable view migration and explain the re-apply trigger.

Input.

Property	Value
File	R__orders_summary_view.sql
Object	orders_summary view
Re-run trigger	checksum change
Order	after all versioned migrations

Code.

-- sql/R__orders_summary_view.sql
-- Repeatable: re-applied whenever this file's checksum changes.
CREATE OR REPLACE VIEW public.orders_summary AS
SELECT
    o.customer_id,
    count(*)                              AS order_count,
    sum(o.total_cents)                    AS lifetime_cents,
    count(*) FILTER (WHERE o.status = 'shipped') AS shipped_count,
    max(o.created_at)                     AS last_order_at
FROM   public.orders o
GROUP  BY o.customer_id;

# First migrate applies it once (checksum recorded in flyway_schema_history)
flyway migrate     # → R__orders_summary_view applied

# Edit the file (e.g. add a column), migrate again:
flyway migrate     # → checksum changed → R__orders_summary_view RE-applied

# Migrate with no edits:
flyway migrate     # → no-op (checksum matches ledger)

Step-by-step explanation.

1. The R__ prefix (no version number) marks this as repeatable. Flyway does not assign it a version; instead it tracks the file by name and stores its checksum in the ledger like any other row.
2. CREATE OR REPLACE VIEW is the idempotent DDL that makes repeatable migrations work: re-running it simply redefines the view to the latest definition, with no DROP needed and no error if it already exists.
3. On the first migrate, Flyway applies the file and records its checksum. On later migrate runs, Flyway compares the on-disk checksum to the ledger — if they match, it skips; if the file changed, it re-applies.
4. Repeatable migrations always run after all pending versioned migrations in a single migrate. This ordering is deliberate: your view can safely reference columns added by a V migration in the same run, because the columns exist by the time the R file runs.
5. This pattern keeps view/function/procedure definitions as a single readable file in git rather than a trail of V17__update_view.sql, V23__update_view_again.sql files — the diff history lives in git, and the database always holds the latest definition.

Output.

migrate run	file edited?	Flyway action
1	(first apply)	apply; record checksum
2	yes (added column)	re-apply; update checksum
3	no	skip (checksum matches)
4	yes (fixed filter)	re-apply; update checksum

Rule of thumb. Put every view, function, and stored procedure in a repeatable (R__) migration with CREATE OR REPLACE, and reserve versioned (V__) migrations for table/column/index/constraint changes. Repeatable migrations let git hold the definition's history while the database always converges on the latest.

Worked example — baselining an existing production database

Detailed explanation. The most common real-world Flyway adoption task: introduce Flyway to a long-lived production database that already has tables but no migration history. You cannot run V1__baseline.sql (the tables exist), so you baseline — mark the current state as an applied version — and only apply future migrations. Walk through the whole adoption.

• The state. Prod Postgres with an orders table created by hand years ago; no flyway_schema_history.
• The baseline. flyway baseline -baselineVersion=1 inserts a baseline marker; V1 is considered applied without running.
• Fresh builds. A V1__baseline.sql (schema dump) so a brand-new database reaches the same starting schema.

Question. Adopt Flyway on the existing prod database and prove a fresh database and prod converge.

Input.

Environment	Has tables already?	Adoption command
existing prod	yes	flyway baseline -baselineVersion=1
fresh dev/CI	no	flyway migrate (runs V1__baseline.sql)

Code.

# 1. On EXISTING prod — do NOT run V1; baseline at version 1.
flyway -configFiles=flyway.conf \
       baseline -baselineVersion=1 -baselineDescription="adopt existing schema"
# Flyway inserts a baseline row; migrations <= 1 are skipped, > 1 will apply.

# 2. On a FRESH dev/CI database — V1__baseline.sql actually runs.
flyway -configFiles=flyway.conf migrate

# flyway.conf — baselineOnMigrate lets fresh runs auto-baseline if needed
flyway.url=jdbc:postgresql://db-primary.internal:5432/production
flyway.user=migration_runner
flyway.password=${FLYWAY_PASSWORD}
flyway.locations=filesystem:./sql
flyway.baselineVersion=1
flyway.baselineOnMigrate=true

-- 3. From now on, V2+ apply identically to BOTH prod and fresh databases.
-- sql/V2__add_orders_status.sql
ALTER TABLE public.orders
    ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

Step-by-step explanation.

1. On existing prod, flyway baseline -baselineVersion=1 writes a single baseline row into flyway_schema_history marking version 1 as the starting point. Flyway will now skip any migration with version ≤ 1 and apply only versions > 1. Crucially, it does not execute V1__baseline.sql — the tables already exist, so running it would error.
2. On a fresh dev or CI database, there is nothing to baseline, so flyway migrate actually executes V1__baseline.sql to create the tables, then continues to V2, V3, and so on. baselineOnMigrate=true lets Flyway auto-insert the baseline marker on the first migrate against a non-empty schema, smoothing the adoption.
3. Both paths converge: existing prod is told it's at V1; fresh databases are built to V1. From V2 onward the two are identical, because V2+ run on both.
4. The baseline description ("adopt existing schema") documents why the baseline exists in the ledger — future engineers reading flyway info see that V1 was a baseline, not a normally-applied migration.
5. This is the standard "brownfield adoption" recipe. The alternative — trying to reverse-engineer the full migration history of a five-year-old database — is a waste of time. Baseline the current state, ship a matching baseline script for fresh builds, and only track changes forward.

Output.

Environment	V1 status	V2 status	Converged schema?
existing prod	baseline (not run)	applied	yes
fresh CI	applied (script run)	applied	yes
new replica	applied (script run)	applied	yes

Rule of thumb. For any existing database with no migration history, baseline at the current version and ship a matching baseline script so fresh builds converge. Never try to reconstruct the full history of a legacy database — baseline the present and track the future.

Senior interview question on Flyway

A senior interviewer might ask: "You're adopting Flyway on a 4-year-old Postgres with tables created by hand, several views changed monthly, and a rule that production changes must be roll-forward only. Design the Flyway layout — the baseline, the versioned migrations, the repeatable migrations for the views, the config, and the CI gate that blocks a PR whose migrations don't apply cleanly."

Solution Using Flyway with baseline + versioned + repeatable + a validate gate

# 1. flyway.conf — one source of truth for connection + layout
flyway.url=jdbc:postgresql://db-primary.internal:5432/production
flyway.user=migration_runner
flyway.password=${FLYWAY_PASSWORD}
flyway.locations=filesystem:./sql
flyway.schemas=public
flyway.baselineVersion=1
flyway.baselineOnMigrate=true
flyway.validateOnMigrate=true

-- 2. Layout under ./sql
-- V1__baseline.sql              (pg_dump --schema-only of current prod)
-- V2__add_orders_status.sql     (forward change)
-- V3__add_order_items.sql       (forward change)
-- R__orders_summary_view.sql    (repeatable; latest view definition wins)
-- R__fn_customer_ltv.sql        (repeatable; stored function)

-- R__orders_summary_view.sql
CREATE OR REPLACE VIEW public.orders_summary AS
SELECT o.customer_id,
       count(*)           AS order_count,
       sum(o.total_cents) AS lifetime_cents
FROM   public.orders o
GROUP  BY o.customer_id;

# 3. CI gate (GitHub Actions) — validate + migrate on a throwaway Postgres
name: flyway-gate
on: [pull_request]
jobs:
  migrate-scratch:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_PASSWORD: ci }
        ports: ["5432:5432"]
    steps:
      - uses: actions/checkout@v4
      - name: flyway migrate (scratch)
        run: |
          docker run --rm --network host -v $PWD/sql:/flyway/sql flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/postgres \
            -user=postgres -password=ci -locations=filesystem:/flyway/sql \
            migrate
      - name: flyway validate
        run: |
          docker run --rm --network host -v $PWD/sql:/flyway/sql flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/postgres \
            -user=postgres -password=ci -locations=filesystem:/flyway/sql \
            validate

Step-by-step trace.

Concern	Answer	Reasoning
Existing DB adoption	baselineOnMigrate=true, baselineVersion=1	mark current prod as V1, don't re-run it
Fresh build	V1__baseline.sql runs on empty DB	prod and fresh converge
Forward changes	V2__, V3__	one file per change, immutable
Monthly view changes	R__ repeatable	latest definition wins; no version churn
Roll-forward rule	no U__ scripts	corrective forward migration only
CI gate	migrate + validate on scratch Postgres	broken migration fails the PR

After rollout, existing prod is baselined at V1 and applies only V2+; fresh CI databases build from V1 up. The monthly view edits are single-file R__ changes whose git diff is the review artifact. The CI gate spins up a throwaway Postgres, replays every migration, and runs validate (which catches checksum drift and missing files) — a broken migration can never reach main.

Output:

Metric	Value
Adoption on legacy DB	baseline at V1 (no replay)
View change workflow	edit one R__ file
Rollback approach	roll forward (corrective V)
CI protection	migrate + validate on scratch DB
Checksum immutability	enforced by validateOnMigrate

Why this works — concept by concept:

• Baseline + baseline script — baselineOnMigrate adopts the legacy schema without replay while V1__baseline.sql builds fresh databases; both converge, solving the brownfield-adoption problem cleanly.
• Versioned files — one immutable V__ file per table/column/index change, applied in numeric order, each recorded with a checksum. This is the append-only history that makes environments reproducible.
• Repeatable migrations — the R__ files hold view and function definitions with CREATE OR REPLACE; Flyway re-applies them whenever their checksum changes, so monthly view edits never spawn new versioned files.
• validate in CI — flyway validate compares on-disk checksums to the ledger and confirms no applied migration was edited; combined with a scratch-DB migrate, it turns any broken or rewritten migration into a red build.
• Cost — one baseline, one config, one file per change, one CI job. The eliminated cost is schema drift, hand-run production DDL, and the "which view definition is actually live" confusion. Forward-only discipline trades the theoretical convenience of undo for the operational safety of an auditable, roll-forward-only history.

SQL
Topic — sql
SQL DDL and versioned-change problems

Practice →

Database Topic — database Database migration and baseline problems

Practice →

---

3. Liquibase — changelogs, changesets, rollback

liquibase describes changes abstractly in a changelog of changesets — XML/YAML/SQL, first-class rollback, preconditions, and true database-agnostic DDL generation

The mental model in one line: liquibase is the database-agnostic migration tool where a changelog file lists ordered changesets, each identified by an id + author + filename triple, describing a change either abstractly (<addColumn>, <createTable>) so Liquibase can generate the correct dialect-specific SQL for Postgres, MySQL, Oracle, or SQL Server, or as raw SQL when you need it — and each changeset can carry an explicit rollback block and preConditions guards, tracked in the databasechangelog ledger by a per-changeset checksum (MD5SUM). Every enterprise that ships one schema to multiple database engines, or needs a rich auditable change record with contexts and labels, reaches for Liquibase.

The four axes for Liquibase.

• Authoring. A changelog (XML, YAML, JSON, or SQL) containing changesets. Abstract change types (addColumn, createIndex, addForeignKeyConstraint) are database-agnostic; <sql> and formatted-SQL changesets let you drop to raw SQL when the abstraction doesn't cover your case.
• Rollback. First-class. Liquibase auto-generates rollback for many change types (an addColumn rolls back to dropColumn) and lets you write an explicit <rollback> for the rest. liquibase rollbackCount, rollbackToDate, and rollback <tag> walk the ledger backward.
• Portability. The strongest of the three. Abstract changesets emit dialect-correct DDL per target, so the same changelog runs on Postgres in the cloud and Oracle on-prem. The dbms attribute scopes a changeset to specific engines when they genuinely differ.
• CI/CD. liquibase update applies; liquibase updateSQL dry-runs (prints the SQL without executing); liquibase validate checks the changelog; liquibase status lists pending changesets. Ships a Docker image and Maven/Gradle plugins.

The changeset — the atomic unit.

• Identity. A changeset is uniquely identified by id + author + the changelog filename. This triple is what Liquibase records in databasechangelog; it is how Liquibase knows a changeset has run.
• Immutability + checksum. Each applied changeset stores an MD5SUM. Editing an applied changeset changes the checksum and Liquibase aborts (unless you explicitly set runOnChange or clear the checksum). Same immutability guarantee as Flyway, at changeset granularity.
• Atomicity. By default each changeset runs in its own transaction and is the unit of rollback. You keep one logical change per changeset so rollback is clean.
• Attributes. context (dev/test/prod gating), labels (arbitrary tags for selective runs), runOnChange, runAlways, failOnError, dbms.

Rollback and preconditions.

• Auto rollback. For reversible change types (add column, create table, create index) Liquibase infers the inverse — no rollback block needed.
• Explicit rollback. For irreversible or custom changes (raw <sql>, data backfills), you write the <rollback> block yourself. If you omit it, rollback errors rather than guessing.
• Preconditions. <preConditions> are guards checked before a changeset runs — tableExists, columnExists, sqlCheck, not. On failure you choose onFail="HALT", MARK_RAN, CONTINUE, or WARN. This is how you make a changeset safe against partial or drifted states.

Contexts and labels — selective execution.

• Contexts. context="prod" (or dev, test) gates which changesets run in which environment. liquibase update --contexts=prod applies only prod-context changesets. Ideal for seed data that differs per environment.
• Labels. Free-form tags (labels="reporting,v2") evaluated with a boolean expression at run time (--labels="reporting AND !experimental"). More expressive than contexts for feature-flag-style rollouts.

Common interview probes on Liquibase.

• "How does Liquibase support multiple databases from one changelog?" — abstract change types generate dialect-specific SQL; dbms scopes the exceptions.
• "What identifies a changeset uniquely?" — the id + author + filename triple, plus the checksum.
• "How do preconditions help?" — guard a changeset against a drifted or partial schema; choose HALT/MARK_RAN/CONTINUE on failure.
• "How do you roll back the last three changesets?" — liquibase rollbackCount 3, using each changeset's rollback block (auto or explicit).

Worked example — an XML changelog with changesets

Detailed explanation. The canonical Liquibase layout: a master changelog that <include>s per-feature changelog files, each holding ordered changesets. Build the master plus the first feature changelog for the orders service using abstract change types.

• Master. db.changelog-master.xml including feature files in order.
• Feature. changelog/001-orders.xml with a createTable and an addColumn changeset.
• Ledger. After update, one databasechangelog row per changeset.

Question. Write the master changelog and the first feature changelog with two changesets.

Input.

File	Changesets	Change types
db.changelog-master.xml	—	includes feature files
changelog/001-orders.xml	id=1, id=2	createTable, addColumn+createIndex

Code.

<!-- db.changelog-master.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.25.xsd">

    <include file="changelog/001-orders.xml" relativeToChangelogFile="true"/>
    <!-- future features appended here, in order -->
</databaseChangeLog>

<!-- changelog/001-orders.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.25.xsd">

    <changeSet id="1" author="data-team">
        <comment>Create the orders table</comment>
        <createTable tableName="orders">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="customer_id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="total_cents" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="created_at" type="TIMESTAMP WITH TIME ZONE"
                    defaultValueComputed="now()">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <!-- auto-rollback: Liquibase infers DROP TABLE orders -->
    </changeSet>

    <changeSet id="2" author="data-team">
        <addColumn tableName="orders">
            <column name="status" type="TEXT" defaultValue="pending">
                <constraints nullable="false"/>
            </column>
        </addColumn>
        <createIndex indexName="idx_orders_status" tableName="orders">
            <column name="status"/>
        </createIndex>
        <!-- auto-rollback: dropIndex + dropColumn inferred -->
    </changeSet>
</databaseChangeLog>

# Apply the whole changelog
liquibase --changeLogFile=db.changelog-master.xml \
          --url=jdbc:postgresql://db-primary.internal:5432/production \
          --username=migration_runner --password=$LB_PASSWORD update

# Dry-run: print the SQL Liquibase WOULD run, without executing
liquibase --changeLogFile=db.changelog-master.xml ... updateSQL

Step-by-step explanation.

1. The master changelog is a table of contents: it <include>s feature files in the order they should apply. New features append a new <include>; you never reorder existing ones. This keeps the changelog readable as the project grows.
2. Changeset id="1" uses the abstract <createTable> change type. Because it is abstract, Liquibase generates Postgres DDL here (BIGSERIAL/BIGINT ... GENERATED), but the same changeset would generate BIGINT AUTO_INCREMENT on MySQL. The autoIncrement="true" attribute is translated per dialect.
3. Changeset id="2" combines <addColumn> and <createIndex>. Both are reversible change types, so Liquibase can auto-generate the rollback (dropIndex + dropColumn) with no explicit <rollback> block — this is the payoff of describing changes abstractly.
4. Each changeset is identified by id + author + filename. When update runs, Liquibase computes each changeset's checksum, checks databasechangelog for a matching row, and applies only the ones not yet recorded. Applied changesets are immutable — editing changeset 1 later changes its checksum and Liquibase halts.
5. liquibase updateSQL is the dry-run verb: it prints the exact SQL Liquibase would execute without touching the database. This is the review artifact for a change-averse DBA who wants to see the generated DDL before it runs.

Output.

id	author	filename	exectype	dialect-generated DDL
1	data-team	001-orders.xml	EXECUTED	CREATE TABLE orders (...)
2	data-team	001-orders.xml	EXECUTED	ALTER TABLE ... ADD status; CREATE INDEX ...

Rule of thumb. Use a master changelog that <include>s ordered feature files, one logical change per changeset, and prefer abstract change types so rollback is auto-generated and the changelog stays database-agnostic. Drop to raw <sql> only when the abstraction genuinely doesn't cover your change.

Worked example — rollback and preconditions in YAML

Detailed explanation. For an irreversible change (a data backfill, a raw SQL statement) Liquibase cannot infer the rollback, so you write it. And to make a changeset safe against a drifted schema, you guard it with preconditions. Build a YAML changeset that backfills status, with an explicit rollback and a precondition that the column exists.

• Precondition. columnExists on orders.status; onFail=MARK_RAN so a missing column skips cleanly.
• Change. Raw SQL backfill of status from a legacy column.
• Rollback. Explicit — reset the backfilled rows.

Question. Write the YAML changeset with a precondition and an explicit rollback.

Input.

Element	Value
Precondition	columnExists orders.status, onFail MARK_RAN
Change	raw SQL backfill
Rollback	explicit reset SQL
Format	YAML changelog

Code.

# changelog/002-backfill-status.yaml
databaseChangeLog:
  - changeSet:
      id: "3"
      author: data-team
      comment: Backfill orders.status from legacy is_shipped flag
      preConditions:
        - onFail: MARK_RAN          # if the column is missing, skip cleanly
        - columnExists:
            tableName: orders
            columnName: status
      changes:
        - sql:
            sql: >
              UPDATE public.orders
              SET    status = CASE WHEN is_shipped THEN 'shipped' ELSE 'pending' END
              WHERE  status = 'pending'
            stripComments: true
      rollback:
        - sql:
            sql: >
              UPDATE public.orders
              SET    status = 'pending'
              WHERE  status IN ('shipped', 'pending')

# Apply
liquibase --changeLogFile=db.changelog-master.xml update

# Roll back just this one changeset (uses the explicit rollback block)
liquibase --changeLogFile=db.changelog-master.xml rollbackCount 1

# Or roll back everything applied after a named tag
liquibase --changeLogFile=db.changelog-master.xml tag release-2026-08
# ... later ...
liquibase --changeLogFile=db.changelog-master.xml rollback release-2026-08

Step-by-step explanation.

1. The preConditions block runs before the changeset. columnExists checks that orders.status is present; onFail: MARK_RAN tells Liquibase that if the precondition fails (column missing), it should record the changeset as run without executing it — so a database that never got the status column skips the backfill cleanly instead of erroring.
2. The change is a raw <sql> backfill. Because it is arbitrary SQL, Liquibase cannot infer how to reverse it — there is no automatic rollback for a data mutation. This is exactly when the explicit rollback block is mandatory.
3. The explicit rollback block defines the reverse operation. Here it resets the affected rows. Rollback for data migrations is often imperfect (you can't always reconstruct the pre-state), which is why senior engineers treat data backfills as roll-forward-preferred and only write a best-effort rollback.
4. rollbackCount 1 walks the ledger backward by one changeset, executing its rollback block. tag + rollback <tag> is the more common production pattern: tag a release, and if it goes wrong, roll everything back to that tag in one command.
5. Preconditions are the drift-safety mechanism: they let a single changelog apply safely across environments that may be in slightly different states, choosing HALT (stop), MARK_RAN (skip), CONTINUE (proceed anyway), or WARN per failure.

Output.

Environment state	Precondition result	Changeset action
status column present	pass	backfill runs
status column missing	fail → MARK_RAN	recorded as run, not executed
Rollback requested	—	explicit reset SQL runs
Rollback to tag	—	all post-tag changesets reversed

Rule of thumb. Any raw-SQL or data-migration changeset needs an explicit <rollback> block (Liquibase cannot infer one), and any changeset that might meet a drifted schema needs preConditions with a deliberate onFail policy. Prefer tag + rollback <tag> over counting changesets for production reversals.

Worked example — database-agnostic changeset with dbms and contexts

Detailed explanation. The headline Liquibase capability: run one changelog against multiple database engines. Most changesets are dialect-agnostic automatically; the exceptions get a dbms attribute so a Postgres-specific changeset and a MySQL-specific one coexist. Add contexts to gate environment-specific seed data. Build a changelog that targets both Postgres and MySQL and seeds dev-only data.

• Agnostic change. <createTable> — one changeset, both engines.
• Engine-specific. A dbms="postgresql" changeset for a Postgres-only JSONB column and a dbms="mysql" twin for JSON.
• Context. A context="dev" seed-data changeset that only runs in dev.

Question. Write the changelog with an agnostic changeset, engine-specific twins, and a dev-only seed changeset.

Input.

Changeset	Scope	Purpose
id=4	all engines	createTable events
id=5a	postgresql	add JSONB payload
id=5b	mysql	add JSON payload
id=6	context=dev	seed test rows

Code.

# changelog/003-events.yaml
databaseChangeLog:
  # 4 — dialect-agnostic: Liquibase emits correct DDL for each engine
  - changeSet:
      id: "4"
      author: data-team
      changes:
        - createTable:
            tableName: events
            columns:
              - column: {name: id, type: BIGINT, autoIncrement: true,
                         constraints: {primaryKey: true, nullable: false}}
              - column: {name: event_type, type: VARCHAR(64),
                         constraints: {nullable: false}}
              - column: {name: created_at, type: "TIMESTAMP WITH TIME ZONE",
                         defaultValueComputed: "now()"}

  # 5a — Postgres only: JSONB
  - changeSet:
      id: "5a"
      author: data-team
      dbms: postgresql
      changes:
        - addColumn:
            tableName: events
            columns:
              - column: {name: payload, type: JSONB}

  # 5b — MySQL only: JSON (no JSONB type)
  - changeSet:
      id: "5b"
      author: data-team
      dbms: mysql
      changes:
        - addColumn:
            tableName: events
            columns:
              - column: {name: payload, type: JSON}

  # 6 — dev-only seed data, gated by context
  - changeSet:
      id: "6"
      author: data-team
      context: dev
      changes:
        - insert:
            tableName: events
            columns:
              - column: {name: event_type, value: "SmokeTest"}

# Prod: apply structural changes, skip dev seed
liquibase --changeLogFile=db.changelog-master.xml update --contexts=prod

# Dev: apply everything including seed data
liquibase --changeLogFile=db.changelog-master.xml update --contexts=dev

Step-by-step explanation.

1. Changeset id="4" uses only abstract change types, so Liquibase generates the correct CREATE TABLE for whichever engine it connects to — BIGSERIAL semantics on Postgres, BIGINT AUTO_INCREMENT on MySQL, VARCHAR(64) on both. One changeset, every engine.
2. The payload column genuinely differs: Postgres has JSONB, MySQL only has JSON. So there are two changesets — 5a scoped dbms: postgresql and 5b scoped dbms: mysql. On a Postgres target, Liquibase runs 5a and skips 5b; on MySQL it does the reverse. Both are recorded in that engine's databasechangelog.
3. Changeset id="6" carries context: dev. It only runs when --contexts=dev is passed. This is how you keep environment-specific seed/test data in the same changelog without it leaking into prod.
4. The --contexts flag at run time selects which changesets apply. --contexts=prod runs the structural changesets (which have no context, so they always run) and skips the dev-only seed. --contexts=dev runs everything.
5. This is the portability + environment-gating story in one file: agnostic changesets for the common case, dbms-scoped twins for genuine dialect differences, and context/labels for environment-specific execution — all tracked per engine and per environment in the ledger.

Output.

Target + context	id=4	id=5a (pg)	id=5b (mysql)	id=6 (dev)
Postgres + prod	run	run	skip	skip
Postgres + dev	run	run	skip	run
MySQL + prod	run	skip	run	skip
MySQL + dev	run	skip	run	run

Rule of thumb. Write dialect-agnostic changesets by default, add dbms-scoped twins only where types genuinely differ (JSONB vs JSON), and use context/labels to gate environment-specific data. One changelog, every engine, every environment — that is the Liquibase differentiator.

Senior interview question on Liquibase

A senior interviewer might ask: "Your company ships the same product schema to Postgres (cloud) and Oracle (on-prem customers). Design a Liquibase setup — the master changelog structure, how you keep changesets dialect-agnostic, how you handle the columns that genuinely differ per engine, the rollback strategy for a bad release, and how CI dry-runs the generated SQL before it touches a customer database."

Solution Using a master changelog + agnostic changesets + dbms twins + tagged rollback

<!-- db.changelog-master.xml — table of contents -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.25.xsd">
    <include file="changelog/001-core.yaml"    relativeToChangelogFile="true"/>
    <include file="changelog/002-events.yaml"  relativeToChangelogFile="true"/>
    <include file="changelog/003-release.yaml" relativeToChangelogFile="true"/>
</databaseChangeLog>

# changelog/003-release.yaml — tag the release, then a dbms-split change
databaseChangeLog:
  - changeSet:
      id: tag-2026-08
      author: release-bot
      changes:
        - tagDatabase:
            tag: release-2026-08     # rollback anchor for this release

  - changeSet:
      id: "10a"
      author: data-team
      dbms: postgresql
      changes:
        - addColumn:
            tableName: events
            columns: [{column: {name: attrs, type: JSONB}}]
      rollback:
        - dropColumn: {tableName: events, columnName: attrs}

  - changeSet:
      id: "10b"
      author: data-team
      dbms: oracle
      changes:
        - addColumn:
            tableName: events
            columns: [{column: {name: attrs, type: "CLOB"}}]
      rollback:
        - dropColumn: {tableName: events, columnName: attrs}

# CI gate — dry-run generated SQL for BOTH engines, then validate
name: liquibase-gate
on: [pull_request]
jobs:
  dry-run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: postgres updateSQL (dry run, no execution)
        run: |
          liquibase --changeLogFile=db.changelog-master.xml \
            --url=jdbc:postgresql://pg:5432/app --username=ci --password=ci \
            updateSQL > pg_plan.sql
      - name: validate changelog
        run: liquibase --changeLogFile=db.changelog-master.xml validate

Step-by-step trace.

Concern	Answer	Reasoning
Structure	master changelog <include>s feature files	ordered, readable, append-only
Portability	abstract change types	one changeset → correct DDL per engine
Genuine differences	dbms-scoped twins (10a/10b)	JSONB (pg) vs CLOB (oracle)
Rollback anchor	tagDatabase per release	rollback release-2026-08 reverses cleanly
CI safety	updateSQL dry-run + validate	review generated DDL before a customer DB runs it

After rollout, one changelog drives both Postgres and Oracle: agnostic changesets emit dialect-correct DDL, and the two dbms-scoped twins cover the one column that genuinely differs (JSONB vs CLOB). Every release begins with a tagDatabase anchor, so a bad deploy is reversed with rollback release-2026-08. CI runs updateSQL to print the exact SQL for review and validate to confirm the changelog is well-formed — no customer database is touched until the generated DDL has been seen.

Output:

Metric	Value
Engines from one changelog	Postgres + Oracle
Dialect handling	abstract types + dbms twins
Rollback mechanism	tagDatabase + rollback <tag>
CI review artifact	updateSQL generated-SQL plan
Changelog validation	liquibase validate

Why this works — concept by concept:

• Master changelog + includes — the ordered table of contents keeps the change history append-only and readable; new features append an <include> and never reorder existing ones.
• Abstract change types — describing changes as addColumn/createTable lets Liquibase generate correct DDL for every target engine, which is the entire reason a multi-database shop picks Liquibase over Flyway.
• dbms-scoped twins — where a type genuinely differs (JSONB vs CLOB), two changesets scoped by dbms run selectively, so one changelog still serves both engines without lowest-common-denominator SQL.
• tagDatabase + rollback — tagging each release creates a named anchor; rollback release-2026-08 reverses every changeset after the tag using each changeset's (auto or explicit) rollback block — a clean, one-command production reversal.
• Cost — an XML/YAML changelog is more verbose than raw SQL, and updateSQL review is an extra CI step. In exchange you get true database portability, first-class rollback, and preconditions for drift safety. For a single-engine Postgres team this ceremony is overkill; for a multi-engine enterprise it is exactly the abstraction that keeps one schema honest across engines.

SQL
Topic — sql
SQL constraint and cross-dialect problems

Practice →

Design Topic — design Design problems on multi-database schema

Practice →

---

4. Alembic — SQLAlchemy autogenerate and the revision graph

alembic is the SQLAlchemy-native tool — each revision is a Python upgrade/downgrade pair, chained by a down_revision pointer into a graph, with autogenerate diffing your models against the live database

The mental model in one line: alembic is the Python migration tool built by the SQLAlchemy author where each migration is a revision file with an upgrade() and a downgrade() function calling the op.* schema-operations API, revisions are linked by a down_revision pointer into a directed graph (not just a linear sequence), the current position is stored as a single revision id in alembic_version, and alembic revision --autogenerate compares your SQLAlchemy model metadata against the live schema to write most of the migration for you. Every Python team whose models are already SQLAlchemy classes reaches for Alembic, because the migration and the ORM share one type system.

The four axes for Alembic.

• Authoring. Python revision files. Each has revision, down_revision, an upgrade(), and a downgrade(). Inside, you call op.create_table, op.add_column, op.create_index, op.execute (raw SQL), etc. Types come from SQLAlchemy (sa.Integer, sa.Text).
• Rollback. First-class and symmetric by design. Every revision ships a downgrade(). alembic downgrade -1 steps back one revision; alembic downgrade <rev> goes to a specific point; alembic downgrade base empties the schema.
• Portability. Alembic emits dialect-specific DDL through SQLAlchemy's dialect layer, but autogenerate and some operations behave differently per backend (SQLite's limited ALTER TABLE needs batch mode). Portable, with per-backend caveats.
• CI/CD. A Python entry point (alembic upgrade head) that drops naturally into Python stacks — FastAPI/Django-adjacent apps, Airflow DAGs, container entrypoints. alembic check (2.x) detects models-vs-migrations drift in CI.

The revision graph — not just a line.

• The pointer. Each revision names its parent via down_revision. The head is the revision no one points to as a parent. alembic upgrade head walks from the current alembic_version to the head.
• Branches. Two developers can create revisions with the same down_revision, producing two heads (a branch). Alembic detects multiple heads and refuses to upgrade head ambiguously until you merge.
• Merges. alembic merge creates a merge revision with two down_revision parents, reuniting the branches into a single head. This is the graph structure that a linear tool like Flyway does not have.
• The alembic_version table. Stores the current head revision id(s). Unlike Flyway/Liquibase (which store a row per applied migration), Alembic stores only the current position — the file chain encodes the history.

Autogenerate — diff models against the database.

• What it does. alembic revision --autogenerate -m "add status" imports your SQLAlchemy MetaData (the models), reflects the live database, diffs them, and writes a revision containing the op.* calls to reconcile the difference.
• What it catches. New/dropped tables, added/removed columns, changed nullability, new indexes and unique constraints, foreign keys (with configuration).
• What it misses. Column renames (it sees a drop + add), some type changes, check-constraint edits, and anything server-side (triggers, functions). Always review autogenerated migrations — they are a first draft, not gospel.
• The env.py wiring. target_metadata = Base.metadata in env.py is what connects autogenerate to your models. Without it, autogenerate has nothing to diff against.

Up/down grade and data migrations.

• Schema ops. op.create_table, op.add_column, op.alter_column, op.drop_column, op.create_index, op.create_foreign_key.
• Data ops. op.execute("UPDATE ...") or op.bulk_insert(table, rows) for backfills inside a migration. Keep schema and data migrations in separate revisions when possible.
• Batch mode. with op.batch_alter_table("t") as batch_op: — required on SQLite (which can't ALTER columns) and useful for grouping table ops; Alembic rebuilds the table under the hood.

Common interview probes on Alembic.

• "What does --autogenerate catch and miss?" — catches structural adds/drops; misses renames, some type changes, server-side objects. Always review.
• "How does Alembic handle two developers branching?" — two heads; alembic merge creates a merge revision with two parents.
• "What's in alembic_version?" — the current head revision id only; the file chain encodes history.
• "How do you write a data backfill?" — op.execute / op.bulk_insert in a separate revision from the schema change.

Worked example — alembic init, first revision, upgrade/downgrade

Detailed explanation. The canonical Alembic bootstrap: alembic init, wire env.py to your models, then write the first revision by hand with a symmetric upgrade/downgrade. Build the orders table as revision 1.

• Init. alembic init migrations scaffolds alembic.ini, env.py, versions/.
• Wire. target_metadata = Base.metadata in env.py.
• Revision. A hand-written upgrade()/downgrade() for the orders table.

Question. Scaffold Alembic, wire the models, and write the first revision with a reversible upgrade/downgrade.

Input.

Step	Command / file
scaffold	alembic init migrations
wire	env.py: target_metadata = Base.metadata
revise	alembic revision -m "create orders"
apply	alembic upgrade head

Code.

# models.py — the SQLAlchemy models autogenerate will diff against
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import BigInteger, Text, TIMESTAMP, func

class Base(DeclarativeBase):
    pass

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    customer_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    total_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    created_at: Mapped[str] = mapped_column(
        TIMESTAMP(timezone=True), server_default=func.now(), nullable=False)

# migrations/env.py (excerpt) — connect autogenerate to the models
from models import Base
target_metadata = Base.metadata          # <-- the line that enables autogenerate

# migrations/versions/0001_create_orders.py
"""create orders

Revision ID: 0001
Revises:
Create Date: 2026-08-03 00:00:00
"""
import sqlalchemy as sa
from alembic import op

revision = "0001"
down_revision = None        # this is the first revision (root of the graph)
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "orders",
        sa.Column("id", sa.BigInteger(), primary_key=True),
        sa.Column("customer_id", sa.BigInteger(), nullable=False),
        sa.Column("total_cents", sa.BigInteger(), nullable=False),
        sa.Column("created_at", sa.TIMESTAMP(timezone=True),
                  server_default=sa.func.now(), nullable=False),
    )


def downgrade() -> None:
    op.drop_table("orders")

alembic upgrade head       # apply → alembic_version now = 0001
alembic downgrade base     # reverse → alembic_version empty again
alembic current            # show the current revision

Step-by-step explanation.

1. alembic init migrations scaffolds the project: alembic.ini (the config with the DB URL), env.py (the runtime that connects and runs migrations), and versions/ (where revision files live).
2. The single most important wiring step is target_metadata = Base.metadata in env.py. This hands Alembic your SQLAlchemy models so --autogenerate has something to diff the live database against. Forgetting this line is the number-one Alembic setup mistake.
3. The revision file carries revision = "0001" and down_revision = None. A None down_revision means this is the root of the graph — the first migration. Later revisions point their down_revision at "0001", forming the chain.
4. upgrade() calls op.create_table with sa.Column definitions that reuse the exact SQLAlchemy types the models use. downgrade() is the exact inverse — op.drop_table. Because you write both, every revision is reversible by construction.
5. alembic upgrade head walks from the current position (nothing) to the head (0001) and runs its upgrade(), then stores 0001 in alembic_version. alembic downgrade base runs downgrade() back to an empty schema. alembic_version holds only the current revision id, not the full history — the file chain is the history.

Output.

Command	alembic_version after	Schema state
(fresh)	(empty)	no tables
upgrade head	0001	orders exists
downgrade base	(empty)	orders dropped
upgrade head	0001	orders re-created

Rule of thumb. Wire target_metadata = Base.metadata in env.py first, write every revision with a symmetric upgrade()/downgrade(), and remember that alembic_version stores only the current head — the revision files' down_revision chain is the real history.

Worked example — autogenerate from SQLAlchemy models

Detailed explanation. Alembic's signature feature: change a model, run --autogenerate, and Alembic writes the migration by diffing models against the live DB. Add a status column and an index to the Order model, autogenerate the revision, and see what it catches (and what to fix by hand).

• Model change. Add status column + a mapped index to Order.
• Autogenerate. alembic revision --autogenerate -m "add order status".
• Review. Confirm the generated op.* calls; add the server_default autogenerate omits.

Question. Change the model, autogenerate the migration, and identify what needs manual review.

Input.

Model change	Autogenerate detects?
add status column	yes (add_column)
add index on status	yes (create_index)
server_default='pending'	partial — review needed
a column rename	no (sees drop + add)

Code.

# models.py — add status to the Order model
class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    customer_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    total_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    status: Mapped[str] = mapped_column(
        Text, nullable=False, server_default="pending", index=True)   # NEW
    created_at: Mapped[str] = mapped_column(
        TIMESTAMP(timezone=True), server_default=func.now(), nullable=False)

# Diff models vs live DB and write the migration
alembic revision --autogenerate -m "add order status"

# migrations/versions/0002_add_order_status.py  (autogenerated, then reviewed)
import sqlalchemy as sa
from alembic import op

revision = "0002"
down_revision = "0001"        # chained onto the first revision
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "orders",
        sa.Column("status", sa.Text(), nullable=False,
                  server_default="pending"),      # server_default confirmed by review
    )
    op.create_index("ix_orders_status", "orders", ["status"])


def downgrade() -> None:
    op.drop_index("ix_orders_status", table_name="orders")
    op.drop_column("orders", "status")

Step-by-step explanation.

1. The only change is to models.py — add a status column with index=True and a server_default. Autogenerate works by importing this model metadata and reflecting the current database, then diffing.
2. alembic revision --autogenerate -m "add order status" produces 0002, automatically setting down_revision = "0001" so the new revision chains onto the previous head. Alembic detects the added column and the new index and writes the op.add_column + op.create_index calls.
3. Autogenerate is reliable for structural adds and drops — new columns, new indexes, new tables, nullability changes. It writes both upgrade() and a matching downgrade().
4. It needs review for two things here: the server_default (autogenerate detects defaults but the rendered value should be confirmed, especially for functions), and — critically — it cannot detect renames. If you had renamed total_cents to amount_cents, autogenerate would emit a drop_column + add_column (data loss!) rather than a rename. You must hand-edit those to op.alter_column(..., new_column_name=...).
5. The rule is: autogenerate is a first draft. Run it, read the diff, fix the renames and server-side objects it can't see, then commit. Treating autogenerate output as final is how teams ship accidental DROP COLUMNs.

Output.

Change	Autogenerate emitted	Manual fix needed
add status column	op.add_column(...)	confirm server_default
add index	op.create_index(...)	none
rename column	drop_column + add_column	rewrite as op.alter_column(new_column_name=)
new trigger/function	(nothing)	add op.execute("CREATE ...")

Rule of thumb. Autogenerate is a first draft, never a final migration. It nails structural adds/drops but turns renames into data-losing drop+add and ignores triggers, functions, and check constraints. Run it, review the diff, fix renames and server-side objects by hand, then commit.

Worked example — branching and merging revisions

Detailed explanation. Two developers on separate feature branches each create a revision whose down_revision is the current head. When both merge to main, Alembic sees two heads — a branch in the revision graph — and refuses to upgrade head ambiguously. alembic merge reunites them. Walk through the branch and the merge.

• The branch. Dev A writes 0003a (down_revision 0002); Dev B writes 0003b (down_revision 0002). Two heads.
• The detection. alembic heads shows both; alembic upgrade head errors on ambiguity.
• The merge. alembic merge -m "merge a and b" 0003a 0003b creates 0004 with two parents.

Question. Reproduce the two-head branch and write the merge revision.

Input.

Revision	down_revision	Head?
0002	0001	no
0003a	0002	yes (branch A)
0003b	0002	yes (branch B)
0004 (merge)	(0003a, 0003b)	yes (single head)

Code.

# Both devs branched from 0002; after merging code, two heads exist:
alembic heads
#  0003a (head)
#  0003b (head)

# upgrade head is now ambiguous and errors:
alembic upgrade head
#  ERROR: Multiple head revisions are present; please specify ...

# Create a merge revision uniting both branches into one head:
alembic merge -m "merge feature branches a and b" 0003a 0003b

# migrations/versions/0004_merge_a_b.py  (generated by `alembic merge`)
"""merge feature branches a and b

Revision ID: 0004
Revises: 0003a, 0003b
Create Date: 2026-08-03 00:00:00
"""
revision = "0004"
down_revision = ("0003a", "0003b")   # TWO parents — this is the merge node
branch_labels = None
depends_on = None


def upgrade() -> None:
    pass          # merge revisions usually carry no schema ops


def downgrade() -> None:
    pass

# Now a single head exists again; upgrade works:
alembic upgrade head        # applies 0003a, 0003b, then 0004
alembic current             # → 0004 (head)

Step-by-step explanation.

1. Both developers based their revisions on the same parent (0002), so after both feature branches merge into main, the revision graph has two leaves — 0003a and 0003b. Neither points to the other, so both are heads.
2. alembic heads reports both. alembic upgrade head cannot proceed because "head" is ambiguous — Alembic will not guess which branch to apply first, and refuses rather than risk a wrong order.
3. alembic merge -m "..." 0003a 0003b generates a merge revision (0004) whose down_revision is a tuple of both branch heads. This is the graph feature that distinguishes Alembic from linear tools: a single node with two parents.
4. The merge revision usually has empty upgrade()/downgrade() bodies — it exists only to reunite the graph. If the two branches touched the same object and need reconciliation, you put that reconciliation SQL in the merge revision's upgrade().
5. After the merge, there is a single head (0004). alembic upgrade head now applies 0003a, 0003b, and 0004 in a valid topological order, and alembic_version records 0004. The branch is resolved; history is linearizable again.

Output.

State	alembic heads	upgrade head
after both branches merge	0003a, 0003b	ERROR (ambiguous)
after alembic merge	0004	applies 0003a, 0003b, 0004
final	0004	idempotent no-op

Rule of thumb. When alembic upgrade head errors with "multiple head revisions," you have a branch — run alembic heads to see them and alembic merge to create a two-parent merge revision. The merge node usually carries no schema ops; it just reunites the graph so head is unambiguous again.

Senior interview question on Alembic

A senior interviewer might ask: "You run a FastAPI service on SQLAlchemy. Design the Alembic workflow — how autogenerate fits your model-first development, what you review before committing an autogenerated migration, how you write a data backfill safely, how you handle two developers branching, and how CI blocks a PR whose models and migrations have drifted apart."

Solution Using autogenerate + reviewed revisions + a data migration + alembic check in CI

# 1. A reviewed autogenerated schema revision
# versions/0005_add_customer_tier.py
import sqlalchemy as sa
from alembic import op

revision = "0005"
down_revision = "0004"

def upgrade() -> None:
    op.add_column("customers",
        sa.Column("tier", sa.Text(), nullable=False, server_default="standard"))

def downgrade() -> None:
    op.drop_column("customers", "tier")

# 2. A SEPARATE data-migration revision (backfill), kept apart from schema DDL
# versions/0006_backfill_customer_tier.py
from alembic import op
import sqlalchemy as sa

revision = "0006"
down_revision = "0005"

def upgrade() -> None:
    # Backfill tier from historical spend; op.execute keeps it in the same txn
    op.execute("""
        UPDATE customers
        SET    tier = CASE
                        WHEN lifetime_cents >= 1000000 THEN 'gold'
                        WHEN lifetime_cents >=  100000 THEN 'silver'
                        ELSE 'standard'
                      END
    """)

def downgrade() -> None:
    op.execute("UPDATE customers SET tier = 'standard'")

# 3. CI gate — block drift between models and migrations, then apply on scratch
name: alembic-gate
on: [pull_request]
jobs:
  check:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_PASSWORD: ci }
        ports: ["5432:5432"]
    steps:
      - uses: actions/checkout@v4
      - run: pip install -r requirements.txt
      - name: apply all migrations to scratch db
        run: alembic upgrade head
        env: { DATABASE_URL: postgresql://postgres:ci@localhost:5432/postgres }
      - name: fail if models drifted from migrations
        run: alembic check          # errors if autogenerate would produce changes
        env: { DATABASE_URL: postgresql://postgres:ci@localhost:5432/postgres }

Step-by-step trace.

Concern	Answer	Reasoning
Model-first dev	edit model → revision --autogenerate	migration is a diff of models vs DB
Review before commit	read diff; fix renames/defaults	autogenerate is a first draft
Data backfill	separate op.execute revision	keep schema and data migrations apart
Branch handling	alembic merge two heads	graph reunited to one head
CI drift gate	alembic upgrade head + alembic check	red build if models ≠ migrations

After rollout, developers change SQLAlchemy models, run alembic revision --autogenerate, and review the diff — fixing any rename that autogenerate rendered as drop+add and confirming server defaults. Schema changes (0005) and data backfills (0006) live in separate revisions so a schema rollback doesn't entangle a data reversal. Branches are merged with alembic merge. CI applies every migration onto a throwaway Postgres and then runs alembic check, which fails the PR if the models and the migration chain have drifted apart — the migration and the ORM can never silently diverge.

Output:

Metric	Value
Authoring	model-first + autogenerate draft
Review focus	renames, defaults, server-side objects
Data migration	separate op.execute revision
Drift gate	alembic check in CI
Rollback	symmetric downgrade() per revision

Why this works — concept by concept:

• Autogenerate + review — diffing SQLAlchemy metadata against the live DB writes the structural migration for you, but the mandatory review step catches the renames and server-side objects autogenerate cannot see, turning a fast draft into a correct migration.
• Separate data migration — keeping the status-column DDL (0005) apart from the backfill (0006) means a schema rollback and a data rollback are independent; entangling them makes both reversals fragile.
• op.execute in-transaction — running the backfill through op.execute keeps it inside the migration's transaction, so a failure mid-backfill rolls back cleanly and alembic_version is not advanced.
• alembic check — the CI drift gate: it runs autogenerate in dry-run mode and fails if it would produce any operations, which means the committed migrations exactly match the models. This is the guarantee that the ORM and the schema history stay in lockstep.
• Cost — Alembic requires a Python/SQLAlchemy stack and the discipline to review autogenerated output, and its per-backend caveats (SQLite batch mode) add edge cases. In exchange you get model-first development where the migration is a reviewed diff of your ORM, symmetric downgrades, and a revision graph that handles real team branching — the reason SQLAlchemy shops pick it over Flyway or Liquibase.

ETL
Topic — etl
ETL problems on data backfills and migrations

Practice →

Database Topic — database Database revision-graph and rollback problems

Practice →

---

5. Decision matrix — pick the tool, wire the CI gate

SQL-first (Flyway) vs abstracted (Liquibase) vs Python-native (Alembic) — the matrix, the CI/CD gate, and the interview signals

The mental model in one line: the tool choice reduces to three archetypes — Flyway is SQL-first (raw SQL files, forward-only, least ceremony), Liquibase is abstracted (dialect-agnostic changesets, first-class rollback, multi-database), and Alembic is Python-native (autogenerate from SQLAlchemy, revision graph, symmetric downgrades) — and the right pick falls out of your team's language, your database count, and your rollback philosophy, while every tool shares the same non-negotiable CI/CD gate: apply migrations to a throwaway database on every PR, block the merge if they fail, and run migrations as an explicit deploy step, never by hand. Getting the tool wrong is recoverable; getting the CI gate wrong ships drift.

The three archetypes side by side.

• SQL-first (Flyway). You write the SQL you would run by hand, versioned in a filename. Forward-only in open source. Lowest ceremony; best when the team lives in SQL, targets one engine, and values roll-forward discipline.
• Abstracted (Liquibase). You describe changes so one changelog runs on many engines, with rollback and preconditions built in. Highest ceremony; best for multi-database enterprises and compliance-grade auditable change logs.
• Python-native (Alembic). You write Python upgrade/downgrade and let autogenerate diff your SQLAlchemy models. Model-first; best for Python services whose schema is their ORM.

The CI/CD gate — identical across all three tools.

• On pull request. Spin up a throwaway database, apply all migrations from scratch, and fail the build on any error. Optionally run the tool's validate/check verb (checksum/drift). This proves the migrations are internally consistent and applyable.
• On deploy. Run migrations as an explicit, ordered step before the new application code starts — flyway migrate / liquibase update / alembic upgrade head. The migration runner is the only role with DDL rights.
• Expand/contract for zero-downtime. For breaking changes, split into two deploys: expand (add the new nullable column, dual-write) → deploy code that reads both → contract (backfill, make not-null, drop old). Never rename-in-place under load.
• Never run migrations by hand on prod, and never let application boot auto-migrate in a multi-replica deploy (two replicas racing the same migration is a classic incident).

CI/CD integration by tool.

• Flyway. flyway/flyway Docker image; migrate, validate, info, repair. Drops into any pipeline as one container step.
• Liquibase. liquibase/liquibase Docker image; update, updateSQL (dry-run the generated SQL for review), validate, status, rollback, tag.
• Alembic. Python entry point; alembic upgrade head, alembic check (drift), alembic downgrade. Natural fit inside a Python container entrypoint or an Airflow task.

Interview signals for tool selection.

• Name the three archetypes (SQL-first / abstracted / Python-native), not just the three product names — senior signal.
• Tie the pick to team language + database count + rollback philosophy, not "which is most popular" — required answer.
• Insist on the CI gate (apply-to-scratch on PR, migrate-on-deploy) regardless of tool — senior signal.
• Describe expand/contract for zero-downtime schema changes — senior signal.
• Distinguish a reverse migration from a restore-from-backup, and say when you'd choose roll-forward over rollback — required answer.

Worked example — the three-tool decision matrix

Detailed explanation. Build the full comparison matrix an interviewer expects you to reproduce on a whiteboard. Score Flyway, Liquibase, and Alembic across the axes that actually drive the decision, then map three team profiles onto the matrix.

• Axes. Authoring, rollback, portability, autogenerate, CI verb, best-fit team.
• Profiles. A Postgres-only data team, a multi-engine enterprise, a Python service team.

Question. Produce the decision matrix and the profile-to-tool mapping.

Input.

Axis	Flyway	Liquibase	Alembic
Authoring	raw SQL	XML/YAML/SQL changesets	Python op.*
Rollback	forward-only (OSS)	auto + explicit	downgrade() per revision
Portability	your-SQL-dependent	dialect-agnostic	dialect via SQLAlchemy
Autogenerate	no	limited	yes (from models)
CI verb	migrate / validate	update / updateSQL	upgrade / check

Code.

# A scoring helper that encodes the matrix as weights per team profile
WEIGHTS = {                      # how much each profile values each axis
    "postgres_data_team": {"sql_first": 3, "portability": 0, "autogen": 0},
    "multi_db_enterprise": {"sql_first": 0, "portability": 3, "autogen": 0},
    "python_service":     {"sql_first": 0, "portability": 0, "autogen": 3},
}
TOOL_AXIS = {
    "flyway":    {"sql_first": 1, "portability": 0, "autogen": 0},
    "liquibase": {"sql_first": 0, "portability": 1, "autogen": 0},
    "alembic":   {"sql_first": 0, "portability": 0, "autogen": 1},
}

def recommend(profile: str) -> str:
    w = WEIGHTS[profile]
    scores = {
        tool: sum(w[a] * axis[a] for a in w)
        for tool, axis in TOOL_AXIS.items()
    }
    return max(scores, key=scores.get)

print(recommend("postgres_data_team"))   # → flyway
print(recommend("multi_db_enterprise"))  # → liquibase
print(recommend("python_service"))       # → alembic

Step-by-step explanation.

1. The matrix rows are the axes that actually differentiate the tools: authoring model, rollback story, portability, autogenerate, and CI verb. Popularity and star count are deliberately excluded — they don't predict fit.
2. The scoring helper encodes each team profile as a weight vector over the axes: a Postgres data team weights sql_first heavily and portability/autogen at zero; a multi-engine enterprise weights portability; a Python service weights autogen.
3. Each tool is scored on which axis it wins — Flyway on SQL-first, Liquibase on portability, Alembic on autogenerate. Multiplying weights by tool strengths and taking the max yields the recommendation.
4. The three profiles map cleanly: Postgres data team → Flyway, multi-DB enterprise → Liquibase, Python service → Alembic. This mirrors the decision tree from section 1 but expressed as a weighted score rather than a branch.
5. The point of the exercise is not the toy code — it's demonstrating that the pick is a function of the team's constraints, and being able to name which axis is decisive for which profile. That framing is what an interviewer scores as senior.

Output.

Team profile	Recommended tool	Decisive axis
Postgres-only data team	Flyway	SQL-first authoring
Multi-engine enterprise	Liquibase	database portability
Python service on SQLAlchemy	Alembic	autogenerate from models
Compliance-heavy multi-DB	Liquibase	auditable changelog + contexts
Rapid reversible experiments	Alembic	symmetric downgrades

Rule of thumb. Reproduce the matrix from memory — authoring, rollback, portability, autogenerate, CI verb — and map the team's constraints onto it. The pick is a weighted function of language, database count, and rollback philosophy, never a popularity contest.

Worked example — the CI/CD migration gate

Detailed explanation. The gate is identical across tools: apply migrations to a throwaway database on every PR, then run them as an explicit deploy step. Wire it once, tool-agnostically, and show where each tool's command slots in. Build the pipeline for a Postgres service.

• PR job. Fresh Postgres container, apply all migrations from scratch, run the validate/check verb.
• Deploy job. Run migrations before the new app version starts; migration runner is the only DDL role.
• Guardrail. Fail closed — a broken or drifted migration blocks the merge/deploy.

Question. Write the tool-agnostic CI/CD gate and show the per-tool command.

Input.

Stage	Action	Fail condition
PR	apply all migrations to scratch DB	any migration error
PR	validate / check	checksum or model drift
deploy	migrate before app starts	migration error aborts deploy

Code.

# .github/workflows/db-gate.yml — the gate, tool-agnostic scaffold
name: db-gate
on:
  pull_request:
  push:
    branches: [main]
jobs:
  migrate-and-validate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_PASSWORD: ci }
        ports: ["5432:5432"]
        options: >-
          --health-cmd="pg_isready -U postgres" --health-interval=5s
          --health-timeout=5s --health-retries=5
    env:
      DB_URL: postgresql://postgres:ci@localhost:5432/postgres
    steps:
      - uses: actions/checkout@v4

      # --- pick ONE block per your tool ---

      # Flyway
      - name: flyway
        run: |
          docker run --rm --network host -v $PWD/sql:/flyway/sql flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/postgres -user=postgres -password=ci \
            -locations=filesystem:/flyway/sql migrate
          docker run --rm --network host -v $PWD/sql:/flyway/sql flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/postgres -user=postgres -password=ci \
            -locations=filesystem:/flyway/sql validate

      # Liquibase (alternative)
      # - run: liquibase --changeLogFile=db.changelog-master.xml --url=$DB_URL update
      # - run: liquibase --changeLogFile=db.changelog-master.xml --url=$DB_URL validate

      # Alembic (alternative)
      # - run: pip install -r requirements.txt && alembic upgrade head
      # - run: alembic check

# Deploy step (runs BEFORE the new app version starts, in the release job)
#   Flyway:    flyway -configFiles=flyway.conf migrate
#   Liquibase: liquibase --changeLogFile=db.changelog-master.xml update
#   Alembic:   alembic upgrade head
# The migration_runner role is the ONLY role granted DDL.

Step-by-step explanation.

1. The PR job spins up a throwaway Postgres 16 as a service container with a health check, then applies every migration from an empty schema. Applying from scratch — not from a snapshot — proves the entire migration chain is internally consistent and would build a fresh database correctly.
2. After applying, the job runs the tool's integrity verb: flyway validate (checksum drift), liquibase validate (well-formed changelog), or alembic check (models-vs-migrations drift). A failure here fails the PR.
3. The three tool blocks are interchangeable — the gate shape is identical, only the command differs. This is the key teaching point: the CI/CD gate is a tool-agnostic discipline, and choosing Flyway vs Liquibase vs Alembic changes one command, not the pipeline.
4. The deploy step runs migrations as an explicit, ordered action before the new application code starts. This ordering matters: code that expects the new column must not start until the migration that adds it has committed.
5. The migration_runner role is the only role with DDL grants, closing the drift loop from section 1: even in production, the only path to a schema change is the pipeline running the tool — never a human at a prompt.

Output.

Stage	Tool command	Effect
PR (Flyway)	migrate + validate	scratch build + checksum check
PR (Liquibase)	update + validate	scratch build + changelog check
PR (Alembic)	upgrade head + check	scratch build + model-drift check
Deploy	migrate / update / upgrade head	apply before app starts

Rule of thumb. The CI/CD gate is tool-agnostic: apply all migrations to a throwaway database on every PR, run the validate/check verb, and fail closed. On deploy, migrate as an explicit step before the app starts, with DDL granted only to the migration runner. Switching tools changes one command, not the discipline.

Worked example — expand/contract for a zero-downtime rename

Detailed explanation. The single most dangerous migration is a rename under load — a direct ALTER TABLE ... RENAME COLUMN breaks every running app instance that still references the old name. The senior pattern is expand/contract: add the new, dual-write, migrate reads, backfill, then drop the old — spread across multiple deploys. Walk through renaming total_cents to amount_cents with zero downtime.

• Expand. Add amount_cents (nullable); deploy code that writes both columns.
• Migrate reads + backfill. Copy total_cents → amount_cents; deploy code that reads amount_cents.
• Contract. Make amount_cents not-null; drop total_cents.

Question. Sequence the expand/contract migrations and the deploys between them.

Input.

Phase	Migration	Deploy after
expand	add nullable amount_cents	code dual-writes both
backfill	copy values	code reads amount_cents
contract	not-null + drop old	(old column gone)

Code.

-- Migration 1 (EXPAND): add the new column, nullable, no default lock
ALTER TABLE public.orders ADD COLUMN amount_cents BIGINT;   -- fast, non-blocking

-- >>> deploy app v2: writes BOTH total_cents AND amount_cents on every write <<<

-- Migration 2 (BACKFILL): copy existing data in batches (avoid one huge txn)
UPDATE public.orders SET amount_cents = total_cents
WHERE  amount_cents IS NULL;                                -- batch in prod

-- >>> deploy app v3: READS amount_cents; still writes both <<<

-- Migration 3 (CONTRACT): enforce + drop, once nothing reads the old column
ALTER TABLE public.orders ALTER COLUMN amount_cents SET NOT NULL;
ALTER TABLE public.orders DROP COLUMN total_cents;

-- >>> deploy app v4: writes only amount_cents <<<

# The app code across the three deploys (illustrative)
# v2 (dual write):
def save_total(cur, order_id, cents):
    cur.execute("UPDATE orders SET total_cents=%s, amount_cents=%s WHERE id=%s",
                (cents, cents, order_id))

# v3 (read new, still dual-write):
def read_total(cur, order_id):
    cur.execute("SELECT amount_cents FROM orders WHERE id=%s", (order_id,))
    return cur.fetchone()[0]

# v4 (single write, old column gone):
def save_total_final(cur, order_id, cents):
    cur.execute("UPDATE orders SET amount_cents=%s WHERE id=%s", (cents, order_id))

Step-by-step explanation.

1. A direct RENAME COLUMN is atomic in the database but catastrophic under load: the instant it commits, every still-running app instance referencing total_cents throws "column does not exist." Expand/contract avoids ever having a moment where the schema and any running code disagree.
2. Expand adds amount_cents as nullable — a fast, non-blocking ADD COLUMN (no default means no table rewrite on modern Postgres). Then app v2 deploys, writing both columns, so new data is consistent across both.
3. Backfill copies existing total_cents into amount_cents (batched in production to avoid a single giant transaction and long locks). After backfill, both columns hold identical data for every row.
4. Deploy app v3, which now reads amount_cents while still writing both. At this point nothing depends on total_cents for reads. The system is fully functional on the new column.
5. Contract makes amount_cents not-null (safe now that it's fully populated) and drops total_cents. App v4 deploys, writing only the new column. Every step overlapped a schema state with a compatible code state — zero downtime, and each migration was individually reversible up to the drop.

Output.

Phase	Schema	Running code compatible?
expand	both columns, new nullable	v1 (old) and v2 (dual) both work
backfill	both populated	v2/v3 both work
read-switch	both populated	v3 reads new
contract	new only, not-null	v4 writes new

Rule of thumb. Never rename or drop a column in-place under load. Split every breaking change into expand (add + dual-write) → migrate reads → contract (backfill + enforce + drop), one deploy between each phase, so the schema and every running code version are always compatible. This is the zero-downtime discipline all three tools support but none enforce.

Senior interview question on migration tool selection

A senior interviewer might ask: "You're the new data platform lead across three teams — a Postgres-only analytics team writing raw SQL, a Java product team shipping to both Postgres and Oracle, and a Python ML-platform team on SQLAlchemy. Standardize their schema-migration approach. Pick a tool per team, justify each, and design the one CI/CD gate and rollback policy that applies to all three."

Solution Using per-team tool selection + a shared CI gate + a roll-forward-first policy

Tool selection (per team, by archetype)
========================================
Analytics team (Postgres, raw SQL)   -> Flyway
   SQL-first authoring, one engine, roll-forward culture.
Product team (Postgres + Oracle, Java) -> Liquibase
   dialect-agnostic changesets; dbms twins for the genuinely different columns.
ML-platform team (Python, SQLAlchemy) -> Alembic
   autogenerate from models; symmetric downgrades; alembic check in CI.

# Shared CI/CD gate (same shape for all three; tool command varies)
name: schema-gate
on: [pull_request]
jobs:
  gate:
    runs-on: ubuntu-latest
    services:
      db: { image: postgres:16, env: { POSTGRES_PASSWORD: ci }, ports: ["5432:5432"] }
    steps:
      - uses: actions/checkout@v4
      - name: apply from scratch          # PROVE the chain builds a fresh DB
        run: ./scripts/migrate.sh apply    # wraps flyway/liquibase/alembic per repo
      - name: integrity check             # checksum / changelog / model drift
        run: ./scripts/migrate.sh check

# Rollback policy (shared): roll FORWARD by default; reverse only when safe.
#  1. A bad migration => write a corrective forward migration (preferred).
#  2. Reverse migration only for pure-schema, no-data-loss changes:
#       Flyway:    (paid undo) OR corrective forward migration
#       Liquibase: liquibase rollback <release-tag>
#       Alembic:   alembic downgrade <rev>
#  3. Data-destructive change gone wrong => restore from backup/PITR, NOT rollback.

Step-by-step trace.

Team	Tool	Decisive axis	Rollback path
Analytics (SQL)	Flyway	SQL-first	corrective forward migration
Product (PG+Oracle)	Liquibase	portability	rollback <tag>
ML-platform (Python)	Alembic	autogenerate	alembic downgrade
all three	shared gate	apply-from-scratch + check	fail closed on PR

After standardization, each team uses the tool that fits its archetype, but all three obey one CI/CD gate — apply every migration to a throwaway Postgres on each PR and run the integrity verb — and one rollback policy: roll forward by default, reverse only for safe pure-schema changes, and restore from backup for data-destructive mistakes. The migrate.sh wrapper hides the per-tool command so the pipeline definition is identical across repos.

Output:

Metric	Value
Tools in use	Flyway + Liquibase + Alembic (per team)
Shared gate	apply-from-scratch + integrity check
Default rollback	roll-forward corrective migration
Reverse migration	safe pure-schema changes only
Data-loss recovery	backup / PITR, not migration rollback

Why this works — concept by concept:

• Tool per archetype — each team gets the tool matched to its language, engine count, and rollback philosophy rather than a forced monoculture; the archetype (SQL-first / abstracted / Python-native) is the selection key.
• Shared CI gate — a single gate shape (apply-from-scratch + integrity check) enforced through a thin migrate.sh wrapper means every team gets the same drift protection regardless of tool; the pipeline is uniform, the command is local.
• Roll-forward-first policy — a corrective forward migration is auditable, reviewable, and safe; it is preferred over a reverse migration because it leaves a complete history rather than un-applying one.
• Reverse vs restore — a reverse migration only undoes a schema change with no data loss; a data-destructive mistake requires point-in-time restore, not a downgrade. Distinguishing the two is the senior signal that prevents "I'll just roll back" from becoming data loss.
• Cost — supporting three tools costs a shared wrapper script and three CI templates, but it lets each team stay fluent in its native workflow while the platform enforces one drift-prevention discipline. The alternative — forcing one tool on everyone — trades team velocity for a superficial uniformity that the CI gate already provides more cheaply.

Design
Topic — design
Design problems on migration and CI/CD gates

Practice →

Data Transformation
Topic — data-transformation
Data-transformation and backfill problems

Practice →

---

Cheat sheet — schema migration recipes

• Which tool when. Flyway when the team writes raw SQL, targets one engine, and wants roll-forward discipline (SQL-first). Liquibase when one schema must ship to multiple engines or needs an auditable, context-gated changelog (abstracted). Alembic when the app is Python on SQLAlchemy and you want autogenerate + symmetric downgrades (Python-native). All three solve drift; pick by language × database count × rollback philosophy.
• The immutability rule. Never edit an applied migration — add a new one. Every tool stores a checksum per applied script (flyway_schema_history, databasechangelog, alembic_version + file chain). Editing an applied migration changes its checksum and the next deploy aborts. This is the guardrail that makes history append-only.
• Baselining a legacy DB (Flyway). flyway baseline -baselineVersion=1 marks the current schema as version 1 without running scripts; ship a matching V1__baseline.sql (a pg_dump --schema-only) so fresh builds converge. Set baselineOnMigrate=true to auto-baseline the first migrate against a non-empty schema. Never reconstruct a legacy DB's full history — baseline the present, track the future.
• Flyway file types. Versioned V{n}__desc.sql (applied once, in order, 90% of migrations); repeatable R__desc.sql (re-applied on checksum change, after all versioned ones — use for views/functions with CREATE OR REPLACE); undo U{n}__desc.sql (paid tier only). Open-source Flyway is forward-only by design.
• Liquibase changeset identity. A changeset is id + author + changelog filename, plus an MD5SUM. Prefer abstract change types (addColumn, createTable) so rollback is auto-generated and the changelog is dialect-agnostic; drop to <sql> only when needed, and then write an explicit <rollback> because Liquibase cannot infer one for raw SQL.
• Liquibase portability + gating. Abstract changesets emit dialect-specific DDL per engine; add a dbms="postgresql"/dbms="mysql" twin only where types genuinely differ (JSONB vs JSON). Use context="dev|prod" to gate environment-specific seed data and labels for boolean-expression selective runs (--labels="reporting AND !experimental").
• Liquibase rollback. liquibase tag release-2026-08 before a release, then liquibase rollback release-2026-08 to reverse everything after it. rollbackCount N steps back N changesets; rollbackToDate reverses to a timestamp. Auto-rollback covers reversible change types; explicit <rollback> blocks cover raw SQL and data migrations.
• Alembic revision anatomy. Each revision has revision, down_revision, upgrade(), downgrade(). down_revision=None is the graph root; a tuple down_revision=(a, b) is a merge node. alembic_version stores only the current head; the file chain is the history. alembic upgrade head / downgrade -1 / downgrade base walk the graph.
• Alembic autogenerate discipline. alembic revision --autogenerate diffs Base.metadata (wired via target_metadata in env.py) against the live DB. It catches structural adds/drops but renders column renames as data-losing drop+add and ignores triggers/functions/check-constraints. Always review the diff and rewrite renames as op.alter_column(new_column_name=).
• Alembic branching. Two revisions sharing a down_revision create two heads; alembic upgrade head errors as ambiguous. alembic heads lists them; alembic merge -m "..." <h1> <h2> creates a two-parent merge revision (usually empty body) that reunites the graph. This graph structure is what linear tools (Flyway) lack.
• The CI/CD gate (tool-agnostic). On every PR: spin up a throwaway database, apply all migrations from scratch, run the integrity verb (flyway validate / liquibase validate / alembic check), fail closed. On deploy: run migrations as an explicit step before the app starts; grant DDL only to the migration_runner role. Never hand-run prod DDL; never auto-migrate on multi-replica boot.
• Zero-downtime expand/contract. Never rename/drop in-place under load. Expand (add nullable column, dual-write) → deploy code → backfill in batches → deploy code that reads the new column → contract (set not-null, drop old) → deploy final code. One deploy between phases so schema and every running code version stay compatible.
• Reverse migration vs restore. A reverse migration (liquibase rollback / alembic downgrade / paid Flyway undo) only safely undoes a schema change with no data loss. A data-destructive mistake needs point-in-time restore from backup, not a downgrade. Roll forward with a corrective migration by default; reserve reversals for safe pure-schema changes.

Frequently asked questions

What are database schema migrations?

database schema migrations are an ordered, immutable sequence of change scripts — checked into version control alongside the application — that evolve a database schema from one known version to the next, so any environment can be rebuilt deterministically by replaying the same scripts in the same order. Each migration is a file (or changeset) with a version identifier and an entry in a bookkeeping ledger table (flyway_schema_history, databasechangelog, or alembic_version) recording which scripts have already run; the tool applies only the pending ones and refuses to re-run or re-order completed ones. Migrations turn the schema into a reviewable, reproducible build artifact and structurally eliminate schema drift — the silent divergence between what dev, staging, and prod actually contain. Every team past its second engineer needs them, and every senior data-engineering interview probes them because they are the load-bearing discipline for safe schema evolution.

Flyway vs Liquibase vs Alembic — how do I choose?

Choose by three axes: team language, database count, and rollback philosophy. Pick Flyway when your team writes raw SQL, targets a single engine, and values roll-forward discipline — it is the SQL-first archetype with the least ceremony (versioned V__ files, repeatable R__ files, baselining). Pick Liquibase when one schema must ship to multiple database engines (Postgres, MySQL, Oracle, SQL Server) or you need an auditable, context-gated changelog — it is the abstracted archetype, describing changes so it can generate dialect-specific DDL and offering first-class rollback and preconditions. Pick Alembic when your application is Python on SQLAlchemy — it is the Python-native archetype, with autogenerate that diffs your ORM models against the live database and a revision graph that handles real team branching. All three solve drift equally well; the difference is authoring ergonomics and rollback story, not correctness.

Can you roll back a database migration?

It depends on the change and the tool, and the crucial distinction is a reverse migration versus a restore-from-backup. A reverse migration — Alembic's downgrade(), Liquibase's rollback, or paid Flyway's U__ undo scripts — safely undoes a schema change that loses no data (drop an added column, remove an added index). But a change that destroyed data (dropped a populated column, truncated a table) cannot be reversed by a migration, because the data is gone; recovering it requires point-in-time restore from a backup. Open-source Flyway is deliberately forward-only: instead of reversing, you write a corrective forward migration, which most senior teams prefer because it leaves a complete, auditable history rather than un-applying one. The rule: roll forward by default, reverse only for safe pure-schema changes, and restore from backup for data-destructive mistakes.

What is schema drift and how do migrations prevent it?

Schema drift is the slow, invisible divergence between what your application code assumes the schema is and what each environment actually contains — caused by out-of-band DDL, like an on-call engineer adding an index to prod by hand and never capturing it in a migration. Drift is the root cause of "it works in staging but the migration fails in prod." Migrations prevent it in two layers: first, every change must go through a reviewed migration file that every environment applies identically, so a change cannot reach one environment without reaching all of them; second — and more importantly — you revoke DDL permissions from human roles and grant them only to the migration runner, so the only possible path to a schema change is a migration, making drift structurally impossible to originate. A belt-and-braces CI check replays all migrations onto a scratch database and diffs the result against the live schema, surfacing any drift the moment it appears.

What is a repeatable migration in Flyway?

A repeatable migration in Flyway is a file named R__description.sql (no version number) that Flyway re-applies every time its checksum changes, always after all pending versioned migrations in the same run. It exists for database objects you want to redefine idempotently — views, functions, and stored procedures — using CREATE OR REPLACE so "the latest definition wins." Instead of a trail of V17__update_view.sql, V23__update_view_again.sql versioned files, a repeatable migration holds the single canonical definition in one file, and git carries its change history. When you edit the file, Flyway notices the checksum changed and re-runs it on the next flyway migrate; when nothing changed, it skips. Reserve versioned (V__) migrations for table/column/index/constraint changes and put every view and function in a repeatable migration — it keeps the definition readable and the version sequence uncluttered.

How does Alembic autogenerate work, and what does it miss?

alembic revision --autogenerate imports your SQLAlchemy model metadata (wired via target_metadata = Base.metadata in env.py), reflects the live database, diffs the two, and writes a revision file containing the op.* calls needed to reconcile them. It reliably catches structural changes — new and dropped tables, added and removed columns, nullability changes, new indexes and unique constraints, and foreign keys — and writes both upgrade() and a matching downgrade(). It misses several things and must always be reviewed: it renders a column rename as a drop_column + add_column (silent data loss if you don't rewrite it as op.alter_column(new_column_name=)), it often can't detect certain type changes or check-constraint edits, and it ignores everything server-side — triggers, functions, and stored procedures — which you add manually with op.execute. The discipline is simple: treat autogenerate as a fast first draft, read the diff carefully, fix the renames and server-side objects, then commit. In CI, alembic check fails the build if models and migrations have drifted apart, keeping the ORM and schema history in lockstep.

Practice on PipeCode

• Drill the database practice library → for the DDL, constraint, baselining, and schema-evolution problems that migration workflows live and die on.
• Rehearse on the design practice library → for the migration-tool selection, CI/CD gate, and zero-downtime expand/contract topology questions senior interviewers open with.
• Sharpen the pipeline axis with the ETL practice library → for the data-backfill, batched-update, and migration-in-a-DAG patterns that separate a safe deploy from a locked table.
• Layer in the data-transformation practice library → for the backfill and column-migration drills that make expand/contract muscle memory.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the three-tool decision matrix against real graded inputs.

Lock in schema-migration muscle memory

Docs explain the flags. PipeCode drills explain the decision — when Flyway's forward-only discipline beats a reverse migration, when Liquibase's abstraction earns its ceremony, when Alembic's autogenerate silently turns a rename into a dropped column, when expand/contract is the only safe path under load. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs data engineers actually face.

Practice database problems →
Practice design problems →