[Bug] < int_zendesk__schedule_spine > may be missing rows after DT ends

jflatter commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

I have spent a good deal of time trying to figure out why the table sla_policy only has rows as recent as Oct 30 2022.

After digging, I found the intermediate table called int_zendesk__schedule_spine, which only has rows with values for "valid until" up to Oct 30 2022 as well. All other tables have seemingly valid data such as "sla policty applied" and "time zones".

Anyone else experiencing this issue?

Relevant error log or model output

No response

Expected behavior

I expect the model to give rows for all recently applied SLAs in the transformed table zendesk__sla_policies.

dbt Project configurations

vars:
    #Shopify Vars
    shopify_union_schemas: ['shopify_tbv','shopify_tel','shopify_tna']

    #Zendesk Vars
    #using_schedules:            False         #Disable if you are not using schedules
    #using_domain_names:         False         #Disable if you are not using domain names
    using_user_tags:            False         #Disable if you are not using user tags
    #using_ticket_form_history:  False         #Disable if you are not using ticket form history
    #using_organization_tags:    False         #Disable if you are not using organization tags

Package versions

packages:

package: fivetran/shopify version: [">=0.6.0", "<0.7.0"]
package: fivetran/netsuite version: [">=0.6.0", "<0.7.0"]
package: fivetran/zendesk version: [">=0.9.0", "<0.10.0"]

What database are you using dbt with?

bigquery

dbt Version

version: '1.0.0'

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
[X] No.

jflatter commented 1 year ago

correction:

dbt Version version: '1.3.0'

fivetran-joemarkiewicz commented 1 year ago

Hi @jflatter thanks so much for opening this issue. I actually think I may have come across a similar issue yesterday.

I see you are not disabling the schedule component of the package (correct me if I am wrong). When using schedules the package will use the daylight_time source table to generate the schedule spine in association with the tickets. However, on my end I noticed the daylight_time model was not up to date and was not including current tickets in our end models.

Would you be able to do the same on your end and confirm the latest entries in the daylight_time source table within your warehouse? I suspect it does not include the latest daylight time change and that is the culprit.

jflatter commented 1 year ago

I think I do need to use the daylight_time component as we run SLAs based on business hours.

I am not sure that the daylight_time source table (as synced by Fivetran) should have rows for the period between DT, such as Oct 30 - March 27. Here is a sample of recent rows in the raw data table:

{ "time_zone": "London", "year": "2022", "_fivetran_synced": "2022-10-27 12:57:07.645000 UTC", "daylight_end_utc": "2022-10-30T02:00:00", "daylight_offset": "1", "daylight_start_utc": "2022-03-27T01:00:00" }, { "time_zone": "London", "year": "2021", "_fivetran_synced": "2022-10-27 12:57:07.644000 UTC", "daylight_end_utc": "2021-10-31T02:00:00", "daylight_offset": "1", "daylight_start_utc": "2021-03-28T01:00:00" }, { "time_zone": "London", "year": "2020", "_fivetran_synced": "2022-10-27 12:57:07.643000 UTC", "daylight_end_utc": "2020-10-25T02:00:00", "daylight_offset": "1", "daylight_start_utc": "2020-03-29T01:00:00" }

If you meant the dbt produced stg_zendesk__daylight_time table the results are similar:

{ "daylight_end_utc": "2022-10-30T02:00:00", "daylight_offset": "1", "daylight_start_utc": "2022-03-27T01:00:00", "time_zone": "London", "year": "2022", "daylight_offset_minutes": "60" }, { "daylight_end_utc": "2021-10-31T02:00:00", "daylight_offset": "1", "daylight_start_utc": "2021-03-28T01:00:00", "time_zone": "London", "year": "2021", "daylight_offset_minutes": "60" }, { "daylight_end_utc": "2020-10-25T02:00:00", "daylight_offset": "1", "daylight_start_utc": "2020-03-29T01:00:00", "time_zone": "London", "year": "2020", "daylight_offset_minutes": "60" }

Either way it does not look like there are ever rows which cover the period between DT periods.

It looks like the int_zendesk__schedule_spine model was introduced in the package's February 2022 release (.80? maybe). Totally possible that this change created the issue as this is the first "end of DT period" since release.

Jacob

fivetran-joemarkiewicz commented 1 year ago

Hey @jflatter thanks for sharing this detail. The int_zendesk__schedule_spine should create records for these non DT periods even though the source table does not provide them. I am curious what the most recent records of the materialized int_zendesk__schedule_spine model look like. Would you be able to materialize this model as a table and see if this is creating records for the in-between periods. I suspect it may be stopping for some reason following this period, but I am unsure why that may be exactly.

The introduction of the int_zendesk__schedule_spine model was required in order for us to properly account for daylight savings across schedules. With that you are correct that this first end of DT period may have a factor of the code that is not fully functioning as expected.

Unfortunately, it is hard to troubleshoot on my end as our own internal data doesn't leverage schedules or take into account the daylight savings offsets. Would you be chance be open to meeting with me live to debug this issue and investigate what may be happening within the package or the source?

fivetran-joemarkiewicz commented 1 year ago

@jflatter I noticed you scheduled some time to discuss during our office hours, but you were not able to attend. The office hours are always open if you would like to schedule another call with our team.

Additionally, I was able to loop up with our engineering team and discovered that they released an update to the connector to adjust the daylight_time source table to account for the recent daylight time change. Would you be able to run the package again and see if you are seeing the issue still? I am curious if the fix on the connector end rectified the initial bug you identified in this issue.

fivetran-joemarkiewicz commented 1 year ago

Marking as blocked for the time being. @jflatter feel free to add more context if you are still experiencing this issue.

fivetran-joemarkiewicz commented 1 year ago

Closing this issue as I believe the update on the connector end addressed the core issue detailed here.

Please feel free to reopen if you are still experiencing this issue or you wish to discuss this further.

fivetran / dbt_zendesk