Schema for Jira

We're updating our terminology in Jira

“Work” is the new collective term for all items tracked in Jira. As we roll out these changes, you may still see the term “issue” in some areas.

Table for board data

Jira board

The jira_board table displays work items from one or more spaces, giving you a flexible way to view, manage, and report on work in progress.

Board ID

A unique identifier for this board.

SQL name	`board_ID`
Data type	String

Board type

SQL name	`board_type`
Data type	String

Board location account ID

The account that the board belongs to.

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

Board location project ID

The space that the board belongs to.

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

Swimlane config

A swimlane is a horizontal categorization of work items in the Active sprints or Kanban board view.

SQL name	`swimlane_config`
Data type	String

Is days in column enabled

Whether a visual indicator showing the number of days a work items has been in a column is displayed.

SQL name	`is_days_in_column_enabled`
Data type	Boolean

Card colors based on

The method for assigning colors to cards on the board (for example, by assignee, work item type, priority, or JQL query).

SQL name	`card_colors_based_on`
Data type	String

Board filter ID

The board filter defines the board view, including sprints and sprint goals.

SQL name	`board_filter_ID`
Data type	String

Has backlog enabled

Whether the backlog is enabled for the board.

SQL name	`has_backlog_enabled`
Data type	Boolean

Has sprints enabled

Whether sprints are enabled for the board.

SQL name	`has_sprints_enabled`
Data type	Boolean

Has reports enabled

Whether reports are enabled for the board.

SQL name	`has_reports_enabled`
Data type	Boolean

Constraint

The type of constraint applied to board columns (for example, issue count or story points).

SQL name	`constraint`
Data type	String

Name

The name of the board.

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

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.

Jira board columns

The jira_board_columns view contains the column configuration for a Jira board. Each row represents one status mapped to a board column.

Board ID

The unique identifier of the board.

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

Name

The name of the board column.

SQL name	`name`
Data type	String

Status

The Jira work item status mapped to this column.

SQL name	`status`
Data type	String

Status category

The category of the Jira work item status.

SQL name	`status_category`
Data type	String
Example values	To Do In Progress Done

Status config sort order

The order that statuses appear in the column's status list, stored in ascending order.

SQL name	`status_config_sort_order`
Data type	Array

Min work item count

The minimum number of work items the column can contain at any time.

SQL name	`min_issue_count`
Data type	Number

Max work item count

The maximum number of issues the column can contain at any time.

SQL name	`max_issue_count`
Data type	Number

Sort order

The order in which the column appears on the board.

SQL name	`sort_order`
Data type	Number

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.

Jira board filter project mapping

The jira_board_filtered_project_mapping view maps each Jira board to the projects included in its filter. Each row represents one board-to-project mapping.

Board ID

The unique identifier of the board.

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

Filtered project ID

The unique identifier of the project included in the board filter. Filtered projects have their work items included on the board.

SQL name	`filtered_project_ID`
Data type	String
Foreign key	Links to a record in the `jira_project` 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 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.

Work item component mapping

The jira_issue_component_mapping table holds associations between work items 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.

Work item ID

The unique identifier of a work item.

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 work item sprint history data

Work item sprint history

The jsw_issue_sprint_history table tracks changes in work item allocation to sprints over time

The table uses the following columns:

Action

Indicates whether the work item 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 work item 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 work item 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.

Work item ID

The unique identifier of the work item 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 work item 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:

Activated at

The date and time the sprint was activated. This cannot be manually changed and reflects the actual date and time the sprint was started.

SQL name	`activated_at`
Data type	Timestamp

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.

Work item sprint mapping

The jira_issue_sprint_mapping table provides a current-state mapping between Jira work items and the sprints they belong to. Use this table to easily query which sprint a work item is currently in, without needing to interpret sprint history actions.

Work item ID

The unique identifier of the Jira work item currently assigned to a sprint.

SQL name

issue_id

Data type

String

Foreign key

Links to a table in the same app schema

Links to a record in the jira_issue table.

Links to a table in a different app schema

Sprint ID

The unique identifier of the sprint the work item is currently assigned to.

SQL name

sprint_id

Data type

String

Foreign key

