Build a Large-Scale Data Migration Solution — System Design Interview Practice

Interview prompt

Design a large-scale migration platform that moves on-premises databases and petabytes of files to the cloud with low downtime, validated copies, and a reversible cutover.

• Inventory dependencies, classify data, and make each transfer chunk resumable with checksums and a durable manifest.
• Use change-data capture for databases, parallel object transfer for files, and a planned dual-write or freeze window for cutover.
• Validate row counts, checksums, schemas, permissions, and application reads before switching traffic.
• Explain throttling, rollback, encryption, source protection, auditability, and recovery from a partial migration.

Requirements and scale assumptions

• Discover source assets and dependencies, create a migration plan, and transfer database tables and file objects.
• Track chunk progress, lag, validation findings, retries, cutover readiness, and rollback state.
• Support encryption, access mapping, schema transformation, incremental sync, final cutover, and audit export.
• Keep the final database cutover under four hours and avoid blocking migration work on one large file or table.
• Transfer petabytes through partitioned workers with source-friendly bandwidth limits and backpressure.
• Make chunks, CDC positions, validation reports, and cutover steps idempotent and auditable.
• Pause safely on source errors, preserve checkpoints, and resume without duplicating or silently skipping data.
• Migrate multiple petabytes of files and tens of terabytes of relational data across a months-long program.
• Partition files by prefix and databases by table or key range; isolate large objects and high-write tables.
• Retain source manifests, CDC logs, checksums, validation evidence, and cutover decisions for replay and audit.

• Migration volume: Petabytes — Volume drives chunking, bandwidth limits, lifecycle storage, and validation strategy.
• Cutover downtime: <4 hours — The final switch is the only user-visible interruption; bulk copy runs beforehand.
• Durable boundary: Committed before async — Source data remains authoritative until validated destination state is promoted at cutover.
• Async boundary: At-least-once workers — Keep bulk transfer, validation, reconciliation, and reporting asynchronous and resumable.

Key entities

• SourcePartitionsourceId, partitionId, cursor, schemaVersion, watermark, status

  Replayable large scale data migration solution source evidence and ingestion cursor.

• SchemaVersiondatasetId, version, compatibility, owner, effectiveAt, status

  Governed large scale data migration solution contract used to validate producers and consumers.

• ProcessingRunrunId, inputWatermark, checkpoint, qualityStatus, codeVersion, status

  Checkpointed large scale data migration solution processing attempt with quality and lineage metadata.

• AnalyticalDatasetdatasetId, partition, watermark, schemaVersion, qualityStatus, location

  Curated large scale data migration solution serving partition with freshness and quality state.

Data flow

1. 1. Register sources and contractsThe large scale data migration solution catalog records owners, schemas, compatibility rules, retention, lineage, and partitioning before data is accepted.
2. 2. Ingest with backpressureConnectors checkpoint large scale data migration solution source cursors, validate schema and deduplication keys, and slow producers when downstream capacity is exhausted.
3. 3. Process event time with checkpointsStream or batch engines compute large scale data migration solution transformations using watermarks, late-data policy, state checkpoints, and deterministic code versions.
4. 4. Publish quality-gated datasetsOnly large scale data migration solution outputs that pass completeness, freshness, validity, and privacy checks become visible to analytical consumers.
5. 5. Serve, replay, and reconcileConsumers read bounded partitions with freshness metadata while operators replay failed large scale data migration solution ranges and compare output checksums.

Deep dives and trade-offs

• Schema evolution and data qualityVersion large scale data migration solution contracts and make compatibility rules explicit for every producer and consumer. Quarantine malformed partitions instead of poisoning the whole dataset. Track row counts, null rates, duplicates, distribution changes, and policy violations by partition.
• Watermarks, late data, and exactly-once effectsUse source cursors and event-time watermarks for large scale data migration solution progress, not wall-clock assumptions. Make checkpoints, output keys, and sink commits retry-safe under at-least-once delivery. Document how late events revise windows, aggregates, or snapshots.
• Replay, lineage, and costKeep immutable large scale data migration solution raw evidence and code or schema versions so failed outputs can be reproduced. Separate hot serving storage from cold retention and cap replay concurrency. Measure freshness, backlog, compute cost, storage growth, and quality-gate failure rate.
• Streaming versus batchUse streaming for freshness-critical large scale data migration solution paths and batch for backfills, compaction, and expensive recomputation. Forcing every workload into streaming makes state, replay, and cost harder to operate.
• Raw retention versus curated-only storageRetain enough immutable raw evidence for replay, audit, and correction, then tier or expire it according to policy. Without raw evidence, a bad transformation can require an unreproducible emergency fix.
• Central warehouse versus domain-owned datasetsCentralize governance and discovery while letting domain owners own contracts and quality signals. A single team owning every transformation becomes a delivery bottleneck and hides data ownership.