You must migrate a company from an on-premise warehouse to Snowflake or BigQuery without breaking hundreds of reports. How would you plan it?

What the interviewer wants to hear

• Inventory tables, pipelines, reports and usage to prioritise and drop dead assets
• Migrate in phases by domain rather than one big switch
• Run old and new in parallel and reconcile outputs automatically
• Translate SQL dialects and rethink design for the new platform
• Plan cutover, rollback and user communication; then decommission
• Set up cost controls and governance from the start

Model answer

I start with an inventory using query logs and lineage: which tables, jobs and reports exist and which are actually used. Usually a big share is dead and can be dropped instead of migrated. Then I migrate in phases by business domain, beginning with one that is valuable but not too risky. For each phase I load history, rebuild pipelines in the new platform, translating SQL dialect differences and taking the chance to fix bad patterns, and then run old and new in parallel. Automated reconciliation compares row counts, sums and key metrics per table and per day, and differences must be explained before cutover. I agree a cutover date with report owners, repoint dashboards, and keep the old system read-only for a rollback window before decommissioning it. From day one I set up role-based access, cost monitors and tagging, so the new platform does not become an expensive surprise.

More hard Data Engineer questions

• Design a data platform for a fast-growing startup with 20 data sources and a small team. What would you choose and why?
• Explain at-most-once, at-least-once and exactly-once processing, and how you achieve effectively-once results in practice.
• A user asks to delete all their personal data. Your data is spread across a data lake, warehouse and backups. How do you handle it?
• How do you choose partitioning and clustering for a very large table? What can go wrong?
• The CEO's revenue dashboard showed wrong numbers for two days before anyone noticed. How do you fix this and prevent it from happening again?
• Design a pipeline that ingests 200,000 clickstream events per second and powers both a real-time dashboard and daily analytics.
• Leadership asks you to cut the cloud warehouse bill by 40 percent without hurting the business. What do you do?
• A metric definition changed and you must backfill two years of a large fact table while the daily pipeline keeps running. How do you do it safely?
• Design a CDC pipeline that replicates a busy PostgreSQL database into Iceberg or Delta tables, including updates and deletes. What are the tricky parts?