Skip to main content

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 QuestionExpected Tool(s)Validates
"What is our refund policy?"Cortex SearchDocument retrieval
"What was revenue last quarter?"Cortex AnalystSQL generation
"How does our warranty policy affect return rates?"BothMulti-tool reasoning
"Who is our top customer?"Cortex AnalystStructured data query
"What onboarding steps does our handbook describe?"Cortex SearchText 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.