v0.12.0 dbt_zendesk

This release includes fixes to issues introduced in v0.11.0-v0.11.1 surrounding the incorporation of schedule holidays.

Special thanks to @cth84 and @nschimmoller for working with us to figure out some seriously tricky bugs!

Bug Fixes

• Adjusted the gap-merging logic in int_zendesk__schedule_spine to look forward in time instead of backward. This allows the model to take Daylight Savings Time into account when merging gaps. Previously, schedule periods with different start_time_utcs (because of DST) were getting merged together (PR #114).
  • Also removed the double_gap logic as it was rendered unnecessary by the above change.
• In all of our intermediate business hour models, adjusted the join logic in the intercepted_periods CTE, where we associate ticket weekly periods with the appropriate business schedule period. Previously, we did so by comparing the ticket's status_valid_starting_at and status_valid_ending_at fields to the schedule's valid_from and valid_until dates. This was causing fanout in certain cases, as we need to take the ticket-status's week_number into account because it is part of the grain of the CTE we are joining (PR #114).
• Adjusted the way we calculate the end of holidays in int_zendesk__schedule_spine. Previously, we calculated the end of holiday day by adding 24*60*60-1 seconds (making the end the last second of the same day) to the start of the holiday. This previously worked because our downstream joins for calculating business metrics were inclusive (ie >= instead of >). We've updated these joins to be exclusive (ie > or <), so we've set the end of the holiday to truly be the end of the day instead of a second prior (PR #114).
• Updated int_zendesk__requester_wait_time_filtered_statuses to include the hold status, as zendesk updated on-hold to just hold (PR #114).
• Updates the logic in int_zendesk__reply_time_combined to bring through the correct sla_event_id records to the end zendesk__sla_policies model. (PR #108)
  • Originally, duplicate sla_event_id records were being persisted because the upstream filtered_reply_times CTE did not include for all scenarios. With this update, the CTE will filter for the following scenarios:
    • Ticket is replied to between a schedule window
    • Ticket is replied to before a schedule window and no business minutes have been spent on it
    • Ticket is not replied to and therefore active. But only bring through the active SLA record that is most recent (after the last SLA schedule starts but before the next)
• Updated the ordering within the int_zendesk__comments_enriched model logic to also take into account when two comments are posted at the exact same time. Previously, the next comment would be picked arbitrarily. However, we now use the commenter_role as the tie breaker giving preference to the end-user as they will likely be the first commenter when two comments are posted at the exact same time. (PR #114)
• Modified the requester and agent wait time sla_elapsed_time metric calculations within the zendesk__sla_policies to capture the max running_total_scheduled_minutes record as opposed to the cumulative sum. Max more accurately represents the upstream data as it is presented in a rolling sum in the previous intermediate models. (PR #114)

Dependency Updates

• The dbt-date dependency has been updated to reflect the recommended latest range, [">=0.9.0", "<1.0.0"]. This will help to avoid upstream dependency conflicts. (PR #113)

Contributors:

• @nschimmoller
• @cth84

Full Changelog: v0.11.2...v0.12.0