[Bug] sla policy events failing due to duplicate values about dbt_zendesk

[fivetran-reneeli]

Hey everyone! We'll be taking this on in our current sprint. Thanks for the data shares which will help tremendously! We'll post in this thread with any questions/updates.

[fivetran-joemarkiewicz]

Hi @mark-ogrady thanks for raising this issue. We actually have seen a few issues pop up recently around the SLA policies table and I believe we may have a workaround.

Would you be able to test the following WIP version of the package and let me know if you still see duplicates after using this version of the package:

packages:
  - git: https://github.com/fivetran/dbt_zendesk.git
    revision: bugfix/sla-investigations
    warn-unpinned: false

[fivetran-catfritz]

Thank you @nbdaniels! I confirm we received it and will take a look soon.

@laurits-inpay If you would also be willing to share an output sample of the below query, that would be helpful. You can send it to [email protected] or let me know if you prefer to do a data share if you use Snowflake or Bigquery.

select *
from <schema>.int_zendesk__reply_time_combined
where ticket_id = <ticket_id>

[nbdaniels]

@fivetran-reneeli I apologize for the delay! I was OOO but I am testing this week. Thank you for making the changes and I'll let you know if we run into any issues!

[fivetran-joemarkiewicz]

Hi @solmazb thanks for chiming in and sharing that you are also experiencing this issue. Unfortunately we do not have a fix available at the moment. The branch we shared above does not seem to accurately address the duplication issue.

We are struggling to find/apply a fix as we are unable to recreate the duplication issue which seems to be occurring during daylight saving schedule changes. @laurits-inpay and others in this thread have been helpful with sharing information around how the issue may be occurring. From this we have been able to identify that the duplication seems to be originating from the int_zendesk__reply_time_combined model; however, we are still unable to accurately recreate the issue and identify a fix at this moment.

If you would be available, it would be incredibly helpful to discuss live the duplicates you are seeing. This way we may possibly be able to understand the source of the duplication further. If you are interested in discussing live, feel free to schedule some time when we are both available. Thanks!

[mark-ogrady]

Thanks Joe,

Quick correction to the above:
git: https://github.com/fivetran/dbt_zendesk.git.

I managed to run this version, but the model failed in the dbt run stage, I got this Bigquery specific error:

17:07:11  Completed with 1 error and 0 warnings:
17:07:11  
17:07:11
17:07:11    Database Error in model int_zendesk__reply_time_business_hours (models/sla_policy/reply_time/int_zendesk__reply_time_business_hours.sql)
  Query exceeded resource limits. This query used 36943 CPU seconds but would charge only 40M Analysis bytes. This exceeds the ratio supported by the on-demand pricing model. Please consider moving this workload to the flat-rate reservation pricing model, which does not have this limit. 36943 CPU seconds were used, and this query must use less than 10200 CPU seconds.
  compiled Code at target/run/zendesk/models/sla_policy/reply_time/int_zendesk__reply_time_business_hours.sql

Suggests there might be some issue with one of the joins in the SLA table perhaps?

[fivetran-avinash]

Hey @mark-ogrady ! I think you might be right, but we'll need to do some further investigating to confirm this. We'll get back to you next week!

[fivetran-avinash]

Hi @mark-ogrady, would you be able to test this branch again? We made some updates to the underlying logic within the branch that we believe will mitigate the computational query load you faced last time.

Let us know how it goes!

[mark-ogrady]

Hi Avinash,

Thanks - I am no longer getting a resource error, but have gone back to the failing test:

