SQL Diff Explainer

See the change. Understand the impact.
Review query behavior, trace changed columns through CTEs, and get practical checks for SQL changes.

Get started  ·  Explore analyses  ·  Use in CI

---

🔗 Query changes	🧭 CTE lineage	✅ Validation suggestions	📋 Schema contracts	🛠️ Migration review
Joins, filters, aggregation, and more	Trace source columns to final outputs	Generate checks for unmatched and duplicate rows	Flag changed, renamed, removed, and reordered outputs	Review common multi-statement ALTER TABLE changes

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 from PyPI and compare two SQL files:

python -m pip install sql-diff-explainer
sql-diff-explainer before.sql after.sql

Get machine-readable output for CI or other tooling:

sql-diff-explainer before.sql after.sql --format json
sql-diff-explainer before.sql after.sql --fail-on-risk high

Or use the Python API:

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)"
    )

Example 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.

Performance

The built-in SQL comparison parses each input once and reuses the parsed SQLGlot trees for schema-contract and CTE-lineage analysis. This reduces parse calls from 4 to 2 for non-CTE comparisons and from 6 to 2 when CTE lineage is included (up to 67% fewer parser calls). That is a reduction in parser work, not a promise that every query runs at the same speed.

In a local microbenchmark comparing this optimization with v0.1.5, the median time for a synthetic two-CTE query comparison fell from 1.57 ms to 0.80 ms (49.1% lower). A sample non-CTE comparison fell from 0.69 ms to 0.49 ms (29.5% lower). Each sample used 1,000 comparisons per run across seven alternating baseline/optimized runs on Python 3.14.7; these figures are illustrative and depend on query shape and environment.

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.