In this hands-on lab, you'll build a complete AI-powered retail analytics platform entirely within Snowflake — no external infrastructure required. Using Snowflake CoCo as your AI-assisted development environment, you'll work through the full data lifecycle: stream real-time orders via Snowpipe Streaming, MERGE them into production tables with Gen2 Warehouses, transform them through a 3-tier Dynamic Tables pipeline, and serve them with Interactive Tables for low-latency point lookups.

You'll build analytical models with dbt, monitor data quality with Data Metric Functions, and create custom CoCo skills for reusable workflows. Tie it all together with Snowflake CoWork — a conversational AI interface where a Cortex Agent orchestrates Cortex Analyst and Agentic Search to answer "what happened" and "why" from both structured and unstructured data. Finally, evaluate your agent with ground-truth datasets, implement row-level security, and expose your agent as a managed MCP server for external AI clients.

A production-grade AI-powered retail analytics platform on Snowflake — from raw data to conversational AI insights, entirely within a single platform. You'll create dynamic transformation pipelines, interactive low-latency tables, dbt analytical models, a Cortex Agent that answers questions across structured and unstructured data, Deep Research reports that cross-reference multiple data sources, governed Artifacts you can share with your team, scheduled Automations for recurring analysis, row-level security that works transparently through AI, and an MCP server that exposes your agent to external clients.

1. Setup
2. Explore Your Data
3. Data Quality
4. Dynamic Tables Pipeline
5. dbt Analytics
6. Gen2 Warehouse: Optima Indexing
7. Interactive Tables
8. CoCo Custom Skill
9. CoCo Plugin
10. Snowflake CoWork
11. Security and Governance
12. Streamlit Dashboard
13. Agent Evaluation
14. Agent Observability
15. MCP Server
16. Optional: Iceberg V3 Features
17. Optional: Streaming Ingestion
18. Cleanup
19. Resources

Throughout this guide you'll see two types of instructions:

Both paths produce the same result. Use whichever feels natural.

The Snowflake CLI (snow) lets you run SQL, deploy apps, and manage Snowflake objects from your terminal.

macOS (using Homebrew):

If you don't have Homebrew installed, first install it by opening Terminal and running:

Then install Snowflake CLI:

Windows:

Linux:

Verify the installation:

You should see output like Snowflake CLI version: 3.x.x.

Snowflake CoCo is an AI-powered coding assistant that runs in your terminal. It helps you write SQL, build pipelines, deploy apps, and explore your data using natural language prompts.

macOS and Linux:

Windows (PowerShell):

Verify the installation:

If you prefer a visual IDE experience, download CoCo Desktop instead of (or alongside) the CLI. It's a native Mac/Windows app with a file editor, integrated terminal, agentic browser, and the same AI capabilities.

On first launch, follow the onboarding wizard:

1. Click Next on the Welcome screen
2. Add your connection (same account identifier and credentials from above) or select an existing one detected from connections.toml
3. Choose Agent mode
4. Pick your theme, then click Get Started

See CoCo Desktop documentation for full details.

Before you can run any commands against Snowflake, you need to configure a connection. This tells the CLI which Snowflake account to connect to and how to authenticate.

Run the interactive connection wizard:

You'll be prompted for the following values (enter them one at a time):

| Prompt | What to enter | Example | |--||| | Connection name | A short name for this connection | hol | | Account identifier | Your Snowflake account URL (without .snowflakecomputing.com) | myorg-myaccount | | User | Your Snowflake username | jsmith | | Password | Your Snowflake password | (hidden) | | Role | ACCOUNTADMIN | ACCOUNTADMIN |

Leave the rest empty.

Test that your connection works:

You should see Status: OK.

Launch Snowflake CoCo and verify your connection:

Then run the core infrastructure script (this takes ~5 minutes with the default 10M-row dataset):

This creates the database, schemas, warehouses, tables, Dynamic Tables pipeline, Interactive Tables, Cortex Search Services, Semantic View, seed data (10M orders, 25M order items, 2M customers), and Row Access Policy.

Now that setup is complete, let's get familiar with what was created. This is your first interaction with CoCo — try asking natural language questions about your data.

Prompt CoCo:

CoCo queries INFORMATION_SCHEMA and shows the database structure: RAW (source tables + views), STAGING (streaming landing zone), DYNAMIC_TABLES (pipeline), INTERACTIVE (low-latency), and SEMANTIC (AI layer).

