How to Audit AI-Generated SQL Before Using It

Why a query that runs can still be wrong

AI-generated SQL can compile, execute and return rows without correctly answering the business question. A database runs the operations it receives; it cannot tell whether a sum represents the metric your team calls “revenue,” whether the chosen period is appropriate, or whether a JOIN duplicated records. Valid syntax is a condition for execution, not proof of logical correctness.

The risk often lies in plausible decisions that go unnoticed: joining tables on the wrong key, applying an unrequested filter, aggregating values after multiplying rows, or interpreting NULL values differently than intended. Before execution, you should be able to explain what each row represents and why every condition appears in the query. Then compare the result with a reference that does not depend on the same AI generation.

This protocol focuses on analytical queries over relational databases. It does not replace defining business rules or carrying out a full security review. Instead, it provides a practical sequence: clarify the question, inspect the SQL statically, test it with limited scope, and validate the output. Read-only access reduces the potential for damage, but does not by itself guarantee correct results.

Step 1: Turn the business question into verifiable criteria

Before reading the SQL, define what the request means. “Quarterly sales by customer” leaves several questions open: gross or net sales? Orders created or orders completed? Which dates define the quarter? Are returns deducted? Which time zone applies? If these decisions are not documented, a seemingly reasonable query may answer a different interpretation of the question.

Write a short specification covering the metric, unit of analysis, period, filters and grouping level. Also identify the approved source for each piece of data. For instance, if the order total is stored in the order header and products are stored in a detail table, specify that the metric must retain one logical row per order before summing. The AI should not silently fill in rules the team has not agreed on.

Clarify the actual schema and SQL dialect. Check tables, columns, types, keys and documented relationships in the database or data dictionary; do not trust names just because they sound plausible. In a natural-language-to-SQL integration, the schema and rules provided to the model are useful context, but the output still needs to be checked in the application code and target database engine. Microsoft Learn, for example, demonstrates generating SQL with parameterized values and checking for prohibited queries afterward; it also cautions that instructions to the model do not replace planning security measures.

If a necessary definition is missing, stop the review and clarify it with the owner of the metric. Adding an invented condition to “make it work” does not resolve the ambiguity; it merely turns it into a decision that may be hard to spot later.

Step 2: Inspect the SQL statically before execution

Read the query from the outside in and mark every table, JOIN, filter, aggregation, subquery and window function. Compare the referenced objects with the actual schema. Then trace the rows: what relationship does each JOIN introduce, which conditions discard records, and when does the granularity change? Do not accept a condition just because its name sounds familiar.

JOINs deserve particular attention. Confirm that the keys represent the right relationship and determine its cardinality: one-to-one, one-to-many or many-to-many. If one table has a row per order and another has a row per item, joining them can repeat the order-header amount once per item. Summing the header amount after that join can inflate the total without causing a syntax error. Adding DISTINCT afterward does not prove that the logic has been fixed.

Check every filter against the specification. Verify statuses, dates, inclusive or exclusive boundaries, and conditions involving NULL. Consider whether the SQL removes records that should count or includes records that should not. NULL values do not behave like ordinary values in a condition such as `column = value`; their treatment must be deliberate, not assumed.

Examine aggregations and window functions by asking a specific question: which set of rows is each value calculated over? Confirm that selected and grouped columns match the required level. For nested subqueries, note which rows each level returns and how it connects to the next. Finally, verify that the functions and syntax match the target database’s dialect.

Step 3: Limit scope and execute in a controlled environment

Whenever possible, run the first test outside production: in an isolated test database with synthetic data, or in an authorized, anonymized copy prepared under your organization’s policies. Synthetic data lets you construct deliberate cases—including duplicates, NULL values and date boundaries—without exposing real records. An anonymized copy may better reflect the real data distribution, but you need to verify that the anonymization process is suitable and that the copy has no write access to production.

