Unique tests are failing for Version 1.5.1

[Bademiya-LR]

Errors:

12:31:22  Failure in test unique_jira__timestamp_issue_field_history_issue_timestamp_id (models/jira.yml)
12:31:22
12:31:22    Got 3 results, configured to fail if != 0
12:31:22  
12:31:22
12:31:22    compiled code at [target/compiled/jira/models/jira.yml/unique_jira__timestamp_issue_field_history_issue_timestamp_id.sql](https://ht901.us1.dbt.com/api/v2/accounts/79120/runs/465598695/artifacts/compiled/jira/models/jira.yml/unique_jira__timestamp_issue_field_history_issue_timestamp_id.sql)
12:31:22  
12:31:22
12:31:22  Failure in test unique_jira__daily_sprint_issue_history_sprint_issue_day_id (models/jira.yml)
12:31:22
12:31:22    Got 75 results, configured to fail if != 0
12:31:22  
12:31:22
12:31:22    compiled code at [target/compiled/jira/models/jira.yml/unique_jira__daily_sprint_issue_history_sprint_issue_day_id.sql](https://ht901.us1.dbt.com/api/v2/accounts/79120/runs/465598695/artifacts/compiled/jira/models/jira.yml/unique_jira__daily_sprint_issue_history_sprint_issue_day_id.sql)

[fivetran-avinash]

Hi @Bademiya-LR , could you provide more context regarding this issue?

For jira__timestamp_issue_field_history and jira__daily_sprint_issue_history, was there any patterns you saw with the test failures? i.e. did the duplicate issues/sprints have anything in common?

Additionally,

• was this a Fivetran Quickstart /Transformations project, or on dbt Core/Cloud?
• dbt_project.yml configurations if applicable
• dbt version
• database you're running with
• did this happen on a full refresh or an incremental run?

[Bademiya-LR]

Hi @fivetran-avinash, we are getting this issue from our CI job when we are trying to update the DBT package to 1.5.x vesion.

Currently, we are using DBT Cloud with latest versionless and we are using the fivetran/jira package in our project. We are using BigQuery as our Data Warehouse.

We are getting the above error in our CI Job, for the command dbt build --full-refresh --select +state:modified+.

Hope you got all the required information.

[fivetran-avinash]

Thanks @Bademiya-LR. Do you happen to know what your prior version of fivetran/jira was you were using as well?

I think the next best step is to open a Fivetran support ticket so we can understand your issue better. Once the ticket is open, we'd request you ask you provide the above details in the ticket, then request a data access share. Our team can then investigate the results of your models directly, and see if we can figure out why these test errors are occurring.

Let me know if you have any questions!

[Bademiya-LR]

@fivetran-avinash - Our current fivetran/jira version is 1.4.0.

[fivetran-avinash]

Hi @Bademiya-LR , again, we recommend creating a support ticket with Fivetran support and requesting a data share for followup investigation. We can investigate your data directly and come up with a better understanding as to why these tests are failing. Let me know if you have any questions about that approach.

I did some investigation from our end on what could've changed from 1.4.0 to 1.5.0. We're thinking that maybe multiple is_active/author_id fields at the same issue_id/updated_at time might be causing fanout.

IMPORTANT: I strongly recommend opening a support ticket above and providing your findings to the inquiries below rather than providing them in this thread.

In jira__timestamp_issue_field_history. could you do the following query:

      select valid_from, issue_id, source_relation
      from `[your database].[your schema].jira__timestamp_issue_field_history`
      group by valid_from, issue_id, source_relation
      having count(*) > 1

That should get you the three test failures and any other default data that could potentially cause fanout. if you look at issue_multiselect_history and view updates to the is_active/author_id fields, what do you observe with the multiple issue_id/updated_at (which becomes valid_from) records? That might help us identify the culprit for the eventual unique test failures.

Likewise with jira__daily_sprint_issue_history, we should see what the duplicates are:

      select date_day, sprint_id, issue_id, source_relation
      from `[your database].[your schema].jira__daily_sprint_issue_history`
      group by date_day, sprint_id, issue_id, source_relation
      having count(*) > 1

The split_sprint_ids macro does now separate out sprints, so it's possible the same sprint might be aggregated in jira__daily_issue_field_history, then split out in jira__daily_sprint_issue_history.

1. Could you see if in your source sprint table, this sprint_id has multiple sprint records, and if one of them is _fivetran_deleted=true. That might explain the potential fanout, with sprint now integral to the join.
2. If not, could you see if in issue_multiselect_history (like above), whether there are multiple updated_at rows for a field_value (where it's a sprint)/issue_id pair on a specific change by day.

Hope that helps!