How to write SQL with Claude and catch fan-out and date errors

Claude can turn a plain-English business question into a SQL query in seconds, and with code execution switched on it can also run that query against a sample of your data inside its sandbox. The two errors that most often slip through an AI-written query are a join that multiplies rows (fan-out), which inflates counts and averages, and a date filter that silently drops the last day of the period. Both produce numbers that look plausible, so the only reliable defense is to know the correct answer for a small sample before you trust the query on the full table.

The method below uses a three-table orders schema, a prompt you can reuse, the correct SQL, and a deliberately flawed version that shows what the two errors do to a monthly revenue report. Every figure was computed in Python with SQLite on the illustrative data, so you can reproduce the check and compare it with whatever Claude returns in your own session.

The requirements and limits quoted here come from Anthropic's help center and privacy pages, verified on November 8, 2026. They cover file uploads, the code execution feature, custom connectors that link Claude to live systems, and how conversation data is treated on consumer and commercial plans.

··········

WHEN ANALYSTS USE CLAUDE TO WRITE SQL.

The typical cases are a report query for a new metric, a rewrite of a query someone else wrote, a translation between SQL dialects, or a first draft against an unfamiliar schema. In each case the analyst knows the business question and roughly where the data lives, and wants a working query faster than writing it from scratch. Claude needs three things to do this well: the table definitions (column names and types), the grain of each table (one row per order, one row per order line), and the exact business rules, such as which order statuses count as revenue and whether the period is defined in local time or UTC.

The risk is specific to SQL. A query can run without errors and still answer the wrong question, and a wrong aggregation still returns a tidy table. That is why the workflow below separates writing the query from checking it, and why the check uses a sample small enough to verify by hand. Earlier coverage of how ChatGPT writes SQL queries from natural-language prompts describes the same basic pattern for another assistant; the controls here apply to any model.

··········

CLAUDE FEATURES AND LIMITS FOR SQL WORK.

You can work with Claude in three ways for SQL: paste the schema and ask for a query, upload a sample file so Claude can run code against it, or connect Claude to a live system through a connector. The documented limits differ for each route.

........

Route	What Claude can do	Documented limits and settings
Chat with pasted schema	Write and explain SQL	No data access; Claude sees only what you paste
Chat file upload	Read CSV, JSON, XLSX and other files	500 MB per file, up to 20 files per chat; XLSX needs code execution
Project files	Reuse schema docs across chats	30 MB per file; total content must fit the context window
Code execution and file creation	Run code on uploaded CSV or TSV data in a sandbox	30 MB per file for uploads and downloads in this feature; uses more of the plan's usage limits
Custom connectors (remote MCP)	Call tools on services you connect	All plans; Free users limited to one custom connector

........

Code execution runs in an isolated container that Anthropic says is not shared between users, with limits on network, storage, task duration and session length. On Free, Pro, Max and Ultra plans it is enabled by default, under Settings, Capabilities, where the toggle is named "Code execution and file creation" or "Create and edit files" in the updated layout. On Team and Enterprise plans the setting sits under Organization settings, Capabilities, and Enterprise organizations start with network egress turned off. Owners can choose between no network access, package managers only, package managers plus specific domains, or all domains.

Connectors are the route to live databases. Anthropic's documentation describes remote MCP servers as giving Claude tools it can invoke during a conversation, limited to the resources the user has granted the server. On Team plans only Owners and Primary Owners can add custom connectors; on Enterprise plans Owners, Primary Owners and people with a custom role that includes Manage access to Libraries can add them. Anthropic warns that custom connectors reach services it has not verified and that malicious MCP servers may contain hidden instructions. The broader picture of Claude Code MCP integrations with databases and external tools covers the developer side of the same mechanism.

··········

SAMPLE ORDERS SCHEMA FOR THE SQL WORKED EXAMPLE.

The illustrative dataset has five customers, eight orders and thirteen order lines. The question is a common one: completed-order revenue, order count and average order value by region for September 2026. Order timestamps include a time of day, which is where date filters usually break.

........

