Learn Snowflake: Build a Conversational AI Agent on US Economic Data

Introduction

Most Snowflake tutorials introduce the platform component by component and lose the reader before the payoff. This one flips the order: build a single, useful product end–to–end — a conversational AI agent that answers plain English questions about the US economy (“what’s the current unemployment rate?” “how has inflation changed in the last two years?”) — and let Snowflake’s pieces fall into place as you need them.

Along the way you’ll touch the marketplace, Snowpark, dynamic tables, semantic views, Cortex Analyst, and a Streamlit–in–Snowflake app. By the end you’ll have a working agent powered by real economic data, the $40 of free credits will barely have moved, and you’ll have a clear mental model for how Snowflake actually fits together as a developer platform.

📚 Table of contents

❄️ What Snowflake actually is, in one paragraph

Snowflake is a cloud data platform that bundles four traditionally separate systems — storage, data warehousing, pipelines, and AI/ML tooling — into one environment, with compute and storage kept independent so you can scale either one without rebuilding the other. That separation is the unlock: spin up a bigger warehouse for a heavy query, leave the data untouched, attach an AI feature without rearchitecting anything.

🆓 Free account and $40 in credits

Sign up at snowflake.com and select the Cortex Code CLI path during onboarding — that’s the developer–oriented signup that grants the $40 trial credit. The whole project in this guide will cost a handful of cents from those credits.

🛒 Step 1 — explore the public marketplace dataset

Open the Snowflake Marketplace and grab the Snowflake Public Data Free share. You only need three indicators for the agent:

• CPI (consumer price index) — inflation.
• Unemployment rate.
• 30–year fixed mortgage rate.

The first two live in FINANCIAL__ECONOMIC_INDICATORS__TIME_SERIES; the mortgage rate lives in FREDDIE_MAC__HOUSING__TIME_SERIES. Inside a new workspace, run small previews:

-- CPI
SELECT DATE, VALUE
FROM   FINANCIAL__ECONOMIC_INDICATORS__TIME_SERIES
WHERE  COUNTRY = 'USA'
  AND  VARIABLE_NAME = 'CPI for All Urban Consumers'
ORDER  BY DATE DESC
LIMIT  12;

Repeat for unemployment and the 30–year mortgage rate. You now have three small previews and a sense of what shape each metric takes.

🏛️ Step 2 — project database, schema, and warehouse

Keep the marketplace data read–only and build alongside it. Run this once in a new SQL file:

CREATE DATABASE IF NOT EXISTS ECON_AGENT_DB;
CREATE SCHEMA   IF NOT EXISTS ECON_AGENT_DB.ANALYTICS;

CREATE WAREHOUSE IF NOT EXISTS COMPUTE_WH
  WITH WAREHOUSE_SIZE = 'XSMALL'
       AUTO_SUSPEND   = 60
       AUTO_RESUME    = TRUE;

USE DATABASE  ECON_AGENT_DB;
USE SCHEMA    ANALYTICS;
USE WAREHOUSE COMPUTE_WH;

Three pieces, each independent. You can resize the warehouse later without touching the database.

🐍 Step 3 — transform with Snowpark from Python

Snowpark gives you a PySpark–style DataFrame API that runs on Snowflake compute. Install it locally:

pip install snowflake-snowpark-python pandas snowflake-connector-python

Connect with username, account, and a personal access token rather than your password if MFA is on. Generate one under Settings → Authentication → Generate token and paste it where you’d normally put a password.

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, lit

params = dict(
  account   = "<your-account>",
  user      = "<your-user>",
  password  = "<personal-access-token>",
  role      = "ACCOUNTADMIN",
  warehouse = "COMPUTE_WH",
  database  = "ECON_AGENT_DB",
  schema    = "ANALYTICS",
)

s = Session.builder.configs(params).create()

ts = s.table('SNOWFLAKE_PUBLIC_DATA_FREE.PUBLIC_DATA_FREE.'
             'FINANCIAL__ECONOMIC_INDICATORS__TIME_SERIES')

cpi = (ts
  .filter(col('COUNTRY')        == 'USA')
  .filter(col('VARIABLE_NAME')  == 'CPI for All Urban Consumers')
  .select(col('DATE'), col('VALUE').cast('double').alias('CPI')))

Repeat for unemployment and the mortgage rate, then union_all them and pivot to one row per date with three metric columns. You now have a small in–memory DataFrame to sanity–check before persisting anything.

