> For the complete documentation index, see [llms.txt](https://docs.decube.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.decube.io/data-quality-v2/monitor-types/custom-sql.md).

# Custom SQL

Custom SQL runs a query you write yourself, and checks how many rows it returns against a tolerance.

This is the most flexible monitor type — it can check anything expressible in SQL, including things that span multiple tables, unlike every other monitor type, which checks a single table or column.

{% embed url="<https://youtu.be/soS4jRlP4qY>" %}

### When to use it <a href="#when-to-use-it" id="when-to-use-it"></a>

A business expects every order in an `orders` table to have a matching customer in a `customers` table. A Custom SQL check runs a query that returns any order missing its customer — for example, a left join with a null check — and expects to see zero rows returned.

### What's configurable <a href="#whats-configurable" id="whats-configurable"></a>

* **Schedule** — fixed recurring, or Manual. Custom SQL supports Manual scheduling with no extra restriction, since it doesn't use a scan method.
* **SQL Query** — the query defining what counts as a failing row. Decube wraps this query in a count, so the number of rows it returns becomes the failing row count.
* **Search Space Query** (optional) — a second query defining the total population being checked — for example, "all orders" — to give the failing count context as a percentage. Providing this is what makes a percentage-based tolerance possible; without it, only an absolute row-count tolerance is available.
* **Tolerance** — Absolute (a fixed number of failing rows allowed) is always available; Percentage (a share of the scanned population) is only available if you provide a Search Space Query.
* **Asset Association** — you can optionally link the monitor to one or more assets (tables), even though the SQL itself runs against the data source rather than a single table. This is what makes the monitor show up on those assets' Asset Details pages, and links any case the monitor generates back to those assets. If you don't associate any assets, the monitor falls back to being associated with the data source as a whole.

### Required validation step: Test Value <a href="#required-validation-step-test-value" id="required-validation-step-test-value"></a>

Before you can create a Custom SQL monitor, you must run **Test Value** — this executes the SQL immediately and returns the number of rows it produces. It's a required check, and it serves two purposes: it confirms to the system that the SQL you've written is valid, and it gives you a way to sanity-check the result against what you'd expect if you ran the same query directly against the warehouse — so you can catch a logic mistake in the query before you commit to creating the monitor. For data sensitivity reasons, Test Value only shows the row count, not a preview of the actual row data.

You can also re-run Test Value later from an existing monitor as a spot-check, independent of the monitor's regular schedule — this doesn't count as a scheduled or manual run and doesn't affect run history, incidents, or the DQ score.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2FuMc1tkrMuw6WW4Tp6Ify%2Fimage-20260812-023812.png?alt=media&amp;token=34cedbae-ff64-49eb-8037-5721dde45fd3" alt=""><figcaption></figcaption></figure>

### Worked example — absolute tolerance, no search space <a href="#worked-example-absolute-tolerance-no-search-space" id="worked-example-absolute-tolerance-no-search-space"></a>

* SQL Query:

```
SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id WHERE c.id IS NULL
```

* Search Space Query: none
* Tolerance: Absolute — 0

**Flow:** you first run Test Value to confirm the query executes and returns the expected kind of result (orphaned orders), then set the tolerance and save.

**Resulting rule:** "An incident will be created when any row fails." (Here, any orphaned order found is a failing row.)

### Worked example — percentage tolerance, with search space <a href="#worked-example-percentage-tolerance-with-search-space" id="worked-example-percentage-tolerance-with-search-space"></a>

* SQL Query: same as above (orphaned orders)
* Search Space Query: `SELECT * FROM orders`
* Tolerance: Percentage — 1%

**Resulting rule:** "An incident will be created when more than 1.0% of the scanned rows fail."

### Notes <a href="#notes" id="notes"></a>

* Almost any configuration change to a Custom SQL monitor — the query, the search space query, the asset associations — is destructive and creates a new version. Adjusting only the tolerance value is not.
* You can change asset association later from the monitor details page, without needing to go through the full monitor configuration flow.