[CT-3168] [Bug] dbt compile is throwing Object Not Found Error

[ankitsr92]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I am running DBT COMPILE and it is failing with error,

Encountered an error: Runtime Error Database Error in model Test_Model (models/staging/views/Test_Model.sql) 002003 (02000): SQL compilation error: Schema 'TEST_DB.TEST' does not exist or not authorized.

The model is calling a Macro within.

Test_Mode.sql

{{ config(
          alias='TEST_MODEL'     
         ,schema='TEST'       
         ,materialized='view'                     
        )
}} 
-- depends_on: {{ ref('BASE_MODEL1') }}
-- depends_on: {{ ref('BASE_MODEL2') }}
{% if execute %}


{{ generate_view (  ref('BASE_MODEL1') , ref('BASE_MODEL2') , 'ARG1', 'TEST_TABLE' ) }}

{% endif %}

The Macro is using dbt_utils.get_single_value(sql_statement) to get some data from a table

{% macro generate_view(source,metadata_source,schema_name,table_name) %}

{% if execute %}

    {% set sql_statement %} 
       <SQL Statement>
       from {{ metadata_source }}
    {% endset %}

    {%- set gen_col_list = dbt_utils.get_single_value(sql_statement) -%}

    {% set ns = namespace(gen_col_list_modified=gen_col_list) %}      

    {%- for key, value in extract_columns.items() -%}  
        {%- set ns.gen_col_list_modified = ns.gen_col_list_modified | replace ( 'JSON:' + value['source'], key )  -%}
    {% endfor %}


    SELECT 
        {{ ns.gen_col_list_modified }} 
    FROM {{ source }}
    WHERE _CURRENT_RECORD = TRUE

{% endif %}

{% endmacro %}

I added if execute at multiple places to avoid this compilation when running dbt compile. But still it is trying to compile the sqls. If I run DBT RUN it will run everything since these dependent models will be created. But as part of deployment into upper env dbt compile is executed which will fail the deployment.

Any known issues with if execute statement?

DBT 1.5 version is being used.

Expected Behavior

DBT COMPILE should not compile SQL within the IF EXECUTE block and not fail.

Steps To Reproduce

Use the code in the Current Behaviour section.

Relevant log output

No response

Environment

- OS:
- Python:
- dbt:

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[dataders]

I added if execute [to avoid] compilation when running dbt compile.

hey @ankitsr92 thanks for raising. the above paraphrased quote brought me back to a time when I also believed the same thing should be happening (wish I could link to me asking basically the same question). Fortunately, someone the issue I raise believed before someone else illuminated the truth to me. so here's me passing this knowledge on.

It comes down to the convolving the ideas of execute and run and the distinction more generally between:

• parsing
• compilation
• execution

dbt uses the term "compile" in a way that's confusing to most software engineers. the reason being is that in dbt, compilation is actually a subset of execution. The reason being that dbt allows for what we call "introspective queries". The dbt_utils.get_single_value() macro that you're using is a prime example.

Rough definition of parsiing

creating internal Python representation of all the YAML and SQL and understanding interdependencies. side effect of parsing is having pure SQL model files

In a project absent of introspective queries, dbt is capable of parsing without needing to connect to the target database. However, introspective queries throw a wrench into this beautiful separation of concerns because resolving them requires connecting to the database. So now we're executing? Yep

IIRC, what {{ if execute }} gets you is that this introspection need not be performed twice, once during compilation and once while while running a model.

Another weird thing about compile is that it doesn't run in DAG order, but rather concurrently up to the thread limit. So if one model’s templated SQL depends on an introspective query that expects another model to have already been materialized, this can lead to errors.

I think this is what you're seeing where you are expecting dbt compile to know that test_model.sql depends on BASE_MODEL1 and BASE_MODEL2, but that's not actually what is happening.

There's a great internal doc with more detail on this that you've inspired us to get out in the open. We hope to have this out in the next week or so.

In the meantime, my recommendation here would be to step back and frame the problem you are trying to solve. You've certainly learned a lot about how dbt works, but my hunch is that if you phrase it from the perspective of the business problem you are trying to accomplish, that folks in the power user of the community Slack will be able to help you out.

I'm going to close this issue, but reply back if you think there's something here that I'm missing.

[dbeatty10]

@ankitsr92 here's some docs that we're working on to explain parsing vs. compilation vs. execution/runtime: #8744

While it's in code review, you can see a preview here.

Update: merged version of those docs here.