Jinja Renders First

Concepts covered: Compilation

2 readers, 1 file

A model file is written for 2 readers. dbt reads it first. To dbt the file is a template, and every piece of Jinja inside it runs to produce text. The warehouse reads second, and the only thing it receives is that text: plain SQL with no braces left in it. The order never varies. Rendering finishes before a single byte crosses the connection, so the warehouse needs no Jinja interpreter and has none.

You have been writing Jinja since your first model. {{ ref('stg_merge_requests') }} is a Jinja expression that calls a function, and {{ source('raw', 'merge_requests') }} is another. dbt builds both functions in and makes them available inside every model, and each returns text: the fully qualified name of a relation, quoted so the warehouse resolves it without guessing. The call disappears and the name stands where it stood. A function you write yourself in Jinja is called a macro, and a later lesson in this track covers writing one.

select
    id as merge_request_id,
    project_id,
    state,
    created_at
from {{ source('raw', 'merge_requests') }}

select
    id as merge_request_id,
    project_id,
    state,
    created_at
from "analytics"."raw"."merge_requests"

Only the from line changed. The 4 column lines passed through byte for byte, because Jinja treats everything outside its delimiters as text it doesn't understand and does not touch. That indifference is what makes it useful: the renderer cannot tell a SQL keyword from a string literal, so it can write any SQL at all. It also means you can predict the output with a pen: copy the file, replace each expression with its value, and you hold the SQL.

An expression need not call a function at all. {{ 60 * 24 }} renders to 1440, so a filter written where minutes_open > {{ 60 * 24 }} reaches the warehouse as where minutes_open > 1440. Jinja did the multiplication, and the query log shows only the product. Whatever runs inside the braces runs in dbt before the warehouse is involved, whether dbt is on your laptop or on the machine that schedules the build.

3 delimiters

An expression prints. A statement acts without printing. {% set %} is one, and section 2 covers it along with {% if %} and {% for %}. Each leaves an empty spot where it stood, and that spot keeps its line break, so compiled SQL often opens with blank lines. A Jinja comment vanishes before the SQL is sent. A SQL line comment, the kind that opens with 2 hyphens, is text to Jinja, so it survives rendering, and the warehouse reads it and ignores it. Pick the delimiter by who should be able to see the note.

config() prints nothing

{{ config(materialized='table') }}

select
    project_id,
    count(*) as open_mrs
from {{ ref('stg_merge_requests') }}
where state = 'opened'
group by project_id

select
    project_id,
    count(*) as open_mrs
from "analytics"."dbt_dev"."stg_merge_requests"
where state = 'opened'
group by project_id

config() sits inside expression braces, so by the table above it should print. It returns an empty string on purpose. Its work happens inside dbt: it records that this model is a table, and dbt later wraps the rendered SELECT in a create table statement because of that record. The warehouse receives the wrapper and the SELECT. It never learns that a configuration existed, and the blank lines at the top are the only trace the call leaves behind.

About this lesson

This section is part of the Jinja and Macros: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.