Database Migration Testing Interview Questions (2026)

TL;DR

A strong migration-testing answer connects each source-to-target rule to executable evidence. Cover schema compatibility, reconciliation, transformations, incremental changes, performance, security, cutover, and a rehearsed recovery path.

Key Takeaways

• Tie every migration test to a source-to-target rule, business invariant, or recovery objective.
• Use row counts only as a first check, then reconcile keys, values, aggregates, constraints, and rejected records.
• Test full loads, deltas, retries, deletes, late events, and restart behavior as separate migration paths.
• Prove schema compatibility across tables, views, routines, indexes, sequences, and consuming applications.
• Rehearse cutover and rollback with production-shaped volume, measured timings, and explicit go or no-go thresholds.
• Protect masked data, permissions, encryption, audit evidence, retention rules, and residency throughout the move.
• In interviews, state the risk, test oracle, SQL evidence, operational response, and residual risk in that order.

Database migration testing interview questions assess whether you can prove that data, behavior, security, and service levels survive a move from one database state to another. A strong answer explains the risk, names the comparison oracle, shows the SQL or operational evidence, and states what happens when validation fails.

These data migration testing interview questions give you 50 model answers for moves between engines, versions, schemas, and platforms. It emphasizes concrete checks you can adapt to a real test plan, from source-to-target reconciliation to cutover decisions. For a hands-on companion, study testing data migrations before practicing the scenario questions.

TL;DR

Use the table as a topic map, not a memorized script. Interviewers usually reward a precise test oracle and failure response more than a long list of generic cases.

1. Database Migration Testing Interview Questions: Foundations

Q: What is database migration testing?

Database migration testing verifies that schema objects, records, business meaning, and dependent behavior remain correct while data moves to a new engine, version, model, or hosting platform. It covers more than copying rows because transformations, defaults, permissions, indexes, jobs, and application contracts can change. The final evidence should connect each acceptance rule to a repeatable query, test, or operational observation.

Q: How are database migration interview questions different from ordinary database testing questions?

Ordinary database testing evaluates the database in its current state, while migration testing compares two states and the mechanism connecting them. A migration suite must reason about mappings, coexistence windows, reruns, rejected rows, and cutover timing in addition to constraints and stored logic. It also has a temporary but critical recovery dimension because a correct target is insufficient if the release cannot be reversed safely.

Q: Which phases belong in a migration test strategy?

Divide the work into discovery, baseline capture, dry runs, functional reconciliation, nonfunctional validation, cutover rehearsal, production verification, and post-migration monitoring. Discovery inventories source objects and consumers, while dry runs expose mapping and capacity problems before the release window. Each later phase should have an owner, entry criteria, evidence location, defect rule, and exit threshold.

Q: What types of database migration should a tester recognize?

Common patterns include engine-to-engine moves, version upgrades, on-premises to cloud relocation, schema redesign, tenant consolidation, sharding, and data-center moves. The risk changes by pattern: a version upgrade emphasizes compatibility, while consolidation emphasizes key collisions and tenant isolation. Ask whether the move is offline, online, phased, or dual-run because that choice determines the concurrency and rollback tests.

Q: What is a test oracle for migrated data?

A test oracle is the authoritative rule used to decide whether a target value is correct. It may be direct source equality, a documented transformation, an invariant such as total account balance, or an independently computed business result. When no reliable oracle exists, obtain sign-off on assumptions and use several weaker signals rather than presenting row-count equality as proof.

2. Planning, Risk, and Scope

Q: How do you prioritize migration test cases?

Rank cases by business impact, transformation complexity, data volume, regulatory sensitivity, and likelihood of silent corruption. Put money, identity, entitlements, active transactions, and irreversible transformations ahead of low-value history. Then map the highest risks to both a preventive check before cutover and a detection query after cutover.

Q: What would you do if production data cannot be copied into test?

Build a representative data model using masked extracts, deterministic synthetic records, and deliberately difficult boundary cases. Preserve distributions that affect plans and storage, such as skewed tenant sizes, null rates, long strings, duplicate candidates, and old timestamps. The test data strategy guide helps separate realism requirements from fields that must never leave production.

