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?

What the interviewer wants to hear

• Make the job parameterised by date and idempotent per partition
• Build into a separate table or version and validate before swapping
• Run in controlled batches with concurrency limits to protect cost and sources
• Avoid conflicts with the live daily job, then switch atomically
• Reconcile results and communicate the changed history to users
• Keep a rollback path such as time travel or the old table

Model answer

First I make sure the transformation takes a date range as a parameter and overwrites whole partitions, so any chunk can be rerun safely. Rather than rewriting the live table in place, I build the new history into a shadow table, or a new version of the model, so dashboards keep working during the backfill. I run it in batches, for example a month at a time, with an Airflow backfill or a dedicated job, limiting concurrency so I do not overload source systems or blow the compute budget. Meanwhile the daily job writes to both tables, or I simply finish by reprocessing the final few days. Before switching, I compare old and new totals per month and explain the expected differences. Then I swap the tables atomically with a rename or view change, keep the old version for rollback, and tell analysts that history changed and why.

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.
• You must migrate a company from an on-premise warehouse to Snowflake or BigQuery without breaking hundreds of reports. How would you plan it?
• Leadership asks you to cut the cloud warehouse bill by 40 percent without hurting the business. What do you do?
• Design a CDC pipeline that replicates a busy PostgreSQL database into Iceberg or Delta tables, including updates and deletes. What are the tricky parts?