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).
• 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
• Commands
  • complexity
  • lint
  • perf
  • advise
  • verify
  • check
• Configuration
• Exit codes
• CI recipe (a gate that actually gates)
• Pre-commit hook
• LLM suggestions
• Limitations

Install

Once published to PyPI:

pip install sqlquality
# or
uv add sqlquality

Until then, install from git:

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

pip install "sqlquality[llm]"

advise needs a database driver. For Postgres:

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. Without the extra, advise degrades with an install hint instead of a traceback.

--version prints the installed version:

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

$ 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 -:

cat model.sql | sqlquality complexity -

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

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

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

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

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

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. An optional dbt manifest enriches the same analysis — see dbt enrichment 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 with what you found; the first user with a cluster is part of closing this gap, not just a consumer of it.

$ 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.
--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:

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

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.

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:

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

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

$ 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 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:

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:

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:

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.

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

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

$ 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). In the default warn mode the same change reports the regression but exits 0:

$ 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):

$ 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:

# 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_ids exempt from the gate.

A complete example:

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

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

# .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 hook that lints staged SQL:

# .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:

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

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: 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), 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 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 payload notes above), and the four Redshift physical fields beneath it are unreadable until it is known.