Q: How do you create traceability for migration coverage?

Give every source-to-target mapping rule an identifier and link it to the destination column, transformation owner, test case, SQL evidence, and defect. Include non-column objects such as sequences, grants, views, triggers, scheduled jobs, and retention policies in the same inventory. Traceability is complete only when an unmapped source object and an untested target rule are both easy to detect.

Q: Is sampling acceptable in data migration testing?

Sampling is useful for expensive semantic checks, but it should not replace full-population tests that are cheap to run. Apply complete counts, null checks, uniqueness checks, and aggregate reconciliations first, then use stratified samples for documents or complex transformations. Choose strata by risk, such as tenant size, date range, status, geography, and exceptional mapping path, instead of selecting arbitrary rows.

Q: What environments are needed for credible migration testing?

Use a source baseline, a target environment matching production configuration, the actual migration tooling, and application versions representing coexistence and post-cutover states. Production-shaped volume matters because a tiny database hides batch duration, lock contention, index build time, and storage pressure. Control configuration drift by capturing engine settings, extensions, collation, time zone, and migration artifact versions with each rehearsal.

3. Schema Migration Testing Questions and Compatibility Validation

Q: How do you compare source and target schemas?

Export catalog metadata into a normalized form and compare object names, types, lengths, precision, nullability, defaults, keys, indexes, partitions, and ownership. Normalize engine-specific syntax so harmless formatting does not bury meaningful differences. The following PostgreSQL script is self-contained. It returns one intentional mismatch, proving the comparison logic runs:

BEGIN;
CREATE TEMP TABLE expected_columns (
  table_name text, column_name text, data_type text, is_nullable text
);
CREATE TEMP TABLE actual_columns (LIKE expected_columns);
INSERT INTO expected_columns VALUES
  ('customer', 'id', 'bigint', 'NO'),
  ('customer', 'email', 'text', 'NO');
INSERT INTO actual_columns VALUES
  ('customer', 'id', 'bigint', 'NO'),
  ('customer', 'email', 'text', 'YES');
SELECT 'missing_or_changed_in_target' AS issue, * FROM expected_columns
EXCEPT
SELECT 'missing_or_changed_in_target', * FROM actual_columns;
ROLLBACK;

Q: How do you test a backward-compatible schema change?

Run the old and new application contracts against the intermediate schema, not just the final application against the final schema. An additive nullable column is usually safer than an immediate rename, but triggers, defaults, serialization, and SELECT * consumers can still break. Prove reads and writes from both versions during the deployment overlap, then verify removal only after old traffic is gone.

Q: Which data type conversions deserve special attention?

Test narrowing integers, decimal precision and scale, floating-point to exact numeric conversion, text encoding, timestamp zones, booleans encoded as flags, and large binary values. Create cases at the minimum, maximum, just-inside, and just-outside each destination boundary. Also inspect the migration rejection path because a visible failed row is safer than silent truncation or rounding.

Q: How do you validate constraints, indexes, and sequences?

Compare definitions through system catalogs, then exercise their behavior with duplicate keys, invalid references, null values, and concurrent inserts. Confirm indexes serve the intended predicates with execution plans rather than assuming that a matching name means matching behavior. For sequences or identity columns, verify the next generated value exceeds the migrated maximum and cannot collide with existing keys.

Q: How do you test views, procedures, triggers, and scheduled jobs after migration?

Inventory each programmable object and its dependencies, deploy it through the production path, and execute a known input with observable outputs. Compare result sets and side effects, including audit rows, notifications, downstream tables, and transaction rollback behavior. Scheduled jobs also need time zone, credential, retry, overlap, and missed-run tests because a successfully created job can still run at the wrong time.

4. Data Mapping, Transformation, and ETL Migration Testing Questions

Q: What should a source-to-target mapping document contain?

Record the source expression, target field, target type, transformation rule, default, null handling, reference lookup, rejection behavior, and owning business rule. Add examples for ambiguous transformations and version the document with the migration code. A tester should be able to derive positive, boundary, and invalid cases without guessing what a developer intended.

Q: How do you test transformation logic?

