Skip to main content

Cortex Search Service Deep Dive

Why Cortex Search Exists​

Building production-quality search over unstructured data traditionally means stitching together an embedding model, a vector database, a keyword index (BM25), and a fusion layer to combine results. That’s a lot of infrastructure to deploy, tune, and keep in sync with your source data.

Cortex Search Service replaces that entire stack with a single Snowflake object. You point it at a text column, and Snowflake handles:

  • Chunking and embedding generation
  • Hybrid retrieval (semantic + keyword)
  • Reciprocal Rank Fusion (RRF) to merge results
  • Incremental refresh as source data changes
  • Access control via standard Snowflake RBAC

No external vector DB. No embedding pipeline. No reindexing cron jobs.


CREATE CORTEX SEARCH SERVICE Syntax​

CREATE OR REPLACE CORTEX SEARCH SERVICE analytics_db.public.product_docs_search
ON doc_text -- The text column to index
ATTRIBUTES category, product_line -- Columns available for filtering
WAREHOUSE = search_wh
TARGET_LAG = '1 hour' -- How fresh the index should be
AS (
SELECT
doc_text,
category,
product_line,
doc_title,
doc_url
FROM analytics_db.public.product_documents
);

Key parameters:

ParameterPurposeNotes
ONThe text column to search againstMust be VARCHAR/STRING
ATTRIBUTESColumns exposed for filteringOptional but recommended for narrowing results
TARGET_LAGMaximum staleness of the indexAccepts intervals like '1 hour', '1 day'
WAREHOUSEWarehouse used for refresh operationsSized based on data volume

The AS subquery defines the source data. It can include joins, filters, and transformations — Cortex Search indexes the output of the query.


How Hybrid Search Works​

Cortex Search does not rely on a single retrieval method. It combines three approaches and fuses the results:

 User Query: "How do I configure SSO for my account?"
│
├──► Semantic Search (embedding similarity)
│ Finds: docs about authentication, identity providers
│
├──► Keyword Search (BM25 term matching)
│ Finds: docs containing "SSO", "configure"
│
└──► Reciprocal Rank Fusion (RRF)
Merges both ranked lists into a single result set
Documents appearing in both lists get a boost

This hybrid approach handles both:

  • Exact term queries — where the user knows the specific term (e.g., "SAML")
  • Conceptual queries — where the user describes the idea (e.g., "single sign-on setup")

You don’t configure the fusion. Snowflake handles it automatically.


Querying from SQL​

SELECT SNOWFLAKE.CORTEX.SEARCH(
'analytics_db.public.product_docs_search', -- service name
'{
"query": "How do I set up role-based access control?",
"columns": ["doc_title", "doc_text", "doc_url"],
"limit": 5
}'
);

The result is a JSON object containing an array of matching documents with the columns you requested, ranked by relevance.


Querying from Python​

from snowflake.snowpark.context import get_active_session
import json

session = get_active_session()

results = session.sql("""
SELECT SNOWFLAKE.CORTEX.SEARCH(
'analytics_db.public.product_docs_search',
'{
"query": "data retention policies",
"columns": ["doc_title", "doc_text", "category"],
"limit": 5
}'
)
""").collect()

search_results = json.loads(results[0][0])
for doc in search_results["results"]:
print(f"{doc['doc_title']}: {doc['doc_text'][:200]}")

Filter Expressions​

Filters narrow results before ranking, which improves both relevance and performance. Use the filter key in the query JSON:

@eq — Exact match​

{
"query": "password reset",
"columns": ["doc_title", "doc_text"],
"filter": {"@eq": {"category": "security"}},
"limit": 5
}

@and — Combine conditions​

{
"filter": {
"@and": [
{"@eq": {"category": "security"}},
{"@eq": {"product_line": "enterprise"}}
]
}
}

@or — Match any condition​

{
"filter": {
"@or": [
{"@eq": {"category": "security"}},
{"@eq": {"category": "compliance"}}
]
}
}

