SQL Diff Explainer

See what changed in a SQL query—and why it might matter.

SQL Diff Explainer compares the structure of two SELECT queries and returns the changes with a static risk signal, so reviews focus on query behavior rather than formatting.

Contents

• Quick start
• Command-line usage
• Directory comparison
• Git-aware pull-request comparison
• SARIF and HTML reports
• Additional SQL QA analyses
• What it compares
• Use cases
• Scope and limitations
• Development
• Contributing
• License

Quick start

Install the latest release from PyPI:

python -m pip install sql-diff-explainer

Compare two queries from Python:

from sql_diff_explainer import explain_sql_change

before = """
SELECT c.customer_id, c.segment, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
"""
after = """
SELECT c.customer_id, c.segment, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
"""

result = explain_sql_change(before, after)
print(result.summary)
for change in result.changes:
    print(
        f"{change.category}: {change.before} -> {change.after} "
        f"({change.risk} risk)"
    )

Output:

1 SQL change(s) detected; overall risk is high.
join: LEFT JOIN orders AS o ON c.customer_id = o.customer_id -> INNER JOIN orders AS o ON c.customer_id = o.customer_id (high risk)

The change is classified as high risk because an INNER JOIN excludes customers without a matching order, whereas a LEFT JOIN retains them. The result is structured: inspect result.risk_level and result.changes, or call result.to_dict() for JSON-ready data. Pass dialect="postgres" (or another sqlglot dialect) for dialect-specific SQL. Invalid SQL raises SQLParseError; non-SELECT input raises UnsupportedSQL.

Command-line usage

The CLI compares two SQL files and prints a human-readable report by default:

sql-diff-explainer examples/join_before.sql examples/join_after.sql

Choose JSON output, set a parsing dialect, or make a risk threshold fail a CI step:

sql-diff-explainer before.sql after.sql --format json
sql-diff-explainer before.sql after.sql --dialect postgres
sql-diff-explainer before.sql after.sql --fail-on-risk medium

--fail-on-risk accepts low, medium, or high. It exits with status 1 when detected changes meet or exceed the threshold; the text or JSON report is still printed. No detected changes exit successfully. Input-file errors and SQL parse/support errors exit with status 2.

Try the runnable before/after pairs in examples/:

sql-diff-explainer examples/filter_before.sql examples/filter_after.sql --format json
sql-diff-explainer examples/aggregation_before.sql examples/aggregation_after.sql
sql-diff-explainer examples/distinct_before.sql examples/distinct_after.sql
sql-diff-explainer examples/cte_lineage_before.sql examples/cte_lineage_after.sql --format json
sql-diff-explainer examples/output_contract_before.sql examples/output_contract_after.sql --format json
sql-diff-explainer --analyze-migration examples/migration.sql --format json

--analyze-migration FILE accepts multi-statement migration scripts and emits text or JSON. It supports common ALTER TABLE column operations; SARIF and HTML formats are not available for this separate migration mode.

Directory comparison

Compare two directory trees recursively. SQL files are paired by their exact relative path (with / separators); the .sql extension is matched case-insensitively. Files present on only one side are reported as additions or removals rather than parsed as empty SQL, and raise the aggregate risk to high:

sql-diff-explainer --compare-dirs models/main models/review --format json
sql-diff-explainer --compare-dirs models/main models/review --fail-on-risk high

The JSON report includes each matched file's ordinary SQL diff, plus unmatched_before and unmatched_after path lists. A missing or non-directory input, unreadable file, or invalid SQL exits with status 2.

The same operation is available in Python:

from sql_diff_explainer import compare_sql_directories

result = compare_sql_directories("models/main", "models/review")
print(result.to_dict())

Git-aware pull-request comparison

Compare SQL paths Git reports as changed between a base ref and the current worktree. Modified files and SQL-to-SQL renames are compared semantically; added or removed SQL files are reported explicitly as high risk. Untracked files are not included. The base can be a branch, tag, or commit:

sql-diff-explainer --git-base origin/main --format json
sql-diff-explainer --git-base "$GITHUB_BASE_REF" --format sarif --output sql-diff.sarif
sql-diff-explainer --git-base origin/main --fail-on-risk high

For GitHub Actions, fetch the base history and use the pull request's base commit. This example checks out the pull-request worktree, installs this repository's source (so the CLI includes the feature), emits SARIF, and fails the job on high-risk changes:

name: SQL review
on:
  pull_request:

jobs:
  sql-diff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: python -m pip install .
      - name: Analyze changed SQL
        run: >-
          sql-diff-explainer
          --git-base "${{ github.event.pull_request.base.sha }}"
          --format sarif
          --output sql-diff.sarif
          --fail-on-risk high
      - name: Upload SQL findings
        if: always() && hashFiles('sql-diff.sarif') != ''
        uses: github/codeql-action/upload-sarif@v3
        with:
          sarif_file: sql-diff.sarif

An unresolved base ref, Git failure, unreadable SQL, or invalid SQL exits with status 2 and a diagnostic. Renames appear in every report; a rename does not raise risk by itself, while semantic changes in its content retain their normal risk level.