Create a decision table for every branch, then compute expected outputs independently of the migration implementation. Cover standard values, boundaries, nulls, malformed input, reference misses, and collisions created by normalization. For complex pipelines, reconcile intermediate stages so the first incorrect transformation is visible instead of diagnosing only the final target.

Q: How should nulls, blanks, and defaults be validated?

Treat SQL NULL, an empty string, whitespace, a missing field, and a business default as distinct inputs. Query their source frequencies, apply the documented mapping, and compare target frequencies by outcome. Reject any design that silently turns unknown information into a meaningful value such as zero unless the business owner explicitly approves that semantics.

Q: What problems occur with encodings, collations, and time zones?

Encoding changes can corrupt multibyte names and symbols, collation changes can alter sorting or uniqueness, and time zone conversion can shift instants around daylight-saving boundaries. Include accented text, emoji, combining characters, case variants, and timestamps near offset changes. Compare both stored values and application-visible behavior because byte equality and user-perceived equality are not always the same.

Q: How do you preserve referential integrity when load order changes?

Build a dependency graph of parent and child entities, then choose ordered loading, deferred constraints, or staged key resolution deliberately. Validate orphan counts before and after each stage and account for references to filtered or archived parents. If constraints are disabled during loading, the release plan must show exactly when they are revalidated and what blocks cutover on failure.

5. SQL Reconciliation in Database Migration Testing Interview Questions

Q: Why are source and target row counts insufficient?

Equal counts can hide one missing row and one duplicate, swapped ownership, corrupted values, or an incorrect filter that happens to preserve volume. Pair counts with key-set differences, duplicate detection, field comparisons, and business aggregates. Segment every result by meaningful dimensions so an overage in one tenant cannot cancel a shortage in another. This is why data integrity validation after migration must go beyond totals.

Q: What do SQL data reconciliation interview questions expect you to explain?

Use anti-joins or EXCEPT for missing keys, grouped counts for duplicates, conditional aggregates for null and status distributions, and joins for attribute mismatches. Compare canonicalized values only when the mapping requires canonicalization, otherwise normalization can conceal defects. Practice these patterns with SQL data integrity validation so you can explain both the query and its blind spots.

Q: How do you reconcile transformed monetary data?

Compare row-level expected values using the documented rounding mode, then reconcile sums by currency, account, and accounting period. Keep decimal arithmetic exact and test half-way values, negative amounts, refunds, and currencies with different minor units. A global grand total is weak evidence because compensating errors can produce the same result.

Q: How can checksums help with large migrations?

A deterministic digest can summarize canonical row content and identify partitions needing deeper comparison. Sort or aggregate by stable keys, delimit fields unambiguously, represent nulls explicitly, and calculate source and target values with equivalent encoding rules. Never rely on one whole-table checksum alone because it does not locate errors and may conceal ordering or canonicalization mistakes.

Q: Can you show a runnable reconciliation query?

This PostgreSQL example checks missing keys and changed values independently, which prevents equal row counts from creating false confidence. It creates disposable tables, reports one absent target row and one changed amount, and rolls everything back. Save the block as reconcile.sql. Run it with psql -v ON_ERROR_STOP=1 -f reconcile.sql to verify both result sets:

BEGIN;
CREATE TEMP TABLE source_orders (id bigint PRIMARY KEY, amount numeric(12,2));
CREATE TEMP TABLE target_orders (id bigint PRIMARY KEY, amount numeric(12,2));
INSERT INTO source_orders VALUES (1, 10.00), (2, 25.50), (3, 8.75);
INSERT INTO target_orders VALUES (1, 10.00), (2, 25.55);
SELECT s.id AS missing_target_id
FROM source_orders s
LEFT JOIN target_orders t USING (id)
WHERE t.id IS NULL;
SELECT s.id, s.amount AS source_amount, t.amount AS target_amount
FROM source_orders s
JOIN target_orders t USING (id)
WHERE s.amount IS DISTINCT FROM t.amount;
ROLLBACK;

6. Incremental Loads, CDC, and Restartability

Q: How does testing a full load differ from testing a delta load?

