Schema for Jira

In addition to the tables for the Jira family of products, Jirahas extra tables added to the Atlassian Data Lake schema that give you more data related to your sprints.

Columns with an asterisk (*) are only available when you include all data for your products in a Data Lake connection. Learn more about how to set the scope of data for Atlassian Data Lake connections.

Table for component data

Component

The jira_component table lists all your global Jira components, which are created in Compass.

The table uses the following columns:

Component ID

The unique identifier of the component.

SQL name	`component_id`
Data type	String

Description

The description of the component.

SQL name	`description`
Data type	String

Name

The name of the component.

SQL name	`name`
Data type	String

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Type

The type of the component.

SQL name	`type`
Data type	String

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Issue component mapping

The jira_issue_component_mapping table holds associations between issues and project components.

The table uses the following columns:

Component ID

The unique identifier of a project component.

SQL name	`component_id`
Data type	String
Foreign key	Links to a record in the `jira_project_component` table.

Global component ID

The unique identifier of the global Jira component.

SQL name	`global_component_id`
Data type	String
Foreign key	Links to a record in the `jira_component` table.

Issue ID

The unique identifier of an issue.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Table for issue sprint history data

Issue sprint history

The jsw_issue_sprint_history table tracks changes in issue allocation to sprints over time

The table uses the following columns:

Action

Indicates whether the issue was added or removed from a sprint.

SQL name	`action`
Data type	String
Example values	added, removed

Action at

The date and time (UTC time zone) when the issue was added or removed from a sprint.

SQL name	`action_at`
Data type	Datetime

Author account ID*

The unique identifier of the person who changed the issue allocation to a sprint.

SQL name	`author_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Issue ID

The unique identifier of the issue that was allocated to a sprint.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table in the schema for Jira family of products.

Sprint ID

The unique identifier of the sprint that the issue was added to or removed from.

SQL name	`sprint_id`
Data type	String
Foreign key	Links to a record in the `jsw_sprint` table.

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Table for sprint data

Sprint

The jsw_sprint table lists the sprints in your Jira projects.

The table uses the following columns:

Completed at

The date and time (UTC time zone) when the sprint’s status changed to closed, which signifies the sprint was completed.

SQL name	`completed_at`
Data type	Datetime

Created at

The date and time (UTC time zone) when the sprint was created.

SQL name	`created_at`
Data type	Datetime

Goal*

A brief explanation of what the team plans to achieve during the course of the sprint.

SQL name	`goal`
Data type	String

Name*

The name of the sprint.

SQL name	`name`
Data type	String

Original board ID*

The unique identifier of the board where the sprint was originally planned or created.

This does not mean the sprint was started on this board. You can create a sprint on one board but start the sprint on a separate board. The original board will be where the sprint was created.

SQL name	`original_board_id`
Data type	String

Projected to end at*

The date and time (UTC time zone) when the sprint was projected to end.

SQL name	`projected_to_end_at`
Data type	Datetime

Projected to start at*

The date and time (UTC time zone) when the sprint was projected to start. It will contain the actual sprint start date when the sprint starts.

SQL name	`projected_to_start_at`
Data type	Datetime

Sort order*

The sequence order of sprints, in which sprints are displayed from smallest to biggest.

SQL name	`sort_order`
Data type	Number

Sprint ID

The unique identifier of the sprint. Use this as the primary key for sprint data.

SQL name	`sprint_id`
Data type	String

Sprint reference

The identifier of the sprint within a Jira site.

SQL name	`sprint_ref`
Data type	String

Status*

The current status of the sprint.

SQL name	`status`
Data type	String
Example values	future, active, closed

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Was this helpful?

It wasn't accurateIt wasn't clearIt wasn't relevant

Still need help?

The Atlassian Community is here for you.

Ask the Community