Learn how to create custom SQL validators for data warehouse sources to validate your business logic with flexible SQL queries. For example, create a custom SQL validator to calculate an exponential moving average of a field over time, compare fields or records between tables, apply custom filters to your data, and more.

Configuring a Custom SQL Validator

To configure a Custom SQL Validator,

1. Navigate to the specific source where you want to add the validator.
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 o 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 and Generating the SQL Query.
   4. (Recommended) Test your SQL query and automatically fix any errors. See Query Testing and Validation.
   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.
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.

Writing the SQL Query

1. Write a valid SQL query to define the metric calculation and return a single numeric value.

   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

   For query syntax and examples, see Custom SQL Validator Query Syntax.

2. (Recommended) Test the SQL query and Fix errors if the test fails. See Query Testing and Validation.

3. Continue with your validator con

Generating the SQL Query

❗️

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.

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

1. 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
   4. For more examples, refer to Custom SQL Validator Recipes.
2. Select an LLM Credential, which is configured for a AI agent and model, to generate the SQL query for you.
3. (Recommended) Test the SQL query and Fix errors if the test fails. See Query Testing and Validation.
4. Continue with your Validator configuration.

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. 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.

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

❗️

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.

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.