@not — Exclude matches​

{
"filter": {
"@not": {"@eq": {"category": "deprecated"}}
}
}

Filters only work on columns declared in the ATTRIBUTES clause of the service definition.


Auto-Refresh and Incremental Updates​

Cortex Search monitors the source query and incrementally updates the index based on your TARGET_LAG setting. When rows are inserted, updated, or deleted in the source tables, the index reflects those changes within the target lag window.

-- Check the current refresh status
SHOW CORTEX SEARCH SERVICES IN SCHEMA analytics_db.public;

-- View refresh history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.CORTEX_SEARCH_SERVICE_REFRESH_HISTORY(
SERVICE_NAME => 'analytics_db.public.product_docs_search'
))
ORDER BY refresh_start_time DESC
LIMIT 10;

If you need the index updated immediately (e.g., after a bulk load):

ALTER CORTEX SEARCH SERVICE analytics_db.public.product_docs_search REFRESH;

Using Cortex Search as an Agent Tool​

As covered in Cortex Agents, a search service can be attached directly to an agent:

CREATE OR REPLACE CORTEX AGENT analytics_db.public.support_agent
MODEL = 'claude-3.5-sonnet'
TOOLS = (
CORTEX_SEARCH_TOOL(
search_service => 'analytics_db.public.product_docs_search'
),
SEMANTIC_VIEW_TOOL(
semantic_view => 'analytics_db.public.sales_sv'
)
)
SYSTEM_PROMPT = 'You help answer product and sales questions. Use search for documentation lookups and the semantic view for data queries.';

The agent automatically decides when to invoke search versus SQL based on the question.


Performance and Cost​

FactorGuidance
Index build timeDepends on data volume; a few minutes for small datasets, longer for millions of rows
Query latencyTypically sub-second for most workloads
Refresh costIncremental — only changed rows are re-processed
Warehouse sizingXSMALL works for most refresh operations; scale up for large initial builds
StorageIndex is stored in Snowflake-managed storage; billed as standard storage
Compute billingRefresh uses the designated warehouse; queries use serverless compute

To manage costs, set TARGET_LAG to the longest acceptable staleness. A '1 day' lag is far cheaper than '1 minute' for data that doesn’t change frequently.


Managing Services​

-- Suspend the service (stops refresh, service remains queryable with stale data)
ALTER CORTEX SEARCH SERVICE analytics_db.public.product_docs_search SUSPEND;

-- Resume the service
ALTER CORTEX SEARCH SERVICE analytics_db.public.product_docs_search RESUME;

-- Drop the service entirely
DROP CORTEX SEARCH SERVICE analytics_db.public.product_docs_search;

-- Describe the service definition
DESCRIBE CORTEX SEARCH SERVICE analytics_db.public.product_docs_search;

Best Practices​

  1. Choose the right text column. Index the column with the most meaningful content. If your documents are split across multiple columns, concatenate them in the source query.

  2. Declare useful attributes. Any column you might want to filter on should be in the ATTRIBUTES list. You can’t filter on columns that aren’t declared.

  3. Set realistic target lag. Match it to your data’s change frequency. Knowledge bases that update weekly don’t need a 1-minute lag.

  4. Pre-filter when possible. Filters are applied before search, which reduces the candidate set and improves relevance. Always pass filters when the user’s intent narrows the domain.

  5. Keep source data clean. Garbage in, garbage out. Remove boilerplate, headers/footers, and formatting noise from documents before indexing.

  6. Monitor refresh health. Check the refresh history periodically to catch failures early, especially after schema changes to source tables.

  7. Grant narrow access. Use RBAC to control who can query the service, just like any other Snowflake object.

GRANT USAGE ON CORTEX SEARCH SERVICE analytics_db.public.product_docs_search
TO ROLE support_role;

Next Steps​

Proceed to Building Intelligent Apps with Streamlit & Cortex to combine search, analyst, and agents into a full application.