[Feature] Support references to ephemeral models in hooks

[siljamardla]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [X] I have searched the existing issues, and I could not find an existing issue for this feature
• [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

I have a macro that is called in a post-hook. In that macro I have used a reference to a staging table stg_XX that has ephemeral materialisation. When running my model that calls this post-hook, I get the following error:

Runtime Error in model XXX (models/XXX.sql)
  [TABLE_OR_VIEW_NOT_FOUND] The table or view `__dbt__cte__stg_XX` cannot be found. Verify the spelling and correctness of the schema and catalog.
  If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog.
  To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS.;

Describe alternatives you've considered

When I switch the staging table to view materialisation, I no longer get the same error.

However, going with this option forces me to turn my staging models from ephemeral to views, which has its own downsides and upsides. Having the possibility to use staging models in hooks should not make me decide.

Who will this benefit?

Anyone who wants to reference ephemeral models in hooks.

Are you interested in contributing this feature?

No response

Anything else?

Low priority, just want to point out the unexpected behaviour.

Found a few other references about this topic: https://stackoverflow.com/questions/64622026/how-to-refer-ephemeral-model-in-pre-post-hook-of-an-incremental-model?newreg=637b64465aca4988a305856104481766 https://discourse.getdbt.com/t/how-to-refer-ephemeral-model-in-pre-post-hook-of-an-incremental-model/1763 Might be related: https://discourse.getdbt.com/t/how-to-use-sources-inside-a-post-hook/10638

[dbeatty10]

Thanks for reporting this @siljamardla !

I see what you are saying about using an ephemeral model in a post hook. It looks to me like the reference is being added the SQL just fine -- the only piece missing is that it isn't injecting the necessary CTE.

I tried out using a source instead of a ref to an ephemeral model, and that worked fine. So this one is not related: https://discourse.getdbt.com/t/how-to-use-sources-inside-a-post-hook/10638 .

Options

There's a few approaches we could take here:

1. Fully support ephemeral models within hooks
2. Raise a helpful error message during parsing or compilation instead saying this isn't supported
3. Status quo

Priority and workaround

Agreed that this would be a low priority for us, so I'm guessing we'll choose to keep the status quo on this one.

In the meantime, we'd recommend using either a table or view materialization as a workaround.

Here's the small dbt project I used to try out the scenario you described:

Reprex

models/my_ephemeral_model.sql

{{ config(materialized="ephemeral") }}

select 1 as id

models/my_model.sql

{{ config(
    post_hook="select * from {{ ref('my_ephemeral_model') }}"
) }}

select 1 as id

dbt run -s my_model