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

# User Guide

> Step-by-step guide to set up Custom SQL sources with warehouse AI functions for MDM and unstructured data validation.

Complete the following steps to use Custom SQL Sources with warehouse AI functions for MDM and unstructured data validation:

1. Enable AI (LLM) Functions in Your Warehouse
2. Create a Custom SQL Source
3. Create Validators on your Custom SQL Source

This guide contains examples for using AI functions (LLM) in different warehouses. Make sure you adapt the examples to match your data schemas and use cases.

## Enable AI functions in your warehouse

### BigQuery

BigQuery provides `ML.GENERATE_TEXT` and `AI.GENERATE` functions for text generation and analysis.

#### Prerequisites

1. Enable the Vertex AI API in your Google Cloud project
2. Create a BigQuery ML remote model pointing to a Gemini model:

   ```sql title="SQL (BigQuery)" theme={null}
   CREATE OR REPLACE MODEL `project.dataset.gemini_model`
   REMOTE WITH CONNECTION `us.my_connection`
   OPTIONS (ENDPOINT = 'gemini-2.5-flash');
   ```
3. Grant the connection service account Vertex AI User role:
   1. Go to your connection details to find the service account
   2. Grant `roles/aiplatform.user` on your project

#### Documentation

Refer to the following documentation for more information:

* [ML.GENERATE\_TEXT Function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-text)
* [Generate Text Tutorial](https://cloud.google.com/bigquery/docs/generate-text-tutorial-gemini)
* [Generative AI Overview](https://cloud.google.com/bigquery/docs/generative-ai-overview)

### Databricks

Databricks provides `ai_query()` and task-specific AI functions.

#### Prerequisites

1. Workspace in a supported Model Serving region
2. AI functions are enabled by default for Foundation Model APIs
3. For external models: Enable "AI\_Query for Custom Models and External Models" in Databricks Previews UI

#### Available models

* Databricks-hosted: `databricks-meta-llama-3-3-70b-instruct`, `databricks-mixtral-8x7b-instruct`, and more
* Task-specific functions: `ai_analyze_sentiment()`, `ai_classify()`, `ai_extract()`, `ai_summarize()`

#### Documentation

Refer to the following documentation for more information:

* [AI Functions Overview](https://docs.databricks.com/en/large-language-models/ai-functions.html)
* [ai\_query Function Reference](https://docs.databricks.com/en/sql/language-manual/functions/ai_query.html)

### Snowflake

Snowflake Cortex provides `COMPLETE` and task-specific AI functions via Snowflake Cortex AISQL.

#### Prerequisites

1. CORTEX\_USER database role (granted to PUBLIC by default)
2. Snowflake account in a supported region
3. Grant access (if needed)

   ```sql title="SQL (Snowflake)" theme={null}
   -- Grant to specific role
   GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE my_role;
   ```

#### Available models

* Snowflake Arctic, Meta Llama 3 (8B/70B), Mistral Large, Mixtral-8x7b, Reka Core, and more
* Task-specific: `CLASSIFY_TEXT()`, `EXTRACT_ANSWER()`, `SENTIMENT()`, `SUMMARIZE()`, `TRANSLATE()`

#### Documentation

Refer to the following documentation for more information:

* [Snowflake Cortex LLM Functions](https://docs.snowflake.com/en/user-guide/snowflake-cortex/llm-functions)
* [COMPLETE Function](https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex)

## Create a Custom SQL source

Create your source in Validio using Custom SQL instead of creating a Custom SQL Validator with LLM function calls. This allows you to define the LLM output as a field on the source, which will make it easier to apply thresholds and debug incidents.

Additional benefits of using a Custom SQL Source include:

* Test and debug AI queries directly in the source configuration
* Iterate on prompt engineering independent of Validators configuration
* Better error messages when using the debug query

### Creating the Custom SQL source

In Validio,

1. Navigate to the **Sources** page and click **+ New source**
2. Select your warehouse type: BigQuery, Databricks, or Snowflake
3. Under "Specify the data", choose **Use Custom SQL**
4. Paste your SQL query with the AI function
5. Set your Polling schedule
6. Click Continue to infer the schema
7. Add Tags, Owner, and create the source

### Example of Custom SQL source definition

```sql title="SQL (Snowflake)" theme={null}
SELECT 
  *,
  SNOWFLAKE.CORTEX.COMPLETE(
    'mistral-large2',
    CONCAT('Is "', country, '" a valid ISO 3166-1 country name? Answer only YES or NO.')
  ) AS country_validation_result
FROM database.schema.table
```

## Create validators on your Custom SQL source

Now create validators on your Custom SQL Source to monitor the AI-validated results. The following validator configuration will count the number of irregular country names per day.

### Example: Count invalid country names

In Validio,

1. Navigate to your Custom SQL source
2. Click **+ New validator**
3. Select validator type: **Volume** → **Count**
4. **Configure**:
   1. **Field:** `country_validation_result` (or your AI result column)
   2. **Filter:** Use String filter → `Field country_validation_result` is exactly `NO`
   3. **Window:** Choose appropriate window (e.g., Daily)
5. Set **Threshold**:
   1. **Fixed Threshold:** `>= 0` to alert on any irregular entries
   2. **Dynamic Threshold:** To detect anomalies in patterns over time
6. Add **Name**, **Description**, and click **Continue**

## Additional examples

### Product category validation

Custom SQL Source:

```sql title="SQL (BigQuery)" theme={null}
SELECT 
  *,
  ML.GENERATE_TEXT(
    MODEL `project.dataset.gemini_model`,
    STRUCT(
      CONCAT('Is "', category, '" a valid product category from this list: [Electronics, Clothing, Home & Garden, Sports, Books]? Answer YES or NO.') AS prompt,
      TRUE AS flatten_json_output
    )
  ).ml_generate_text_result AS category_is_valid
FROM `project.dataset.products`
```

Validator: Count products with invalid categories using a Volume validator with filter `category_is_valid = 'NO'`.

### Product description quality

Custom SQL Source:

```sql title="SQL (Snowflake)" theme={null}
SELECT 
  *,
  CAST(SNOWFLAKE.CORTEX.COMPLETE(
    'mixtral-8x7b',
    CONCAT('Rate this product description quality from 1-5 (5=excellent): "', description, '". Respond with only the number.')
  ) AS INTEGER) AS description_quality_score
FROM products
```

Validator: Count low-quality descriptions using a Volume validator with filter `description_quality_score <= 2`.