Data Migration Interview Questions: Complete Preparation Guide (2026)

What Data Migration Interviews Test

Data migration interviews target a specific intersection of skills: can you move large volumes of data between heterogeneous systems without losing records, corrupting relationships, or blowing past a downtime window? Every question is probing one of these areas:

Skill Area	What They Evaluate	How It Appears in Questions
ETL/ELT Design	Can you architect pipelines that handle schema differences, data type mismatches, and transformation logic without building something fragile?	Pipeline architecture, tool selection, CDC implementation
Data Quality Engineering	Do you treat validation as an afterthought or build it into every stage? Can you catch problems before they reach production?	Profiling strategies, reconciliation methods, Great Expectations or dbt tests
Risk Management	When a migration fails at 2 AM on cutover night, do you have a rollback plan that actually works? Have you tested it?	Rollback strategies, fallback architecture, go/no-go criteria
Hands-On Tool Experience	Have you actually configured AWS DMS replication instances, or do you only know what the service does from reading documentation?	Tool-specific configuration, troubleshooting, performance tuning
Constraint Management	Can you deliver under real-world pressure — tight downtime windows, legacy systems with no documentation, billions of rows?	Scenario questions, war stories, trade-off discussions

The strongest candidates demonstrate depth in at least two of these areas and working familiarity with the rest. Interviewers want someone who has been in the room when a migration went sideways and knows what to do next.

Technical and Process Questions

1. When would you choose a big bang migration over a phased approach, and what are the risks of each? (Migration Methodology)

Why they ask: This is the most fundamental architectural decision in any migration project. It reveals whether you understand the trade-offs or just default to whatever you have done before.

How to answer: Frame it as a risk/complexity trade-off. Big bang is simpler but higher risk; phased is safer but introduces dual-system complexity.

Example answer: “Big bang works when the dataset fits within a scheduled maintenance window and systems have minimal interdependencies. I used it for a 200GB Oracle-to-PostgreSQL migration with a 6-hour weekend window — full extract, transform, load, validate, and cutover. The risk: if validation fails at hour five, you are rolling back under pressure. Phased migration is my default for anything complex. On an ERP migration with 40+ entity types, we migrated reference data first, then transactional data in weekly batches using CDC for synchronization. The complexity is managing dual-write periods and referential integrity across phases. Deciding factors are downtime tolerance, data volume, and system coupling.”

2. Describe how you would design an ETL pipeline for migrating data between two systems with different schemas. (ETL Design)

Why they ask: Schema mismatch is the norm, not the exception. They want to see your thought process for decomposing the problem into manageable layers.

How to answer: Walk through the pipeline in stages: extract, stage, transform, load. Emphasize the staging layer where most real work happens.

Example answer: “I structure the pipeline in four layers. Extract from the source into a raw staging area with no transformations — an immutable audit trail. A cleansing layer handling nulls, whitespace, date standardization, and business rules. A transformation layer mapping source to target schema — for example, parsing a legacy single-field address into structured street, city, state, and zip columns using regex and a geocoding API for ambiguous cases. Finally, a load layer with upsert logic and a control table tracking batch IDs, row counts, and timestamps. If the pipeline fails mid-batch, I resume from the last checkpoint rather than starting over.”

3. How do you approach data mapping when source and target systems use fundamentally different data models? (Data Mapping)

Why they ask: Data mapping is where migrations succeed or fail. Poorly mapped fields cause downstream bugs that surface weeks after go-live.

How to answer: Describe building a mapping document, handling ambiguous mappings, and collaborating with business stakeholders who understand the semantics.

Example answer: “I extract data dictionaries from both systems and build a mapping spreadsheet: source table, source column, data type, target table, target column, transformation rule, and edge case notes. The hard mappings require business input. For a mainframe-to-Salesforce migration, the legacy system had a ‘customer type’ code field with 47 values, some undocumented. Frequency analysis showed 8 codes covered 98% of records — I mapped those with business sign-off and sent the remaining 39 low-count codes to a default category flagged for manual review. Every mapping gets a test case. No exceptions.”

4. Walk me through how you validate that a migration was successful. (Data Validation)

Why they ask: Anyone can move data. Proving it arrived correctly is what separates professionals from people who got lucky.

How to answer: Cover row counts, checksums, business rule validation, and end-user acceptance testing. Emphasize automation.

