Building Intelligent Apps with Streamlit & Cortex
Streamlit in Snowflake Overview
Streamlit in Snowflake (SiS) lets you build interactive Python applications that run entirely inside your Snowflake account. There’s no external server to manage — the app runs on Snowflake compute, has native access to your data, and inherits your RBAC model.
You create a Streamlit app by uploading Python files to a stage and registering the app with DDL:
-- Create a stage for app files
CREATE OR REPLACE STAGE analytics_db.public.streamlit_stage
DIRECTORY = (ENABLE = TRUE);
-- Upload app file
PUT file:///path/to/app.py @analytics_db.public.streamlit_stage
AUTO_COMPRESS = FALSE
OVERWRITE = TRUE;
-- Create the Streamlit app
CREATE OR REPLACE STREAMLIT analytics_db.public.sales_chat_app
ROOT_LOCATION = '@analytics_db.public.streamlit_stage'
MAIN_FILE = 'app.py'
QUERY_WAREHOUSE = app_wh;
Once created, the app is accessible in Snowsight. Users with the right grants can open it directly in the browser.
Building a Chat Interface with AI_COMPLETE
The simplest intelligent app is a chat interface backed by a Cortex LLM. Here’s the core pattern:
import streamlit as st
from snowflake.snowpark.context import get_active_session
session = get_active_session()
st.title("AI Assistant")
# Initialize conversation history
if "messages" not in st.session_state:
st.session_state.messages = []
# Render existing messages
for msg in st.session_state.messages:
with st.chat_message(msg["role"]):
st.markdown(msg["content"])
# Handle new input
if prompt := st.chat_input("Ask me anything..."):
st.session_state.messages.append({"role": "user", "content": prompt})
with st.chat_message("user"):
st.markdown(prompt)
with st.chat_message("assistant"):
with st.spinner("Thinking..."):
result = session.sql(f"""
SELECT SNOWFLAKE.CORTEX.AI_COMPLETE(
'mistral-large2',
'{prompt.replace("'", "''")}'
)
""").collect()
answer = result[0][0]
st.markdown(answer)
st.session_state.messages.append({"role": "assistant", "content": answer})
This gives you a working chat UI in about 30 lines. But without grounding, the model can only answer from its training data. The next sections add grounding via search and structured data.
Integrating Cortex Search for RAG
Retrieval-Augmented Generation (RAG) means: search first, then generate. You query Cortex Search to find relevant documents, then pass those documents as context to the LLM.
import streamlit as st
import json
from snowflake.snowpark.context import get_active_session
session = get_active_session()
st.title("Product Documentation Assistant")
if "messages" not in st.session_state:
st.session_state.messages = []
for msg in st.session_state.messages:
with st.chat_message(msg["role"]):
st.markdown(msg["content"])
def search_docs(query: str, limit: int = 3) -> list:
"""Retrieve relevant documents from Cortex Search."""
search_query = json.dumps({
"query": query,
"columns": ["doc_title", "doc_text"],
"limit": limit
})
result = session.sql(f"""
SELECT SNOWFLAKE.CORTEX.SEARCH(
'analytics_db.public.product_docs_search',
'{search_query}'
)
""").collect()
return json.loads(result[0][0])["results"]
def generate_answer(question: str, context_docs: list) -> str:
"""Generate an answer grounded in retrieved documents."""
context = "\n\n---\n\n".join(
f"**{doc['doc_title']}**\n{doc['doc_text']}" for doc in context_docs
)
prompt_text = f"""Answer the question based on the provided context.
If the context does not contain enough information, say so.
Context:
{context}
Question: {question}"""
result = session.sql(f"""
SELECT SNOWFLAKE.CORTEX.AI_COMPLETE(
'mistral-large2',
'{prompt_text.replace("'", "''")}'
)
""").collect()
return result[0][0]
if prompt := st.chat_input("Ask about our products..."):
st.session_state.messages.append({"role": "user", "content": prompt})
with st.chat_message("user"):
st.markdown(prompt)
with st.chat_message("assistant"):
with st.spinner("Searching documentation..."):
docs = search_docs(prompt)
with st.spinner("Generating answer..."):
answer = generate_answer(prompt, docs)
st.markdown(answer)
# Show sources in an expander
with st.expander("Sources"):
for doc in docs:
st.markdown(f"- **{doc['doc_title']}**")
st.session_state.messages.append({"role": "assistant", "content": answer})
The pattern is: Search -> Retrieve -> Augment prompt -> Generate. The LLM never sees your entire document corpus — only the relevant chunks.
Integrating Cortex Analyst for Data Q&A
For structured data questions ("What was revenue last quarter?"), use Cortex Analyst instead of search:
def query_data(question: str) -> dict:
"""Send a natural language question to Cortex Analyst."""
result = session.sql(f"""
SELECT SNOWFLAKE.CORTEX.ANALYST(
'{question.replace("'", "''")}',
SEMANTIC_VIEW_REFERENCE => 'analytics_db.public.sales_sv'
)
""").collect()
return json.loads(result[0][0])
You can combine both in a single app by routing questions:
if prompt := st.chat_input("Ask about data or documentation..."):
st.session_state.messages.append({"role": "user", "content": prompt})
with st.chat_message("user"):
st.markdown(prompt)
with st.chat_message("assistant"):
# Simple keyword-based routing (a real app might use an LLM classifier)
data_keywords = ["revenue", "sales", "orders", "total", "count", "average"]
is_data_question = any(kw in prompt.lower() for kw in data_keywords)
if is_data_question:
with st.spinner("Querying sales data..."):
result = query_data(prompt)
st.markdown(result.get("interpretation", ""))
if "results" in result:
st.dataframe(result["results"])
else:
with st.spinner("Searching documentation..."):
docs = search_docs(prompt)
answer = generate_answer(prompt, docs)
st.markdown(answer)
For more sophisticated routing, use a Cortex Agent instead of manual keyword matching — the agent handles tool selection automatically.
Session State Patterns
Managing state correctly is critical in Streamlit apps. Here are the patterns you’ll use most often:
| Pattern | Code | When to Use |
|---|---|---|
| Conversation history | st.session_state.messages = [] | Chat interfaces — stores the full message list |
| Toggle flag | st.session_state.show_sources = False | Show/hide UI sections |
| Cached query result | st.session_state.last_result = None | Avoid re-running expensive queries on rerun |
| User preferences | st.session_state.model = "mistral-large2" | Let users pick model, limit, or other settings |
| Form data | st.session_state.filters = {} | Multi-step forms with filter selections |
Important: Streamlit reruns the entire script on every interaction. Any variable not stored in st.session_state is lost between reruns.
Deployment and Sharing
Full deployment sequence
-- 1. Create a dedicated stage
CREATE OR REPLACE STAGE analytics_db.public.chat_app_stage
DIRECTORY = (ENABLE = TRUE);
-- 2. Upload application files
PUT file:///local/path/app.py @analytics_db.public.chat_app_stage
AUTO_COMPRESS = FALSE OVERWRITE = TRUE;
PUT file:///local/path/utils.py @analytics_db.public.chat_app_stage
AUTO_COMPRESS = FALSE OVERWRITE = TRUE;
-- 3. Upload requirements if needed
PUT file:///local/path/environment.yml @analytics_db.public.chat_app_stage
AUTO_COMPRESS = FALSE OVERWRITE = TRUE;
-- 4. Create the Streamlit app
CREATE OR REPLACE STREAMLIT analytics_db.public.product_assistant
ROOT_LOCATION = '@analytics_db.public.chat_app_stage'
MAIN_FILE = 'app.py'
QUERY_WAREHOUSE = app_wh
COMMENT = 'Product documentation and sales data assistant';
-- 5. Grant access to users
GRANT USAGE ON STREAMLIT analytics_db.public.product_assistant
TO ROLE analyst_role;
Updating a deployed app
-- Upload the updated file (OVERWRITE = TRUE replaces the existing file)
PUT file:///local/path/app.py @analytics_db.public.chat_app_stage
AUTO_COMPRESS = FALSE OVERWRITE = TRUE;
-- Users see the new version on their next page load
No restart command is needed. Snowflake picks up the new file automatically.
Best Practices
Use st.spinner for long operations
Every Cortex call takes at least a second or two. Always wrap them in a spinner so the user knows something is happening:
with st.spinner("Searching..."):
results = search_docs(prompt)
Sanitize user input
Never pass raw user input directly into SQL strings. At minimum, escape single quotes:
safe_prompt = prompt.replace("'", "''")
For production apps, use parameterized queries or Snowpark DataFrame methods to avoid injection risks entirely.
Cache expensive operations
Use @st.cache_data for results that don’t change often:
@st.cache_data(ttl=3600)
def get_category_list():
return session.sql("SELECT DISTINCT category FROM product_documents").collect()
The ttl parameter sets cache lifetime in seconds. Use it for reference data, not for user-specific query results.
Handle errors gracefully
Cortex calls can fail — model timeouts, malformed queries, permission issues. Always wrap calls in try/except:
try:
result = session.sql(f"""
SELECT SNOWFLAKE.CORTEX.AI_COMPLETE('mistral-large2', '{safe_prompt}')
""").collect()
answer = result[0][0]
except Exception as e:
answer = f"Something went wrong: {str(e)}"
st.error(answer)
Limit context window usage
When building RAG prompts, don’t stuff every retrieved document into the context. Three to five relevant chunks is usually enough. More context means higher latency and cost, not necessarily better answers.
Keep the UI simple
Resist the urge to expose every configuration option. Most users want to ask a question and get an answer. Put advanced settings (model selection, result limits, debug info) behind an st.expander or st.sidebar.
Putting It All Together
A production-quality intelligent app typically combines:
- Cortex Search for document/knowledge retrieval
- Cortex Analyst for structured data queries
- AI_COMPLETE for generation and summarization
- Cortex Agent to orchestrate tool selection automatically
Start simple — a single search-backed chat. Add analyst integration when users need data answers. Graduate to an agent when you need the LLM to decide which tool to use.
Next Steps
This concludes Module 24. Continue to Module 25 to apply these components in capstone projects that combine semantic models, agents, search, and Streamlit into end-to-end solutions.