Query Cortex Search Directly in SQL with the CORTEX_SEARCH_RETRIEVE Function

Snowflake has released the CORTEX_SEARCH_RETRIEVE table function in Public Preview. This function enables low-latency Cortex Search queries directly within SQL statements.

Use cases

Imagine you have a table containing customer support tickets and need to fetch the most relevant knowledge base articles in real time to ground an LLM response or enrich data, without leaving SQL.

This function enables you to:

• Retrieve contextual text for RAG pipelines and chatbots using SQL.
• Run row-by-row semantic lookups over a table using LATERAL joins.
• Combine search results with downstream analytical tables using JOIN and GROUP BY.

How to use

The general syntax uses the table function wrapper:

SELECT *
FROM TABLE(CORTEX_SEARCH_RETRIEVE(
    service_name => '<database>.<schema>.<cortex_search_service>',
    query => '<query_string>',
    limit => 10
));

The following example runs a single semantic search against a configured search service:

SELECT *
FROM TABLE(CORTEX_SEARCH_RETRIEVE(
    service_name => 'my_db.my_schema.my_search_service',
    query => 'how do I file an expense report',
    limit => 5
));

What happens in this code:

1. The function targets my_search_service in the specified database and schema.
2. The query string is processed against the existing serving layer of the search service.
3. The statement outputs the top 5 matching records with their associated metadata.

To query the service once per record in a table, use a LATERAL join:

SELECT
    q.question_id,
    q.question_text,
    cs.article_title,
    cs.METADATA$RESULT_DETAIL
FROM user_questions AS q,
LATERAL CORTEX_SEARCH_RETRIEVE(
    service_name => 'my_db.my_schema.my_search_service',
    query => q.question_text,
    limit => 2
) AS cs;

What happens in this code:

1. The LATERAL join executes the search function once for each row in user_questions.
2. The value in q.question_text acts as the dynamic query string.
3. Up to 2 matching records are retrieved for each row without aborting the batch if an isolated row fails.

What it returns

The function returns the indexed source columns (excluding VECTOR data types) alongside the following metadata fields:

Field	Description
METADATA$REQUEST_ID	Unique request identifier useful for logging and debugging queries.
METADATA$ERROR	Error string if the search failed for that specific row; NULL on success.
METADATA$RESULT_DETAIL	JSON string containing scoring metrics and score components.

Limitations and requirements

• Maturity: Public Preview across all accounts in regions where Cortex Search is available.
• Permissions: The executing role requires the USAGE privilege on the Cortex Search Service.
• Serving state: The serving layer must be running. If it was suspended due to inactivity, the first query resumes it, introducing initial latency.
• Workload design: Designed for low-latency queries on single items or small sets of rows. For high-volume entity resolution or batch jobs, use CORTEX_SEARCH_BATCH instead.
• Data sharing: Not supported for shared services accessed via listings or secure data shares.