CTE no longer interpolated into target compiled query for ephemeral model

[cmcnicoll]

Describe the bug

Recently upgraded to 0.19. For some ephemeral models, I am no longer able to run the compiled query as-is. Sample error: Invalid operation: relation "__dbt__cte__xyz" does not exist;.

Steps To Reproduce

Issue occurs when an ephemeral model references another ephemeral model. The compiled query contains select * from __dbt__CTE__xyz, but the CTE query is missing.

Expected behavior

Be able to run all compiled queries as-is.

Screenshots and log output

N/A

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

$ dbt --version
installed version: 0.19.0
   latest version: 0.19.0

Up to date!

Plugins:
  - bigquery: 0.19.0
  - postgres: 0.19.0
  - redshift: 0.19.0
  - snowflake: 0.19.0

The operating system you're using: Windows 10 Pro

The output of python --version: Python 3.8.7

Additional context

N/A

[jtcohen6]

Good catch @cmcnicoll, and thanks for the bug report. This is a regression in v0.19.0 associated with our rework of compiled_sql and removal of injected_sql (#2834). Handling for ephemeral models was the trickiest piece of that, and the ephemeral-of-ephemeral compiled_sql is still not quite right. We'll work on a fix for v0.19.1.

models/my_ephemeral_model:

{{ config(materialized = 'ephemeral') }}
select 1 as fun

models/another_ephemeral_model:

{{ config(materialized = 'ephemeral') }}
select * from {{ ref('my_ephemeral_model') }}

models/my_other_model:

select * from {{ ref('another_ephemeral_model')}}
union all
select 2 as fun

dbt v0.19.0

✅ target/compiled/[project_name]/models/my_ephemeral_model:

select 1 as fun

❌ target/compiled/[project_name]/models/another_ephemeral_model.sql

select * from __dbt__CTE__my_ephemeral_model

✅ target/compiled/[project_name]/models/my_other_model.sql:

with __dbt__CTE__my_ephemeral_model as (


select 1 as fun
),  __dbt__CTE__another_ephemeral_model as (


select * from __dbt__CTE__my_ephemeral_model
)select * from __dbt__CTE__another_ephemeral_model
union all
select 2 as fun

dbt v0.18.1

target/compiled/[project_name]/models/another_ephemeral_model.sql

with __dbt__CTE__my_ephemeral_model as (

select 1 as fun
)select * from __dbt__CTE__my_ephemeral_model

[gshank]

Ephemeral models referencing other ephemeral models cannot be resolved to 'compiled_sql' at the same time as other models because the enclosing non-ephemeral models need the non-compiled sql. The compiled sql could be saved in some other place and updated at the end of compilation, or re-compiled at the end of compilation. This code is in core/dbt/compilation.py, in the '_recursively_prepend_ctes' method.