Analytical SQL from Natural Language: Draft, Verify, Optimize

Tier: Foundation · Workflow area: Analytical SQL

When to use

• •
  Drafting non-trivial analytical queries: multi-join aggregations, window functions, cohort/retention logic, funnel steps.
• •
  Translating a stakeholder's English question into SQL against a warehouse you partially know.
• •
  Optimizing a slow query someone else wrote.

Skip it for one-table lookups you can type faster than you can prompt.

Prerequisites

• •
  Read access to the warehouse (BigQuery, Snowflake, Databricks, Postgres) and permission to run EXPLAIN.
• •
  The real DDL for the tables involved (SHOW CREATE TABLE / information_schema), plus 3–5 sample rows per table.
• •
  An assistant: the warehouse's built-in copilot or a general model (Claude/GPT) in chat.
• •
  Knowledge of each table's grain — if you cannot state it, find out first; this is the top source of silently wrong SQL.

The workflow

1. 1.

   Build a context block once per schema area. Paste DDL, grain, and known traps:

   Copy

   Dialect: BigQuery standard SQL.
   Tables:
   - fct_orders (grain: one row per order ITEM, not order): order_id, item_id,
     customer_id, order_ts (UTC), status, gross_amount, is_deleted
   - dim_customers (grain: one row per customer): customer_id, country,
     acquired_channel, created_at
   Traps: is_deleted=true rows must be excluded; order_ts is UTC, business
   reports use Asia/Kolkata; gross_amount is in paise (divide by 100).
   

   Save this block; reuse it in every session. Quality of generated SQL tracks quality of this block almost linearly.

2. 2.

   Ask for the query plus its assumptions:

   Copy

   Question: monthly revenue and distinct purchasing customers by country for
   2025, top 10 countries by total revenue.
   Write the SQL, then list every assumption you made (deduplication, filters,
   timezone, currency, ties) as bullets I can veto.
   

   Read the assumptions before the SQL — vetoing a wrong assumption is faster than debugging its consequences.

3. 3.

   Run the verification pair before trusting results. For any aggregate query, generate two cheap checks:

   • •
     Reconciliation: total of the grouped result vs a direct ungrouped total (SELECT SUM(gross_amount)/100 FROM fct_orders WHERE ...) — must match to the rupee.
   • •
     Grain check: SELECT order_id, item_id, COUNT(*) ... GROUP BY 1,2 HAVING COUNT(*) > 1 on any table you assumed unique. Ask the assistant to write both; run them yourself.
4. 4.

   Eyeball edge rows. Pull 5 rows feeding one output cell (one country-month) and hand-verify inclusion, timezone bucketing, and the paise conversion.

5. 5.

   Optimize with EXPLAIN in the loop. Paste the plan, not vibes:

   Copy

   Here is the EXPLAIN output and the query. It scans 1.4TB. Propose rewrites
   in order of expected impact (partition pruning on order_ts, pre-aggregation,
   join reordering, approximate distinct where acceptable). For each, state the
   trade-off and whether results can change.
   

   Re-run EXPLAIN after each accepted rewrite; require "results unchanged" rewrites to be verified with the step-3 reconciliation.

6. 6.

   Comment and commit. Have the assistant add a header comment documenting question, grain, filters, and verification date. Uncommented generated SQL becomes unmaintainable folklore within a month.

Tool variants

• Warehouse-native copilot (Databricks Assistant, BigQuery Gemini, Snowflake Copilot): already schema-aware, so step 1 shrinks — but they do not know grain traps or unit conventions; still supply those, still run the verification pair.
• General chat model: full context block required; best when the query spans systems or needs explanation for review.
• IDE assistant over a dbt repo: point it at model files so it reuses existing logic (ref('fct_orders')) instead of re-deriving filters that dbt models already apply — double-applied filters are a classic bug here.

Verification

• •
  Reconciliation total matches an independently computed ungrouped total.
• •
  Grain/duplicate check passes on every table assumed unique.
• •
  One output cell hand-verified from raw rows, including timezone and unit conversion.
• •
  Post-optimization results are byte-identical (or differences explicitly accepted, e.g. APPROX_COUNT_DISTINCT).
• •
  Bytes scanned/cost reduced and recorded, if optimization was the goal.

Pitfalls

• Fan-out joins. Joining item-grain to order-grain silently multiplies revenue. The grain check in step 3 exists for this; run it every time.
• Plausible column invention. Models will happily use orders.revenue when the column is gross_amount. Real DDL in context prevents most of this; a dry-run/compile catches the rest.
• Dialect drift.DATE_TRUNC vs TRUNC vs date_trunc('month', ...) — always state the dialect and validate with a dry run before a full scan.
• Optimizing correctness away. Accepting a rewrite that swaps COUNT(DISTINCT ...) for an approximate version without flagging it to consumers.
• Stale context blocks. Schema evolves; a context block older than the last migration generates confidently obsolete SQL. Date-stamp the block.

Restricted-environment variant

• •
  Never paste row-level data into hosted assistants — DDL plus invented-but-realistic sample rows carry almost all the prompting value.
• •
  If even schema is sensitive, alias table/column names consistently (fct_orders → t1, mapping kept locally), or use the warehouse vendor's in-platform copilot, which operates under your existing data-processing agreement.
• •
  Fully air-gapped: a local model via Ollama drafts competent SQL from a good context block; the verification pair matters even more because local models invent columns more often.

Success criteria

• •
  Correct, verified SQL for a 3+ table analytical question in under 15 minutes.
• •
  Both verification queries run and passing before results were shared.
• •
  A measured cost/latency improvement on at least one real slow query, with identical results.
• •
  Your saved context block produces correct grain handling on the first generation for new questions in that schema area.