🔁 Step 4 — self–refreshing dynamic table

Running a Python script every month to keep the dashboard fresh is not the right answer. Dynamic tables are: you give Snowflake a SQL query plus a target lag (e.g. one hour) and it keeps the table up to date automatically, with no cron job and no external orchestrator.

CREATE OR REPLACE DYNAMIC TABLE ECON_AGENT_DB.ANALYTICS.ECONOMIC_DASHBOARD_LIVE
  TARGET_LAG = '1 hour'
  WAREHOUSE  = COMPUTE_WH
AS
WITH combined AS (
  SELECT DATE, 'CPI'               AS METRIC, VALUE::DOUBLE AS VALUE FROM ... -- CPI rows
  UNION ALL
  SELECT DATE, 'UNEMPLOYMENT_RATE' AS METRIC, VALUE::DOUBLE AS VALUE FROM ... -- unemployment rows
  UNION ALL
  SELECT DATE, 'MORTGAGE_RATE_30Y' AS METRIC, VALUE::DOUBLE AS VALUE FROM ... -- mortgage rows
)
SELECT *
FROM   combined
PIVOT  (MAX(VALUE) FOR METRIC IN ('CPI', 'UNEMPLOYMENT_RATE', 'MORTGAGE_RATE_30Y'))
ORDER  BY DATE;

Refresh status is exposed in the UI — check rows, bytes, lag, and refresh mode in the database explorer.

🧠 Step 5 — semantic view (the AI–readable layer)

A semantic view is a data dictionary in your schema with business–friendly names, descriptions, and metrics. It’s what Cortex Analyst reads when an LLM tries to query your data. Without it, the LLM doesn’t know what CPI means or that it represents inflation.

The right–hand playground now accepts natural–language queries. Try “what was unemployment in November 2025?” or “what’s the highest CPI on record?”. Cortex Analyst will rewrite the question as SQL, run it, and return both the SQL and the rows.

💬 Step 6 — Streamlit–in–Snowflake AI agent

Snowflake hosts Streamlit natively. That means a real chat UI for your data, no separate deployment, no auth headaches.

Run it inside Snowflake and ask the agent free–form questions: “how has CPI changed from today to six months ago?”, “what’s the trend for mortgage rates?”. Responses land within a couple of seconds.

🛰️ Step 7 — talk to Cortex Analyst from your own code

Same agent, this time outside Snowflake. Two REST calls.

# 1. Trade token for a session JWT
POST https://<account>.snowflakecomputing.com/session/v1/login-request
Body: { ACCOUNT_NAME, LOGIN_NAME, PASSWORD: <personal-access-token> }

# 2. Ask Cortex Analyst for SQL
POST https://<account>.snowflakecomputing.com/api/v2/cortex/analyst/message
Headers: Authorization: Snowflake Token="<jwt>"
Body: {
  messages: [{ role: 'user', content: [{ type: 'text', text: 'What is the current unemployment rate?' }] }],
  semantic_view: 'ECON_AGENT_DB.ANALYTICS.ECONOMIC_DASHBOARD_SEMANTIC'
}

The response is a SQL statement plus an interpretation of the question. Execute the SQL through the Snowflake Python connector to get the rows. From here, pipe the result into any LLM of your choice for nicer prose or chain it into a larger agent.

🧨 Common mistakes & pro tips

🏁 Conclusion

A single project ends up teaching most of what makes Snowflake interesting in 2026: the marketplace for free data, Snowpark for Python transforms, dynamic tables for hands–off freshness, semantic views for AI–readable schemas, Cortex Analyst for grounded natural–language queries, and Streamlit–in–Snowflake for an instant UI. Everything lives in one environment, behind one set of credentials, on credits the trial gives you for free.

The next step is the obvious one. Swap economic data for your own tables, point a semantic view at them, and you have the skeleton for an AI agent your team can actually use — one that answers in seconds, never hallucinates a metric, and stays current without anyone running a cron job.

Explore More on DevShelf

• →
  LangChain — Tool Profile

  The agent framework alternative to Cortex Analyst — useful when you need multi-step reasoning over structured and unstructured data together.

• →
  MCP Explained: Build Your Own Server

  How to expose your Snowflake agent's tools via the Model Context Protocol — so any MCP-compatible IDE can use them.