Katkat
Back

Design a Team SQL Workbench with Snapshots and Async Runs

AnthropicSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteSystem Design

Last updated

Design a multi-user SQL workbench where analysts co-edit drafts, freeze immutable snapshots, and launch long-running warehouse queries whose results stay traceable to their exact inputs. Tests concurrency control, immutability boundaries, and reliable async execution.

Your company wants a shared SQL workbench for its analytics teams. Analysts in a workspace co-author queries, take named snapshots of a query they trust, and execute a chosen snapshot against the data warehouse. Results must always be traceable to the exact SQL, parameters, and warehouse settings that produced them.

Functional requirements

  1. Shared drafts: several members of a workspace can edit the same query draft. They see who else is present and where their cursors are.
  2. Snapshots: any editor can freeze the current draft into an immutable, numbered snapshot. A snapshot contains the SQL text, parameter definitions, default parameter values, and execution settings (warehouse cluster, timeout, row limit).
  3. Runs: a user picks a snapshot, supplies parameter values, and launches a run. Runs execute asynchronously. They may take from seconds to 30 minutes, and users can close the tab and come back.
  4. Results: each run stores status, timing, row count, an error message if it failed, and a result preview of up to 10,000 rows, plus a link to the full export. Users can compare two runs side by side.
  5. Access control: workspace roles are viewer (read), editor (edit and snapshot), and runner (editor plus launching runs). Warehouse credentials are never visible to clients.

Non-functional requirements

  • 20,000 workspaces, 150,000 monthly active analysts, at most around 3,000 concurrent editors.
  • 400,000 runs per day, peaking at 30 runs/second.
  • An edit should appear to collaborators in under 300 ms (p95).
  • No lost edits after an acknowledgement. No duplicate warehouse executions from client retries.
  • A run's recorded inputs must never change after it starts.

Clarify before designing

  • Is character-level real-time co-editing required, or is "save with conflict detection" acceptable?
  • Can runs be cancelled? What should the UI show if the warehouse connection drops mid-run and the outcome is unknown?
  • How long are results retained, and can results be shared outside the workspace?

Deliver

  • A data model for drafts, snapshots, runs, and results.
  • The editing and sync protocol, including reconnection after being offline.
  • The run lifecycle: API, queueing, workers, idempotency, cancellation, and handling of uncertain outcomes.
  • A component diagram, the scaling approach, and the main trade-offs.

Hints

Hint 1

Separate three kinds of state: ephemeral presence, the mutable draft, and immutable snapshots and runs.

Hint 2

A run should never point at the draft. What exactly should it reference so that later edits can't change its meaning?

Hint 3

Client retries and worker crashes can both cause duplicate executions. Where do idempotency keys go, on both the API and the warehouse side?

Hint 4

What should the system show when the worker loses contact with the warehouse mid-run?

Follow-up questions

  • How would you support branching: two analysts experimenting on diverging versions of one query and later merging?
  • A run's snapshot references a table that has since been dropped. How do you present a re-run of it?
  • How would you add scheduled runs, for example every morning at 6am, without double-firing during a deploy?
  • How would you let a workspace share a read-only result link with an external stakeholder safely?