By Deiby Gómez
One of the biggest challenges in working with data is giving non-technical users direct access without relying on Data Engineers or Analysts. While SQL remains the backbone of data querying, business users still need to extract insights quickly without writing code.
This is where Snowflake Cortex Analyst comes into play. It’s a built-in feature in Snowflake’s AI stack that enables Natural Language to SQL conversion, allowing users to ask questions like “What were the top 5 selling products in Q1?” directly. Cortex Analyst understands the intent behind the natural language query, generates an appropriate SQL statement, executes it, and returns the result in seconds. It reduces dependency on analysts and speeds up how teams get answers.
Cortex Analyst makes it easier for teams to get answers without writing SQL.
In this article, we’ll walk through how to set up and use Cortex Analyst with your Snowflake data. Whether you’re a Data Engineer building internal tools or working closely with cross-functional teams, this guide is designed to help you leverage AI in Snowflake to create a smarter, more interactive data environment.
Here’s what I built:
- Fact tables and dimensions
- Cortex Search Service
- Semantic layer file
- Python script to call Snowflake Cortex API
- Print the result in a Streamlit application
Creating the Fact Table and Dimension
I created the following tables and I loaded corresponding data:
-- Fact table: daily_revenue
CREATE OR REPLACE TABLE cortex_analyst.sales_timeseries.daily_sales (
date DATE,
sales FLOAT,
product_id VARCHAR(16777216),
store_id VARCHAR(16777216)
);
-- Dimension table: product_dim
CREATE OR REPLACE TABLE cortex_analyst.sales_timeseries.product_dim (
product_id VARCHAR(16777216),
product_desc VARCHAR(16777216),
product_line VARCHAR(16777216)
);
-- Dimension table: store_dim
CREATE OR REPLACE TABLE cortex_analyst.sales_timeseries.store_dim (
store_id VARCHAR(16777216),
store_region VARCHAR(16777216),
state VARCHAR(16777216)
);
Cortex Search Service
Cortex Search Service in Snowflake is a managed, AI-powered semantic search engine that allows users to perform natural language searches across structured and unstructured data by understanding the meaning behind queries. It enhances Cortex Analyst (which converts business questions in natural language into SQL queries) by enabling more accurate query generation and context-aware interpretations.
CREATE OR REPLACE CORTEX SEARCH SERVICE product_line_search_service
ON product_dimension
WAREHOUSE = cortex_analyst_wh
TARGET_LAG = '1 hour'
AS (
SELECT DISTINCT product_line AS product_dimension FROM cortex_analyst.sales_timeseries.product_dim
);
Semantic layer file:
The semantic model file in Snowflake Cortex Analyst is a structured YAML configuration that describes your data in business-friendly terms. It defines how tables, columns, relationships, metrics, and dimensions are used by Cortex Analyst when translating natural language questions into SQL. This file acts as a bridge between technical database structures and everyday business language, enabling more accurate and context-aware query generation. By explicitly modeling how data is organized and used, the semantic model file improves Cortex Analyst’s responses and empowers business users to ask questions naturally – without needing to understand SQL or the underlying schema.
name: Sales
tables:
- name: daily_sales
description: Daily total sales.
base_table:
database: cortex_analyst
schema: sales_timeseries
table: daily_sales
primary_key:
columns:
- date
- product_id
- store_id
time_dimensions:
- name: date
expr: date
description: date with measures of sales.
unique: true
data_type: date
measures:
- name: daily_sales
expr: sales
description: total sales for the given day
synonyms: ["sales", "income"]
default_aggregation: sum
data_type: number
dimensions:
- name: product_id
expr: product_id
data_type: varchar
- name: store_id
expr: store_id
data_type: varchar
- name: product
description: Product dimension table with unique product identifiers and attributes.
base_table:
database: cortex_analyst
schema: sales_timeseries
table: product_dim
primary_key:
columns:
- product_id
dimensions:
- name: product_id
expr: product_id
data_type: varchar
- name: product_line
expr: product_line
description: Product line associated with sales
data_type: varchar
sample_values:
- Electronics
- Clothing
- Home Appliances
- Toys
- Books
- name: store
description: store dimension table with unique store identifiers and geographic attributes.
base_table:
database: cortex_analyst
schema: sales_timeseries
table: store_dim
primary_key:
columns:
- store_id
dimensions:
- name: store_id
expr: store_id
data_type: varchar
- name: sales_store
expr: sales_store
description: store associated with sales
data_type: varchar
sample_values:
- North America
- Europe
- Asia
- South America
- Africa
- name: product_dimension
base_table:
database: cortex_analyst
schema: sales_timeseries
table: product_dim
dimensions:
- name: product_line
expr: product_line
cortex_search_service_name: product_line_search_service
data_type: varchar
relationships:
- name: sales_to_product
left_table: daily_sales
right_table: product
relationship_columns:
- left_column: product_id
right_column: product_id
join_type: left_outer
relationship_type: many_to_one
- name: sales_to_store
left_table: daily_sales
right_table: store
relationship_columns:
- left_column: store_id
right_column: store_id
join_type: left_outer
relationship_type: many_to_one
Python script to call Snowflake Cortex API
This Python code sends a natural language question to Snowflake Cortex Analyst using its REST API. It constructs a POST request to the /api/v2/cortex/analyst/message endpoint, including the user prompt (prompt) and a reference to a semantic model file (sales_timeseries.yaml) stored in a Snowflake stage. The semantic model helps Cortex Analyst understand the database structure and translate the user’s natural language query into accurate SQL. The request includes an authorization header with a Snowflake token.
HOST = "<host>"
DATABASE = "cortex_analyst"
SCHEMA = "sales_timeseries"
STAGE = "raw_data"
FILE = "sales_timeseries.yaml"
request_body = {
"messages": [{"role": "user", "content": [{"type": "text", "text": prompt}]}],
"semantic_model_file": f"@{DATABASE}.{SCHEMA}.{STAGE}/{FILE}",
}
resp = requests.post(
url=f"https://{HOST}/api/v2/cortex/analyst/message",
json=request_body,
headers={
"Authorization": f'Snowflake Token="{mytoken}"',
"Content-Type": "application/json",
},
)
request_id = resp.headers.get("X-Snowflake-Request-Id")
if resp.status_code < 400:
return {**resp.json(), "request_id": request_id}
else:
raise Exception(
f"Failed request (id: {request_id}) with status {resp.status_code}: {resp.text}"
)
Result:
The result shown demonstrates how Snowflake’s Cortex Analyst can accurately interpret a natural language question and generate a corresponding SQL query to retrieve the desired data. The question was “What is the total sales for January 2025?” Cortex Analyst translated it into a SQL query that calculates the sum of daily sales for that month using a DATE_TRUNC function to filter by January 2025. To validate the output, a manually written SQL query was also executed, filtering dates from ‘2025-01-01’ to ‘2025-02-01’. Both queries returned the same total sales value: 117,019,061.386, confirming that the AI-generated query is semantically and functionally correct. This confirms Cortex Analyst can handle real-world business questions using natural language.
Conclusion
As organizations strive to become more data-driven, bridging the gap between technical complexity and user accessibility becomes essential. Snowflake Cortex Analyst offers a solution by allowing natural language interactions with structured data—effectively making SQL optional for many use cases. It frees up Data Engineers from routine query support, allowing them to focus on building scalable data architectures and advanced analytics solutions.
By incorporating Cortex Analyst into your Snowflake ecosystem, you open the door to AI-driven insights, faster time to answers, and a more inclusive data culture across teams. Setup is simple. The shift in how people interact with data is a big deal. If you’re looking to unlock business intelligence for everyone in your organization, Cortex Analyst is a powerful step in that direction.
Resources:
- The Definitive Guide to Analyzing Queries in Snowflake
- How to Detect Query Profile Changes in Snowflake
- How to Analyze Snowflake Credit Consumption for Cost Management
- How to Quickly Identify User and Roles with ACCOUNTADMIN Privileges in Snowflake
- Snowflake-related blogs
- Snowflake Developer Resources
Deiby Gómez was the first Oracle ACE Director of Guatemala (2015-2023), he has 35 Technology certifications among them the highest certifications for Oracle Database (Oracle Certified Master, OCM) and three Advanced Snowflake Certifications (Architect, Administrator and Data Engineer). Deiby is an engineer with two master’s degrees and a future lawyer. He has spoken at Technology Events in more than 12 countries including Oracle Open World in San Francisco. Most Recently, Deiby was selected as a Snowflake Squad Member by Snowflake in 2025.
