February 2026

How Lexega Turns SQL Into Findings

A quick tour of findings, rules, and policy decisions

If you've ever reviewed a SQL PR where "the diff is bigger than your screen", you already know the failure mode:

  • the change looks reasonable,
  • tests pass,
  • but a single dangerous statement slips through because nobody can realistically inspect everything.

Lexega is built around a simple idea: turn SQL into deterministic, actionable findings before it runs, then use policy to decide what to do with those findings. It's a guardrail layer for SQL -- a structural analysis engine that sits between "code written" and "code deployed".

This post is a quick tour of what actually happens under the hood when you run lexega-sql analyze.


The pipeline: SQL → semantics → findings

At a high level, Lexega does four things:

  1. Tokenize and parse your SQL (including multi-statement scripts and Jinja/dbt templates)
  2. Walk the AST to extract semantic facts -- tables read/written, grants, policy changes -- that describe what actually happened
  3. Match rules (builtin + custom YAML) against those facts; each rule that matches raises a finding with its severity and message
  4. Evaluate policy to produce a decision (block, warn, or allow)

You can think of it like this:

SQL text
  → lexer/parser
  → semantic extraction (facts)
  → rule matching (builtin + custom)
  → findings
  → policy evaluation (env-aware)
  → decision.json (allow / warn / block)

The important bit is that nothing in that pipeline is probabilistic: the same input yields the same findings—every time.


Findings are semantic events

Findings in Lexega describe what a statement does, not how it looks. Examples:

  • an unbounded write (e.g. DELETE FROM t; without a WHERE)
  • a policy removed (e.g. a masking/row access policy dropped)
  • a storage security change (e.g. encryption disabled on an external stage)

Those events are then mapped to rules that decide severity and messaging, and to policies that decide enforcement.


A concrete example: a bug that requires column lineage to detect

Here's a query that looks perfectly reasonable:

WITH order_details AS (
  SELECT
    o.order_id,
    o.total,
    c.name,
    c.tier
  FROM orders o
  LEFT JOIN customers c
    ON o.customer_id = c.id
)
SELECT *
FROM order_details
WHERE tier = 'enterprise'
ORDER BY total DESC;

Run it through Lexega. This finding, like the diff example further down, needs a trial key. Without one, Lexega analyzes each statement as written, doesn't follow the column through the CTE, and reports nothing on this query.

echo "WITH order_details AS (
  SELECT o.order_id, o.total, c.name, c.tier
  FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.id
)
SELECT * FROM order_details
WHERE tier = 'enterprise'
ORDER BY total DESC;" | lexega-sql analyze --stdin --min-severity info

Output as of v3.

findings:
  [HIGH] Outer join's nullable side filtered in WHERE clause or by a later join. The unmatched rows the outer join keeps are dropped, likely a bug.
    ↳ Line 7 • `Q-JOIN-LEFT-FILT` • "tier" • :7:7

The WHERE tier = 'enterprise' in the outer query silently converts the LEFT JOIN inside the CTE into an INNER JOIN -- any order without a matching customer is dropped instead of preserved with NULLs. This is one of the most common SQL bugs in analytics code, and the LEFT JOIN and the filter aren't even in the same scope. Catching it requires:

  1. Parsing the LEFT JOIN inside the CTE and knowing c is the nullable (right) side
  2. Tracking that tier in the CTE's SELECT list originates from c.tier
  3. Following that column lineage through the CTE boundary into the outer query
  4. Recognizing that WHERE tier = 'enterprise' filters on a nullable-origin column, negating the LEFT

That's column lineage across CTE boundaries -- the kind of structural analysis that makes Lexega's findings useful for catching real bugs, not just flagging syntax.


More finding categories

Semantic diff: catching high-risk changes

If you care about changes (not just "does this SQL contain a JOIN"), you need semantic diff.

