-
Notifications
You must be signed in to change notification settings - Fork 30
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[Question] Duplicate SLA Event IDs #108
Comments
hey there @nschimmoller 👋 thanks for taking the time to make this issue! it sounds like this could be related to #107, in which the |
@fivetran-jamie thanks for getting back to me I'll try and add some more detail for troubleshooting.
Unfortunately I do not believe this to be the case as my issue does not have do with a period spanning the changing of a schedule's
Yes I am seeing multiple records for However, I believed this to be intentional as there is no overt filtering function used in the However, the output from the Importantly though neither of these three rows report the correct This ticket was created on Wednesday 2023-08-02 21:01:35.000 +0000. Our schedule during the week is 04:00:00.000 +0000 - 04:00:00.000 +0000, or in other words open 24/5 with our business days starting at 00:00:00.000 -0400 or (US ET or Americas/New_York). Given that this Ticket came in during "open" business hours the Digging into the code I believe this to be do to the filtering used in the aforementioned
This can be summarized as:
However, this does not account for the scenario in which the agent responds on a day after the correct
At this point we finally have the right row generating as a result of the You can also see in the most recent file that the
The issue being that all of the rows have the same values for
|
I just set up the fivetran/dbt_zendesk package and am also running into this issue |
Thanks for chiming in @trunsky and sharing that you are also experiencing this issue. My team and I are currently scoping this issue out and hope to share some insights in the next day or so. Be sure to follow here for updates! |
Hey all, Wanted to reach back out with an update on a handful of changes I made in my environment that was able to resolve the issues described above: int_zendesk__reply_time_business_hours.sql
Added a column for
Passing through int_zendesk__reply_time_combined.sql
Brining in
Continuing to pass through
Updated order by logic for
Problem:
Prior filtering only retained rows where agent_reply_at and sla_schedule_start at occured on the same day, or agent_reply_at occurred prior to the sla_schedule_start_at and the sum_lapsed_business_minutes_new was 0. In otherwords when a ticket was replied to before opening hours. However, this misses the common scneario of when a ticket was replied to after the day of the sla_schedule_start, but prior to the sla_schedule_end at.
Changes:
Replaced logic to retain rows where agent_reply_at and sla_schedule_start at occurred on the same day with logic to capture rows where the agent_reply_at occurs between the start and the end of the schedule.
Problem:
Changes:
Thoughts:Truthfully, I'm not entirely sure what purpose or function updated_sla_policy_starts_at or is_stale_sla_policy serves. I'm assuming that since the logic used here I was able to identify as being problematic elsewhere the same changes were needed.
Use row_num to filter to only one row per ticket metric combination |
@nschimmoller thank you so much for the detailed write up and sharing the code you were able to apply to address the duplicates. I am curious if this fixed all the duplicates and did not introduce any other issues? I attempted to make the same updates (you can see them in the bugfix/duplciate-sla-policies branch I created); however, I found this reduced the record count of calendar metric tickets in my Nevertheless, I believe you are onto something with calling out that the additional logic within the In the meantime, I am curious if you experience these same issues in the previous minor version of the package v0.10.2? This version does not account for schedule holidays, but I am curious if the duplicates you see are resolved? You can leverage this version by downgrading the package in your packages.yml. If you do not see any errors with this version then that will really help us identify the problem code and ensure we are able to find a viable solution. @nschimmoller I know you said you were unable to leverage the package out of the box, but you can see the code differences between the versions here. packages:
- package: fivetran/zendesk
version: [">=0.10.0", "<0.11.0"] Further, would either @trunsky or @nschimmoller be interested in meeting with a member of my team to troubleshoot in more detail so we may understand the issue further and collaborate on a viable solution? Unfortunately, I will be out on PTO for the next few weeks, but @fivetran-jamie from my team will be able to continue scoping this out further. Thanks! |
Confirming that downgrading the package resolved the test error for me. I'm probably the wrong person to troubleshoot this data, as pulling in zendesk slas was a request from someone on our team, and I'd be lying if I fully understand the Zendesk data at this point 😅. |
@fivetran-joemarkiewicz can we setup some time to walk through this early next week? |
@nschimmoller yes definitely, what days/times work for y'all? |
@fivetran-jamie I'm free any time after noon ET next Monday (9/11). Does that work for you at all? |
@nschimmoller works perfect for me, I'm free all day Monday! Thanks |
If you'd like to send out the invite, my email is jamie.rodriguez@fivetran.com |
Hi @nschimmoller you can include me as well! renee.li@fivetran.com |
Hi @nschimmoller again, we appreciate you opening this up and working with us through the fix! This has been included in our latest release of the package and as such we'll be closing this out. |
Hi Renee,
Thanks so much for following up on this! I'll definitely be comparing the
solution you implemented to the one I built as a workaround to see if there
is anything I could be optimizing for better.
Apologies for not being able to contribute the other week with data
samples. Had a bit of a medical emergency in the family, everything is
doing better now but was in and out the past few weeks.
Thanks for being such great collaborators.
…On Thu, Oct 12, 2023 at 5:00 PM Renee Li ***@***.***> wrote:
Hi @nschimmoller <https://github.com/nschimmoller> again, we appreciate
you opening this up and working with us through the fix! This has been
included in our latest release
<https://github.com/fivetran/dbt_zendesk/releases/tag/v0.12.0> of the
package and as such we'll be closing this out.
—
Reply to this email directly, view it on GitHub
<#108 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AV3Q77YWPHZYJLPH3PSJWJDX7BK6FANCNFSM6AAAAAA3MAU65E>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
--
Nate Schimmoller
he / him / his
Manager, Customer Success Operations
klaviyo.com <https://www.klaviyo.com>
--
*Any information contained in or attached to this e-mail is intended solely
for the use of the intended recipient(s) and may contain certain
information that is confidential, proprietary and/or legally privileged. If
you are not the intended recipient, you may not review, copy or distribute
this message. If you receive this in error, please notify the sender and
destroy all copies of this message and attachments.*
|
Is there an existing issue for this?
Describe the issue
Hey all,
First all thanks so much for a great package. I unfortunately couldn't use it in my environment out of the box but was able to get far enough in converting most of the code to Snowflake SQL to suffice! However, I am running into one unexpected issue.
In zendesk_sla_policies I am getting duplicate sla_events_ids. Here is an example:
Because the filtered_reply_times CTE evaluates if the agent_reply_at date is the same as both the sla_schedule_start_at dates both rows are retained and are passed through to the zendesk_sla_policies table.
While I know I'm not using the out of the box version of the code and thus this may be user error. I was wondering if somebody may be able to point how the code is supposed to solve for this. type of scenario.
The only thing I can think of is perhaps is_breached_during_schedule was supposed to be used to indicate which row to retain. Feels odd that work goes into defining that but is never used.
Relevant error log or model output
No response
Expected behavior
Would expect it to only return one row
dbt Project configurations
I don't have this :(
Package versions
I don't have this :(
What database are you using dbt with?
snowflake
dbt Version
I don't have this :(
Additional Context
No response
Are you willing to open a PR to help address this issue?
The text was updated successfully, but these errors were encountered: