[Bug] Agent Work Time and Requester Wait Time Erroneously Being Filtered Out of zendesk__sla_policies

[fivetran-joemarkiewicz]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

When working through PR #154, a test was written to validate the integrity of the zendesk__sla_policies model. This integrity test is ensuring the row count from the source is matching the expected granular row count of the end model. In short, we want to ensure that all SLAs identified in the source are being reported in the end model. However, in writing this test it became known that one ticket was causing the test to fail.

When investigating the ticket the test is failing on, I can confirm that the 2 row count delta is a result of the agent_work_time and requester_wait_time SLAs are not being brought through to the end model.

I was then able to identify that for both agent_work_time and requester_wait_time SLAs in this instance, the fields are being filtered out within the respective CTEs

• Agent work time intercepted_periods cte
• Requester wait time intercepted_periods cte

I imagine that some of the many updates we have applied to the first_reply_time and next_reply_time SLA policy metrics also need to be applied to the agent_work_time and requester_wait_time SLAs policy metrics.

Relevant error log or model output

14:57:35  Finished running 5 tests, 1 hook in 0 hours 0 minutes and 4.12 seconds (4.12s).
14:57:35  
14:57:35  Completed with 1 error and 0 warnings:
14:57:35  
14:57:35  Failure in test sla_count_match (tests/integrity/sla_count_match.sql)
14:57:35    Got 1 result, configured to fail if != 0
14:57:35  
14:57:35    compiled Code at target/compiled/zendesk_integration_tests/tests/integrity/sla_count_match.sql

Expected behavior

All supported ('next_reply_time', 'first_reply_time', 'agent_work_time', 'requester_wait_time') SLAs properly identify in the source are accounted for the in end zendesk__sla_policies end model.

The test sla_count_match is passing within our validations.

dbt Project configurations

vars:
  zendesk_schema: zendesk_test_env
  using_domain_names: false
  
  using_user_tags: false
  using_organization_tags: false
  fivetran_integrity_sla_first_reply_time_exclusion_tickets: (1,56,80)
  fivetran_consistency_ticket_metrics_exclusion_tickets: (11092,11093,11094)
  fivetran_integrity_sla_count_match_tickets: (76)

Package versions

Latest

What database are you using dbt with?

postgres, redshift, snowflake, bigquery, databricks

dbt Version

Latest

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.