Overview

Prompt Approach

What You'll Learn

What You'll Need

Important: In a hands-on lab, use the Snowflake account your lab provides. Otherwise, you need an account where Snowflake App Runtime is enabled. Iteration 3 uses Cortex AI, which is off by default on self-service trial accounts until you add a credit card.

# macOS, Linux, or WSL
curl -LsS https://ai.snowflake.com/static/cc-scripts/install.sh | sh

# Windows (PowerShell)
irm https://ai.snowflake.com/static/cc-scripts/install.ps1 | iex

Environment Setup

Verify Tools

# Check the required and optional tools
snow --version
node --version
git --version

# optional tool
cortex --version

Clone the Repository

# Clone the companion repo
git clone https://github.com/Snowflake-Labs/sfguide-build-full-stack-apps-with-snowflake-app-runtime.git
cd sfguide-build-full-stack-apps-with-snowflake-app-runtime

Tip: The solution branch has the finished ChurnGuard app through iteration 3. Use it to compare your app or to skip ahead, but don't merge it into my-app. Once you have my-app (below), compare at any point with:

git diff my-app origin/solution -- churnguard/

Add a Snowflake CLI Connection

# Add the quickstart connection, then test it
snow connection add --connection-name quickstart \
  --account <your_account> --user <your_user> \
  --authenticator 'OAUTH_AUTHORIZATION_CODE' \
  --client-store-temporary-credential \
  --no-interactive

snow connection test --connection quickstart

Note: The guide uses the connection name quickstart. If you use another name, change it in .env, AGENTS.md, and every --connection flag (including cortex mcp add in iteration 3).

Run the Setup Scripts

# Run the setup scripts in order
snow sql -f scripts/setup.sql --connection quickstart       # database and TPC-DS check
snow sql -f scripts/grants.sql --connection quickstart      # quickstart role and grants
snow sql -f scripts/setup-data.sql --connection quickstart  # sample data (usually under a minute)

snow sql --connection quickstart --role SFQUICKSTART_CHURNGUARD_ROLE \
  --warehouse SFQUICKSTART_CHURNGUARD_WH -q "
  SELECT 'CHURN_METRICS' AS tbl, COUNT(*) AS row_count
    FROM SFQUICKSTART_CHURNGUARD.PUBLIC.CHURN_METRICS
  UNION ALL
  SELECT 'CHURN_TRENDS', COUNT(*)
    FROM SFQUICKSTART_CHURNGUARD.PUBLIC.CHURN_TRENDS"

Prepare Your Working Directory

# Create my-app from the iteration 1 branch
git fetch origin
git checkout -b my-app origin/iteration-1/data-exploration

# Load the default connection and role
cp .env.example .env

# with direnv (https://direnv.net)
echo dotenv > .envrc && direnv allow
# or, without direnv, run this instead in each new terminal:
# set -a; source .env; set +a

snow connection test   # should show quickstart with SFQUICKSTART_CHURNGUARD_ROLE

role = "SFQUICKSTART_CHURNGUARD_ROLE"
warehouse = "SFQUICKSTART_CHURNGUARD_WH"

Iteration 1: Build the Data Explorer

The Prompt

/snowflake-apps

Goal: Build a customer churn risk dashboard called ChurnGuard
that helps retention teams identify and prioritize at-risk customers.

Requirements:
- KPI cards: total customers, churn rate, revenue at risk, avg return rate
- Segment chart: churn risk distribution by credit rating (High/Medium/Low)
- Trend chart: active customers vs churn indicator over time
- Customer details table showing individual high-risk customers
  with 10-per-page pagination and risk level filter tabs
- Drill down: clicking a segment in the churn-risk chart filters the
  customer table to that segment; a "Clear filter" button resets it
- Pivot: a dimension selector above the segment chart lets users switch
  the grouping column (credit rating, marital status, education level,
  or gender) and the chart re-aggregates on selection
- Query the pre-computed tables in SFQUICKSTART_CHURNGUARD.PUBLIC:
  CHURN_METRICS (customer-level data) and CHURN_TRENDS (monthly aggregates)

