Interactive SQL Query Notebook (Snowflake)

0) Problem Restatement

Snowflake asked: design a notebook-like service where users type SQL queries in cells, run them against a data warehouse, and see results. Queries may take milliseconds or hours, and results can be a few rows or millions. The focus: how the client learns the job status and gets results, including long queries, big results, cancellation and a page refresh in the middle.

1) Requirements

• Submit a query from a cell. See status (queued, running, progress, done, failed).
• View results in pages, sort and download.
• Cancel a running query.
• Refresh the browser or reopen the notebook later and still see the running or finished results.
• Many users running queries concurrently, with fair limits.

2) Architecture

flowchart LR
    NB["Notebook UI"] -->|"submit"| API["Query API"]
    API --> JDB[("Query jobs DB")]
    API --> SCH["Scheduler - per-user/warehouse limits"]
    SCH --> ENG["Query engine / warehouse"]
    ENG -->|"progress"| JDB
    ENG -->|"results"| RS[("Result store - chunked files")]
    NB -->|"status: SSE / polling"| API
    NB -->|"fetch pages"| API
    API --> RS

3) Flow

1. Submit: POST /queries { sql, notebook_id, cell_id } → 202 { query_id }. The job is saved as queued.
2. Short queries fast path: the API waits up to ~1–2 seconds. If the query finishes, it returns the first page of results directly (no second round trip for most interactive queries).
3. Status: for longer queries, the UI subscribes via SSE (server-sent events) to /queries/{id}/events for status and progress, with polling as a fallback (every 1–2 s, with backoff as time passes).
4. Results: the engine writes results in chunks (e.g., 10 MB compressed files) to the result store, along with the schema and row counts. GET /queries/{id}/results?page=N (or a cursor) reads the right chunk. The first page shows immediately, and the rest load when the user scrolls.
5. Download: a pre-signed URL to the chunk files (or a single exported file).
6. Cancel: POST /queries/{id}/cancel → the engine stops the query and the status becomes cancelled.

4) Reconnecting and State

• The query ID is stored on the notebook cell. After a refresh, the UI reads the cell's last query_id, and resumes status or results from the server. Nothing lives only in the browser.
• Results are kept for, e.g., 24 hours (same as Snowflake's result cache), and re-running the same query on unchanged data can reuse cached results.

5) Limits and Fairness

• Per-user and per-warehouse concurrency limits: excess queries wait in queued with a visible position.
• Timeouts (e.g., max 2 hours by default) and max result size (bigger results must be exported).
• Cost controls: show estimated cost or scanned bytes, and warn on huge scans.

6) Wrap-Up

Treat each cell execution as an async query job: submit returns a query ID (with a short synchronous wait so fast queries return inline), status flows over SSE with polling fallback, and results are written in chunks to a result store and fetched page by page or downloaded via pre-signed URLs. Store query IDs on cells so refreshes resume, support cancellation and timeouts, cache results, and enforce per-user concurrency limits with visible queuing.