AI agents & Snowflake: Hands-on Native Agent Orchestration / Agent Gateway

-- ========================================
-- Set active role with full admin privileges
-- ========================================
USE ROLE ACCOUNTADMIN; 

-- ========================================
-- Create database and schema for storing agent data
-- ========================================
CREATE DATABASE IF NOT EXISTS agent_gateway;
USE DATABASE agent_gateway;

CREATE SCHEMA IF NOT EXISTS ag_schema;

-- ========================================
-- Create a virtual warehouse to process queries and tasks
-- ========================================
CREATE OR REPLACE WAREHOUSE agent_wh;
USE WAREHOUSE agent_wh;

-- ========================================
-- Create custom Cortex role and link it to Snowflake Cortex privileges
-- ========================================
CREATE OR REPLACE ROLE cortex_user_role;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE cortex_user_role;

-- ========================================
-- Create users and assign roles
-- ========================================
CREATE OR REPLACE USER agent_user;
GRANT ROLE cortex_user_role TO USER agent_user;

-- Assign to your user (replace with real username if needed)
GRANT ROLE cortex_user_role TO USER mheino;

-- ========================================
-- Grant necessary warehouse permissions
-- ========================================
GRANT USAGE ON WAREHOUSE agent_wh TO ROLE cortex_user_role;
GRANT OPERATE ON WAREHOUSE agent_wh TO ROLE cortex_user_role;

-- ========================================
-- Grant full access to the database and schema
-- ========================================
GRANT ALL ON DATABASE agent_gateway TO ROLE cortex_user_role;
GRANT ALL ON SCHEMA ag_schema TO ROLE cortex_user_role;

-- ========================================
-- Set password for agent_user (not secure for prod environments)
-- ========================================
ALTER USER agent_user
SET PASSWORD = 'agent_user' MUST_CHANGE_PASSWORD = FALSE;

-- ========================================
-- Switch to cortex role to begin resource creation
-- ========================================
USE ROLE cortex_user_role;

-- ========================================
-- Create external stages for PDF documents and repair manuals
-- These are used as file storage sources
-- ========================================

-- Stage for storing VW documents
create or replace stage agent_gateway.ag_schema.vw
  directory = (enable = true)
  encryption = (type = 'SNOWFLAKE_SSE');

-- Stage for storing VW repair manuals
create or replace stage agent_gateway.ag_schema.repair_manual
  directory = (enable = true)
  encryption = (type = 'SNOWFLAKE_SSE');

-- Stage for storing Cortex Analyst semantic model
CREATE STAGE public DIRECTORY = (ENABLE = TRUE);
GRANT READ ON STAGE public TO ROLE PUBLIC;

-- Create table to store parsed documents
create or replace table parsed_documents (
  file_name string,
  content string
);

-- Query documents within external stage
select distinct metadata$filename as file_name
from @agent_gateway.ag_schema.vw;

-- Parse documents and insert them into parsed_documents
insert into parsed_documents
select
  file_name,
  parse_result:content::string as content
from (
  select
    'transporter-with-wbx-vag-self-study-programme.pdf' as file_name,
    snowflake.cortex.parse_document(
      @agent_gateway.ag_schema.vw,
      'transporter-with-wbx-vag-self-study-programme.pdf',
      {'mode': 'OCR'}
    ) as parse_result
);

-- ========================================
-- Test the PDF chunker using a sample PDF from VW stage
-- ========================================
SELECT *
FROM TABLE(agent_gateway.ag_schema.pdf_text_chunker(
    BUILD_SCOPED_FILE_URL(@agent_gateway.ag_schema.vw, 'transporter-with-wbx-vag-self-study-programme.pdf')
));

Now the data looks good for Cortex Search usage, so we can continue with Cortex Search initialization.

Cortex Search initialize & configuration

What is Cortex Search? Cortex Search is a search function that enables high-quality “fuzzy” search over your data. Under the hood, Cortex Search takes a hybrid approach to retrieving and ranking documents. Each search query utilizes:

