Katkat
Back

Design an Interactive Query Execution Notebook

SnowflakeSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenSystem Design

Last updated

Tests your ability to design an asynchronous, scalable system for executing interactive SQL queries and streaming execution state and tabular results back to web clients.

Scenario

You are asked to design an interactive SQL notebook platform (similar to Snowflake Worksheets or Databricks SQL notebooks). Users write SQL queries in their browser, click "Run", and expect real-time feedback including execution status (queued, running, compiling, finished, failed), runtime metrics, and the eventual tabular result set.

Queries range from sub-second exploratory lookups to long-running analytical queries lasting several minutes. The client interface must remain responsive and resilient to transient network drops.

Requirements

Functional Requirements

  • Query Submission & Execution: Users can submit arbitrary SQL queries against target database clusters.
  • Status & Progress Tracking: The frontend must receive near real-time updates on query state transitions (Queued -> Parsing/Validating -> Executing -> Fetching Results -> Completed/Failed).
  • Result Retrieval & Pagination: Users can view the first page of results quickly (e.g., first 500 rows) and scroll/paginate through larger result sets up to 100 MB.
  • Cancellation: Users can cancel an in-flight query at any point during execution.

Non-Functional Requirements

  • Low Latency: Submission acknowledgment within 100 ms. Result delivery latency overhead under 200 ms once database execution completes.
  • Fault Tolerance: If a client browser tab closes or disconnects, the query should continue running (or abort based on configuration) without leaking backend resources.
  • Scalability: Support 50,000 daily active users with a peak of 2,000 concurrently running queries.

Scale & Traffic Estimates

  • Query Volume: ~500,000 query executions per day; peak 50 queries/second submitted.
  • Result Size: Median result size is 50 KB (a few hundred rows), 95th percentile is 10 MB, max single result preview supported in UI is 100 MB.

Key Design Aspects to Cover

  1. Client-Server Communication Protocol: Compare and justify your choice between HTTP Long Polling, Server-Sent Events (SSE), and WebSockets for status updates and result delivery.
  2. API & Data Model: Define key endpoints for submission, status monitoring, cancellation, and result fetching, along with data structures tracking query execution state.
  3. System Architecture: Detail the query broker/orchestrator, execution worker pool, and storage strategy for ephemeral result sets.
  4. Handling Large Results: How the system prevents memory exhaustion in the gateway/API layer when handling high-cardinality result sets.

Hints

Hint 1

Consider decoupling the query execution lifecycle from the client connection lifecycle so client network drops do not crash workers or lose results.

Hint 2

Evaluate whether bidirectional communication (WebSockets) is strictly necessary, or if unidirectional streaming (Server-Sent Events) paired with standard REST calls for actions like cancellation is simpler and more resilient.

Hint 3

Avoid streaming multi-megabyte result sets directly through the same channel used for query status; consider storing results in object storage or an ephemeral cache and providing chunked pagination or presigned URLs.

Follow-up questions

  • How would you handle query cancellation reliably when the query has already been dispatched to a third-party or remote warehouse engine?
  • If a user closes their browser and opens it 10 minutes later, how does your system allow them to recover the execution history and result set of previously submitted queries?
  • How would you implement fair scheduling to prevent a few users submitting massive batch queries from starving interactive queries from other users?