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, row-level security that works transparently through AI, and an MCP server that exposes your agent to external clients.

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 (using Homebrew):

Windows / Linux:

Verify the installation:

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):

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 ~10-15 minutes):

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.

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.

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 September 2025 (back-to-season peak), each with ~$183M revenue and ~117K orders.

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.

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.

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). CoCo automatically injects your active Snowflake connection into the dbt profile — no manual configuration needed.

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.

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 → CoWork (or search "CoWork" in the global search bar). Select the BUSINESS_INSIGHTS_AGENT agent. Then try each question to demonstrate different tool routing:

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."

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 states visible.

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.

Switch to the WEST_COAST_MANAGER role in Snowsight, then 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.

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 New evaluation run, name it (e.g. hol-eval-run-1), click Next
4. Select Create new dataset from table
5. Under Source table, set Database and schema to DASH_AUTOMATED_INTELLIGENCE_DB.SEMANTIC, then select AGENT_EVALUATION_DATA
6. Under New dataset location, keep DASH_AUTOMATED_INTELLIGENCE_DB.SEMANTIC
7. Set Dataset name: hol_eval_dataset
8. Click Next
9. Under Define metrics, confirm Input query = INPUT_QUERY
10. Toggle on Answer Correctness, set Expected answer = GROUND_TRUTH
11. Toggle on Logical Consistency
12. 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

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:

Now any MCP-compatible client (CoCo, 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 table with ENABLE_ICEBERG_MERGE_ON_READ = TRUE, inserts data, then runs an UPDATE that uses deletion vectors instead of full file rewrites.

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 (contents of rsa_key.p8), and role.

You should see 10,000 orders and ~50,000 order items in staging.

Use CoCo to merge the streamed data:

Congratulations! You've successfully built a complete AI-powered retail analytics platform on Snowflake — from streaming ingestion through conversational AI and MCP server exposure.

Documentation: