[Bug] <Zendesk Package Missing bunch of date values>

[zztkitty]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Hi, I am currently working on zendesk__ticket_metrics Table in Snowflake. However, when we tried to get some of the tickets, all of these shown as zero for business metrics but for calendar date (mertics) we do have value. When we tracked back to what source (back to int_zendesk__schedule_spine) that created the date we found that we have bunch of gaps (we do not have value from 2024-07-07 to 2024-08-04 and also 2024-08-11 to 2024-2024-11-03 ) for the date so that's why the ticket created between the those periods do not have any value

<img width="1141" alt="Screen Shot 2024-08-16 at 10 45 51 AM" src="https://github.com/user-attachments/assets/c00e358c-55f7-45dd-8984-b4146d2ab03d">

Relevant error log or model output

zendesk__ticket_metrics
int_zendesk__schedule_spine

Expected behavior

The expected thing should be all the gaps need to be filled in appropriately so that the ticket that create in these date do have value

dbt Project configurations

int_zendesk__schedule_spine: Until_From to Until_End

Package versions

zendesk 0.16.0

What database are you using dbt with?

snowflake

dbt Version

////

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.

[fivetran-reneeli]

Hi @zztkitty! Could you provide more context into this issue you're seeing? For example, do you have a schedule where you see the gap? Are there holidays in that schedule and what's the time zone?

[zztkitty]

Hi, taking this one as an example, we missed a lot of dates like:

1. 2024-08-11 to 2024-11-03
2. 2024-07-07 to 2024-08-04
3. 2024-06-23 to 2024-07-07

[zztkitty]

These schedule_id is corresponding to business_hour but miss a lot of date for business hours.

[zztkitty]

These are all Eastern Time Zone

[fivetran-reneeli]

Thanks @zztkitty-- a few followups:

• I noticed that every schedule period (every row) has is_holiday_week = true. Is that so, where each week has a holiday?
• As for the missing weeks-- any chance those are completely holidays?

[zztkitty]

@fivetran-reneeli

I checked it, all the missing periods are all business hours (it's impossible for the whole week to be holiday)
I mean for those is_holiday_week = true, between those date, there are a lot of missing periods that are not holiday.

The first picture is the date that I added 2024-06-23 to 2024-06-30 while the second picture is the date from the fivetran dbt package which are missing 2024-06-23 to 2024-06-30 and it's not a holiday week

[fivetran-reneeli]

Thanks @zztkitty! I see your issue, however am unable to reproduce it with our own data. If you could open a support ticket, this would allow us to see into your data so we can investigate this further.

[fivetran-reneeli]

Marking as blocked for the time being; let us know when you've been able to create that support ticket and we'll take a look from there!

[zztkitty]

Hi @fivetran-reneeli, I have submitted the ticket support request with ID #219240, please let me know any update from you end.

FYI: I accidentally created two same tickets where you can ignore the #219242.

[fivetran-catfritz]

Hi @zztkitty to give you an update on this one. Thank you for opening the ticket! I was able to recreate the issue with int_zendesk__schedule_spine you were seeing. I am working on identifying the cause and fix and will keep you posted.

[fivetran-catfritz]

Hi @zztkitty we were able to identify the issue that you saw in int_zendesk__schedule_spine. The fix is a bit complex since we will need to update the window logic used in that model, but we plan to address this in the next couple weeks.

[fivetran-catfritz]

Hi @zztkitty I have reworked the int_zendesk__schedule_spine that should fix the issues your seeing. I have created a test branch of this package with the updates. If you would like to install it, you can add the following code in place of the existing zendesk in your packages.yml.

- git: https://github.com/fivetran/dbt_zendesk.git
  revision: feature/historical-schedules
  warn-unpinned: false

Let me how this works for you!

[LGBI]

I has this exact issue and this git branch worked for me. What would be the next step? Should I continue using this git branch in my package.yml going forward?

[zztkitty]

@fivetran-catfritz

Thank you for working on resolving the issue. Could you please confirm if the changes to the package were only made to the int_zendesk__schedule_spine model? Were there any updates made to its dependent sources as well?

I've been manually testing it on my end today, but I'm still seeing a lot of gaps.

Thank you!

[fivetran-catfritz]

@zztkitty Thanks for trying it out! I made changes to int_zendesk__schedule_spine and upstream as well. Here are all my changes and had made some changes just recently. I'm hoping you didn't catch the test branch during one of my mistake pushes. Would you be able to re-install the test branch, re-run and share an example of the gaps you're still seeing? You will still see gaps where there are holidays, but the larger gaps were looking closed when I tested.

[zztkitty]

Hi @fivetran-catfritz, thank you for your clarification.

We are unable to install it because of the version (v1.0 instance). That's why I manually test it from the source . However, even after testing manually, the gaps remain the same as before.

Additionally, regarding the note:

"You will still see gaps where there are holidays, but the larger gaps were looking closed when I tested."

Does this mean we will continue to see small gaps during holidays, but the larger gaps—like those between holiday and non-holiday periods—are covered?

[fivetran-catfritz]

@zztkitty Yes, your understanding is correct. When you run manually, how are you compiling the code? When last I tested on your data, it was looking good, but our access expired today so I didn't get to test with today's changes. I will ask customer support to re-establish access, and I'll check it out again.

[zztkitty]

@fivetran-catfritz
Thanks for confirming! When I run manually, I compile the code by executing SQL scripts directly in the Snowflake environment. I usually ensure that all dependencies are correctly set up and test the query against our current dataset.

Do you have any plans to address the gaps between holidays?

Also, could you please keep me updated once access is re-established and everything is good from your end. Let me know if there’s anything I need to do on my end to help re-enable access.

Thanks!

[fivetran-catfritz]

@zztkitty Sorry I should have mentioned--what I meant for the holiday gaps, if for example you have an entire week that is a holiday, then you will see a gap for that entire week. If you have at least 1 non-holiday in a week, then you will have schedule records for that entire week.

As for compiling, I did add some intermediate models, so I'm wondering if that's causing some discrepancies. Usually what I do is compile with the upstream models set as ephemeral. I could send you what I compiled for Snowflake, but it ends up being almost 1000 lines long. I could email it to you if that sounds helpful. I can get your email from our records but want to make sure you're ok with that.

Also for access, a customer support rep will reach out to you re-establish access!

[zztkitty]

@fivetran-catfritz Yes, this would be very helpful!!!! Thank you so much for offering to send it over! I appreciate your support and assistance in resolving this.

[fivetran-catfritz]

@zztkitty I just sent the compiled model to you. Also it looks like the access request was sent this morning as well!

[zztkitty]

@fivetran-catfritz Thank you! I received it. and please keep me updated everything is good from your end.