Configure the connection with least privilege. For a query test, use a read-only identity and limit access to the tables or views required. Check resource constraints too: maximum runtime, row volume and the ability to cancel execution. A read-only query can still consume excessive resources or return data the user should not see; security and correctness are related, but distinct, controls.

Do not execute AI-generated SQL without application-side validation. Prompt instructions can guide the model, but they are not a security boundary. Allow only approved operations and objects, validate parameters, and use parameterized queries for values. Microsoft Learn describes an example in which the server supplies strings as parameters and checks the query before execution; the prohibited-keyword list shown there is illustrative, not a complete defense that can be applied without adaptation.

Start with the smallest scope that can test the logic: a known entity, a short period or a small sample. If you need to inspect an execution plan, do so in an authorized environment and account for the cost. The goal is not to prove that the query can run across the whole database, but to safely test the relevant assumptions before expanding its scope.

Step 4: Test against known cases and compare results

A plausible output is not a reference. Validation should use cases whose results can be anticipated without asking the same model to confirm its own query. Prepare small examples with calculable results, records at period boundaries, entities with and without related records, and rows containing NULL values where relevant. With synthetic data, you can control these conditions precisely.

Compare the result with an approved reference: a simplified query reviewed by a person, a report whose definition matches the specification, or a manual calculation over a small dataset. Make sure both methods use the same period, filters, exclusion rules and granularity. Two queries that differ on these points are not comparable, even if they use the same metric name.

Validate both structure and values. Check row counts, duplicate keys, totals by group and edge cases. If the result should have one row per customer, verify that uniqueness; if it is grouped by month, inspect months with no activity and the date boundaries. When results differ, narrow the test: inspect the data before the JOIN, then after each join, and finally after filters and aggregations. This helps locate the stage at which the figure changes.

Cross-validation does not turn a limited test into a universal guarantee. A known case can confirm that a query behaves correctly in that situation, but not necessarily across every period or data combination. Turn cases that expose errors into regression tests to check that future changes do not reintroduce the same problem.

Errors, empty results and ambiguity

A syntax error means the database cannot interpret the query, but fixing it does not validate the logic. Check the SQL dialect, names and types before requesting another generation. If you ask for a correction, describe the specific error and preserve the business conditions you have verified. Do not simply say “fix it”: the response may change other parts of the query.

An empty result also has more than one possible explanation. A filter may be too restrictive; values may differ in capitalization or format; a date may be wrong; a relationship may exclude rows; or there may genuinely be no matches. Inspect each condition separately in the test environment. Do not automatically remove filters just to get results: you could turn an interpretation problem into a query that includes out-of-scope data.

If the model cannot explain where a table, relationship or rule came from, treat it as unverified. Reformulate the question with the missing definition or ask the metric owner for clarification. The model’s explanations can help you understand its proposal, but they are not independent evidence that it is correct.

If the SQL differs from the reference, retain the query, parameters, schema version, test case and observed result, subject to appropriate privacy safeguards. Fix the cause, not just the visible value. Then repeat the affected checks and add a regression test if the problem could recur.

Final checklist before using the query

Before using AI-generated SQL in an analysis or pipeline, confirm that the original question has a verifiable definition; every table, column and relationship exists in the schema; and the JOINs preserve the required granularity. Review filters, aggregations, NULL handling, dates, functions and dialect. Also make sure the query was tested in an authorized environment with suitable data and read-only permissions where appropriate.

The output must pass an independent check using known cases or comparable reference data. If the check fails, do not present the result as confirmed. Correct or reformulate the query, rerun the tests and record the approved version and its scope. This checklist does not remove the need for expert review of high-impact metrics, sensitive data or complex queries.

The practical rule is simple: a query is ready only when it is understood, limited to authorized access and produces consistent results under tests that reflect the business question. If any of these conditions cannot be demonstrated, preserve the uncertainty and request a review before using it.

Keep exploring

Corrections and transparency

If you spot incorrect or outdated information, send us a correction with the page and source we should review.