Data Studio

Data Studio is a powerful PostgreSQL query and data visualization tool that lets you explore your engineering data, create custom reports, and share insights with your team.

Common use cases for Data Studio include:

Custom reporting: Build tailored reports and dashboards beyond the standard DX views to answer specific questions about your engineering metrics.
Data exploration: Query your raw engineering data directly using SQL to uncover patterns, trends, and insights unique to your organization.
AI-powered analysis: Use the built-in AI assistant to generate queries and visualizations based on natural language requests, making data analysis accessible to non-technical team members.
Team collaboration: Save and share queries with teammates, enabling collaborative data analysis and consistent reporting across your organization.
Real-time data access: Connect external tools like Google Sheets to your data through datafeeds for live reporting and analysis.

Getting started

Data Studio includes a full query editor with schema documentation so you can understand your available data. You can write SQL directly or use the AI assistant to generate queries from natural language requests.

AI assistant

The AI assistant understands your entire DX dataset and can help generate SQL queries, create visualizations, and provide insights about your engineering data. Simply describe what you want to analyze in natural language, and the AI will generate the appropriate SQL query.

AI Chat Example

The AI assistant has full context about your data schema and can help you:

Generate complex SQL queries from natural language requests
Suggest relevant visualizations for your data
Provide insights and explanations about your results
Help optimize query performance

Query editor

The query editor provides a full SQL interface with syntax highlighting, autocomplete, and schema browsing. You can write queries directly against your engineering data and see results in real-time.

Query Editor

Features include:

Schema browser: Explore all available tables and columns with detailed documentation
Query history: Access previously run queries and saved queries
Syntax highlighting: Full SQL syntax support with error detection
Autocomplete: Smart suggestions for table names, columns, and SQL keywords
Template variables: Create dynamic queries using $variable_name syntax for interactive parameters

Template variables

Template variables allow you to create dynamic, reusable queries by adding input parameters. Use the $ prefix to define variables in your SQL queries, and Data Studio will automatically generate input fields for each variable.

For example, in a query like:

SELECT * FROM github_pulls
WHERE repo_name = $repo_name
LIMIT 10;

Using $repo_name in your query will create a repo_name input field that users can fill in to filter results dynamically. This makes queries more flexible and enables self-service data exploration for your team members.

CSV export

Export query results as CSV files for use in external tools or further analysis. This is useful for:

Creating reports in other tools
Sharing data with stakeholders
Archiving analysis results

Visualization

Transform your query results into compelling visualizations with built-in charting capabilities. Data Studio supports various chart types including:

Standalone tables for detailed data views
Pie charts for proportional data
Bar charts (stacked or clustered) for comparisons
Line charts for trends over time
Scatter charts for plotting correlated data

All chart views support drilldowns for more granular analyses of the underlying data.

Datafeeds

Connect your DX data to external tools through datafeeds, which provide real-time CSV access to your query results. This enables:

Google Sheets integration: Import live data directly into spreadsheets using the provided datafeed URL
External dashboards: Connect BI tools to your DX data for advanced reporting
Automated reporting: Use datafeeds in scripts and automated workflows

To use a datafeed:

Run and save your query in Data Studio
Copy the provided datafeed URL from the share menu
Use the URL in external tools like Google Sheets with the =IMPORTDATA() function
For more flexibility; use the queries.datafeed API method to display data in any format and embed the data in any system you’d like.

Saved query referencing

Saved query references allow you to build modular, reusable queries by referencing other saved queries as subqueries. This enables you to create complex analyses by composing smaller, well-tested query building blocks.

Reference a saved query using the $query_<id> syntax, where <id> is the unique identifier of the saved query:

-- Reference a saved query that returns active users
SELECT name, email FROM $query_abc123
WHERE created_at > '2024-01-01'

When the query executes, the reference is replaced with the saved query’s SQL wrapped in parentheses, making it a valid subquery.

To reference a saved query:

Open the saved queries panel in the query editor
Find the query you want to reference
Click the menu icon (three dots) next to the query name
Select Copy as subquery to copy the reference to your clipboard
Paste the reference into your query

When referencing saved queries, the following limitations apply:

Maximum nesting depth: Queries can be nested up to 5 levels deep. Exceeding this limit will result in an error.
Circular references: A query cannot reference itself, either directly or through a chain of other queries. Data Studio validates for circular references when you save a query.
Access control: You can only reference:
- Public saved queries (visible to your entire account)
- Private saved queries that you own
You cannot reference another user’s private queries.

Backstage charts

Integrate DX data directly into your Backstage service catalog using the DxDataChartCard component from the @get-dx/backstage-plugin. This allows you to display Data Studio query results as charts within your Backstage service pages.

Backstage DORA Charts Demo

Setup

Install the DX Backstage plugin in your Backstage instance
Create and save a query in Data Studio
Copy the datafeed token from the query’s share menu
Add the DxDataChartCard component to your Backstage service page

Usage

The DxDataChartCard component displays saved Data Studio queries as charts within Backstage:

import { DxDataChartCard } from "@get-dx/backstage-plugin";

function MyDashboard() {
  const { entity } = useEntity();

  return (
    <DxDataChartCard
      title="Deployment Frequency"
      description="Weekly deployments over time"
      datafeedToken="your-datafeed-token"
      unit="deployments"
      variables={{
        teamId: entity.metadata.annotations?.["getdx.com/id"],
      }}
      chartConfig={{
        type: "line",
        xAxis: "date",
        yAxis: "count",
      }}
    />
  );
}

Custom reports

Custom reports allow you to save queries and chart configurations as reusable tiles that can be displayed on dedicated report pages. These reports provide a powerful way to create personalized dashboards and share insights with your team. Custom reports can be viewed alongside standard DX reports, as well as added to report Collections and Dashboards for added visibility and navigation.

Custom reports example with Copilot usage

Key features

Tiles: Save query and chart combinations as resizable, reorderable tiles
Breakdown tables: Aggregate query results by team hierarchy, leaf team, group, or attribute in an interactive table
Drilldowns: Add supplemental queries to chart data points that provide granular detail views
Sections: Organize tiles into sections that can also be reordered
Descriptions: Add notes at the top of reports or sections with full markdown support
Flexible layout: Resize and arrange tiles to create the perfect dashboard layout

Access control

Custom reports include robust visibility and access settings:

Edit permissions: Control who can modify the custom report
Individual access: Grant access to specific team members
Account visibility: Choose whether the report is visible to your entire account or kept private

Charts

Data Studio supports the following chart options:

Line chart: Use for trends over time, such as weekly PR volume or deployment frequency
Bar chart: Use for comparing categories, with stacked bars for composition and clustered bars for side-by-side comparisons
Pie chart: Use for part-to-whole views with a small number of segments, such as share by team or work type
Scatter plot: Use for relationships between two metrics, such as cycle time vs. review time, and for spotting outliers

All chart options support Drilldown detailed views.

Breakdown tables

Breakdown tables bring the team hierarchy, group, and attribute views from standard DX reports to your custom reports. Reuse the same query as you switch views.

Start with a query that includes a user or team ID, then choose metrics such as PR count or median PR size. DX calculates them for each team, group, or attribute value. Viewers can switch views, expand the team tree, and export the results without editing SQL.

Two breakdown table tiles showing median PR size by Developer Type attribute and total merged PRs in an expandable team tree

Prepare your source query

The query must return exactly one of these columns:

user_id: Values matching dx_users.id. Supports Team tree, Leaf teams, Groups, and attributes.
team_id: Values matching dx_teams.id. Supports Team tree and Leaf teams.

Use the Data Cloud id values, not names, email addresses, or source_id values. Alias an existing column when needed, such as pull_requests.dx_user_id AS user_id. Do not return both user_id and team_id.

