About dbt compile command

dbt compile generates executable SQL from source model, test, and analysis files. You can find these compiled SQL files in the target/ directory of your dbt project.

The compile command is useful for:

1. Visually inspecting the compiled output of model files. This is useful for validating complex Jinja logic or macro usage.
2. Manually running compiled SQL. While debugging a model or schema test, it's often useful to execute the underlying select statement to find the source of the bug.
3. Compiling analysis files. Read more about analysis files here.

Some common misconceptions:

• dbt compile is not a pre-requisite of dbt run, or other building commands. Those commands will handle compilation themselves.
• If you just want dbt to read and validate your project code, without connecting to the data warehouse, use dbt parse instead.

Interactive compile

Starting in dbt v1.5, compile can be "interactive" in the CLI, by displaying the compiled code of a node or arbitrary dbt-SQL query:

• --select a specific node by name
• --inline an arbitrary dbt-SQL query

This will log the compiled SQL to the terminal, in addition to writing to the target/ directory.

For example:

dbt compile --select "stg_orders"                           
dbt compile --inline "select * from {{ ref('raw_orders') }}"

returns the following:

dbt compile --select "stg_orders"                           

21:17:09  Running with dbt=1.7.5
21:17:09  Registered adapter: postgres=1.7.5
21:17:09  Found 5 models, 3 seeds, 20 tests, 0 sources, 0 exposures, 0 metrics, 401 macros, 0 groups, 0 semantic models
21:17:09  
21:17:09 Concurrency: 24 threads (target='dev')
21:17:09  
21:17:09  Compiled node 'stg_orders' is:
with source as (
    select * from "jaffle_shop"."main"."raw_orders"

),

renamed as (

    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status

    from source

)

select * from renamed

dbt compile --inline "select * from {{ ref('raw_orders') }}"

18:15:49  Running with dbt=1.7.5
18:15:50  Registered adapter: postgres=1.7.5
18:15:50  Found 5 models, 3 seeds, 20 tests, 0 sources, 0 exposures, 0 metrics, 401 macros, 0 groups, 0 semantic models
18:15:50  
18:15:50  Concurrency: 5 threads (target='postgres')
18:15:50  
18:15:50  Compiled inline node is:
select * from "jaffle_shop"."main"."raw_orders"

The command accesses the data platform to cache-related metadata, and to run introspective queries. Use the flags:

• --no-populate-cache to disable the initial cache population. If metadata is needed, it will be a cache miss, requiring dbt to run the metadata query. This is a dbt flag, which means you need to add dbt as a prefix. For example: dbt --no-populate-cache.
• --no-introspect to disable introspective queries. dbt will raise an error if a model's definition requires running one. This is a dbt compile flag, which means you need to add dbt compile as a prefix. For example:dbt compile --no-introspect.

FAQs

Why dbt compile needs a data platform connection

dbt compile needs a data platform connection in order to gather the info it needs (including from introspective queries) to prepare the SQL for every model in your project.

dbt compile

The dbt compile command generates executable SQL from source, model, test, and analysis files. dbt compile is similar to dbt run except that it doesn't materialize the model's compiled SQL into an existing table. So, up until the point of materialization, dbt compile and dbt run are similar because they both require a data platform connection, run queries, and have an execute variable set to True.

However, here are some things to consider:

• You don't need to execute dbt compile before dbt run
• In dbt, compile doesn't mean parse. This is because parse validates your written YAML, configured tags, and so on.

Introspective queries

To generate the compiled SQL for many models, dbt needs to run introspective queries, (which is when dbt needs to run SQL in order to pull data back and do something with it) against the data platform.

These introspective queries include:

• Populating the relation cache. For more information, refer to the Create new materializations guide. Caching speeds up the metadata checks, including whether an incremental model already exists in the data platform.
• Resolving macros, such as run_query or dbt_utils.get_column_values that you're using to template out your SQL. This is because dbt needs to run those queries during model SQL compilation.

Without a data platform connection, dbt can't perform these introspective queries and won't be able to generate the compiled SQL needed for the next steps in the dbt workflow. You can parse a project and use the list resources in the project, without an internet or data platform connection. Parsing a project is enough to produce a manifest, however, keep in mind that the written-out manifest won't include compiled SQL.

To configure a project, you do need a connection profile (profiles.yml if using the CLI). You need this file because the project's configuration depends on its contents. For example, you may need to use {{target}} for conditional configs or know what platform you're running against so that you can choose the right flavor of SQL.