UI:
- Modern card-based layout with generous spacing and subtle shadows
- Professional color palette (blues and grays with accent colors for risk levels)
- Responsive grid that works well on wide screens
- Smooth loading states and transitions
- Data-dense but not cluttered -- prioritize readability

Constraints:
- Query only the local CHURN_METRICS and CHURN_TRENDS tables
  (do NOT query SNOWFLAKE_SAMPLE_DATA directly -- the fact tables are too large)
- Add the usage marker from AGENTS.md (APP_ORIGIN in app.yml)

Output:
- Deployed interactive dashboard at the Application Service URL
- Clean Next.js project structure ready for iteration

What Happens

Key Concepts

Verify

Tip: Cortex Code shows the App URL when the deploy finishes, so you can click it, or ask Cortex Code to open the app. It runs snow app open --connection quickstart, which you can also run yourself: add --print-only to print the URL, or --watch while a deploy is still finishing.

Note: The tables come from sampled TPC-DS data, so your numbers may differ if you rerun setup-data.sql.

Note: SHOW APPLICATION SERVICES may report SUSPENDED right after a deploy. That's normal: the service suspends when idle and resumes on the first request, so just open the URL.

Test Locally (Optional)

npm run dev

Understanding app.yml

Tip: Have a project that uses snowflake.yml? See Migrate from snowflake.yml to app.yml.

Key Fields

Note: ChurnGuard carries one anonymous usage marker: APP_ORIGIN in app.yml. It tells Snowflake DevRel that an app started from this guide. It holds no customer data, and the app works the same without it. To opt out, delete it. AGENTS.md in the companion repo describes it.

Tip: With the default auto_suspend_secs of 0, the app never suspends and keeps consuming credits while idle. For a lab, set it to 300 (five minutes), or run the cleanup when you finish. See Scale and suspend apps.

Iteration 2: Add Workflow Capabilities

Setup

# Commit iteration 1, then merge in iteration 2
git add -A && git commit -m "Iteration 1 done"
git fetch origin && git merge --no-edit origin/iteration-2/workflow-app

snow sql -f scripts/grants.sql --connection quickstart

snow sql -f scripts/iteration-2-setup.sql --connection quickstart

Note: From here on, some objects use credits while they run: the dynamic table and alert (both every minute) and, in iteration 3, a daily task. Run Cleanup when you're done.

The Prompt

/snowflake-apps

Goal: Add retention workflow capabilities to ChurnGuard so teams
can act on churn insights -- flag customers, assign tasks, and
track resolution -- writing to a Snowflake table, with a dynamic
table and an alert turning those writes into notifications.

Requirements:
- "Flag for Retention" action button on high-risk customer rows
- Retention action form: assignee, action type
  (Call / Email / Offer / Escalate), notes field
- Write flagged actions to SFQUICKSTART_CHURNGUARD.PUBLIC.ACTIONS
  (created by iteration-2-setup.sql; columns ACTION_ID, CUSTOMER_KEY,
  ASSIGNED_TO, ACTION_TYPE, STATUS, NOTES, CREATED_AT, UPDATED_AT,
  CREATED_BY) with querySnowflake and ? bind variables
- One open action per customer: POST /api/actions creates the action
  with a single MERGE ... WHEN NOT MATCHED THEN INSERT that matches the
  customer's actions that aren't Resolved, using an ACTION_ID generated
  in the app (crypto.randomUUID()). If the MERGE inserts no row, look up
  the open action and return HTTP 409 with "already flagged: <action_id>".
  The form shows that message as a toast instead of creating a duplicate
- Read the action list from the ACTIONS table
- Action Board page: all tasks with status badges
  (New / In Progress / Resolved)
- Status update controls to move tasks through the workflow
  (UPDATE ACTIONS, setting UPDATED_AT)
- Summary KPI cards on the Action Board computed from the
  ACTIONS table (same source as the action list -- no sync delay)
- Global toast notifications from the NOTIFICATIONS table
  (populated by ESCALATION_ALERT -- the alert reads the
  ACTION_SUMMARY dynamic table; the DT exists solely to power
  the Snowflake alert pipeline)

