4 new Jira tables for fresher data

[#1]

Hi, community!

A while ago, we made efficiency changes to 5 of our most commonly queried Jira tables. This significantly improved their query performance, but it also impacted the freshness of their data.

We’ve heard your feedback about needing to report with fresher data, so we’re introducing new versions for 4 of those 5 Jira tables. These new versions will have the same columns as the original tables, but they’ll reflect product changes in up to 90 minutes.

However, queries that use these new tables will take longer to run, especially if there’s a lot of data. This is because they’re calculated every time the data is accessed, unlike the existing tables that use a pre-calculated snapshot.

If your reporting use case doesn’t require fresh data, you may want to keep using the original tables for better query performance.

Identifying the new tables

You’ll find the new tables under the “Jira family of products” category in the schema browser. They’ll each have (live) appended to their display names and _live appended to their SQL names.

When you create a visual mode query, make sure to toggle Show full schema at the bottom of the schema browser to see the new tables.

Note: The display names using “Issue” will change to “Work item.” Read our announcement for more information about the terminology changes.

Display names for new tables - Fresher data	SQL names for new tables	Corresponding original tables - Faster query speed
Issue (live)	jira_issue_live	Issue
Issue field (live)	jira_issue_field_live	Issue field
Issue status history (live)	jira_issue_status_history_live	Issue status history
Project (live)	jira_project_live	Project

Best practices for using the new tables

Don’t combine the use of the new and original tables

Because of the differences in data freshness and query performance, we recommend that you don’t combine the use of the new tables with the original tables in a single dashboard or chart. This will help to avoid data inconsistencies and potentially strangely formed queries.

Create and configure a new Data Lake connection to exclusively use the new tables

To further avoid mixed usage of the new and original tables, we recommend that you ask your organization admin to create a new Data Lake connection exclusively for the new tables.

Your admin or someone with manage access to the Data Lake connection will then need to edit your foreign keys used in the new connection’s schema. Editing the foreign keys is important for ensuring the correct join paths are selected when you’re creating visual mode queries.

Here’s a quick Loom video to demonstrate how the join paths are affected and how to edit the foreign keys in your schema:

To edit foreign keys in the schema:

1. Select Data from the global navigation.

2. Select the newly created Data Lake connection from the list of data sources. This will take you to its data source settings.

3. Select Schema.

4. For every table that has foreign keys that point to one of the original tables:

   1. Select the arrow by the table’s name to expand the list of columns.

   2. Select the column that you want to change the foreign key for.

   3. Under the “Foreign keys” settings for the column, change the table of the foreign key to the corresponding new table (for example, if “jira_issue” was used, change it to “jira_issue_live”).

   4. Select Save to save your changes.

Do not edit the foreign keys in your pre-existing Data Lake connections

If you edit the foreign keys of a Data Lake connection that's being queried for preexisting charts and dashboards, keep in mind that visual mode queries will break. Additionally, all dashboards or charts created from a template will also be broken because they use the original tables.

SQL mode queries wouldn’t be affected. However, to avoid the risk of breaking any queries, we recommend creating and configuring a new Data Lake connection as mentioned previously.

Set your dashboards to manual refresh

If you plan to use the new tables for your dashboard, we recommend that you change the dashboard settings so it refreshes manually. This can help avoid long query runtimes and make it so you only refresh the dashboard as needed.

How to get the new tables

The new tables are available now.

For new Data Lake connections that include Jira data, they'll automatically be available.

Again, we don’t recommend editing your existing Data Lake connections, especially if they’re being heavily used. But if you wanted to do this, an organization admin needs to edit the connections. If the connection already includes Jira data, the organization admin can edit the connection and save it without making any changes. More about editing Data Lake connections.

Once you have the new tables, make sure to edit your foreign keys accordingly, as described earlier in this post.

Tell us what you think

Comment below or contact support if you have any questions or concerns. Thanks!