Example answer: “I validate at four levels. Row count reconciliation per table. Checksum validation on critical columns — MD5 hashes on concatenated key fields compared between source and target:

-- Source checksum
SELECT COUNT(*) AS row_count,
       SUM(CAST(MD5(CONCAT(customer_id, email, account_status))
           AS BIGINT)) AS checksum
FROM source_db.customers;

-- Target checksum (same logic)
SELECT COUNT(*) AS row_count,
       SUM(CAST(MD5(CONCAT(customer_id, email, account_status))
           AS BIGINT)) AS checksum
FROM target_db.customers;

Referential integrity checks — every foreign key resolves to a valid parent. Business rule validation — sample records reviewed by stakeholders to confirm derived fields and status transitions. I automate the first three levels and generate a reconciliation report as the go/no-go artifact.”

5. How do you handle performance when migrating billions of rows? (Large-Scale Migration)

Why they ask: A migration that works on a million rows and collapses at a billion is a common failure mode. They want to know if you have dealt with genuine scale.

How to answer: Discuss partitioning, parallelism, bulk loading, and index management.

Example answer: “Partition-based migration is the core strategy. On a 3.2 billion row Oracle table, I partitioned by date range and ran 12 parallel extraction threads. Each partition loaded into PostgreSQL staging tables with indexes and constraints dropped. After loading, I rebuilt indexes concurrently and re-enabled constraints. The load used COPY instead of INSERT — roughly 10x faster. I also tuned shared_buffers, work_mem, and checkpoint intervals for the load window. Total time went from 72 hours to 11 hours. The key: treat the database as a batch engine during migration, not an OLTP system.”

6. Describe your approach to designing a rollback strategy for a migration. (Rollback Strategy)

Why they ask: A migration without a tested rollback plan is reckless. They want to confirm you have planned for failure.

How to answer: Distinguish rollback at different stages: pre-cutover, during cutover, and post-cutover when users are writing to the new system.

Example answer: “Rollback planning starts at the architecture phase. For phased migrations, I maintain backward-compatible CDC from target to source for reverse-replication during parallel-run periods. For big bang, I take a point-in-time snapshot before load begins — RDS or EBS snapshots on AWS. Rollback triggers are specific: row count discrepancy above 0.01%, any referential integrity failure, or more than 5 rejected sample records in user validation. I test the full rollback during dress rehearsal. If it takes longer than 30 minutes, I redesign it. An untested rollback plan is not a plan — it is a hope.”

7. How would you implement a zero-downtime migration for a system that cannot go offline? (Zero-Downtime Migration)

Why they ask: Many production systems cannot tolerate a maintenance window. This tests whether you understand continuous replication patterns.

How to answer: Describe the CDC-based approach: bulk load, continuous change capture, then cutover when the target catches up.

Example answer: “Bulk load historical data, use CDC to stream ongoing changes until the target is caught up, then cut over connections. For an Oracle-to-PostgreSQL migration, I used AWS DMS with full-load-plus-CDC. The full load ran 14 hours on 500GB. DMS captured changes via Oracle LogMiner and applied them to the target. Once replication lag was consistently under 2 seconds, we cut over via DNS switch — about 90 seconds of effective downtime. The tricky part is DDL changes during replication. We froze schema changes two weeks before cutover. Unavoidable changes were applied to both systems simultaneously with DMS task mapping updates.”

8. How do you handle slowly changing dimensions during a migration? (SCD Implementation)

Why they ask: SCD handling reveals whether you understand temporal data modeling or just flatten everything and lose history.

How to answer: Focus on Type 2 since it is the most common and complex to migrate. Discuss preserving history and managing surrogate keys.

Example answer: “If the target only needs current state, SCD Type 1 is straightforward. But most analytical targets need history — SCD Type 2 with effective dates and a current-record flag. I extract the full history from source, assigning effective_from and effective_to dates with 9999-12-31 for current records. Surrogate keys are always generated fresh in the target — never reuse source keys, as they may collide with the target’s sequence. I have seen migrations fail from this assumption. The transformation uses a merge pattern: match on natural business key, compare attributes, and either close the existing record and insert a new version, or skip if unchanged.”

Tools and Technology Questions

9. How have you used AWS DMS and SCT in a migration project? (AWS DMS)