Links to a table in the same app schema

Links to a record in the jsw_sprint table.

Links to a table in a different app schema

Row refreshed at

No need to provide info. All tables should have this column.

Workspace ID

No need to provide info. All tables should have this column.

Jira board

Board configuration data from Jira. Each row represents one Jira board and its settings.

Board ID

The unique identifier of the board.

SQL name	`baord_id`
Data type	String
Example values	workspace_id::board_id
Foreign key	Primary key

Workspace ID

The unique identifier of the workspace (site) the board belongs to.

SQL name	`workspace_ID`
Data type	String
Foreign key	Joins to `workspace.workspace_id`

Name

The name of the board.

SQL name	`name`
Data type	String

Board type

The type of the board.

SQL name	`board_type`
Data type	String
Example values	scrum kanban

Board account ID

The account identifier of the board administrator.

SQL name	`board_account_ID`
Data type	String
Foreign key	Joins to `account.account_id`

Board project ID

The identifier of the project associated with the board.

SQL name	`board_project_ID`
Data type	String
Foreign key	Joins to `jira_project.project_id`

Board filter ID

The identifier of the filter used by the board.

SQL name	`board_filter_ID`
Data type	String
Foreign key	Joins to `jira_filter.filter_id`

Estimation type

The estimation type configured for the board.

SQL name	`estimation_type`
Data type	String

Swimlane config

The swimlane configuration for the board.

SQL name	`swimlane_config`
Data type	String

Is days in column enabled

Whether the days in column feature is enabled for the board.

SQL name	`is_days_in_column_enabled`
Data type	Boolean

Card colors based on

The field used to determine card colors on the board.

SQL name	`card_colors_based_on`
Data type	String
Example values	ASSIGNEE ISSUE_TYPE PRIORITY NONE

Has backlog enabled

Whether the backlog is enabled for the board.

SQL name	`has_backlog_enabled`
Data type	Boolean

Has sprints enabled

Whether sprints are enabled for the board.

SQL name	`has_sprints_enabled`
Data type	Boolean

Has reports enabled

Whether reports are enabled for the board.

SQL name	`has_reports_enabled`
Data type	Boolean

Constraint

The constraint type configured for the board.

SQL name	`constraint`
Data type	String

Filter projects

The list of projects whose issues appear on the board, serialized as JSON.

SQL name	`filter_projects`
Data type	String (JSON)

Customer filters

The quick filters configured for the board, serialized as JSON.

SQL name	`custom_filters`
Data type	String (JSON)

Additional card fields

The additional fields shown on board cards, serialized as JSON.

SQL name	`additional_card_fields`
Data type	String (JSON)

Card colors

The card color configuration for the board, serialized as JSON.

SQL name	`card_colors`
Data type	String (JSON)

Board columns

The column configuration for the board, serialized as JSON.

SQL name	`board_columns`
Data type	String (JSON)

Row refreshed at

The timestamp when the row was last refreshed in the data lake.

SQL name	`row_refreshed_at`
Data type	Timestamp

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

Schema for Jira

Table for board data

Jira board

Board ID

Board type

Board location account ID

Board location project ID

Swimlane config

Is days in column enabled

Card colors based on

Board filter ID

Has backlog enabled

Has sprints enabled

Has reports enabled

Constraint

Name

Row refreshed at

Workspace ID

Jira board columns

Board ID

Name

Status

Status category

Status config sort order

Min work item count

Max work item count

Sort order

Row refreshed at

Workspace ID

Jira board filter project mapping

Board ID

Filtered project ID

Row refreshed at

Workspace ID

Table for component data

Component

Component ID

Description

Name

Row refreshed at

Type

Workspace ID

Work item component mapping

Component ID

Global component ID

Work item ID

Row refreshed at

Workspace ID

Table for work item sprint history data

Work item sprint history

Action

Action at

Author account ID*

Work item ID

Sprint ID

Workspace ID

Table for sprint data

Sprint

Activated at

Completed at

Created at

Goal*

Name*

Original board ID*

Projected to end at*

Projected to start at*

Sort order*

Sprint ID

Sprint reference

Status*

Workspace ID

Work item sprint mapping

Data share

Still need help?