---
title: "Writing Scorecard Checks"
canonical_url: "https://docs.getdx.com/scorecards/writing-checks/"
md_url: "https://docs.getdx.com/scorecards/writing-checks.md"
last_updated: "2026-05-19"
---

# Writing Scorecard Checks
DX Scorecards are powered by SQL. This lets teams build scorecard checks in a familiar, flexible way and take advantage of the wide ecosystem of SQL resources available. Further, check queries run against **your organization's DX Data Cloud (DXDC) instance**. This means that any data in your DXDC instance is available as input data for check queries.

The Scorecards engine runs on a regular schedule and is also triggered when the scorecard’s configuration changes. When it is time to evaluate a check result for an entity:

- The check's SQL query is executed in the DX Data Cloud (DXDC) instance database.
- Once the query has ran, the engine expects the query to have a `status` column in its results. The engine then checks if this column value is a pass, warn, or fail.
  - If there is no `status` column, the first column in the query results is used.
- The query results may also contain an optional `output` column. The `output` value represents extra information that gets displayed in the UI for service owners and stakeholders.

## Getting started

To write a check, navigate to the [Scorecards screen](https://app.getdx.com/scorecards) and select an existing scorecard or create a new one. In the scorecard editor view, there is a section for levels & checks, where you can define scorecard levels (thresholds to determine the completeness of the scorecard), and checks that correspond to each level.

### A minimal static example

Here is a "hello world" check query to try out in the query editor as you get started:

```sql
SELECT 'PASS' AS status
```

This check query does a few things:

- The checks engine looks for a `status` column. If there is no `status` column, it uses the first column in the query results.
- The value in that `status` column should be either `'PASS'`, `'WARN'`, or `'FAIL'`.

In this case, the check passes every time.

### Real-world check examples

#### Owner defined

This check relies on some of the built-in Catalog tables that are automatically synced with the DXDC instance. It checks if the entity has at least one assigned owner.

```sql
SELECT CASE
    WHEN COUNT(*) > 0 THEN 'PASS'
    ELSE 'FAIL'
  END AS status
FROM dx_catalog_entities e
  JOIN dx_catalog_entity_owners o ON e.id = o.entity_id
WHERE e.identifier = $entity_identifier;
```

#### Tier defined

This check relies on tables related to entity properties. `dx_catalog_properties` looks up the "Service Tier" property definition, then `dx_catalog_entity_properties` looks up how the entity has configured its instance of that property - specifically, whether or not the entity has a value assigned to the Service Tier property.

```sql
SELECT CASE
    WHEN COUNT(*) > 0 THEN 'PASS'
    ELSE 'FAIL'
  END AS status
FROM dx_catalog_entities e
  JOIN dx_catalog_entity_properties ep ON e.id = ep.entity_id
  JOIN dx_catalog_properties p ON p.id = ep.property_id
WHERE e.identifier = $entity_identifier
  AND p.identifier = 'Service Tier';
```

This pattern can be followed for any type of property in the catalog, including `computed` properties, which receive their values based on their own SQL queries.

#### < 3 open bugs

This check relies on the `issues` table to get issues out of your organization's issue tracker. When you connected your source code management tool to DX, an `issues` table will automatically be created that stores all issues per repository.

This implementation of the check expects the entity identifier to be directly in the title of the issue. You could choose to associate between issues and entities through other means like categories or tagging as well.

```sql
SELECT CASE
    WHEN (COUNT(*) < 3) THEN 'PASS'
    ELSE 'FAIL'
  END AS status
FROM issues
WHERE title ILIKE CONCAT('%', $entity_identifier, '%')
  AND title ILIKE '%bug%'
  AND completed IS NULL;
```

## Best practices

### General best practices

#### Name your `status` column

If a check query does not contain a status column, the Scorecards engine will attempt to parse a status out of the first column in the result set. Naming the column makes the query's intent more clear and understandable.

#### Use SQL `CASE` statements

Check queries will need to return a `status` with one of the correct check status values.

```sql
SELECT CASE
    WHEN (COUNT(*) < 3) THEN 'PASS'
    ELSE 'FAIL'
  END AS status
FROM issues
WHERE title ILIKE CONCAT('%', $entity_identifier, '%')
  AND title ILIKE '%bug%'
  AND completed IS NULL;
```

Using the CASE statement here lets us convert from a boolean value (`COUNT(*) < 3` will be either `TRUE` or `FALSE`) to the string value for the status we want.

#### Always return a row, even in "no data" cases

When the Scorecards engine receives zero rows, it considers it a **query error**. This will show up as "not passed" for an entity just like if a `'FAIL'` status is returned, but will also show an error message when service owners go to inspect the failure. Other query errors include:

- Syntax error in the query
- No `status` column returned
- Incorrect `status` value
- etc.

To avoid confusion from service owners, make sure to always return at least one row.

#### Return extra columns when helpful

Additional columns beyond just `status` and `output` can also be returned. The values from these extra columns are _not_ visible in the Catalog or Scorecards UI, but they can be accessed by running the check queries manually in Data Studio.

This can be useful when a service owner needs more context on why their service is failing a check. For example, if you are inspecting a JSON payload from a `json` entity property or from a custom data item, you might choose to include the full payload in a `raw_json` column.

### Tips for more complex queries

#### Document your check queries with comments

Check queries are going to be read by you and other platform team members, service owners, and _your future self_ - documenting these makes that easier for everyone.

#### Use SQL `WITH` queries to split the work into explicit steps

These are also known as Common Table Expressions or CTEs. See [Postgres documentation](https://www.postgresql.org/docs/current/queries-with.html) for more details on how they can be used.

Writing the check in this way rather than with deeply nested SELECT statements allows the check to be read top-to-bottom rather than inside-out. It makes the work steps more explicit so it is easier for your team to come back to the check and make changes in the future.

```sql
-- Bad: deeply-nested `SELECT` statements make this complex query hard to reason about
SELECT * FROM (
  SELECT * FROM (
    SELECT * FROM ...
  )
)

-- Good: CTEs make this complex query read top-to-bottom
WITH step_one AS (
  SELECT * FROM ...
),
step_two AS (
  SELECT * FROM ...
)
SELECT * FROM ...
```

### Example of a complex check query

The following check query is for a check named "Standard SLO: Uptime > 99.9%". It assumes a [Custom table](https://docs.getdx.com/custom-tables/) named `custom.standard_slos` with columns: `entity_identifier`, `type` (e.g. `uptime`, `latency`, `success_rate`), and `value` (float).

```sql
-- Step 1: Fetch the SLO data for this entity, if it exists
WITH entity_slo AS (
  SELECT value AS uptime
  FROM custom.standard_slos
  WHERE entity_identifier = $entity_identifier
    AND type = 'uptime'
),
-- Step 2: Normalize to a single uptime value
check_data AS (
  SELECT COALESCE((SELECT uptime FROM entity_slo), 0) AS uptime
)
-- Step 3: Return the check results based on the uptime value
SELECT
  CASE
    WHEN uptime >= 99.99 THEN 'PASS'
    WHEN uptime >= 99.9 THEN 'WARN'
    ELSE 'FAIL'
  END AS status,
  uptime AS output
FROM check_data;
```

Let's break down the logic:

- **Step 1: Fetch the SLO data for this entity, if it exists**
  - The `entity_slo` CTE reads the row from `custom.standard_slos` for the current entity where `type = 'uptime'`, and uses the `value` column as the uptime percentage.
- **Step 2: Normalize to a single uptime value**
  - The `check_data` CTE takes that uptime and defaults it to `0` with `COALESCE` when no row exists (meaning the entity is not yet reporting this SLO), ensuring exactly one row for the final step.
- **Step 3: Return the check results based on the uptime value**
  - Returns `status` using a `CASE` statement to produce `'PASS'`, `'WARN'`, or `'FAIL'` based on the uptime percentage, and returns uptime as the `output` column so it can be shown in the UI.

## Reference

### Data sources

As noted above, check queries run against your organization's DX Data Cloud (DXDC) instance, meaning they can leverage any of that data. The table below outlines the various data sources and their example tables that can be accesssed from DXDC.

| Data source | Description                                                                                                                                    | Example tables                                                                        |
| ----------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------- |
| Connectors  | Third-party data sources representing important parts of the software development lifecycle.                                                   | `github_pulls`<br>`jira_issues`<br>`pagerduty_incidents`                              |
| DX catalog  | Details about each entity: its core attributes, owners, properties, etc.                                                                       | `dx_catalog_entities`<br>`dx_catalog_entity_owners`<br>`dx_catalog_entity_properties` |
| Custom data | Data sent to DXDC by your own scripts and automated processes, like during GitHub Actions steps. See the Custom data section for more details. | `custom.standard_slos`<br>`custom.security_cve_results`<br>`custom.internal_tool_xyz` |

The complete list of available tables can be seen in the check query editor.

### Custom data

You can load your own data into the DX Data Cloud using [Custom tables](https://docs.getdx.com/custom-tables/). Create tables and indexes in the `custom` schema (e.g. `custom.my_data`) to use them in check queries. Use custom tables when you need to join internal or script-generated data with connector and catalog data.

### Example check query: Repo is not affected by CVE-12345

This check assumes the Security team maintains a normalized table in the custom schema that lists which entities are affected by each CVE. Table and example data:

```sql
CREATE TABLE custom.cve_affected_repos (
  cve_id             TEXT NOT NULL,
  entity_identifier  TEXT NOT NULL,
  PRIMARY KEY (cve_id, entity_identifier)
);

INSERT INTO custom.cve_affected_repos (cve_id, entity_identifier) VALUES
  ('cve_12345', 'billing'),
  ('cve_12345', 'messaging'),
  ('cve_12345', 'weekly-email-digest');
```

The check query reads from that table:

```sql
SELECT CASE
    WHEN COUNT(*) = 0 THEN 'PASS'
    ELSE 'FAIL'
  END AS status
FROM custom.cve_affected_repos
WHERE cve_id = 'cve_12345'
  AND entity_identifier = $entity_identifier
```

### Query result structure

The final value of the check is determined by the results of the _check query_.

#### Number of result rows

- If no rows are returned, the check's status is assumed to be `'FAIL'`.
- If multiple rows are returned, only the first row will be evaluated, the others will be ignored.

#### The `status` column

This is the most important part of the query: whether an entity passes or fails this check.

Possible values:

- `'PASS'`: The entity passed this check.
- `'WARN'`: The entity passed this check, but there are warnings. The entity is at risk of failing in the near future unless the owners take action.
- `'FAIL'`: The entity failed this check.

#### The `output` column

This is an optional column that makes it possible to display the output value of a check to viewers across the platform, in addition to the status of the check (pass/fail/warn). Output values can be a string or a number, and any value can be `NULL`.

To use output values, toggle the "display check value" switch, then select an output type from the dropdown. Then edit the check query to include an `output` column in the result set.

This feature has strict validation to make sure that accurate data is shown to service owners:

- The data type of the `output` column must match the data type of "output type" dropdown or the check will fail. Call the SQL `CAST` function to switch between types if needed.
  - Example: If "string" is selected but the numeric value `2` is returned, the check will fail.
  - Example: If "percentage" is selected but the _string_ value `'99.9'` is returned, the check will fail.
- The `output` column must be included if outputs are enabled, or the check will fail.
- The `output` column must _not_ be included if outputs are not enabled, or the check will fail.

#### The `message` column

This is an optional column that allows a dynamic message to be saved as part of the check result. This can be used to present tailored content to viewers using custom logic. If a message is returned it will be displayed alongside task information across the platform. Markdown syntax is supported for advanced formatting.

There are a few options available for defining message content. One is to simply have it inline in your check query. For short messages this may be sufficient. You can define the message as a multi-line string, or use [escape characters](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE). Another option would be to store the messages as [custom data records](https://docs.getdx.com/datacloudapi/methods/?group=customData) and then retrieve them in your check query, decoupling the message content from your check logic.

Here is an example check query:

```sql
SELECT 'FAIL' AS status,
  E'# Hello world\n\n## Second header\n\nLorem ipsum dolor sit amet, consectetur adipiscing elit.\n\nHere is a [link](https://example.com).\n\n- One\n- Two\n- Three' AS message
```

#### The `related_properties` column

This optional column lets you, as the check author, enable users to easily update a property required for the check to pass.

`related_properties` can be a single property identifier, or a comma separated list of multiple. i.e. `service-tier, language, uptime-slo`. However, each property identifier in `related_properties` must match the `identifier` of a property in your catalog.

Here's an example check query that sets `Service Tier` as a related property:

```sql
SELECT CASE
    WHEN count(*) > 0 THEN 'PASS'
    ELSE 'FAIL'
  END AS status,
  'service-tier' AS related_properties
FROM dx_catalog_entities e
  JOIN dx_catalog_entity_properties ep ON e.id = ep.entity_id
  JOIN dx_catalog_properties p ON p.id = ep.property_id
WHERE e.identifier = $entity_identifier
  AND p.identifier = 'service-tier';
```

In the UI, users will now be able to edit the Service Tier upon seeing that the check did not pass:

![Related properties UI example](https://docs.getdx.com/assets/images/scorecards/related_properties_ui.png)

#### The `metadata` column

This is an optional column that details relevant metadata to a check result.

The `metadata` field must be defined as a JSON object. It can have a maximum of 20 keys. The values can be strings, numbers, booleans, or null.

Here's an example check query that adds the `tier` as a piece of metadata:

```sql
SELECT CASE
    WHEN count(ep.id) > 0 THEN 'PASS'
    ELSE 'FAIL'
  END AS status,
  jsonb_build_object('tier', ep.value) AS metadata
FROM dx_catalog_entities e
  JOIN dx_catalog_entity_properties ep ON e.id = ep.entity_id
  JOIN dx_catalog_properties p ON p.id = ep.property_id
WHERE e.identifier = $entity_identifier
  AND p.identifier = 'service-tier'
  GROUP BY ep.id;
```

In the UI, this appears in the check details view:
![Metadata UI example](https://docs.getdx.com/assets/images/scorecards/check-metadata.png)

### Query variables

These entity-related values are interpolated into the check query each time it runs for a given entity.

One variable is available by default:

- `$entity_identifier`: The entity's machine-readable name, like `authentication-service`. It can be set through the "Identifier" form field in the catalog or through the API.

Additional variables become available when an entity has aliases. For example, if an entity has an alias for a Github repo, these variables can be used:

- `$entity_github_repo_ids`: Unique identifiers of the entity Github Repo aliases.
- `$entity_github_repo_names`: Names of the entity Github Repo aliases.

The full list of available variables can be found in the tooltip to the right of the "Query" form field and in auto-complete while editing the check query.
---

## Sitemap

[Overview of all docs pages](/llms.txt)