Return one row per record you want to measure, such as a pull request. Include numeric columns for totals, averages, or percentiles.

This example returns each merged PR’s author ID and hours from creation to merge. It includes only PRs linked to a DX user. Enable the report’s date range variables before using $start_date and $end_date.

SELECT
    pull_requests.dx_user_id AS user_id,
    EXTRACT(EPOCH FROM (pull_requests.merged - pull_requests.created))
        / 3600.0 AS hours_to_merge
FROM pull_requests
JOIN dx_users ON dx_users.id = pull_requests.dx_user_id
WHERE pull_requests.merged >= $start_date
    AND pull_requests.merged < $end_date::date + INTERVAL '1 day'
    AND pull_requests.deleted IS NOT TRUE

Choose Row count to count merged PRs, or Average or Median of hours_to_merge to measure time to merge.

Add a breakdown table tile

Open a custom report you can edit. Select the + tile to add a tile, or open an existing tile’s menu and select Edit.
Enter a title and your source SQL, or select a saved query with the required columns.
Select Breakdown table in the visualization selector and run the query. Check the IDs and metric columns in Source data.
Under Breakdown columns, choose a Calculation. For calculations other than Row count, select a numeric Source column and set Precision to the number of decimal places to display.
Select Add column for each additional metric. You can calculate both an average and a median from the same source column.
Open Breakdown view to preview the result. Use View by to try the available groupings.
Select Save.

Available calculations are:

Row count: Counts source rows in each grouping. No source column is required.
Total: Sums the selected numeric column.
Average: Calculates the arithmetic mean of the selected numeric column.
Median: Calculates the 50th percentile of the selected numeric column.
P10, P25, P75, and P90: Calculate the corresponding percentile of the selected numeric column.

The Source selector lists numeric columns, excluding user_id and team_id. Headings combine the source name and calculation, such as Hours to merge (Median). Row counts always display as whole numbers.

Explore the breakdown

The table starts in Team tree view. Use View by to switch between:

Team tree: Expand parent teams to see their children. Parent metrics include data from the parent and all its descendant teams.
Leaf teams: Show only teams with no child teams.
Groups: Compare metrics across user groups. Requires user_id.
Attributes: Compare values of an attribute, such as Developer Type. Requires user_id.

DX calculates parent metrics from the source records. For example, a parent’s average time to merge includes every matching PR, so teams with more PRs carry more weight. Adding parent and child totals would double-count records. Group totals can also overlap when users belong to multiple groups.

Search by team, group, or attribute value, or sort by a column. Download CSV exports the current view, preserving the search, sort order, and expanded rows. Expand any teams you want included in the download.

Iterating on standard DX reports

Many DX reports support a SQL preview. These queries can serve as a great starting point for Data Studio queries and reports, especially when introducing variables. For the best results, filter the report before copying the SQL to Studio so that variables can be cleanly introduced to queries.

Variables

Custom reports support dynamic filtering through variables, making your reports interactive and adaptable. When you enable variables on a report, users can select filter values from dropdowns, and those selections are automatically substituted into your SQL queries.

Variables use a simple substitution syntax: place the variable name (prefixed with $) anywhere in your SQL query. When the report runs, the system replaces the variable with the selected values.

For example, a query could be written using the $team_ids, $start_date, and $end_date variables to count merged PRs per developer:

SELECT
    dx_users.name AS developer,
    COUNT(*) AS pull_requests_merged
FROM pull_requests
JOIN dx_users ON dx_users.id = pull_requests.dx_user_id
JOIN dx_teams ON dx_teams.id = dx_users.team_id
WHERE pull_requests.dx_user_id IN (
        SELECT dxu.id
        FROM dx_teams t
          JOIN dx_team_hierarchies h ON t.id = h.ancestor_id
          JOIN dx_users dxu ON h.descendant_id = dxu.team_id
        WHERE t.id IN ($team_ids)
      )
    AND pull_requests.merged >= $start_date
    AND pull_requests.merged < $end_date::date + interval '1 day'
    AND pull_requests.deleted IS NOT TRUE
