Bugfix/remianing sla issues

PR Overview

This PR will address the following Issue/Feature: Issue #144

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

Similar to the previous v0.15.0 release, this is not a breaking change. However, this will impact the results of the end models and we would rather users are aware of these changes when upgrading as opposed to unexpectedly seeing the results change.

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

🚨 Minor Upgrade 🚨

Although this update is not a breaking change, it will likely impact the output of the zendesk__sla_policies and zendesk__sla_metrics models. PR #154 includes the following changes:

Bug Fixes

Addresses the potential issue where the first_reply_time_business_minutes metric within the zendesk__ticket_metrics model would incorrectly calculate the elapsed time when daylight savings occurred. This change involved adjusting a join to reference the difference of two dates as opposed to timestamps. This more accurately applies a cutoff event during daylight savings.

Introduction of an additional condition within the filtered_reply_times cte of the int_zendesk__reply_time_combined model to ensure tickets replied to before any schedule begins and no business minutes have been spent is reserved for only the first day the ticket is open. Previously, this condition could be met on days other than the first.

Under the Hood

Addition of integrity and consistency validation tests within integration tests for the zendesk__sla_policies and zendesk__ticket_metrics models.

PR Checklist

Basic Validation

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

[X] dbt run –full-refresh && dbt test
[X] 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) Will be generated after approval
[x] BuildKite integration tests are passing
[x] Detailed validation steps have been provided below

Detailed Validation

Please share any and all of your validation steps:

Please see below for confirmation that the validations are netting the expected results:

The following variables used to test locally are as follows:

vars:
  zendesk_schema: zendesk_test_env
  using_domain_names: false
  using_user_tags: false
  using_organization_tags: false
  fivetran_integrity_sla_first_reply_time_exclusion_tickets: (1,56,80)
  fivetran_consistency_ticket_metrics_exclusion_tickets: (11092,11093,11094)
  fivetran_integrity_sla_count_match_tickets: (76)

Reason for the exclusion tickets are as follows:

fivetran_integrity_sla_first_reply_time_exclusion_tickets: (1,56,80)
- A result of the following tickets not following the normal process of Zendesk Business SLAs. These tickets were modified beyond the point where they can accurately be tracked. As such, the results are inconclusive when comparing the SLA Policies results to the Ticket Metrics results. I don't see the behavior of these tickets being the norm for customer environments. As such, we should not worry about these records not passing the validation.
fivetran_consistency_ticket_metrics_exclusion_tickets: (11092,11093,11094)
- These tickets are all impacted as a result of the following timestamp to date join condition change. Upon inspecting these tickets in the Zendesk UI, we can validate that the dev results more closely match the results within the Zendesk UI. Further, the discrepancy we see can be attributed to a modification of the schedule by 135 minutes (which is the delta for each discrepancy). As the data model does not support modified schedules at this moment, we are comfortable with this delta in our test data. See the results of these tickets in the validation, and the results in the Zendesk UI. You can see the delta is exactly 135 minutes for each dev ticket result.
fivetran_integrity_sla_count_match_tickets: (76)
- When testing that the count of tickets matches between the source and end model, it became apparent that the agent_work_time and requester_wait_time metric for one ticket (76) were not being brought through to the final end model. This issue is out of scope for this initial Issue. Therefore, this ticket will be filtered out for this test and a new corresponding Issue #155 has been created to address this bug directly.

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

🧛

fivetran / dbt_zendesk