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/:
# 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:
SELECT customer_id, region FROM {{ source('crm', 'customers') }}
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:
DELETE FROM analytics.events
WHERE event_date < DATEADD(day, -90, CURRENT_DATE());
On the branch:
DELETE FROM analytics.events;
grants/reporting.sql moves a grant from reporting_reader to PUBLIC:
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:
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:
lexega-sql review main...HEAD
## 🔴 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:
lexega-sql analyze models/revenue_by_customer.sql
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,
reviewcompares each changed statement with its version onmain, and analyzes each query across its parts and each model with the models it reads.
The same command, with a key:
## 🔴 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-UNBOUNDEDsays the statement as it stands has no filter.DIFF-WRITE-WHERE-RMVcomes from comparing the purge with its version onmain, and says this change is what removed it. The comparison reports the removal once: the more generalDIFF-WHERE-RMVstays silent whereDIFF-WRITE-WHERE-RMVreports. Semantic Diff covers what the comparison reports. Q-JOIN-LEFT-FILTcomes from analysis across the query's parts. Line 8 filterso, 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:
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
cireads the pull-request event, takes the pull request's base commit from it and runsreviewfrom that commit toHEADover the whole repository. On a push it runsanalyzeon the tree instead and leaves out--pr-comment. The CLI Reference lists its options.--pr-commentposts 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 needsGITHUB_TOKENand thepull-requests: writepermission.fetch-depth: 0fetches the history the review compares against.- If the
LEXEGA_LICENSE_KEYsecret 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,--envand--decision-outas 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].