SQL query

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.

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

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:

[
  {
    "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"
  }
]

[
  {
    "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"
  }
]

Special characters ratio

SQL query return rows

⌘I

Get started

Workspace setup

Governance

Observability

Offline testing

Tests

Gateway

Data quality monitoring

Administration

Notifications

Other resources

Definition

Taxonomy

Why it matters

Test configuration examples

​Definition

​Taxonomy

​Why it matters

​Test configuration examples

Definition

Taxonomy

Why it matters

Test configuration examples