> ## Documentation Index
> Fetch the complete documentation index at: https://docs.elementary-data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Singular Tests

Singular tests in dbt are custom SQL tests that allow you to write specific queries to validate your data. Unlike generic tests (like `not_null` or `unique`) that can be applied to multiple models, singular tests are one-off tests written as SQL queries in `.sql` files within your `tests/` directory.

## What are Singular Tests?

Singular tests are dbt tests defined as standalone SQL files. They are executed when you run `dbt test`, and they pass when the query returns zero rows, or fail when the query returns any rows. This makes them perfect for testing complex business logic, relationships between multiple tables, or custom validation rules that don't fit into standard generic tests.

### How Singular Tests Work

A singular test is simply a SQL query that should return no rows if the test passes. For example:

```sql theme={null}
-- tests/assert_no_null_orders.sql
select *
from {{ ref('orders') }}
where status is null
```

This test will fail if there are any orders with a null status, and pass if all orders have a status value.

## Test shows no assets linked to this test

If a test in Elementary Cloud shows **No assets linked to this test**, Elementary couldn't work out which table the test belongs to. The test still runs and its results are still collected, but without a linked asset:

* Its incidents don't show on any asset's page, lineage, or catalog entry
* It doesn't inherit owners or tags from a model, so owner-based alert routing can't reach anyone

### Why it happens

When Elementary uploads your dbt artifacts, it picks the test's primary asset in this order:

1. The `override_primary_test_model_id` setting, if the test has one (see below).
2. The test's parent, if the test depends on **exactly one** model, source, or snapshot in your project.
3. For generic tests, the `model` argument the test was applied to.

Singular tests have no `model` argument, so they are only linked automatically when they reference exactly one node. A singular test ends up with no asset when:

* It references **two or more** models or sources (a join, or a comparison between tables)
* It references **nothing** in your project (for example, it only queries tables by their hardcoded name, or only refs models from another package)

For example, this test joins two tables, so Elementary can't tell whether a failure is about `orders` or `payments`:

```sql theme={null}
select * 
from {{ ref('orders') }} 
join {{ ref('payments') }} 
where status is null
```

### How to fix it: set the primary model

Tell Elementary which asset the test is about with `override_primary_test_model_id`. You can set it in the test's `config`:

```sql theme={null}
{{ 
    config( 
        severity='error', 
        override_primary_test_model_id='model.jaffle_shop_online.orders' 
    ) 
}}

select * 
from {{ ref('orders') }} 
join {{ ref('payments') }} 
where status is null
```

Or in its `meta`:

```sql theme={null}
{{ config(meta={'override_primary_test_model_id': 'model.jaffle_shop_online.orders'}) }}
```

The value is the dbt **unique\_id** of the node, not its name. It has the form `<resource_type>.<project_name>.<name>`, for example `model.jaffle_shop_online.orders` or `source.jaffle_shop_online.raw.payments`. To look it up:

```sql theme={null}
select unique_id
from <elementary_schema>.dbt_models
where name = '<model_name>'
```

To avoid hardcoding the project name (useful when the same test is shared across projects), build the id from dbt's `project_name`:

```sql theme={null}
{{ config(override_primary_test_model_id='model.' ~ project_name ~ '.orders') }}
```

Pick the asset that the failure is actually about. For a test that compares an upstream table to a downstream one, that is usually the downstream table, since that is where the bad data ends up.

The link appears in Elementary Cloud after the next run uploads your dbt artifacts. You can confirm it by checking that `parent_model_unique_id` is set for the test in `<elementary_schema>.dbt_tests`.

> [!NOTE]
>
> This setting works for any dbt test, not only singular tests. It's also the fix for a generic test that shows no asset, for example a custom generic test that references several models.

### Ownership behavior

When you set `override_primary_test_model_id`, Elementary uses the owner from the specified primary model. In the example above, even though both `orders` and `payments` are in the query, Elementary uses the owner of `orders`, as long as the test doesn't set its own owner in `meta`.

This ensures that:

* Test incidents are properly attributed to the correct asset
* Alerts are routed to the right owners
* The test appears in the correct asset's lineage and catalog page

## Creating Singular Tests in Elementary Cloud

In Elementary Cloud, you can add singular tests through the visual interface.

> [!NOTE]
>
> This workflow creates a test in your dbt project and runs it with your dbt jobs. To run SQL on an Elementary-managed schedule without creating dbt code, use a [custom SQL cloud test](https://docs.elementary-data.com/cloud/features/data-tests/custom-sql-tests.md).

The UI guides you through:

1. Writing your SQL query
2. Configuring the primary model (if your query involves multiple tables)
3. Setting test metadata like name, description, severity, tags, and owners

![Singular test configuration in Elementary Cloud UI](https://res.cloudinary.com/do5hrgokq/image/upload/v1765889347/image_30_qivykc.png)

The UI automatically handles the conversion of your test into a proper dbt singular test file, including the `override_primary_test_model_id` configuration when needed.

## Best Practices

* **Use descriptive names** — Name your singular test files clearly to indicate what they're testing (e.g., `assert_orders_have_valid_payments.sql`)
* **Set the primary model** — Always use `override_primary_test_model_id` when your query involves multiple tables to ensure proper incident attribution
* **Add descriptions** — Include comments in your SQL explaining the business logic being tested
* **Configure severity** — Use `severity='error'` for critical tests and `severity='warn'` for non-critical validations
* **Link to assets** — When possible, configure the primary model to link the test to the most relevant asset for better visibility


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.