[Bug] Cannot compile models until they have been run

Is there an existing issue for this?

I have searched the existing issues

Describe the issue

When trying to compile a project using this package, in int_zendesk__ticket_schedules it tries to fetch a default_schedule_id from the materialised data. If you have not already run the models then the table it tries to fetch this from does not exist, and you get an error.

Relevant error log or model output

Runtime Error
  Runtime Error in model int_zendesk__ticket_schedules (models/intermediate/int_zendesk__ticket_schedules.sql)
    404 Not found: Table <gcp_project>:_zendesk_source.stg_zendesk__schedule was not found in location EU

Expected behavior

The models successfully compile without any of the data this package generates already being in place.

dbt Project configurations

No special configuration. I can reproduce this with a fresh project generated by dbt init

Package versions

0.10.0

What database are you using dbt with?

bigquery

dbt Version

Core:

installed: 1.4.4
latest: 1.4.4 - Up to date!

Plugins:

bigquery: 1.4.1 - Up to date!

Additional Context

The code which creates the error is here
https://github.com/fivetran/dbt_zendesk/blob/v0.10.0/models/intermediate/int_zendesk__ticket_schedules.sql#L26-L44

It is wrapped in an {% if execute %}, which I assumed would prevent this from running during a dbt compile, but this does not appear to be working.

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.

Hi @tim-summerton-brier thanks for opening this issue. We actually saw a similar issue a few weeks ago regarding the date_spine model having the same issue.

Thank you for investigating this and calling out the problematic lines. I believe if we change the following line to use a source call as opposed to a reference, then that should do the trick!

dbt_zendesk/models/intermediate/int_zendesk__ticket_schedules.sql

Line 33 in 1c7d4fc

from {{ ref('stg_zendesk__schedule') }}

I can have a version available to you in a few minutes to try and see if this edit addresses the issue you are facing.

@tim-summerton-brier, as I am looking into this a bit more I am realizing that changing this to be a source reference would not work for all our users. Reason being is that the ticket_schedule table is only created if an account is using multiple schedules. Therefore, an account could have the schedules turned on, but not have this source.

We currently account for this by creating an empty tmp model if there is only one schedule. As such, we would need to reference the created model for most cases as opposed to the source. 🤔

I will keep thinking through this and will get back to you with any other thoughts or solutions as opposed to just running the package before compiling.

Edit: I was looking at the wrong model 🤦. The problem is the stg_zendesk__schedule as opposed to stg_zendesk__ticket_schdeule. Nevertheless I am still running into an issue with a quick solution, but will keep you posted.

Hi @tim-summerton-brier I was able to implement a hot fix to resolve the core issue you identified. When you have a moment, would you be able to try the following version of the package in your packages.yml?

packages:
  - git: https://github.com/fivetran/dbt_zendesk.git
    revision: hotfix/schedule-source-adjustment
    warn-unpinned: false

You can use the above in place of the official version of the zendesk package. Let me know if you are able to dbt compile using this version. Thanks!

Hi @fivetran-joemarkiewicz
That hotfix version is working for me, I can now compile with no issue. Thanks!
When can we expect the hotfix to be available in the official release?

That is great to hear!

I will open the PR today and you can expect the release to be live most likely early next week.

Hi @tim-summerton-brier thanks again for raising this to our team. The PR has since been merged and is live on the latest version of the package!

As such, I will close out this issue. Please feel free to reopen if the original issue persists.