[Bug] Status field populated with null in incremental running

RivkiHofman commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

Hi

Regarding the field : status in table : JIRA__DAILY_ISSUE_FIELD_HISTORY. When I run full running the field populate fantastic, but when I run incremental running the field populate with null values. I investigate the code and see that in the high version of package(version: 0.9.0) the value in the table is status_name, in the low version(version: 0.6.0) the value is status_id, and when the value is status_id the problem not happen, because in high version the join is between status_id and status_name:

In this table status_id:

select * from BIPRODDB.dwh_actimize_jira.int_jira__field_history_scd where valid_starting_on >= (select max(date_day) from BIPRODDB.dwh_actimize_jira.jira__daily_issue_field_history )

In this table status_naem:

select * from BIPRODDB.dwh_actimize_jira.jira__daily_issue_field_history where date_day = (select max(date_day) from BIPRODDB.dwh_actimize_jira.jira__daily_issue_field_history )

And if the value arrived from the second table(most_recent_data) and join to status table with status_id, it will be null...

Relevant error log or model output

No response

Expected behavior

I expect to get the last value of status and not null, need to change or remove the join to status table, in cases that the value arrived from target table and not from the increment table.

dbt Project configurations

name: 'actimize_project' version: '1.0.0' config-version: 2

vars:
  # jira_issue_history_buffer: 12
  issue_field_history_columns: ['acceptance owner','acceptance criteria','action completed date','actual delivery date','actual pi','aggregated done story points','aggregated remaining story points',
                                'aggregated story points','aha url','analysis status','append release notes','artifact id','artifact version','artifactory link','assignment type','automated',
                                'backlog rank','begin date','bug origin','candidate for automation','capitalize','clone','closed','code freeze date','commitment level','commitment type','cs task id',
                                'cs task owner','customer','customer facing guides - docenter','customer facing guides - sandbox','customer impact','customer priority','documentation completed',
                                'documentation link','documentation required','draft document url','end date','environment name','epic color','epic description','epic link','epic name','epic planned pi',
                                'epic status','epic story points','epic type','escape analysis category','eta given date','expected next update','external issue id','field readiness notes','fix notes',
                                'flag - comment added','flag - status changed','found by automation','found on build','fr status','functionality','functionality required version','gdpr files location',
                                'git link','guide names','guide page\section','guide version','high level estimate','impact','installation steps','instance sp','instance version','internal facing guides - docenter',
                                'internal facing guides - sandbox','investigation steps','issue color','justification','last status change','last updated by wiser','limitation reason','limited release',
                                'link to case/sr','link to cs task','link to task','milestone','old issue key','origin','original estimate','originated in version','os type','overall cst priority',
                                'packaging status','planned date','planned pi','planned release','possible root cause','priority customer','product','project type','quality reason','quality sub-reason',
                                'rank','regression','release highlights','release notes','release on scope','release on time','release type','reopen counter','requested date','requested pi','requested release',
                                'required actions','requirement category','resolution name','resolution type','resolved date','risk score','rn description','rn fix','rn summary','rollback instructions',
                                'root cause analysis','root cause for no automation','rtc id','run on build','sdc link','severity','shared attachment folder','short description','shortcut opportunity',
                                'shortcut opportunity description','solution required version','start date','steps to reproduce','story description','story points','support quality of escalation','svn commits',
                                't-shirt size','task classification','task id','team name','technical availability date','technical description \ limitation','tenant id','test coverage type','url to rtc',
                                'url to tracker','version','virus scan status','wa bug created','wiser attachments','wiser case / sr owner','wiser case/sr#','wiser parent creation date','wiser sysid','wiser task cancellation reason']

  jira_database: fivetran_actimize
  jira_schema: jira_cloud_prod

models:
  +copy_grants: true 
  jira:
      +schema: dwh_actimize_jira
  jira_source:    
      +schema: dwh_actimize_jira

Package versions

packages:

package: fivetran/jira version: 0.9.0

What database are you using dbt with?

snowflake

dbt Version

dbt Version : 1.3

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.

fivetran-joemarkiewicz commented 1 year ago

Hi @RivkiHofman thanks for reaching out and creating this issue.

I am currently looking into this issue and appreciate your insight into where the issue may be originating. I will post back once I have narrowed down the issue and have identified a possible resolution.

fivetran-joemarkiewicz commented 1 year ago

Hi @RivkiHofman thanks so much for pointing us in the right direction in regards to what may be going wrong with the incremental strategy. You were completely correct that the status join is what was returning the inaccurate status data on incremental runs.

I was able to open the above PR that should resolve this issue in our next release of the package. If you have availability, you can test these changes using the below packages.yml config in place of the official Jira version.

packages:
  - git: https://github.com/fivetran/dbt_jira.git
    revision: bugfix/status-id-matching  
    warn-unpinned: false

Please be aware though that this is built off our latest version of the package (v0.12.2) as opposed to the version you highlighted in this ticket (v0.9.0). Let me know if you are able to try the work in progress branch. Thanks again!

RivkiHofman commented 1 year ago

Hi @fivetran-joemarkiewicz

I tested this version and it works well.

Thanks for the quick fix, I really appreciate it. I'm going on a vacation in the next two weeks, so when I will come back I will change the package version.

Thank you very much and have a good weekend.

fivetran-joemarkiewicz commented 1 year ago

Thanks again for helping us through identifying this issue and zeroing in on what the issue was. The latest release (v0.13.0) takes the branch you tested above into consideration. Therefore, the issue should be resolved upon upgrading to the latest version.

I will close out this issue, but please feel free to reopen if the issue persists on your end.

RivkiHofman commented 1 year ago

Thank you very much for your quick handling and kind support.

fivetran / dbt_jira