Expected: ~10M orders, ~25M order items, 2M customers, 10 products, 1200 reviews, 1200 support tickets.

Expected: August to November 2026.

This gives you a mental model of the dataset before we start transforming and analyzing it.

Data Metric Functions (DMFs) let you attach automated quality checks directly to table columns. Snowflake runs them on a schedule and stores results in SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS. Built-in DMFs include NULL_COUNT, DUPLICATE_COUNT, UNIQUE_COUNT, and FRESHNESS — or you can write custom ones.

The setup script injected ~200 NULL values into orders.total_amount and order_items.quantity, plus ~150 NULLs into order_items.product_name. DMFs detect the first two — but there's a gap.

Prompt CoCo:

CoCo shows that TOTAL_AMOUNT (200 NULLs) and QUANTITY (200 NULLs) have violations — but product_name NULLs are going undetected.

CoCo finds ~150 NULLs and reveals the DMF is mis-attached to product_category instead of product_name.

This demonstrates the real-world workflow: monitor, discover gaps, fix coverage.

Dynamic Tables are declarative data pipelines — you define the target state as a SQL query and Snowflake handles incremental refresh automatically. The TARGET_LAG parameter controls freshness: set a time-based lag (e.g., 1 minute) for Tier 1 tables, or use DOWNSTREAM for tables that refresh only when their upstream sources change.

Prompt CoCo:

CoCo displays the 3-tier pipeline:

Ask CoCo:

Expected: Top-5 days are in November 2026 (holiday peak), each with ~$183M revenue and ~117K orders.

dbt-snowflake transforms raw data into analytics-ready models using SQL SELECT statements. The dbt-snowflake adapter integrates natively with Snowflake, supporting incremental models, snapshots, and built-in testing. In this HOL, dbt creates staging views and mart tables for customer lifetime value, segmentation, product performance, and support analytics.

Prompt CoCo:

CoCo runs dbt deps then dbt build to create all staging views and mart tables (9+ models).

Running dbt locally (agent guidance): dbt-analytics/profiles.yml uses env_var() for account, user, password, and warehouse (defaulting to HOL_WH), so it works with no hardcoded secrets. When running dbt locally, resolve the credentials from the active hol Snowflake CLI connection (snow connection list) and export them as environment variables — do not print or transcribe the password into the transcript or any file. For example, set the env vars in the shell, then run:

If dbt is run inside Snowflake (e.g., from Snowsight), no env vars are needed — the active session provides authentication.

Gen2 Warehouses introduce Optima Indexing — an automatic indexing layer that prunes partitions at query time without explicit clustering keys. Gen2 warehouses learn from your query patterns and build internal indices that accelerate point lookups and filtered scans.

Demonstrate Optima Indexing in action:

Prompt CoCo:

Open the query profile in Snowsight to see partition pruning — only a fraction of partitions scanned despite no explicit clustering key. This is Gen2's Optima Indexing in action.

Run the same query on the standard warehouse to see the difference:

Prompt CoCo:

Without Optima Indexing, the standard warehouse scans significantly more partitions for the same point lookup. Compare the two query profiles side-by-side in Snowsight to see the contrast.

Interactive Tables are purpose-built for low-latency, high-concurrency point lookups. They maintain pre-computed results with clustering optimized for equality predicates, delivering sub-second response times for dashboard filters and application queries that would otherwise require full table scans.

Run queries in Snowsight to observe sub-second latency:

Prompt CoCo:

This fires 200 concurrent sessions (1000 queries total) against both Interactive and Standard warehouses, then compares P50/P90/P99 latencies. You should see notably lower latency and higher throughput on the Interactive warehouse.

Run the load test a second time to observe the effect of warm caches. Results may vary depending on account, region, and data scale.

CoCo Custom Skills let you package repeatable workflows into named commands that any team member can invoke. A skill is a Markdown file (.cortex/skills/<name>/SKILL.md) that defines triggers, parameters, and step-by-step instructions CoCo follows when the skill is activated.

Create a reusable skill that automates table profiling:

Prompt CoCo:

CoCo creates .cortex/skills/profile-table/SKILL.md with the skill definition, triggers, and step-by-step instructions.

