Capstone: Enterprise Search with RAG
Project Overview
Build an enterprise search system that combines Retrieval-Augmented Generation (RAG) with structured data analytics. Users ask questions in natural language and get answers grounded in company documents and database records. The system uses Cortex Search for document retrieval, AI_COMPLETE for generation, and a Cortex Agent that orchestrates both.
Architecture
User Question
│
▼
Cortex Agent
│
├──► Cortex Search Service ──► Retrieve relevant document chunks
│
├──► Cortex Analyst ──► Query structured data via semantic view
│
▼
AI_COMPLETE synthesizes answer from all retrieved context
Step 1: Build the Knowledge Base
Prepare Documents
-- Create a stage for company documents
CREATE OR REPLACE STAGE kb_db.public.company_docs
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');
-- Upload documents (policies, handbooks, product docs, FAQs)
-- PUT file:///path/to/docs/*.pdf @kb_db.public.company_docs;
Parse and Chunk Documents
-- Parse documents into text
CREATE OR REPLACE TABLE kb_db.public.parsed_docs AS
SELECT
RELATIVE_PATH AS source_file,
SNOWFLAKE.CORTEX.AI_PARSE_DOCUMENT(
BUILD_SCOPED_FILE_URL(@kb_db.public.company_docs, RELATIVE_PATH),
{'mode': 'LAYOUT'}
):content::VARCHAR AS full_text
FROM DIRECTORY(@kb_db.public.company_docs)
WHERE RELATIVE_PATH LIKE '%.pdf';
-- Chunk documents for search indexing
CREATE OR REPLACE TABLE kb_db.public.doc_chunks AS
SELECT
source_file,
chunk.value::VARCHAR AS chunk_text,
chunk.index AS chunk_index
FROM kb_db.public.parsed_docs,
LATERAL FLATTEN(
INPUT => SNOWFLAKE.CORTEX.SPLIT_TEXT_RECURSIVE_CHARACTER(
full_text, 'markdown', { 'chunk_size': 1500, 'chunk_overlap': 200 }
)
) AS chunk;
Step 2: Create Cortex Search Service
CREATE OR REPLACE CORTEX SEARCH SERVICE kb_db.public.knowledge_base_search
ON chunk_text
ATTRIBUTES source_file
WAREHOUSE = compute_wh
TARGET_LAG = '1 hour'
AS (
SELECT
chunk_text,
source_file,
chunk_index
FROM kb_db.public.doc_chunks
);
Step 3: Implement RAG with AI_COMPLETE
Before building the full agent, test the RAG pattern directly:
-- Search for relevant context
SET search_results = (
SELECT ARRAY_AGG(chunk_text) AS chunks
FROM TABLE(
kb_db.public.knowledge_base_search!SEARCH(
QUERY => 'What is the company refund policy?',
COLUMNS => ['chunk_text', 'source_file'],
LIMIT => 5
)
)
);
-- Generate answer using retrieved context
SELECT SNOWFLAKE.CORTEX.AI_COMPLETE(
'claude-3.5-sonnet',
CONCAT(
'Answer the following question based only on the provided context. ',
'If the context does not contain the answer, say so.\n\n',
'Context:\n', $search_results::VARCHAR, '\n\n',
'Question: What is the company refund policy?\n\n',
'Answer:'
)
) AS answer;
Step 4: Create a Semantic View for Structured Data
CREATE OR REPLACE SEMANTIC VIEW kb_db.public.business_sv AS $$
name: business_analytics
tables:
- name: orders
base_table:
database: kb_db
schema: public
table: fact_orders
dimensions:
- name: product_name
expr: product_name
description: "Name of the product"
- name: customer_segment
expr: segment
description: "Customer segment: Enterprise, SMB, Consumer"
measures:
- name: total_revenue
expr: SUM(amount)
description: "Total revenue from orders"
default_aggregation: sum
- name: order_count
expr: COUNT(*)
description: "Number of orders"
default_aggregation: count
time_dimensions:
- name: order_date
expr: order_date
description: "Date the order was placed"
data_type: date
$$;
Step 5: Build the Cortex Agent
CREATE OR REPLACE CORTEX AGENT kb_db.public.enterprise_assistant
MODEL = 'claude-3.5-sonnet'
TOOLS = (
CORTEX_SEARCH_TOOL(
search_service => 'kb_db.public.knowledge_base_search'
),
SEMANTIC_VIEW_TOOL(
semantic_view => 'kb_db.public.business_sv'
)
)
SYSTEM_PROMPT = '
You are an enterprise assistant. You can answer questions using:
1. Company documents (policies, handbooks, product docs) via search
2. Business data (orders, revenue, customer metrics) via SQL
Combine both sources when needed. Always cite document sources.
For data questions, include specific numbers.
';
Step 6: Streamlit Chat Interface
import streamlit as st
from snowflake.snowpark.context import get_active_session
session = get_active_session()
st.title("Enterprise Knowledge Assistant")
st.caption("Ask about company policies, product docs, or business metrics")
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"])
if prompt := st.chat_input("What would you like to know?"):
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 and analyzing..."):
result = session.sql(f"""
SELECT SNOWFLAKE.CORTEX.AGENT(
'{prompt.replace("'", "''")}',
AGENT => 'kb_db.public.enterprise_assistant'
)
""").collect()
answer = result[0][0]
st.markdown(answer)
st.session_state.messages.append({"role": "assistant", "content": answer})
Testing the System
| Test Question | Expected Tool(s) | Validates |
|---|---|---|
| "What is our refund policy?" | Cortex Search | Document retrieval |
| "What was revenue last quarter?" | Cortex Analyst | SQL generation |
| "How does our warranty policy affect return rates?" | Both | Multi-tool reasoning |
| "Who is our top customer?" | Cortex Analyst | Structured data query |
| "What onboarding steps does our handbook describe?" | Cortex Search | Text retrieval |
Keeping the Knowledge Base Fresh
-- Create a task to re-chunk new documents periodically
CREATE OR REPLACE TASK kb_db.public.refresh_knowledge_base
WAREHOUSE = compute_wh
SCHEDULE = 'USING CRON 0 2 * * * America/New_York'
AS
BEGIN
ALTER STAGE kb_db.public.company_docs REFRESH;
-- Re-parse and re-chunk new documents
-- Cortex Search Service auto-refreshes based on TARGET_LAG
END;
ALTER TASK kb_db.public.refresh_knowledge_base RESUME;
Summary
This capstone built an enterprise search system combining RAG (Cortex Search + AI_COMPLETE) with structured data analytics (Cortex Analyst) through a Cortex Agent. The agent intelligently routes questions to the right tool and synthesizes answers from both document and database sources.