Cortex Search SQL vs Snowsight

0.1Create the sample table and an indexing warehouse

The four rows come from the Cortex Search documentation. The warehouse is only for search: Snowflake recommends a dedicated warehouse, MEDIUM or smaller, for each search service.

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS CORTEX_DEMO_DB;
CREATE SCHEMA IF NOT EXISTS CORTEX_DEMO_DB.SUPPORT_SEARCH;

-- A dedicated warehouse for indexing (Snowflake recommends MEDIUM or smaller)
CREATE WAREHOUSE IF NOT EXISTS CORTEX_SEARCH_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE CORTEX_SEARCH_WH;
USE SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH;

-- Sample data from the Cortex Search documentation
CREATE OR REPLACE TABLE SUPPORT_TRANSCRIPTS (
  transcript_text VARCHAR,
  region          VARCHAR,
  agent_id        VARCHAR
);

INSERT INTO SUPPORT_TRANSCRIPTS VALUES
  ('My internet has been down since yesterday, can you help?', 'North America', 'AG1001'),
  ('I was overcharged for my last bill, need an explanation.', 'Europe', 'AG1002'),
  ('How do I reset my password? The email link is not working.', 'Asia', 'AG1003'),
  ('I received a faulty router, can I get it replaced?', 'North America', 'AG1004');

-- Search services refresh with change tracking. Turn it on as the table owner.
ALTER TABLE SUPPORT_TRANSCRIPTS SET CHANGE_TRACKING = TRUE;

Why change tracking? Search services refresh incrementally, so every source table needs change tracking and a Time Travel retention above zero. Snowflake tries to turn change tracking on when you create the service; doing it here as the table owner avoids a failure if the builder role cannot alter the table.

0.2Create a role for building search services

The builder role needs the SNOWFLAKE.CORTEX_USER database role, CREATE CORTEX SEARCH SERVICE on the schema, SELECT on the source table and USAGE on the warehouse. Both paths use this role.

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS CORTEX_SEARCH_ROLE;
GRANT ROLE CORTEX_SEARCH_ROLE TO USER <your_user>;
GRANT ROLE CORTEX_SEARCH_ROLE TO ROLE SYSADMIN;

USE ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE CORTEX_SEARCH_WH TO ROLE CORTEX_SEARCH_ROLE;
GRANT USAGE ON DATABASE CORTEX_DEMO_DB TO ROLE CORTEX_SEARCH_ROLE;
GRANT USAGE ON SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH TO ROLE CORTEX_SEARCH_ROLE;
GRANT SELECT ON TABLE CORTEX_DEMO_DB.SUPPORT_SEARCH.SUPPORT_TRANSCRIPTS TO ROLE CORTEX_SEARCH_ROLE;
GRANT CREATE CORTEX SEARCH SERVICE ON SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH TO ROLE CORTEX_SEARCH_ROLE;

USE ROLE ACCOUNTADMIN;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE CORTEX_SEARCH_ROLE;

Searching is granted separately. People who only search need USAGE on the service, its database and its schema. You grant that in A5, after reading the owner's rights warning there.

0.3Choose the embedding model

The model turns the search column into vectors. Pick it before you build, because you can't change it afterwards.

English only

snowflake-arctic-embed-m-v1.5, the default when you leave the model out.

Other languages, or a mix

A multilingual model such as snowflake-arctic-embed-l-v2.0, for example for Bahasa Indonesia mixed with English. Both paths in this guide use it.

Changing the model later means CREATE OR REPLACE, which rebuilds the index. Each model has its own price per million tokens, and some models are only available in certain regions, so check availability in your region first.

A1Create the search service

Run this as the builder role. The index is built as part of the statement, so it returns when the service can answer queries. On large tables that can take a while.

USE ROLE CORTEX_SEARCH_ROLE;
USE WAREHOUSE CORTEX_SEARCH_WH;
USE SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH;

CREATE OR REPLACE CORTEX SEARCH SERVICE TRANSCRIPT_SEARCH_SVC
  ON transcript_text
  ATTRIBUTES region
  WAREHOUSE = CORTEX_SEARCH_WH
  TARGET_LAG = '1 day'
  EMBEDDING_MODEL = 'snowflake-arctic-embed-l-v2.0'
  COMMENT = 'Support transcripts: search by meaning, filter by region'
  AS (
    SELECT transcript_text, region, agent_id
    FROM CORTEX_DEMO_DB.SUPPORT_SEARCH.SUPPORT_TRANSCRIPTS
  );

ON transcript_text

The column people search. It must be text. Snowflake embeds it for semantic search and uses the full text for keyword search.

