SQL Diff Explainer

Explain the meaningful structural differences between two SQL SELECT queries.

The package uses sqlglot behind an abstract parser interface, so callers receive a stable result even if the parser implementation changes. It reports changes to projections, joins, filters, grouping, aggregation, ordering, pagination, and DISTINCT, with a static low/medium/high risk classification.

The next patch release is 0.1.2. It adds the CI-friendly CLI --fail-on-risk threshold.

Why use SQL Diff Explainer?

Use it in a pull request, migration review, or CI check when a textual SQL diff does not explain the behavioral impact of a query change. It is especially useful for data-engineering teams reviewing:

• dbt model changes where a join or filter can change row counts
• warehouse reports where grouping or aggregation changes the grain
• incremental pipeline queries where DISTINCT, ORDER BY, LIMIT, or OFFSET changes the records consumed downstream
• generated SQL changes where normalized syntax is easier to compare than formatting or alias changes

The result is a structured object, so a CI integration can fail or annotate a review based on result.risk_level, inspect result.changes, or serialize result.to_dict() as JSON.

Real-life data-engineering examples

The examples/ directory contains runnable before/after pairs. For example:

sql-diff-explainer examples\join_before.sql examples\join_after.sql
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

These scenarios demonstrate the changes reviewers most often need to discuss:

1. Outer join to inner join: changing LEFT JOIN to INNER JOIN can exclude dimension or fact rows without a match, so the tool reports one semantic join change at high risk.
2. Added WHERE filter: adding WHERE order_status = 'completed' narrows the population and is reported as a filter change.
3. Aggregation or grouping change: changing COUNT(*) to SUM(revenue), or changing the GROUP BY grain from customer to customer and day, can change metric meaning and row cardinality.
4. DISTINCT, ordering, and pagination: adding DISTINCT changes duplicate handling; changing ORDER BY changes presentation or deterministic paging; changing LIMIT/OFFSET changes which rows a downstream consumer receives.

Try the same comparisons from Python:

from sql_diff_explainer import explain_sql_change

result = explain_sql_change(
    "SELECT c.id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id",
    "SELECT c.id FROM customers c INNER JOIN orders o ON c.id = o.customer_id",
)
assert result.risk_level == "high"
for change in result.changes:
    print(change.category, change.risk, change.description)

What it catches—and what it cannot know

The analyzer parses both inputs with sqlglot and compares the supported SELECT structure: projections, joins and join types, WHERE, GROUP BY, aggregate functions, ORDER BY, LIMIT, OFFSET, and DISTINCT. It provides static risk guidance; it does not execute SQL or connect to a warehouse.

Risk is therefore a useful review signal, not a correctness proof. The tool cannot infer table statistics, actual null rates, constraints, query plans, cost, freshness, permissions, dialect-specific UDF behavior, or whether a business metric is correct. It also does not validate schema availability or compare multiple statements in a migration file. Pair its output with warehouse tests, representative row-count checks, query-plan review, and domain-owner approval.

Development

This project uses a src/ layout and requires Python 3.10 or newer.

python -m venv .venv
.venv\Scripts\Activate.ps1
python -m pip install -e ".[dev]"
pytest

Library usage

from sql_diff_explainer import explain_sql_change

result = explain_sql_change(
    "SELECT department, COUNT(*) FROM employees GROUP BY department",
    "SELECT department, COUNT(*) FROM employees WHERE active = TRUE GROUP BY department",
)
print(result.to_dict())

explain_sql_change raises SQLParseError for invalid SQL and UnsupportedSQL for non-SELECT statements. Pass dialect="postgres" (or another sqlglot dialect) when parsing dialect-specific syntax.

CLI

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

--fail-on-risk accepts low, medium, or high and exits with status 1 when detected overall risk meets or exceeds the chosen threshold. It does not suppress the regular text or JSON report. Comparisons with no detected changes exit successfully even though their baseline report risk is low. JSON output is intended for CI and integrations; text output is optimized for human review.

Exit codes:

• 0: the comparison succeeded and no detected change met the configured threshold (or --fail-on-risk was not set).
• 1: detected overall risk met or exceeded --fail-on-risk.
• 2: an input file could not be read or an expected SQL parse/support error occurred.