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?