Problem RestatementProblem
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.
RequirementsRequirements
- 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.
ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
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 --> RSFlowFlows
- Submit:
POST /queries { sql, notebook_id, cell_id }→202 { query_id }. The job is saved asqueued. - 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).
- Status: for longer queries, the UI subscribes via SSE (server-sent events) to
/queries/{id}/eventsfor status and progress, with polling as a fallback (every 1–2 s, with backoff as time passes). - 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. - Download: a pre-signed URL to the chunk files (or a single exported file).
- Cancel:
POST /queries/{id}/cancel→ the engine stops the query and the status becomescancelled.
Deep Dive — A query that outlives the browser tabDeep dive
Cells run for milliseconds or for hours. Laptops sleep, tabs get closed, wifi drops. The query must not care.
Hold the query in the browser
The cell issues the query over a long-lived connection and renders results when they arrive.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
CELL["Run cell"] --> CONN["Long-lived HTTP connection"]
CONN --> WH["Warehouse - 40 minute query"]
LAPTOP["Laptop sleeps / tab closed"] --> DROP["Connection drops"]
DROP --> LOST["UI has no handle - cannot tell if it is still running"]
DROP --> RERUN["User re-runs - a second 40-minute query starts"]The only reference to the running query lives in a browser tab, which is the least durable component in the system. Losing it means the user cannot check on their query and, rationally, re-runs it — doubling the load on a warehouse that is already busy.
Poll a status endpoint
Submit the query, get an id back, and have the UI poll for status and results.
The query is now server-side and survives a dropped connection. What is still fragile is the id: it lives in page state, so a refresh loses it. The user comes back to a cell that looks idle while the query is still running, and re-runs it anyway.
Persist the query id on the cell
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
RUN["Run cell"] --> SUB["Submit - returns query_id"]
SUB --> SAVE["query_id stored on the notebook cell, server-side"]
SAVE --> POLL["UI polls status by query_id"]
REFRESH["Refresh / new device / next day"] --> READ["Read the cell's last query_id"]
READ --> POLL
POLL --> DONE["Results from the result store - retained ~24 h"]
RERUN["Re-run an identical query on unchanged data"] --> CACHE["Reuse cached results - no warehouse work"]- The cell owns the query id, not the page. Refreshing, switching device or opening the notebook the next morning all reconnect to the same running or finished query, because nothing important ever lived only in the browser.
- Results are stored and retained for a window — a day is the common choice — so viewing them again is a fetch, not a re-execution.
- A result cache keyed by query text and data version makes re-running an unchanged cell free, which is exactly what people do when reading a notebook someone shared.
Two protections that belong with it: cancel must be a real server-side operation on the query id, because a user who navigates away should be able to stop an expensive query; and results must be paginated or size-capped, since a SELECT * that returns fifty million rows will otherwise take the notebook service down rather than the warehouse.
Limits and Fairness
- Per-user and per-warehouse concurrency limits: excess queries wait in
queuedwith 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.
Wrap-UpWrap-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.