nicholashidalgo SQL and Data Gotcha Pack
This is a shared rule library. It is not a standalone skill. Data and SQL skills load it to enforce a common set of Never-do-X constraints.
How it works
Every rule in this pack has:
- A unique ID (G001 through G015)
- A severity: HIGH, MEDIUM, or LOW
- A machine-readable failure pattern used by CI
- A rationale grounded in a real reporting failure class
- A bad and good example
- A skill scope list (which skills enforce which rules)
Rules with failure_pattern_type: regex or regex_flag_only are caught
automatically by sql_data_gotcha.flag_gotchas(text, skill).
Rules with failure_pattern_type: behavioral are injected as enforcement
instructions into the dependent skill's SKILL.md and enforced by the LLM
at generation time.
Rule Index
| ID | Severity | Rule summary | Skills |
|---|---|---|---|
| G001 | HIGH | Never SELECT * | sql-report-builder, schema-join-risk-reviewer, data-integrity-investigator |
| G002 | HIGH | Never aggregate before confirming join cardinality | sql-report-builder, schema-join-risk-reviewer, data-integrity-investigator, python-report-validation |
| G003 | HIGH | Never treat NULL as zero in aggregations | all 6 data+SQL skills, kpi-definition-governance |
| G004 | HIGH | Never let LEFT JOIN be silently converted to INNER JOIN | sql-report-builder, schema-join-risk-reviewer, data-integrity-investigator |
| G005 | HIGH | Never join SCD dimension without current/active filter | sql-report-builder, schema-join-risk-reviewer, data-integrity-investigator |
| G006 | HIGH | Never mix grains in the same aggregation without documenting it | sql-report-builder, schema-join-risk-reviewer, kpi-definition-governance, requirements-to-report-spec |
| G007 | HIGH | Never validate using the same logic as the report under test | sql-report-builder, data-integrity-investigator, python-reconciliation-engine, python-report-validation |
| G008 | HIGH | Never define a KPI without inclusion and exclusion criteria | kpi-definition-governance, requirements-to-report-spec |
| G009 | MEDIUM | Never report on a column without checking its null rate first | python-data-investigator, data-integrity-investigator, python-report-validation, requirements-to-report-spec |
| G010 | MEDIUM | Never infer join type from column name; verify cardinality | schema-join-risk-reviewer, data-integrity-investigator, sql-report-builder |
| G011 | MEDIUM | Never let DISTINCT mask a duplicate without investigating the source | sql-report-builder, schema-join-risk-reviewer, data-integrity-investigator |
| G012 | HIGH | Never reconcile datasets without confirming shared grain+period | python-reconciliation-engine, data-integrity-investigator, python-report-validation |
| G013 | LOW | Never write a report spec without a sort tie-break rule | requirements-to-report-spec, kpi-definition-governance |
| G014 | HIGH | Never validate a report by row count only | python-report-validation, data-integrity-investigator |
| G015 | MEDIUM | Never treat net-zero reconciliation as proof of correctness | python-reconciliation-engine, data-integrity-investigator |
Enforcement model
For LLM skills
Dependent skills include this block in their Runtime Configuration:
gotcha_pack: "sql-data-gotcha-pack"
gotcha_pack_version: "1.0.0"
gotcha_enforcement: "block_on_high"
And a Gotcha Enforcement section that lists the applicable rule IDs and instructs Claude to check each before finalizing output.
For CI
from shared.sql_data_gotcha_pack.sql_data_gotcha import flag_report
result = flag_report(generated_sql, skill="sql-report-builder")
assert result["pass"], f"HIGH gotcha violations: {result['hits']}"
Adding rules
- Add an entry to
gotchas.jsonwith the next sequential ID. - Add the skill to the
skillslist of each affected rule. - Add the rule ID to the
Gotcha Enforcementblock in each affected SKILL.md. - Add a test case to
tests/shared/test_sql_data_gotcha_pack.py. - Run
python tools/check_gotcha_coverage.pyto verify all skills are covered.