sql-review

# SQL Review ## Requirements **Agent:** any (read-only analysis) **Tools used:** altimate_core_check, altimate_core_grade, sql_analyze, read, glob, bash (for git operations) ## When to Use This Skill **Use when the user wants to:** - Review SQL quality before merging a PR - Get a quality grade (A-F) on a query or model - Run a comprehensive lint + safety + syntax check in one pass - Audit SQL files in a directory for anti-patterns **Do NOT use for:** - Optimizing query performance -> use `query-optimize` - Fixing broken SQL -> use `dbt-troubleshoot` - Translating between dialects -> use `sql-translate` ## Workflow ### 1. Collect SQL to Review Either: - Read SQL from a file path provided by the user - Accept SQL directly from the conversation - Auto-detect changed SQL files from git: ```bash git diff --name-only HEAD~1 | grep '\.sql$' ``` For dbt models, compile first to get the full SQL: ```bash altimate-dbt compile --model <name> ``` ### 2. Run Comprehensive Check Call `altimate_core_check` — this is the single-call code review that composes: - **Syntax validation**: Parse errors with line/column positions - **Lint (26 anti-patterns)**: SELECT *, unused CTEs, implicit casts, NULL comparisons, missing WHERE on DELETE/UPDATE, cartesian joins, non-sargable predicates, missing partition filters, and more - **Injection scan**: Tautology attacks, UNION injection, stacked queries, comment injection, Jinja template injection - **PII exposure**: Flags queries accessing columns classified as PII ``` altimate_core_check(sql: <sql>, schema_context: <schema_object>) ``` ### 3. Grade the SQL Call `altimate_core_grade` to get an A-F quality score with per-category breakdown: ``` altimate_core_grade(sql: <sql>, schema_context: <schema_object>) ``` Categories scored: - **Readability**: Naming, formatting, CTE structure - **Performance**: Anti-patterns, index usage, scan efficiency - **Correctness**: NULL handling, join logic, type safety - **Best Practices**: Explicit columns, proper materialization hints ### 4. Run Anti-Pattern Analysis Call `sql_analyze` for the detailed anti-pattern breakdown with severity levels and concrete recommendations: ``` sql_analyze(sql: <sql>, dialect: <dialect>) ``` ### 5. Present the Review ``` SQL Review: <file_or_query_name> ============================== Grade: B+ (82/100) Readability: A (clear CTEs, good naming) Performance: B- (missing partition filter on large table) Correctness: A (proper NULL handling) Best Practices: C (SELECT * in staging model) Issues Found: 3 [HIGH] SELECT_STAR — Use explicit column list for contract stability [MEDIUM] MISSING_PARTITION_FILTER — Add date filter to avoid full scan [LOW] IMPLICIT_CAST — VARCHAR compared to INTEGER on line 23 Safety: PASS (no injection vectors detected) PII: PASS (no PII columns exposed) Verdict: Fix HIGH issues before merging. MEDIUM issues are recommended. ``` ### 6. Batch Mode When reviewing multiple files (e.g., all changed SQL in a PR): - Run the check on each file - Present a summary table: ``` | File | Grade | Issues | Safety | Verdict | |------|-------|--------|--------|---------| | stg_orders.sql | A | 0 | PASS | Ship | | int_revenue.sql | B- | 2 | PASS | Fix HIGH | | mart_daily.sql | C | 5 | WARN | Block | ``` ## Usage - `/sql-review models/marts/fct_orders.sql` -- Review a specific file - `/sql-review` -- Review all SQL files changed in the current git diff - `/sql-review --all models/` -- Review all SQL files in a directory