Metadata-Version: 2.4
Name: sqlquality
Version: 0.4.0
Summary: Measure dbt model performance and complexity, and gate changes on the delta.
Project-URL: Homepage, https://github.com/hanslemm/sqlquality
Project-URL: Issues, https://github.com/hanslemm/sqlquality/issues
Author: Hans Lemm
License-Expression: MIT
License-File: LICENSE
Keywords: complexity,data-engineering,dbt,lint,sql
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Requires-Python: >=3.11
Requires-Dist: pyyaml>=6
Requires-Dist: rich>=13
Requires-Dist: sqlfluff<5,>=4
Requires-Dist: sqlglot<31,>=30.12
Requires-Dist: typer>=0.12
Provides-Extra: llm
Requires-Dist: anthropic>=0.40; extra == 'llm'
Provides-Extra: postgres
Requires-Dist: psycopg[binary]>=3.1; extra == 'postgres'
Provides-Extra: warehouse
Requires-Dist: psycopg[binary]>=3.1; extra == 'warehouse'
Description-Content-Type: text/markdown

# sqlquality

Measure the **structural complexity** of dbt models' SQL and **gate pull requests
on the complexity delta** between two dbt manifests. Alongside the gate, `sqlquality`
runs per-engine static **performance anti-pattern** checks (with optional
captured-`EXPLAIN` analysis), sqlfluff-backed **linting**, and optional, advisory
**LLM suggestions**.

sqlquality **never executes your SQL**. `complexity`, `lint`, `perf`, `check` and `verify`
are fully offline and never open a connection. `advise` is the one exception: it opens a
**read-only** session to read query history and catalog metadata, using only a fixed set
of built-in introspection statements. Run `sqlquality advise --dry-run` to print every
statement it can issue, without connecting.