Example: a PR touches a revenue query. The text diff shows one line changed in a JOIN predicate. Is that a big deal? Depends which tables are involved and what the predicate used to be.

Before:

SELECT
  o.order_id,
  o.total,
  c.customer_name,
  c.tier
FROM ANALYTICS.ORDERS o
INNER JOIN ANALYTICS.CUSTOMERS c
  ON o.customer_id = c.customer_id
WHERE o.region = 'NA'
ORDER BY o.total DESC;

After (one column name changed in the ON clause):

SELECT
  o.order_id,
  o.total,
  c.customer_name,
  c.tier
FROM ANALYTICS.ORDERS o
INNER JOIN ANALYTICS.CUSTOMERS c
  ON o.billing_customer_id = c.customer_id
WHERE o.region = 'NA'
ORDER BY o.total DESC;

In a text diff, that's a single-line change buried in context. Lexega's semantic diff (trial key needed) surfaces what it means:

lexega-sql diff main..HEAD models/ -r --min-severity info
Semantic Diff: main → HEAD
Files analyzed: 1, Files with semantic changes: 1

─── models/revenue.sql ───
  🟢 DIFF-JOIN-COND-CHG JOIN condition between ANALYTICS.ORDERS and ANALYTICS.CUSTOMERS changed. Data relationship logic altered. (line 7)

A changed join condition is reported as context (info), so it shows at --min-severity info; at the default threshold the same run says the file has a semantic change below high.

The key point isn't that it can "parse JOIN". It's that it can answer the review question you actually care about: "did the join condition between ORDERS and CUSTOMERS change?"

Jinja/dbt-templated SQL

Templating—especially without Python—is very difficult to get right. Even if the underlying SQL is valid, the file is no longer "just SQL".

Lexega renders Jinja templates before analysis, with or without a license key. Once it renders, the question is how much context it has to work with.

dbt project mode (automatic): When you run lexega-sql analyze from inside a dbt project, Lexega detects the project context and automatically renders templates using your project's profiles, vars, and macros -- including ref(), source(), and config() resolution. No flags required. This is the default behavior in CI when your working directory is a dbt project.

Explicit vars: Outside a dbt project, you can supply variables directly with --var, --var-file, or --dbt-profile. Undefined Jinja variables evaluate as falsy (standard Jinja behavior), so even without explicit vars, {% if %} branches resolve deterministically -- you just might not get the branch you intended.

Fallback: If rendering fails entirely (e.g., templates use runtime-only dbt functions like run_query or adapter.*), Lexega falls back to analyzing the raw template structure. Coverage will be limited -- Jinja in structurally important positions (table names, conditions) means less analysis. Rendering is always the stronger path.

Consider a template-guarded deletion:

{% if safe_mode %}
  DELETE FROM {{ table }}
  WHERE created_at < DATEADD('day', -90, CURRENT_TIMESTAMP());
{% else %}
  DELETE FROM {{ table }};
{% endif %}

Run it with safe mode off:

cat model.sql | lexega-sql analyze --stdin \
  --var "table=production.users" --var "safe_mode=false"

Output as of v3.

findings:
  [CRITICAL] Unbounded write operation detected - the statement has no WHERE clause or its WHERE clause matches every row. This affects ALL rows in the target table(s).
    ↳ Line 5 • `DML-WRITE-UNBOUNDED` • "DELETE FROM production.users" • :5:3

Now flip the variable:

cat model.sql | lexega-sql analyze --stdin \
  --var "table=production.users" --var "safe_mode=true"

No findings. The bounded DELETE ... WHERE is structurally safe.

Same template, different variables, completely different risk profile -- and the analysis is deterministic for each combination. This is the kind of thing you can wire into CI: render with your actual deployment context, then enforce policy on the result.


Your rules, same engine

The built-in rules number in the hundreds, but the underlying engine is the same one you use to define your own.

Custom rules are declarative YAML -- no plugins, no scripting, no code to deploy. You match on the same facts that the built-in rules use, but you can combine them in ways that are specific to your org.