Why they ask: AWS DMS is the most commonly used managed migration service. They want to know if you have configured it or just read about it.

How to answer: Be specific about replication instance sizing, table mappings, task settings, and limitations encountered.

Example answer: “I used DMS and SCT for Oracle 12c to Aurora PostgreSQL. SCT flagged 23 incompatible objects — mostly PL/SQL packages needing manual PL/pgSQL rewrites. For DMS, I configured a dms.r5.2xlarge replication instance with multi-AZ. Table mappings used selection rules for specific schemas and transformation rules to convert Oracle uppercase names to PostgreSQL lowercase. The biggest gotcha was LOB handling — full LOB mode was too slow, so I used limited LOB mode at 32KB and handled oversized LOBs with a separate Python script. CloudWatch logging from day one saved us when a unique constraint violation during CDC needed diagnosis.”

10. When would you choose Azure Data Factory over a custom Python pipeline? (Azure Data Factory)

Why they ask: Tool selection is an architectural decision. They want to see judgment, not brand loyalty.

How to answer: Frame it around team capability, maintenance burden, and transformation complexity.

Example answer: “ADF excels when the migration is connector-heavy — pulling from many SaaS APIs, databases, and file shares. I used it for multi-source consolidation into Azure Synapse with data from Dynamics 365, Salesforce, and on-premises SQL Server. Copy activities handled 80% of the work. For complex deduplication and fuzzy matching, I used ADF data flows and Azure Functions. Where I would not use ADF: single source-to-target with heavy transformation logic. A Python pipeline with pandas or PySpark gives you version-controlled code, unit tests, and debuggability that ADF’s visual interface cannot match.”

11. Describe a scenario where you used Informatica PowerCenter or a similar enterprise ETL tool. (Informatica)

Why they ask: Enterprise ETL tools are still dominant in large organizations. They want to know if you can operate in that ecosystem.

How to answer: Describe a specific workflow with enough detail to prove hands-on experience.

Example answer: “I built migration workflows for a legacy ERP-to-SAP migration using Informatica PowerCenter 10.2. The customer master data mapping had 14 transformations: source qualifier from DB2, expressions for field logic, a router splitting active/inactive customers, lookups resolving foreign keys against migrated reference tables, and an update strategy for upserts. Initial run: 9 hours for 12 million records. I tuned it by increasing DTM buffer to 2GB, enabling pushdown optimization, and partitioning into 8 parallel pipelines by region — down to 2.5 hours. Lesson: Informatica makes it easy to build something that works but hard to build something that performs.”

12. How would you use Python and SQL together for a migration that does not justify a full ETL tool? (SQL Scripting)

Why they ask: Not every migration needs Informatica. They want to know if you can build something lightweight and reliable.

How to answer: Describe Python for orchestration and complex transformations, SQL for bulk movement, and a control table for state management.

Example answer: “For a 50-table MySQL-to-PostgreSQL migration with nearly identical schemas, I wrote a Python script using SQLAlchemy and pandas:

import pandas as pd
from sqlalchemy import create_engine

source = create_engine('mysql+pymysql://user:pass@source-host/db')
target = create_engine('postgresql://user:pass@target-host/db')

for table in migration_manifest:
    df = pd.read_sql(f'SELECT * FROM {table}', source, chunksize=50000)
    for chunk in df:
        chunk = apply_transforms(chunk, table)
        chunk.to_sql(table, target, if_exists='append',
                     index=False, method='multi')

Each table had a YAML manifest entry specifying column renames, type casts, and custom transforms. A control table logged start/end time, row count, and status per table. Failed runs resumed from the last incomplete table. Three days to build versus three weeks for standing up an ETL tool.”

13. What is Change Data Capture and how have you implemented it? (CDC)

Why they ask: CDC is essential for any migration that cannot afford a long freeze period. It separates candidates who understand real-time synchronization from those who only know batch processing.

How to answer: Explain the mechanism types (log-based, trigger-based, timestamp-based), then describe a real implementation.

Example answer: “CDC captures row-level changes from the source and applies them to the target in near real-time. Log-based is the gold standard — it reads the transaction log without adding source load. I implemented it using Debezium with PostgreSQL logical replication, publishing change events to Kafka topics. A consumer applied changes via idempotent upserts. The hardest part was schema evolution: when the source team added a column mid-migration, the schema registry and consumer both needed updates to handle records with and without the new field. Trigger-based CDC is simpler but adds write overhead — I only use it for low-volume tables.”

