Cortex AI – SQL Functions & Cortex Search
Day 15 covered pulling your code into Snowflake with Snowpark. Day 16 flips the direction. With Cortex, the AI already lives inside Snowflake. You call it from a SQL worksheet like any other function. No Python, no API keys, no model deployment. The common trap on C03 mocks is the line “Cortex AI requires Snowpark or Python.” It does not. It is pure SQL. Today fixes that misconception, locks down the six function names, and adds Cortex Search, the hybrid retrieval service, on top.
| Term | Plain meaning |
|---|---|
| LLM | Large Language Model. Trained on huge text corpora. You give it a prompt, it returns a continuation. Llama, Mistral, Claude, Snowflake Arctic. |
| Cortex AI | Snowflake’s AI/ML feature bundle. LLM SQL functions, ML SQL functions, Cortex Search, Cortex Analyst, Cortex Agents. All hosted inside Snowflake. |
| Cortex SQL function | A pre-built function in the SNOWFLAKE.CORTEX schema. Calls an LLM or ML model from plain SQL. |
| Embedding | A numeric vector that represents text. Two pieces of text with similar meaning produce similar vectors. This is what powers semantic search. |
| Semantic search | Finds text that means something similar to the query. Words can differ. Driven by embeddings. |
| Lexical search | Finds text that contains the same words as the query. Classic keyword matching. |
| Hybrid search | Semantic and lexical combined, then reranked. This is what Cortex Search does. |
| RAG | Retrieval-Augmented Generation. Fetch relevant documents from a search index, feed them to an LLM as context. The LLM then answers grounded in your data. |
| Cross-region inference | Calling a Cortex model hosted in a different region from your account. Enabled by an account parameter. Adds some latency. |
Today’s Concept
Micro-Concept 1: Cortex SQL Functions (The One-Liner)
The core sentence:
Cortex AI functions are called from pure SQL. No Python, no Snowpark, no external API setup.
That single fact rules out the most common Cortex distractor on the exam. The line “Cortex requires Snowpark or Python” is always false. Cortex functions live in the SNOWFLAKE.CORTEX schema. They look like any other SQL function:
SELECT SNOWFLAKE.CORTEX.SENTIMENT('Snowflake is great for analytics!');
-- Returns a number between -1 (very negative) and 1 (very positive)One SELECT. One function. One model invocation. The LLM runs inside Snowflake. Your data never leaves the security perimeter. There are no API keys to rotate, no rate limits to manage, no endpoints to configure.
Micro-Concept 2: The Six Functions to Memorize
The exam tests name-to-purpose recognition. Six functions matter. Learn the pairing.
| Function | Purpose | Returns |
|---|---|---|
COMPLETE | Given a model name and a prompt, generate a text response. The most general-purpose function. | Generated text (string) |
SUMMARIZE | Condense input text into a short summary. English-optimized. | Summary text (string) |
TRANSLATE | Translate input text between supported languages. Pass '' as the source to auto-detect. | Translated text (string) |
SENTIMENT | Score the sentiment of input text. Returns a number, not a label. | Float between -1 (very negative) and 1 (very positive) |
EXTRACT_ANSWER | Given a text passage and a question, return the answer if it appears in the text. | Array of answer plus confidence (variant) |
CLASSIFY_TEXT | Classify input text into one of the categories you supply as a list. | Object containing the chosen label |
The SENTIMENT trap. SENTIMENT returns a score, not a class label. If an option says SENTIMENT returns 'positive', 'negative', or 'neutral', eliminate it. The return value is a number between -1 and 1. CLASSIFY_TEXT is the function that returns a label from a list you supply. In training sessions I have run, this single fact catches more candidates than any other Cortex point.
Two related functions to recognize even though they sit outside the core six:
EMBED_TEXT_768returns a 768-dimension vector embedding. Used for similarity search and as input to vector stores.TRY_COMPLETEis the error-tolerant version of COMPLETE. ReturnsNULLon failure instead of raising.
Micro-Concept 3: COMPLETE (Picking a Model)
COMPLETE is the gateway to the LLM catalog. The first argument is the model name as a string. Snowflake hosts a rotating set of models from Anthropic, Meta, Mistral, OpenAI, and Snowflake’s own Arctic family.
SELECT SNOWFLAKE.CORTEX.COMPLETE(
'snowflake-arctic',
'Explain micro-partitions in one sentence.'
);The exam does not test which model fits which task. That catalog churns monthly. It tests three things. (a) COMPLETE selects a model via a string argument. (b) Models include Snowflake Arctic plus open-source families like Llama and Mistral. (c) Not every model is hosted in every region.
That last point is where scenario questions live. If your account region does not host a particular model, you have two clean options:
- Enable cross-region inference at the account level. The parameter is
CORTEX_ENABLED_CROSS_REGION, set withALTER ACCOUNTby ACCOUNTADMIN. Snowflake routes the request to a region that hosts the model. Adds latency, no data egress charges, and credits are billed in your requesting region. - Pick a different model that is available locally.
The other access-control knob the exam may test is the CORTEX_USER database role. By default it is granted to PUBLIC, so any user can call Cortex functions. An administrator can revoke that grant to lock things down. A related account parameter, CORTEX_MODELS_ALLOWLIST, restricts which models are callable.
Micro-Concept 4: A Quick Note on the Newer AI_* Family
Snowflake has rolled out a parallel naming family with AI_ prefixes: AI_COMPLETE, AI_CLASSIFY, AI_SUMMARIZE, AI_SENTIMENT, AI_TRANSLATE, AI_EXTRACT. The docs label these as the updated versions of the older SNOWFLAKE.CORTEX.* functions. Both surfaces work today.
For the C03 exam as it stands now, the SNOWFLAKE.CORTEX namespace is the standard answer. An AI_COMPLETE distractor is not factually wrong, since it is the newer name. But question stems and answer keys still use the older form. Recognize both. On test day, default to SNOWFLAKE.CORTEX.* when the question is name-matching.
Micro-Concept 5: Cortex Search (Hybrid Retrieval)
Cortex Search is a fully-managed retrieval service. You point it at a text column. It builds and maintains a hybrid index. You query it for relevant rows with low latency. It is the default building block for RAG-style applications and enterprise search inside Snowflake.
“Hybrid” means three things happening together:
- Vector search. Snowflake embeds your text column (typically via a Snowflake Arctic Embed model) and finds rows whose embeddings are nearest to the query embedding. This catches semantic similarity. Results mean the same thing even when no keywords overlap.
- Keyword search. Classic lexical match. Catches exact strings vector search would miss: product codes, named entities, jargon.
- Semantic reranking. A reranker model merges the two candidate sets and orders them by overall relevance.
The exam-relevant contrast is sharp: Cortex Search is not LIKE. A scenario asking for “find documents related to X” where the match requires understanding synonyms or paraphrasing is a Cortex Search question. A scenario asking for “find rows where a specific string appears” is a SQL LIKE question. In practice tests I review, candidates pick Cortex Search for every “find text” stem. Only pick it when the use case genuinely needs semantic relevance.
Other facts to file away:
- The DDL is
CREATE CORTEX SEARCH SERVICE. It points at a source query (typically aSELECTover a base table) and produces a queryable service object. - Snowflake handles embedding refresh as the underlying data changes, controlled by
TARGET_LAG. You do not re-embed manually. - Cortex Search supports metadata filtering via attribute columns declared at service creation. Filter results by date, region, status, or any scalar you attach.
- Cortex Search runs entirely inside Snowflake. No data export, no external vector database.
- Cortex Search is region-bound like the LLM functions. Confirm availability before committing an architecture to it.
Cheat Sheet
| Concept | What to remember |
|---|---|
| Cortex SQL functions one-liner | Pure SQL, no Python required. Live in the SNOWFLAKE.CORTEX schema. |
| The six functions | COMPLETE, SUMMARIZE, TRANSLATE, SENTIMENT, EXTRACT_ANSWER, CLASSIFY_TEXT |
| SENTIMENT return type | Float between -1 and 1. A score, NOT a label. |
| COMPLETE model selection | First argument is a model-name string (e.g. 'snowflake-arctic', 'llama3.1-70b', 'mistral-large2'). |
| Available models | Snowflake Arctic plus Llama, Mistral, Anthropic Claude, OpenAI, others. Catalog changes, check docs. |
| Region constraint | Cortex models are region-bound. Enable cross-region inference to call a non-local model. |
| Required role | CORTEX_USER database role. Granted to PUBLIC by default. |
| Restrict allowed models | CORTEX_MODELS_ALLOWLIST account parameter. |
| Cortex Search one-liner | Hybrid retrieval: vector + keyword + reranker. Built for RAG and enterprise search. |
| Cortex Search vs LIKE | LIKE = exact string match. Cortex Search = semantic + lexical. Pick Cortex Search when meaning matters more than exact words. |
| Cortex Search DDL | CREATE CORTEX SEARCH SERVICE. Points at a base query, auto-embeds, refreshes via TARGET_LAG. |
| Metadata filtering | Cortex Search supports filtering by attached scalar columns (date, region, tag, etc.). |
Newer AI_* family | Going-forward names (AI_COMPLETE, AI_CLASSIFY, etc.) work alongside SNOWFLAKE.CORTEX.*. Old form is the C03 standard for now. |
| Where data lives | Inputs and outputs stay inside Snowflake’s security perimeter. No external API calls. |
Three options that are always wrong:
(1) “Cortex AI functions require Snowpark or Python to call.” FALSE. They are SQL functions. This is the most-tested distractor in sub-obj 1.6. Eliminate it on sight. (2) “SENTIMENT returns one of: positive, negative, neutral.” FALSE. SENTIMENT returns a float from -1 to 1. CLASSIFY_TEXT is the function that returns a label from a list you supply. (3) “Cortex Search is just a wrapper for LIKE / ILIKE.” FALSE. Cortex Search does hybrid retrieval: vector embeddings plus keyword search plus a reranker. LIKE has no semantics. One scenario pattern to anchor: when the stem mentions “the model is not available in the user’s region,” the answer is almost always cross-region inference. It is not “switch your account region” and not “deploy the model yourself.”
Hands-On Lab
day10_orders table from Day 10 still alive.Confirm Cortex access from your account.
USE ROLE ACCOUNTADMIN;
USE WAREHOUSE lab_xs;
-- Sanity: confirm CORTEX_USER role is grantable / visible
SHOW ROLES LIKE 'CORTEX_USER';
-- Quickest possible Cortex call — SENTIMENT on a literal string
SELECT SNOWFLAKE.CORTEX.SENTIMENT('Snowflake is amazing for analytics!') AS score;CORTEX_ENABLED_CROSS_REGION = 'ANY_REGION' at the account level to fix it.Run each of the six core functions. These are the names and purposes the exam will test.
-- 1. SUMMARIZE — shrink a passage to its essentials
SELECT SNOWFLAKE.CORTEX.SUMMARIZE(
'Snowflake architecture has three independently scaling layers: storage, '
'compute, and cloud services. Storage holds the data in micro-partitions, '
'compute is delivered as virtual warehouses, and cloud services handles '
'metadata, security, and query optimization.'
) AS summary;
-- 2. TRANSLATE — from English to Spanish
SELECT SNOWFLAKE.CORTEX.TRANSLATE('Hello, how are you?', 'en', 'es') AS spanish;
-- 3. COMPLETE — free-form prompt + model selection
SELECT SNOWFLAKE.CORTEX.COMPLETE(
'snowflake-arctic',
'Explain Snowflake micro-partitions in exactly one sentence.'
) AS arctic_says;
-- 4. CLASSIFY_TEXT — pick from a category list you supply
SELECT SNOWFLAKE.CORTEX.CLASSIFY_TEXT(
'The product arrived broken and the support team ignored me.',
['positive', 'negative', 'neutral']
) AS classification;
-- 5. EXTRACT_ANSWER — question against an unstructured passage
SELECT SNOWFLAKE.CORTEX.EXTRACT_ANSWER(
'Snowpark-Optimized warehouses provide 16x the memory per node compared to '
'standard warehouses and have a minimum size of Medium.',
'What is the minimum size for a Snowpark-Optimized warehouse?'
) AS answer;CLASSIFY_TEXT returns an object with the chosen label. EXTRACT_ANSWER returns an array containing the extracted answer plus a confidence score.Apply Cortex inline to a table column. This is the realistic production pattern: score or classify every row of a table in one query.
-- Create a small set of fake customer comments to score
CREATE OR REPLACE TEMPORARY TABLE day16_comments (id INT, comment STRING);
INSERT INTO day16_comments VALUES
(1, 'The dashboard is fantastic, it saved me hours this week.'),
(2, 'Slow query times have been a major frustration.'),
(3, 'It works fine, nothing special.'),
(4, 'Absolutely thrilled with the new clustering feature!');
-- Score each comment with SENTIMENT, classify with CLASSIFY_TEXT
SELECT
id,
comment,
ROUND(SNOWFLAKE.CORTEX.SENTIMENT(comment), 2) AS score,
SNOWFLAKE.CORTEX.CLASSIFY_TEXT(
comment,
['positive', 'negative', 'neutral']
):label::STRING AS label
FROM day16_comments
ORDER BY score DESC;CLASSIFY_TEXT returns a variant, so we project :label with the colon-path syntax and cast to STRING. The TEMPORARY table drops itself when the session ends. No cleanup needed.Concept check: Cortex Search DDL (no need to run). A real Cortex Search service needs a base table with meaningful text plus warehouse time to build the index. The shape of the DDL is what the exam tests:
-- Concept check only — DO NOT RUN unless you have a meaningful text corpus.
/*
CREATE OR REPLACE CORTEX SEARCH SERVICE day16_kb_search
ON content_text -- the column to embed
ATTRIBUTES region, created_at -- scalar columns for metadata filtering
WAREHOUSE = lab_xs
TARGET_LAG = '1 hour' -- how fresh the index must be
AS
SELECT doc_id, content_text, region, created_at
FROM knowledge_base
WHERE active = TRUE;
-- Query the service via the SEARCH_PREVIEW function
SELECT PARSE_JSON(
SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
'MY_DB.MY_SCHEMA.DAY16_KB_SEARCH',
'{
"query": "how do I tune a slow dashboard?",
"columns": ["doc_id", "content_text"],
"limit": 5,
"filter": {"@eq": {"region": "us-east"}}
}'
)
):results;
*/The newer AI_* family: same idea, different name.
-- The newer canonical surface; identical purpose to SNOWFLAKE.CORTEX.SENTIMENT
SELECT AI_SENTIMENT('Snowflake is amazing for analytics!') AS score;
-- Equivalent of SNOWFLAKE.CORTEX.COMPLETE
SELECT AI_COMPLETE(
'snowflake-arctic',
'Explain Snowflake micro-partitions in one sentence.'
) AS arctic_says;SNOWFLAKE.CORTEX.* form is what C03 references today. The newer AI_* form is the going-forward standard surface. It may appear as either a synonym or a distractor depending on when your exam form was last refreshed. Recognize both. Default to the older form on test day.Cleanup. Nothing to drop. Everything in this lab was either a function call or a TEMPORARY table that auto-cleans when the session ends.
-- Optional: drop the temporary table now if you'd like
DROP TABLE IF EXISTS day16_comments;
-- day10_orders remains intact — we'll keep using it through Day 39.SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY filtered to SERVICE_TYPE = 'AI_SERVICES'.Snowflake Documentation
Official docs for today’s topics.
External References
Function references and region availability.
Practice Questions
Options:
A. SNOWFLAKE.CORTEX.SUMMARIZE
B. SNOWFLAKE.CORTEX.SENTIMENT
C. SNOWFLAKE.CORTEX.EXTRACT_ANSWER
D. SNOWFLAKE.CORTEX.TRANSLATE
Why B: SENTIMENT is the purpose-built scorer for positive/negative tone. It returns a float between -1 and 1 and is callable from plain SQL. No Python or Snowpark required.
Why not A: SUMMARIZE condenses text into a summary. It does not score tone.
Why not C: EXTRACT_ANSWER answers a specific question against a text passage. Wrong tool for tone scoring.
Why not D: TRANSLATE converts text between languages.
Options:
A. Cortex AI functions can only be invoked from Snowpark Python; SQL invocation is not supported.
B. Cortex AI functions are SQL functions in the SNOWFLAKE.CORTEX schema and can be called from any SQL interface.
C. Cortex AI functions require an external HTTPS endpoint and an API integration.
D. Cortex AI functions are only available on Business Critical Edition.
Why B: Cortex AI functions are SQL functions, callable from worksheets, drivers, or any SQL interface. No Python or Snowpark needed. The data stays inside Snowflake; the LLMs are hosted by Snowflake.
Why not A: The most-tested Cortex distractor. Cortex is SQL-first. Snowpark Python access is one option, not a requirement.
Why not C: No external endpoint. That is External Functions, which is the opposite direction. Data is sent out of Snowflake.
Why not D: Cortex availability depends on region and model RBAC, not on edition.
Options:
A. SQL LIKE with wildcards
B. Cortex Search service
C. Search Optimization Service
D. A standard view with full-table SELECT
Why B: Cortex Search performs hybrid retrieval over a text column: vector (semantic) plus keyword (lexical) plus reranking. That maps directly to “find documents that mean the same thing.” It is the engine of choice for RAG-style applications inside Snowflake.
Why not A: LIKE is pure pattern matching with no understanding of meaning. It misses any document that paraphrases the query.
Why not C: Search Optimization Service accelerates point-lookup queries (highly selective equality, IN, or substring filters) on structured columns. It does not do semantic search. Re-read Day 13 if missed.
Why not D: A view does not change retrieval semantics. SELECT returns rows; it does not rank by meaning.
Options:
A. Enable cross-region inference on the account.
B. Deploy the model manually using a stored procedure.
C. Use an External Function to call the model’s vendor API.
D. Wait for Snowflake to enable the model globally; nothing else can be done.
Why A: Cross-region inference is Snowflake’s built-in mechanism for invoking a Cortex model hosted in a different region. ACCOUNTADMIN sets CORTEX_ENABLED_CROSS_REGION at the account level and Cortex routes the request to a region that hosts the model. Adds latency, no data egress charges, no account recreation.
Why not B: Cortex models are Snowflake-hosted. Customers cannot deploy them manually via stored procedures.
Why not C: External Functions send data out of Snowflake to a vendor API. That is the opposite direction from the Cortex value proposition, and not how Snowflake’s hosted models are accessed.
Why not D: Waiting is not a solution. Cross-region inference is the documented workaround for exactly this scenario.
Options:
A. Cortex Search performs hybrid retrieval, combining vector (semantic) and keyword (lexical) search with semantic reranking.
B. Cortex Search requires the customer to maintain and refresh the vector embeddings manually.
C. A Cortex Search service supports filtering query results by scalar attribute columns attached to the indexed rows.
D. Cortex Search is functionally equivalent to using SQL LIKE against a text column.
E. Cortex Search runs entirely outside Snowflake and requires data export to an external vector database.
Why A: Hybrid retrieval (vector + lexical + reranker) is the defining architecture of Cortex Search and the reason it outperforms either approach used alone.
Why C: Cortex Search supports metadata filtering. You declare scalar attribute columns at service creation and filter queries by them: date range, region, status, and so on.
Why not B: Snowflake auto-embeds and refreshes the index based on the configured TARGET_LAG. The customer does not manage embeddings manually.
Why not D: LIKE is pure pattern matching with no semantic understanding. Cortex Search adds vector retrieval and reranking on top of (not instead of) keyword search.
Why not E: Cortex Search runs entirely inside Snowflake. No data leaves the security perimeter.
Today you learned: Cortex AI functions are pure SQL. The most-tested trap in sub-objective 1.6 is the claim that they require Snowpark or Python. They do not. Six functions to recognize by name and purpose. COMPLETE generates free-form text and takes the model as its first argument. SUMMARIZE condenses text. TRANSLATE converts between languages. SENTIMENT returns a float from -1 to 1, NOT a label. EXTRACT_ANSWER answers a question against a passage. CLASSIFY_TEXT returns a label from a list you supply. COMPLETE selects from Snowflake Arctic, Llama, Mistral, Claude, and others via a string argument. Not every model is hosted in every region. Cross-region inference, the CORTEX_ENABLED_CROSS_REGION account parameter set by ACCOUNTADMIN, is the answer when the model is elsewhere. The newer AI_* family is the going-forward standard surface, but C03 still tests against SNOWFLAKE.CORTEX.* names. Cortex Search is a fully-managed hybrid retrieval service built on top of a text column: vector plus keyword plus reranker. Pick it when the scenario calls for semantic relevance, not when SQL LIKE would do.
Key takeaway: Cortex AI collapses the AI-developer pattern. The old shape was “spin up a model server, export data, write Python, manage tokens.” The new shape is “SELECT a function from a worksheet.” Once that shift is internalized, Cortex exam questions reduce to two recognition tasks: name-to-purpose mapping and trap detection. No Python required, region-bound, SENTIMENT-is-a-number.
Tomorrow (Day 17): Cortex Analyst + Snowflake ML. We close sub-objective 1.6 with two more pieces of the AI stack. Cortex Analyst is natural-language ↔ SQL translation driven by a semantic-model YAML file. Snowflake ML provides SQL-callable ML built-ins like FORECAST, DETECT_ANOMALIES, and classification, plus the Model Registry where trained models become first-class database objects. Then Day 18 starts Domain 2 with RBAC + DAC foundations, the bedrock of Snowflake security.
