Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Table for getting history details of jira data with old value & new value

Not planned
Shilpa Sanil User

Connector name:All

Table name:New table 

API documentation link: NA

 

Additional details:

Currently, in the Fivetran history tables (issue_field_history and issue_multiselect_history), data is populated with the newly updated value and the corresponding timestamp for a given Jira Issue ID and field combination.

However, the current schema does not store the previous (old) value when a field value is modified. While it is technically possible to derive the old value through SQL queries on the history tables, doing so introduces significant performance overhead, as these tables are quite large.

 

In the on-premise Jira database, we were able to retrieve both the old value and new value directly, which greatly simplified reporting and analysis.

 

Would it be possible to create a new table in the Fivetran database with the following structure?

Column Name

Description

Issue ID

Jira Issue Identifier

Custom Field ID

Jira Custom Field Identifier

Old Value

Previous field value

New Value

Updated field value

Modified On

Date and time of modification

 

Having such a table would significantly simplify BI reporting and historical analysis while avoiding complex and performance-intensive queries on the existing history tables.

Please sign in to leave a comment.

Comments

2 comments

Date Votes
  • Official comment
    Frank Fivetranner
    • Edited

    Hi Shilpa,

    Thanks for submitting this feature request. I would recommend that you consider a downstream transformation to achieve this instead:

    Here's an example of what that could look like

    -- Derives old_value using LAG() over the chronological history per issue+field
    WITH field_history_with_old_value AS (
    SELECT
    issue_id,
    field_id,
    time AS modified_on,
    LAG(value) OVER (PARTITION BY issue_id, field_id ORDER BY time) AS old_value,
    value AS new_value
    FROM jira.issue_field_history
    )

    SELECT
    h.issue_id,
    h.field_id,
    f.name AS field_name,
    h.old_value,
    h.new_value,
    h.modified_on
    FROM field_history_with_old_value h
    LEFT JOIN jira.field f ON h.field_id = f.id

    Let me know if this helps.

    Thanks,
    Frank

  • Shilpa Sanil User

    Hi Frank,

     

    Thanks for the reply. we already have this kind of query but the request is whether we can get this as a table as most of our teams would need such data to be pulled and the query does have performance issue and can't get the data as per rquirements.