GROUP BY dx_users.name
ORDER BY pull_requests_merged DESC

Core variables

$service_ids - Filter by specific service entities in the dx_catalog_entities table
$team_ids - Filter by team assignments in the dx_teams table
$tag_ids - Filter by attributes in the dx_tags table
$user_ids - Filter by individual users in the dx_users table
$repo_ids - Filter by repositories in the repos table
$start_date and $end_date - Filter by date range

Custom variables

Create your own dropdown or text field variables to further filter custom reports.

Text field variables allow free text inputs and require:

Variable label: Display name for the variable
Variable name: Reference name for use in queries (e.g., $my_variable)
Add quotes around variables toggle: Enable quotes around values for strings, disable for numerical inputs

Dropdown variables support single-select or multi-select input and require:

Variable label: Display name for the variable
Variable name: Reference name for use in queries (e.g., $my_variable)
SQL statement: Query to populate dropdown options
that return exactly two columns:
- value - The actual value used in the query
- label - The display text shown to users
Single-select or multi-select toggle: Choose single-select to allow one selection (for example, a time increment filter) or multi-select to allow multiple selections (for example, a projects filter)
Shared toggle: Enable to allow reuse of the variable across additional reports, or disable to keep it private

Once enabled in report settings, all variables can be referenced in any query in the custom report using $variable_name syntax, enabling dynamic filtering and personalized data views. These filters support saving default selections to scope the data for future visits to the report, as needed.

Note: There are a few differences in the standard report filtering and custom report filtering logic to be aware of:

Standard Teams and Attributes filters rely on the source_id column, whereas the equivalent Core Variables in Data Studio rely on the id column in the dx_teams and dx_tags tables. To match standard team filtering, including child teams, filter on dx_teams.id through dx_team_hierarchies as in the example above. For attributes, filter on dx_tags.id.

Standard date range filtering includes the entire end date, whereas comparing directly to $end_date stops at the start of that day. To include the full end date, use an exclusive upper bound of $end_date::date + interval '1 day', as in the example above.

Drilldowns

Drilldowns let viewers click a chart point, bar, or slice to open a table of the rows behind that value. Use them when a tile shows a summary and you want people to inspect the underlying records.

Common uses include:

Click a week on a PR volume chart to see the pull requests merged that week
Click a team on a pie chart of incidents to list those incidents
Click a service on a deploy chart to review the deployments behind the count

Drilldowns are available on line, bar, scatter, and pie chart tiles. Standard table and breakdown table tiles do not support drilldowns; breakdown tables support expanding parent teams to see their children.

To add a drilldown:

Open the tile menu and select Add drilldown.
Write a SQL query that returns the rows you want to show. The dialog lists available variables for the clicked value, typically the X-axis value, plus grouping or series values when those apply.
Use those $ variables in your query (usually in a WHERE clause) to filter to the selection.
Select Save drilldown.

For example, a tile that counts merged PRs by week could drill down with:

SELECT
    pull_requests.title,
    dx_users.name AS author,
    pull_requests.merged
FROM pull_requests
JOIN dx_users ON dx_users.id = pull_requests.dx_user_id
WHERE pull_requests.merged >= $drilldown_week_start
    AND pull_requests.merged < $drilldown_week_start + INTERVAL '7 days'
    AND pull_requests.deleted IS NOT TRUE
ORDER BY pull_requests.merged DESC

Variable names are based on the tile’s chart columns. The Add drilldown dialog always shows the exact names for that tile.

You can also use report variables such as $team_ids and $start_date in drilldown SQL so the table follows the report’s filters.

After a drilldown is saved, clicking a point, bar, or slice opens a table. Viewers can search the results or download them as CSV. To change or remove a drilldown, open the tile menu and select Edit drilldown.