[Bug] `jira__daily_issue_field_history` incorrectly joins component fields to `jira.field_option` instead of `jira.components`

agibralter commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

When you include Components (or other multi-select fields that do not store options in jira.field_option but rather in their own special tables like jira.component), jira__daily_issue_field_history incorrectly tries to expand the field values by joining on field_option for all fields.

Relevant error log or model output

No response

Expected behavior

The models should handle the "special case" fields like component and join on the correct table.

dbt Project configurations

vars:
  jira:
    issue_field_history_columns:
      # - ...
      - "Components"
      # - ...

Package versions

jira 0.8.0

What database are you using dbt with?

bigquery

dbt Version

1.0.0

Additional Context

https://getdbt.slack.com/archives/C01D1R2JLLA/p1670436806950859

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 commented 1 year ago

Thanks so much for opening this issue @agibralter and for working with @fivetran-jamie within the linked Slack thread to uncover the root issue you are experiencing.

I do feel there is a bit more for us to scope out to understand if there are any other pieces within the jira__daily_issue_filed_history model that need the joins refactored to point to the correct table. In the meantime, I will accept this bug into a future sprint. However, it is likely that we will not be able to dig deeper into it until after the New Year. We will post back here with updates once we have picked up the issue and have a working branch for you to try out!

fivetran-catfritz commented 1 year ago

Summarizing what we understand so far:

Issue occurs when specifying Components under issue_field_history_columns when declaring dbt vars.
It seems this issue occurs because components is not explicitly called out in intermediate models and gets brought in by inference
The model int_jira__field_history_scd correctly handles components, but there is an issue when iint_jira__field_history_scd gets rolled into jira__daily_issue_field_history.
The fix may be to incorporate special logic to join on jira.component instead of jira.field_option when the field_id = components in jira__daily_issue_field_history. See jira__project_enhanced as an example where components is used.
As a last task, we may want to identify other columns with similar issues and open issues on those.

fivetran-avinash commented 1 year ago

Hello @agibralter ! We have deployed a fix for this issue in our latest release of the branch! Can you test and see if this newest version of the package correctly joins the component fields from jira.components?

fivetran-avinash commented 1 year ago

@agibralter One mistake by me: I forgot to add the above specified variables to dbt_project.yml, so it might error out. We created an issue for it so we should fix it soon, but if you do add the dbt Project configuration above, it should work!

agibralter commented 1 year ago

@fivetran-avinash Hi! I was finally able to upgrade my packages and test this out. Am I supposed to see the names of the components now in the jira__issue_enhanced.components field? I still see ids:

fivetran-joemarkiewicz commented 1 year ago

Hi @agibralter thanks for testing out the latest version of the package! Unfortunately when implementing this update we realized this was much more complex than we initially understood. The changes we rolled out ended up breaking other users implementations of the package. 😨

As such, we rolled back these changes and kept this issue open. We are planning to come back to this issue in our next sprint to take more time assessing the scope of the changes and ensure we are able to resolve the initial component bug while ensuring we do not interrupt existing users workflows. When the time comes that we have a working branch available, we would love if you would be willing to test and validate you are getting the expected results once it is available.

We really appreciate your patience as we work through this issue.

agibralter commented 1 year ago

Ahh ok, no worries!! Please keep me posted.

fivetran-avinash commented 1 year ago

Hi @agibralter, we believe we've identified a solve for the components issue.This isn't live yet on main, but we do have the newest version of the daily_issue_field_history model live on a branch if you'd like to test off of and see if this actually provides you the correct data. Take a look at the branch and test and see if this new solution works for you? https://github.com/fivetran/dbt_jira/tree/release/v0.14.0

Here's the code you need to paste into your packages.yml:


packages:

[YOUR PACKAGES]

[INSERT THE FOLLOWING BELOW]
  - git: https://github.com/fivetran/dbt_jira.git
    revision: release/v0.14.0
    warn-unpinned: false

agibralter commented 1 year ago

@fivetran-avinash ah thank you!! I set it up and it seems to be working in cases where an issue has a single component selected. However, this is a one-to-many relationship in Jira so an issue can have multiple components selected. When that happens, the ids of the components are left: SELECT DISTINCT(components)... produces:

fivetran-avinash commented 1 year ago

Hi @agibralter , that is something we discovered near the end of our bug process and we've created an additional issue to address this (it seems to be impacting other fields with multiple values too), so we're hoping to have this fixed in a coming sprint. We'll keep you updated when this is deployed so you can test this as well!

Just out of curiosity, how analytically valuable is it to have the component names? We're a bit wary of doing string aggregations so we want to be sure this would be useful to switch up.

fivetran-avinash commented 1 year ago

@agibralter This current fix is now live in our latest release of dbt_jira!

agibralter commented 1 year ago

@fivetran-avinash the issue with the multiple values is still open and being addressed separately, correct?

fivetran-joemarkiewicz commented 1 year ago

@agibralter correct Issue #101 dives into the multiple values component that was uncovered in this issue.

fivetran / dbt_jira