order_id	customer	region	order_ts	status	order total (USD)
101	Acme Corp	East	2026-09-02 10:15:00	completed	320.00
102	Birch LLC	West	2026-09-05 16:40:00	completed	335.00
103	Acme Corp	East	2026-09-12 09:05:00	cancelled	600.00
104	Cobalt Inc	East	2026-09-18 14:20:00	completed	490.00
105	Dune Co	Central	2026-09-30 18:45:00	completed	740.00
106	Birch LLC	West	2026-09-30 08:10:00	completed	160.00
107	Cobalt Inc	East	2026-10-01 00:30:00	completed	360.00
108	Dune Co	Central	2026-08-31 23:50:00	completed	90.00

........

The table above is a summary for the reader. In the database the data sits in three tables: customers (customer_id, name, region), orders (order_id, customer_id, order_ts, status) and order_items (order_id, sku, qty, unit_price). Order totals are the sum of qty times unit_price across each order's lines, and orders 101, 102, 104 and 105 have more than one line, which is what makes the fan-out error visible.

··········

PROMPTING CLAUDE FOR THE REGIONAL REVENUE QUERY.

A good prompt states the dialect, the schema with the grain of each table, the business rules and the expected output columns. It also asks Claude to state its assumptions and to test the query when a sample is available.

You are writing SQLite-compatible SQL.

Tables:
- customers(customer_id INTEGER, name TEXT, region TEXT): one row per customer
- orders(order_id INTEGER, customer_id INTEGER, order_ts TEXT 'YYYY-MM-DD HH:MM:SS', status TEXT): one row per order
- order_items(order_id INTEGER, sku TEXT, qty INTEGER, unit_price REAL): one row per order line

Question: for September 2026, return region, number of completed orders,
completed revenue (qty * unit_price) and average order value.
Rules: only status = 'completed'; include every timestamp in September,
including September 30 after midnight; one order counts once even if it has several lines.

List your assumptions, then give the query. If I upload a sample CSV,
load it into SQLite with Python and show the result next to a reconciliation total.

The last rule in the prompt names the fan-out risk directly. In practice, spelling out the grain of each table and the period boundary removes most of the ambiguity a model would otherwise fill with a guess.

··········

THE CORRECT SQL AND THE EXPECTED RESULT.

The correct query counts distinct orders, sums line revenue, and uses a half-open date range: greater than or equal to the first day of the month and strictly less than the first day of the next month.

