Sqlsure: Deterministic Semantic Checks for AI-Generated SQL

Sqlsure: Deterministic Semantic Checks for AI-Generated SQL

Sqlsure prevents silent SQL failures by validating query semantics

Sqlsure is a deterministic semantic inspector designed to catch "silent" SQL errors—queries that are syntactically valid and run without error but return mathematically incorrect results. It targets common failures in AI-generated SQL, such as revenue double-counting caused by joins (fan-out) or the summation of non-additive measures like averages.

Unlike traditional linters or LLM-based reviews, Sqlsure operates as a deterministic engine. It uses dictionary lookups against a declared semantic model to provide a verdict in approximately 0.1 ms without requiring network calls or database access.

Core Semantic Rules and Error Detection

Sqlsure evaluates SQL against a set of predefined rules to identify structural and semantic risks. When the engine cannot verify a specific operation, it reports "can't verify" rather than assuming the query is safe.

Critical Errors

  • FANOUT: Detects the summation or counting of additive measures after a one-to-many join, which typically leads to double-counting.
  • CHASM: Identifies multiple fan-out joins that multiply the result set, compounding the error.
  • ADDITIVITY: Catches the summation of non-additive measures such as rates or averages.
  • SEMI_ADDITIVE: Detects balances or censuses being summed across their snapshot dimension.
  • JOIN_KEY: Flags joins on columns that do not match any declared relationship in the semantic model.
  • CROSS_JOIN: Flags joins performed without a predicate.

Warnings and Policy Violations

  • WEIGHTED_AVG: Warns when an average is silently re-weighted by a fan-out join.
  • UNDECLARED_JOIN: Warns when a join is used that has no declared relationship (unverifiable).
  • SENSITIVE_COLUMN: A policy check that flags the exposure of PHI/PII columns in the query output.

Integration and Implementation Patterns

Sqlsure can be integrated into the data pipeline at three primary entry points:

  1. CI/CD Gates: It can be used as a CLI tool to block pull requests if a query is found to be semantically incorrect.
  2. MCP Server: As a Model Context Protocol (MCP) server, it allows AI agents to pass inspection before executing a query, enabling a "draft $\rightarrow$ check $\rightarrow$ fix $\rightarrow$ execute" loop.
  3. Library Integration: The check() function can be embedded directly into text-to-SQL frameworks (such as Vanna or WrenAI) to act as a semantic gate or an evaluation metric for NL2SQL output.

Semantic Model Sourcing

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

  • dbt: It can ingest manifest.json or schema.yml files, converting dbt unique tests into grain definitions and relationships tests into join cardinality.
  • Live Databases: The sqlsure.introspect module can build a model from the database catalog using SQLite PRAGMAs or information_schema for PostgreSQL and MySQL.
  • Semantic Layers: It includes loaders for OSI and WrenAI MDL.
  • Custom JSON: Users can define their own semantic models via a JSON specification.

Trust and Security Properties

Sqlsure is designed for high-security environments with the following properties:

  • Deterministic: The same SQL and rulebook always produce the same result.
  • Offline and Private: The tool operates entirely offline with zero network calls; SQL queries never leave the local machine.
  • No Data Access: Sqlsure parses the query text itself and never connects to the actual database data.
  • No Telemetry: The tool collects no data from the user.

Performance and Validation

In audits of the BIRD and Spider text-to-SQL benchmarks, Sqlsure identified 45 flags across 2,568 expert-written queries with zero false alarms. This included identifying a BIRD dev gold answer that was provably wrong by a factor of 8x due to a fan-out bug, as well as identifying schema defects that were subsequently filed upstream.

Sources