[Bug] Ephemeral models fail when used with run_query

[fbertsch]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The ephemeral materialization of some staging/tmp tables cause failures during the compilation stage. Specifically, int_zendesk__calendar_spine depends on the model stg_zendesk__ticket. However it's not explicit: the referenced table is used for a run_query macro. When that macro runs, it tries to run it on the CTE (__DBT__CTE__STG_ZENDESK__TICKET), and that CTE is not included in the run_query macro's query. Since the table doesn't exist, the DBT compilation fails.

Relevant error log or model output

% dbt compile -m +int_zendesk__calendar_spine
17:50:37  Running with dbt=1.1.0
17:50:37  Unable to do partial parsing because a project config has change
17:50:54  Found 414 models, 579 tests, 0 snapshots, 0 analyses, 651 macros, 4 operations, 3 seed files, 253 sources, 0 exposures, 0 metrics
17:50:54
17:50:55  Concurrency: 4 threads (target='dev')
17:50:55
17:50:59  Encountered an error:
Runtime Error
  Database Error in model int_zendesk__calendar_spine (models/utils/int_zendesk__calendar_spine.sql)
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__STG_ZENDESK__TICKET' does not exist or not authorized.

Expected behavior

I would expect the project to compile without any changes on my end.

dbt Project configurations

My dbt_project.yml contains:

models:
  materialized: ephemeral
  zendesk:
    enabled: true
  zendesk_source:
    enabled: true

Package versions

version 0.8.3, and version 0.6.1 of zendesk_source.

  - package: fivetran/zendesk
    version: [">=0.8.0", "<0.9.0"]

What database are you using dbt with?

snowflake

dbt Version

Fails with both 1.1.0 and 1.0.3. Didn't try <1.

Additional Context

No response

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[fbertsch]

Workaround, for now, is to materialize these tables as views.

[fivetran-joemarkiewicz]

Hi @fbertsch thanks so much for opening this issue.

Looking at the issue you described above, it seems this may be an issue with dbt compile not succeeding before dbt run is executed. Since the calendar spine model depends on the stg_zendesk__ticket model, it cannot compile because that model does not exist yet. I believe since you have run the model, you can execute dbt compile or dbt run without needing to override the materialization. Would you be able to attempt this and see if that is true?

A possible workaround to ensure dbt compile succeeds before dbt run is to have the calendar spine refer to the source ticket table instead of the staging model generated by the package. I wonder if setting the --depends-on could resolve this as well in a similar fashion to what is performed here.

Let me know if changing the materialization now works. Thanks!

[fbertsch]

@fivetran-joemarkiewicz thank you for responding! On my end, I've tried two things.

1. Using ephemeral materialization for stg_zendesk__ticket:
   When I run it this way, it doesn't matter whether the model is available as a view/table in Snowflake or not (I've tried it both ways, which of course doesn't really make sense for an ephemeral materialization). Both ways end up with the same DBT error: Object '__DBT__CTE__STG_ZENDESK__TICKET' does not exist or not authorized.. See above for why this error arises

2. Using view materialization for stg_zendesk__ticket:
   This fails during compilation of int_zendesk__calendar_spine, but only when stg_zendesk__ticket has not yet been materialized. You can work around this by first running dbt run -m +stg_zendesk__ticket, which will materialize the stg_zendesk__ticket; then the int_zendesk__calendar_spine is able to compile.

A possible workaround to ensure dbt compile succeeds before dbt run is to have the calendar spine refer to the source ticket table instead of the staging model generated by the package

I believe this is the correct solution. The real problem is that the DBT compilation stage is trying itself to query a model, which may or may not exist.

[fbertsch]

Aside: This could possible be filed upstream, to try and get DBT to materialize ephemeral models correctly during the run_query macro if run during compilation. I can't imagine this is a straightforward fix, though.

[fivetran-joemarkiewicz]

This could possible be filed upstream, to try and get DBT to materialize ephemeral models correctly during the run_query macro if run during compilation. I can't imagine this is a straightforward fix, though.

@fbertsch, unfortunately I don't believe that will be a straightforward fix 😢 . However, I think your solution is probably the best one! I noticed you mentioned you were open to creating a PR! Let me know if you are still interested in making this contribution as we would love to incorporate this solution so others don't experience the same issue as you. Let me know if you are still open to contributing!

Otherwise, I will be happy to open a PR on my end 😄

[fbertsch]

@fivetran-joemarkiewicz I'll let you take it from here, you have more context than I :)

[fivetran-reneeli]

Hi @fbertsch , we've included this change in our most recent release! Thanks for raising this and let us know if there's anything else!