A full load emphasizes completeness, capacity, deterministic transformation, and initial key generation. A delta load adds watermark boundaries, change ordering, duplicates, updates, hard deletes, tombstones, and events arriving after the window. Test the handoff between full and incremental processing because records created at that boundary are especially easy to miss or apply twice.

Q: How do you verify change data capture?

Create insert, update, and delete transactions with known commit order, then trace their log position or offset through to the target. Include multiple changes to one key, rolled-back source transactions, large transactions, schema changes, and consumer restarts. Success means the final target state and processing checkpoint are correct, not merely that every event was observed somewhere.

Q: How do you test idempotency in a migration job?

Run the identical batch at least twice with the same batch identity and confirm that the second run creates no additional business effect. The target needs a stable conflict key and explicit update rules, while the job ledger needs a durable success state. The standalone PostgreSQL block below demonstrates an idempotent upsert. Its final query verifies that only two rows exist after replay:

BEGIN;
CREATE TEMP TABLE migrated_customer (
  customer_id bigint PRIMARY KEY,
  email text NOT NULL,
  source_updated_at timestamptz NOT NULL
);
INSERT INTO migrated_customer VALUES
  (101, 'a@example.test', '2026-08-01T10:00:00Z'),
  (102, 'b@example.test', '2026-08-01T10:05:00Z')
ON CONFLICT (customer_id) DO UPDATE SET
  email = EXCLUDED.email,
  source_updated_at = EXCLUDED.source_updated_at;
INSERT INTO migrated_customer VALUES
  (101, 'a@example.test', '2026-08-01T10:00:00Z'),
  (102, 'b@example.test', '2026-08-01T10:05:00Z')
ON CONFLICT (customer_id) DO UPDATE SET
  email = EXCLUDED.email,
  source_updated_at = EXCLUDED.source_updated_at;
SELECT count(*) AS rows_after_replay FROM migrated_customer;
ROLLBACK;

Q: How should late-arriving records and deletes be tested?

Send a record whose event time precedes the watermark but whose arrival time follows it, then confirm the chosen lateness policy includes or quarantines it visibly. Test hard deletes, soft deletes, undeletes, and parent deletion with surviving children. Measure deletion lag separately from update lag because privacy and retention obligations may impose a stricter deadline.

Q: What proves a migration job is restartable?

Force termination before extraction, during a batch, after target commit, and before checkpoint commit. On restart, the job should resume from a durable boundary, neither skipping uncommitted work nor duplicating committed effects. Reconcile the target and job-control tables after every fault, and ensure operators can distinguish a safe retry from a batch requiring compensation.

7. Performance, Volume, and Concurrency

Q: What performance measurements matter during migration?

Track throughput, end-to-end lag, query latency, lock waits, CPU, memory, storage growth, transaction log volume, replica lag, and error rates. Compare them with a baseline collected under a production-shaped workload and define thresholds tied to the cutover window. Average throughput alone is misleading when long tails or checkpoints create periods in which applications cannot meet service objectives.

Q: How do you design a volume test for migration?

Model total rows, row width, large objects, tenant skew, index size, churn rate, and expected growth through the release date. Generate or mask enough data to reproduce the largest partitions and hottest keys, then run the same artifacts used in production. Record phase timings separately so tuning extraction does not distract from a slower index build or validation stage.

Q: How do you test concurrent application writes during an online migration?

Synchronize writers around snapshot capture, copy boundaries, and CDC startup rather than issuing transactions sequentially. Exercise updates to rows being copied, insert-then-delete sequences, conflicting writes from old and new paths, and long-running transactions. Reconcile final values against commit order and confirm that no write vanishes in the gap between snapshot and change stream.

Q: How do you diagnose a query regression on the target?

Compare representative query latency and execution plans with equivalent statistics and warm-up conditions. Look for cardinality errors, full scans, changed join order, missing or unusable indexes, implicit casts, partition pruning failures, and parameter sensitivity. The SQL validation guide for ETL is useful preparation because diagnosis should connect a plan difference to a specific data distribution or schema change.

Q: Can you demonstrate a repeatable plan check?

