Overview

How this differs from other support quickstarts: Unlike call-center-analytics-with-ai-transcribe-and-cortex-agents (which builds a full Cortex Agent for Q&A) or streamlining-support-case-analysis (which uses LangChain for summarization), this guide takes a pure-SQL, zero-orchestration approach. A single Dynamic Table handles transcription, classification, sentiment scoring, and recommendation generation — no agents, no external frameworks, no scheduler. New tickets are enriched automatically on refresh.

Architecture

  [Web/Email Forms] ──►  RAW.SUPPORT_TICKETS (text)
  [Phone Calls]     ──►  @SUPPORT_AUDIO_STAGE (audio files)
                              │
                              ▼  Dynamic Table (auto-refresh)
                    ANALYTICS.ENRICHED_TICKETS
                      ← AI_TRANSCRIBE  (audio → text)
                      ← AI_CLASSIFY    (issue category)
                      ← AI_COMPLETE    (sentiment score)
                      ← AI_COMPLETE    (recommended action)
                              │
                              ▼
                    Streamlit in Snowflake (Ops Dashboard)

What You'll Need

What You Will Learn

What You Will Build

Setup

-- Create the database and schemas
CREATE DATABASE IF NOT EXISTS SUPPORT_OPS_AI;
CREATE SCHEMA IF NOT EXISTS SUPPORT_OPS_AI.RAW;
CREATE SCHEMA IF NOT EXISTS SUPPORT_OPS_AI.ANALYTICS;

-- Create a warehouse for AI Function processing
CREATE WAREHOUSE IF NOT EXISTS SUPPORT_OPS_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

USE DATABASE SUPPORT_OPS_AI;
USE SCHEMA RAW;
USE WAREHOUSE SUPPORT_OPS_WH;

-- Create the stage for phone call recordings
CREATE STAGE IF NOT EXISTS SUPPORT_AUDIO_STAGE
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
  DIRECTORY = (ENABLE = TRUE);

