Overview

Architecture

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

Run the Setup Script

Import and Configure the Notebook

Generate Audio Files

USE DATABASE tasty_audio_db;
USE SCHEMA orders;
SELECT COUNT(*) AS phrase_count FROM ORDER_PHRASES;

import requests
import io
import urllib.parse
from snowflake.snowpark.context import get_active_session

session = get_active_session()

phrases_df = session.sql("SELECT phrase_id, order_text FROM ORDER_PHRASES").collect()

for row in phrases_df:
    phrase_id = row['PHRASE_ID']
    text = row['ORDER_TEXT']
    filename = f"order_{str(phrase_id).zfill(2)}.mp3"

    encoded_text = urllib.parse.quote(text)
    url = f"https://translate.google.com/translate_tts?ie=UTF-8&client=tw-ob&tl=en&q={encoded_text}"
    response = requests.get(url)

    audio_buffer = io.BytesIO(response.content)
    session.file.put_stream(
        audio_buffer,
        f"@AUDIO_STAGE/{filename}",
        auto_compress=False,
        overwrite=True
    )
    print(f"Generated: {filename}")

print(f"\nGenerated {len(phrases_df)} audio files!")

ALTER STAGE AUDIO_STAGE REFRESH;
LIST @AUDIO_STAGE;

Note: Snowflake Notebooks do not support pip install. The requests library is available by default — use it for any HTTP API calls.

Test Transcription

SELECT AI_TRANSCRIBE(TO_FILE('@AUDIO_STAGE', 'order_01.mp3')) AS transcription;

Run the Full AI Pipeline

INSERT INTO CUSTOMER_ORDERS (
    audio_file, 
    raw_transcript, 
    item_name, 
    quantity, 
    size, 
    special_instructions
)

WITH staged_files AS (
    SELECT RELATIVE_PATH AS filename
    FROM DIRECTORY(@AUDIO_STAGE)
    WHERE RELATIVE_PATH LIKE '%.mp3'
),

transcriptions AS (
    SELECT 
        filename,
        AI_TRANSCRIBE(TO_FILE('@AUDIO_STAGE', filename)) AS transcript
    FROM staged_files
),

extracted AS (
    SELECT 
        filename,
        transcript:text::VARCHAR AS transcript_text,
        AI_COMPLETE(
            model => 'mistral-large2',
            prompt => 'Extract order details from: ' || transcript:text::VARCHAR,
            response_format => {
                'type': 'json',
                'schema': {
                    'type': 'object',
                    'properties': {
                        'order_items': {
                            'type': 'array',
                            'items': {
                                'type': 'object',
                                'properties': {
                                    'item_name': {'type': 'string'},
                                    'quantity': {'type': 'integer'},
                                    'size': {'type': 'string'},
                                    'special_instructions': {'type': 'string'}
                                },
                                'required': ['item_name', 'quantity']
                            }
                        }
                    },
                    'required': ['order_items']
                }
            }
        ) AS extracted_json
    FROM transcriptions
)

SELECT 
    filename,
    transcript_text,
    f.value:item_name::STRING,
    f.value:quantity::INTEGER,
    COALESCE(f.value:size::STRING, ''),
    COALESCE(f.value:special_instructions::STRING, '')
FROM extracted,
LATERAL FLATTEN(input => PARSE_JSON(extracted_json):order_items) f;

SELECT * FROM CUSTOMER_ORDERS ORDER BY order_id;

Create a Semantic View

CREATE OR REPLACE SEMANTIC VIEW customer_orders_semantic
  TABLES (
    orders AS tasty_audio_db.orders.CUSTOMER_ORDERS
      PRIMARY KEY (order_id)
  )
  DIMENSIONS (
    orders.item_name AS item_name,
    orders.size AS size,
    orders.order_status AS order_status,
    orders.order_timestamp AS order_timestamp,
    orders.special_instructions AS special_instructions
  )
  METRICS (
    orders.total_quantity AS SUM(quantity),
    orders.order_count AS COUNT(DISTINCT order_id)
  );

Run Analytics Queries

SELECT item_name, COUNT(*) AS order_count, SUM(quantity) AS total_qty
FROM CUSTOMER_ORDERS 
GROUP BY item_name 
ORDER BY order_count DESC;

Create a Cortex Agent

Create the Agent

Add Description and Sample Questions

Add Cortex Analyst as a Tool

Configure Orchestration

Set Up Access

GRANT USAGE ON AGENT tasty_audio_db.orders.tasty_orders_agent TO ROLE <role_name>;

Test the Agent

User: What are the most popular items?

Agent: Based on the TastyBytes voice order data, here are the most popular items:

- Hamburger - 15 orders (23 total quantity)
- Pepperoni Pizza - 12 orders (18 total quantity)
- Cheeseburger - 10 orders (14 total quantity)
- Fries - 9 orders (16 total quantity)
- Chocolate Milkshake - 7 orders (11 total quantity)

Hamburgers are the clear favorite, accounting for nearly 20% of all orders!

Using the Agent via REST API

curl -X POST "$SNOWFLAKE_ACCOUNT_URL/api/v2/cortex/threads" \
  --header "Authorization: Bearer $PAT" \
  --header "Content-Type: application/json" \
  --data '{"origin_application": "tasty_orders_app"}'

curl -X POST "$SNOWFLAKE_ACCOUNT_URL/api/v2/databases/TASTY_AUDIO_DB/schemas/ORDERS/agents/TASTY_ORDERS_AGENT:run" \
  --header "Authorization: Bearer $PAT" \
  --header "Content-Type: application/json" \
  --data '{
    "thread_id": "<thread_id>",
    "parent_message_id": "0",
    "messages": [{
      "role": "user",
      "content": [{"type": "text", "text": "What are the most popular items?"}]
    }]
  }'

Conclusion and Resources

What You Learned

Key Concepts

Cleanup

DROP AGENT IF EXISTS tasty_audio_db.orders.tasty_orders_agent;
DROP DATABASE IF EXISTS tasty_audio_db;
DROP INTEGRATION IF EXISTS google_tts_integration;
DROP DATABASE IF EXISTS policy_db;

Related Resources