14. How do you evaluate and select a migration tool for a new project? (Tool Selection)

Why they ask: Tool selection mistakes are expensive and hard to reverse. They want a structured evaluation process.

How to answer: Describe evaluation criteria weighted by project constraints, including team skills.

Example answer: “Five criteria. Source/target connectivity — native support or custom connectors? Transformation complexity — simple renames work anywhere, complex logic needs code-based ETL. Volume — for 500GB+, benchmark against the migration window. Operational fit — Informatica is great if you have the developers, a liability if not. Cost — Talend is free but needs Java; DMS is cheap per hour but adds up during extended CDC. For a recent Oracle-to-Aurora migration, I recommended DMS for bulk migration with custom Python for transformations DMS could not handle.”

Data Quality and Testing Questions

15. How do you profile source data before starting a migration? (Data Profiling)

Why they ask: Profiling is where you discover problems that will derail your migration if ignored. Skipping it is the most common mistake in failed projects.

How to answer: Describe column-level, table-level, and cross-table profiling activities. Mention tooling.

Example answer: “Three levels. Column-level: data type, min/max, null percentage, distinct count, pattern analysis for strings — catching things like phone numbers in 15 formats. Table-level: row counts, duplicate detection on unique columns, orphaned foreign keys. Cross-table: referential integrity across the entire schema. I use Great Expectations for automated profiling because it generates executable test suites from results. For a healthcare migration, profiling revealed 12% of patient records had null date-of-birth — a required target field. Caught in week one instead of UAT. The profiling report directly informs cleansing plans and transformation rules.”

16. What is your approach to data cleansing during a migration? (Data Cleansing)

Why they ask: Migration is often the only chance to clean up decades of data debt. They want to know if you treat it as an opportunity or just shovel dirty data into a new system.

How to answer: Separate cleansing into automated rules and manual review queues. Emphasize that cleansing decisions need business approval.

Example answer: “Three tiers. Tier one, automated and non-controversial: trimming whitespace, standardizing dates, normalizing case, removing exact duplicates. Tier two requires business rules: resolving near-duplicates, filling defaults for missing required fields, mapping deprecated codes. These get business sign-off before implementation. Tier three is manual review: records that fail automated cleansing go into an exception queue. On a financial services migration, tier three was 0.3% of records — 18,000 rows. We built a simple web interface for the business team rather than passing spreadsheets. Critical rule: never silently drop or modify records. Every action is logged and auditable.”

17. How do you perform reconciliation between source and target after migration? (Reconciliation)

Why they ask: Reconciliation is the evidence that your migration worked. Without it, you are asking stakeholders to trust you on faith.

How to answer: Cover quantitative and qualitative reconciliation, plus automation and reporting.

Example answer: “Three levels. Quantitative: row counts per table, numeric column sums, and hash-based key field comparison — any variance above 0% triggers investigation. Qualitative: 50-100 records per entity compared field by field, catching errors aggregates miss like a status mapping that skipped one code. Business validation: stakeholders run common reports against the target and confirm results. Reconciliation queries are built into the pipeline, running automatically after every batch.”

18. How do you ensure referential integrity is maintained after migration? (Integrity Checks)

Why they ask: Broken foreign key relationships cause application failures that may not surface until weeks after go-live.

How to answer: Cover load order, surrogate key remapping, and post-load verification.

Example answer: “Start with load order: build a dependency graph from foreign keys and topologically sort — parents first, then children. For changing surrogate keys, maintain a mapping table (source_key to target_key) per entity; child records resolve foreign keys through it before loading. After all tables load, query every FK constraint for orphaned records. On a retail migration with 200+ tables, we found 4,000 order line items pointing to deleted products — a pre-existing issue. Those went into an exception table; the business mapped them to a ‘discontinued product’ placeholder.”

19. How do you approach regression testing after a migration? (Regression Testing)

Why they ask: Migration is not done when the data lands. Applications that depend on that data need to work correctly.

How to answer: Describe data-layer, application-layer, and integration tests. Mention working with QA.

