Closed SethPreissV closed 7 months ago
Hi @SethPreissV fair question!
We perform aggregations on sla_breach_at because there are multiple records for each ticket, sla, and metric.
Re: max sla_breach_at
Re: min sla_breach_at
However, if you're not seeing the correct sla_breach_at
time, I'd try updating to our latest release, v0.12.0. Let us know if that still doesn't resolve the issue.
So looking at the int_zendesk__requester_wait_time_business_hours table and an example I have to work with (data below), the SLA_BREACH_AT time that is correct would be "10/18/2023 2:51:00 PM" when IS_BREACHED_DURING_SCHEDULE is TRUE but SLA_BREACH_AT that is shown in ZENDESK__SLA_POLICIES is "10/19/2023 8:51:00 PM" because the max function is used while creating the table. Working the math backwards, "10/18/2023 2:51:00 PM" would be correct based on the original VALID_START_AT and based on the datetime shown in Zendesk itself. If I understand your message correctly, is the SLA being restarted in the dbt code whenever there is another event in the ticket?
TICKET_ID SLA_APPLIED_AT TARGET VALID_STARTING_AT VALID_ENDING_AT WEEK_NUMBER TICKET_WEEK_START_TIME_MINUTE TICKET_WEEK_END_TIME_MINUTE SCHEDULE_START_TIME SCHEDULE_END_TIME SCHEDULED_MINUTES RUNNING_TOTAL_SCHEDULED_MINUTES REMAINING_TARGET_MINUTES BREACH_MINUTES BREACH_MINUTES_FROM_WEEK LAG_CHECK IS_BREACHED_DURING_SCHEDULE SLA_BREACH_AT 112186 2023-10-17 14:51:01.000 +0000 540 2023-10-17 14:51:01.000 +0000 2023-10-18 17:49:50.002 +0000 0 3,771.02 5,389.83 3,750 4,290 518.983333 518.983333 21.016667 540 4,311.02 FALSE 10/17/23 11:51 PM 112186 2023-10-17 14:51:01.000 +0000 540 2023-10-17 14:51:01.000 +0000 2023-10-18 17:49:50.002 +0000 0 3,771.02 5,389.83 5,190 5,730 199.833334 718.816667 -178.816667 21.016667 5,211.02 21.016667 TRUE 10/18/23 2:51 PM 112186 2023-10-17 14:51:01.000 +0000 540 2023-10-18 17:49:50.002 +0000 2023-10-20 15:54:04.000 +0000 0 5,389.83 8,154.07 5,190 5,730 340.166667 1,058.98 -518.983334 -178.816667 5,211.02 -178.816667 FALSE 10/18/23 2:51 PM 112186 2023-10-17 14:51:01.000 +0000 540 2023-10-18 17:49:50.002 +0000 2023-10-20 15:54:04.000 +0000 0 5,389.83 8,154.07 6,630 7,170 540 1,598.98 -1,058.98 -518.983334 6,111.02 -518.983334 FALSE 10/19/23 5:51 AM 112186 2023-10-17 14:51:01.000 +0000 540 2023-10-18 17:49:50.002 +0000 2023-10-20 15:54:04.000 +0000 0 5,389.83 8,154.07 8,070 8,610 84.066666 1,683.05 -1,143.05 -1,058.98 7,011.02 -1,058.98 FALSE 10/19/23 8:51 PM
Hi @SethPreissV , yes so sla_breach_at
is updated following each event (in this case when the ticket status is changed to where ticket_historical_status.status in ('new', 'open', 'on-hold', 'hold')). Therefore, based on the sla target time we are recalculating the sla_breach_at
value because we need to properly account for the time that has passed since the last event (in this case status change). If we didn't do this then the sla_breach_at would not be accurate for the sla policy.
Ultimately, we grab the max sla_breach_at
because the last record should be the sla_breach_at
that we calculated that is relevant.
We have realized this worked when we were testing because we didn't have a sla that was breached in the test data, so we didn't test for such case. It may be that the logic needs to be updated to reflect that. Thanks for providing the sample-- if you could provide a csv version of a few example cases it would be very appreciated since it would make it easier to analyze and test!
Hey @SethPreissV! I saw you may have commented but it got deleted?
Also, if we could grab the data in csv form, that you shared, that'd be greatly appreciated!
This has issue should now be addressed in the latest v0.14.0
release of the dbt_zendesk package. As such, closing this out.
Please feel free to reopen or create a new issue if the original issue persists following the upgrade.
Is there an existing issue for this?
Describe the issue
Hey team,
I'm trying to understand sla_breach_at and why the max sla_breach_at for requester_wait_time and min for reply_time is used instead of the sla_breach_at when the is_sla_breach = true. It seems that the existing logic pulls the max/min calculated sla_breach_at instead of the actual time the sla_breach_at was breached for the metric. I feel like I might be missing something.
Relevant error log or model output
Expected behavior
Correct sla_breach_at to be shown in zendesk__sla_policies
dbt Project configurations
name: 'zendesk' version: '0.11.2' profile: 'zendesk'
config-version: 2 require-dbt-version: [">=1.3.0", "<2.0.0"] on-run-start: '{{ fivetran_utils.empty_variable_warning("ticket_field_history_columns", "zendesk_ticket_field_history") }}' models: zendesk: +schema: zendesk +materialized: table agent_work_time: +materialized: ephemeral intermediate: +schema: zendesk_intermediate +materialized: table reply_times: +materialized: ephemeral resolution_times: +materialized: ephemeral sla_policy: +schema: zendesk_intermediate +materialized: table ticket_history: +schema: zendesk_intermediate +materialized: ephemeral utils: +materialized: ephemeral vars: zendesk: ticket_field_history_columns: ['assignee_id', 'status', 'priority'] ticket_field_history_updater_columns: [] group: "{{ ref('stg_zendeskgroup') }}" brand: "{{ ref('stg_zendeskbrand') }}" domain_name: "{{ ref('stg_zendeskdomain_name') }}" organization_tag: "{{ ref('stg_zendeskorganization_tag') }}" organization: "{{ ref('stg_zendeskorganization') }}" schedule: "{{ ref('stg_zendesk__schedule') }}" ticket: "{{ ref('stg_zendeskticket') }}" ticket_form_history: "{{ ref('stg_zendeskticket_form_history') }}" ticket_comment: "{{ ref('stg_zendeskticket_comment') }}" field_history: "{{ ref('stg_zendeskticket_field_history') }}" ticket_schedule: "{{ ref('stg_zendeskticket_schedule') }}" ticket_tag: "{{ ref('stg_zendeskticket_tag') }}" user_tag: "{{ ref('stg_zendeskuser_tag') }}" user: "{{ ref('stg_zendeskuser') }}" daylight_time: "{{ ref('stg_zendesk__daylight_time') }}" time_zone: "{{ ref('stg_zendesktime_zone') }}" using_schedules: true using_domain_names: true using_user_tags: true using_ticket_form_history: true using_organization_tags: true
Package versions
packages:
package: fivetran/zendesk_source version: [">=0.8.0", "<0.9.0"]
package: calogica/dbt_date version: [">=0.7.0", "<0.8.0"]
What database are you using dbt with?
snowflake
dbt Version
Core:
Additional Context
No response
Are you willing to open a PR to help address this issue?