For example, say your revenue pipeline depends on INNER JOINs between ANALYTICS.ORDERS and ANALYTICS.CUSTOMERS -- if someone changes one of those to a LEFT JOIN in a PR, you want that blocked in prod. A custom diff rule can match on exactly that:

rules:
  - id: ACME-002
    risk_level: critical
    message: "Join type changed on revenue-critical tables ({witness.left.canonical}, {witness.right.canonical}): now {witness.head_kind}. Requires data-eng approval."
    triggers:
      diff.events:
        each:
          all_of:
            - kind: join_type_changed
            - baseline_kind: inner
            - head_kind: { in: [left, cross] }
            - any_of:
                - left.canonical:  { in: ["ANALYTICS.ORDERS", "ANALYTICS.CUSTOMERS"] }
                - right.canonical: { in: ["ANALYTICS.ORDERS", "ANALYTICS.CUSTOMERS"] }

This matches only when a semantic diff detects a join type change from INNER to LEFT or CROSS, and only on those two tables. The finding itself reports the affected tables and the before/after join types, so the alert tells you exactly what changed.

You can also override built-in rules. The built-in SCHEMA-DROP rule fires at Critical severity when someone drops a schema. If your org uses ephemeral schemas as a standard part of your data pipeline (e.g., swap-and-drop patterns for zero-downtime deploys), Critical is the wrong default -- it's noise, not risk. You don't have to disable detection entirely; you can downgrade it with a partial override -- no triggers: field required, the built-in's predicate is inherited:

rules:
  - id: SCHEMA-DROP
    risk_level: low
    message: "Schema dropped (downgraded per org policy -- swap-and-drop is expected)."

Because there's no triggers:, the engine treats this as a partial overlay: the built-in's kind: drop_schema predicate is inherited verbatim, and only risk_level and message change. Same rule_id, so the built-in Critical rule is replaced by your Low rule everywhere it would have fired. No code changes, no recompilation, no forking. If you got the rule id wrong (say SCHEMA-DROPX), the loader fails fast with partial override 'SCHEMA-DROPX' does not match any built-in rule id -- a partial overlay that doesn't match any built-in id is a hard error, not a silently-inert rule.

The point is that Lexega's detection layer isn't a black box you have to accept as-is. The built-in rules are a starting point; your org's specific risks get the same treatment.


From findings to policy: CI-friendly decisions

Findings are informational on their own. Enforcement comes from policy.

In CI/CD you typically run analysis with a policy file and environment context:

lexega-sql analyze migrations/*.sql \
  --policy .lexega/policy.yml \
  --env prod \
  --decision-out decisions/$GITHUB_RUN_ID/

That produces a machine-readable decision artifact (decision.json) that your pipeline can treat as a gate.

This separation is intentional:

  • Rules define what gets detected (and at what severity)
  • Policies define what to do about it (block in prod, warn in staging, allow in dev)
  • Exceptions provide auditable, time-scoped overrides

Why determinism matters (especially with AI-generated SQL)

If your review volume is increasing because of AI-assisted changes, the worst possible property in a guardrail is "it depends".

Deterministic guardrails buy you:

  • Repeatability: the same PR produces the same findings every time
  • Auditability: you can point to exactly why a deploy was blocked
  • Low-noise enforcement: policies can be tuned per environment instead of relying on human judgment in every PR

It's not a replacement for review. It's a guardrail that makes review possible again when the diffs stop fitting inside a human brain.


Try it

See the examples, for data teams or for security teams — real SQL through the real engine, blocked and allowed, with the evidence highlighted. No install, no key, no account.

Or install the CLI for local use:

curl -fsSL https://lexega.com/install.sh | sh
export LEXEGA_LICENSE_KEY=<your-key>
lexega-sql analyze your_migration.sql --min-severity medium

Get a free trial key — no account required, instant activation.