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:
| Parameter | Purpose | Notes |
|---|---|---|
ON | The text column to search against | Must be VARCHAR/STRING |
ATTRIBUTES | Columns exposed for filtering | Optional but recommended for narrowing results |
TARGET_LAG | Maximum staleness of the index | Accepts intervals like '1 hour', '1 day' |
WAREHOUSE | Warehouse used for refresh operations | Sized 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
| Factor | Guidance |
|---|---|
| Index build time | Depends on data volume; a few minutes for small datasets, longer for millions of rows |
| Query latency | Typically sub-second for most workloads |
| Refresh cost | Incremental — only changed rows are re-processed |
| Warehouse sizing | XSMALL works for most refresh operations; scale up for large initial builds |
| Storage | Index is stored in Snowflake-managed storage; billed as standard storage |
| Compute billing | Refresh 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
-
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.
-
Declare useful attributes. Any column you might want to filter on should be in the
ATTRIBUTESlist. You can’t filter on columns that aren’t declared. -
Set realistic target lag. Match it to your data’s change frequency. Knowledge bases that update weekly don’t need a 1-minute lag.
-
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.
-
Keep source data clean. Garbage in, garbage out. Remove boilerplate, headers/footers, and formatting noise from documents before indexing.
-
Monitor refresh health. Check the refresh history periodically to catch failures early, especially after schema changes to source tables.
-
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.