Build a SQL / Data Analyst Copilot

What you'll build

An internal copilot where analysts ask data questions in plain English, review generated SQL, run it safely against a read-only warehouse, and get answers with charts — without filing a ticket.

The safety model

Question → Generate SQL → Validate → Execute (read-only) → Explain
                ↑              ↑
           schema RAG      sqlglot + allowlist

Non-negotiable: the database role can SELECT and nothing else. Validation runs before execution, not after.

Step 1 — Schema catalog

Export metadata your analysts already trust:

tables:
  - name: analytics.fct_orders
    description: One row per completed order. Partitioned by order_date.
    columns:
      - name: order_id
        type: STRING
        description: Primary key
      - name: mrr_usd
        type: FLOAT
        description: Monthly recurring revenue attributed to this order

Pair with metric definitions:

metrics:
  - name: net_revenue
    sql: "SUM(mrr_usd) - SUM(refund_usd)"
    tables: [analytics.fct_orders]

Refresh nightly. Stale schemas produce confident wrong SQL.

Step 2 — Few-shot retrieval

Embed your library of approved queries. When a user asks "monthly churn by plan," retrieve:

• churn_cohort_monthly.sql
• subscription_cancellations_by_tier.sql

Few-shots teach join paths the schema alone doesn't convey. See Embeddings Explained and RAG Explained.

Step 3 — Generate SQL

Retrieve only relevant tables — embedding search over table descriptions, not the full warehouse.

The model outputs:

• sql — the query
• explanation — what it does
• assumptions — "assuming churn = cancelled within 30 days"
• confidence — flag low-confidence for human review

Show SQL in the UI before running. Let analysts edit inline.

Step 4 — Validation gate

Never skip this layer:

Rejected queries return a clear error — "table raw_events not in allowlist" — not a warehouse exception.

Step 5 — Execute read-only

-- DB role: analyst_copilot_ro
GRANT SELECT ON SCHEMA analytics TO analyst_copilot_ro;
-- No INSERT, UPDATE, DELETE, CREATE, DROP. Ever.

Run against a replica when possible. Log: user_id, question, sql, row_count, duration_ms, thumbs.

Step 6 — Insight + chart

Profile the result:

• Row count, null rates, numeric ranges
• Sample 10 rows for the insight model

The model returns a headline ("Enterprise churn spiked to 4.2% in May"), chart spec, and caveats.

Render with Streamlit, Plotly, or your internal chart library.

Context engineering matters

The quality ceiling is retrieval, not model size:

• Table descriptions beat raw information_schema dumps.
• Metric definitions prevent "revenue" meaning three different things.
• Approved query few-shots teach join paths.

See Context Engineering Explained for the broader pattern.

MCP alternative

If your team lives in Claude Desktop or Cursor, wrap this as MCP tools:

• list_tables — searchable schema
• generate_sql — question → validated SQL
• run_query — read-only execution

One implementation, multiple clients. Start with the MCP server from your API workflow and point tools at your warehouse API layer.

Rollout plan

1. Week 1 — 3 analysts, 10 approved tables, manual schema.
2. Week 2 — Add validation metrics, thumbs up/down.
3. Week 4 — Expand allowlist if run-without-edit > 60%.
4. Week 8 — Open to PMs with narrower table scope.

Extensions

• Query explain — "why is this slow?" with EXPLAIN output interpretation.
• Semantic layer — dbt metrics as the source of truth instead of raw tables.
• Scheduled questions — "email me MRR every Monday" from saved queries.
• Anomaly mode — compare this week's result to trailing 4-week average automatically.

The copilot doesn't replace analysts — it absorbs the repetitive 60% so they focus on the questions that need judgment.