Data Teams: First Review

This page runs lexega-sql review on a branch that changes a migration, a grant and a dbt model, and shows what the review reports without a key, dbt model included, and what a trial or Team key adds. It ends with the review posted as a comment on every pull request.

The branch#

The repository is a small dbt project with a migrations/ and a grants/ folder beside models/:

yaml
# dbt_project.yml
name: warehouse
version: '1.0.0'
profile: warehouse
model-paths: ["models"]

models/customers.sql and models/orders.sql select from their sources:

sql
SELECT customer_id, region FROM {{ source('crm', 'customers') }}
sql
SELECT order_id, customer_id, amount, status FROM {{ source('shop', 'orders') }}

The branch monthly-cleanup makes one commit on top of main that changes three files.

The purge in migrations/purge_events.sql loses its filter. On main it reads:

sql
DELETE FROM analytics.events
WHERE event_date < DATEADD(day, -90, CURRENT_DATE());

On the branch:

sql
DELETE FROM analytics.events;

grants/reporting.sql moves a grant from reporting_reader to PUBLIC:

sql
GRANT SELECT ON TABLE analytics.customers TO ROLE PUBLIC;

models/revenue_by_customer.sql gains line 8, a filter on the orders it LEFT JOINs:

sql
SELECT
    c.customer_id,
    c.region,
    SUM(o.amount) AS revenue
FROM {{ ref('customers') }} c
LEFT JOIN {{ ref('orders') }} o
    ON o.customer_id = c.customer_id
WHERE o.status = 'complete'
GROUP BY c.customer_id, c.region

Review it without a key#

From the branch:

bash
lexega-sql review main...HEAD
text
## 🔴 Batch Analysis Summary

**3 files analyzed** | **2 with findings** | **2 total findings**

**Note**: 57 of 957 rules use analysis that needs a license key and may stay silent or report less precisely.

**Note**: The change was not compared with its base: no DIFF-* rule ran. A license key adds the comparison: https://lexega.com

| Level | Count |
|-------|-------|
| 🔴 Critical | 2 |

### Top Risky Files

| File | Findings | Max Level |
|------|---------|-----------|
| `grants/reporting.sql` | 1 | 🔴 critical |
| `migrations/purge_events.sql` | 1 | 🔴 critical |

### Top Matched Rules

| Rule | Occurrences |
|------|-------------|
| `DML-WRITE-UNBOUNDED` | 1 |
| `GRT-TO-PUBLIC` | 1 |

<details>
<summary>🔍 Analysis Transparency</summary>

| Metric | Value |
|--------|-------|
| Files Processed | 3/3 |
| SQL Statements | 3 |

</details>

### Per-File Finding Details

#### `grants/reporting.sql`

| Level | Finding | Evidence |
|-------|--------|----------|
| 🔴 critical | Avoid granting privileges to PUBLIC. Every user receives what PUBLIC is granted, which may expose data unintentionally. | Line 1: `GRT-TO-PUBLIC` |

#### `migrations/purge_events.sql`

| Level | Finding | Evidence |
|-------|--------|----------|
| 🔴 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 1: `DML-WRITE-UNBOUNDED` |

The migration and the grant are caught as written: the DELETE has no WHERE, so it empties analytics.events, and the grant opens analytics.customers to every user in the account.

The model is a Jinja template, and it is rendered before it is analyzed, without a key too: {{ ref('orders') }} becomes the relation the model reads. Lexega renders the models itself, from the files in the repository. It does not run dbt and needs no Python. Analyzing the model on its own shows the render:

bash
lexega-sql analyze models/revenue_by_customer.sql
text
Analysis Report
========================
Confidence: HIGH (full render, no placeholders)
Note: 57 of 957 rules use analysis that needs a license key and may stay silent or report less precisely.

Summary:
  Total findings: 0
...

The model still has no finding. The rule that catches a filter on the outer side of a LEFT JOIN, Q-JOIN-LEFT-FILT, is one of the rules the first Note line counts: it needs the analysis a key adds.

The second Note says the review did not compare the change with main; that comparison is also what a key adds below. dbt, Templates and Variables covers how models are rendered.

What a key adds here#

Needs a trial or Team key. With a key, review compares each changed statement with its version on main, and analyzes each query across its parts and each model with the models it reads.

The same command, with a key:

text
## 🔴 Batch Analysis Summary

**3 files analyzed** | **3 with findings** | **4 total findings**

| Level | Count |
|-------|-------|
| 🔴 Critical | 3 |
| 🟠 High | 1 |
...
#### `models/revenue_by_customer.sql`

| Level | Finding | Evidence |
|-------|--------|----------|
| 🟠 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 8: `Q-JOIN-LEFT-FILT` |
...
#### `migrations/purge_events.sql`

| Level | Finding | Evidence |
|-------|--------|----------|
| 🔴 critical | Write to analytics.events became unbounded after WHERE clause was removed. May affect entire table. | Line 1: `DIFF-WRITE-WHERE-RMV` |
| 🔴 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 1: `DML-WRITE-UNBOUNDED` |
  • The purge has two findings, each saying something different. DML-WRITE-UNBOUNDED says the statement as it stands has no filter. DIFF-WRITE-WHERE-RMV comes from comparing the purge with its version on main, and says this change is what removed it. The comparison reports the removal once: the more general DIFF-WHERE-RMV stays silent where DIFF-WRITE-WHERE-RMV reports. Semantic Diff covers what the comparison reports.
  • Q-JOIN-LEFT-FILT comes from analysis across the query's parts. Line 8 filters o, the outer side of the LEFT JOIN, so customers with no completed order drop out of the revenue table instead of appearing with no revenue.

What a Key Adds lists everything else a key changes.

Post the review on pull requests#

This GitHub Actions workflow posts the review as a comment on every pull request:

yaml
name: SQL review
on: pull_request

jobs:
  review:
    runs-on: ubuntu-latest
    permissions:
      contents: read
      pull-requests: write
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0

      - name: Install Lexega
        run: |
          curl -sSL -O https://github.com/Lexega/lexega/releases/latest/download/lexega-sql-linux-x64
          curl -sSL https://github.com/Lexega/lexega/releases/latest/download/CHECKSUMS.sha256 | grep ' lexega-sql-linux-x64$' | sha256sum -c -
          sudo install -m 755 lexega-sql-linux-x64 /usr/local/bin/lexega-sql

      - name: Review SQL
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
          LEXEGA_LICENSE_KEY: ${{ secrets.LEXEGA_LICENSE_KEY }}
        run: lexega-sql ci --pr-comment
  • ci reads the pull-request event, takes the pull request's base commit from it and runs review from that commit to HEAD over the whole repository. On a push it runs analyze on the tree instead and leaves out --pr-comment. The CLI Reference lists its options.
  • --pr-comment posts the Markdown review shown above as a comment on the pull request, and edits that comment on later runs instead of adding a new one. It needs GITHUB_TOKEN and the pull-requests: write permission.
  • fetch-depth: 0 fetches the history the review compares against.
  • If the LEXEGA_LICENSE_KEY secret is not set, the variable is empty and the review runs without a key.
  • Without a policy, the step exits 0 whatever the review finds. To fail the job, add --policy, --env and --decision-out as in Quick Start; a policy needs no key.

CI/CD Integration has the same job for GitLab CI, Azure DevOps and Bitbucket Pipelines, and how to store the key as a CI secret.

Something missing or wrong? Open an issue on GitHub or write to [email protected].