Generating and Debugging SQL with AI

SQL is still the beating heart of data analyst work, and AI tools are remarkably good at writing it — when you give them the right context. This lesson walks through how to generate queries, debug broken ones, translate between dialects, and optimize slow queries using ChatGPT, Claude, and your IDE's built-in copilot.

What You'll Learn

• How to prompt AI to generate SQL that actually works on your warehouse
• A debugging workflow for when a query returns wrong results
• Translating queries between PostgreSQL, BigQuery, Snowflake, and Redshift
• How to ask AI to optimize a slow query without breaking its logic

The Minimum Viable Prompt for SQL Generation

Before you press enter on any SQL generation prompt, make sure it contains these six things:

1. Dialect. "BigQuery Standard SQL," "Snowflake," "PostgreSQL 15," "Redshift."
2. Table names and schemas. Paste the output of DESCRIBE TABLE or \d table_name.
3. A sample row or two. Five rows if there are nullable fields or weird types.
4. Business rules. How "active" is defined, what filters to apply.
5. The exact output shape. Columns, grain (per user? per day? per user-day?), sort order.
6. Style preferences. CTEs versus subqueries, lowercase keywords, trailing commas.

Here is a concrete template:

Dialect: Snowflake
Tables:
  analytics.orders (order_id STRING, customer_id STRING, order_date DATE, revenue NUMERIC(10,2), status STRING)
  analytics.customers (customer_id STRING, country STRING, segment STRING, signup_date DATE)

Sample orders rows:
  ord_001 | cust_9912 | 2026-03-14 | 128.50 | completed
  ord_002 | cust_9912 | 2026-03-15 | 42.00  | refunded

Business rules:
  - "Completed order" = status = 'completed'
  - Revenue is in USD already
  - Exclude customers with segment = 'internal_test'

Task:
  Return the top 10 countries by total completed revenue in Q1 2026 (Jan 1 – Mar 31).
  Columns: country, order_count, total_revenue, avg_order_value.
  Sort by total_revenue descending.

Style: CTEs, lowercase keywords, trailing commas, explanatory comment above each CTE.

A prompt like this produces a query that is almost always correct on the first try. The AI knows exactly what tables, what grain, and what output you want.

A Debugging Workflow That Works

Most analyst debugging prompts fail because the analyst just pastes the query and says "this is wrong." AI cannot read your warehouse, so it cannot tell you why.

Here is a better workflow:

Step 1 — State expected versus actual.

This query is supposed to return 1,847 rows (one per active customer last month) but it returns 3,211. Known answer: customer cust_8821 should appear exactly once.

Step 2 — Paste the query.

Step 3 — Paste the evidence.

Here are three sample rows from the output showing cust_8821 appearing three times. [Paste rows.]

Step 4 — Ask for a diagnosis, not a fix.

What is causing cust_8821 to appear three times? Do not rewrite the query yet; just tell me the root cause.

Once the AI names the bug ("your left join to orders fans out because customers can have multiple orders on the same day"), you can confirm the logic yourself and then ask for a fix. This two-step approach — diagnose, then fix — catches cases where the AI would have "fixed" the wrong thing.

Translating Between Dialects

This is one of the most reliable uses of AI for analysts. Example prompt:

Translate this BigQuery query to Snowflake. Preserve the exact logic. Call out any dialect-specific functions I need to be aware of (for example, date functions or array handling) in a comment at the top.

[Paste query.]

Common translation gotchas the AI will catch:

• DATE_TRUNC('month', date) (Postgres, Snowflake) vs DATE_TRUNC(date, MONTH) (BigQuery) vs TRUNC(date, 'MM') (Oracle)
• ARRAY_AGG is available everywhere but ordering syntax differs
• Snowflake's QUALIFY clause does not exist in Postgres — it becomes a subquery with ROW_NUMBER
• BigQuery's UNNEST vs Postgres unnest() vs Snowflake FLATTEN (each has slightly different semantics)

Always run the translated query on a small slice and compare output to the original.

Optimizing Slow Queries

Before asking AI to optimize, gather:

• The query itself
• The EXPLAIN or query plan output (every warehouse has one)
• Row counts of the key tables
• Indexes (Postgres, Redshift) or cluster keys (Snowflake, BigQuery) on the tables

Then prompt:

Here is a Snowflake query that takes 14 minutes. The orders table has 220M rows and is clustered on order_date. The customers table has 5M rows. The events table has 3B rows and is clustered on (user_id, event_date).

Here is the query: [paste]

Here is the query plan: [paste EXPLAIN output]

Suggest three optimizations ranked by expected speed-up. For each one, explain the mechanism and show the rewritten SQL. Do not change the output — the query must return the exact same rows and columns.

The AI will usually suggest partition pruning, pre-aggregation with CTEs, removing unnecessary joins, or using window functions to replace self-joins.

Window Functions, CTEs, and Advanced Patterns

AI is particularly strong at patterns that analysts often get wrong from memory:

Gap and island problems (detecting consecutive days of activity):

Write a BigQuery query that, for each user, finds their longest streak of consecutive days with at least one event. Use the classic gaps-and-islands pattern with ROW_NUMBER and date subtraction.

Funnel analysis:

Write a Snowflake query that returns conversion rates between these four steps in a signup funnel: visit, signup_started, email_confirmed, first_purchase. Each user may hit each step at most once. Use QUALIFY to pick the first occurrence.

Cohort retention:

Write a PostgreSQL query that returns week-over-week retention by signup cohort. Columns: cohort_week, week_offset, retained_users, retention_pct. Cohort is the week the user first signed up. Use DATE_TRUNC and a self-join.

Verifying AI-Generated SQL

A query that looks right and runs without errors is not necessarily correct. Always:

1. Run it on a small slice (WHERE order_date = '2026-03-14') and spot-check against a known number
2. Run a totals check: does the sum match the known revenue for that period?
3. Count distinct users — does it match SELECT COUNT(DISTINCT user_id) from the base table?
4. Eyeball the first 10 rows for obvious weirdness (negative values, duplicated IDs, nulls where they should not be)

Skipping verification is how analysts end up presenting wrong numbers. AI speeds up writing the query, not trusting the query.

Key Takeaways

• Good SQL prompts include dialect, schema, sample rows, business rules, output shape, and style
• Debug by stating expected vs actual and asking the AI to diagnose before it fixes
• Translation between dialects is reliable — call out gotchas like date functions
• For optimization, include the query plan and row counts
• Always verify AI-generated queries against a known value before shipping