Metadata-Version: 2.5
Name: sql-diff-explainer
Version: 0.1.2
Summary: Explain SQL schema and migration differences
Author: zmodz1010
License: MIT
License-File: LICENSE
Requires-Python: >=3.10
Requires-Dist: sqlglot>=25.0
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Description-Content-Type: text/markdown

# SQL Diff Explainer

Explain the meaningful structural differences between two SQL `SELECT` queries.

The package uses [sqlglot](https://github.com/tobymao/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:

```powershell
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:

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

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

## Library usage

```python
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

```powershell
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.