Example answer: “Three layers. Data layer: automated checks on counts, types, constraints, and business rules using Great Expectations or custom SQL. Application layer: work with QA to run the top 20 critical workflows — for e-commerce, that meant test orders, inventory reports, and customer history. Integration layer: run the existing API test suite against the new backend and compare payloads. The most common regression is performance degradation — a 200ms Oracle query might take 3 seconds on PostgreSQL due to plan differences. I benchmark the top 50 slowest queries as part of regression.”

Scenario and Behavioral Questions

20. Tell me about a migration that failed or went significantly wrong. What happened and what did you do? (Failed Migration Recovery)

Why they ask: Every experienced migration engineer has a war story. If you do not, they question your experience. They are evaluating incident response and learning ability.

How to answer: Be honest about what went wrong. Focus on recovery actions and process changes. Do not blame others.

Example answer: “On a CRM migration, we discovered during cutover that production had 3 million records with Unicode characters our staging dataset did not include — it had been ASCII-normalized. The ETL truncated multi-byte characters, corrupting 40,000 customer names. Checksum validation caught it. We rolled back from a pre-migration snapshot in 25 minutes. Fix: I mandated all future dress rehearsals use production-scale copies with full character sets, and added a pre-migration encoding compatibility check. The failure cost one weekend, but the process improvement probably prevented worse failures later.”

21. How do you communicate migration risks and timelines to non-technical stakeholders? (Stakeholder Communication)

Why they ask: Migration projects fail as often from organizational misalignment as from technical problems.

How to answer: Describe translating technical risk into business impact with specific artifacts.

Example answer: “Three artifacts. A risk register mapping technical risks to business impact — ‘CDC lag may exceed 5 seconds’ becomes ‘customers may see stale data briefly during transition.’ A weekly dashboard showing progress by entity type with red/amber/green status and reconciliation numbers. A go/no-go checklist with clear pass/fail criteria that the business signs off on. If there is a risk of delay, I communicate immediately with what happened, the impact, and what we are doing about it. Stakeholders can handle bad news. They cannot handle surprises.”

22. You discover that the source system has no documentation and the original developers are gone. How do you proceed? (Legacy System Analysis)

Why they ask: Undocumented legacy systems are the norm for migration projects. They want to see your reverse-engineering methodology.

How to answer: Describe a systematic approach: schema analysis, data profiling, code review, and stakeholder interviews.

Example answer: “I have done this twice — a mainframe COBOL system and a critical Access database. Extract and catalog the physical schema (parsing COBOL copybooks for the mainframe). Profile data for usage patterns: recent inserts, always-null columns, value frequencies. Trace the application layer — search source code for SQL queries mapping reads and writes. Interview business users: ‘when we create an order, it shows here’ maps operations to tables. Document everything in a living data dictionary validated against test scenarios on the source.”

23. How do you handle a situation where the migration deadline is unrealistic given the data quality issues you have discovered? (Timeline Pressure)

Why they ask: They want to know if you will quietly cut corners or raise the issue professionally.

How to answer: Present options, not ultimatums. Quantify the risk of each so the business can decide.

Example answer: “Three options. Reduce scope — migrate clean data now, defer problematic records. Extend by a specific amount — ‘three weeks for 18,000 exception records’ is defensible. Or proceed with known risk and a post-migration cleanup plan. On a financial services migration, option three meant 2.3% of accounts with incorrect addresses — about 1,200 returned mailings. The business chose reduced scope: clean records on schedule, remainder next month. ‘We need more time’ gets pushback. ‘18,000 records will fail, here is the impact, here are three paths’ gets a decision.”

24. Describe how you manage a migration team across different time zones and skill sets. (Team Coordination)

Why they ask: Large migrations involve DBAs, ETL developers, QA, and analysts, often distributed globally.

How to answer: Focus on communication structure, task independence, and dependency management.

Example answer: “I structure work around independence — each member or pair owns an entity group end to end, minimizing cross-timezone blocking. Async standups via Slack; one synchronous weekly meeting for cross-cutting concerns. For cutover, a detailed runbook with time-stamped tasks and owners, rehearsed two weeks prior. On a recent migration across New York, Hyderabad, and London, the runbook had 47 steps across 8 hours. The rehearsal revealed an undocumented dependency between steps 19 and 23 — exactly the kind of thing that causes 3 AM chaos during real cutover.”

