AI for Data Analysis & Decision Making · Writing formulas, SQL and Python with AI, and verifying them · lesson 8 of 16 · 11 min

SQL with AI: from question to query

Text-to-SQL in practice

AI assistants can translate a business question into SQL. This opens databases to people who are not SQL experts, and speeds up experts. But SQL has a dangerous property: a wrong query usually still runs and returns plausible numbers. Verification is essential.

Give the model the schema

The model cannot guess your tables and columns reliably. Provide:

table names, columns, types and short descriptions
how tables join (keys)
business definitions (what "active" means, which statuses count as "completed")
the SQL dialect (different databases have different date functions and syntax)

Dialect: PostgreSQL
Tables:
  orders(order_id PK, customer_id FK, created_at timestamptz,
         status text  -- 'completed','refunded','canceled',
         total_amount numeric  -- in AED, includes VAT)
  customers(customer_id PK, country text, signup_date date)
Definitions: revenue = completed orders only, excluding VAT (VAT rate 5%).
Question: monthly revenue by customer country for 2026, excluding VAT.
Return the SQL and explain each part.

Reviewing the query: a checklist

Read the generated SQL with these questions:

Filters: are the right statuses and date ranges applied? Is the end date inclusive or exclusive?
Joins: is the join type right (inner vs left)? Could a join multiply rows (one-to-many) and inflate sums?
Grain: what does one row of the result represent? Does the GROUP BY match?
Aggregation: SUM vs COUNT vs COUNT(DISTINCT); averages of averages.
Nulls: how are missing values treated in filters and aggregates?
Definitions: does it apply your business rules (VAT, refunds, time zones)?

The join fan-out trap

The most common silent error: joining orders to a table with multiple rows per order (for example order_items or payments) and then summing order totals. Each order's total gets counted once per matching row.

-- WRONG: order total repeated for every item row
SELECT SUM(o.total_amount)
FROM orders o JOIN order_items i ON i.order_id = o.order_id;

-- RIGHT: aggregate at the correct grain
SELECT SUM(o.total_amount)
FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.order_id);

Ask the AI directly: "Could any join in this query duplicate rows? Check the cardinality of each join."

Verification techniques

Row counts at each step: run the query in stages and check counts.
Reconcile to a trusted number (a finance report, a dashboard total).
Spot-check a few records manually (pick one customer and trace their orders).
Sanity bounds: is monthly revenue in the expected range? Are percentages between 0 and 100?
Second method: compute a key number another way (a different query shape, or a spreadsheet on an export).

Safety and access

Use read-only credentials for AI-assisted querying. An assistant should never be able to run UPDATE or DELETE on production data by accident.
Limit access to the tables needed, and mask sensitive columns.
Beware expensive queries on large tables: add limits and date filters while exploring.
If you build a text-to-SQL feature for others, add query validation, allow-lists of tables and cost limits.

Worked example

An analyst asks for "average order value by country". The AI's query averages the total_amount after joining to payments, where some orders have two payments (split payments). The average is distorted. The analyst's row-count check reveals more rows than orders. After fixing the join, the result matches the finance dashboard within rounding.

Time zones and date boundaries

A subtle but common issue: timestamps stored in UTC while the business reports in local time (for example Gulf Standard Time or UK time). Orders placed late in the evening can land in the wrong day or month. Tell the AI which time zone reports use, and check month-end totals against finance figures.

Hands-on: a schema card and a query review in practice

Give the model a compact schema card, then review what comes back:

DIALECT: BigQuery Standard SQL
TABLES
  sales.orders(order_id STRING PK, customer_id STRING, created_at TIMESTAMP (UTC),
               status STRING -- completed | refunded | canceled,
               total_aed NUMERIC -- includes 5% VAT)
  sales.order_items(order_id STRING FK, sku STRING, qty INT64, unit_price_aed NUMERIC)
  crm.customers(customer_id STRING PK, country STRING, signup_date DATE)
JOINS  orders.customer_id = customers.customer_id (many-to-one)
       order_items.order_id = orders.order_id (many-to-one; DO NOT sum order totals after joining items)
RULES  revenue = completed orders only, excluding VAT (total_aed / 1.05); months in Asia/Dubai time

A generated query to review:

