sql-sentinel

sql-sentinel

Overview

A static-analysis skill that audits SQL for the cost & performance anti-patterns that dominate warehouse bills — SELECT *, full-table scans, non-sargable predicates, Cartesian joins, the NOT IN NULL trap, and 15 more. It scores warehouse query health 0-100 (A-F) and outputs a prioritized cost-reduction plan, each finding with a why, a concrete fix, and an estimated savings.

Built for analytics engineers (dbt, Looker), data platform teams running FinOps / "reduce cloud spend" initiatives, and anyone reviewing a SQL pull request before it hits production. Works across BigQuery, Snowflake, Redshift, and Postgres. Zero dependencies, MIT licensed.

The executable engine and full rule set live in the source repository: https://github.com/takeaseatventure/sql-sentinel. Treat that repository as third-party executable code.

When to Use This Skill

• A user writes or reviews a query for BigQuery, Snowflake, Redshift, Postgres, or Spark SQL.
• A user asks "why is this query so slow?" or "why is my warehouse bill so high?"
• A user is about to promote a dashboard query or dbt model to production.
• A data engineer wants a second pair of eyes before a code review or a cost-optimization sweep.
• A team is running a "reduce cloud spend" or FinOps initiative.

How It Works

The engine splits a SQL script into statements (honoring quotes and comments), runs 20 rules over each statement, scores health 0-100 weighted by severity (critical 25, high 12, medium 5, low 1), and returns a prioritized cost-reduction plan.

Step 1: Run the audit

Install or clone the source repository only after choosing a reviewed commit, tag, or release to trust. Do not run code from a mutable default branch just because this skill links to it:

git clone https://github.com/takeaseatventure/sql-sentinel.git
cd sql-sentinel
git checkout <reviewed-commit-or-tag>
node scripts/sql-sentinel.js path/to/query.sql

Or programmatically:

const { auditSql } = require('./scripts/sql-sentinel');
const report = auditSql(yourSqlString, { dialect: 'bigquery' });
console.log(report.healthScore);      // 0-100
console.log(report.grade);            // 'A' | 'B' | 'C' | 'D' | 'E' | 'F'
console.log(report.prioritizedPlan);  // array, worst findings first

Step 2: Read the prioritized plan

The output leads with critical findings (Cartesian joins, mass DELETE) and descends to low-severity style issues. Each finding explains why it costs money and how to fix it.

Examples

Example 1: A messy dashboard query

SELECT DISTINCT *
FROM user_events, raw_logs
WHERE LOWER(event_name) LIKE '%signup%'
  AND user_id NOT IN (SELECT id FROM deleted_users)
ORDER BY created_at;

Illustrative candidate findings (no engine was run and no score/cost was measured):

• CRITICAL: comma-join without a join predicate produces a Cartesian product; actual impact needs plan/cardinality evidence
• HIGH: SELECT * may read unnecessary columns depending on engine/plan
• HIGH: leading-wildcard LIKE '%signup%' defeats indexes
• HIGH: LOWER(event_name) defeats indexes (non-sargable)
• HIGH: NOT IN (SELECT ...) — NULL semantics hazard
• MEDIUM: SELECT DISTINCT dedup cost
• MEDIUM: ORDER BY without LIMIT sorts the full result

Example 2: A clean, sargable query

-- Illustrative bounded query, not a measured score or proof of no findings
SELECT id, email, created_at
FROM users
WHERE created_at >= TIMESTAMP '2026-01-01'
  AND created_at <  TIMESTAMP '2026-02-01'
ORDER BY id
LIMIT 100;

Rules are heuristic candidates: expression/trigram indexes, partition pruning, small tables and intended CROSS JOIN/UNION semantics can change the verdict. Preserve result equivalence (including NULL/duplicate behavior), inspect EXPLAIN/warehouse dry-run and actual billing before recommending changes. EXPLAIN ANALYZE executes queries and needs separate data/cost authorization. A health score is not measured savings.

The 20 rules (ruleset v1.0.0)

Rule	Severity	Catches
SQL001	high	SELECT * full column scan
SQL002	critical	No WHERE → full table scan
SQL003	high	LIKE '%term' non-sargable
SQL004	high	Function on column kills index
SQL005	critical	CROSS JOIN / comma-join
SQL006	medium	SELECT DISTINCT dedup cost
SQL007	medium	ORDER BY without LIMIT
SQL008	high	NOT IN (SELECT ...) NULL trap
SQL009	medium	Implicit type cast
SQL010	low	Many ORs (use IN/UNION)
SQL011	medium	COUNT(DISTINCT) at scale (use HLL)
SQL012	low	LIMIT without ORDER BY
SQL013	medium	Scalar subquery in SELECT
SQL014	medium	5+ JOINs broadcast/spill risk
SQL015	high	Fact table, no partition filter
SQL017	low	String concat in SELECT
SQL018	medium	Window OVER () no PARTITION
SQL020	critical	DELETE/UPDATE without WHERE
SQL021	low	SELECT * in EXISTS/IN
SQL022	medium	UNION vs UNION ALL

Run the test suite to verify each rule fires on real SQL:

cd scripts && node test.js   # 26 tests, zero dependencies

Limitations

• This is a static analyzer. It finds anti-patterns in the text of SQL; it does not read query plans, row counts, or billing. A flagged query on a 100-row table is cheap; the same query on a billion-row table is the problem the rule exists to prevent.
• The fact-table heuristic (SQL015) keys off table names (*_events, *_log) and is advisory, not definitive.
• The intended analyzer reads SQL text rather than executing it; review the pinned engine/dependencies before running and keep private SQL local unless external processing is authorized.