dbt, Templates and Variables
SQL in a repository is often not the SQL that runs: deployment tooling substitutes ${DB} markers, dbt and Jinja render models, and Python or notebooks hold SQL in strings. This page shows how to give Lexega the values it needs so findings and policy see the real object names, and how to analyze SQL embedded in Python and notebooks.
Deployment variables#
Deployment scripts often rewrite SQL before it runs (${NAME} through envsubst, sqlcmd's $(NAME) with --var-syntax dollar-paren, or a scheme of your own). Lexega resolves those markers first, so findings name the real objects and a policy that gates the production database matches the resolved name. This applies to analyze, review and ci, and it needs no key. fmt never substitutes.
Take deploy.sql:
USE DATABASE ${ELT_DB};
DROP TABLE ${ELT_DB}.staging.orders;
GRANT SELECT ON TABLE ${ELT_DB}.marts.revenue TO ROLE analyst;
Without a value, the marker is analyzed as its name. lexega-sql analyze deploy.sql prints this warning on stderr:
⚠️ Unresolved deployment variable(s): ELT_DB. Set values with --var NAME=VALUE or --var-env NAME; analysis continues with the variable name at lower confidence.
and this report on stdout:
Analysis Report
========================
Confidence: MEDIUM (2 high-impact, 1 low-impact placeholders)
Note: 57 of 957 rules use analysis that needs a license key and may stay silent or report less precisely.
Summary:
Total findings: 1
Critical: 1
High: 0
Medium: 0
Low: 0
Context: 0
Coverage:
Analyzed: 3 SQL statements
Data Operations:
Tables Read: 0
DDL Operations: 1
Cross-Database: No
findings:
[CRITICAL] DROP TABLE detected. Table and all data will be permanently deleted.
↳ Line 2 • `TBL-DROP` • "DROP TABLE ELT_DB.staging.orders" • deploy.sql:2:1
With --var ELT_DB=PROD_DW the finding names the real table and the confidence is back to high:
lexega-sql analyze deploy.sql --var ELT_DB=PROD_DW
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: 1
Critical: 1
High: 0
Medium: 0
Low: 0
Context: 0
Coverage:
Analyzed: 3 SQL statements
Data Operations:
Tables Read: 0
DDL Operations: 1
Cross-Database: No
findings:
[CRITICAL] DROP TABLE detected. Table and all data will be permanently deleted.
↳ Line 2 • `TBL-DROP` • "DROP TABLE PROD_DW.staging.orders" • deploy.sql:2:1
Where values come from#
| Option | Meaning |
|---|---|
--var NAME=VALUE | Set one value. Repeatable. |
--var-file <FILE> | Load values from a JSON or YAML file (chosen by the .json, .yaml or .yml extension). Repeatable. A local path. |
--var-env <NAME> | Read NAME from the environment. An explicit list: nothing is read from the environment unless named. Repeatable. |
--snowsql-config <FILE> | Load SnowSQL &var values from a config file's [variables] section. Repeatable. |
When the same name is set twice, --var wins over --var-file, which wins over --var-env, which wins over --snowsql-config. A variable file that is not an object of names and values, or a missing one, is an error naming the file.
lexega-sql analyze deploy.sql --var-file vars.json # {"ELT_DB": "PROD_DW"}
ELT_DB=PROD_DW lexega-sql analyze deploy.sql --var-env ELT_DB
lexega-sql analyze script.sql --snowsql-config ~/.snowsql/config
A SnowSQL script that uses &ELT_DB resolves from --snowsql-config (with [variables] containing ELT_DB = SNOW_DB) the same way: DROP TABLE &ELT_DB.s.t is analyzed as DROP TABLE SNOW_DB.s.t. A SnowSQL variable with no value is left as written.
Marker syntax#
${name} is recognized by default. --var-syntax adds others, and can be repeated:
lexega-sql analyze deploy.sql --var-syntax dollar-paren # also $(name)
lexega-sql analyze deploy.sql --var-syntax '%%NAME%%' --var ELT_DB=X # also %%ELT_DB%%
A sqlcmd :setvar in the script is not read as a value: pass the value with --var. For a syntax of your own, write your delimiters around the word NAME. A repository can choose among the built-in syntaxes in .lexega.toml:
[template.substitution]
presets = ["dollar-brace", "dollar-paren"] # omit for the default (["dollar-brace"]); [] turns recognition off
Set presets = [] for a repository whose SQL contains ${...} for another reason. Environment variable names (--var-env) and custom delimiters are command-line settings only: .lexega.toml comes from the repository being analyzed, and the code under analysis should not choose what is read from the environment. A run stops with an error if that file sets them.
Unresolved markers#
An unresolved marker does not stop the run. It is analyzed as its name, a warning lists the variables, and the report's confidence is lowered. A resolved value is substituted everywhere, including inside string literals; an unresolved marker inside a string literal is left as written, and markers in comments are ignored.
dbt and Jinja models#
Lexega renders dbt and Jinja templates before it analyzes them, from the files in the repository: it does not run dbt and needs no Python. Rendering needs no key.
Take proj/models/grant_orders.sql in the dbt project proj:
GRANT SELECT ON TABLE {{ ref('orders') }} TO ROLE {{ var('role') }};
Point --dbt-project at the project and give the variables. A model analyzed from inside its project finds the project on its own; --dbt-project names it for a run started elsewhere:
lexega-sql analyze proj/models/grant_orders.sql --dbt-project proj --var role=public
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: 1
Critical: 1
High: 0
Medium: 0
Low: 0
Context: 0
Coverage:
Analyzed: 1 SQL statements
Data Operations:
Tables Read: 0
Cross-Database: No
findings:
[CRITICAL] Avoid granting privileges to PUBLIC. Every user receives what PUBLIC is granted, which may expose data unintentionally.
↳ Line 1 • `GRT-TO-PUBLIC` • "public" • proj/models/grant_orders.sql:1:51
This run was made without a key, as its Note: line says: the role name comes from the rendered SQL, and the finding is on it.
Needs a trial or Team key. With a key, each model is also analyzed with the models it reads, so a column is followed through
ref()from model to model, andreviewcompares each changed model with its base.
Options#
| Option | Meaning |
|---|---|
--dbt-project <PATH> | The dbt project directory. Its macros and dbt_packages/ are loaded for rendering. |
--dbt-profile <NAME> | The profile to read the target (adapter, database, schema) from. Defaults to the profile: in dbt_project.yml. Profiles are read from $DBT_PROFILES_DIR/profiles.yml, or ~/.dbt/profiles.yml. |
--load-macros | Accepted for compatibility; the project's macros and dbt_packages/ always load. |
--fail-on-missing-packages | Fail if macros from a package the project requires are missing. |
--var KEY=VALUE, --var-file <FILE> | The values the template reads with var('KEY'). The same options set deployment variables above. |
Jinja without a dbt project (a plain {{ var('t') }}) also renders with --var:
lexega-sql analyze j.sql --var t=users
A value a template needs that you do not supply, or a macro that needs a live database, becomes a placeholder. Placeholders lower the confidence the report prints; supplying --var values, --catalog and --dbt-project reduces them. --strict turns a file whose template did not render into a failure. Jinja in a CI pipeline works the same way: pass the same options to ci.
Format Jinja templates#
fmt --jinja formats a template and keeps the Jinja in the output. fmt --render-jinja, --var, --var-file, --dbt-project and --dbt-profile render first and format the resulting SQL. Neither needs a key. See SQL Formatter.
SQL embedded in Python and notebooks#
--scan-embedded extracts SQL from Python files and notebooks and analyzes each fragment on its own. It needs no key.
| File | What is extracted |
|---|---|
.py | SQL string arguments to calls of methods named sql or execute (spark.sql(...), cursor.execute(...)), where the call is a statement of its own or the value of a plain assignment |
.ipynb | %sql and %%sql cells, and those calls in code cells |
Databricks .py notebook | # MAGIC %sql cells |
Python is parsed, not matched with a regular expression, and a plain string held in a variable is followed to the call (query = "..."; spark.sql(query)). An f-string's interpolations become names, __INTERP_<n>__, so the statement around them is analyzed; what an interpolation inserts is not checked for injection, and an f-string held in a variable is not followed. See Known Limitations for the calls and strings that are not read.
emb/job.py:
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
query = "DROP TABLE staging.orders_raw"
spark.sql(query)
table = "events"
spark.sql(f"TRUNCATE TABLE {table}")
spark.sql("SELECT id FROM orders WHERE id = 1")
lexega-sql analyze emb/job.py --scan-embedded --dialect databricks --detail
The run extracts three fragments, prints the batch summary, and ends with the per-fragment detail. Each fragment is named by the file, line and column where its SQL text starts, and each finding's location is in the Python file:
Per-File Finding Details
══════════════════════════════════════════════════════════════
── emb/job.py:5:10 (1 finding)
[CRITICAL] DROP TABLE detected. Table and all data will be permanently deleted.
↳ Line 5 • `TBL-DROP` • "DROP TABLE staging.orders_raw" • emb/job.py:5:10
── emb/job.py:9:13 (1 finding)
[CRITICAL] TRUNCATE TABLE detected. All rows will be permanently deleted.
↳ Line 9 • `TBL-TRUNCATE` • "TRUNCATE TABLE __INTERP_1__" • emb/job.py:9:13
The SELECT on line 10 has no finding, so it is not listed. Pass a directory with -r and --scan-embedded to include .py and .ipynb files with the .sql files; without the flag a directory scan reads only *.sql. Set --dialect to the SQL the code runs; the examples here use databricks. In a pull-request review, --scan-embedded also reads the Python and notebook files the range changes.
Something missing or wrong? Open an issue on GitHub or write to [email protected].