SELECT
  FORMAT_TIMESTAMP('%Y-%m', o.created_at, 'Asia/Dubai') AS month,
  c.country,
  COUNT(DISTINCT o.order_id)                        AS orders,
  ROUND(SUM(o.total_aed) / 1.05, 2)                 AS revenue_ex_vat
FROM sales.orders AS o
LEFT JOIN crm.customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
  AND o.created_at >= TIMESTAMP('2026-01-01', 'Asia/Dubai')
  AND o.created_at <  TIMESTAMP('2027-01-01', 'Asia/Dubai')
GROUP BY month, c.country
ORDER BY month, revenue_ex_vat DESC;

Review notes: status filter correct; end date exclusive; time zone applied both in the filter and the grouping; the LEFT JOIN keeps orders with unknown customers (their country shows as NULL, which you should count, not hide); no item-level join, so no fan-out.

A row-count check you can paste after any join

SELECT
  (SELECT COUNT(*) FROM sales.orders WHERE status = 'completed')              AS orders_rows,
  (SELECT COUNT(*) FROM sales.orders o JOIN crm.customers c USING (customer_id)
   WHERE o.status = 'completed')                                              AS joined_rows;
-- joined_rows > orders_rows means duplicate customer rows (fan-out); fewer means unmatched orders

Second worked example: a Pakistani telecom reseller

An analyst asks for "active subscribers by city". The AI's query counts rows in a subscriptions table that has one row per plan change, so subscribers who changed plans are counted several times. The row-count check (rows versus distinct subscriber IDs) exposes it. The fixed query takes each subscriber's latest row with a window function (ROW_NUMBER() OVER (PARTITION BY subscriber_id ORDER BY changed_at DESC) = 1) before counting.

Going further

Build a small library of verified queries for core metrics, with definitions in comments, and give it to the AI as examples. Models produce much better SQL when they can see how your organization already calculates key metrics correctly.

Video lecture: SQL with AI: from question to query

Lecture coming soon · 15 chapters · about 8 minutes. Read the full transcript below.

SQL with AI
Why it matters
Give the schema
Six-point review
Join fan-out
Verify
Example 1: split payments
Example 2: telecom subscribers
Watch me do it, part 1
Watch me do it, part 2
Safe access
A verified query library
Common mistakes
Recap
Try this now

Lecture transcript

SQL with AI

SQL has a dangerous property. A wrong query usually still runs, and it returns numbers that look perfectly plausible. AI assistants can now translate business questions into SQL in seconds, which opens databases to far more people, and speeds up experts. But speed plus plausible wrong answers is a risky combination. In this lecture you'll learn to give models the schema they need, a six-point review checklist, the join fan-out trap, verification techniques, time zone gotchas, and safe access patterns.

Why it matters

Why does this matter? Because SQL results often feed the most important numbers in a business: revenue, active customers, churn. A join that silently doubles rows, or a filter that includes canceled orders, can move a board-level number by double digits without anyone noticing. The model can't see your data's quirks unless you tell it. And reviewing SQL is a learnable skill, even if you never write it from scratch yourself.

Give the schema

Here's the core idea: give the model the schema. It can't reliably guess your tables and columns. Provide table names, columns, types and short descriptions. How tables join, and whether each join is one-to-one or one-to-many. Business definitions, like what counts as active, or which statuses mean completed. And the SQL dialect, because date functions and syntax differ between databases. Think of it like giving a taxi driver the full address, not just the neighborhood.

Six-point review

Now the six-point review. Filters: right statuses and date ranges, and is the end date inclusive or exclusive? Joins: the right type, inner or left, and could any join multiply rows? Grain: what does one row of the result represent, and does the group by match? Aggregation: sum, count or count distinct, and no averages of averages. Nulls: how missing values are treated in filters and aggregates. And definitions: does it apply your business rules, like VAT, refunds and time zones?

Join fan-out

The most common silent error is join fan-out. You join orders to a table with several rows per order, like order items or payments, and then sum order totals. Each order's total gets counted once per matching row. Think of a restaurant bill split three ways, where the waiter charges the full bill to each person. The fix is to aggregate at the correct grain, or to check existence without joining. And ask the AI directly: could any join in this query duplicate rows? Check the cardinality of each join.

Verify

Verification techniques. Check row counts at each step, running the query in stages. Reconcile to a trusted number, like a finance report or a dashboard total. Spot-check a few records by tracing one customer's orders. Check sanity bounds: is monthly revenue in the expected range, are percentages between zero and a hundred? And compute a key number a second way, with a different query shape or a spreadsheet on an export. And one special trap: time zones. Timestamps in UTC while the business reports in Gulf or UK time can move late-evening orders into the wrong day or month.

Example 1: split payments

First example, a simple one. An analyst asks for average order value by country. The AI's query averages the order total after joining to payments, where some orders have two payments because customers split them. The average is distorted. The analyst's row-count check shows more rows than orders. After fixing the join, the result matches the finance dashboard within rounding. One check, one minute, one embarrassing number avoided.

Example 2: telecom subscribers

Second example, a business case. A Pakistani telecom reseller wants active subscribers by city. The AI's query counts rows in a subscriptions table that has one row per plan change, so anyone who changed plans is counted several times. The analyst compares rows with distinct subscriber ids, and the gap exposes the problem. The fixed query uses a window function to keep each subscriber's latest row before counting. Active subscribers drop to a realistic number that matches billing.

Watch me do it, part 1

Watch me write a schema card. Dialect: BigQuery standard SQL. Tables: orders with order id, customer id, created at in UTC, status as completed, refunded or canceled, and total in dirhams including five percent VAT. Order items with order id, sku, quantity and unit price. Customers with customer id, country and signup date. Joins: orders to customers is many-to-one; items to orders is many-to-one, and a warning not to sum order totals after joining items. Rules: revenue means completed orders only, excluding VAT, with months in Dubai time.

Watch me do it, part 2

The assistant returns a query. I review it with my six points. Filters: status is completed, good; the end date uses less than the first of January, so it's exclusive, good. Time zone: applied in both the filter and the month grouping, good. Joins: a left join to customers, which keeps orders with unknown customers, and their country shows as null, which I'll count rather than hide. No item join, so no fan-out. Aggregation: count distinct orders and sum divided by one point oh five. Then I paste the row-count check to confirm the join didn't duplicate anything.

Safe access

Safety and access matter too. Use read-only credentials for AI-assisted querying, so an assistant can never run an update or delete on production data by accident. Limit access to the tables needed and mask sensitive columns. Beware expensive queries on large tables: add limits and date filters while exploring. And if you build a text-to-SQL feature for others, add query validation, allow-lists of tables, and cost limits. The same applies to assistants connected to databases through M C P servers: least privilege, logged queries.

A verified query library

Here's a habit that makes all of this easier over time: build a small library of verified queries for your core metrics, like revenue, active customers and churn, with the business definitions written as comments at the top. Give that library to the AI as examples whenever you ask for something new. Models write much better SQL when they can see how your organization already calculates key numbers correctly. And your reviews get faster, because new queries look like the old, trusted ones.

Common mistakes

Common mistakes. No schema, so the model guesses column names. Inclusive end dates that pull in the first day of the next month. Join fan-out inflating sums. Averages of averages. Ignoring nulls, so unmatched rows vanish. Time zone mismatches. And running AI-generated SQL with write permissions.

Recap

Recap. Provide schema, join keys with cardinality, business definitions and dialect. Review every generated query on filters, joins, grain, aggregation, nulls and definitions. Watch for fan-out, check row counts and reconcile to trusted figures. Mind time zones. And use read-only, limited, cost-controlled access. Going further: build a small library of verified queries for core metrics, with definitions in comments, and give it to the AI as examples.

Try this now

Try this now. Write a schema card for two related tables you use, including join cardinality and business rules. Ask an AI for a query that answers a real question, apply the six-point review, run the row-count check after the join, and reconcile the result to a number you already trust.

Key takeaways

Provide schema, join keys, business definitions and SQL dialect.
Review filters, joins, grain, aggregation, nulls and definitions for every generated query.
Watch for join fan-out inflating sums; check row counts and reconcile to trusted figures.
Use read-only access, limited tables and cost controls for AI-assisted querying.

Try it

Write a schema description for two related tables you use. Ask an AI for a query answering a real question, and apply the six-point review checklist before trusting the result.