-- Create the raw tickets table
CREATE TABLE IF NOT EXISTS SUPPORT_TICKETS (
  ticket_id     VARCHAR DEFAULT UUID_STRING(),
  channel       VARCHAR,
  raw_text      VARCHAR,
  audio_path    VARCHAR,
  created_at    TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO SUPPORT_TICKETS (channel, raw_text) VALUES
('email', 'Our API integration has been timing out for the past 3 hours. We are losing transactions and this is affecting our revenue. This needs to be fixed immediately.'),
('web', 'I cannot log in with SSO. Every time I click the login button it redirects me back to the login page. I have tried clearing cookies and using incognito mode. Nothing works.'),
('email', 'I was charged the full monthly rate even though I upgraded mid-cycle. According to your pricing page, upgrades should be prorated. Please issue a credit for the difference.'),
('web', 'Would love to see a dark mode option in the dashboard. The current white background is harsh during late-night monitoring sessions.'),
('email', 'Hi, just wanted to confirm whether your platform supports webhooks for real-time event notifications. We are evaluating tools for our integration layer.'),
('web', 'The API has been returning 504 errors intermittently since yesterday morning. Our batch processing jobs are failing and we have a client delivery deadline tomorrow.'),
('email', 'I have been locked out of my account for 2 days now. The password reset email never arrives. I have checked spam. This is completely unacceptable for a paid service.'),
('web', 'Your invoice from last month shows a charge for 50 seats but we only have 32 active users. Can someone look into this and issue a correction?'),
('email', 'It would be really helpful if the analytics dashboard had an export-to-PDF feature. Right now I have to screenshot everything for my weekly reports.'),
('web', 'Just wanted to say the new onboarding flow is excellent. Took me 5 minutes to get set up compared to an hour last year. Great improvement.'),
('email', 'API latency has spiked to 8 seconds on average. Normal is under 200ms. Something is seriously wrong on your end. We need an update ASAP.'),
('web', 'SSO login works fine on Chrome but fails completely on Firefox. The SAML assertion seems to be malformed for Firefox user agents.'),
('email', 'Can you explain why my bill went up 40% this month? I did not change my plan or add any users. There is no explanation on the invoice.'),
('web', 'Feature request: please add support for custom RBAC roles. The current admin/viewer split is too coarse for our team structure.'),
('email', 'Quick question: do you offer volume discounts for organizations with 500+ seats? We are planning a company-wide rollout next quarter.');

-- Refresh directory metadata after uploading files
ALTER STAGE SUPPORT_AUDIO_STAGE REFRESH;

-- Insert phone tickets referencing the uploaded audio files
INSERT INTO SUPPORT_TICKETS (channel, audio_path)
SELECT 'phone', RELATIVE_PATH
FROM DIRECTORY(@SUPPORT_AUDIO_STAGE);

Transcribe Phone Calls

-- Preview transcription (run only if you uploaded audio files)
SELECT
  ticket_id,
  AI_TRANSCRIBE(
    TO_FILE('@SUPPORT_AUDIO_STAGE', audio_path)
  ):text::VARCHAR AS transcript
FROM RAW.SUPPORT_TICKETS
WHERE channel = 'phone';

Classify and Score Tickets

Classify with AI_CLASSIFY

SELECT
  ticket_id,
  raw_text,
  AI_CLASSIFY(
    raw_text,
    ARRAY_CONSTRUCT(
      'System Bug: API Timeout',
      'Account Access: SSO',
      'Billing: Prorated Upgrades',
      'Feature Request',
      'General Inquiry'
    )
  ):labels[0]::VARCHAR AS issue_category
FROM RAW.SUPPORT_TICKETS
WHERE channel != 'phone'
LIMIT 5;

Score Sentiment Numerically

SELECT
  ticket_id,
  raw_text,
  TRY_TO_DOUBLE(
    AI_COMPLETE(
      'claude-sonnet-4-5',
      CONCAT(
        'Rate the sentiment of this support ticket on a scale from -1.0 ',
        '(extremely frustrated) to +1.0 (satisfied/positive). ',
        'Return ONLY a decimal number, no explanation. ',
        'Ticket: ', raw_text
      )
    )::VARCHAR
  ) AS sentiment_score
FROM RAW.SUPPORT_TICKETS
WHERE channel != 'phone'
LIMIT 5;

Build the Dynamic Table Pipeline

USE SCHEMA SUPPORT_OPS_AI.ANALYTICS;
USE WAREHOUSE SUPPORT_OPS_WH;

CREATE OR REPLACE DYNAMIC TABLE ENRICHED_TICKETS
  TARGET_LAG = '1 hour'
  REFRESH_MODE = FULL
  WAREHOUSE = SUPPORT_OPS_WH
AS
WITH transcribed AS (
  SELECT
    ticket_id, channel, created_at,
    CASE
      WHEN channel = 'phone'
        THEN AI_TRANSCRIBE(
               TO_FILE('@RAW.SUPPORT_AUDIO_STAGE', audio_path)
             ):text::VARCHAR
      ELSE raw_text
    END AS ticket_text
  FROM RAW.SUPPORT_TICKETS
),
classified AS (
  SELECT *,
    AI_CLASSIFY(
      ticket_text,
      ARRAY_CONSTRUCT(
        'System Bug: API Timeout',
        'Account Access: SSO',
        'Billing: Prorated Upgrades',
        'Feature Request',
        'General Inquiry'
      )
    ):labels[0]::VARCHAR AS issue_category,
    TRY_TO_DOUBLE(
      AI_COMPLETE(
        'claude-sonnet-4-5',
        CONCAT('Rate sentiment -1.0 to +1.0. Return only a number. Ticket: ', ticket_text)
      )::VARCHAR
    ) AS sentiment_score
  FROM transcribed
)
SELECT
  ticket_id, channel, created_at, ticket_text,
  issue_category, sentiment_score,
  AI_COMPLETE(
    'claude-sonnet-4-5',
    CONCAT(
      'Support ops manager. Category: ', issue_category,
      '. Sentiment: ', COALESCE(ROUND(sentiment_score, 2)::VARCHAR, 'unknown'),
      '. Recommend one operational action in 1-2 sentences.'
    )
  )::VARCHAR AS recommended_action
FROM classified;

Note: The first refresh may take 15–30 minutes as AI Functions transcribe and enrich all existing tickets. If the table appears empty, wait and re-query.

Verify the Pipeline

-- Check that the Dynamic Table populated
SELECT * FROM ANALYTICS.ENRICHED_TICKETS LIMIT 10;

-- Check refresh status
SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'SUPPORT_OPS_AI.ANALYTICS.ENRICHED_TICKETS'
)) ORDER BY REFRESH_START_TIME DESC LIMIT 5;

