BUG - SLA policies model failling uniqueness test

[james-smith-bbm]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I recently upgraded from v0.5.2 to v0.7.1 of the Zendesk package, and have successfully done a full refresh dbt run of all zendesk models since. I now receive a dbt test failure, saying that the sla_event_id column in the sla_policies model is not unique.

I have debugged this as best I can myself. The issue seems to be caused by a change introduced in v0.7.1 (absent from v0.7.0, still present in v0.8.0).

v0.7.0 has this join condition in the int_zendesk__sla_policy_applied model:
and sla_policy_applied.sla_applied_at >= sla_policy_name.valid_starting_at

Since v0.7.1, it has been changed to this:
and sla_policy_applied.sla_applied_at >= {{ fivetran_utils.timestamp_add(datepart='second', interval='-5', from_timestamp='sla_policy_name.valid_starting_at') }}

Relaxing the join condition in this way causes duplication in the join, and ultimately in the final model output, as indicated by the unque test fail.

Relevant error log or model output

[31mCompleted with 1 error and 0 warnings:[0m

[31mFailure in test unique_zendesk__sla_policies_sla_event_id (models/zendesk.yml)[0m
  Got 1406 results, configured to fail if != 0

  compiled SQL at target/compiled/zendesk/models/zendesk.yml/schema_test/unique_zendesk__sla_policies_sla_event_id.sql

Done. PASS=75 WARN=0 ERROR=1 SKIP=0 TOTAL=76

Expected behavior

Successful dbt test, as was happening under v0.5.2.

dbt Project configurations

  zendesk:
    +schema: zendesk
    +tags:
      - daily_04_utc
      - eu
      - prod_only
    intermediate:
      +schema: zendesk_staging
    sla_policy:
      +schema: zendesk_staging
    ticket_history:
      +schema: zendesk_staging

  zendesk_source:
    +schema: zendesk_staging
    +tags:
      - hourly
      - eu
      - prod_only

Package versions

packages:

  - package: dbt-labs/dbt_utils
    version: 0.7.6

  # will also import fivetran/zendesk_source as a dependency
  - package: fivetran/zendesk
    version: 0.7.1

  - package: fivetran/ad_reporting
    version: [">=0.2.2", "<0.6.0"]

  - package: calogica/dbt_expectations
    version: 0.4.7

  - package: dbt-labs/segment
    version: 0.6.0

What database are you using dbt with?

bigquery

dbt Version

installed version: 0.21.1
   latest version: 1.0.0

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 0.21.1

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-joemarkiewicz]

Hi @james-smith-bbm thanks so much for raising this issue and bringing the test failure to our attention. 👀

The code you pointed out is exactly where this duplication is happening. We originally added this join condition to help capture the sla_policy_name when it was not set at the same time as the actual sla_policy. We found there would be occurrences would be set just a few seconds before or after the sla_policy being set. This is why we have the 5 second window to capture these names.

However, as we can see from the failure in the test, this clearly is not working for all instances. I wasn't the biggest fan of this solution originally and believe there is a better way to keep the original logic we had previously while also capturing the sla_policy_name for each sla and keeping the uniqueness.

My first thought is to not leverage the sla_policy_applied.sla_applied_at in the join query and instead use a more accurate timestamp to join on. I believe sla_policy_applied.valid_starting_at would probably be more accurate. I have actually applied this change in a working branch and found the duplication issue to be resolved as well sla_policy_name being populated for each record.

Would you be able to try the below working branch as a dependency and let me know if you see the issue persist?

packages:
    - git: https://github.com/fivetran/dbt_zendesk.git
      revision: bugfix/sla_duplicates
      warn-unpinned: false

[james-smith-bbm]

Hi @fivetran-joemarkiewicz, thanks for taking a look and confirming it's in hand. I tried that branch, and was able to dbt run and dbt test all Zendesk package models without error, all tests passed.

In the meantime, I have solved my immediate issue by downgrading to v0.7.0.

[fivetran-joemarkiewicz]

Hi @james-smith-bbm thanks so much for validating this issue is resolved on your end in the working branch. In the meantime, I will open a PR for this and ensure the fix will be integrated into the next 0.8.1 release of the package.

Feel free to follow this issue until release and thanks again for raising this issue with us!

[fivetran-joemarkiewicz]

Just wanted to provide an update here that the PR mentioned above will be reviewed during my teams next sprint (starting 3/9) and should be expected to be integrated into the next release following PR approval.

[fivetran-joemarkiewicz]

Closing this as the latest release is now live.