All jobs / Database administrator
Migrations interview questions
Migrations questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.
You have to load tens of millions of rows into a table tonight. How?
The strongest answer: Use the engine’s bulk-load path, load in committed batches, consider dropping and recreating non-critical indexes, and refresh statistics after — while watching log and disk space
Why it works: Exactly — bulk path, batches, index strategy, statistics and space are the checklist.
- Use the bulk-load path.
- Commit in batches.
- Handle indexes deliberately.
- Refresh statistics after.
A minor security patch for the database engine is out. How do you roll it out?
The strongest answer: Read the release notes, apply it to a test environment, then patch replicas first and fail over or patch the primary in a window, with a rollback path and monitoring afterwards
Why it works: Exactly — notes, test, replicas first, then the primary with a way back.
- Read the notes, test first.
- Replicas before primary.
- Have a rollback path.
- Internal is not safe.
A developer sends a schema script to run on production at 17:00 on Friday. Your response?
The strongest answer: Review it, run it in a lower environment, check it has a rollback and that someone will be around afterwards — if any of those fail, schedule it for when the team is present
Why it works: Exactly — timing is about who can respond, and review is not optional.
- Review and rehearse.
- Insist on a rollback.
- Deploy when people are present.
- Blame is not a process.
A migration has copied a few hundred tables into a new database and the team wants to switch over tonight.
How do you prove the data actually arrived intact?
The strongest answer: Row counts per table, checksums on the key and numeric columns, a sample of rows compared end to end, and business queries that must give the same answer on both sides
Why it works: Exactly — counts catch missing rows, checksums catch changed values, and a business query catches the truncation or encoding problem that neither notices.
- Counts catch missing rows.
- Checksums catch changed values.
- Compare real business answers.
- Record the evidence before switching.
The migration is due to run automatically at deploy time on the busiest table in the system.
A migration adds a NOT NULL column with a default to a table with hundreds of millions of rows. Concern?
The strongest answer: Depending on the engine and version it may rewrite the whole table under a lock; I check how this engine handles it, and if needed add nullable, backfill in batches, then add the constraint
Why it works: Exactly — know your engine, and use the expand-backfill-constrain pattern when a rewrite would lock.
- Check engine behaviour first.
- Add nullable, backfill, constrain.
- Batch the backfill.
- Never assume metadata-only.
You need a new index on a busy table. How do you build it?
The strongest answer: With the engine’s online or concurrent build so writes continue, during a quiet period, watching for long transactions that would block it — and check the result is valid afterwards
Why it works: Exactly — online build, quiet window, and verify the index actually completed.
- Use the online build.
- Pick a quiet window anyway.
- Watch for blocking transactions.
- Verify the index is valid.
How do you rename a heavily used column without downtime?
The strongest answer: Expand and contract: add the new column, dual-write from the application, backfill in batches, switch reads, then drop the old column in a later release once nothing references it
Why it works: Exactly — several small, reversible steps instead of one coordinated leap.
- Add, dual-write, backfill, switch, drop.
- Each step is reversible.
- Old and new code coexist.
- Drop only when nothing references it.
A backfill must touch every row of a large live table. How do you run it?
The strongest answer: In small keyed batches with short transactions, pausing between them, throttled on replication lag and lock waits, resumable from where it stopped, and run outside the peak
Why it works: Exactly — small, resumable, throttled on the signals that matter.
- Small keyed batches.
- Throttle on lag and locks.
- Resumable from a checkpoint.
- Off-peak, from a server.
How do you plan a major version upgrade of a production database?
The strongest answer: Inventory extensions, drivers and deprecated features, test the application against the new version, choose the method — in-place, logical replication cut-over or dump and restore — rehearse it with timings, and keep a rollback
Why it works: Exactly — compatibility, method, rehearsal, rollback.
- Check compatibility first.
- Test the application.
- Rehearse with timings.
- Keep a rollback.
How do you upgrade a major database version across a large fleet with near-zero downtime?
The strongest answer: Build a repeatable pipeline: compatibility checks, a logical-replication or blue-green cut-over per database, canary a low-risk system first, automate the switch with a rollback, and schedule the fleet in waves with a stop rule
Why it works: Exactly — canary, automate, wave, and know when to stop.
- Canary, then waves.
- Automate the cut-over and rollback.
- A stop rule for the wave.
- Repeatable beats heroic.
A major engine version upgrade is planned for the platform a dozen applications sit on.
How do you test compatibility before the date?
The strongest answer: Restore a production copy onto the new version, replay a captured workload, compare plans and timings, check deprecated syntax and driver support — and keep the old optimiser behaviour at first
Why it works: Exactly — a real workload replayed against a real copy is the only compatibility test that counts, and separating the optimiser change keeps the rollback simple.
- Restore a production copy.
- Replay a real workload.
- Compare plans and timings.
- Separate the optimiser change.
A change freeze is in force and a team needs an urgent index to stop a customer-facing slowdown. How do you decide?
The strongest answer: Weigh doing it against not doing it: an online index build with a rollback is low risk, so it goes through the emergency change path with a second reviewer and a record
Why it works: Exactly — the emergency path exists for exactly this, with review and a trail.
- Compare risk of doing vs not.
- Use the emergency path.
- Second reviewer and a record.
- Urgent is not a criterion.
A director wants an untested schema change run on the payments database tonight to hit a launch date.
How do you handle it?
The strongest answer: Say clearly what the risk is and what would make it acceptable — a rehearsal, a rollback, a smaller scope, a different time — and escalate with the trade-off written down, neither blocking nor caving
Why it works: Exactly — a conditional no with a route to yes, and the decision owned at the right level with eyes open.
- Name the risk precisely.
- Offer the conditions for yes.
- Escalate with the trade-off written.
- Blame is not mitigation.
You are moving a critical on-premises database to a cloud-managed service with minimal downtime. Plan?
The strongest answer: Assess compatibility and unsupported features, seed the target from a backup, keep it in sync with replication, rehearse the cut-over with timings and a rollback, then switch in a short window at zero lag
Why it works: Exactly — seed, sync, rehearse, switch, with a way back.
- Assess compatibility.
- Seed, then replicate.
- Rehearse the cut-over.
- Keep a rollback to on-prem.
Teams wait weeks for the DBA team to review schema changes. How do you fix the bottleneck without losing safety?
The strongest answer: Encode the review as automated checks in the migration pipeline — lock-risk patterns, missing indexes on foreign keys, unsafe defaults — with guardrails and paved patterns, and reserve human review for the flagged minority
Why it works: Exactly — turn expert judgement into tooling and spend humans on the exceptions.
- Automate the checks.
- Paved patterns by default.
- Humans review the flagged few.
- Queues create workarounds.
In a retrospective, three teams say they now avoid asking the DBAs so they can ship on time.
Developers describe the DBA team as “the department of no”. What do you change?
The strongest answer: Publish the patterns we say yes to, embed with teams early in design instead of at the gate, give them tools to see their own plans and lag, and measure ourselves on delivery time too
Why it works: Exactly — move the expertise upstream and make the safe path the easy one.
- Publish the yes patterns.
- Engage at design, not the gate.
- Give teams the tools to self-serve.
- Measure delivery, not only incidents.