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

# SQL query

> Learn how to use the SQL query test to create custom data validation tests

## Definition

The SQL query test allows you to write custom SQL queries to validate your data and set
thresholds on the query results. This test executes a user-defined SQL query that
returns a numerical result, which can then be compared against specified thresholds.

The SQL query must reference your dataset as `df` (the table name) and should return a single numerical value.

> [!NOTE]
>
> Inside `df`, columns use their **canonical platform names**, not the raw
> column names from your dataset config. In particular, the model output column
> is `openlayer_output` (not the `outputColumnName` you declared), and the
> system columns are `openlayer_latency`, `openlayer_num_of_tokens`, and
> `openlayer_cost`. A query like `SELECT COUNT(*) FROM df WHERE output IS NULL`
> errors with `"output" not found in FROM clause` — use `openlayer_output`. Your
> own feature columns keep the names you gave them.

## Taxonomy

* **Task types**: LLM, tabular classification, tabular regression, text classification.
* **Availability**: development (Continuously evaluate your models and datasets as you iterate on their versions.)
  and monitoring (Monitor a model in production, measure its health, check for drifts and set up alerts.).

## Why it matters

* The SQL query test provides flexibility for creating custom data validation
  rules tailored to your specific use case.
* You can implement complex business logic and data quality checks that
  aren't covered by standard tests.
* This enables domain-specific validation rules, such as checking data consistency
  across multiple columns, validating ranges, or ensuring specific business constraints are met.
* Custom SQL queries allow you to leverage the full power of SQL for data analysis
  and validation within your testing pipeline.

## Test configuration examples

If you are writing a `tests.json`, here are a few valid configurations for the SQL query test:

```json Development theme={null}
[
  {
    "name": "Minimum record count check",
    "description": "Ensures the dataset has at least 1000 records",
    "type": "integrity",
    "subtype": "sqlQuery",
    "thresholds": [
      {
        "insightName": "sqlQuery",
        "insightParameters": [
          { "name": "query", "value": "SELECT COUNT(*) FROM df" }
        ],
        "measurement": "result",
        "operator": ">=",
        "value": 1000
      }
    ],
    "subpopulationFilters": null,
    "mode": "development",
    "usesValidationDataset": true,
    "usesTrainingDataset": false,
    "usesMlModel": false,
    "syncId": "b4dee7dc-4f15-48ca-a282-63e2c04e0689"
  },
  {
    "name": "Age range validation",
    "description": "Checks that no records have age values outside the expected range (18-100)",
    "type": "integrity",
    "subtype": "sqlQuery",
    "thresholds": [
      {
        "insightName": "sqlQuery",
        "insightParameters": [
          { "name": "query", "value": "SELECT COUNT(*) FROM df WHERE age < 18 OR age > 100" }
        ],
        "measurement": "result",
        "operator": "<=",
        "value": 0
      }
    ],
    "subpopulationFilters": null,
    "mode": "development",
    "usesValidationDataset": true,
    "usesTrainingDataset": false,
    "usesMlModel": false,
    "syncId": "96622fba-ea00-4e42-8f42-5e8f5f60805f"
  }
]
```

```json Monitoring theme={null}
[
  {
    "name": "Revenue consistency check",
    "description": "Validates that average transaction amount is within expected range",
    "type": "integrity",
    "subtype": "sqlQuery",
    "thresholds": [
      {
        "insightName": "sqlQuery",
        "insightParameters": [
          { "name": "query", "value": "SELECT AVG(transaction_amount) FROM df" }
        ],
        "measurement": "result",
        "operator": ">=",
        "value": 50.0
      }
    ],
    "subpopulationFilters": null,
    "mode": "monitoring",
    "usesProductionData": true,
    "evaluationWindow": 3600,
    "delayWindow": 0,
    "syncId": "b4dee7dc-4f15-48ca-a282-63e2c04e0689"
  },
  {
    "name": "Data completeness check",
    "description": "Ensures that critical fields are not null for more than 5% of records",
    "type": "integrity",
    "subtype": "sqlQuery",
    "thresholds": [
      {
        "insightName": "sqlQuery",
        "insightParameters": [
          {
            "name": "query",
            "value": "SELECT (COUNT(*) - COUNT(customer_id)) * 100.0 / COUNT(*) FROM df"
          }
        ],
        "measurement": "result",
        "operator": "<=",
        "value": 5.0
      }
    ],
    "subpopulationFilters": null,
    "mode": "monitoring",
    "usesProductionData": true,
    "evaluationWindow": 3600,
    "delayWindow": 0,
    "syncId": "96622fba-ea00-4e42-8f42-5e8f5f60805f"
  }
]
```


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