# I Built a Snowflake RAG Assistant That Actually Works in Production > Source: > Published: 2026-09-26 18:45:20+00:00 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 ..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 ..PRODUCT_DATA_CSS ON CONTENT ATTRIBUTES DOC_PATH, DOC_TYPE, CHUNK_INDEX WAREHOUSE = TARGET_LAG = '1 hour' EMBEDDING_MODEL = 'snowflake-arctic-embed-l-v2.0' AS ( SELECT DOC_PATH, DOC_TYPE, CHUNK_INDEX, CONTENT FROM ..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 ..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 . 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 . Covers all documented platform features, settings, workflows, and processes. Use this tool for ANY question that could relate to the platform — always search before deciding something is out of scope. tool_resources: HelpSearch: 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 ..AI_HELP_AGENT TO ROLE ; GRANT USAGE ON CORTEX SEARCH SERVICE ..PRODUCT_DATA_CSS TO 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 ..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 ..DOCUMENTS_STREAM; ALTER STAGE ..DOCUMENTS REFRESH; CREATE OR REPLACE TEMPORARY TABLE _to_ingest AS WITH latest AS ( SELECT DOC_PATH, MAX(INGESTED_AT) AS ingested_at FROM ..PRODUCT_DATA GROUP BY DOC_PATH ) SELECT d.RELATIVE_PATH AS doc_path FROM DIRECTORY(@..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 ..PRODUCT_DATA WHERE DOC_PATH IN ( SELECT doc_path FROM _to_ingest ); INSERT INTO ..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( @..DOCUMENTS, RELATIVE_PATH, {'mode': 'LAYOUT'} ):content::string AS full_text FROM DIRECTORY(@..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 ..DOCUMENTS_STREAM ON STAGE ..DOCUMENTS; ``` Then create a task that runs only when the stream has data: ``` CREATE OR REPLACE TASK ..PRODUCT_DATA_INGEST_TASK WAREHOUSE = WHEN SYSTEM$STREAM_HAS_DATA('..DOCUMENTS_STREAM') AS CALL ..SP_PRODUCT_DATA_INGEST(); ALTER TASK ..PRODUCT_DATA_INGEST_TASK RESUME; ``` Make sure that task is resumed after creation and you can test it by running `CALL ..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.