Architecture:
- App reads and writes the ACTIONS table (action list, KPI summaries,
  status transitions -- all through querySnowflake)
- Alert pipeline (Snowflake-only): ACTION_SUMMARY DT reads ACTIONS
  (1 minute target lag) -> ESCALATION_ALERT fires on open escalations ->
  writes to NOTIFICATIONS table
- App reads NOTIFICATIONS and displays them as global toasts

UI:
- Action Board as a clean list/table with status pill badges
  and 25-per-page pagination
- Summary metric cards above the action table
- Global toast notifications for escalation alerts (visible on any page)
- Slide-over or modal form for creating actions -- not a separate page
- Inline status transitions (click badge to advance)
- Table action columns use icon buttons with the label as tooltip/alt text
- Visual distinction between action statuses using color coding
- Consistent design language with the existing explorer dashboard

Constraints:
- Use ? bind variables for every query that takes a value
- Use owner's rights for all Snowflake queries (the default)
- Snowflake doesn't enforce primary keys or CHECK constraints on
  standard tables: validate action types and statuses in the API
- Return timestamps to the browser as ISO 8601 strings
- app.yml doesn't change in this iteration: no secrets or
  external access integrations are needed
- Keep the existing dashboard views working. Adding the Flag for
  Retention column to the customer table is the only dashboard change
- Keep the APP_ORIGIN usage marker from AGENTS.md

Output:
- Updated app with a new Action Board page
- Snowflake writes for action CRUD operations
- Action summaries computed from the ACTIONS table (no sync delay)
- Global toast notifications from Snowflake alerts
- Redeploy to the same Application Service URL

What Happens

await querySnowflake(
  `MERGE INTO SFQUICKSTART_CHURNGUARD.PUBLIC.ACTIONS t
   USING (SELECT ? AS ACTION_ID, ? AS CUSTOMER_KEY, ? AS ASSIGNED_TO, ? AS ACTION_TYPE, ? AS NOTES) s
     ON t.CUSTOMER_KEY = s.CUSTOMER_KEY AND t.STATUS <> 'Resolved'
   WHEN NOT MATCHED THEN
     INSERT (ACTION_ID, CUSTOMER_KEY, ASSIGNED_TO, ACTION_TYPE, NOTES)
     VALUES (s.ACTION_ID, s.CUSTOMER_KEY, s.ASSIGNED_TO, s.ACTION_TYPE, s.NOTES)`,
  { binds: [id, customerKey, assignedTo, actionType, notes] },
)

Key Concepts

Verify

Iteration 3: Make ChurnGuard Intelligent

Setup

# Commit iteration 2, then merge in iteration 3
git add -A && git commit -m "Iteration 2 done"
git fetch origin && git merge --no-edit origin/iteration-3/ai-app

snow sql -f scripts/grants.sql --connection quickstart

snow sql -f scripts/iteration-3-setup.sql --connection quickstart

The Prompts

/agent-studio

Goal: Give ChurnGuard a Cortex Agent that explores churn data, recommends
retention actions from the RETENTION_PLAYBOOK, and stages proposals via a
custom tool.

Requirements:

1. Create semantic view CHURNGUARD_SV over:
   - CHURN_METRICS (customer-level orders, spend, returns, churn risk)
   - CHURN_TRENDS (monthly active vs churning customers)
   - CUSTOMER_RECOMMENDATIONS (recommended action, rule, reason,
     open-action flag per customer)
   - ACTION_SUMMARY and NOTIFICATIONS (action counts, escalations)
   Add synonyms (at-risk, churner, retention action, playbook) and
   verified queries for: top high-risk customers by spend, churn risk
   by credit rating, monthly churn trend, high-risk customers with no
   open action.

2. Create agent CHURNGUARD_AGENT (model auto, display name "ChurnGuard")
   with tools:
   - Cortex Analyst on CHURNGUARD_SV, warehouse SFQUICKSTART_CHURNGUARD_WH
   - Data to Chart for trends and comparisons

