[Question] sla_breach_at confusion

[SethPreissV]

Is there an existing issue for this?

• I have searched the existing issues

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

None

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_zendesk__group') }}"
brand: "{{ ref('stg_zendesk__brand') }}"
domain_name: "{{ ref('stg_zendesk__domain_name') }}"
organization_tag: "{{ ref('stg_zendesk__organization_tag') }}"
organization: "{{ ref('stg_zendesk__organization') }}"
schedule: "{{ ref('stg_zendesk__schedule') }}"
ticket: "{{ ref('stg_zendesk__ticket') }}"
ticket_form_history: "{{ ref('stg_zendesk__ticket_form_history') }}"
ticket_comment: "{{ ref('stg_zendesk__ticket_comment') }}"
field_history: "{{ ref('stg_zendesk__ticket_field_history') }}"
ticket_schedule: "{{ ref('stg_zendesk__ticket_schedule') }}"
ticket_tag: "{{ ref('stg_zendesk__ticket_tag') }}"
user_tag: "{{ ref('stg_zendesk__user_tag') }}"
user: "{{ ref('stg_zendesk__user') }}"
daylight_time: "{{ ref('stg_zendesk__daylight_time') }}"
time_zone: "{{ ref('stg_zendesk__time_zone') }}"
using_schedules: true
using_domain_names: true
using_user_tags: true
using_ticket_form_history: true
using_organization_tags: true

ticket_field_history_extension_months: 0 # how long to extend a ticket's field history past its closure date
ticket_field_history_timeframe_years: 50 # how far back to pull tickets' field histories. default is everything

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:

• installed: 1.5.8

Additional Context

No response

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[fivetran-reneeli]

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

• We use max in the end sla_policies model because in the upstream intermediate models, we calculate the sla_breach_at time for each day of the business week that the ticket is active. Therefore we can have multiple records per sla, where for each we are calculating the lapsed minutes it was active. Then we filter to only the records it's been last active. This gives us the most recent record which is the one we want because it's the latest one where the sla was tracked. So the max captures the latest sla record.

Re: min sla_breach_at

• min(sla_breach_at) is used here because we want to calculate the first time for each event per each ticket and metric.

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.

[SethPreissV]

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

[fivetran-reneeli]

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!

[fivetran-reneeli]

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!

[fivetran-joemarkiewicz]

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.