This PR will result in the following new package version:
v0.12.0
Please detail what change(s) this PR introduces and any additional information that should be known during the review of this PR:
int_schedule_spine was updated to take daylight savings time into account when merging schedule periods. Previously, schedule periods with different start_time_utcs (because of DST) were getting merged together.
now, the package ensures that they remain distinct periods and will only merge gaps if have the same offset.
we also removed the double_gap logic as it was rendered unnecessary by our changes. it was necessary before because we used to look backward in time to merge gap periods, whereas now we look forward in time and will keep merging gaps until we get to the present day
int_schedule_spine was also updated to create more appropriate valid_until's for holiday periods. 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). this worked before 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 need to set the end of the holiday to truly be the end of the day. Thus, i've updated the holiday_end_at to be calculated by adding 24*60*60 seconds to the holiday_start
in all the int business_hours models, i've updated the join logic in the intercepted_periods CTE. this is where we associate ticket weekly periods with the appropriate business schedule, in order to generate business minutes. previously, we did so by comparing the status_valid_starting_at and status_valid_ending_at fields to the schedule valid_from and valid_until. this was causing fanout in the join, as we needed to take week_number into account, given that the grain of the previous CTE was ticket+status+week. I don't know if i can explain that better, but it was an issue!
now, we use the ticket_week_start_time and ticket_week_end_time fields instead of the timestamp status_valid_starting/ending_at fields in our join comparison. We take the start of first weekly period and add week_number * 7 * 24 * 60 (min in a week) + ticket_week_start/end_time to most accurately compare against the schedule valid_from/until
updated int_zendesk__requester_wait_time_filtered_statuses to include the hold status, since zendesk updated on-hold to just hold
PR Checklist
Basic Validation
Please acknowledge that you have successfully performed the following commands locally:
[x] dbt compile
[x] dbt run –full-refresh
[x] dbt run
[x] dbt test
[x] dbt run –vars (if applicable)
Before marking this PR as "ready for review" the following have been applied:
[x] The appropriate issue has been linked and tagged
[x] You are assigned to the corresponding issue and this PR
[ ] BuildKite integration tests are passing -- passing everywhere except postgres + redshift, which are having buildkite issues rn
Detailed Validation
Please acknowledge that the following validation checks have been performed prior to marking this PR as "ready for review":
[x] You have validated these changes and assure this PR will address the respective Issue/Feature.
[ ] You are reasonably confident these changes will not impact any other components of this package or any dependent packages.
[ ] You have provided details below around the validation steps performed to gain confidence in these changes.
Lots of validation via slack with the @cth84 for business minutes
I've also compared our internal zendesk data before-and-after (calendar minutes only) and everything except the total_reply_time_calendar_minutes is tying out (there are 26 tickets that are off). shared the exact query i used for this in Slack.
I have not compared the sla_policy metrics however, as I imagine @fivetran-reneeli's changes will be affect things there.
Standard Updates
Please acknowledge that your PR contains the following standard updates:
Package versioning has been appropriately indexed in the following locations:
[x] indexed within dbt_project.yml
[x] indexed within integration_tests/dbt_project.yml
[ ] CHANGELOG has individual entries for each respective change in this PR - Not yet, would like to discuss how much detail/how to explain this whole thing!
[x] README updates have been applied (if applicable)
[ ] DECISIONLOG updates have been updated (if applicable)
[ ] Appropriate yml documentation has been added (if applicable)
dbt Docs
Please acknowledge that after the above were all completed the below were applied to your branch:
[ ] docs were regenerated (unless this PR does not include any code or yml updates)
If you had to summarize this PR in an emoji, which would it be?
PR Overview
This PR will address the following Issue/Feature:
This PR will result in the following new package version:
v0.12.0
Please detail what change(s) this PR introduces and any additional information that should be known during the review of this PR:
int_schedule_spine
was updated to take daylight savings time into account when merging schedule periods. Previously, schedule periods with different start_time_utcs (because of DST) were getting merged together.double_gap logic
as it was rendered unnecessary by our changes. it was necessary before because we used to look backward in time to merge gap periods, whereas now we look forward in time and will keep merging gaps until we get to the present dayint_schedule_spine
was also updated to create more appropriate valid_until's for holiday periods. previously, we calculated the end of holiday day by adding24*60*60-1
seconds (making the end the last second of the same day). this worked before 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 need to set the end of the holiday to truly be the end of the day. Thus, i've updated the holiday_end_at to be calculated by adding24*60*60
seconds to theholiday_start
business_hours
models, i've updated the join logic in theintercepted_periods
CTE. this is where we associate ticket weekly periods with the appropriate business schedule, in order to generate business minutes. previously, we did so by comparing thestatus_valid_starting_at
andstatus_valid_ending_at
fields to the schedulevalid_from
andvalid_until
. this was causing fanout in the join, as we needed to takeweek_number
into account, given that the grain of the previous CTE was ticket+status+week. I don't know if i can explain that better, but it was an issue!ticket_week_start_time
andticket_week_end_time
fields instead of the timestampstatus_valid_starting/ending_at
fields in our join comparison. We take the start of first weekly period and addweek_number * 7 * 24 * 60 (min in a week) + ticket_week_start/end_time
to most accurately compare against the schedulevalid_from/until
int_zendesk__requester_wait_time_filtered_statuses
to include thehold
status, since zendesk updatedon-hold
to justhold
PR Checklist
Basic Validation
Please acknowledge that you have successfully performed the following commands locally:
Before marking this PR as "ready for review" the following have been applied:
Detailed Validation
Please acknowledge that the following validation checks have been performed prior to marking this PR as "ready for review":
Lots of validation via slack with the @cth84 for business minutes
I've also compared our internal zendesk data before-and-after (calendar minutes only) and everything except the
total_reply_time_calendar_minutes
is tying out (there are 26 tickets that are off). shared the exact query i used for this in Slack.I have not compared the sla_policy metrics however, as I imagine @fivetran-reneeli's changes will be affect things there.
Standard Updates
Please acknowledge that your PR contains the following standard updates:
dbt Docs
Please acknowledge that after the above were all completed the below were applied to your branch:
If you had to summarize this PR in an emoji, which would it be?
☠️