Databricks Genie Without the UI

Three Conversation API calls, a streaming trick that halves perceive latency, and a fallback for restricted-egress Databricks Apps.

Genie spaces give business users a natural-language interface over Unity Catalog. The UI handles SQL generation, follow-up turns, chart selection, and CSV download. The moment you need that experience inside your own app, you only have the API.

The Conversation API is small. Three SDK calls cover almost every flow. The rest of this post walks the calls, two perception-latency tricks, the path from a Genie attachment to any plotting library, and what to do when the result outgrows app memory.

The Three Calls

from databricks.sdk import WorkspaceClient
w = WorkspaceClient()

# 1. First turn
msg = w.genie.start_conversation_and_wait(
    space_id=SPACE_ID,
    content="Top 5 positions by notional",
)

# 2. Follow-up. Genie keeps schema + intent across the conversation.
follow = w.genie.create_message_and_wait(
    space_id=SPACE_ID,
    conversation_id=msg.conversation_id,
    content="Filter to the Rates desk only",
)

# 3. Fetch the cached result Genie already ran
qa = next(a for a in msg.attachments if a.query)
result = w.genie.get_message_attachment_query_result(
    space_id=SPACE_ID,
    conversation_id=msg.conversation_id,
    message_id=msg.message_id,
    attachment_id=qa.attachment_id,
).statement_response

qa.query.query is the generated SQL. qa.query.description is a one-sentence summary. result.manifest.schema and result.result.data_array carry the rows [1].

The warehouse Genie used lives on the space (w.genie.get_space(space_id).warehouse_id). Reuse it when you rerun edited SQL and you keep Unity Catalog governance, row-level filters, and audit trail [2].

Stream the SQL while the warehouse runs

qa.query.query and qa.query.description are populated before the message reaches COMPLETED. While Genie's warehouse is still executing the SQL, the poll loop already returns a message with the SQL attached and status == "IN_PROGRESS". Surface that SQL the moment it appears and the user reads it during the 2 to 10 seconds the warehouse needs to finish. Perceived latency drops by roughly half.

while time.time() < deadline:
    msg = w.genie.get_message(...)
    qa = next((a for a in (msg.attachments or [])
               if a.query and a.query.query), None)
    if qa and not rendered_sql:
        # Render the SQL now. Do not wait for COMPLETED.
        sql_placeholder.code(qa.query.query, language="sql")
        rendered_sql = True
    if msg.status in TERMINAL_STATES:
        break
    time.sleep(poll_interval)

Pair this with adaptive polling (0.3 s for the first 5 s, then 1.5 s) and a live elapsed counter and the wait stops feeling like a hang. Genie’s status field walks through IN_PROGRESS, EXECUTING_QUERY, and FETCHING_METADATA. Map each to a one-line label and the user sees a progress narrative, not a spinner.

From rows to chart: a library-agnostic pattern

The attachment gives you a typed result schema and rows. That is everything any plotting library needs. Convert once, render with whatever your team already uses. This is the pattern TopGenie uses with Plotly:

import pandas as pd
import plotly.express as px

# 1. Coerce Genie's string rows using the schema types
def result_to_df(sr):
    cols = sr.manifest.schema.columns
    df = pd.DataFrame(sr.result.data_array or [], columns=[c.name for c in cols])
    for c in cols:
        t = c.type_name.value.upper()
        if t in {"INT", "BIGINT", "LONG", "FLOAT", "DOUBLE", "DECIMAL"}:
            df[c.name] = pd.to_numeric(df[c.name], errors="coerce")
        elif t in {"TIMESTAMP", "TIMESTAMP_NTZ", "DATE"}:
            df[c.name] = pd.to_datetime(df[c.name], errors="coerce")
    return df

df = result_to_df(result)

# 2. Pick a chart from the schema
num = [c for c in df.columns if pd.api.types.is_numeric_dtype(df[c])]
tcols = [c for c in df.columns if pd.api.types.is_datetime64_any_dtype(df[c])]

if tcols and num:
    fig = px.line(df, x=tcols[0], y=num[0], markers=True)
elif num and len(df.columns) >= 2:
    cat = next(c for c in df.columns if c not in num)
    fig = px.bar(df.sort_values(num[0], ascending=False),
                 x=cat, y=num[0], color=num[0])
else:
    fig = px.scatter(df, x=df.columns[0], y=num[0]) if num else None

Two steps: (1) typed DataFrame from manifest.schema and result.data_array, (2) a chart picker driven by column types.

Step 2 is library-agnostic. Swap px.line for altair.Chart(df).mark_line(), matplotlib.pyplot.plot, bokeh.plotting.figure, or a hand-built Vega-Lite spec. The inputs are the same DataFrame and the same column-type heuristics. The Genie attachment exposes the only contract a renderer needs: a schema with types, and rows that match it.

Scaling beyond the inline result

The default INLINE disposition caps at ~25 MB per chunk and materializes rows in app memory as a DataFrame. Databricks Apps instances have around 2 GB RAM[3], and Streamlit re-runs the script on every interaction. A question like "show me all 2024 trades" will OOM the app. Three workarounds, in order of effort:

1. Cap the SQL

Add “default to top 100 unless asked” to the Genie space’s instructions. Genie reads space instructions when generating SQL and will push LIMIT or aggregation automatically. Cheapest fix, covers most ad-hoc questions.

2. Switch the disposition to EXTERNAL_LINKS

Re-execute Genie’s SQL via the Statement Execution API with results written to cloud storage and returned as presigned URLs[4]. No inline cap, no in-memory blow-up.

from databricks.sdk.service.sql import Disposition, Format

resp = w.statement_execution.execute_statement(
    statement=qa.query.query,
    warehouse_id=warehouse_id,
    disposition=Disposition.EXTERNAL_LINKS,
    format=Format.ARROW_STREAM,
    wait_timeout="50s",
)
# resp.result.external_links[*].external_link is a presigned URL per chunk.
# Stream each with pyarrow.ipc.open_stream and concat into one DataFrame.

On Databricks Apps with restricted egress, the app container often cannot reach the presigned cloud-storage URLs (URLError: Connection refused). The fix is a hybrid: try EXTERNAL_LINKS + ARROW_STREAM first, and on URLError or OSError, fall back to INLINE + JSON_ARRAY and page through chunks via get_statement_result_chunk_n to stay on the Databricks control plane. Same final DataFrame, no cloud-storage dependency.

3. Use Genie’s native download endpoint

w.genie.generate_download_full_query_result(...) followed by get_download_full_query_result(...) produces a downloadable artifact server-side. Stream it to the user without ever materializing in the app process. Best when the user just wants the CSV file rather than an in-app render.

A working reference: TopGenie

TopGenie is a single-file Streamlit app on Databricks Apps that wraps these three calls. It surfaces the generated SQL as editable text in an Ace editor, reruns edits on the same warehouse, picks a default chart from the result schema, and exports CSV. The repo ships the patterns above as production code: streamed SQL during polling, live elapsed timer with stage labels, optimistic user-message echo, per-turn truncation banner with one-click Re-fetch with current cap or Load full via External Links, and the EXTERNAL_LINKS to paginated-inline fallback for restricted-egress workspaces.