Reading Compiled SQL

Concepts covered: Compilation

2 folders under target/

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.

Compiled and run, side by side

select
    project_id,
    sum(case when state = 'merged'
        then 1 else 0 end) as merged_mrs,
    sum(case when state = 'opened'
        then 1 else 0 end) as opened_mrs
from "analytics"."dbt_dev"."stg_merge_requests"
group by project_id

create table "analytics"."dbt_dev"."mr_by_state" as
select
    project_id,
    sum(case when state = 'merged'
        then 1 else 0 end) as merged_mrs,
    sum(case when state = 'opened'
        then 1 else 0 end) as opened_mrs
from "analytics"."dbt_dev"."stg_merge_requests"
group by project_id

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.

create view "analytics"."dbt_dev"."stg_merge_requests" as
select
    id as merge_request_id,
    project_id,
    state,
    created_at
from "analytics"."raw"."merge_requests"

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.

Reasons to open target/ first

A warehouse that rejects a query reports a line number, and that number counts lines of the statement it received. That statement is the file in target/run, and the count includes the wrapper and any leading blank lines. A loop has already made that statement longer than your template, and the wrapper pushes every SELECT line further down. Line 14 in the message may be line 13 of the compiled file and come from line 6 of your template. A reviewer reading a template with 3 Jinja statements in it has to render the loop in their head to know whether the last column ends in a comma; the compiled file shows the answer. The resolved relation names tell you which schema a run built into. And a colleague who has never installed dbt can paste the compiled file into any SQL client and run it, because it contains nothing dbt-specific.

Counting a cited line

Here the wrapper takes 1 line, so every line the warehouse cites sits 1 below its place in the compiled file: a message about line 8 points at the from clause, which is line 7 of the compiled file. An adapter that spreads its wrapper over several lines moves the offset, which is why the run file is where you count. Find the cited line there, then read the same text in the compiled file, where the wrapper is out of the way and the SELECT is the one you wrote, rendered.

Compile alone

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.

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.