[Clarification] Issue Field History restarted to To Do status after resolved

[dennysrega1ado]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Since I could not find the Fivetran community forum, I posted this issue here.
Why does the issue_field_history table continue recording "changes" if the issue has already been closed?
It seems like the status was reset to To Do status.

Relevant error log or model output

n/a

Expected behavior

I expect the issue_field_history to stop recording the issue since the ticket status has not changed.

dbt Project configurations

  jira:
    +schema: fivetran_jira

Package versions

packages:
  - package: dbt-labs/dbt_utils
    version: 1.0.0
  - package: brooklyn-data/dbt_artifacts
    version: 2.2.2
  - package: data-mie/dbt_profiler
    version: 0.8.1
  - package: fivetran/zendesk
    version: 0.12.0
  - package: dbt-labs/audit_helper
    version: 0.7.0
  - package: get-select/dbt_snowflake_monitoring
    version: 5.0.3
  - package: fivetran/jira_source
    version: [">=0.7.0", "<0.8.0"]
  - package: fivetran/jira
    version: [">=0.17.0", "<0.18.0"]

What database are you using dbt with?

snowflake

dbt Version

root@82b6a9e763a1:/app/data_marts# dbt --version
Core:
  - installed: 1.6.9

Additional Context

We are running DBT CLI in AWS ECS

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]

Hey @dennysrega1ado thanks for opening this question. This issue is a great place to ask the clarification question!

I have seen this behavior in other environments when some tickets are prematurely resolved and then moved back into an active ticket. For example, at the end of the day someone accidentally marks the ticket resolved, but then switches the status back to TO DO. This will update the status, but the resolved date will be unchanged.

For this ticket, are you able to confirm if a similar behavior is seen within your Jira application? Alternatively, you may be able to see this in the underlying source issue_field_history table for this ticket; however, you will only see status IDs and whatnot.

[dennysrega1ado]

🤔 Hi @fivetran-joemarkiewicz
Thanks for the quick response.
The underlying table doesn't seem to describe that behavior.

JIRA UI

issue_field_history table

In addition, there seems to be a discrepancy between the status_id and the status values in the issue_enhanced table.

The status_id=6 correspond to Closed, not To Do.

Underlying status table

[fivetran-joemarkiewicz]

@dennysrega1ado thanks for sharing these additional details.

The disconnect between status and the jira__issue_enhanced model makes sense since the status is pulled from the latest record in the jira__daily_issue_field_history model. Therefore, since we see that status is incorrectly labeled as To Do, it persists to the issue enhanced model. I believe there should be a field titled current_status in the jira__issue_enhanced model that will map correctly. In an ideal scenario these two fields should be the same.

I do recall seeing a similar issue you are highlighting in the past. In Issue #100 we found that when an issue is closed but other fields are updated after the closed date it can cause some unexpected behavior upon incremental runs. The issue I shared resulted in null records, but there very well be something at play here which is a different issue but seeing a similar unexpected result. Do you know if these tickets you are sharing had any other changes outside of the status field after they were closed?

Lastly, do confirm if this is an incremental issue, would you be comfortable running a full refresh and letting me know if that resolves the issue? If it does, then we know where to look for possibly resolving an issue. If not, then we may need to scope this out further. Thanks!

[dennysrega1ado]

@fivetran-joemarkiewicz it seems to be an incremental issue.

I ran the full-refresh on jira__daily_issue_field_history and the issues have gone.

Checking the underlying table ISSUE_FIELD_HISTORY, the latest change occurred when the ticket was closed on July 5th.

As far as I know, I can't check historical changes over this ticket using JQL in Jira UI.

1. Is there an easy way to check log changes?
2. We have a github integration on Jira, so the branches, commits and PRs are automatically updated.

select
    issue_id, time,
    field_id,
    value,
    IS_ACTIVE
    from pangea.jira.ISSUE_FIELD_HISTORY
where
 ISSUE_ID= 68470
order by time desc;

[fivetran-joemarkiewicz]

Thanks for sharing @dennysrega1ado and I would agree that this does seem to be an incremental issue as it was able to be accurately resolved following a full refresh.

I still am not exactly sure where the issue may be originating. However, I am going to try and setup a test scenario on my end to try and recreate the issue you saw previously. From there I should be able to see if I can recreate the issue and then triage further from there to see what possible updates we need to apply to the underlying model code.

I'll be sure to report back once I am able to recreate the issue and if I find any leads. Feel free to share any other insights in the meantime or if you start to see this issue on another ticket.

[fivetran-joemarkiewicz]

@dennysrega1ado I attempted to recreate the issue locally, but was unable to obtain the same results you shared. I know these issues have resolved following the full refresh, but are you seeing any others since then?

Additionally, was this a widespread issue or was this only impacting a few issues in your Jira instance?

[fivetran-reneeli]

Hi @dennysrega1ado ! It seemed that the full refresh resolved this issue, but wondering if you've seen it pop up since then as well.

[fivetran-reneeli]

Marking as stale since we've been unable to recreate the issue. If anyone else comes across this issue feel free to post in this thread and we will reopen.

[dennysrega1ado]

Hey @fivetran-reneeli, @fivetran-joemarkiewicz

It happened again and the full-refresh fixed it.
I'll troubleshoot it and let you know my findings.

[fivetran-reneeli]

Thanks @dennysrega1ado for reaching back out and informing! Keep us updated!

[dennysrega1ado]

do you think upgrading from 0.17.0 to 0.18.0 will fix this issue or is unrelated?