• Vector search for retrieving semantically similar documents.
• Keyword search for retrieving lexically similar documents.
• Semantic reranking for reranking the most relevant documents in the result set.

Initializing Cortex Search happens by creating a search service from your text chunks. As noted above, Cortex Search works best with small chunks of data, recommending 512 tokens (about 385 English words). When the chuck of data is within 512 tokens, it uses all approaches of document retrieval. If the chuck (entire body of the text) is longer than 512 tokens, Cortex uses keyword search for the whole body of text, but uses only 512 tokens for semantic retrieval.

Cortex Search has few parameters that you can tweak.

• Query column (in our example chunk contains the body of text to search)
• Attributes as filter (in our example relative_path, which is the original document name)
• Warehouse, which is used in materializing the results
• Target lag, which dictates how often Search service will check updates on the base table (for new documents in this pattern)
• Embedding model (which differ from credit cost as they differ in parameter size) 
• Finally the select query defines columns that are also indexed and returned together with query (we will use relative_path, file_url, chunk and page_number)

Known limitations are that the result query must not exceed 100M of rows as the results result in query failure.

Testing Cortex Search

Which gives us really promising answers as seen below

Cortex Analyst initialize & configuration

What is a Cortex Analyst? ​Cortex Analyst is a fully managed service within Snowflake Cortex that enables users to interact with structured data using natural language. It translates user queries into SQL, providing accurate answers without the need for SQL expertise. You can think of Cortex Analyst as a sidekick for Cortex Search. Whereas Cortex Search worked for unstructured data, Analyst does the same for structured data.

To use Cortex Analyst, we need to initialize it with structured data. For this purpose I have included a few tables that contain master data information about Volkswagen Transporter T2.5/T3 VINs and chassis codes. With this information we can identify any Transporter just by their VIN number.

After setting up the data, we need to create a semantic model. For this purpose I have created the following semantic model, with example query using natural language. Upload this file as .yaml to the public schema, which we created at beginning of the blog post.

Now that we have two tools, let's do a test run of the actual Agent Gateway.

Agent Gateway Installation and configuration

Installation

This installation process follow instructions found from Github.

The actual code is shown below, where we set up the Snowflake connection and create the tools. Use the values for Cortex Search and Cortex Analyst from the code examples provided above. Note that the better the tool descriptions are, the easier it is for the task planner to select the correct tool.

The interactive shell part is my own addition, as I'm running this from the terminal.

Testing the tools works as follows. I’ve highlighted my commands for clarity and separated the responses. In this mode, the GatewayLogger (set to INFO) displays all the tools being used. In this particular case, the logger showed that only Cortex Search was used — which isn’t an ideal example of agentic orchestration.

Let’s add the part which most likely will not work all the time e.g. our Python tool. We will introduce a new tool that will scrape eBay, Autoteilmarkt and Autodoc for part prices. 

Testing the tool. This video is not sped up — you can see the Gateway takes its time to think and ultimately doesn’t understand my question about the Syncro (a different VW model).

Conclusion

This was a crude but functional demonstration of what’s possible with Snowflake’s Agent Gateway. We combined Cortex Search, Cortex Analyst, and a custom Python tool to build a multi-tool AI agent that can answer complex questions related to Volkswagen T25/T3 repair needs — based on real documents and live web data.

The biggest wins here come from having good tools — Python being the standout in this case — and, as always, from solid data quality. Without clean and structured data, even the smartest orchestration won’t give you great results.

While this was run from a terminal for simplicity, in a real-world use case we would’ve wrapped the whole thing with Streamlit to make it user-friendly. And since we’re already in Snowflake, we could’ve used Snowflake Container Services to host a production-ready version of the agent, as suggested in the official GitHub repo.

There are still a few limitations — like how Cortex Parse doesn’t chunk text, and how scanned PDFs can be messy — but the overall architecture is solid and extendable. With the right tools and the right data, this setup becomes a very capable assistant for diagnostics, VIN decoding, and spare part estimation.