SELECT c.region,
       COUNT(DISTINCT o.order_id) AS orders,
       ROUND(SUM(oi.qty * oi.unit_price), 2) AS revenue,
       ROUND(SUM(oi.qty * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
  AND o.order_ts >= '2026-09-01'
  AND o.order_ts < '2026-10-01'
GROUP BY c.region
ORDER BY revenue DESC;

Run in SQLite on the sample, it returns the result below. Order 103 is excluded as cancelled, order 107 falls in October and order 108 falls in August.

........

region	orders	revenue (USD)	avg_order_value (USD)
East	2	810.00	405.00
Central	1	740.00	740.00
West	2	495.00	247.50

........

The reconciliation query computes one total per order first and then adds them up, so it cannot be affected by fan-out. On the sample it returns 5 completed orders and 2,045.00 USD, which equals the sum of the three regional rows.

SELECT COUNT(*) AS completed_orders,
       ROUND(SUM(order_total), 2) AS total_revenue
FROM (
  SELECT o.order_id, SUM(oi.qty * oi.unit_price) AS order_total
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
    AND o.order_ts >= '2026-09-01'
    AND o.order_ts < '2026-10-01'
  GROUP BY o.order_id
) AS t;

··········

HOW FAN-OUT AND BETWEEN DATE FILTERS DISTORT THE SQL RESULT.

The flawed version below is an example of the two error patterns, written for this comparison. It counts order_id without DISTINCT after joining to order lines, and it filters with BETWEEN '2026-09-01' AND '2026-09-30'.

SELECT c.region,
       COUNT(o.order_id) AS orders,
       ROUND(SUM(oi.qty * oi.unit_price), 2) AS revenue,
       ROUND(SUM(oi.qty * oi.unit_price) / COUNT(o.order_id), 2) AS avg_order_value
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
  AND o.order_ts BETWEEN '2026-09-01' AND '2026-09-30'
GROUP BY c.region
ORDER BY revenue DESC;

........

region	correct orders	flawed orders	correct revenue (USD)	flawed revenue (USD)	correct AOV (USD)	flawed AOV (USD)
East	2	4	810.00	810.00	405.00	202.50
Central	1	missing	740.00	missing	740.00	missing
West	2	3	495.00	335.00	247.50	111.67

........

Because order_ts is stored as text with a time of day, '2026-09-30 08:10:00' sorts after '2026-09-30', so BETWEEN excludes both September 30 orders. Central disappears from the report entirely and West loses 160.00 USD. The flawed total is 1,145.00 USD against a correct 2,045.00 USD, a gap of 900.00 USD. The fan-out error is quieter: revenue for East is still right, because summing order lines is the correct grain for revenue, yet the order count doubles from 2 to 4 and average order value halves from 405.00 to 202.50 USD. A report built on the flawed query would pass a casual glance, which is why the reconciliation total belongs in every review.

··········

REVIEW STEPS BEFORE A CLAUDE-WRITTEN QUERY GOES INTO A REPORT.

Start by asking Claude to list its assumptions and compare them with the business rules. Statuses, time zones, period boundaries and the treatment of refunds are the usual points of disagreement, and a query that assumes the wrong rule is correct SQL for the wrong question.

Next, check the grain. For every join, confirm whether it can return more than one row per row of the driving table. If it can, counts and averages must use DISTINCT or the aggregation must happen in a subquery at the right grain before the join. Ask Claude to explain the row count at each step; a wrong answer to that question exposes fan-out faster than reading the SQL.

Then test boundaries with rows that sit exactly on them: a record at 23:50 on the last day of the period, one at 00:30 on the first day of the next, a cancelled record, and a customer with no activity. If the sample does not contain such rows, add them. With code execution on, you can upload a CSV extract and ask Claude to load it into SQLite with Python's built-in sqlite3 module, run both the report query and the reconciliation query, and show the two totals side by side.

Finally, run the query on the production warehouse yourself and compare one period with a figure the business already trusts, such as a closed month in the finance or sales system. Dialect differences show up at this stage: date functions, integer division and string comparison of timestamps behave differently across SQLite, PostgreSQL, BigQuery, Snowflake and SQL Server, so state the dialect in the prompt and retest after any translation. The approval of a query that feeds a published dashboard should stay with a person who knows the data model. For broader context on using the assistant with files and charts, see how to use Claude for data analysis.

··········

DATA PRIVACY AND GOVERNANCE WHEN CLAUDE SEES QUERY DATA.

The safest pattern is to give Claude the schema and a synthetic or masked sample rather than production rows. On consumer plans (Free, Pro and Max), Anthropic's privacy center says chats may be used to improve models if the user allows it through the Model Improvement setting, that incognito chats are not used for training even with that setting on, and that raw content from connectors, including MCP servers, is excluded unless it is copied into a chat. For commercial products, which the same help center lists as Claude for Work, the Anthropic API and Claude Gov, Anthropic states that by default it will not use inputs or outputs to train models, with exceptions for explicitly submitted feedback or bug reports and for customers who opt in.

The code execution documentation adds a security point that applies to SQL work with uploaded files. Instructions hidden in an external file or website could lead Claude to run untrusted code or send data from its context to an outside party when network access is enabled. Disabling network egress keeps data inside the sandbox, but Anthropic notes that MCP connections can still communicate externally regardless of that setting. Teams that connect Claude to a database should use a read-only account limited to the schemas the analyst needs, and keep write permissions out of the connector.

··········

SQL WITH CLAUDE: WHAT THE DOCUMENTATION CONFIRMS AND WHAT YOU STILL CHECK.

Anthropic's documentation confirms that Claude can read CSV, JSON and XLSX uploads, run code on uploaded data in a sandbox when code execution is enabled, and call tools on databases and other services through connectors on every plan, with admin controls on Team and Enterprise. File limits are documented as 500 MB per file and 20 files per chat for chat uploads, and 30 MB per file within the code execution feature and for project files. Network access, connector rights and training settings depend on the plan and on choices made by the account owner or organization admin.

What no setting guarantees is a correct query. In the September example, two small errors turned a 2,045.00 USD month into 1,145.00 USD, removed a region and halved the average order value. A boundary-aware sample, a reconciliation total computed at the right grain and a comparison against a trusted figure catch both errors before anyone builds a dashboard on them.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org