[fivetran-reneeli]

Hi @dennysrega1ado, the v0.18.0 release definitely may help, due to the lookback window now spanning 1 week vs a few days. But we'd have to know more about the specific root cause of the issue before suggesting any other solutions.

[syed-a-ali]

Hi, we had upgraded to v0.18.0 in November as advised but still the issue intermittently occurs the latest being the components field in the jira__issue_enhanced model. In short, the component was populated on Feb 9th but did not show up in the jira__issue_enhanced when incrementally running until a full refresh was performed. Please let us know if any further details are needed but below is the query and results:

select
    date_day, issue_id, date_week, components, issue_day_id
from fivetran_jira.jira__daily_issue_field_history
where issue_id = '72302' order by date_day;

+----------+--------+----------+-------------+--------------------------------+
|DATE_DAY  |ISSUE_ID|DATE_WEEK |COMPONENTS   |ISSUE_DAY_ID                    |
+----------+--------+----------+-------------+--------------------------------+
|2025-02-08|72302   |2025-02-03|             |9c2a1cbdab1ae2e0a601670c9f8977b2|
|2025-02-09|72302   |2025-02-03|PartnerOutage|f90b9737dc0fc6c61445a0b38279b8f6|
|2025-02-10|72302   |2025-02-10|PartnerOutage|c2e813a425370591825e421f5f9f847f|
|2025-02-11|72302   |2025-02-10|PartnerOutage|b535a8aad64d8ecc30ea92e924edab4d|
|2025-02-12|72302   |2025-02-10|PartnerOutage|796f104b866029592c1396ccd6b19672|
|2025-02-13|72302   |2025-02-10|PartnerOutage|e6b6675807fb4315218b49bc4b391e63|
|2025-02-14|72302   |2025-02-10|PartnerOutage|fe875f6f5c8f93bfe3f0e5884df73283|
|2025-02-15|72302   |2025-02-10|PartnerOutage|ff9579921745b8e8f2aa26cd7ebfcb1b|
|2025-02-16|72302   |2025-02-10|PartnerOutage|6ae3fdb9cdd746f7fe433eac86f3e62d|
|2025-02-17|72302   |2025-02-17|PartnerOutage|b5f5d424acee4d9cbb5d4c3a70de409b|
|2025-02-18|72302   |2025-02-17|PartnerOutage|d10aef360701a68111d59d61247691da|
|2025-02-19|72302   |2025-02-17|PartnerOutage|d681781d3cb1c6f3c2d34baaec4a41dc|
+----------+--------+----------+-------------+--------------------------------+

[syed-a-ali]

After going over resolved issues it seems our symptoms are aligned with this closed issue, however, we have upgraded to the version that it was resolved in

[fivetran-joemarkiewicz]

Hi @syed-a-ali thanks for chiming in here! Happy to help look into this further. I wonder if the timing of the component being applied on the last day of the windowed week could be a lead 🤔. I'll look into this on our end.

Are you seeing this issue for other issue fields in the table? If so, does it align with the last day of the week window being a pattern? Also, what warehouse type are you using. Thanks!

[syed-a-ali]

Hi @fivetran-joemarkiewicz , thank you for your response. It is possible the last day of the window might affect this. Since we just ran a full refresh we may not presently have an active use case. However, we'll monitor the data and update here should more issues crop up.

[fivetran-joemarkiewicz]

@syed-a-ali would you be able to share which warehouse you're using? I've attempted to recreate the same scenario (date of update and component being updated), but was unable to see the same issue. I tested on BigQuery, but I wonder if this issue is specific to a different warehouse.

[syed-a-ali]

@fivetran-joemarkiewicz we're using Snowflake. Thanks.

[fivetran-joemarkiewicz]

Thanks @syed-a-ali - I've been able to test a variety of scenarios on a Snowflake warehouse and came to the following conclusion:

• ✅ I'm able to see the expected results on an incremental run when an issue has a component added on 2/9/25. If I run once on 2/9/25 without the component added I see no component (as expected), then after adding the component at the source and running an incremental run I now see the component in the jira__daily_issue_field_history model (expected). Then subsequent days/weeks the component is properly updated.
• ✅ If I follow the same approach but I wait roughly a week to run in the incremental job then I still see the component updated as expected.
• ❌ If I follow the same approach used above but wait roughly three weeks to run the incremental job then I see the same behavior you called out, then a full refresh fixes this issue.

Based on the above, do you know if in your scenario either:

• The incremental run wasn't executed for at least over 2 weeks.
• The component addition at the source wasn't picked up for at least over 2 weeks.

If not, I'm struggling to understand what could be the issue here since I was only able to replicate the issue when waiting for more than 2 weeks time.

If this does explain your scenario then there is a fairly straightforward fix (as long as you're using this dbt package via dbt Core). You can leverage the lookback_window variable (details here) to expand the lookback window the incremental model currently uses. By default we only look back 1 week in addition to the active week (roughly 2 weeks). This is to save on performance and compute costs. However, if you want/need to expand this window further you can do so by setting the window to look back more weeks. This just means we will take more weeks into consideration on each subsequent incremental run. So if you run the models and an update from 4 weeks ago needs to now be captured, it will be able to account for this on an incremental run. The below variable config matches the scenario I just detailed:

vars:
  lookback_window: 4 ## In weeks. Default is 1

If you're using this as a Quickstart data model, then unfortunately a periodic full refresh will be required until we're able to support variable configuration.

Hopefully this helps. Thanks!