12:07:42 32 of 32 PASS unique_stg_zendesk__user_user_id ................................. [PASS in 1.52s]
12:07:43 29 of 32 PASS unique_stg_zendesk__ticket_comment_ticket_comment_id ............. [PASS in 2.35s]
12:07:43  
12:07:43
12:07:43  Finished running 32 tests, 1 hook in 0 hours 0 minutes and 5.67 seconds (5.67s).
12:07:43  
12:07:43
12:07:43  Completed with 1 error and 0 warnings:
12:07:43  
12:07:43
12:07:43  Failure in test unique_zendesk__sla_policies_sla_event_id (models/zendesk.yml)
12:07:43
12:07:43    Got 272 results, configured to fail if != 0
12:07:43  
12:07:43
12:07:43    compiled Code at [target/compiled/zendesk/models/zendesk.yml/unique_zendesk__sla_policies_sla_event_id.sql](https://cloud.getdbt.com/api/v2/accounts/9087/runs/251561555/artifacts/compiled/zendesk/models/zendesk.yml/unique_zendesk__sla_policies_sla_event_id.sql)
12:07:43  
12:07:43
12:07:43  Done. PASS=31 WARN=0 ERROR=1 SKIP=0 TOTAL=32

Is there any information I can get for you that will help further identify the error?

[fivetran-avinash]

Hi @mark-ogrady, could you share a few examples of duplicate records that are failing? If you could grab them from the end model and any source data with these records, we can try and locally reproduce the error and figure out where the issue is coming from.

[nbdaniels]

Hi @fivetran-avinash! I'm actually running into this issue as well after making the switch to using_schedules: true and I wasn't having this issue last week and earlier this week. Anything I can do to fix the failing tests for duplicate sla_event_id?

The duplicate events have the exact same values except for sla_breach_at and sla_elapsed_time, if that helps.

[fivetran-avinash]

Hi @mark-ogrady ! What are the metric values by chance for these duplicates? Is it requester_wait_time, agent_work_time, something else entirely, or multiple values? That might help us hone in on what exactly might be happening in the underlying models to cause these test failures.

[nbdaniels]

Hey @fivetran-avinash ! I hope I’m not taking away from @mark-ogrady's ticket but I can add some information from my instance.

The metric values that I’m seeing for duplicate sla_event_id are all first_reply_time. These were SLAs applied between March 6th - March 11th 2023. Please let me know if I can provide any other details to apply a fix!

[fivetran-avinash]

Hi @nbdaniels , thanks for your contributions!

It is interesting that you are only seeing these duplicates in one week of the SLA. I am wondering if it has to do with daylight savings time, as the week the SLAs were applied were the week before DST started in 2023.

Before we dive deeper, we want to make sure there is no issue in your source data. Can you run this query on tickets where you are seeing duplicate SLA values in the source? We just want to confirm this before we investigate within the package.

select 
    ticket_id,
    field_name, 
    value,
    updated
from [your zendesk schema].ticket_field_history
where field_name = 'first_reply_time'
    and ticket_id = <ticket_id>
order by updated

@mark-ogrady, also feel free to run this query too for where you are seeing SLA policy test duplicates!

[nbdaniels]

Hi @fivetran-avinash! Sorry for the delay here, I totally missed this notification.

I've run the above query for all duplicate SLA policies:

with duplicate_sla_tickets as (
    select
        sla_event_id,
        ticket_id,
        count(*)
    from zendesk.zendesk__sla_policies
    group by 1,2
    having count(*) > 1
)
select 
    ticket_id,
    field_name, 
    value,
    updated
from zendesk.ticket_field_history
where field_name = 'first_reply_time'
    and ticket_id in (select distinct ticket_id from duplicate_sla_tickets)
order by ticket_id, updated

There are a handful of these tickets that have multiple rows for applying first_reply_time SLA but they are all distinct rows with no duplicate values in the source data. Many of these tickets just have a single row in the results.

Please let me know if I can run anything else on my end!

[fivetran-catfritz]

Hi @nbdaniels thank you giving that a run! Would you also be able to share the results of this query:

select *
from <schema>.int_zendesk__reply_time_combined
where ticket_id = <ticket_id>

This way we can see the records in the intermediate model that are coming through and resulting in duplicates. From there we can work to make the proper adjustments!

[nbdaniels]

Hi @fivetran-catfritz! Sorry I completely missed this!

Just ran the query and will share some non-sensitive information. High-level: It looks like differing schedules start / end times may be causing this issue.

• Duplicate rows for ticket_id - sla_policy_name - metric - sla_applied_at - agent_reply_at - next_solved_at - first_sla_breach_at - current_time_check -
• The duplicated rows have differing values for sum_lapsed_business_minutes, sla_schedule_start_at, sla_schedule_end_at, sla_breach_at, next_schedule_start, sum_lapsed_business_minutes_new, updated_sla_policy_starts_at (only one row is populated, the other is null), sla_elapsed_time
• is_stale_sla_policy and is_sla_breached may be different depending on the values in the other columns mentioned above

If you need example CSVs or any other details, please let me know and we can figure out the best way to send over some information.

[fivetran-catfritz]

Hi @nbdaniels thank you for giving that a run! It does seem that your findings align with the daylight savings issue, but it would indeed be beneficial if you could provide CSV files containing the duplicate rows. We'd like to check out the differing schedules and sample tickets with the duplicates. You can email the CSVs to us at [email protected], referencing this ticket. Alternatively, you could initiate a data share with us, since we also use BigQuery, so let me know if you prefer to go that route!

[laurits-inpay]

We are facing the same issue, and it seems also related to daylight saving time as all duplicates occur around these dates. @fivetran-catfritz Let me know if you need any more examples around the issue.

[nbdaniels]

@fivetran-catfritz Apologies again for the delay! I just emailed a CSV over to [email protected] but please let me know if I can provide anything else in the meantime!

[fivetran-reneeli]

Hi all, we made changes in this following branch and invite you to test it out. Please let us know if this tackles the issue of duplicate sla event ids!

packages:
  - git: https://github.com/fivetran/zendesk.git
    revision: bugfix/duplicate_sla_event_ids_from_dst
    warn-unpinned: false

[nbdaniels]

@fivetran-reneeli It looks like we're still hitting the error when pulling from the branch you mentioned. Please see below:

22:23:47  Installing https://[email protected]/fivetran/dbt_zendesk.git/
22:23:48  Installed from revision bugfix/duplicate_sla_event_ids_from_dst

FAIL 14 unique_zendesk__sla_policies_sla_event_id ................... [FAIL 14 in 1.26s]

Please let me know if I can provide any other details or data examples!

[solmazb]

Hi all,
We are facing this same issue of the test breaking for 2 duplicates. I did try the proposed temp fix but I think the URL there gives 404, I did try with this setting:

- git: https://github.com/fivetran/dbt_zendesk.git
   revision: bugfix/duplicate_sla_event_ids_from_dst
   warn-unpinned:  false

and the same test still fails, somehow it creates duplicate surrogate keys, We really appreciate if the fix can go out sooner as this is causing all our dbt runs get marked as failed.