ATTRIBUTES region

Columns you can filter on at query time. They must also be in the source query.

WAREHOUSE

Runs the source query, builds the index and keeps it fresh.

TARGET_LAG

How far the index may fall behind the source table. Refreshes process changes incrementally.

EMBEDDING_MODEL

Optional. Leave it out for the English-only default. Fixed after creation.

AS ( SELECT ... )

The source query. Every column it returns, here including agent_id, can come back in results.

Long text? Split it into chunks first. Snowflake recommends chunks of no more than 512 tokens, about 385 English words, and SPLIT_TEXT_RECURSIVE_CHARACTER can do the splitting. See the PDF example D2.

Have a unique ID? If a text column such as a ticket ID identifies each row, add PRIMARY KEY (ticket_id) after the ON clause. Services with a primary key can use an optimized refresh path that cuts the cost and latency of refreshes.

Replacing a live service? With CREATE OR REPLACE, the old index stops serving when the statement commits, and the new one serves once it is built.

A2Check that it is ready

USE ROLE CORTEX_SEARCH_ROLE;
SHOW CORTEX SEARCH SERVICES IN SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH;
DESCRIBE CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC;

Look at INDEXING_STATE and SERVING_STATE. If indexing stops, INDEXING_ERROR shows the SQL error from the source query. Indexing suspends itself after five refresh failures in a row; fix the cause, then run ALTER CORTEX SEARCH SERVICE ... RESUME INDEXING.

A3Test it with SEARCH_PREVIEW

Run the test search from a worksheet: transcripts about internet issues, only in North America.

SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC',
    '{
      "query": "internet issues",
      "columns": ["transcript_text", "region", "agent_id"],
      "filter": {"@eq": {"region": "North America"}},
      "limit": 2
    }'
  )
)['results'] AS results;

To see the results as rows, flatten them. Try words that don't appear in any transcript, such as billing problem: semantic search can still match the transcript about an overcharged bill.

SELECT
  value['transcript_text']::TEXT AS transcript_text,
  value['region']::TEXT          AS region,
  value['agent_id']::TEXT        AS agent_id
FROM TABLE(FLATTEN(PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC',
    '{"query": "billing problem", "columns": ["transcript_text", "region", "agent_id"], "limit": 3}'
  )
)['results']));

For testing only. SEARCH_PREVIEW checks a service from SQL. It takes string literals only, returns at most 300 KB and is slower than the REST and Python APIs, so applications should use A4.

Filters work on ATTRIBUTES columns with @eq, @contains, @gte, @lte and @primarykey, combined with @and, @or and @not.

A4Query it from an application

Applications call the REST endpoint or the Python API with the same request: the query text, the columns to return, an optional filter and a limit.

# PAT holds a programmatic access token for a user whose role can use the service
curl -X POST "https://<account_url>/api/v2/databases/CORTEX_DEMO_DB/schemas/SUPPORT_SEARCH/cortex-search-services/TRANSCRIPT_SEARCH_SVC:query" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json" \
  -H "Authorization: Bearer $PAT" \
  -d '{
    "query": "internet issues",
    "columns": ["transcript_text", "region", "agent_id"],
    "filter": {"@eq": {"region": "North America"}},
    "limit": 5
  }'

from snowflake.core import Root
from snowflake.snowpark import Session

# Fill in your connection parameters: account, user, authentication and role
session = Session.builder.configs({...}).create()
root = Root(session)

svc = (root.databases["CORTEX_DEMO_DB"]
           .schemas["SUPPORT_SEARCH"]
           .cortex_search_services["TRANSCRIPT_SEARCH_SVC"])

resp = svc.search(
    query="internet issues",
    columns=["transcript_text", "region", "agent_id"],
    filter={"@eq": {"region": "North America"}},
    limit=5,
)
print(resp.to_json())

The REST API accepts programmatic access tokens, key-pair JWTs and OAuth. The Python example needs version 0.8.0 or later of the snowflake package (pip install snowflake -U). Responses can be up to 10 MB.

Plan for HTTP 429. Cortex Search returns 429 when requests arrive too fast, so retry with backoff. For more than 20 queries per second on one service, contact your Snowflake account team.

A5Share it with the people who search

USE ROLE SYSADMIN;
GRANT USAGE ON DATABASE CORTEX_DEMO_DB TO ROLE <app_role>;
GRANT USAGE ON SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH TO ROLE <app_role>;

USE ROLE CORTEX_SEARCH_ROLE;
GRANT USAGE ON CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC TO ROLE <app_role>;