- **Complexity** is computed from the SQL AST (via [sqlglot](https://github.com/tobymao/sqlglot)).
- **Performance** is static anti-pattern detection plus ingestion of an `EXPLAIN`
  plan you captured yourself — no query is ever run.
- **Neighbors** (a changed model's direct upstream/downstream models) are *reported*
  for context; they are not scored or gated.
- **Advice** is derived from your query history and catalog statistics, and is emitted as
  a report plus a DDL file for you to review and apply. sqlquality never writes to your
  database.

Requires Python 3.11+.

## Contents

- [Install](#install)
- [Commands](#commands)
  - [complexity](#complexity)
  - [lint](#lint)
  - [perf](#perf)
  - [advise](#advise)
  - [verify](#verify)
  - [check](#check-the-ci-gate)
- [Configuration](#configuration-sqlqualityyml)
- [Exit codes](#exit-codes)
- [CI recipe (a gate that actually gates)](#ci-recipe-a-gate-that-actually-gates)
- [Pre-commit hook](#pre-commit-hook)
- [LLM suggestions](#llm-suggestions-optional-advisory)
- [Limitations](#limitations)

## Install

Once published to PyPI:

```bash
pip install sqlquality
# or
uv add sqlquality
```

Until then, install from git:

```bash
pip install "sqlquality @ git+https://github.com/hanslemm/sqlquality"
# or
uv add "git+https://github.com/hanslemm/sqlquality"
```

The optional LLM suggestions feature needs the `llm` extra (pulls in the Anthropic
SDK):

```bash
pip install "sqlquality[llm]"
```

`advise` needs a database driver. For Postgres:

```bash
pip install "sqlquality[postgres]"
# or, for the driver bundle covering every engine advise targets over time:
pip install "sqlquality[warehouse]"
```

Today `[warehouse]` pulls in the same driver as `[postgres]` (psycopg) — Redshift and
Snowflake support is designed but not yet implemented; see
[Limitations](#limitations). Without the extra, `advise` degrades with an install hint
instead of a traceback.

`--version` prints the installed version:

```console
$ sqlquality --version
0.2.0
```

## Commands

```
sqlquality complexity   Score the structural complexity of a single SQL file.
sqlquality check        Gate a dbt change on the complexity delta of its changed models.
sqlquality lint         Lint SQL files for best-practice violations (SQLFluff); --fix rewrites them.
sqlquality perf         Analyze a SQL file for performance anti-patterns (+ optional EXPLAIN plan).
sqlquality advise       Propose database optimizations from query history and catalog metadata.
sqlquality verify       Diff two `advise --json` artifacts: was each proposal applied, and did it help?
```

The `--dialect` / `-d` flag is validated against sqlglot's dialect registry on every
command; an unknown value fails fast with exit 2 and a suggestion. `complexity` and
`lint` also accept `-` to read SQL from stdin.

### complexity

Scores one SQL file and prints a per-metric contribution breakdown plus a composite.

```console
$ sqlquality complexity model.sql
  Complexity — model.sql  (composite 18.4)
┏━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━┓
┃ metric            ┃ value ┃ contribution ┃
┡━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━┩
│ join_count        │     0 │          0.0 │
│ cte_count         │     2 │          4.0 │
│ subquery_count    │     0 │          0.0 │
│ window_count      │     1 │          4.0 │
│ case_count        │     0 │          0.0 │
│ union_count       │     0 │          0.0 │
│ distinct_count    │     0 │          0.0 │
│ max_select_depth  │     2 │         10.0 │
│ projected_columns │     2 │          0.4 │
└───────────────────┴───────┴──────────────┘
```

Read SQL from stdin with `-`:

```bash
cat model.sql | sqlquality complexity -
```

`--json` emits a machine-readable payload (composite, per-metric contributions, and
the raw metrics):

```console
$ sqlquality complexity model.sql --json
{
  "components": {
    "case_count": 0.0,
    "cte_count": 4.0,
    "distinct_count": 0.0,
    "join_count": 0.0,
    "max_select_depth": 10.0,
    "projected_columns": 0.4,
    "subquery_count": 0.0,
    "union_count": 0.0,
    "window_count": 4.0
  },
  "composite": 18.4,
  "dialect": "postgres",
  "metrics": {
    "case_count": 0,
    "cte_count": 2,
    "distinct_count": 0,
    "join_count": 0,
    "max_select_depth": 2,
    "projected_columns": 2,
    "select_count": 3,
    "subquery_count": 0,
    "union_count": 0,
    "window_count": 1
  },
  "path": "model.sql"
}
```

**dbt / Jinja models:** if the file contains Jinja (`{{ ... }}`, `{% ... %}`),
`sqlquality` first tries to parse it as-is; on failure it retries with Jinja
markers stripped to placeholders and prints a notice to **stderr**:

```
analyzed with Jinja placeholders — results are approximate; prefer compiled SQL from target/compiled/
```

For accurate scores, point `complexity` at compiled SQL from `target/compiled/`
after `dbt compile`. The composite is a real, comparable score in both cases — but
placeholder-stripped results are approximate.

### lint

Lints SQL with sqlfluff and prints findings per file.

```console
$ sqlquality lint messy.sql
                                     Lint —
                            messy.sql (5 findings)
┏━━━━━━┳━━━━━━┳━━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ line ┃ code ┃ severity ┃ fix? ┃ message                                      ┃
┡━━━━━━╇━━━━━━╇━━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│    1 │ AM04 │ warning  │      │ Query produces an unknown number of result   │
│      │      │          │      │ columns.                                     │
│    1 │ RF02 │ warning  │      │ Unqualified reference '*' found in select …   │
│    2 │ AL01 │ warning  │  ✓   │ Implicit/explicit aliasing of table.         │
│    2 │ AL01 │ warning  │  ✓   │ Implicit/explicit aliasing of table.         │
│    2 │ AL05 │ warning  │  ✓   │ Alias 'o' is never used in SELECT statement.  │
└──────┴──────┴──────────┴──────┴──────────────────────────────────────────────┘
```

**Exit-code semantics:** `lint` exits **1** when any `WARNING`/`ERROR` finding is
present, so it gates CI and pre-commit by default. `--warn-only` prints/emits
findings but always exits 0. Findings from unresolved Jinja are demoted to `info`
severity and **never** gate.

Useful flags:

| Flag | Effect |
|---|---|
| `--fix` | Rewrite the file with auto-fixes. The exit code still reflects *pre-fix* findings (a fully-fixed file still exits 1). Cannot rewrite stdin. |
| `--warn-only` | Always exit 0. |
| `--sqlfluff-config <file>` | Apply a custom sqlfluff config (e.g. `.sqlfluff`). |
| `--exclude-rules <codes>` | Comma-separated rule codes to skip. |
| `--json` | Emit machine-readable JSON. |

`lint` accepts multiple files (and `-` for stdin), which is what the pre-commit hook
relies on.

### perf

Detects static performance anti-patterns for a given engine, and optionally folds in
findings parsed from a captured `EXPLAIN` plan.

```console
$ sqlquality perf messy.sql
                         Perf — messy.sql (postgres, 3 findings)
┏━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ code  ┃ severity ┃ message                                                             ┃
┡━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ SQ001 │ warning  │ SELECT * projects an unknown/wide column set; list columns          │
│       │          │ explicitly.                                                         │
│ SQ002 │ warning  │ Cartesian/cross join without an ON/USING condition.                 │
│ SQ003 │ warning  │ Leading-wildcard LIKE ('%...') is non-sargable and cannot use an    │
│       │          │ index.                                                              │
└───────┴──────────┴─────────────────────────────────────────────────────────────────────┘
```

Supported engines: **`postgres`** and **`redshift`** (Redshift additionally infers
`DISTKEY`/`SORTKEY` advice). Any other valid sqlglot dialect is accepted for
`complexity`/`lint` but has no perf adapter, so `perf` exits 2 for it.

**Exit code:** `perf` exits **1 only when a finding is `ERROR` severity** — which in
practice means the SQL was unparseable (`SQ000`). Anti-pattern findings are `warning`
severity and exit **0**, so `perf` surfaces advice without blocking a build. Bad
input (missing file, unreadable `--explain`) exits 2.

**Captured EXPLAIN.** `--explain <file>` takes a plan you captured yourself:

- **Postgres:** `EXPLAIN (FORMAT JSON) <query>` output (JSON).
- **Redshift:** the plan text from `EXPLAIN <query>`.

```console
$ sqlquality perf messy.sql --explain plan.json
                         Perf — messy.sql (postgres, 4 findings)
┏━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ code  ┃ severity ┃ message                                                             ┃
┡━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ SQ001 │ warning  │ SELECT * projects an unknown/wide column set; list columns …        │
│ SQ002 │ warning  │ Cartesian/cross join without an ON/USING condition.                 │
│ SQ003 │ warning  │ Leading-wildcard LIKE ('%...') is non-sargable …                    │
│ PG001 │ warning  │ Seq Scan on orders — consider an index if the filter is selective.  │
└───────┴──────────┴─────────────────────────────────────────────────────────────────────┘
```

`--json` emits findings and any LLM suggestions. `--suggest` enriches findings with
advisory LLM suggestions — see [LLM suggestions](#llm-suggestions-optional-advisory).

### advise

Reads a database's query history and catalog metadata over a **read-only** connection,
weights column usage by the cost of the queries that use it, and proposes concrete
optimizations — indexes to add, indexes to drop, partial indexes, non-sargable
predicates, and hot `SELECT *`. Output is an advisory report plus a DDL file for you to
review. **`advise` never writes to your database and never executes DDL.**

**Postgres** and **Redshift** are implemented; Snowflake is designed but not built — see
[Limitations](#limitations). An optional dbt manifest enriches the same analysis — see
[dbt enrichment](#dbt-enrichment-optional) below.

**What is proven for Redshift, and what is not — read this before pointing `--engine
redshift` at a production cluster.** There is no Redshift container available for
development, and Postgres — where every other engine's introspection SQL gets exercised
during tests — does not implement Redshift's `svv_*`/`sys_*` system views at all, so
nothing in this adapter can be run against a real Redshift cluster before release. What
*is* verified: the **connection path** (Redshift speaks the PostgreSQL wire protocol, so
the read-only session, the statement timeout and secret scrubbing are exercised live
against a real Postgres server); every introspection **statement's syntax**, checked with
sqlglot's `redshift` dialect; and every statement's **bindability** — that its parameters
can actually be prepared and sent over the wire — proven live against stand-in tables
shaped like the real views. What is **not** verified: the **column names and the
semantics of the resulting proposals**. Those come from AWS's published system-view
documentation, not from an observed row, and have never been executed against a live
cluster. A wrong column name degrades one capability (recorded in `degraded`, never a
crash — see the Redshift section below), but it can still mean thin or wrong evidence.
Run `sqlquality advise --engine redshift --dry-run` first: it prints every statement this
adapter can issue, with no connection at all, so you can review it — or hand it to a DBA
— before `advise` ever touches your cluster. If you run this against a real cluster,
please [open an issue](https://github.com/hanslemm/sqlquality/issues) with what you found;
the first user with a cluster is part of closing this gap, not just a consumer of it.

```console
$ sqlquality advise --dsn postgresql://readonly@db.internal/analytics
engine: postgres (credentials from --dsn)
window: since stats reset at 2026-07-19 03:00:00+00
analyzed 3 of 3 query group(s); skipped 0 unparseable, 0 filtered, 0 unresolvable
                Advise — postgres (5 proposals, 3 query groups)
┏━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ code   ┃ conf   ┃ cost share ┃ proposal                                      ┃
┡━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ ADV001 │ high   │      67.0% │ Add index on orders(status)                   │
│ ADV005 │ high   │      22.7% │ Non-sargable predicate on orders.email        │
│ ADV006 │ medium │      22.7% │ Hot SELECT * over wide table(s): orders       │
│ ADV005 │ medium │      10.3% │ Leading-wildcard LIKE in a hot query group    │
│ ADV002 │ medium │          — │ Drop unused index idx_orders_customer_ref on  │
│        │        │            │ orders                                        │
└────────┴────────┴────────────┴───────────────────────────────────────────────┘
```

**Credentials**, resolved in precedence order:

1. `--dsn` — a full database URL.
2. `SQLQUALITY_DSN` — the same, from the environment.
3. `--profile` (with optional `--target` and `--profiles-dir`, default `~/.dbt`) — reads a
   dbt `profiles.yml`. `advise` is not dbt-specific; this is a convenience for projects
   that happen to have one, not a requirement.

The engine is inferred from the DSN scheme (`postgresql://` → `postgres`) or the resolved
dbt adapter type; `--engine` overrides both. The resolved source is always printed to
stderr — `engine: postgres (credentials from --dsn)` — the same discipline `check` uses
for its dialect resolution. Requires the `sqlquality[postgres]` extra (`psycopg`); a
missing driver degrades with an install hint instead of a traceback.

**Flags:**

| Flag | Default | Effect |
|---|---|---|
| `--engine` | inferred | `postgres` or `redshift`. See the Redshift section below for what is and is not proven on that engine. |
| `--dsn` | — | Database URL. Overrides `SQLQUALITY_DSN`. |
| `--profile` | — | dbt profile name, read from `profiles.yml`. |
| `--target` | — | dbt target within the profile. |
| `--profiles-dir` | `~/.dbt` | Directory holding `profiles.yml`. |
| `--project-dir` | — | dbt project dir; reads `target/manifest.json` to enrich proposals (optional). See [dbt enrichment](#dbt-enrichment-optional). |
| `--manifest` | — | Path to a dbt `manifest.json`. Overrides `--project-dir`. |
| `--schema` | `public` | Schema to introspect. Repeat for several: `--schema public --schema sales`. See Limitations for the ambiguity caveat. |
| `--since` | — | Window, e.g. `7d`. **Not honored on Postgres** — see Prerequisites below. |
| `--limit` | `500` | Max query-history rows to read. **On Redshift this counts *executions*, not query groups** — see the Redshift section below. |
| `--min-cost-share` | `0.01` | Suppress proposals below this share of workload cost. Applies to the **cost-weighted** rules (ADV001, ADV004, ADV005, ADV006, ADV007, ADV008, ADV301 — the last only with `--project-dir`/`--manifest`); the index-hygiene rules **ADV002 and ADV003**, and **ADV303** (its evidence is absence, not cost, so there is no share to threshold), carry no cost evidence and are reported whatever the threshold. ADV303 has its own non-threshold suppression: it emits nothing at all when no query usage could be extracted, since then every model would look untouched by definition. |
| `--keep-literals` | off | Do **not** redact literal values from query text. |
| `--timeout` | `30` | Statement timeout in seconds (rejected outside 1–3600). |
| `--dry-run` | off | Print every statement the adapter would issue, then exit 0 **without connecting**. |
| `--json` | off | Emit a machine-readable payload. |
| `--markdown <path>` | — | Write a markdown report. |
| `--ddl <path>` | — | Write proposed DDL for review — sqlquality never executes it. |

**`--dry-run`** is how you verify the read-only claim before `advise` ever touches your
database — it prints the complete, fixed set of statements the adapter can issue and
exits 0 without connecting:

```console
$ sqlquality advise --engine postgres --dry-run
-- workload: requires the pg_stat_statements extension (PostgreSQL 13+) and pg_read_all_stats or superuser; enable via shared_preload_libraries then CREATE EXTENSION. On PostgreSQL 12 and older the view lacks total_exec_time and this will fail.
SELECT s.query, s.calls, s.total_exec_time, s.rows
            FROM pg_stat_statements s
            JOIN pg_database d ON d.oid = s.dbid
            WHERE d.datname = current_database()
            ORDER BY s.total_exec_time DESC
            LIMIT %s

-- stats_reset: reads pg_stat_database; world-readable unless explicitly revoked
SELECT stats_reset
            FROM pg_stat_database
            WHERE datname = current_database()
```

(truncated here; the real output also lists the `schema`, `table_facts`, `ndv` and
`indexes` capabilities). `sqlquality advise --engine redshift --dry-run` works exactly the
same way — no credentials needed, no connection made — and is the recommended way to
review Redshift's introspection SQL yourself (or hand it to a DBA) before trusting it with
a real cluster; see the note at the top of the [Redshift
section](#redshift---engine-redshift) for what is and is not verified about it. `--json`
is honored on `--dry-run` too, so the statement list can
be diffed or fed into review tooling.

**Data protection.** Query history routinely contains personal data inside predicates
(`WHERE email = 'name@example.com'`). Literal values are **redacted at ingest, by
default**: every literal in the parsed query is replaced with a placeholder before
aggregation, before any file is written, before any log line. `--keep-literals` is the
only way to retain them, and the report states which mode produced it. `advise` never
writes to your database — proposed DDL only ever goes to a file (`--ddl`) for you to
review and apply by hand.

One rendering quirk worth knowing before you read a report: `pg_stat_statements` replaces
an interval literal with its own parameter marker (`interval $2`), and sqlglot renders that
back as `INTERVAL '2'`. So `created_at > CURRENT_TIMESTAMP - INTERVAL '2'` in a report
stamped `"redacted": true` means **the interval was parameterised**, not that someone wrote
a two-something interval — the `2` is Postgres's parameter index. Nothing leaked, but the
statement is not valid SQL to copy out and run.

**Prerequisites and limits:**

- **`pg_stat_statements`** must be installed (`shared_preload_libraries` +
  `CREATE EXTENSION`), and the connecting role needs `pg_read_all_stats` or superuser to
  see queries run by other users.
- **PostgreSQL 13+.** `pg_stat_statements.total_exec_time` did not exist before version 13
  (it was `total_time`); older servers fail the workload read outright.
- **`--since` cannot be honored on Postgres.** `pg_stat_statements` is cumulative since the
  last statistics reset and carries no per-statement timestamps before PostgreSQL 17
  (which added `stats_since`). Passing `--since` does not narrow the query — the report
  states the real window instead: `since stats reset at <timestamp>`.

**Proposal codes** (Postgres):

| Code | Proposal | Evidence |
|---|---|---|
| ADV001 | Composite index candidate: hot equality columns, then one range/sort column, arity ≤ 3, and only columns some single query group filters on *together* | cost share, NDV, row estimate, joint co-occurring fingerprint count, absence of a covering index |
| ADV002 | Drop an index with zero recorded scans (excludes unique/primary-key indexes) | scans since last stats reset, size |
| ADV003 | Drop an index whose column list is a strict prefix of a wider index | both column lists |
| ADV004 | Partial index: a hot equality column guarded by a hot, co-occurring `IS [NOT] NULL` check | cost share, co-occurring fingerprint count, absence of a plain index leading with the guarded column |
| ADV005 | Non-sargable predicate — a cast/function on a column, or a leading-wildcard `LIKE` | cost share |
| ADV006 | Hot `SELECT *` on a wide table (≥15 columns) | cost share, column count |
| ADV007 | Add index on a hot join key with no existing index leading with it | cost share, NDV, row estimate, absence of a covering index |
| ADV008 | Composite index for a hot `GROUP BY`, column order inferred from cost, capped at MEDIUM | cost share, row estimate, absence of a covering index |
| ADV301¹ | Materialize a `view`-backed dbt model that carries a hot share of workload cost, capped at MEDIUM | cost share, dbt model |
| ADV303¹ | A dbt model within reach of the manifest that the analyzed workload never touched and no other model, snapshot or exposure declares as a consumer, capped at LOW | dbt model |

¹ Only fires with `--project-dir` or `--manifest` loaded — see [dbt enrichment](#dbt-enrichment-optional).

**ADV302 is not in that table, because it is not a proposal code.** It is a *rewrite*
applied to another rule's proposal — ADV001, ADV004, ADV007 or ADV008 keeps its own code,
confidence and cost share, and only its `ddl` and `rationale` change. So no proposal ever
carries `code: "ADV302"`, and a `--json` consumer filtering on that code sees zero rows on
every run; filter on `evidence.dbt_index_config == true` instead (present, and `true`, only
on a proposal whose DDL was replaced by a dbt config block). The terminal table shows the
original rule's row unchanged, so `advise` prints a line on stderr saying how many proposals
ADV302 rewrote. See [dbt enrichment](#dbt-enrichment-optional).

**Confidence model**, mechanical rather than judgment-based:

- **HIGH** — cost share above `--min-cost-share`, **and** supporting catalog stats present
  (e.g. NDV), **and** confirmation that the proposed index does not already exist.
- **MEDIUM** — cost evidence is solid but a catalog input is missing or stale. ADV002 is
  capped at MEDIUM unconditionally: `idx_scan` only accumulates since the last statistics
  reset, so zero scans can never prove an index is unused across a full business cycle.
  ADV008 is also capped at MEDIUM unconditionally, for a different reason: whether Postgres
  uses the index for grouping depends on its choice between `GroupAggregate` and
  `HashAggregate`, a planner decision driven by `work_mem` and group cardinality that no
  catalog view exposes — HIGH would claim to know the planner's choice, not the catalog's
  state, so ADV008 never reaches it.
- **LOW** — thin evidence, and specifically **any check that could not be run**: the row
  count is unknown so the small-table floor could not be applied, or the existing-index
  list was denied so "no index already covers this" could not be confirmed. Absent
  evidence lowers confidence; it is never assumed away.

Every proposal's evidence renders inline (cost share, calls, fingerprints, row estimate,
NDV, existing index state) so it can be judged from the report alone.

#### Redshift (`--engine redshift`)

Redshift has no indexes at all, so none of ADV001–ADV008 apply. Its physical-design
levers are different, and so is the blast radius: **ADV101, ADV102 and ADV103 each
rewrite the entire table.** Redshift copies every row, holds a lock on the table for the
whole rewrite, and needs roughly the table's own size again in free disk space while it
runs — on a large table that is hours, not seconds. Unlike a Postgres `CREATE INDEX
CONCURRENTLY`, **there is no concurrent-build escape on Redshift**: none of the three can
be applied alongside normal traffic. Schedule them for a maintenance window; do not run
them ad hoc. `--ddl`'s generated script says this loudly, at the top of the file, not only
beside each individual statement.

| Code | Proposal | Table rewrite? | Evidence |
|---|---|---|---|
| ADV101 | `ALTER TABLE ... ALTER SORTKEY`: sort the table on its hottest range/equality predicate column, capped at MEDIUM | **Yes** | cost share, current sort key, `stats_off` staleness |
| ADV102 | `ALTER TABLE ... ALTER DISTKEY`: distribute the table on its hottest join predicate column, capped at MEDIUM | **Yes** | cost share, current distribution style, `skew_rows`, `stats_off` |
| ADV103 | `ALTER TABLE ... ALTER DISTSTYLE ALL`: replicate a small (≤1,000,000-row), frequently-joined dimension to every node, capped at MEDIUM | **Yes** | cost share, row estimate, current distribution style |
| ADV104 | `VACUUM` (unsorted region ≥20%) and/or `ANALYZE` (stale statistics ≥20%), each its own proposal | **No** — reclaims sort order or refreshes statistics in place; no exclusive lock for its duration | `unsorted`, `stats_off` (direct catalog measurements) |
| ADV105 | Amazon Redshift Advisor's own SORTKEY/DISTSTYLE recommendation, relayed verbatim | Whatever Advisor recommends — read its own `note` | attributed as Advisor's, not sqlquality's |

**ADV101, ADV102 and ADV103 can never reach HIGH confidence, by design, not merely by
current implementation.** Whether a SORTKEY, DISTKEY or DISTSTYLE ALL change is actually
worth its rewrite depends on the predicate's selectivity and the table's distribution
skew — and Redshift exposes no per-column distinct-value statistics (no `pg_stats
.n_distinct` equivalent) to measure either. Claiming HIGH would assert something about
data distribution this tool cannot see, while recommending a statement that rewrites the
whole table. ADV104 is the exception: `unsorted`/`stats_off` are direct catalog
measurements, not an inference about data this tool cannot see, and its remediation does
not rewrite anything — so it is also the only Redshift rule that can reach HIGH.

**ADV105 is Redshift Advisor's own recommendation, never sqlquality's inference — and it
says so everywhere a reader might look.** Its title, rationale, evidence
(`evidence.source == "amazon_redshift_advisor"`) and `note` all attribute it explicitly,
and the DDL script marks its header line `(Amazon Redshift Advisor — not sqlquality)` on
top of that — someone skimming only header lines, never the prose, still cannot mistake
an Advisor statement for one this tool generated. When ADV101/102/103 and an Advisor row
agree on the same table and category, the sqlquality proposal's rationale says so as an
added sentence; the two stay separate proposals rather than merging, so it is always
clear which conclusion is whose. When ADV103 (DISTSTYLE ALL) and ADV102 (DISTKEY) both
fire for the same table, only ADV103 survives — replicating to every node already removes
redistribution for every join, which strictly subsumes any single-column DISTKEY choice —
and the surviving proposal says so.

A relation with a hot predicate but absent from Redshift's own physical-design catalog
(`svv_table_info`) gets **no** ADV101/102/103 proposal at all, and the run discloses how
many relations this affected (`reduced coverage — physical_facts_gap: ...`) rather than
silently dropping them: that absence cannot, by itself, tell an external Spectrum table
(which cannot carry a SORTKEY/DISTKEY/DISTSTYLE) apart from a genuinely empty local one,
and proposing a rewrite for something that might not even support one is worse than
proposing nothing.

**The workload can come back silently partial — grant `SYSLOG ACCESS UNRESTRICTED` first.**
`advise` reads `sys_query_history`, and without that privilege Redshift does not deny the
read: it returns **only the connecting user's own queries**. There is no error, no denied
capability and nothing in `degraded` — a cluster whose whole workload is invisible to your
read-only role looks exactly like a quiet cluster with little traffic, and every proposal is
then built from one user's slice of it. Grant it before your first run:

```sql
ALTER USER <your_readonly_user> SYSLOG ACCESS UNRESTRICTED;  -- superuser-only
```

`--dry-run` prints this same warning beside the statement it applies to, and the hint is
also recorded in `degraded` **if** the read is refused outright — but the failure described
here is precisely the one that is never refused, so the hint alone is not disclosure. This
is the same class of trap as Postgres's `pg_stats`, and unlike a missing grant it costs you
coverage rather than a capability.

**`--limit` means executions on Redshift, not query groups.** `sys_query_history` is one
row per *execution*, where Postgres's `pg_stat_statements` is already aggregated per
normalised statement — so `--limit 500` reads the 500 most expensive **executions**, and 500
executions of one bad query is a legal outcome that leaves every other statement unseen.
The `window:` line names what was actually read ("the 500 most expensive successful queries
…"); raise `--limit` if the coverage line shows fewer query groups than you expect.

**dbt interaction.** [ADV302](#dbt-enrichment-optional) rewrites `CREATE INDEX`
proposals into dbt `indexes:` config, which has no Redshift equivalent (SORTKEY/DISTKEY
have no comparable dbt config key modeled by this tool). Rather than leave a dbt-managed
Redshift model's table-rewrite proposal silently unwarned — which would be worse than the
Postgres case ADV302 exists to fix, since the wasted work is hours rather than seconds —
`enrich_proposals`'s existing generic path (built for any DDL that is not `CREATE INDEX`
or `DROP INDEX`) already recognises ADV101–105 and attaches a warning to both the
proposal's `rationale` **and** its `--ddl` `note`: that the relation is dbt-managed, that
the statement is not expressed as dbt config, and that it may not survive the model's next
rebuild. This is not the same thing as the `adapter_type` mismatch warning: a Redshift dbt
project correctly records `adapter_type: redshift`, so that check does not fire — this is
a separate, always-on warning specific to table-rewrite and maintenance statements.

**How overlapping proposals are reconciled.** The rules above are evaluated independently,
but their output is not shipped independently: two of them can reach the same index from
different evidence, and following both would mean creating a redundant pair that ADV003 then
advises dropping on the next run. So before anything is reported:

- **Identical DDL collapses to one proposal.** The higher confidence wins; on a tie a fixed
  rule preference decides, never list order.
- **A narrower index collapses into a wider one.** If one proposal's columns are a leading
  prefix of another's for the same table, only the wider survives — it serves every lookup
  the narrower would. Partial (`WHERE`) proposals never participate: a partial index is a
  different object even when its column list is a prefix.
- **Same columns in a different order are both kept**, each disclosing the other. `(status,
  region)` and `(region, status)` serve different probes, so neither is redundant — but
  creating both means indexing the same columns twice, and the report says so.

Two consequences worth knowing before you consume the output:

- **A rule can fire and still contribute no proposal.** A `--json` consumer counting
  `ADV007` entries can legitimately see zero on a run where ADV007 did propose something
  that was absorbed. The absorbed proposal's rule code, confidence and rationale appear in
  the surviving proposal's `rationale`, attributed — that is where to look for it.
- **The absorbed proposal's `evidence` is discarded, not merged.** Its rationale (the
  constraint an operator needs) is preserved verbatim; its numbers (`leading_ndv`,
  `partial_indexes_skipped`, `co_occurring_fingerprints`) are not carried into the
  survivor's evidence block.

`--ddl` writes a standalone, commented script — never executed by sqlquality:

```sql
-- Generated by `sqlquality advise` — REVIEW BEFORE RUNNING.
-- sqlquality does not execute this script and has not validated it against
-- your workload's write patterns. Each statement is advisory.
--
-- On a live table prefer CREATE INDEX CONCURRENTLY / DROP INDEX CONCURRENTLY:
-- the plain forms below take a lock that blocks writes for the duration.
-- Note that CONCURRENTLY cannot run inside a transaction block, so apply those
-- statements individually rather than piping this whole file into one.

-- ADV001 [high, 67.0% of workload cost]
-- Add index on orders(status)
CREATE INDEX ON "public"."orders" ("status");

-- ADV002 [medium]
-- Drop unused index idx_orders_customer_ref on orders
DROP INDEX "public"."idx_orders_customer_ref";
```

`--json` emits the same evidence as a structured payload (`analyzed`, `degraded`,
`engine`, `physical_state`, `proposals`, `query_groups`, `redacted`, `skipped`, `window`,
plus `dbt` when — and only when — a manifest was loaded). This is the first proposal from
the run above, and the block is abridged in exactly two places: the real payload lists all
five proposals under `proposals`, and the `query_groups` list is trimmed to one entry. The
ADV001 object is complete — its `evidence` key set is the one a real ADV001 carries — but each
rule's `evidence` holds whatever that rule measured, so the key set varies by code.

```console
$ sqlquality advise --dsn postgresql://readonly@db.internal/analytics --json
{
  "analyzed": {
    "query_groups": 3,
    "query_groups_in_window": 3,
    "tables": [
      "public.orders"
    ],
    "total_cost_ms": 925000.0
  },
  "degraded": [],
  "engine": "postgres",
  "physical_state": {
    "public.orders": {
      "indexes": [
        {
          "columns": [
            "id"
          ],
          "is_partial": false,
          "is_unique": true,
          "name": "orders_pkey"
        }
      ],
      "is_ordinary_table": true
    }
  },
  "proposals": [
    {
      "code": "ADV001",
      "confidence": "high",
      "ddl": "CREATE INDEX ON \"public\".\"orders\" (\"status\");",
      "evidence": {
        "calls": 15000,
        "co_occurring_fingerprints": 1,
        "columns": [
          "status"
        ],
        "cost_share": 0.6702702702702703,
        "expression_indexes": [],
        "fingerprint_digests": [
          "d2e8aa0a67af"
        ],
        "leading_ndv": 500.0,
        "partial_indexes_skipped": [],
        "roles": [
          "equality"
        ],
        "row_estimate": 5200000,
        "schema": "public",
        "table": "orders"
      },
      "rationale": "These columns carry the table's hottest predicates and no existing index leads with them. Equality columns come first so the range column can be scanned last.",
      "title": "Add index on public.orders(status)"
    }
    /* … 4 more proposal objects, same shape … */
  ],
  "query_groups": [
    {
      "calls": 15000,
      "digest": "d2e8aa0a67af",
      "mean_ms": 41.333333333333336,
      "total_time_ms": 620000.0
    }
    /* … 2 more query groups, same shape … */
  ],
  "redacted": true,
  "skipped": {
    "noise": 0,
    "unparseable": 0,
    "unqualifiable": 0,
    "ambiguous": 0
  },
  "window": {
    "description": "since stats reset at 2026-07-19 03:00:00+00",
    "engine": "postgres",
    "limit": 500,
    "since": null,
    "since_duration_seconds": null,
    "stats_reset_at": "2026-07-19 03:00:00+00"
  }
}
```

**Three of those keys exist so that [`verify`](#verify) can diff two runs**, and they are the
only part of the payload whose contract is about a *later* run rather than this one:

- **`window`** is an object, not the prose sentence 0.3.0 wrote there: `description` (that
  same sentence, unchanged), `engine`, `stats_reset_at`, `since`,
  `since_duration_seconds` (the *requested* `--since` duration, e.g. `604800.0` for `7d`,
  as distinct from `since`'s absolute cutoff) and `limit`. Postgres reports `since` and
  `since_duration_seconds` as `null` always: it cannot apply `--since` at all, so echoing
  the flag back would claim a filter that was never applied.
- **`physical_state`** records, per `"schema.table"`, what the run's catalog reads already
  saw — no extra round trip. On Postgres that is `is_ordinary_table` plus each existing
  index's `name`, `columns`, `is_partial` and `is_unique`; on Redshift it is
  `is_ordinary_table`, `sortkey1`, `diststyle`, `unsorted` and `stats_off`. Every field is a
  **three-way** signal: `null` means *this run could not tell you* (the relation's facts were
  never fetched, or the read was denied — see `degraded`), while `false`/`[]` is a real
  measurement. `verify` reads a `null` as unknown and never as "no".

  On Postgres `is_ordinary_table: false` means a view, a foreign table or a partitioned
  parent, because the catalog read behind it filters `relkind = 'r'`. **On Redshift the same
  `false` means less**: it comes from the relation's presence in `svv_table_info`, which
  omits external (Spectrum) tables *and* genuinely empty local tables, and nothing available
  distinguishes those — so a Redshift `false` conflates "not a table" with "a table nobody
  has written to yet". The two engines are deliberately not at parity here.
- **`query_groups`** is every query group this run analysed — `digest`, `calls`,
  `total_time_ms` and `mean_ms` (`null`, never `0.0`, when `calls` is `0`) — not only the
  ones some proposal cites, and each index-rule proposal names the ones behind it in
  `evidence.fingerprint_digests`. This is a **different key** from
  `analyzed.query_groups`, which is (and stays) an integer count.

  `analyzed.query_groups` is how many groups were understood; `analyzed.query_groups_in_window`
  is how many the window held.

All three keys are **always present** — `{}`, `[]` and a full object rather than omitted when
empty. That is what lets `verify` tell an artifact written before these keys existed (which it
refuses) from one that genuinely has nothing to report.

**Coverage is always disclosed**, not just when it is bad — the terminal, markdown and
JSON paths all print how many query groups were actually understood:

```console
analyzed 2 of 3 query group(s); skipped 0 unparseable, 0 filtered, 1 unresolvable, 0 ambiguous
low coverage: 33% of candidate statements could not be analyzed (0 unparseable, 1
unresolvable against the schema, 0 ambiguous across the introspected schemas). Cost shares
are computed against the whole window, so they are diluted and --min-cost-share is
effectively stricter — few or no proposals may reflect coverage rather than a healthy
workload.
```

This matters because `cost_share` is **not** a partition of the workload: a query
filtering two columns credits its full cost to *both* entries (proposals take the max
over their columns rather than the sum, since summing would double-count), and the
denominator always includes queries that could not be parsed or resolved against the
schema. Poor coverage silently dilutes every proposal's share rather than inflating it —
read the skip counts alongside every proposal.

Running with more than one `--schema`, a bare, unqualified table name held by two or more
of the introspected schemas is genuinely ambiguous — attributing it to either would be a
guess, so it is dropped and counted rather than guessed at:

```console
2 statement(s) named a table held by more than one of the introspected schemas without
qualifying it, so they could not be attributed and were dropped. Qualify the table in the
query, or run advise once per --schema.
```

A missing grant degrades one capability at a time rather than aborting the whole run:

```console
reduced coverage — ndv: permission denied for table pg_stats — reads pg_stats, which
exposes only rows for tables the current role owns or can select from — a role without
table access silently sees no statistics
```

#### dbt enrichment (optional)

Passing `--project-dir` (reads `<project-dir>/target/manifest.json`) or `--manifest
<path>` layers dbt model metadata onto the same analysis. Neither is required: every
`advise` invocation without one behaves exactly as documented above, and that no-manifest
path is proven byte-identical (stdout, markdown, DDL and stderr) to a run with no dbt
support at all — dbt is enrichment layered on top of an engine-agnostic core, never a
requirement of it.

**Why ADV302 exists.** The rules above propose DDL from query cost and catalog metadata
with no idea whether the table they're indexing is dbt-managed — and if it is, that
matters. dbt's `table` materialization drops and recreates its relation on *every*
`dbt run`, so a raw `CREATE INDEX` applied once is silently gone the next time dbt runs.
`incremental` differs only in degree: a normal run keeps the relation, but
`dbt run --full-refresh` rebuilds it the same way. `materialized_view` behaves like
`incremental` — refreshed in place on a normal run, rebuilt on `--full-refresh` or a config
change dbt can't apply in place. A plain `view` has no storage of its own at all, so it
cannot carry an index. Confidently advising DDL that a routine `dbt run` silently erases is
worse than advising nothing, which is what **ADV302** exists to prevent: with a manifest
loaded, an index-creating proposal for a `table`-, `incremental`- or
`materialized_view`-materialized relation is rewritten into a commented dbt `indexes:`
config block you paste into that model's own config instead of DDL you'd apply once and
lose; on a `view` the **DDL** is dropped and explained instead (there is no relation to
index) while the proposal itself stays, downgraded to LOW — "this index cannot apply here"
is the finding; on any other or absent materialization the DDL is left untouched, since
unrecognised is not the same as known-safe. A partial (`WHERE`-restricted) index has no
config-block equivalent — dbt's `indexes` config carries no predicate — so that proposal is
disclosed as not expressible rather than silently dropping the predicate.

**One model, one `indexes:` block.** dbt reads a single `indexes` key per model config, so
when a run recommends several indexes for the same model they are merged into one block,
carried by the highest-ranked of those proposals; each of the others points at it by code
instead of emitting a block of its own. Two standalone blocks pasted under one `config:` are
a duplicate YAML mapping key, and PyYAML — dbt's own parser — resolves that by silently
keeping one and discarding the other recommended index, with no error.

**Whenever a statement is left executable for a dbt-managed relation** — the partial-index,
unrecognised-materialization, no-column-list and non-btree paths above — the warning is
written into the `--ddl` script itself, as comment lines directly above the statement, not
only into the `rationale`. The DDL script carries no rationales, and it is the artifact a
human actually applies.

**A `DROP INDEX` proposal on a dbt-managed relation is the same hazard pointing the other
way.** ADV002 and ADV003 read the catalog, not the manifest, so they will propose dropping an
index that the model's `indexes:` config still declares — and the next `dbt run` puts it
straight back, after which the tool proposes the same drop again. Those proposals keep their
DDL (dropping a genuinely unused index is still right, and dbt's `indexes` config cannot
express a removal) and gain a warning, in the rationale *and* in the `--ddl` file, that the
config entry has to be removed as well or the drop will not stick.

**`advise` checks the manifest against the connection**, the same two checks `check` makes on
the same file: it warns when the manifest is not a v12 schema, and when its `adapter_type` is
neither `postgres` nor `redshift`. The second matters more than the missing `indexes:` config
key would suggest: a Snowflake or BigQuery manifest paired with a Postgres connection means
dbt is not building the relations `advise` just introspected *at all*, so every match is a
name coincidence and all three dbt rules are wrong — ADV302's premise that a `dbt run`
rebuilds the relation included. A manifest recording **no** `adapter_type` warns too, since
`dbt compile` always writes one and the honest statement is that the pairing could not be
checked. `advise` warns rather than suppressing: the mismatch is something to fix in your
invocation, and dropping all dbt output silently would hide it.

**The block is rebuilt from the proposal's column list, not from its DDL**, and always as
`type: btree`. That is faithful for every rule shipping today — each emits a plain btree over
a column list with no `USING`, expression, `DESC`/`NULLS` or opclass — and a proposal naming a
non-btree access method declines the rewrite rather than being flattened into a btree.
Ordering, opclasses and expression indexes are *not* detected: a future rule emitting one
would need this reconstruction extended alongside it.

Two more proposals only fire with a manifest loaded — see the proposal table above for
ADV301 and ADV303. Both are capped below HIGH, for the same reason ADV302's rewrite trusts
the manifest as of whenever `dbt compile` last ran: a model's materialization or its
consumers can change without a fresh compile, so a stale manifest degrades to a wrong (but
traceable — the disclosed materialization or lack of a consumer names why) recommendation
rather than a silent one.

**Matching is exact, deliberately.** A model's `relation_name` is dropped down to its
`(schema, table)` pair (dbt writes a `catalog.schema.table` name; the database part is
discarded, since `advise` connects to one database at a time) and matched against the
relation each proposal already carries — **there is no bare-table-name fallback**. A dbt
project's target schema (`dev`, `main`, a CI schema, ...) routinely differs from the schema
`advise` introspects in production, so matching on the table name alone would risk
attributing a production table's proposal to an unrelated development model — and ADV302
would then rewrite that table's DDL on the strength of a wrong guess. If two *different*
models both build the same `(schema, table)` pair (legitimate when a project targets more
than one database), `advise` cannot tell which is live: that relation is dropped from
matching entirely — not guessed at — and counted, both in the CLI's `dbt enrichment from
...` disclosure line and in the JSON payload's `dbt.dropped_collisions`.

```console
$ sqlquality advise --dsn postgresql://readonly@db.internal/analytics --project-dir ./my_dbt_project
engine: postgres (credentials from --dsn)
dbt enrichment from my_dbt_project/target/manifest.json (42 model(s))
...
```

A manifest that is missing, unreadable or malformed degrades to "no enrichment" plus a
line on stderr — `advise` never aborts an otherwise-successful run over an optional input,
since by the time the manifest loads the whole catalog analysis has already run.

### verify

Closes `advise`'s feedback loop. Every proposal `advise` makes is a hypothesis; `verify`
diffs a baseline `advise --json` artifact against a later one and reports, per proposal,
whether the advice was **applied** and whether the queries that justified it actually got
**faster**.

It is **fully offline**: it reads two files, opens no connection and needs no credentials,
so anyone reviewing a change can run it — including people who will never have production
access. The baseline is an ordinary `advise --json` run; there is no separate file format.

```console
$ sqlquality advise --dsn postgresql://readonly@db.internal/analytics --json > before.json
# ... create the proposed index, let the workload run ...
$ sqlquality advise --dsn postgresql://readonly@db.internal/analytics --json > after.json
$ sqlquality verify before.json after.json
Window relation: nested — both runs report the same stats_reset_at and neither restricted
its window, so the after run's cumulative pg_stat_statements counters contain the before
run's. Every pre-change execution is still averaged into the after mean, so a real
improvement is understated here, sometimes badly: a proposal that genuinely helped can read
as 'unchanged'. Verdicts are capped at medium confidence for that reason. For an undiluted
comparison, call pg_stat_statements_reset() yourself right after applying a change and take
the after artifact from there — sqlquality never writes to your database, this reset
included.
Run order is taken from the argument order: BEFORE, then AFTER. ...
workload: before 5000.0 ms across 1 query group(s); after 2000.0 ms across 1 query group(s)
             Verify — postgres (1 proposal, 1 applied, 1 improved)
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━┓
┃ proposal                      ┃ applied ┃ outcome  ┃ mean per call ┃ conf   ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━┩
│ ADV001 public.orders (status) │ yes     │ improved │ 50.0 → 2.0 ms │ medium │
└───────────────────────────────┴─────────┴──────────┴───────────────┴────────┘
```

The table, the per-proposal notes and the disclosures above go to stdout and stderr
respectively; `--json` emits the same content (verdicts, counts, window relation and every
caveat) as one machine-readable payload, and `--markdown PATH` writes a report for a ticket
or PR comment. `verify` **reports and never gates**: exit 0 whenever a comparison was
reported, exit 2 when it was refused, never 1. There is deliberately no `--gate` flag.

**`applied` is observed, not declared.** It comes from the physical state each run recorded
(`physical_state` in the artifact) — an index that now leads with the proposed columns, a
dropped index that is gone, a `sortkey1`/`diststyle` that matches the *proposed target*
rather than merely having changed. `applied: unknown` is a distinct answer from
`applied: no`: "we could not tell whether you did it" is not "you did not do it", and the
rules that have nothing observable at all (ADV005 and ADV006 advise a query rewrite, ADV303
needs a dbt manifest an offline command does not have) are always `unknown` rather than
guessed at.

**Outcomes** are `improved`, `unchanged`, `regressed`, `disappeared`, `not_applied` and
`unobservable`. The most valuable is **applied but unchanged** — the work was done and it
did not help, which the note says in as many words.

**Mean time per call is the metric; `cost_share` is not.** `pg_stat_statements` is
cumulative and carries no per-statement timestamps, so a group's share of the window falls
simply because a week of other traffic accumulated. `cost_share` is the right metric for
*prioritising* work (which is why `advise` uses it) and close to useless for *measuring* an
improvement, so it rides along as context — whether the finding still matters — and never as
the verdict. The workload-context line prints both runs' total window cost and group count
so a global workload shift is visible rather than deduced.

**Confidence comes from how comparable the two windows are:**

| windows | grade | why |
|---|---|---|
| disjoint (the counters were reset between the runs) | high | independent samples |
| comparable duration (both runs requested the same `--since`) | high | the same window length by construction |
| nested (Postgres cumulative, never reset) | medium | pre-change executions dilute the mean, so a real gain is **understated** |
| incomparable (a one-sided or mismatched `--since`, an unknown `stats_reset_at`) | low | the windows cannot be placed relative to each other |

The **nested** case is the common one — you baselined last Tuesday and never reset the
counters — and it is the one to know about: a proposal that genuinely helped can read as
`unchanged` there. `verify` prints the caveat on every such run and suggests
`pg_stat_statements_reset()` for an undiluted comparison. sqlquality never runs it for you;
it never writes to your database.

**Run order is taken from the argument order** (`BEFORE` first). An `advise` artifact
carries no run timestamp — deliberately, so that two runs over an unchanged workload produce
comparable bytes — so a swapped pair is only detectable in one case, which is refused: both
runs report a `stats_reset_at` and the after run's is *earlier*, which cannot happen, since a
server's statistics-reset instant does not move backwards. Otherwise `verify` says it cannot
tell rather than inferring an order it has no evidence for.

**`verify` refuses rather than guesses** — exit 2, naming the cause:

| refused | why |
|---|---|
| an unreadable, non-UTF-8, malformed, or non-object JSON file | it is not an `advise --json` artifact |
| an artifact missing the keys 0.4.0 added (`window` as an object with all its fields, `physical_state`, `query_groups`) | 0.3.0 and intermediate builds wrote less; regenerate the baseline rather than let a verdict rest on absent data |
| the same artifact twice — identical path, or byte-identical content | it would report every proposal unchanged, which reads as a finding rather than a mistake; two genuinely distinct runs cannot be byte-identical, since the counters accumulate |
| two artifacts from different engines | they describe two different servers, so nothing in one corresponds to anything in the other |
| two runs that disagree about `--keep-literals` | redaction changes the canonical query text every digest is computed from, so the same query group is recorded under different identifiers; its "absence" would be a fact about the flag, not about your database |
| a demonstrably swapped pair (see above) | the comparison would be reversed |

Everything else that weakens the comparison is **disclosed rather than folded into a
verdict**: a `--limit` mismatch (a group missing from one artifact may be a sampling
artifact rather than a real disappearance, so `disappeared` is not graded on that alone),
each run's `degraded` capabilities (a read that could not run produces the same emptiness a
real change would), recommendations whose key matched more than one proposal *within their
own artifact* (reported as unmatched — neither disappeared nor new), and proposals only the
after run makes (no verdict, because there is nothing to compare them against).

**An after-only proposal is only called a *new* finding when both runs' coverage supports
that.** Three things withhold the claim:

- the **before** run's reads were degraded, so it may never have had the evidence to make that
  recommendation — its absence is then a fact about that run, not about your database;
- the **before** run's window sampled fewer (or an unknown number of) query groups, for the
  same reason;
- the **after** run's reads were degraded in a way that can *relax* a rule rather than silence
  it. A rule that cannot evaluate a threshold proposes anyway at reduced confidence — the right
  posture for `advise`, which discloses rather than withholds — so a run that could not read
  table sizes, or could not see an index that already covers the predicate, can make a
  recommendation a fully-observed run would not have made at all.

In each case `verify` still lists those proposals and says why it will not call them new. This
is the same treatment a query group's absence from the *after* run already gets before
`disappeared` may be graded; the directions of the same reasoning are deliberately symmetric.

### check (the CI gate)

Scores each changed model on both a candidate and a baseline dbt manifest, and gates
the change on the per-model **complexity delta**.

Requirements:

- **dbt >= 1.5 on `PATH`** (override the executable with `--dbt`). `check` shells out
  to `dbt ls --select state:modified` to discover changed models.
- A **compiled candidate manifest** at `<project-dir>/target/manifest.json` — run
  `dbt compile` first (the gate scores compiled SQL; uncompiled models are skipped).
- A **baseline artifacts directory** (`--state`) containing the prior
  `manifest.json` to diff against.

The dialect is auto-resolved from the manifest's `adapter_type` (falling back to
`postgres`), and printed to stderr; pass `--dialect` to override. `--state` and
`--project-dir` are resolved to absolute paths, so `check` works from a monorepo root.

```console
$ sqlquality check --project-dir . --state prod-artifacts/
dialect: postgres (from manifest adapter_type)
          sqlquality: ❌ FAIL  (changed 1, neighbors 2)
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━┳━━━━┓
┃ model                      ┃ baseline ┃ candidate ┃ delta ┃    ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━╇━━━━┩
│ model.demo.customer_orders │     11.2 │      17.6 │  +6.4 │ ⚠️ │
└────────────────────────────┴──────────┴───────────┴───────┴────┘
```

Whether a regression **fails** the build depends on `gate.mode` (see
[Configuration](#configuration-sqlqualityyml)). In the default `warn` mode the same
change reports the regression but exits 0:

```console
$ sqlquality check --project-dir . --state prod-artifacts/
 sqlquality: ⚠️ WARN (1 regression, gate mode: warn)  (changed 1, neighbors 2)
...
```

`--json` emits the full gate report (verdict, per-model deltas, neighbors, skipped
models):

```console
$ sqlquality check --project-dir . --state prod-artifacts/ --json
{
  "mode": "fail",
  "models": [
    {
      "baseline": 11.2,
      "candidate": 17.6,
      "delta": 6.4,
      "is_new": false,
      "unique_id": "model.demo.customer_orders"
    }
  ],
  "neighbors": [
    "model.demo.orders",
    "model.demo.stg_orders"
  ],
  "passed": false,
  "regressions": [
    "model.demo.customer_orders"
  ],
  "skipped": [],
  "warned": false
}
```

`--markdown <path>` writes a report suitable for a PR comment, and `--html <path>`
writes a self-contained HTML report. The markdown looks like:

```markdown
# sqlquality: ❌ FAIL

| model | baseline | candidate | delta | |
|---|---:|---:|---:|:--:|
| model.demo.customer_orders | 11.2 | 17.6 | +6.4 | ⚠️ |
```

## Configuration (`sqlquality.yml`)

`check` reads `<project-dir>/sqlquality.yml` by default, or the path given to
`--config`. All keys are optional; absent files use the defaults below.

| Key | Type | Default | Meaning |
|---|---|---|---|
| `gate.mode` | `warn` \| `fail` | `warn` | `warn` reports regressions but exits 0; `fail` exits 1 on any regression. **The default `warn` does not fail CI** — set `fail` to actually gate. An invalid value is rejected with exit 2. |
| `gate.max_complexity_increase` | float | `10.0` | A model is a regression when its delta exceeds this threshold. New models (no baseline) are never counted as regressions. |
| `waivers` | list of strings | `[]` | Model `unique_id`s exempt from the gate. |

A complete example:

```yaml
gate:
  mode: fail
  max_complexity_increase: 10.0
waivers:
  - model.my_project.legacy_wide_fact
  - model.my_project.known_gnarly_rollup
```

## Exit codes

Every command follows the same contract:

| Code | Meaning |
|---|---|
| `0` | Pass / no findings. |
| `1` | Findings present, or the gate failed. |
| `2` | Usage, config, or input error (bad flag, unknown dialect, unparseable SQL, unreadable file, malformed `sqlquality.yml`, dbt invocation failure). |

Per-command nuances of code `1`:

- **`complexity`** never gates — it always exits 0 unless the input errors (2).
- **`lint`** exits 1 on any `WARNING`/`ERROR` finding; `info`-level (unresolved-Jinja)
  findings never gate; `--warn-only` forces 0.
- **`perf`** exits 1 only on an `ERROR`-severity finding (unparseable SQL). Anti-pattern
  warnings exit 0.
- **`check`** exits 1 only when `gate.mode: fail` and a regression is present; `warn`
  mode exits 0 even with regressions.
- **`advise`** exits 0 on any successful analysis, whether or not proposals were
  produced — proposals are advisory and never gate. It exits 2 on a usage, config,
  connection or input error (unresolvable credentials, connection failure, missing
  driver, malformed `--since`, out-of-range `--timeout`). It never exits 1.
- **`verify`** exits 0 whenever a comparison was reported, whatever the verdicts say — a
  regression does not gate, and there is deliberately no `--gate` flag. It exits 2 when it
  refuses the pair (see [verify](#verify) for the full list: an unreadable or pre-0.4.0
  artifact, the same artifact twice, two engines, two redaction settings, a demonstrably
  swapped pair) or when a `--markdown` path cannot be written. It never exits 1.

## CI recipe (a gate that actually gates)

To make CI fail on a complexity regression you must (1) set `gate.mode: fail` in
`sqlquality.yml`, (2) `dbt compile` so the candidate manifest exists, and (3) provide
a baseline (`--state`) produced from your production `dbt compile` artifacts.

```yaml
# sqlquality.yml (committed to the repo)
gate:
  mode: fail
  max_complexity_increase: 10.0
```

```yaml
# .github/workflows/sqlquality.yml
name: sqlquality
on: pull_request

jobs:
  gate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v5

      # Fetch the baseline artifacts your production run published.
      # These must come from `dbt compile` (a compiled manifest.json), not a bare parse.
      - name: Download baseline artifacts
        run: ./scripts/download-prod-artifacts.sh prod-artifacts/

      # Produce the candidate manifest for the PR.
      - name: dbt compile
        run: uv run dbt compile

      - name: sqlquality gate
        run: >
          uv run sqlquality check
          --project-dir .
          --state prod-artifacts/
          --markdown report.md

      - name: Comment report on the PR
        uses: actions/github-script@v7
        if: always()   # post the report even when the gate fails
        with:
          script: |
            const body = require('fs').readFileSync('report.md', 'utf8')
            github.rest.issues.createComment({
              ...context.repo,
              issue_number: context.issue.number,
              body,
            })
```

Baseline hygiene: the baseline `manifest.json` must be a **compiled** artifact
(`dbt compile` output). A parse-only manifest lacks `compiled_code`, so those models
are skipped rather than scored.

## Pre-commit hook

`sqlquality` ships a [pre-commit](https://pre-commit.com) hook that lints staged SQL:

```yaml
# .pre-commit-config.yaml
repos:
  - repo: https://github.com/hanslemm/sqlquality
    rev: v0.2.0
    hooks:
      - id: sqlquality-lint
```

The hook runs `sqlquality lint` on staged `.sql` files and excludes `target/`. It
lints **raw model files** (not compiled SQL), so unresolved-Jinja findings are demoted
to `info` and don't block the commit — only real `WARNING`/`ERROR` findings do.

To make the hook non-blocking (report only), pass `--warn-only`:

```yaml
      - id: sqlquality-lint
        args: [--warn-only]
```

## LLM suggestions (optional, advisory)

`perf --suggest` can attach a short, concrete rewrite suggestion to each finding using
an LLM. It is **off by default** and **advisory only** — suggestions never change
findings, severities, exit codes, or the gate.

Setup:

1. Install the extra: `pip install "sqlquality[llm]"`.
2. Set `SQLQUALITY_LLM=anthropic` (also accepts `1` or `true`).
3. Provide `ANTHROPIC_API_KEY` (read by the Anthropic SDK).
4. Optionally set `SQLQUALITY_LLM_MODEL` to override the model (the built-in default is
   `claude-opus-4-8`).

```bash
export SQLQUALITY_LLM=anthropic
export ANTHROPIC_API_KEY=sk-ant-...
sqlquality perf model.sql --suggest
```

If `--suggest` is passed without `SQLQUALITY_LLM` set, `perf` prints a note to stderr
and continues without suggestions. If the extra or credentials are missing, it
degrades gracefully (findings still print, exit code unchanged):

```
LLM suggestions unavailable: The 'anthropic' package is required for AnthropicProvider. Install it with: pip install 'sqlquality[llm]'
```

> **⚠️ Data egress warning.** `perf --suggest` sends the analyzed SQL (up to 20,000
> characters per finding) to the Anthropic API. Do **not** enable it on proprietary or
> sensitive SQL without clearance. API cost scales with the number of findings (one
> call per finding).

## Limitations

- **Complexity is structural.** The composite is an open-ended, weighted score of
  AST features (joins, CTEs, subqueries, windows, select depth, …); it is not capped,
  so a large model can exceed 100. As a rough guide, ~100 is very complex. It measures
  shape, not runtime cost or correctness.
- **`perf` is static.** Anti-patterns and captured-`EXPLAIN` ingestion only —
  `sqlquality` never runs your queries in this command. Perf adapters exist for
  **postgres** and **redshift** only today.
- **Jinja analysis is approximate.** Raw dbt models are analyzed by stripping Jinja to
  placeholders (with a stderr notice). Prefer compiled SQL from `target/compiled/` for
  accurate results.
- **Neighbors are reported, not scored.** A changed model's direct upstream/downstream
  models are surfaced for context; the gate only evaluates the changed models
  themselves.
- **`advise` proposals are ranked by evidence, not proven.** A HIGH-confidence proposal
  is well-supported, not guaranteed correct — it is still advice to review, not a
  decision already made.
- **Index write cost is not modeled.** Proposals weigh read-side benefit (cost share,
  selectivity) against the fact that an index exists; they do not estimate the ongoing
  cost of maintaining it on every write. A hot write path with many proposed indexes
  needs a human judgment call `advise` does not make.
- **Conclusions are only as representative as the log window.** `pg_stat_statements` is
  cumulative since the last statistics reset, with no per-statement timestamps before
  PostgreSQL 17. A reset an hour ago produces a confident-looking report over an hour of
  traffic; the report's `window:` line is the only way to know which you have.
- **`cost_share` is not a partition.** A query filtering two columns credits its full
  cost to *both* — summing across columns double-counts. The denominator also includes
  statements that could not be parsed or resolved against the schema, so poor coverage
  dilutes every share and makes `--min-cost-share` effectively stricter; the CLI warns
  when coverage is poor, and the report always prints the skip counts.
- **Join keys and grouping columns are measured and read, not just cost-weighted.**
  `advise` classifies eight column roles; join keys are read by ADV007 (a hot unindexed
  foreign-key join produces a proposal, not just an `orders.customer_id join cost_share
  1.0` line that goes nowhere) and `GROUP BY` columns are read by ADV008, as one composite
  index rather than one per column — `GROUP BY a, b` needs input sorted by `(a, b)`, and
  two single-column indexes cannot provide that. Any column under a `JOIN` is classified
  as a join key, so a predicate you placed in an `ON` clause (as `LEFT JOIN` semantics
  require) is not treated as a filter and drops out of ADV001's reach — it is ADV007's
  candidate instead. ADV008's column order within the composite is inferred from cost,
  not read from the query, because redaction does not preserve each column's position in
  the `GROUP BY` clause — check it against the actual grouping before applying.
- **A declared cursor's predicates are analyzed, but its cost usually reads as zero.**
  `DECLARE cur CURSOR FOR SELECT ... WHERE ...` and `COPY (SELECT ... WHERE ...) TO STDOUT`
  are unwrapped to their inner query before the noise filter runs, so both reach
  `aggregate` — Django's `QuerySet.iterator()` and every psycopg2 server-side cursor emit
  the first form, so on a Django codebase this can be most of your hot reads. `COPY (...)
  TO` attributes correctly: Postgres charges the whole execution's time and rows to the
  `COPY` statement. A `DECLARE`, measured on PostgreSQL 16, does not — opening a cursor
  does no scanning, so while it is counted accurately by *call count* (one call per cursor
  opened), its time and rows read as near-zero; the actual work is charged to the `FETCH`
  statements that follow, which carry no query text and stay filtered as noise. So a
  cursor read's columns can still join an index candidate, but the read cannot earn a
  proposal on cost alone, and the default `--min-cost-share` can suppress it outright.
- **A `COPY (...) TO` execution can be counted twice under `pg_stat_statements.track =
  all`.** That setting (not the default `track = top`) makes Postgres record both the
  verbatim top-level `COPY` statement and its normalised nested query as separate rows for
  the same execution, and `unwrap`/redaction give the pair different fingerprints (a real
  literal in one, `$1` in the other) — so it lands in `aggregate` as two query groups at
  roughly twice the execution's true cost, inflating both that group's `cost_share` and the
  whole-window denominator. This is accepted rather than fixed, **for a stated price rather
  than for want of a way to fix it**. A blanket `AND s.toplevel` is not the answer:
  `toplevel = false` is also the *only* way Postgres ever exposes the SQL executed inside a
  PL/pgSQL function body, and tried live it made a genuinely hot, function-wrapped query
  disappear from evidence entirely (no predicates, no cost, no disclosure that anything was
  dropped) while a much colder query took its place as a `high`-confidence proposal —
  confidently wrong, which is strictly worse than an inflated `cost_share`. A *narrow*
  predicate does exist, though, and was measured to work: on PostgreSQL 16 the COPY's nested
  row keeps its wrapper (`COPY (SELECT ... $1) TO STDOUT`) while a function body is recorded
  bare, so `NOT (s.toplevel = false AND s.query ~* '^\s*COPY\s*\(')` removes exactly the
  duplicate and leaves function bodies alone. It is declined because *naming* `s.toplevel`
  at all requires PostgreSQL 14 — the column does not exist on 13 — so adding it would cost
  every PostgreSQL 13 user the entire workload capability in exchange for removing a 2×
  over-count of one statement form under a non-default setting. If the supported floor ever
  rises to 14, that is the predicate to add.
- **Every PL/pgSQL function call is counted twice under `pg_stat_statements.track = all`,
  and no predicate can fix it.** That setting records both the calling statement and each
  statement inside the function body: on one PostgreSQL 16 run, a single execution of
  `SELECT lc.hot()` appeared as the call at 68.21 ms *and* its body at 67.67 ms — the two
  durations track each other, so the absolute figures vary per machine. Both land in
  the whole-window denominator, so on a function-heavy workload every `cost_share` is
  roughly halved and `--min-cost-share` is correspondingly stricter than it looks. Unlike
  the `COPY` case above there is no filter that helps: the call carries the cost while the
  body carries the predicates a proposal is built from, so dropping either row loses
  something real. On the default `track = top` neither this nor the `COPY` duplicate arises,
  because Postgres records no nested statements at all — if you run `track = all`, read
  `cost_share` as a lower bound.
- **Expression indexes are read but not matched.** `advise` now sees that an index on
  `lower(status)` exists and names it in the proposal's evidence, but it cannot tell whether
  that index already serves a lookup on `status` — so it proposes and says so, rather than
  suppressing or ignoring. Confirm before applying. True of all three index-creating rules,
  ADV001, ADV007 and ADV008.
- **ADV003 only compares plain indexes.** A pair where either index carries a `WHERE`
  predicate or an indexed expression is skipped entirely rather than proposed at lower
  confidence: a partial index exists to serve a subset, so recommending its removal is
  likely wrong rather than merely uncertain. Plain pairs are reported at HIGH.
- **Both `DROP INDEX` rules only look at tables the workload actually used.** ADV002 and
  ADV003 iterate the relations that appear in the analyzed query groups, so an index on a
  table no observed statement touched is never proposed for removal — including when it sits
  in a second `--schema` whose table happens to share a bare name with a hot one.
- **A partial index does not suppress a proposal.** `idx ON orders(status) WHERE
  shipped_at IS NULL` does not serve `WHERE status = $1`, so it is not treated as covering
  a candidate index — it is named in the evidence instead. True of all three index-creating
  rules, ADV001, ADV007 and ADV008.
- **Multiple `--schema` values are supported, with one honest caveat.** Every catalog fact
  (table sizes, NDV statistics, index lists, the `qualify()` schema) is keyed by
  `schema.table`, so `orders` in two introspected schemas no longer aliases into one
  another. What remains is genuine ambiguity in the *query text* itself: a statement that
  says `from orders` bare, when two of the introspected schemas both hold `orders`, cannot
  be attributed to either without guessing — it is dropped and counted rather than guessed
  at (see `ambiguous` in the skip counts, and the coverage warning that names the remedy).
  Qualify the table in the query, or run `advise` once per `--schema`, to recover it.
  Generated DDL is qualified with the schema it was read from, so it does not depend on the
  applying session's `search_path`.
- **Snowflake is designed but not implemented.** `advise` supports `postgres` and
  `redshift` today; passing `--engine snowflake` (or anything else unrecognised) fails
  with a clear error rather than silently degrading.
- **Redshift's catalog SQL has not been executed against a live Redshift cluster.** See
  the prominent note at the top of the [Redshift section](#redshift---engine-redshift):
  the connection path is verified live (Redshift speaks the Postgres wire protocol), every
  statement's syntax and bindability are verified live, but the column names and the
  resulting proposals' semantics come from AWS documentation, not an observed row. Run
  `--dry-run` first and review before connecting to a production cluster.
- **Redshift declares no NDV and no index capability**, deliberately: Redshift exposes no
  `pg_stats.n_distinct` equivalent, and it has no indexes at all — its levers are SORTKEY,
  DISTKEY/DISTSTYLE and VACUUM/ANALYZE staleness. This is why ADV101/102/103 can never
  reach HIGH confidence (see the [Redshift section](#redshift---engine-redshift)), not a
  gap left for a later release.
- **A relation absent from `svv_table_info` is ambiguous, not conclusive.** Redshift omits
  both external (Spectrum) tables and genuinely empty local tables from that view, and
  nothing else this adapter reads can tell the two apart — so a relation missing from it
  gets no ADV101/102/103 proposal at all rather than a guess either way, and the run
  discloses how many relations this affected.
- **Redshift's workload is silently partial without `SYSLOG ACCESS UNRESTRICTED`.**
  `sys_query_history` returns only the connecting user's own queries to a role lacking that
  privilege, and it does so with **no error at all** — so a cluster whose traffic your
  read-only role cannot see is indistinguishable from a quiet one, and every `cost_share` is
  computed over one user's slice. This is the one Redshift failure mode with no signal
  anywhere in the run; see the [Redshift section](#redshift---engine-redshift) for the grant.
- **`--limit` counts executions on Redshift and query groups on Postgres.**
  `sys_query_history` is per-execution; `pg_stat_statements` is pre-aggregated per normalised
  statement. So on Redshift `--limit 500` means "the 500 most expensive executions", and 500
  executions of a single bad query is a legal outcome that hides every other statement. The
  `window:` line always says which was read.
- **Identifier case and attached comments can split one Redshift statement into several
  query groups.** `sys_query_history` stores the *verbatim* text the client sent, unlike
  `pg_stat_statements`, which Postgres has already parsed and re-serialised (identifiers
  folded to lowercase) before storing. So two executions of what is semantically one
  statement still fingerprint separately when they differ only in identifier case or in an
  attached comment — an ORM query tag, for instance. That inflates the number of query groups
  the window's total cost is spread over, which shrinks every `cost_share` and makes
  `--min-cost-share` correspondingly stricter, in the same way the `cost_share` and PL/pgSQL
  caveats above do. Not "fixed" by case-folding before fingerprinting: nothing there can tell
  an unquoted (case-insensitive) identifier from a deliberately quoted, case-sensitive one, so
  a general fold risks collapsing a real distinction instead of a spurious one.
- **dbt enrichment trusts the manifest as of its last `dbt compile`.** ADV302 rewrites DDL
  based on a model's materialization as the manifest records it; a materialization changed
  without a fresh `dbt compile` produces a stale — but traceable, since the disclosed
  materialization names its own source — rewrite. Nothing verifies the manifest against
  the live relation.
- **A manifest for another warehouse is warned about, not rejected.** `advise` connects to
  Postgres; a manifest whose `adapter_type` is something else (or absent) gets a stderr
  warning and enrichment still runs, so a project whose manifest and target database disagree
  gets dbt proposals built on `(schema, table)` name coincidences. dbt's `indexes` model
  config is likewise implemented by the postgres and redshift adapters only, and ADV302's
  rewrite is not translated per adapter.
- **ADV302 reconstructs the index from the proposal's column list.** The emitted block is
  always `type: btree` over that column list; column *ordering* is preserved but opclasses,
  `DESC`/`NULLS` and expression indexes are not expressible, and a non-btree access method
  declines the rewrite rather than being silently flattened. That last check is textual (no
  rule records an access method in evidence), so a column whose *name* contains a `USING`
  clause declines a rewrite that would have been fine — the safe direction.
- **ADV303 only looks at a model's immediate consumers.** A dead model feeding another dead
  model is not reported until the downstream one is gone, so a fully dead chain unwinds one
  model per run, from its leaf. Conservative by construction: it never flags a model that
  something declares a dependency on.
- **`verify` cannot tell which of two artifacts is older, except in one case.** An `advise`
  artifact carries no run timestamp — deliberately: two runs over an unchanged workload
  produce comparable bytes, which is what makes them diffable at all — so `verify` takes run
  order from the argument order. The one detectable swap is refused: both runs report a
  `stats_reset_at` and the after run's is earlier, which a server cannot do. Pass the earlier
  artifact first; nothing in the pair will catch it for you if you do not.
- **On the common nested-window Postgres path `verify` understates a real improvement.**
  `pg_stat_statements` is cumulative, so unless the counters were reset between the two runs
  the later window contains the earlier one and every pre-change execution is still averaged
  into the after mean. A proposal that genuinely helped can therefore read as `unchanged`.
  This is disclosed on every such run and capped at medium confidence rather than engineered
  around, because the alternative is writing to your database: `verify` suggests
  `pg_stat_statements_reset()` and never performs it.
- **`verify` cannot observe whether ADV005, ADV006 or ADV303 were acted on.** The first two
  advise a query rewrite, whose only trace is the group's fingerprint changing because the
  SQL changed; ADV303 needs a dbt manifest an offline command does not have. Their verdict is
  `unobservable`, and a vanished query group is reported as *possibly* addressed at low
  confidence — disappearance is not proof.
- **`verify`'s Redshift verdicts are weaker than its Postgres ones, beyond the standing
  "never run against a live cluster" caveat.** ADV002/ADV003/ADV104/ADV105/ADV301 name no
  individual query group, so none of them can ever be graded `improved`, `unchanged` or
  `regressed` — only `not_applied` or `unobservable`, with the applied signal beside it — and
  on Redshift that leaves ADV101/ADV102/ADV103 as the only rules whose speed change is
  gradable at all. `is_ordinary_table` also means less there than on Postgres (see the
  [`advise --json`](#advise) payload notes above), and the four Redshift physical fields
  beneath it are unreadable until it is known.