3. Register a custom tool `stage_retention_actions` backed by stored
   procedure STAGE_RETENTION_PROPOSALS. The tool writes proposals to
   PROPOSED_ACTIONS and returns the count of staged proposals.

4. Instructions:
   - Recommend actions only from CUSTOMER_RECOMMENDATIONS; cite the
     rule REASON. Never invent an action type.
   - Skip customers where HAS_OPEN_ACTION is true
   - At most 5 proposals per turn
   - When the user asks to flag or stage actions, call
     stage_retention_actions right away with max_proposals = 5. Don't
     list candidates or ask for confirmation first: proposals are only
     suggestions, and people approve them on the app's Agent Suggested
     tab, which the next prompt (03-add-agent.md) adds. Until then they
     wait in PROPOSED_ACTIONS. Summarize what was staged.
   - Be concise; prefer a table for lists of customers

5. Test the agent with these questions, in the Snowsight agent playground
   or through the agent's REST API:
   - "Who are the top 5 high-risk customers by spend and what should we do?"
   - "Show the monthly churn trend as a chart"
   - "Flag high-risk customers for retention" (should call the tool)

Constraints:
- Use role SFQUICKSTART_CHURNGUARD_ROLE; create objects in
  SFQUICKSTART_CHURNGUARD.PUBLIC

Output:
- CHURNGUARD_SV semantic view with verified queries
- CHURNGUARD_AGENT answering the test questions, with the custom tool
  staging proposals into PROPOSED_ACTIONS

/snowflake-apps

Goal: Make ChurnGuard agent-assisted with proactive recommendations,
an MCP server for external automation, and consistent UI polish.

Requirements:
- API route streaming the agent:run REST API (SSE) for CHURNGUARD_AGENT,
  one thread per conversation
- Chat drawer opened from the header and Cmd+K; thread survives page
  navigation, with a "New conversation" action
- Contextual entry points that pre-fill context: details row "Ask about
  this customer", segment bar "Explain this segment", High-risk KPI
  "Why?", Action Board card "Suggest next step", escalation toast
- Page-aware suggested prompts in an empty drawer
- Render agent tables and charts inline; when the agent calls
  stage_retention_actions, show the staged count and link to the
  Agent Suggested tab
- "Agent Suggested" tab on Action Board reading PROPOSED_ACTIONS
  (pending) via querySnowflake: each card shows customer key, action
  type, and rule reason. "Approve All" button above the list;
  per-card Approve / Dismiss. Approve writes to ACTIONS via
  POST /api/actions, assigned to "Retention team", and sets
  PROPOSED_ACTIONS.STATUS = 'approved'.
  If POST /api/actions answers 409 "already flagged", mark the
  proposal 'dismissed' with STATUS_REASON set to that message instead
  of failing. Approve All
  approves the rest and reports how many were skipped as duplicates.
  Tab badge showing pending count.
- Toast notification when pending proposals exist on page load
- MCP server at /api/mcp exposing tools: flag_customer, list_actions,
  get_customer_risk, get_churn_summary. Auth via Snowflake ingress.
  flag_customer uses the same one-open-action-per-customer rule as
  POST /api/actions and returns "already flagged: <action_id>" as a
  tool error instead of creating a duplicate.

UI:
- Multi-phase loading: thinking indicator, live status steps from the
  stream, shimmer, then the streamed answer
- All agent entry points (header button, contextual row/card actions,
  Agent Suggested tab) use the same sparkle/wand icon for consistency
- Table/list action columns: icon buttons with label as tooltip
- Approval buttons (Approve, Dismiss, Approve All): icon + label
- Action cards show customer, action, and rule reason
- Consistent with the existing ChurnGuard design language

Constraints:
- The agent never writes directly; Approve calls POST /api/actions
- Keep Dashboard and Action Board working; Action Board refreshes
  after approve
- MCP server shares the same auth as the app's API routes
- Keep the APP_ORIGIN usage marker from AGENTS.md

Output:
- Agent-assisted ChurnGuard with Agent Suggested tab, MCP server,
  redeployed to the same Application Service URL

