Preparing your design workspace

Loading the problem and your latest saved attempt…

Design a Daily Slowest-Query Report for a Data Warehouse

Report the slowest query of each fixed UTC day for a large data warehouse and each of its projects, with late records handled correctly.

Problem statement

For this exercise, assume a large cloud data warehouse, such as BigQuery, that runs SQL queries for millions of customer projects. When a query finishes, the warehouse emits one completion record with the query ID, project, start and finish times, and final state.

Design a system that reports the slowest query of each day: the single slowest query across the whole warehouse, and the slowest query of each project. A day is a fixed calendar window in UTC, from midnight to midnight, not a sliding 24-hour window, and a query belongs to the day in which it finished.

Show today's answer so far, publish each day's answer shortly after the day ends, and keep past days readable. Running the queries, alerting, and dashboard design are outside scope.

What to build

  1. For each UTC calendar day, report the slowest finished query in the whole warehouse and the slowest finished query of each project, with its query ID, project, duration, and start and finish times; equal durations are broken by the earlier finish time, then the smaller query ID.
  2. Today's answer so far is readable and reflects new completions within about a minute; a day's answer becomes final shortly after the day ends, and a late record that changes a final answer produces a visible, versioned correction.
  3. A reader can fetch the answer for any project or the whole warehouse for any day in the past 400 days, and sees whether it is provisional or final.

At scale

  • For this exercise, assume about 2 billion finished queries per day, peaking at about 100,000 completion records per second, spread over about 10 million projects, of which about 1 million run a query on a given day.
  • For this exercise, assume completion records can arrive late or twice: 99.9% arrive within 10 minutes of the query finishing, and the rest within 6 hours.
  • For this exercise, assume a day's answer must be published within 15 minutes after the day ends, today's answer must lag by at most about 1 minute, and results are kept for 400 days.