25. A critical migration is scheduled for this weekend, and you discover a blocker on Thursday afternoon. What do you do? (Crisis Management)

Why they ask: Last-minute blockers are routine in migration work. They want to see your triage process.

How to answer: Walk through: assess severity, find workarounds, escalate, recommend proceed or postpone.

Example answer: “First, assess scope: does it block the entire migration or one entity group? If one table out of 50, I can defer it. If systemic — like CDC not keeping up with production writes — it blocks everything. Second, determine fastest resolution: fixable by Friday evening, or not? Third, escalate immediately to the project lead and sponsor with a clear summary and recommendation: proceed, postpone, or proceed-with-reduced-scope. Never wait hoping it resolves. On one project, we discovered Thursday that a third-party API rate limit prevented migrating a key dataset in the window. I proposed a direct database extract instead, validated it Friday morning, and we proceeded on schedule. Postponing would have cost two weeks and $150K.”

Questions to Ask the Interviewer

Strong candidates ask questions that reveal operational maturity. These five demonstrate real migration experience:

1. “What is the total data volume and how many source systems are involved?” — This sizes the problem. A 50GB single-source migration is a different beast from a 5TB multi-source consolidation.
2. “What is the downtime tolerance for the cutover, and has that been agreed upon with the business?” — An undefined downtime window is a red flag. If the business expects zero downtime but the plan assumes a 4-hour window, you are walking into a conflict.
3. “Has the source data been profiled, and are there known data quality issues?” — If no, the project is earlier-stage than described. If yes with specifics, the team is mature.
4. “What does the rollback plan look like, and has it been tested?” — This signals you take operational safety seriously.
5. “How is the migration team structured, and who owns the go/no-go decision?” — This reveals accountability and whether the migration engineer has authority or is just executing.

What Strong Candidates Demonstrate

Interviewers evaluate migration candidates across five dimensions:

Dimension	Weak Signal	Strong Signal
Technical Depth	Describes migration as “moving data from A to B.” Cannot explain ETL stages or schema mapping.	Discusses partition strategies, CDC implementation details, checksum validation, and index management during bulk loads.
Tool Proficiency	Lists tools on resume but cannot describe configuration details or limitations encountered.	Explains specific DMS task settings, Informatica session tuning, or Debezium connector configurations from real projects.
Risk Awareness	Mentions backups as the rollback strategy. No mention of dress rehearsals or go/no-go criteria.	Describes tested rollback plans, quantified go/no-go thresholds, and specific examples of risk mitigation.
Communication	Speaks only in technical terms. Cannot translate risk into business impact.	Naturally shifts between technical detail and business context. Uses concrete numbers to quantify impact.
Problem Ownership	Blames tools, teams, or timelines for failures. No mention of lessons learned.	Owns failures, describes specific recovery actions, and explains process changes implemented afterward.

Common mistakes that cost candidates offers:

• Speaking only in theory. Saying “I would use CDC” without describing how you configured it and what issues you hit.
• Ignoring data quality. Jumping to pipeline design without asking about source data profiling.
• No rollback story. If you have done real migrations, you have either executed a rollback or come close.
• Underselling communication skills. Purely technical answers with no stakeholder management suggest you cannot operate beyond the keyboard.
• Tool tunnel vision. Recommending one tool for every problem instead of matching tools to constraints.

Quick Prep Checklist

Use this checklist in the 48 hours before your interview:

• Prepare two migration war stories — one successful, one where something went wrong. Know the data volume, source/target systems, tools used, timeline, and your role.
• Review your tool stack. For every tool on your resume, be ready to describe a configuration decision, a limitation, and a performance optimization.
• Practice explaining a technical concept to a non-technical audience. Pick CDC, ETL, or rollback and explain it in two sentences without jargon.
• Know your numbers. Rows migrated? Downtime window? Full load duration? Replication lag? Concrete numbers build credibility.
• Refresh your SQL. Be ready to write a reconciliation query, checksum comparison, or integrity check on a whiteboard.
• Review the target company’s stack. If they mention AWS, review DMS and SCT. Azure: Data Factory and Synapse. Informatica: session tuning and mapping optimization.
• Prepare your interviewer questions. Asking about data volume, downtime tolerance, and rollback plans signals experience.