Build the Streamlit Dashboard

import streamlit as st
from snowflake.snowpark.context import get_active_session
import pandas as pd

session = get_active_session()

st.title("Support Operations Intelligence")
st.caption("Powered by Snowflake AI Functions · Refreshes every hour")

df = session.sql("""
    SELECT
        issue_category,
        COUNT(*)                          AS volume,
        ROUND(AVG(sentiment_score), 2)    AS avg_sentiment,
        ANY_VALUE(recommended_action)     AS recommended_action
    FROM analytics.enriched_tickets
    WHERE created_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())
    GROUP BY issue_category
    ORDER BY volume DESC
""").to_pandas()

def sentiment_label(score):
    if score <= -0.7:   return "Frustrated"
    elif score <= -0.4: return "Stressed"
    elif score <= -0.1: return "Annoyed"
    elif score <= 0.3:  return "Neutral"
    else:               return "Positive"

df["Sentiment Label"] = df["AVG_SENTIMENT"].apply(sentiment_label)
df["Avg. Sentiment"]  = df.apply(
    lambda r: f"{r['AVG_SENTIMENT']} ({r['Sentiment Label']})", axis=1
)

col1, col2, col3 = st.columns(3)
col1.metric("Total Tickets (30d)", f"{df['VOLUME'].sum():,}")
col2.metric("Avg. Sentiment", f"{(df['AVG_SENTIMENT'] * df['VOLUME']).sum() / df['VOLUME'].sum():.2f}")
col3.metric("Issue Categories", len(df))

st.divider()

st.subheader("Issue Summary & Recommended Actions")
st.dataframe(
    df[["ISSUE_CATEGORY", "Avg. Sentiment", "VOLUME", "RECOMMENDED_ACTION"]].rename(columns={
        "ISSUE_CATEGORY":       "Issue Category",
        "VOLUME":               "Volume",
        "RECOMMENDED_ACTION":   "Sample Recommendation"
    }),
    use_container_width=True,
    hide_index=True
)

st.subheader("Daily Sentiment Trend")
trend_df = session.sql("""
    SELECT
        DATE_TRUNC('day', created_at) AS day,
        issue_category,
        ROUND(AVG(sentiment_score), 3) AS avg_sentiment
    FROM analytics.enriched_tickets
    WHERE created_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())
    GROUP BY 1, 2
    ORDER BY 1
""").to_pandas()

st.line_chart(
    trend_df.pivot(index="DAY", columns="ISSUE_CATEGORY", values="AVG_SENTIMENT")
)

Best Practices

PII Handling with AI_REDACT

SELECT
  ticket_id,
  AI_REDACT(ticket_text) AS redacted_text,
  issue_category,
  sentiment_score,
  recommended_action
FROM ANALYTICS.ENRICHED_TICKETS
LIMIT 5;

Cost Optimization

Human-in-the-Loop

Cleanup

DROP DATABASE IF EXISTS SUPPORT_OPS_AI;
DROP WAREHOUSE IF EXISTS SUPPORT_OPS_WH;

Conclusion And Resources

What You Learned

Related Resources