AI-Assisted SQL Code Review

Who This Guide Is For

Software engineers who work with SQL transformations and want to use build-cli to produce a structured review before submitting code for peer review. This guide is suitable for any skill level — “beginner” here refers to the AI workflow, not SQL expertise.

What You Will Learn

How to start a build-cli session and provide SQL for review
A structured review covering logic errors, data quality risks, performance, and readability
How to ask follow-up questions on flagged issues
How to generate a review summary for your pull request
What the AI can and cannot catch (and why the human reviewer’s sign-off is still required)

Step-by-Step Instructions

Step 1: Start a Session in Your Project Directory

cd path/to/your/project
build-cli

Step 2: Provide the SQL and Ask for a Structured Review

Review this SQL transformation. Check for:
1. Logic errors — does the transformation do what it's supposed to do?
2. Data quality risks — null handling, type mismatches, unexpected duplicates, fan-out joins
3. Performance concerns — missing filters, cartesian products, unnecessary subqueries
4. Readability and maintainability issues
5. Any deviation from standard naming or style conventions

Here is the SQL:

[paste your SQL here]

And here is what this transformation is supposed to do:
[describe the business intent in 1-3 sentences]

Step 3: Ask Follow-Up Questions

After the initial review, follow up on any flagged issues:

You flagged a potential fan-out on the join between orders and line_items.
Can you show me the specific lines and explain what to check to confirm whether it's actually happening?

What's the safest way to fix the null handling issue you identified on the customer_id column?

Step 4: Generate a Review Summary

Before handing off to a human reviewer, generate a summary they can use as a starting point:

Summarise the review findings as a structured comment I can add to the pull request.
Include: what was checked, what was found (severity: high / medium / low), and what I fixed.
Format it as a markdown checklist.

Step 5: Human Reviewer Validates

Share the AI-generated review summary with your reviewer. The reviewer:

Confirms the AI findings are accurate
Adds any findings the AI missed
Makes the approval decision independently

The AI review does not replace the reviewer’s sign-off.

Verification

Your review is complete when:

build-cli has checked all five dimensions (logic errors, DQ risks, performance, readability, conventions)
You have followed up on any high-severity findings
You have a formatted PR review comment ready for your pull request
A human reviewer has made the final approval decision

Next Steps

See the ‘AI-Assisted Python and PySpark Pipeline Review’ guide for the Python equivalent of this workflow
See the dbt project CLAUDE.md example article for a ready-made CLAUDE.md template for dbt projects

CLAUDE.md Snippet

Add this to your project’s CLAUDE.md to give build-cli SQL context for every session:

## SQL conventions
- Database: [your DB — e.g. Snowflake / Databricks / PostgreSQL]
- Naming: snake_case for all identifiers
- Null handling: explicit COALESCE or IS NOT NULL checks required on all join keys
- No SELECT * in production transformations
- CTEs preferred over nested subqueries

## Review checklist
When reviewing SQL, always check: join cardinality, null propagation on join keys,
filter pushdown, and alignment with the source-to-target mapping document.

Example

Input SQL:

SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    o.order_date,
    SUM(li.amount) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN line_items li ON o.order_id = li.order_id
GROUP BY o.order_id, o.customer_date, c.customer_name

Business intent provided: “Calculate total order value per order, enriched with customer name.”

AI review output (excerpt):

HIGH — Logic error in GROUP BY: o.customer_date in the GROUP BY does not exist in the orders table — this will error or silently group incorrectly depending on the database.

MEDIUM — Potential fan-out on line_items join: If line_items.order_id is not unique (multiple line items per order), the SUM will be correct only if line_items rows are not duplicated. Confirm the grain of line_items.

LOW — Missing null handling on join keys: o.customer_id and c.id are join keys with no null guard. If either is null, the row silently drops from the result set.

What to watch for: The AI correctly identified the GROUP BY typo and the fan-out risk. It did not catch that customers.id and orders.customer_id may have different data types — a human reviewer familiar with the schema should verify this.

Known Limitations

The AI cannot access your actual database to verify row counts, data types, or whether nulls actually exist. It reasons from the SQL structure alone.
For very long SQL files (500+ lines), break the review into logical sections.
The AI may not know your team’s specific naming conventions unless they are in a CLAUDE.md.
Not suitable for SQL that handles regulated or PII data without first confirming anonymisation.
Production changes always require a human approval step downstream.