How would you design an hourly sync job pipeline that writes to a golden layer used by BI tools, and how would you handle network outages that can cause failures?

Question

Assisting AI · Accepted Answer

The pipeline starts with incremental CDC from source tables, writes to a staging (silver) layer in a data lake, then transforms into the golden layer. To handle outages, we use retry logic with exponential back‑off, a dead‑letter queue for failed records, and a checkpointing mechanism so the job can resume from the last successful offset. We also schedule a nightly full refresh to catch any missed data. Monitoring alerts on job failures and data lag, and we expose metrics to a Grafana dashboard. This design ensures that BI tools like Power BI always read a consistent golden view while the system recovers gracefully from network issues.

How would you design an hourly sync job pipeline that writes to a golden layer used by BI tools, and how would you handle network outages that can cause failures?

💡 Model Answer

🎤 Get questions like this answered in real-time