I Built a Snowflake RAG Assistant That Actually Works in Production

A developer built a production RAG help assistant on Snowflake infrastructure to reduce user support tickets for a complex B2B SaaS product. The system combines a Snowflake Agent as controller, Cortex Search for embeddings retrieval, agent tools, Snowflake tables for normalized product knowledge, stages for raw documents, and Snowflake Openflow to sync documents from Microsoft SharePoint. The developer emphasized clear instructions, budget limits, fallback behavior, and minimizing non-deterministic failure points to move from PoC to production in weeks.

I was given a task to build a help assistant that could reduce the number of support tickets raised by our users. We have a complex B2B SaaS product with many features, company specific rules and business processes. It should understand the user question and find the most relevant information from our internal docs. Sounds like a trivial RAG, right? So I decided to use an existing Snowflake infrastructure to bring this into the life. Today, it is common to hear that agents should be replaced with deterministic workflows, pipelines, or APIs because agents are non-deterministic and can waste tokens or make the wrong decision. I think the more practical view is that the result depends heavily on how you use the technology and what practicies are you applying. In my case, I needed to move from PoC to production fast weeks, not months , so using a Snowflake Agent was a pragmatic choice. I focused on keeping the instructions clear, limiting the budget, defining fallback behavior, and reducing the places where non-deterministic behavior could become a real problem. Another benefit was staying inside Snowflake’s existing agentic ecosystem. It makes it easier to integrate with other Snowflake-based products and services later, reuse the same security and data environment, and avoid building separate infrastructure for the new AI capability. Let's start from the most important and exciting part - architecting. The main building blocks for our agent are: Snowflake Agent - the controller that decides how to answer and what tool to use, with the instructions, format rules and auto orchestration, Cortex Search — to store and retrieve embeddings of our product knowledge, Agent tools - so agent can access and use Cortex Search to retrieve required context, Snowflake tables — to store product knowledge in a normalized, structured form. Selected columns, such as content, are then used as the SEARCH TEXT source for Cortex Search, Stages - to store raw documents, Snowflake Openflow - to sync documents from Microsoft SharePoint into Snowflake Stages. Let’s visualize the overall flow first, and then I’ll go through each part in more detail. Question/Answer flow Injection of Product knowledge flow Now that the architecture is clear, let’s move to the implementation. I’ll start with the Snowflake side, beginning with access control, because the agent, Cortex Search, and API integration all depend on the right permissions being in place. First, make sure your current role has permissions to create new objects like DATABASES, SCHEMAS and Cortex Search Service ask someone with ACCOUNTADMIN access to grant you required roles. Also, it would be much cleaner to create a new set of roles for managing our objects. Snowflake has an article about about access control https://docs.snowflake.com/en/user-guide/security-access-control-overview Let's create a table to store our product knowledge: CREATE TABLE IF NOT EXISTS <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA DOC PATH VARCHAR, -- source filename DOC TYPE VARCHAR, -- 'pdf', 'docx', useful for filtering or groupping CHUNK INDEX NUMBER 38,0 , -- the index of the chunk per document CONTENT VARCHAR, INGESTED AT TIMESTAMP NTZ DEFAULT CURRENT TIMESTAMP ; So, this table serves as the basis for the data we will pass to the agent when the user asks a question. For example if users asked about account management or how to use feature X, we can query the ingested content and return an exact chunk of information. Later, I will show how we can use SNOWFLAKE.CORTEX.PARSE DOCUMENT to automatically extract text from documents inside stage. Next, we create the Cortex Search service; I have a separate article https://dev.to/artemooon/building-production-ready-semantic-search-with-python-and-snowflake-cortex-42a7 in which I examine CSS in detail. CREATE OR REPLACE CORTEX SEARCH SERVICE <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA CSS ON CONTENT ATTRIBUTES DOC PATH, DOC TYPE, CHUNK INDEX WAREHOUSE = <YOUR WAREHOUSE NAME TARGET LAG = '1 hour' EMBEDDING MODEL = 'snowflake-arctic-embed-l-v2.0' AS SELECT DOC PATH, DOC TYPE, CHUNK INDEX, CONTENT FROM <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA ; Cortex Search keeps the service synchronized with our PRODUCT DATA table. New or updated rows are indexed asynchronously, so fresh content can take up to the configured TARGET LAG — in this case, one hour — before embeddings are generated and the data becomes available to the agent through Cortex Search. If you’re building a multilingual agent, choose an EMBEDDING MODEL https://docs.snowflake.com/en/user-guide/snowflake-cortex/vector-embeddings text-embedding-models that supports the languages your users and product knowledge use. Otherwise, semantic retrieval quality can degrade significantly for non-English queries or content. Now time to create our Snowflake Agent. We can always modify agent rules in place without recreating an agent, so feel free to experiment with the instructions to get the best output. CREATE OR REPLACE AGENT <YOUR DB NAME .<YOUR SCHEMA NAME .AI HELP AGENT COMMENT = "My AI Help Agent" PROFILE = '{"display name": "Product Assistant", "color": "green"}' FROM SPECIFICATION $$ models: orchestration: auto orchestration: budget: Adjust according to your needs and budget. seconds: 30 tokens: 20000 instructions: response: | You are a friendly, helpful support assistant for the <YOUR APP NAME . Users ask about any aspect of the platform — features, settings, workflows, and processes. Always search before deciding something is out of scope. tools: - tool spec: type: "cortex search" name: "HelpSearch" description: | Searches curated answers and process documentation for the <application name . Covers all documented platform features, settings, workflows, and processes. Use this tool for ANY question that could relate to the <application name platform — always search before deciding something is out of scope. tool resources: HelpSearch: name: "<YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA CSS" max results: "8" title column: "DOC PATH" columns and descriptions: CONTENT: description: "FAQ answer or process-document text about a procurement workflow." type: "string" searchable: true filterable: false DOC PATH: description: "Source: 'FAQ' for curated Q&A, or the source document filename." type: "string" searchable: false filterable: true DOC TYPE: description: "Content type. Values: 'faq', 'pdf', 'docx'" type: "string" searchable: false filterable: true $$ Let's examine each parameter in detail. Orchestration controls how the agent plans and uses tools. budget.seconds and budget.tokens are probably the most important safety settings here. They are the real stop button that prevents the agent from running for too long, burning too many tokens, or continuing to speculate when it gets lost. Instructions are where most of the agent behavior is defined. Keep them explicit and easy to follow: define the agent’s role, what it should and should NOT answer, guardrails, escalation rules, response format, internal terminology, and how it should behave when information is missing. Avoid huge prompts with duplicated rules — redundancy often makes behavior less predictable. Tools give the agent access to external capabilities. In this case, Cortex Search is the main knowledge tool, so its description should clearly tell the agent when to use it and what kind of information it contains. Models define which model is used for orchestration and reasoning. This can affect quality, latency, and cost, so it is worth testing different configurations against real support questions rather than choosing only by benchmark performance. The querying user’s our Django API default role must have privileges not only on the Agent, but also on every tool the Agent may use, so we need to run GRANT USAGE for CORTEX SEARCH SERVICE. GRANT USAGE ON AGENT <YOUR DB NAME .<YOUR SCHEMA NAME .AI HELP AGENT TO ROLE <YOUR API USER ROLE ; GRANT USAGE ON CORTEX SEARCH SERVICE <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA CSS TO ROLE <YOUR API USER ROLE ; Another interesting and important case is when the agent cannot find a reliable answer in the knowledge base. In this situation, a clear fallback instead of guessing would be much better outcome: the agent can return a predefined marker together with a short user-facing message, and then frontend can parses that marker to show a Helpdesk/Support action directly in the chat. For example we can add in our instructions a new rule: RULES: 1. If the retrieved content says "Not yet documented" or you cannot find a relevant answer, keep it simple and straight: do NOT say this isn't documented. Do not guess at the process. Then, as the LAST thing in your reply, on its own line, with nothing after it, append the literal text NEEDS HELP — exactly as written, always in English, never translated, never explained, never wrapped in quotes or formatting. The calling application detects this exact token. It must appear every time you give this kind of answer, Follow the principle of least privilege. Do not rely on agent instructions alone to protect sensitive data or actions — if the agent should never access something, do not grant its role access to it in the first place. Do not allow the agent to access confidential information. Instead, the information accessible to the agent should be sanitized, making it useful without revealing confidential data. Agent permissions use the caller’s default role, not just whatever role is active in the session. That default role needs access to the Agent, warehouse, database/schema, and every tool the Agent may call. USAGE on the Agent is not enough. If Cortex Search is configured as a tool, the caller’s role also needs USAGE on that Cortex Search Service; otherwise the tool may be unavailable during the run. Another problem was: how can I let our non-technical team members upload new knowledge into the agent? Fortunately, Snowflake has already taken care of this and provided the Openflow service https://docs.snowflake.com/en/user-guide/data-integration/openflow/about . It is an integration service that connects different data sources and destinations, with hundreds of processors supporting structured and unstructured text, images, audio, video, and sensor data. This is exactly what we need. Openflow already provides many built-in connectors for different services, so we do not need to write and maintain a custom ETL pipeline. However, we still need to configure and set up the connector for our specific source and destination. It was a whole other story to make SharePoint and Openflow work together, but kudos to this amazing article https://olujonathan.medium.com/connect-sharepoint-to-snowflake-via-openflow-6b7e1de08cd1 — it helped me set up the connection. Just go through it; everything is clearly explained there. Make sure you have the Global Administrator or SharePoint Administrator role granted so you can create the required API permissions. After this you should have a DOCUMENTS stage created by Openflow, where we will store raw documents from SharePoint very similar to S3 , also we can organises different folders structure, to restrict or group files. Have you done the Snowflake integration? Amazing job Now let's move to the automatic documents parsing. I have used SNOWFLAKE.CORTEX.PARSE DOCUMENT , which is now deprecated and Snowflake suggest to use new function AI PARSE DOCUMENT . It returns the extracted content from a document on a Snowflake stage as a JSON-formatted string, exactly what we need for our PRODUCT DATA table. We can create a procedure to parse documents into our DB rows, so we can reuse and run the same code multiple times. CREATE OR REPLACE PROCEDURE <YOUR DB NAME .<YOUR SCHEMA NAME .SP PRODUCT DATA INGEST RETURNS STRING LANGUAGE SQL EXECUTE AS CALLER AS $$ DECLARE ingested INTEGER DEFAULT 0; BEGIN -- Consume the stream so its offset advances. CREATE OR REPLACE TEMPORARY TABLE stream consumed AS SELECT FROM <YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS STREAM; ALTER STAGE <YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS REFRESH; CREATE OR REPLACE TEMPORARY TABLE to ingest AS WITH latest AS SELECT DOC PATH, MAX INGESTED AT AS ingested at FROM <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA GROUP BY DOC PATH SELECT d.RELATIVE PATH AS doc path FROM DIRECTORY @<YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS d LEFT JOIN latest l ON l.DOC PATH = d.RELATIVE PATH WHERE d.RELATIVE PATH ILIKE '%.pdf' OR d.RELATIVE PATH ILIKE '%.docx' AND l.DOC PATH IS NULL OR TO TIMESTAMP NTZ CONVERT TIMEZONE 'UTC', d.LAST MODIFIED l.ingested at ; -- Remove old chunks when an existing document is updated. DELETE FROM <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA WHERE DOC PATH IN SELECT doc path FROM to ingest ; INSERT INTO <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA DOC PATH, DOC TYPE, CHUNK INDEX, CONTENT, INGESTED AT WITH parsed AS SELECT RELATIVE PATH AS doc path, LOWER SPLIT PART RELATIVE PATH, '.', -1 AS doc type, SNOWFLAKE.CORTEX.PARSE DOCUMENT @<YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS, RELATIVE PATH, {'mode': 'LAYOUT'} :content::string AS full text FROM DIRECTORY @<YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS WHERE RELATIVE PATH IN SELECT doc path FROM to ingest , chunked AS SELECT doc path, doc type, SNOWFLAKE.CORTEX.SPLIT TEXT RECURSIVE CHARACTER full text, 'markdown', 1000, 200 AS chunks FROM parsed SELECT doc path, doc type, chunk.index::NUMBER, chunk.value::VARCHAR, CURRENT TIMESTAMP FROM chunked, LATERAL FLATTEN input = chunks AS chunk WHERE TRIM chunk.value::VARCHAR = ''; ingested := SQLROWCOUNT; RETURN 'Chunks inserted: ' || ingested; END; $$; The procedure reads documents from our Stage, extracts their content using PARSE DOCUMENT , splits the extracted text into smaller chunks, and stores every chunk as a separate row in PRODUCT DATA . This table then becomes the source for our Cortex Search service. LAYOUT is a PARSE DOCUMENT mode that tries to preserve the document’s structure, instead of returning only plain extracted text. That is helpful before chunking, because the chunks keep more meaningful context from the original PDF or DOCX file. In SPLIT TEXT RECURSIVE CHARACTER full text, 'markdown', 1000, 200 , 1000 keeps each chunk short enough for Cortex Search to match a specific part of the document instead of one large block of text. 200 overlaps the chunks, so some context is repeated between them and important information is less likely to be lost at the split point. Moreover, we can run the procedure only when new documents are detected in the DOCUMENTS stage by combining a Stream with a triggered Task. This avoids running the ingestion procedure on a fixed schedule when there is nothing new to process. Streams were invented exactly for this, let's generate a new one: CREATE OR REPLACE STREAM <YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS STREAM ON STAGE <YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS; Then create a task that runs only when the stream has data: CREATE OR REPLACE TASK <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA INGEST TASK WAREHOUSE = <YOUR WAREHOUSE NAME WHEN SYSTEM$STREAM HAS DATA '<YOUR DB NAME .<YOUR SCHEMA NAME .DOCUMENTS STREAM' AS CALL <YOUR DB NAME .<YOUR SCHEMA NAME .SP PRODUCT DATA INGEST ; ALTER TASK <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA INGEST TASK RESUME; Make sure that task is resumed after creation and you can test it by running CALL <YOUR DB NAME .<YOUR SCHEMA NAME .PRODUCT DATA INGEST TASK ; The important part here is that SYSTEM$STREAM HAS DATA only checks whether the stream has changes — it does not consume them. If the procedure never reads from the stream in a DML context, the stream offset does not advance and the stream can eventually become stale Snowflake has an Agent Preview feature that lets you test your agent directly through a chat interface before connecting it to your backend. You can benchmark it with real questions and use cases, inspect tool usage, and see how the agent behaves in different scenarios. Also test repeatability by running the same question several times and checking whether the agent still chooses the right tools and produces consistently useful answers. Then test failure cases on purpose: missing knowledge, weak search results, permission errors, timeouts, and large inputs — the important part is that the agent fails safely instead of guessing. Personally, I find it very interesting and enjoyable to test the created agents using the Snowflake Preview interface. It provides a good understanding of how an agent performs before it is deployed to the production. Well done if you read all the way to the end We built more than just a RAG chatbot. We created a full flow for syncing product knowledge from SharePoint, processing and chunking documents in Snowflake, indexing them with Cortex Search, and exposing that knowledge through an Agent with guardrails, fallback behavior, and controlled permissions. The main takeaway for me is that making an AI assistant production-ready is not mostly about prompts or the model itself. The important parts are the surrounding engineering: access control, clean knowledge ingestion, good retrieval, strict budgets, safe fallback behavior, testing, and knowing when the agent should stop and hand the user over to a human.