dbt-cst

A lossless dbt model parser written in Rust with Python bindings. It parses a model (Jinja templated SQL) into a concrete syntax tree (CST) that keeps every comment, blank line, indentation and Jinja tag, so a file can be read, modified and written back out with its formatting intact:

from dbt_cst import parse

doc = parse(source)
assert str(doc) == source  # byte for byte, for every model in albert-dbt-analytics-transforms

The tree groups the SQL into queries, clauses, select columns and CTEs, and the Jinja into blocks, without interpreting either: a column is the text between two commas, a WHERE clause is the text between its keyword and the next clause keyword. That is enough to add and remove columns in a model someone has customized by hand, which rewriting the file from a template cannot do. The Python package is an abi3 wheel built against Python 3.9, so one wheel works on Python 3.9 and 3.11.

Python usage

from dbt_cst import parse, DbtSyntaxError

doc = parse(path.read_text())

    doc.config["materialized"]                  # "view"
    doc.config["materialized"] = "table"        # quoted like the call's other strings
    doc.config["tags"] = ["pii"]                # added in the call's layout: one per line, trailing comma
    doc.config.set_raw("schema", 'var("schema")')
    del doc.config["alias"]

    # Example: Modifying multiple config values
    doc.config["materialized"] = "table"
    doc.config["schema"] = "analytics"
    doc.config["tags"] = ["pii", "finance"]
    doc.config["enabled"] = False

    # Example: Using raw expressions for dynamic values
    doc.config.set_raw("alias", 'var("model_alias")')
    doc.config.set_raw("schema", 'var("schema")')

    # Example: Removing config values
    if "description" in doc.config:
        del doc.config["description"]

    doc.sources                                 # [("trading", "apex_apexentry")]
    doc.refs                                    # [("users",), ("package", "model")]

query = doc.query                           # the model's own SELECT, after any CTEs
select = query.select
select.names                                # ["account_id", "activity_id", "percent"]
"account_id" in select                      # True, compared ignoring case

select.add("user_id")                       # appended in the select's comma style
select.add("amount / 100 AS dollars", index=select.index("account_id") + 1)
select.remove("activity_id")                # with its comma and the comments above it
select.find("percent").text = '"percent" AS pct'

renamed = query.cte("renamed").query.select # CTE bodies are queries too
for query in doc.queries():                 # every query, subqueries included
    ...

path.write_text(str(doc))

API

parse(source) -> Document raises DbtSyntaxError (a ValueError) with line, column and offset attributes. Only a Jinja tag that is never closed is an error. SQL that does not fit the expected shape still parses: it ends up as unstructured tokens and still round trips.

Config values are Jinja literals: strings, numbers, booleans, None, lists and dicts. Reading an argument whose value is an expression such as var("x") raises ValueError; use raw() for its source text.

Column.name is the alias, or the last part of a plain column reference (t.id gives id), unquoted. It is None for an unaliased expression or a bare Jinja tag such as {{ dbt_utils.star(...) }}.

Handles are live: a Column from select.columns stays attached to the document, and two handles to the same node compare equal.

Building new models

Model, Source and Ref build a model from Python, for writing a new file or parsing into a Document to edit further:

from dbt_cst import Model, Source

model = Model(
    "id",
    '"name"',
    from_=Source("banking", "banking_account"),
    where="NOT __deleted\nOR __deleted IS NULL",
    config={"materialized": "view", "schema": "banking"},
    comma="leading",                     # or "trailing", the default
)
path.write_text(str(model))
doc = model.to_document()

The config call gets one argument per line with a trailing comma, its values rendered as Config.__setitem__ renders them. SELECT gets one column per line, and FROM and WHERE each start a line. Columns are SQL source, so quote identifiers that need it. A column that parses as more than one select item raises ValueError.

How it parses

1. Lexing (src/lexer.rs) splits the file into tokens covering every byte. Jinja tags end where Jinja says they do, skipping string literals and nested brackets, so {{ {'a': 1} }} and {{ "}}" }} are one tag each. A tag inside a SQL string or comment is absorbed into that token. {% raw %} sections are one token.
2. Grouping (src/parser.rs) pairs (/), [/] and Jinja block tags with one stack. Any {% name %} whose {% endname %} appears in the file is opened tentatively, so tags from Jinja, dbt (materialization, test, snapshot) and packages all work without a list of names. A tag that never closes, like a one line {% set %}, or one whose end would cross a parenthesis, is put back as a plain token. elif and else split blocks into branches.
3. Structure: a top level SELECT or WITH starts a query, which is split into clauses at top level clause keywords. SELECT items are split at top level commas, and WITH clauses into CTEs. "Top level" means outside any bracket or Jinja block, so commas in function calls and keywords in subqueries never split anything. A Jinja block that supplies its own commas, like {% for c in cols %}{{ c }},{% endfor %}, separates the columns around it.

The lexer is written with nom, like looker-cst's parser. The one hand written scan is the end of a {{ }} or {% %} tag, which tracks bracket depth and skips string literals the way Jinja's own lexer does.

Rust usage

let doc = dbt_cst::parse(&source)?;
let query = doc.query().unwrap();
let select = dbt_cst::query::clauses_named(&query.read(), "SELECT").remove(0);
dbt_cst::edit::insert_item(&mut select.write(), usize::MAX, "user_id")?;
assert!(doc.to_string().contains("user_id"));

cargo run --example dump -- path/to/model.sql prints the tree.

Development

cargo test                           # Rust tests, cloning the corpus into target/corpus on first run
uv run pytest                        # builds the extension with maturin, then runs the Python tests
DBT_EXTRA_REPOS=../albert-dbt-analytics-transforms cargo test --release --test corpus

The corpus is the 14 public dbt projects listed in tests/corpus_repos.txt (dbt-utils, dbt-expectations, automate-dv, the Fivetran packages and others, about 1,700 .sql files). Both test suites shallow clone them into target/corpus on first use, or into DBT_CORPUS when set. A failed clone skips those files unless REQUIRE_DBT_CORPUS is set, as in CI. DBT_EXTRA_REPOS adds more checkouts, separated by :.

• tests/corpus.rs round trips every file, adds and removes a column in every SELECT, removes each column in turn, and adds and removes a config argument.
• tests/python/test_corpus.py does the same read, modify and write cycle through the Python API, one test per file.
• tests/fixtures/ holds awkward inputs (CRLF, unicode, unterminated quotes, unbalanced brackets, unpaired Jinja blocks) that both suites round trip, and edge_cases.sql, a customized model whose structure both suites check.
• tests/python/test_albert_models.py checks every model in a sibling albert-dbt-analytics-transforms checkout (or ALBERT_DBT_REPO) against the field list DBTGenerator reads today.