Java.DBMigrationTools.How do you prevent accidental data loss during migration?

Interview-ready answer (30s)

“I prevent data loss by using expand–migrate–contract, making destructive changes a separate, delayed step, validating with preconditions/checks, keeping migrations idempotent and small, taking backups/snapshots before risky steps, and running migrations in staging with production-like data and verification queries.”

1) Use Expand → Migrate → Contract (the #1 protection)

Never drop/rename/overwrite data in one step.

Expand (safe)

add new column/table
keep old data intact

Migrate (safe backfill)

backfill in batches (app job), dual-write if needed

Contract (destructive, delayed)

drop old columns/tables in a later release, after verification

Key idea: destructive actions happen only when you’ve proven the new path works.

2) Make destructive migrations explicit and gated

Treat these as “dangerous”:

DROP TABLE, DROP COLUMN
TRUNCATE
UPDATE ... without strict WHERE
data type changes that can truncate (e.g., bigint → int, text → varchar(50))

Practical guardrails:

Put them in a separate migration file (easy to audit)
Require a manual approval step in CI/CD for “destructive” migrations
Use naming like V2026_01_13_99__DESTRUCTIVE_drop_old_column.sql

3) Add preconditions / assertions (stop if unsafe)

Liquibase

preConditions can prevent running if state isn’t expected (e.g., column missing/present, row counts).

Flyway

No built-in preconditions like Liquibase, but you can:
- run verification SQL as a separate step in pipeline
- use callbacks (or app-side checks) to fail fast

Examples of assertions you want:

“Row count is within expected range”
“No NULLs before adding NOT NULL constraint”
“No duplicates before adding UNIQUE index”
“No invalid values before adding FK”

4) Avoid irreversible transforms in migrations

Bad:

UPDATE t SET payload = regexp_replace(payload, ...); -- overwrites original

Better:

write transformed data into a new column
keep original until verified
only then remove original

If you must transform, consider:

storing old values (audit table / temp backup table)
using a reversible mapping (rare)

5) Back up / snapshot before risky steps

For production safety:

take a DB snapshot/backup before destructive migration window
verify restore procedure exists (not just “we have backups”)

This is boring, but it’s what prevents catastrophes.

6) Use transactional safety (but know the limits)

Many DBs run migrations in a transaction (or the tool does), but:

some DDL is non-transactional depending on DB
long transactions increase lock time and risk

Senior practice:

short transactions
avoid huge updates in one transaction
batch backfills outside release

7) Test migrations like code

At minimum:

apply migrations on a clean DB
apply on a staging copy with prod-like data volume
run verification queries after migration

Verification examples:

counts match (old_table vs new_table)
checksums/hashes for critical columns
“no NULLs”, “no orphans”, “no duplicates”

8) Permissions and blast-radius control

Run migrations with a role that can:

create/alter intended schema
But not:
drop everything by accident (where feasible)

Also: lock down who can run migrations manually in prod.

A strong “senior” playbook (quick checklist)

✅ Expand–migrate–contract
✅ Destructive steps delayed & separately approved
✅ Preconditions/assertions + verification queries
✅ Backups/snapshots before destructive changes
✅ Backfill via app job in batches (observable + retryable)
✅ Test on prod-like data