Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Expected JIRA Data

Now that JIRA is successfully connected to your Stitch account, what comes next? In this article, we'll cover:

Replicating JIRA Data

Historical Sync

By default, a historical sync of JIRA data goes back one year from the Stitch connection date.

Frequency

Stitch will replicate JIRA data based on the frequency you define. The default setting is every half hour, but you can change it to more or less frequent based on your needs.

Replication Method

When Stitch replicates JIRA data, it does so using Full Table Replication. This means that every time JIRA data is queued for replication, all rows in a table - including existing, new, and updated - will be replicated.

The only exception to this is the jira_issues table, which supports Incremental Replication and tends to be the largest table.

Due to the potentially high number of rows for each replication attempt, we recommend choosing a less often frequency setting to keep your row count down.

JIRA Tables in Your Data Warehouse

Below is a list of the main and subtables that will be created in your data warehouse. Because JIRA uses nested arrays to structure data in some of its API's endpoints, Stitch will de-nest these arrays and create subtables.

Refer to the Handling of Nested Data Structures doc for details on this Stitch feature and how to connect de-nested subtables back to their parent table.

To see a full list of attributes for each table, refer to JIRA's API documentation - click the name of the table to go there.

jira_issues

Primary Key: _id
The jira_issues table contains information about the issues in your JIRA account. Attributes include the issue ID (id), URL, Key, fields, and attachment details.

Accounting for Deleted Issues
When an issue is hard-deleted in JIRA, the record for the issue will remain in your data warehouse. This happens because of the Replication Method the jira_issues table uses and how JIRA's API functions.

  • The jira_issues table is incrementally replicated based on the updated_at column. This means that rows with values in this column that are greater than or equal to the last recorded MAX value will be selected for replication. If a record is hard-deleted, there'll be no value for Stitch to read and thus no way to update the record, which is why it will remain in the data warehouse.
  • JIRA's API doesn't include a flag to indicate deletes.

The suggested workaround (although it may be a bit cumbersome) is to use the fields__status__name column - which indicates the current status of an issue - to track deletes. Before deleting the issue, you could change the status to something that would only indicate a delete and then use that status as a filter in your queries.

Below are the issues subtables:

  • jira_issues__fields__attachment - This table contains data about issue attachments.
  • jira_issues__fields__comment__comments - This table contains data about issue comments. 
  • jira_issues__fields__fixversions - This table contains data about issue fixes.
  • jira_issues__fields__labels - This table contains data about issue labels.
  • jira_issues__fields__subtasks - This table contains data about issue subtasks.

jira_projects

Primary Key: _id
The jira_projects table contains information about individual projects in your JIRA account. Attributes include the project ID (id), description, Key, name, lead info, assignee info, avatar URLs, project category info, version info, and user info.

Below is the projects subtable:

  • jira_projects__projectkeys - This table contains data about project keys.

jira_project_categories

Primary Key: _id
The jira_project_categories table contains information about the categories assigned to the projects in your JIRA account. Attributes include the category ID (id), name, and description.

jira_project_roles

Primary Key: _id
The jira_project_roles table contains information about the roles that can be assigned to projects in your JIRA account. Attributes include the role ID (id), name, and description.

Below is the project_roles subtable:

jira_project_types

Primary Key: Key
The jira_project_types table contains information about the project types defined in your JIRA account. Attributes include the project type Key, description, icon, and hex color code.

jira_resolutions

Primary Key: _id
The jira_resolutions table contains information about the resolutions in your JIRA account. Attributes include the resolution ID, name, description, and URL.

jira_users

Primary Key: Key
The jira_users table contains information about the users in your JIRA account. Attributes include the user’s name, Key, email address, avatar info, display name, status, and timezone.

RELATED

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!