What Happens

Key Concepts

Verify

# Add the ChurnGuard MCP server, then start the CLI
cd churnguard
cortex mcp add churnguard "$(snow app open --print-only --connection quickstart)/api/mcp" \
  --type http --snowflake-connection quickstart
cortex mcp reconnect churnguard   # should report 4 tools
cd ..
cortex

Troubleshooting

-- Grants
SHOW GRANTS TO ROLE SFQUICKSTART_CHURNGUARD_ROLE;

-- Actions, the dynamic table, and the alert
SELECT * FROM SFQUICKSTART_CHURNGUARD.PUBLIC.ACTIONS ORDER BY CREATED_AT DESC LIMIT 5;
SELECT * FROM SFQUICKSTART_CHURNGUARD.PUBLIC.ACTION_SUMMARY;
SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME => 'SFQUICKSTART_CHURNGUARD.PUBLIC.ACTION_SUMMARY'
)) ORDER BY REFRESH_END_TIME DESC LIMIT 5;
SELECT * FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(
    SCHEDULED_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())
)) ORDER BY SCHEDULED_TIME DESC LIMIT 5;
SELECT * FROM SFQUICKSTART_CHURNGUARD.PUBLIC.NOTIFICATIONS ORDER BY CREATED_AT DESC LIMIT 5;

-- Agent, semantic view, and the daily task
SHOW AGENTS IN SCHEMA SFQUICKSTART_CHURNGUARD.PUBLIC;
SHOW SEMANTIC VIEWS IN SCHEMA SFQUICKSTART_CHURNGUARD.PUBLIC;
SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
    TASK_NAME => 'AGENT_SUGGESTION_TASK'
)) ORDER BY SCHEDULED_TIME DESC LIMIT 5;
SELECT * FROM PROPOSED_ACTIONS ORDER BY PROPOSED_AT DESC LIMIT 10;
SHOW TASKS LIKE 'AGENT_SUGGESTION_TASK';
ALTER TASK AGENT_SUGGESTION_TASK RESUME;   -- if suspended
EXECUTE TASK AGENT_SUGGESTION_TASK;        -- run it now

Merge conflict when moving to the next iteration

git checkout --theirs <file> && git add <file> && git commit --no-edit

Anything else

"I'm running the ChurnGuard quickstart and got this error: [paste error]. What's wrong and how do I fix it?"

Cleanup

snow sql -f scripts/cleanup.sql --connection quickstart

-- Manual cleanup: run these in order
USE ROLE ACCOUNTADMIN;
DROP APPLICATION SERVICE IF EXISTS SFQUICKSTART_CHURNGUARD.PUBLIC.CHURNGUARD;
DROP DATABASE IF EXISTS SFQUICKSTART_CHURNGUARD;
DROP WAREHOUSE IF EXISTS SFQUICKSTART_CHURNGUARD_WH;
DROP ROLE IF EXISTS SFQUICKSTART_CHURNGUARD_ROLE;

cortex mcp remove churnguard

Conclusion And Resources

What You Learned

Build Your Own App

Goal: Personalize ChurnGuard for each signed-in user.

Requirements:
- Identify the signed-in user from the sf-context-current-user request header
  (or CURRENT_USER() in a query run with the callersRights: true option).
  Snowflake already handles login.
- Show the user's name and role in the header, with a profile menu
- Store preferences (default risk filter, default page) in a USER_PREFERENCES table
- Show "My actions" on the Action Board: actions assigned to the current user
- Only users with the CHURNGUARD_MANAGER role can see Approve All

Constraints:
- Check roles in the API route, not only in the UI
- Keep per-request identity in request scope, never in module-level variables

Output: Redeployed app with profile menu, saved preferences, and role-based approval.

Goal: Let users take a filtered customer list into a spreadsheet.

Requirements:
- Add an "Export CSV" button to the customer table
- Export the rows that match the current filters, not only the current page
- Name the file churnguard-<risk>-<date>.csv

Output: Redeployed app with CSV export.

Related Resources