Jinja and Macros: Beginner

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.

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.

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() 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.

Every command that renders a model writes the result to disk before anything reaches the warehouse. dbt compile renders and stops there. dbt run and dbt build render, write, then execute. In every case the text lands in the target/ folder at the project root, in 2 subfolders that hold 2 different things, and reading the wrong one is a common way to be puzzled by a correct answer.

The folder under each, analytics here, is the name: from dbt_project.yml, and the path under it mirrors your models/ tree. Compiled SQL is your model after Jinja and before materialization. Run SQL is what appears in the warehouse's query history. The difference between the 2 is the wrapper the materialization adds, and nothing else.

A model configured as a view gets the same SELECT under a different first line. Here is the staging model from section 0 as the warehouse received it. The first words of the run file tell you how dbt built the model: it opens with either create table or create view.

The adapter for your warehouse may build into a temporary name first and swap it into place, so the run file can hold more than one statement. The SELECT inside is the compiled file, unchanged.

dbt compile --select mr_by_state renders that 1 model, writes it under target/compiled and prints it in the terminal. It creates no table and no view, so you can check what a loop produces as often as you like without rebuilding anything. Because ref() must resolve, compile still reads the whole project and still needs the connection profile the project uses; what it leaves out is executing a model. Change the template, compile, read, repeat: the loop costs seconds and touches no data.

target/ holds the output of the last command that touched each node. If you edit a model and open its compiled file without recompiling, you're reading the previous version. dbt clean deletes the folder when you want to start from nothing.

{% set states = ['opened', 'merged', 'closed'] %} creates a variable that lives for the rest of the file. It prints nothing, because it's a statement. Writing {{ states[0] }} later prints opened without quotes, because Jinja inserts the raw text of the value. When the SQL needs a string literal, you type the quotes: '{{ states[0] }}' renders to 'opened'. A number set with {% set min_year = 2024 %} renders through {{ min_year }} as 2024, which the warehouse reads as a number because nothing quotes it.

Everything between {% for s in states %} and {% endfor %} is rendered once per item, with s holding the current item. The body is text with expressions in it, so it can hold a single column or a whole UNION branch. The 3 CASE columns from the intro become 1 block with 1 copy to edit, and the 4th state is 1 more entry in the list.

The if on the comma line places a comma only when another state follows. The loop variable it tests and the - inside {%- are explained further down this section.

Each pass printed the same 2 lines with s substituted twice: once inside quotes as a literal the warehouse compares against, once bare as part of a column name. The for statement left a blank line where it stood, and if and endfor did the same, so blank lines separate the columns. The warehouse skips them.

The comma after each column is the detail that breaks copied SQL most often, and {% if not loop.last %},{% endif %} places it only when another column follows. loop is a variable Jinja provides inside every for block, and it carries the position of the current pass.

A loop body can be a whole SELECT. Suppose each region's merge requests land in a raw table of its own, declared as 3 sources. The loop below writes 1 branch per region, and the same loop.last test that placed commas now places union all between branches. ~ is Jinja's string join, so 'merge_requests_' ~ r evaluates to merge_requests_us on the first pass.

materialized can be set in 3 files that all claim the same model. dbt_project.yml sets it for every model under a folder. A properties file sets it for 1 named model. The model file sets it for itself with config(). When they agree there is nothing to decide. When they disagree, dbt follows 1 rule: the setting closest to the model wins.

The + marks a config key as opposed to a folder name, and the nesting follows the folder tree: marts here means models/marts/. A deeper key overrides a shallower one, so every model under models/marts/ is a table and every other model in the project is a view, before any other file has spoken.

Folder rules nest as deep as the folders do, and among them the deepest key whose folder contains the model wins. A model in models/marts/finance/ is a view, one directly in models/marts/ is a table, and one in models/staging/ falls back to the project-wide view. Because a folder rule follows the file's location, moving a file can change how it's built without changing a line inside it. Move a model with no config() and no properties entry from models/staging/ into models/marts/, and the next run builds a table where a view stood.

target/run shows which setting won. A statement that opens with create table or create view is the materialization dbt resolved, whatever the 3 files seem to say, so the habit from section 1 covers configs as well as SQL. One config behaves differently: tags from every level are combined rather than replaced, so a model tagged nightly by its folder and finance by its properties carries both.

Type {{ ref('stg_merge_request') }} with the final s missing, and dbt reports a Compilation Error in model mr_by_state. The message names the file and says the model depends on a node that was not found. Because dbt resolves every ref() when it parses the project, this error arrives before any node runs, and the whole command stops. A {% for %} with no {% endfor %} fails the same way: Jinja cannot finish the template, so there's no SQL to send.

Type stat for state inside the CASE and the template renders without complaint, because Jinja has no idea what a column is. The warehouse rejects the query, and dbt reports a Database Error in model mr_by_state followed by the warehouse's message: a column that does not exist, and the line it appears on. That line counts the statement in target/run, wrapper included, so find it there and then read the same text in the compiled file. Other models keep building; this one fails and its children are skipped, as you saw with failing tests.

The case that confuses people sits between the 2. Remove the if not loop.last guard from section 2's loop and every pass emits a comma, so the compiled file reads as closed_mrs, on 1 line and from on the next. Jinja was content: a comma is text. The warehouse reports a syntax error near from. The heading says Database Error but the cause is in the template, so read the compiled file to see the stray comma, then fix the loop that printed it.