Sqlsure: Deterministic Semantic Checks for AI-Generated SQL

Sqlsure is a deterministic semantic inspector for SQL designed to prevent silent errors—queries that run without error but return mathematically incorrect results. It targets the gap where databases, linters, and LLMs fail to catch semantic bugs like fan-out double-counting and additivity violations.

Deterministic Verification of SQL Semantics

Sqlsure evaluates SQL queries against a set of declared facts about a database schema, such as grain, join cardinality, and measure additivity. Unlike LLM-based reviewers, sqlsure uses dictionary lookups to ensure that the same input always produces the same verdict, operating fully offline in approximately 0.1 ms per check.

Key Semantic Rules

Sqlsure implements a set of rules (v0.1) to catch common semantic failures:

Rule	Severity	Catches
FANOUT	Error	SUM/COUNT of additive measure after one-to-many join
CHASM	Error	Multiple fan-out joins multiplying each other
ADDITIVITY	Error	SUM of a non-additive measure (e.g., rates, averages)
SEMI_ADDITIVE	Error	Balances or censuses summed across snapshot dimensions
JOIN_KEY	Error	Joins on columns without a declared relationship
CROSS_JOIN	Error	Joins with no predicate
WEIGHTED_AVG	Warning	Averages silently re-weighted by fan-out
UNDECLARED_JOIN	Warning	Joins with no declared relationship
SENSITIVE_COLUMN	Policy	Exposure of PHI/PII columns in query output

Integration and Workflow

Sqlsure can be integrated into the data pipeline in three primary ways:

CI Gate: Using the CLI, sqlsure can block pull requests when a query is found to be semantically incorrect.
MCP Server: AI agents can be configured to pass inspection via the Model Context Protocol (MCP) before executing a query. Rejections include machine-actionable fix hints, which the author claims produced a passing query 10/10 times when applied verbatim in benchmarks.
Python Library: The check() function can be embedded directly into text-to-SQL products or agent frameworks to create a "Semantic Gate."

Rulebook Generation

Sqlsure does not require a new language for configuration. It generates its rulebook from existing metadata:

dbt: Uses manifest.json or schema.yml (converting unique tests to grain and relationships tests to join cardinality).
Database Introspection: sqlsure.introspect can build a rulebook from SQLite PRAGMAs or information_schema (Postgres/MySQL) PK/FK declarations.
Semantic Layers: Supports OSI and WrenAI MDL loaders.
Manual Configuration: Supports hand-written JSON models.

Performance and Trust Properties

Sqlsure is designed for security and privacy. It parses query text without connecting to the database or accessing actual data. It operates entirely offline with no telemetry and ships via PyPI Trusted Publishing.

Benchmark Results

In an audit of the Spider and BIRD text-to-SQL benchmarks, sqlsure analyzed 2,568 expert-written queries. It identified 45 flags with zero false alarms. Notably, it identified a BIRD gold answer that was provably wrong by a factor of 8x due to a fan-out bug, leading to an upstream issue report.

Community Discussion

While the project provides a deterministic approach to semantic validation, some users have questioned the necessity of a library for these issues. One commenter noted that fan-out errors are often a symptom of poor database design:

"An OLAP table should be designed so that values can be summed if that's the purpose of the table... I think the better solution is to fix the problem, not the queries."

The author responded by clarifying that the author targets the specific risk of joining to order_items and summing order_total, where every dollar is counted once per line item—a common scenario where the query runs successfully but the result is silently wrong.