QUESTION - Zendesk__ticket_backlog not working because of invalid identifier ASSIGNEE_ID about dbt_zendesk

[fivetran-joemarkiewicz]

Hi @jordstew thanks for opening this Question! I am sorry to hear you have run into an error on the zendesk__ticket_backlog model.

This package is intended to run without variable configuration (apart from possibly disabling models if you do not have the table), which is strange to see you are running into an error like this. I just tested this quickly on my end to ensure my models would succeed without variable config and it succeeded with a full refresh run. Have you tried running your package with dbt run --full-refresh as this may do the trick?

[jordstew]

Hey @fivetran-joemarkiewicz , that does not work, still same error:

Completed with 1 error and 0 warnings:

Database Error in model zendesk__ticket_backlog (models/zendesk__ticket_backlog.sql)
000904 (42000): SQL compilation error: error line 644 at position 35
invalid identifier 'TICKET_FIELD_HISTORY.ASSIGNEE_ID'
compiled SQL at target/run/zendesk/models/zendesk__ticket_backlog.sql

When I look at the compiled code, like I described in my first post, it is clear why this is erroring and not working. I have no custom variables in my dbt_project.yml file besides this:

vars:
  zendesk_schema: ZENDESK_SUPPORT_SECURE
  zendesk_database: RAW
  zendesk_source:
    using_schedules: false
    group: "{{ source('zendesk', 'group_') }}"        # Disable if you do not have the schedule and ticket_schedule tables, or if you do not want metrics reported in business hours    # Disable if you are not using SLAs 
  zendesk:
    using_schedules: false

reason for the group in the zendesk_source variable is because we have to recreate it as GROUP_ because group is a reserved word in snowflake and it does not work when have a table simply named group, as it thinks we are doing a group by.

Any other ideas, Joe?

[fivetran-joemarkiewicz]

Hmm my variable config looks very similar to yours, but I am unable to replicate the error. Would you be able to clarify which version of the package you are using?

Looking at your past comments I noticed the models are CTE's, whereas for me they are materializing as tables. I also see your error line for the backlog model is at line 644, while my backlog model target only contains ~70 lines?

[jordstew]

Would it help if I sent you the compiled query for reference? I am using version 0.4.1

[fivetran-joemarkiewicz]

As it seems the compiled query is extremely long, I feel it would probably be best if we would be able to debug this live.

You can schedule some time to chat with myself and members of our team on our Calendly link for Thursday so we can debug live and hopefully get the package running successfully for you! Let me know if you have any questions.

[jordstew]

Hey Joe I figured it out. So for the first version of zendesk hub and zendesk source hub, we did the following in our dbt_project.yml:

  zendesk:
    #+database: EDW
    #+schema: ZENDESK_SECURE
    +database: DE_SANDBOX
    +schema: JSTEW
    +materialized: ephemeral
    zendesk_sla_breach:
      +materialized: table
    zendesk_ticket_enriched:
      +materialized: table
    zendesk_ticket_field_history:
      +materialized: table
    zendesk_ticket_metrics:
      +materialized: table
    zendesk__ticket_summary:
      +materialized: table
    zendesk__ticket_backlog:
      +materialized: table
    
  zendesk_source:
    #+database: EDW
    #+schema: ZENDESK_STG_SECURE
    +database: DE_SANDBOX
    +schema: JSTEW
    +materialized: table

we did this because we want the models to be materialized as tables instead of views. this worked for the first version of the zendesk hub, but now having this line about all other materializations being ephemeral seemed to break it:

  zendesk:
    #+database: EDW
    #+schema: ZENDESK_SECURE
    +database: DE_SANDBOX
    +schema: JSTEW
    **+materialized: ephemeral**

having this works:

  zendesk:
    #+database: EDW
    #+schema: ZENDESK_SECURE
    +database: DE_SANDBOX
    +schema: JSTEW
    #+materialized: ephemeral
    zendesk_sla_breach:
      +materialized: table
    zendesk_ticket_enriched:
      +materialized: table
    zendesk_ticket_field_history:
      +materialized: table
    zendesk_ticket_metrics:
      +materialized: table
    zendesk__ticket_summary:
      +materialized: table
    zendesk__ticket_backlog:
      +materialized: table

Thanks for the help, Joe. You can close this and cancel our working session, and I will also decline it.

[fivetran-joemarkiewicz]

That's great to hear you were able to get the package to work with the materialization update to the some of those intermediate models.

That also explains the large compile code length. Thanks again for opening this issue and for letting us know you were able to come to a resolution, as such I will close this issue.