-- Path B service (skip if you only built Path A)
GRANT USAGE ON CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_GUI TO ROLE <app_role>;

Searching needs USAGE on the service, its database and its schema. No grant on SUPPORT_TRANSCRIPTS is needed, which is exactly why the warning above matters.

A6Control cost and freshness

Each service has the three cost meters shown in How it works. These commands tune them.

USE ROLE CORTEX_SEARCH_ROLE;

-- Pause serving after 30 idle minutes (Preview, minimum 1800 seconds)
ALTER CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC SET AUTO_SUSPEND = 1800;

-- Set how fresh the index must be
ALTER CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC SET TARGET_LAG = '1 hour';

-- Process new rows now instead of waiting for the next refresh
ALTER CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC REFRESH;

-- Pause or resume by hand (needs OPERATE on the service)
ALTER CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC SUSPEND SERVING;
ALTER CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_SVC RESUME SERVING;

Auto-suspend is in Preview. After the idle period it pauses serving, and the next query resumes it. Resuming can take a few minutes, and requests in that window get HTTP 429 with a Retry-After header.

See what it costs. Run these as a role that can read SNOWFLAKE.ACCOUNT_USAGE.

-- Serving, embedding and batch credits per service per day
SELECT usage_date, service_name, consumption_type, model_name, tokens, credits
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_SEARCH_DAILY_USAGE_HISTORY
WHERE database_name = 'CORTEX_DEMO_DB'
ORDER BY usage_date DESC, service_name;

-- Refresh compute, on the dedicated indexing warehouse
SELECT start_time, end_time, credits_used
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE warehouse_name = 'CORTEX_SEARCH_WH'
ORDER BY start_time DESC;

The usage view covers serving, embedding tokens and batch search, but not warehouse compute, which is one more reason to give each service its own warehouse. To cap spend, set up a resource budget for Cortex Search.

B1Open Cortex Search

1. Open your user menu at the bottom left of Snowsight, select Switch role and choose CORTEX_SEARCH_ROLE.
2. In the left navigation, select AI & ML›Cortex Search.
3. Click Create at the top right.

B2Name the service

• Role and warehouse: CORTEX_SEARCH_ROLE and CORTEX_SEARCH_WH. The warehouse runs the source query when the service is created and each time it refreshes.
• Service database and schema: CORTEX_DEMO_DB.SUPPORT_SEARCH
• Service name: TRANSCRIPT_SEARCH_GUI
• Click Next.

Use uppercase. Snowsight double-quotes the service name. An uppercase name works in SQL with or without quotes, while a lowercase one such as demo_test must always be written as "demo_test".

B3Select the data

• Select Table or view. Stage (Preview) is the PDF route, covered in D1.
• Choose CORTEX_DEMO_DB › SUPPORT_SEARCH › SUPPORT_TRANSCRIPTS and check the Data preview.
• Click Next.

The wizard indexes one table or view. For joins, transformations or several sources, create a view first or use SQL.

B4Choose what to search, filter and return

Three short screens follow. Click Next after each one. Their order can differ between Snowsight releases, so follow the step list on the left.

B5Configure indexing

• Target Lag: 1 day, as in Path A.
• Embedding model: snowflake-arctic-embed-l-v2.0, as in Path A. The default, snowflake-arctic-embed-m-v1.5, is English only, and you can't change the model later.
• Warehouse for indexing: CORTEX_SEARCH_WH
• Optional: click Show advanced options, turn Auto-suspend (Preview) on and enter at least 30 minutes.
• Click Create.

B6Watch it build

1. After you click Create, a message says Creating search service and points you to Query History. Open Query History to follow the statements the wizard runs.
2. In the captured demo, those statements include CREATE CORTEX SEARCH SERVICE. Copy it into a script if you want to keep the service as code A1.
3. Back on the Cortex Search page, find TRANSCRIPT_SEARCH_GUI and check its indexing and serving state.

B7Test it in the Playground

1. On the Cortex Search page, choose CORTEX_DEMO_DB.SUPPORT_SEARCH in the database and schema filter.
2. Click Playground at the top right.
3. Make sure TRANSCRIPT_SEARCH_GUI is the selected service, type internet issues and review the results.

The Playground does not support multi-index services. To test the exact region filter from the top of this page, run the query in B8.

B8Use the same SQL from here on

A service built in the wizard is a normal Cortex Search Service. Check, query, share and tune it with A2 to A6, using TRANSCRIPT_SEARCH_GUI as the name. For example:

USE ROLE CORTEX_SEARCH_ROLE;
DESCRIBE CORTEX SEARCH SERVICE CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_GUI;

SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'CORTEX_DEMO_DB.SUPPORT_SEARCH.TRANSCRIPT_SEARCH_GUI',
    '{"query": "internet issues", "columns": ["transcript_text", "region"], "filter": {"@eq": {"region": "North America"}}, "limit": 2}'
  )
)['results'] AS results;

If the service name has lowercase letters, wrap it in double quotes inside the string as well.

D1In Snowsight (Preview)

1. In B3, select Stage, choose the stage and the PDF files, then click Next.
2. Parsing strategy: Text embedding for documents that are mostly text, or Multimodal embedding for documents with diagrams, images or drawings.
3. Automated processing: choose the Destination schema for the table of parsed content that the wizard creates. Under Show advanced options you can set a prefix for new objects and pick a chunking strategy:
   • Chunk by size: 1,800 characters with a 300-character overlap by default. Snowflake recommends 1,800 characters, about 512 tokens, or fewer.
   • No chunking: one chunk per document, for short documents.
   • Split by page: one chunk per page, when each page has little text.
4. Configure indexing as in B5 and click Create. Parsing can take several minutes, and the service has no data until it finishes.

D2With SQL

This follows Snowflake's PDF tutorial with this guide's names: AI_PARSE_DOCUMENT extracts the text and layout, and SPLIT_TEXT_MARKDOWN_HEADER splits it on headers.

-- The document route also needs CREATE STAGE and CREATE TABLE
USE ROLE SYSADMIN;
GRANT CREATE STAGE, CREATE TABLE ON SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH TO ROLE CORTEX_SEARCH_ROLE;

USE ROLE CORTEX_SEARCH_ROLE;
USE WAREHOUSE CORTEX_SEARCH_WH;
USE SCHEMA CORTEX_DEMO_DB.SUPPORT_SEARCH;

-- A stage for the PDF files, with a directory table
CREATE OR REPLACE STAGE DOCS_STAGE
  DIRECTORY = (ENABLE = TRUE)
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

Upload the PDFs in Snowsight: Catalog›Explorer › CORTEX_DEMO_DB › SUPPORT_SEARCH › Stages › DOCS_STAGE, click + Files at the top right, drop the files and click Upload. Then parse, chunk and index:

-- Refresh the directory table so it lists the uploaded files
ALTER STAGE DOCS_STAGE REFRESH;

-- 1. Parse: extract text and layout from each PDF
CREATE OR REPLACE TABLE DOCS_RAW_TEXT AS
SELECT
  relative_path,
  TO_VARCHAR(
    AI_PARSE_DOCUMENT(
      TO_FILE('@CORTEX_DEMO_DB.SUPPORT_SEARCH.DOCS_STAGE', relative_path),
      {'mode': 'LAYOUT'}
    ):content
  ) AS extracted_layout
FROM DIRECTORY('@CORTEX_DEMO_DB.SUPPORT_SEARCH.DOCS_STAGE')
WHERE relative_path LIKE '%.pdf';

-- 2. Chunk: split on the top two Markdown headers, 2,000 characters, 300 overlap
CREATE OR REPLACE TABLE DOCS_CHUNKS AS
SELECT
  relative_path,
  BUILD_SCOPED_FILE_URL(@CORTEX_DEMO_DB.SUPPORT_SEARCH.DOCS_STAGE, relative_path) AS file_url,
  (
    relative_path || ':\n'
    || COALESCE('Header 1: ' || c.value['headers']['header_1'] || '\n', '')
    || COALESCE('Header 2: ' || c.value['headers']['header_2'] || '\n', '')
    || c.value['chunk']
  ) AS chunk,
  'English' AS language
FROM DOCS_RAW_TEXT,
  LATERAL FLATTEN(SNOWFLAKE.CORTEX.SPLIT_TEXT_MARKDOWN_HEADER(
    extracted_layout,
    OBJECT_CONSTRUCT('#', 'header_1', '##', 'header_2'),
    2000,
    300
  )) c;

-- 3. Index: search the chunks, filter by language
CREATE OR REPLACE CORTEX SEARCH SERVICE DOCS_SEARCH_SVC
  ON chunk
  ATTRIBUTES language
  WAREHOUSE = CORTEX_SEARCH_WH
  TARGET_LAG = '1 hour'
  AS (
    SELECT chunk, relative_path, file_url, language
    FROM DOCS_CHUNKS
  );

This is a one-time load. For new files, insert their chunks into DOCS_CHUNKS instead of recreating the table, because a recreated source table needs change tracking turned on again. AI_PARSE_DOCUMENT is billed separately from the search service.