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

# Custom SQL Validators

> Use Custom SQL Validators in Validio for bespoke data quality checks not covered by the out-of-the-box validator library.

Custom SQL Validators are the bespoke option for data quality checks where the metric itself isn't expressible as one of Validio's [out-of-the-box validators](https://docs.validio.io/v11.1/docs/validators/validator-types.md). Use them to calculate exponential moving averages over time, compare fields or records between tables, apply custom filters, and define any metric you can write in SQL.

## When to use a Custom SQL validator

Most data quality rules in Validio are covered by the [extensive library of out-of-the-box validators](https://docs.validio.io/v11.1/docs/validators/validator-types.md). Reach for a Custom SQL Validator when the metric calculation itself needs bespoke logic — for example:

* Bespoke metric calculations such as exponentially weighted moving averages or custom ratios.
* Cross-table comparisons where the metric is the comparison itself (for example, the percentage difference in row counts between two tables).
* Business-specific checks that encode a rule involving multiple fields or conditions.

If you only need to reshape, join, or enrich your data so an out-of-the-box validator can be applied, use a [Custom SQL Source](https://docs.validio.io/v11.1/docs/sources/custom-sql-sources.md) instead. See [Choosing Between a SQL Source and a Custom SQL Validator](https://docs.validio.io/v11.1/docs/sources/custom-sql-sources.md#choosing-between-a-sql-source-and-a-custom-sql-validator) for guidance.

## Configuring a Custom SQL validator

To configure a Custom SQL Validator,

1. Navigate to the specific source where you want to add the validator. If you need to reshape data before validation, consider configuring a [Custom SQL Source](https://docs.validio.io/v11.1/docs/sources/custom-sql-sources.md) first.
2. Click **+ New validator** to start the configuration wizard.
3. Under **Validator Type**, select **Custom SQL**.
4. Under **Metric**,
   1. Select a **Window** or create a new window to specify the amount of data or time over which the validator operates.
   2. Select a **Segmentation** or create a new segmentation to break your data into separate groups for analysis.
   3. **Define the metric calculation** by writing a SQL query or describing what you want and let AI generate it. The query should return a single numeric value. See [Writing the SQL Query](#writing-the-sql-query) and [Generating the SQL Query](#generating-the-sql-query).
   4. (Recommended) Test your SQL query and automatically fix any errors. See [Query Testing and Validation](#query-testing-and-validation) and [Fixing Errors](#fixing-errors) .
   5. Check **Initialize using historic data** to backfill with historical data for validation.
5. Under **Thresholds**, select the type of threshold to use to detect incidents. You can configure a Fixed threshold, Dynamic threshold, or a Difference threshold. See [About Thresholds](https://docs.validio.io/v11.1/docs/thresholds.md).
6. Under **Validator Details**, enter a Name, Description, Tags, and Owner.
7. Click **Continue** to create the Custom SQL Validator.

For examples of Custom SQL validator configurations, refer to [Custom SQL Validator Recipes](https://docs.validio.io/v11.1/docs/validators/validator-types/custom-sql/custom-sql-validator-recipes.md).

### Writing the SQL query

Write a valid SQL query in the text field. The query should define the metric calculation and return a single numeric value.

![The Custom SQL query text field in the validator configuration wizard](https://mintcdn.com/validio/36lh-6_uXUsulPvT/images/docs/validators/730-custom-sql-query-field.png?fit=max\&auto=format\&n=36lh-6_uXUsulPvT\&q=85\&s=f1335e53c11156323e432185fe7a230b)

Custom SQL query field

A valid SQL query may include mandatory fields, segmentation fields, and template functions depending on the window type.

* **Tumbling windows**: Use template functions and return both `validio_metric` and `validio_window_id`
* **Global windows**: Use regular SQL syntax and return `validio_metric`

Return these fields in lower case. On sources that convert unquoted names to upper case, such as Snowflake, Oracle, and IBM Db2, put the alias in double quotes, for example `AS "validio_metric"`.

For query syntax and examples, see [Custom SQL Validator Query Syntax](https://docs.validio.io/v11.1/docs/validators/validator-types/custom-sql/sql-validator-query-syntax.md).

### Generating the SQL query

> [!WARNING]
>
> You will only be able to use the **Generate SQL** feature if your workspace settings allow **LLM provider credentials** and you have at least one credential configured. See [LLM Credentials](https://docs.validio.io/v11.1/docs/integrations/llm-credentials.md).

When you select **Generate SQL**, you can leverage AI agents to compose the SQL query for you.

![The LLM credential picker shown when generating a Custom SQL validator query](https://mintcdn.com/validio/36lh-6_uXUsulPvT/images/docs/validators/730-sql-generation-llm-credential.png?fit=max\&auto=format\&n=36lh-6_uXUsulPvT\&q=85\&s=ce41a64bf9f648233749365a2b0beb4f)

Selecting an LLM credential to generate the SQL query

1. Use the text field to **Describe the metric you want to calculate**. Some examples of metric calculation descriptions are,
   1. Exponential moving average of quantity
   2. Sum of total sales per day per customer group
   3. For each city, calculate the ratio of the average total sales today against the average from two previous days. For more examples, refer to [Custom SQL Validator Recipes](https://docs.validio.io/v11.1/docs/validators/validator-types/custom-sql/custom-sql-validator-recipes.md).
2. Select an **LLM Credential**, which is configured for a AI agent and model, to generate the SQL query for you.

### Query testing and validation

Because syntax errors are common in custom SQL, we recommend always testing the query so that you can discover and correct errors early, instead of during source polling.

![The LLM credential picker shown when fixing errors in a Custom SQL validator query](https://mintcdn.com/validio/36lh-6_uXUsulPvT/images/docs/validators/730-fix-errors-llm-credential.png?fit=max\&auto=format\&n=36lh-6_uXUsulPvT\&q=85\&s=70a5035f8b7048bd6f1cc2ea9dfa5c81)

Selecting an LLM credential to fix query errors

You have two options for testing the SQL query:

* **Run**: (Recommended)
  * If the query executes properly, returns a table with the first 20 records.
  * If the query did not execute properly, returns an error message from the data source.
* **Dry run**: Validate the SQL query structure without running it. Dry run is not supported for [Progress OpenEdge](https://docs.validio.io/v11.1/docs/integrations/transactional-databases/progress-openedge.md), [IBM Db2](https://docs.validio.io/v11.1/docs/integrations/transactional-databases/ibm-db2.md), or [Azure Synapse](https://docs.validio.io/v11.1/docs/integrations/data-warehouses/azure-synapse.md) serverless SQL pools; use **Run** to test the query on these sources. All other data warehouses and databases, including Azure Synapse dedicated SQL pools, support Dry run.

### Fixing errors

> [!WARNING]
>
> You will only be able to use the **Fix errors** feature if your workspace settings allow **LLM provider credentials** and you have at least one credential configured. See [LLM Credentials](https://docs.validio.io/v11.1/docs/integrations/llm-credentials.md).

If the SQL query test fails, you can use **Fix errors** to analyze your query and suggest corrections.

1. Click **Fix errors**
2. Select an **LLM credential** to use to analyze and fix your query.
3. (Recommended) Test the query again to ensure that there are no more errors.
4. Click **Apply** to accept the changes.