What was the biggest challenge you faced and solved during a migration?

During a migration of a legacy on‑premises data warehouse to Snowflake, the biggest challenge was ensuring zero data loss while the source system remained operational. The source database was a high‑traffic Oracle instance, and we needed to keep it online for business continuity. I led a phased approach: first, I implemented a CDC (Change Data Capture) pipeline using Oracle GoldenGate to stream changes to a Kafka cluster. Next, I built a Spark job on EMR that consumed Kafka topics, performed incremental transformations, and loaded data into Snowflake using the Snowpipe feature. To validate data integrity, I wrote a set of SQL scripts that compared row counts and checksums between source and target tables after each batch. When discrepancies were detected, the pipeline automatically rolled back the affected batch and logged the issue for manual review. The result was a smooth migration with no downtime, and the new Snowflake environment provided faster query performance and lower operational costs.