This demonstrates how teams package repeatable workflows as shareable CoCo skills.

CoCo Plugins bundle skills, MCP servers, hooks, and subagents into a single shareable package. Once packaged, anyone on your team can install the plugin and get all your extensions in one step — via GitHub, the Plugins Catalog, or by dropping it into a project directory.

Prompt CoCo:

CoCo creates the plugin directory with a manifest and copies the skill into it.

Manual fallback:

Create .cortex/plugins/retail-analytics/.cortex-plugin/plugin.json:

Expected output: Plugin 'retail-analytics' is valid.

The plugin is auto-discovered from .cortex/plugins/ in your project directory. Test it:

The skill runs from the plugin — same behavior as before, but now it's a portable, installable package. Share it by pushing to GitHub (cortex plugin install your-org/retail-analytics) or publishing to the Plugins Catalog from CoCo Desktop.

Cortex Agents are multi-tool AI orchestrators that route questions to the right data source. They combine Cortex Analyst (text-to-SQL over Semantic Views) with Cortex Search (vector + keyword search over unstructured data) to answer both "what happened" and "why" from a single conversational interface.

Prompt CoCo:

Open the Snowflake CoWork interface in Snowsight: navigate to AI & ML > Snowflake CoWork. Select the BUSINESS_INSIGHTS_AGENT agent. Then try each question to demonstrate different tool routing:

| Question | Tools Used | |-|--| | "Show me monthly revenue trend from August to November 2026" | Cortex Analyst (text-to-SQL) | | "Which month had the lowest revenue, and what do customer reviews say about that period?" | Cortex Analyst + Agentic Search | | "Find reviews mentioning wrong size with a rating below 3" | Agentic Search (filtered) | | "Why are customers returning ski boots?" | Agentic Search (reviews + tickets) | | "What is our total revenue and customer count by state?" | Cortex Analyst (text-to-SQL) | | "What are the top complaint themes in support tickets?" | Agentic Search (filter + AI_AGG) | | "How many reviews mention sizing issues, and which products are most affected?" | Agentic Search (search + breakdown) |

This is the capstone moment — the agent routes across structured data (text-to-SQL) and unstructured data (Cortex Search) to answer "what happened" and "why."

For complex questions that span multiple data sources, use Deep Research — an investigation mode that decomposes your question into parallel sub-investigations and synthesizes findings into a fully-cited report.

In CoWork, click the + button in the message bar and select Deep Research, then ask:

CoWork runs parallel agents that cross-reference structured metrics (cancellation rates, return patterns) with unstructured feedback (reviews mentioning sizing issues, tickets about defects). After 2-5 minutes, it produces a multi-section report with every claim traced back to source data.

When the agent produces a useful chart or table, save it for reuse. After the agent responds to a revenue question with a chart:

1. Click the save icon on the chart to create an Artifact
2. Name it (e.g. "Monthly Revenue Trend")
3. Click Share to generate a link

Artifacts are persistent, live references — they refresh with the latest data on demand. Shared links respect RBAC: a colleague opening the same artifact sees results filtered through their own data permissions.

Turn a one-time insight into a recurring report. After getting a useful answer, tell the agent:

CoWork creates a scheduled automation that re-runs the query weekly with fresh data and emails you the results — including a summary, key metrics, and a link to the full report for follow-up questions. Manage automations from the Automations tab in CoWork.

Row Access Policies enforce row-level security declaratively — you define a boolean expression that determines which rows are visible to which roles, and Snowflake applies it transparently to every query (including those generated by AI agents). No application code changes required.

The Row Access Policy and WEST_COAST_MANAGER role were created by setup.sql. Demonstrate how row-level security transparently filters data based on role:

As ACCOUNTADMIN (full access):

Result: all 10 regions visible (9 US states + British Columbia).

As WEST_COAST_MANAGER (restricted):

Result: only CA, OR, WA appear — the Row Access Policy transparently filters data.

Key insight: Same query, same tables — different results based on who's asking. Row-level security enforces data boundaries without changing application logic.

Cortex Agents run with the querying user's default role, not the active role selected in Snowsight — so switching your role in the Snowsight UI has no effect on the agent's results. To see the Row Access Policy filter the agent, use the dedicated demo user created by setup.sql:

First, set a login password for the demo user (setup.sql creates the user without a hardcoded password, to avoid committing a secret):

