Understanding dbt model execution, view replacement, and temporary view handling

In-Depth Discussions
1

I’ve been building some models using staging/intermediate/marts practices. Let’s assume I have some queries in the marts layer that are connected to the intermediate layer, and those queries in the intermediate layer are referenced to the staging layer.

For Question 1, let’s assume I run these two commands:

  • Command 1: dbt run --vars '{ "prefix": "my_prefix", "filter_value": my_filter_value}'
  • Command 2: dbt run --models marts --vars '{ "prefix": "my_prefix", "filter_value": my_filter_value}'

The main difference is that the second command runs only the models in the marts folder. If those models reference any queries in the intermediate layer, they will use the views that are already in the data warehouse. In other words, if I have modified a query in the intermediate layer that feeds one of the marts queries I’m running, that modification will not be included because I did not include the intermediate layer in Command 2. However, if I run Command 1, the modification will be included as dbt will recreate every view from the staging and intermediate layers, including any modifications I have made. Is my understanding correct?

Question 2
After I run dbt run, dbt creates the view for (for example) the staging layer and stores the executed query in target/run/<project_name>/models/staging/<my_database>/<my_query.sql>. When I look at that file, the view is created with this command:

create view "<my_database>"."<my_schema>"."stg_<my_database>__<my_table>__dbt_tmp" as (
    SELECT feature_1, feature_2, feature_3
FROM "<my_database>"."<my_schema>"."<my_table>"
  ) ;

If the view already exists, how is dbt able to overwrite the view if it’s not using the command CREATE OR REPLACE VIEW? If I run this command manually, the error “This view already exists” will arise, right? Why doesn’t this happen with dbt? What is dbt doing under the hood that I’m missing?

Question 3
How, when, or where does dbt get rid of the __dbt_tmp suffix when creating (for example) the view in Question 2? When I look at the data warehouse, the view name is <my_database>"."<my_schema>"."stg_<my_database>__<my_table>, not <my_database>"."<my_schema>"."stg_<my_database>__<my_table>__dbt_tmp. So, when, where, and how does that modification happen?

2

Question 1: Your understanding is correct.

Question 2 and 3: It uses a create view because it is creating a view that will be dropped at the end of the execution. (__dbt_temp).

If you go to logs/dbt.log, you can see what dbt is doing. If it is following the default behavior, and if I am not wrong (suppose the name of your view is my_view)

  • renames my_view to my_view__backup
  • creates my_view__dbt_tmp
  • if it was created successfully, rename my_view__dbt_tmp to my_view
  • drops my_view__backup

You can see these alter rename statements in logs/dbt.log