This PostgreSQL script creates 10,000 rows, indexes the search column, refreshes statistics, and displays the actual execution plan. The assertion query verifies the expected record independently of the planner's chosen access method. Paste it into psql. The final value should be 5050:

BEGIN;
CREATE TEMP TABLE migration_probe AS
SELECT n AS id, n % 100 AS tenant_id, n * 10 AS amount
FROM generate_series(1, 10000) AS n;
CREATE INDEX migration_probe_tenant_id_idx ON migration_probe (tenant_id);
ANALYZE migration_probe;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT sum(amount) FROM migration_probe WHERE tenant_id = 0;
SELECT sum(amount) / 1000 AS verification_value
FROM migration_probe WHERE tenant_id = 0;
ROLLBACK;

8. Security, Privacy, and Compliance

Q: How do you test masking during a migration?

Classify sensitive columns first, then verify that the approved masking rule is irreversible where required and consistent where joins must survive. Search extracts, staging tables, logs, rejects, backups, and temporary files because protection on the final target does not secure intermediate copies. Include rare formats and nulls, then use AI-powered test data masking as a deeper checklist for leakage tests.

Q: How do you validate roles and permissions on the target?

Create a subject-action-resource matrix for application roles, operators, analysts, migration accounts, and break-glass users. Test allowed operations and explicit denials with real identities, including ownership, default privileges, row-level rules, and stored routine execution. Remove or expire the elevated migration credential after cutover and prove that normal service accounts still have only least-privilege access.

Q: What encryption checks belong in migration testing?

Verify transport encryption for every source, target, staging, replication, and backup connection, then inspect at-rest settings and key ownership. Test certificate validation and rotation paths instead of accepting a successful connection configured to skip trust checks. Confirm sensitive values are absent from command histories, pipeline output, error logs, and exported diagnostic bundles.

Q: How do you test auditability after a migration?

Perform representative reads, writes, permission failures, administrative changes, and bulk operations with known identities. Confirm the target audit trail records actor, action, object, timestamp, outcome, and correlation details in the required time standard. Send evidence to the real monitoring destination and test alert rules because a local audit table that nobody collects is not operationally sufficient.

Q: How do retention and data residency affect the test plan?

Map each data class to its allowed region, retention duration, legal hold behavior, deletion workflow, and backup lifecycle. Verify the migration does not resurrect expired records, move restricted data across a boundary, or leave uncontrolled source copies. Test deletion through replicas, search indexes, archives, and restore media so the evidence covers the complete data path.

9. Cutover, Rollback, and Disaster Recovery

Q: What should a cutover rehearsal prove?

A rehearsal should execute the production runbook with production-shaped volume, named owners, realistic network paths, and the same observability used on release day. Capture timings for quiescing writes, final delta, validation, application switch, smoke checks, and decision points. The result must show whether the plan fits the window and how much contingency remains for a safe abort.

Q: How do you test rollback?

Define the rollback point, the authority for invoking it, and the treatment of writes accepted after cutover. Execute the reverse or restore procedure in a rehearsal, then reconcile both data and application behavior on the recovered platform. A backup existing somewhere is not rollback proof unless restore time, write reconciliation, credentials, and client routing have all been exercised.

Q: What backup and restore tests are required?

Restore the exact backup type planned for recovery into an isolated environment and validate integrity, permissions, encryption keys, extensions, and application startup. Measure recovery time and the latest recoverable transaction against the agreed RTO and RPO. Include a corrupted or unavailable backup in a tabletop exercise so operators know the next recovery source and escalation path.

Q: How do canary and dual-run strategies reduce migration risk?

A canary routes a controlled tenant or read workload to the target, while dual-run compares behavior across both systems before full promotion. Choose comparison fields carefully because generated IDs, timestamps, and eventually consistent results may require semantic rather than byte equality. Prevent dual writes from creating conflicting authorities by documenting ownership, conflict resolution, and the exact point at which the old system becomes read-only.

Q: What belongs in a go or no-go decision?

Use preapproved thresholds for reconciliation defects, rejected rows, validation duration, replication lag, error rate, latency, rollback time remaining, and monitoring health. Present unresolved issues with business impact and workaround, not only severity labels. One accountable decision maker should record the evidence, decision time, and conditions for continuing or reversing the release.