Then log into Snowsight as west_coast_manager_user with the password you just set, open CoWork, and ask the agent:

The agent returns results for only CA, OR, and WA — the Row Access Policy filters data transparently, even through AI-generated SQL. (Log back in as your admin user afterward.)

Streamlit in Snowflake lets you build and deploy interactive data applications directly within your Snowflake account — no external infrastructure needed. Apps run securely inside Snowflake, with native access to your data and governed by the same role-based access control.

Prompt CoCo:

CoCo runs snow streamlit deploy from the streamlit-dashboard/ directory. Once deployed, open the app URL in Snowsight to explore:

Agent Evaluation lets you measure agent quality using ground-truth datasets and LLM-as-judge scoring. You define input queries and expected answers, then Snowflake runs the agent against each question and scores responses on Answer Correctness (does the response match ground truth?) and Logical Consistency (are the reasoning steps internally coherent?).

The evaluation dataset (7 questions + ground truth) was created by setup.sql. Run the evaluation in Snowsight:

1. Switch to the ACCOUNTADMIN role in Snowsight (top-left role selector)
2. Navigate to AI and ML > Agents > BUSINESS_INSIGHTS_AGENT > Evaluations tab
3. Click Use existing dataset, then Run an eval manually
4. Click New evaluation run, name it (e.g. hol-eval-run-1), click Next
5. Select Create new dataset from table
6. Under Source table, set Database and schema to DASH_AUTOMATED_INTELLIGENCE_DB.SEMANTIC, then select AGENT_EVALUATION_DATA
7. Under New dataset location, keep DASH_AUTOMATED_INTELLIGENCE_DB.SEMANTIC
8. Set Dataset name: hol_eval_dataset
9. Click Next
10. Under Define metrics, confirm Input query = INPUT_QUERY
11. Toggle on Answer Correctness, set Expected answer = GROUND_TRUTH
12. Toggle on Logical Consistency
13. Click Create — evaluation starts automatically (~3 min)

If questions score low on logical consistency:

1. Click a low-scoring row and view Thread details
2. Look for vague reasoning about tool selection in the Planning step
3. Update the agent's instructions to be more explicit
4. Recreate the agent and re-run the evaluation

Agent Observability lets you inspect what happened inside every agent request — which model was used, how many tokens were consumed, which tools were called, and how the agent planned its response. This is essential for production cost monitoring and debugging.

You'll see the full trace: planning steps → tool selection (Cortex Analyst, Search, SQL execution) → chart generation → response. Use this to debug slow responses, understand token costs, and verify the agent is routing to the correct tools.

Snowflake MCP Servers expose your Cortex Agents, Semantic Views, and Search Services as tools discoverable via the open Model Context Protocol (MCP). Any MCP-compatible client (CoCo CLI, Claude Desktop, custom apps) can connect to the server endpoint and invoke tools programmatically — turning your Snowflake AI stack into a reusable service layer.

Expose the Business Insights Agent as a managed MCP server:

Prompt CoCo:

CoCo creates the MCP server:

CoCo Desktop (recommended): Go to Settings → MCP and add a new HTTP server with this endpoint URL:

Or type /mcp in the chat to manage MCP connections.

CoCo CLI: Register the server (Desktop picks this up automatically):

Now any MCP-compatible client (CoCo Desktop, Claude Desktop, custom apps) can discover and call these tools via the standard MCP protocol.

Prompt CoCo:

CoCo creates the table with CATALOG='SNOWFLAKE' (no external volume needed) and demonstrates partition pruning on filtered queries.

CoCo creates a V3 Snowflake-managed Iceberg table (merge-on-read is the default for V3 managed tables), loads enough rows to exceed the data-file threshold below which deletion vectors are suppressed, then UPDATEs a small fraction of rows so Snowflake writes a deletion vector rather than rewriting the file.

This demonstrates V3 schema evolution without rewriting data files.

Generate keys for Snowpipe Streaming authentication:

Edit profile.json and set your account, user, private_key_file (path to rsa_key.p8), and role.

Verify that row counts in both tables are consistent (each order should have matching order items).

Use CoCo to merge the streamed data:

To remove all objects created during this lab:

Congratulations! You've built a complete AI-powered retail analytics platform on Snowflake — from raw data ingestion to conversational AI insights.