Build a Snowflake Agent in 10 Minutes

The world of data is buzzing with the promise of Large Language Models (LLMs), but how do you move them from simple chat interfaces to intelligent actors that can do things? The answer is agents. This guide will show you how to build your very first Snowflake Agent in minutes, creating a powerful assistant that can understand your data and write its own SQL.

Welcome to the next step in the evolution of the data cloud.

What Exactly is a Snowflake Agent?

A Snowflake Agent is an advanced AI entity, powered by Snowflake Cortex, that you can instruct to complete complex tasks. Unlike a simple LLM call that just provides a text response, an agent can use a set of pre-defined “tools” to interact with its environment, observe the results, and decide on the next best action to achieve its goal.

It operates on a simple but powerful loop called the ReAct (Reason + Act) framework:

1. Reason: The LLM thinks about the goal and decides which tool to use.
2. Act: It executes the chosen tool (like a SQL function).
3. Observe: It analyzes the output from the tool.
4. Repeat: It continues this loop until the final goal is accomplished.

Our Project: The “Text-to-SQL” Agent

We will build a Snowflake Agent with a clear goal: “Given a user’s question in plain English, write a valid SQL query against the correct table.”

To do this, our agent will need two tools:

• A tool to look up the schema of a table.
• A tool to draft a SQL query based on that schema.

Let’s get started!

Step 1: Create the Tools (SQL Functions)

An agent is only as good as its tools. In Snowflake, these tools are simply User-Defined Functions (UDFs). We’ll create two SQL functions that our agent can call.

First, a function to get the schema of any table. This allows the agent to understand the available columns.

-- Tool #1: A function to describe a table's schema
CREATE OR REPLACE FUNCTION get_table_schema(table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    SELECT GET_DDL('TABLE', table_name);
$$;

Second, we’ll create a function that uses SNOWFLAKE.CORTEX.COMPLETE to draft a SQL query. This function will take the user’s question and the table schema as context.

-- Tool #2: A function to write a SQL query based on a schema and a question
CREATE OR REPLACE FUNCTION write_sql_query(schema VARCHAR, question VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    SELECT SNOWFLAKE.CORTEX.COMPLETE(
        'llama3-8b', -- Using a fast and efficient model
        CONCAT(
            'You are a SQL expert. Based on the following table schema and user question, write a single, valid SQL query. Do not add any explanation, just the code.\n\n',
            'Schema:\n', schema, '\n\n',
            'User Question:\n', question
        )
    )
$$;

With our tools ready, we can now assemble the agent itself.

Step 2: Create Your First Snowflake Agent

Creating the agent involves a single, declarative SQL command. Here, we define the agent, grant it access to our functions (tools), and give it its core objective using a prompt.

-- Now, we create the agent and give it the tools
CREATE OR REPLACE AGENT my_first_sql_agent
  MODEL = 'llama3-70b' -- Use a powerful model for reasoning
  FUNCTIONS = (get_table_schema, write_sql_query)
  PROMPT =
$$
You are an expert SQL-writing agent. Your goal is to help users by writing a single SQL query that answers their question.

Here is your plan:
1. First, you MUST use the get_table_schema tool to find the columns of the table the user mentions.
2. Once you have the schema, you MUST use the write_sql_query tool to generate the final SQL.
3. Finally, output the generated SQL query as your final answer. Do not add any extra text or explanation.
$$;

And that’s it. Your Snowflake Agent is now live and ready for instructions.

Step 3: Chat with Your Agent

To interact with your agent, you use the CALL command. The agent will show its thought process as it uses the tools you provided.

Let’s assume you have the SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER table.

-- Let's test our new agent!
CALL my_first_sql_agent('Show me the names of all customers in the USA, table name is SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER');

Expected Agent Thought Process (the output you’ll see):

1. Thinking: The user wants customer names from the USA and has given me a table name. My first step is to get the schema of that table. I will call get_table_schema.
2. Calling Tool: get_table_schema('SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER')
3. Observing Output: (The agent now sees the CREATE TABLE statement with columns like C_NAME, C_NATIONKEY, etc., and knows there’s a related NATION table for country names).
4. Thinking: Okay, I have the schema. The user wants names (C_NAME) where the nation is USA. I have all the information I need to write the query. I will now call write_sql_query.
5. Calling Tool: write_sql_query('<schema_output>', 'Show me the names of all customers in the USA')
6. Observing Output: SELECT c.c_name FROM customer c JOIN nation n ON c.c_nationkey = n.n_nationkey WHERE n.n_name = 'USA';
7. Final Answer: The agent outputs the generated SQL.

Conclusion: From Minutes to Mastery

You’ve just built a functional Snowflake Agent that can reason and act within your data cloud. This simple prototype is just the beginning. Imagine agents that can manage data quality, perform complex transformations, or even administer security, all through natural language commands. Welcome to the future of data interaction.