Skip to main content

RAG Architecture Fundamentals

Foundation Models (like Llama 3) are incredibly smart, but they suffer from two fatal flaws when used in an enterprise setting:

  1. They hallucinate: If they don't know the answer, they often confidently make one up.
  2. They lack private context: They were trained on the public internet. They know what a "laptop" is, but they have no idea what your company's specific laptop upgrade policy is.

To solve this, we use RAG (Retrieval-Augmented Generation).

RAG stops the LLM from relying on its own memory. Instead, when a user asks a question, we retrieve the exact internal documents related to their question, and we augment the prompt by giving those documents to the LLM to read before it generates an answer.


️ The RAG Pipeline​

A standard RAG pipeline consists of 4 steps. In Snowflake, we can do all 4 steps entirely in SQL without moving any data!

  1. Chunking: Break large company documents into smaller paragraphs (chunks).
  2. Embedding: Convert those chunks into Vectors using CORTEX.EMBED_TEXT.
  3. Retrieval: Use Semantic Search (VECTOR_COSINE_SIMILARITY) to find the chunks most relevant to the user's query.
  4. Generation: Pass the user's query and the retrieved chunks to CORTEX.COMPLETE to generate a final answer.

We already covered Steps 1, 2, and 3 in the previous module. Now, let's tie it all together with Step 4.


‍ The Complete SQL Implementation​

Assume we have our KNOWLEDGE_BASE table populated with our company's IT and HR policies, along with their embeddings.

A user asks: "What is the policy for getting a new computer?"

Step 1: The RAG Query​

We will write a CTE (Common Table Expression) that first retrieves the most relevant document, and then passes it to the LLM.

-- Define the user's question
SET user_question = 'What is the policy for getting a new computer?';

WITH RELEVANT_DOCS AS (
-- 1. RETRIEVAL PHASE: Find the Top 1 most relevant document chunk
SELECT
TEXT_CONTENT,
VECTOR_COSINE_SIMILARITY(
TEXT_EMBEDDING,
SNOWFLAKE.CORTEX.EMBED_TEXT('snowflake-arctic-embed-m', $user_question)
) AS SIM_SCORE
FROM KNOWLEDGE_BASE
ORDER BY SIM_SCORE DESC
LIMIT 1
)
-- 2. GENERATION PHASE: Inject the retrieved document into the LLM prompt
SELECT
SNOWFLAKE.CORTEX.COMPLETE(
'llama3-8b',
CONCAT(
'You are a helpful IT assistant. Answer the user question using ONLY the provided context document. Do not hallucinate or make up policies.',
'\n\nContext Document: ', (SELECT TEXT_CONTENT FROM RELEVANT_DOCS),
'\n\nUser Question: ', $user_question
)
) AS RAG_ANSWER;

Step 2: The LLM's Thought Process​

Behind the scenes, the CONCAT() function builds the following final prompt, which is sent to Llama 3:

"You are a helpful IT assistant. Answer the user question using ONLY the provided context document. Do not hallucinate or make up policies.

Context Document: To request a replacement laptop, submit a ticket to the IT Helpdesk via Jira.

User Question: What is the policy for getting a new computer?"

Step 3: Expected Outcome​

The LLM reads the context document, understands that a "new computer" means a "replacement laptop," and generates a natural, highly accurate response based exclusively on your private data:

RAG_ANSWER: "To get a new computer (replacement laptop), you need to submit a ticket to the IT Helpdesk using Jira."


Benefits of RAG in Snowflake​

By building your RAG architecture entirely within Snowflake using SQL:

  • Zero Egress: Your sensitive IT policies, HR manuals, and customer data never leave Snowflake.
  • Simplicity: No complex vector databases (like Pinecone) or external orchestration frameworks (like LangChain) are required. Everything is just tables, functions, and SQL.
  • Accuracy: The LLM is forced to cite your actual data, eliminating hallucinations.

You now have a fully functional Generative AI application running natively on your data warehouse!