Categories:: String & binary functions (AI Functions)

AI_FILTER¶

Classifies free-form prompt inputs into a boolean. Currently supports both text and image filtering.

Region availability¶

The following table shows the regions where you can use the AI_FILTER function for both text and images:

Data type	AWS US West 2 (Oregon)	AWS US East 1 (N. Virginia)	AWS Europe Central 1 (Frankfurt)	AWS Europe West 1 (Ireland)	AWS AP Southeast 2 (Sydney)	AWS AP Northeast 1 (Tokyo)	Azure East US 2 (Virginia)	Azure West Europe (Netherlands)	AWS (Cross-Region)
TEXT	✔	✔	✔	✔	✔	✔	✔	✔	✔
IMAGE	✔	✔	✔						✔

Syntax¶

Applying AI_FILTER to an input string:

AI_FILTER( <input> )

Copy

Applying AI_FILTER to single image:

AI_FILTER( <predicate> , <input> )

Copy

Applying AI_FILTER to multiple columns with both text and images, leveraging the PROMPT function:

AI_FILTER( PROMPT('<template_string>',  <col_1>, … ) )

Copy

Arguments¶

Required:

If you’re specifying an input string:

input: A string containing the text to be classified.

If you’re filtering on one file:

predicate: A string containing the instructions to classify the file input as either TRUE or FALSE.
file: The column that the file is classified by based on the instructions specified in predicate. You can use IMAGE FILE as an input to the AI_FILTER function.

If you’re using the PROMPT() function to format the inputs:

For more complicated prompts, especially with multiple file columns, you can use the PROMPT function to help with creating an input.

The PROMPT() function supports formatting across both strings and FILE datatypes. For detailed usage, see Examples.

Returns¶

Returns a Boolean value that indicates whether the statement evaluates to TRUE or FALSE for the specified text.

Access control requirements¶

Users must use a role that has been granted the SNOWFLAKE.CORTEX_USER database role. See Required privileges for more information on this privilege.

Usage notes¶

For optimal performance, follow these guidelines:

Make sure the columns sent into AI_FILTER don’t contain NULL values.
Use plain text in English for the input string or for PROMPT() arguments.
Provide details for the input text instruction. For example, instead of a statement like “sounds satisfied”, use “In the following support transcript, the customer sounds satisfied”.
Consider phrasing the input in the form of a question. For example, “In the following support transcript, does the customer sound satisfied?”

Examples¶

AI_FILTER: Text¶

Can be called as a simple scalar Boolean function on string constants.

SELECT AI_FILTER('Is Canada in North America?');

Copy

TRUE

You can CONCAT , || instructions with text columns to use this function:

WITH reviews AS (
            SELECT 'Wow... Loved this place.' AS review
  UNION ALL SELECT 'The pizza is not good.'
)
SELECT * FROM reviews
WHERE AI_FILTER(CONCAT('The reviewer enjoyed the restaurant: ', review));

Copy

For easier templated formatting across multiple columns, Snowflake provides PROMPT function; for example:

WITH reviews AS (

SELECT 'Wow... Loved this place.' AS review
UNION ALL SELECT 'The pizza is not good.'
)
SELECT * FROM reviews
WHERE AI_FILTER(PROMPT('The reviewer enjoyed the restaurant: {0}', review));

Copy

+--------------------------+
| REVIEW                   |
|--------------------------+
| Wow... Loved this place. |
+--------------------------+

While evaluating the quality of AI_FILTER, it can be helpful to compare candidate predicates across columns.

WITH country AS (
          SELECT 'Switzerland' AS country,
UNION ALL SELECT 'Korea'
),
region AS (
            SELECT 'Asia' AS region,
  UNION ALL SELECT 'Europe'
)
SELECT country,
      region,
      AI_FILTER(PROMPT('{0} is in {1}', country, region)) AS result
FROM country CROSS JOIN region ;

Copy

+-------------+-------+--------+
| COUNTRY     |REGION | RESULT |
|-------------+-------+--------+
| Switzerland |Europe | TRUE   |
|-------------+-------+--------+
| Switzerland | Asia  | FALSE  |
|-------------+-------+--------+
| Korea       |Europe | FALSE  |
+-------------+-------+--------+
| Korea       | Asia  | TRUE   |
+-------------+-------+--------+

Using AI_FILTER with a JOIN¶

You can use AI_FILTER with a JOIN to express linking two tables with a natural language prompt that AI can reason on.

Important

When performing JOIN operations that utilize the AI_FILTER function, each table in the JOIN can’t exceed 500 rows.

To enable larger scale joins, contact your account manager to enable adaptive optimization preview.

The following example joins the RESUMES table with the JOBS table using a prompt with the AI_FILTER function.

SELECT *
FROM RESUMES
JOIN JOBS
ON AI_FILTER(PROMPT('Evaluate if this resume {0} fits this job description {1}', RESUME.contents, JOBS.jd));

Copy

AI_FILTER: Images¶

The following examples filter image files based on an instruction.

Filter images by providing an instruction predicate and the image file column:

WITH pictures AS (
  SELECT
      TO_FILE(file_url) AS img
  FROM DIRECTORY(@file_stage)
)
SELECT
FL_GET_RELATIVE_PATH(img) AS file_path FROM pictures
WHERE AI_FILTER('Is this a picture of a cat?', img);

Copy

WITH pictures AS (
  SELECT
      TO_FILE(file_url) AS img
  FROM DIRECTORY(@file_stage)
)
SELECT
    FL_GET_RELATIVE_PATH(img) AS file_path FROM pictures
WHERE AI_FILTER(PROMPT('{0} is a cat picture', img));

Copy

+--------------------------+
|        FILE_PATH         |
|--------------------------+
|        2cats.jpg         |
+--------------------------+
|        cat1.png          |
+--------------------------+
|      orange_cat.jpg      |
+--------------------------+

Limitations¶

Snowflake AI functions don’t support dynamic table incremental refresh.
Snowflake AI functions don’t work on FILEs created from stage files from the following stage types:
- Internal stages with encryption mode TYPE = 'SNOWFLAKE_FULL'
- External stages with any customer-side encrypted mode:
  - TYPE = 'AWS_CSE'
  - TYPE = 'AZURE_CSE'
- User stage, table stage
- Stage with double-quoted names