debug duplicate sla event ids

[fivetran-reneeli]

PR Overview

This PR will address the following Issue/Feature: https://github.com/fivetran/dbt_zendesk/issues/144

Note that this PR currently addresses the below scenarios that resulted in duplicate sla_event_ids, though please note the issue is not completely closed out yet:

• 2 versions of the same schedule due to daylight savings time
• overlapping schedule windows resulting from holidays that ran across more than 1 schedule week

The remaining root cause of duplicates we are still working to address is due to more than 1 condition in the filtered_reply_times CTE in the int_zendesk__reply_time_combined model being met (condition 2 in addition to condition 5). We have a working solution but still need to go through testing.

This PR will result in the following new package version: v0.14.1

Non breaking

Please provide the finalized CHANGELOG entry which details the relevant changes included in this PR:

Bug Fixes

• Fixes the issue of potential duplicate sla_event_id's occurring in the zendesk__sla_policies model.
  • This involved updating the int_zendesk__schedule_spine which was previously outputting overlapping schedule windows, to account for when holidays transcended a given schedule week.
  • This also involved updating the int_zendesk__reply_time_business_hours model, in which two different versions of a schedule could exist due to daylight savings time.
• Improved performance by adjusting the int_zendesk__reply_time_business_hours model to only perform the weeks cartesian join on tickets that require the further look into the future.
  • Previously the int_zendesk__reply_time_business_hours would perform a cartesian join on all tickets to calculate weeks into the future. This was required to accurately calculate sla_elapsed_time for tickets with first replies far into the future. However, this was only necessary for a handful of tickets. Therefore, this has been adjusted to accurately only calculate the future weeks as far as either the first reply time or first solved time.

Documentation Updates

• Addition of the reference to the Fivetran prebuilt Zendesk Streamlit report in the README.
• Updates DECISIONLOG to include a note that the generated time series for ticket SLA policies is limited to a year into the future to maintain performance.

Bug Fixes

• Fixes the issue of sla_event_id's occurring in the zendesk__sla_policies model.
  • This involved updating the int_zendesk__schedule_spine which was previously outputting overlapping schedule windows, to account for when holidays transcended a given schedule week.
  • This also involved updating the int_zendesk__reply_time_business_hours model, where within the model two different versions of a schedule would exist due to daylight savings time.

Documentation Updates

• Addition of the reference to the Fivetran prebuilt Zendesk Streamlit report in the README.

PR Checklist

Basic Validation

Please acknowledge that you have successfully performed the following commands locally:

• [x] dbt run –full-refresh && dbt test
• [ ] dbt run (if incremental models are present) && dbt test

Before marking this PR as "ready for review" the following have been applied:

• [x] The appropriate issue has been linked, tagged, and properly assigned
• [x] All necessary documentation and version upgrades have been applied
• [ ] docs were regenerated (unless this PR does not include any code or yml updates)
• [x] BuildKite integration tests are passing
• [ ] Detailed validation steps have been provided below

Detailed Validation

Please share any and all of your validation steps:

• this will be internally shared in our internal ticket as we tested on customer data
• but customers have noted that the duplicates and the compute time have drastically decreased

If you had to summarize this PR in an emoji, which would it be?

:dancer:

[fivetran-reneeli]

regen docs when approved

[fivetran-reneeli]

Hi @fivetran-joemarkiewicz , this is ready for re-review. Please give the updated PR template a read as well; I made sure to call out the 2 root causes of the duplicates that are addressed here. If that looks good I will make a similar comment in the issue thread so customers are aware.