10. Database Cutover Testing Scenarios and Database Migration Testing Interview Questions

Q: Counts match after cutover, but users report missing orders. What do you investigate first?

Segment counts by tenant, status, and date, then compare order key sets to find offsetting duplicates and omissions. Trace a reported order through source, staging, rejection, target, cache, search index, and API authorization because the row may exist but remain invisible. Freeze destructive cleanup until the authoritative gap and affected cutover interval are known.

Q: A decimal column is rounded differently on the target. How do you respond?

Stop treating it as a cosmetic mismatch and identify the approved precision, scale, and rounding rule for each currency or measure. Quantify affected rows and financial aggregates, including negative and halfway values, then reproduce the conversion outside the migration code. Correct the mapping, remigrate the bounded population, and require business reconciliation before release approval.

Q: Foreign-key creation fails after a bulk load. What is your approach?

Run an anti-join for each failed relationship and classify orphans as bad source data, filtered parents, mapping defects, or load-order problems. Do not leave the constraint disabled merely to finish the cutover because future writes would compound the corruption. Repair or quarantine according to an approved rule, recreate the constraint, and rerun both orphan and application-write tests.

Q: A batch fails after writing 70 percent of its rows. What should happen next?

Determine whether writes committed per row, per chunk, or as one transaction and inspect the durable checkpoint before choosing retry or compensation. Replaying is safe only if keys and update rules are idempotent; otherwise restore the affected partition or remove rows by recorded batch identity. After recovery, reconcile the batch boundaries and inject the same fault again to prove restart behavior.

Q: How would you test a zero-downtime schema migration used by multiple API versions?

Model the expand, migrate, switch, and contract stages, then run old and new API versions against every overlapping database state. Validate reads, writes, defaults, backfill progress, replication lag, and rollback before removing the old column or constraint. Add contract checks from the API contract testing guide so a database change cannot silently alter response shape or semantics.

How Interviewers Grade Your Answers

Interviewers listen for a chain of reasoning, not a catalog of tools. A senior response moves from consequence to proof and closes with an operational decision.

Use a compact answer pattern: state the risk, identify the oracle, describe representative data, name the executable checks, and explain the response to failure. For scenario questions, quantify illustrative thresholds only after saying they must be agreed with product, operations, and compliance owners. You can rehearse aloud with the QA interview practice tool and refine any answer that lacks observable evidence.

Common Mistakes

• Declaring success from equal total row counts while ignoring duplicate keys, changed attributes, and compensating errors.
• Testing only the first full load and skipping deltas, replay, delete propagation, late events, and checkpoint recovery.
• Comparing schemas by table and column names but omitting defaults, collation, indexes, grants, routines, jobs, and sequences.
• Using random samples that miss the largest tenants, rare transformations, boundary dates, and invalid source records.
• Disabling constraints for speed without a mandatory revalidation gate before application traffic moves.
• Treating performance as batch duration alone while locks, replica lag, log growth, and application latency degrade.
• Copying production data without proving masking across extracts, staging, rejects, logs, snapshots, and backups.
• Saying "we can roll back" without handling target-side writes accepted after the switch.
• Quoting fixed acceptance percentages without tying them to business impact and approved release criteria.
• Writing a reconciliation query that normalizes both sides so aggressively that it hides the defect under test.

Conclusion

The best database migration testing interview questions reveal whether you can turn migration risk into defensible evidence. Build answers around mappings, invariants, SQL reconciliation, incremental behavior, production-shaped performance, protected data, and a timed recovery path.

Do not memorize all 50 responses word for word. Pick a realistic migration, practice explaining your oracle and failure response, then use SQL test data setup and teardown to sharpen the executable parts of your examples.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Database Testing Scenario Interview Questions for Senior QA (2026)
• 500+ QA and Manual Testing Interview Questions and Answers (2026)
• Accessibility Testing Interview Questions and Answers (2026)
• API testing Scenario-Based Interview Questions and Answers (2026)
• Contract Testing Interview Questions for Microservices (2026)
• Cypress Component Testing Interview Questions for React (2026)