SARIF and HTML reports

Emit SARIF 2.1.0 results for GitHub code scanning integrations, or generate a self-contained HTML report that can be attached to a review:

sql-diff-explainer before.sql after.sql --format sarif --output sql-diff.sarif
sql-diff-explainer --compare-dirs models/main models/review --format html --output review.html

Both formats work with the existing --fail-on-risk threshold. SARIF maps high, medium, and low risk to error, warning, and note; report output is still written when the threshold causes exit status 1. HTML escapes SQL text and file paths. Python callers can use to_sarif(result) or render_html_report(result) with either SQLDiffResult or SQLDirectoryDiffResult.

Additional SQL QA analyses

Query comparisons include three static QA views in SQLDiffResult and JSON output:

• CTE lineage (lineage) traces explicit output expressions through non-recursive CTE chains to their source columns. Changed sources or expressions are also included in changes and the risk summary. Lineage requires explicit projections, aliases for calculated expressions, and direct table/CTE sources; recursive CTEs, wildcard projections, nested subqueries, CTE column alias lists, and ambiguous unqualified columns are reported as unsupported.
• Output-schema contracts (schema_contract) report added, removed, renamed, reordered, changed, and supported type-changed output columns. Types are inferred only for explicit casts and aliased literals; column types that require a database catalog remain unknown. Wildcard projections, unnamed expressions, and set operations are reported as unsupported.
• Validation suggestions (validation_checks) provide a checklist and, when the before/after output columns are compatible, SQL for unmatched-row and duplicate checks. The unmatched-row query uses EXCEPT; adapt it for dialects that use different set-difference syntax. These checks are suggestions only: SQL Diff Explainer never connects to a database or executes generated SQL.

Use the analysis helpers directly when needed:

from sql_diff_explainer import (
    analyze_migration,
    compare_output_schema,
    trace_cte_lineage,
)

before_sql = """
WITH totals AS (SELECT customer_id, amount FROM sales)
SELECT customer_id, amount FROM totals
"""
after_sql = """
WITH totals AS (SELECT customer_id, net_amount AS amount FROM sales)
SELECT customer_id, amount FROM totals
"""

lineage = trace_cte_lineage(before_sql, after_sql)
contract = compare_output_schema(before_sql, after_sql)
migration = analyze_migration(
    "ALTER TABLE users ADD COLUMN email TEXT;"
    "ALTER TABLE users RENAME COLUMN old_email TO email_address;"
)
print(lineage.to_dict(), contract.to_dict(), migration.to_dict())

Migration analysis recognizes ADD COLUMN, DROP COLUMN, RENAME COLUMN, ALTER COLUMN ... TYPE, and ALTER COLUMN ... SET/DROP NOT NULL. Other statements and ALTER actions are listed as unsupported rather than being silently treated as analyzed. Use --analyze-migration for a file or analyze_migration(sql) from Python.

What it compares

The analyzer compares supported SELECT query features and reports additions, removals, or changes with a low, medium, or high risk classification:

Feature	Examples of changes
Projections	Selected columns and expressions
Joins	Join target, predicate, or type
Filters	WHERE predicates
Aggregation	Aggregate functions and GROUP BY expressions
Ordering and pagination	ORDER BY, LIMIT, and OFFSET
Duplicate handling	DISTINCT added or removed
Output schema	Result-column additions, removals, renames, ordering, and known type changes

Join or filter changes can alter which rows qualify; grouping or aggregation changes can change a metric's meaning or result grain; and pagination changes can affect which rows downstream consumers receive.

Use cases

• Review dbt models or warehouse SQL in a pull request.
• Spot join and filter changes that could alter row counts.
• Check grouping and aggregation changes that could shift metric grain.
• Add a lightweight risk threshold to a CI check.
• Explain generated SQL changes without treating formatting-only differences as query changes.

Scope and limitations

This is static analysis, not a SQL execution engine or correctness proof. It does not connect to a database, validate schemas or permissions, estimate query plans or cost, inspect table statistics or null rates, or decide whether a business metric is correct. Output-column types that require database metadata are unknown. CTE lineage and query validation SQL support only the documented static cases, and migration analysis recognizes only the listed ALTER TABLE operations. Generated validation SQL is not executed and may need dialect adaptation. The analyzer does not guarantee complete semantic equivalence across SQL dialects. Risk levels are review signals, not predictions. Pair the report with warehouse tests, representative row-count checks, query-plan review, and domain-owner input.

Development

The package requires Python 3.10 or newer and uses a src/ layout.

python -m venv .venv

Activate the environment, then install the development dependencies and run the checks:

# macOS / Linux
source .venv/bin/activate

# Windows PowerShell
.venv\Scripts\Activate.ps1

python -m pip install -e ".[dev]"
python -m pytest
ruff check .

Contributing

Bug reports, focused improvements, and pull requests are welcome. Please include or update tests for behavior changes and keep documentation aligned with the supported API.

License

Distributed under the MIT License.