An end-to-end analytics project built live during an in-person Snowflake Cortex Code workshop. Starting from 10 raw CSV files for a fictional food-delivery company, QuickBite, it uses Snowflake Cortex Code (Snowflake's AI coding assistant) to load the data, find out why refunds spiked in autumn 2026, and present the answer in a liquid-glass Streamlit dashboard for the COO.
All data in this repo is synthetic and was provided for the workshop. Names, emails and phone numbers are fake (
@example.com).
"Refunds as a share of GMV jumped from ~3.5% to 4.76% by October 2026. Why?"
Almost all of the increase comes from one zone, Whitefield-East (Bengaluru). On 8 Sep 2026, the rider-assignment algorithm v2.3 widened the assignment radius to 10 km. In Whitefield-East alone, the average distance to the assigned rider tripled (2.1 km to 6.0 km), pickup waits grew by about 6 minutes, and the late-delivery rate (over 45 min) rose from 6.9% to 28.4%. Rain made it much worse: 58% of rainy-hour orders were late. Customers asked for refunds for Late delivery and Cold food. The newly launched Cloud Kitchens Hub in Hyderabad was a red herring (about 5% of the increase).
| Metric (Whitefield-East) | Before v2.3 | After v2.3 | All other zones |
|---|---|---|---|
| Avg rider assign distance | 2.11 km | 6.01 km | 2.10 km |
| Avg pickup wait | 15.2 min | 21.3 min | 15.1 min |
| Late deliveries (> 45 min) | 6.9% | 28.4% | 7.3% |
| Late in rain | 9.6% | 58.3% | 9.9% |
| Refunds % of GMV (Aug to Oct) | 3.37% | 11.84% | ~3.8% |
Whitefield-East accounts for ~78% of the August-to-October refund increase. AI_CLASSIFY on 446 support chats from the zone showed that 65% complained that the rider was assigned too far away.
.
├── data/ # 10 raw CSVs (synthetic workshop data)
│ ├── app_releases.csv # release log (consumer app, rider-assign algo, ...)
│ ├── cities_zones.csv # 40 delivery zones across 6 cities
│ ├── customers.csv # 30,000 customers
│ ├── orders.csv # 247,741 delivered orders (Jan-Oct 2026), the fact table
│ ├── order_items.csv # 521,607 basket lines
│ ├── refunds.csv # 15,057 refunds with reason
│ ├── restaurants.csv # 1,386 restaurants (incl. 60 Cloud Kitchens Hub brands)
│ ├── riders.csv # 2,828 riders
│ ├── support_chats.csv # 6,728 customer support messages
│ └── weather_hourly.csv # hourly rain / temperature per city
├── sql/
│ └── quickbite_setup_and_analysis.sql # DB/schema/stage, INFER_SCHEMA load, analysis, Cortex AI
├── streamlit_app.py # COO dashboard (Streamlit, Snowsight + local)
├── .streamlit/config.toml # dark theme
├── environment.yml / pyproject.toml # Streamlit in Snowflake dependencies
└── snowflake.yml # Snowflake CLI project definition
CITIES_ZONES (ZONE_ID)
┌──────────┬──────┴─────┬──────────────┐
CUSTOMERS RESTAURANTS RIDERS (ORDERS.ZONE_ID)
└──────────┼────────────┘
▼
ORDERS ──── WEATHER_HOURLY (CITY + hour of PLACED_AT)
│ └──── APP_RELEASES (ASSIGNMENT_ALGO_VERSION)
┌───────────────┼───────────────┐
ORDER_ITEMS REFUNDS SUPPORT_CHATS
One row in ORDERS is one delivered order, from placement to delivery (ORDER_ID is unique).
streamlit_app.py is a single-page COO Refund Brief with an iOS-style liquid-glass UI
(frosted panels, blur, a lift on hover):
- KPI cards: October refunds, refund % of GMV, orders, and the share of the increase from the top zone
- Monthly GMV vs refund rate: click bars, or use the month chips, to filter the whole brief to one or more months
- Refund increase by zone (Aug to Oct, or refunds for the selected months)
- Zone selector: weekly late-delivery % for any zone vs all other zones, with the v2.3 release marked
- Rain effect: late % in rain vs dry, before vs after v2.3
- Red herring: Cloud Kitchens Hub's share of the increase
- What customers say: Cortex
AI_CLASSIFYcomplaint themes for Whitefield-East chats - Recommendation: cap the v2.3 radius in Whitefield-East, add a rain guardrail, add rider supply
It runs in Snowsight (Streamlit in Snowflake, using the active Snowpark session) and locally (falls back to a named Snowflake connection).
Open sql/quickbite_setup_and_analysis.sql in a Snowsight worksheet or in Cortex Code, then:
- Create
QUICKBITE_DBwith theRAWandANALYTICSschemas, the stage and the CSV file format (section 1). PUTeach file fromdata/into its own stage folder, e.g.@QUICKBITE_STAGE/orders/(section 2).- Run the
INFER_SCHEMA+COPY INTOloop, which builds one table per CSV from its header (section 3). - Run the Cortex
AI_CLASSIFYstep to buildANALYTICS.WFE_CHAT_THEMES(section 5). The dashboard reads this table.
Create a Streamlit app in Snowsight (warehouse runtime, COMPUTE_WH), paste in streamlit_app.py,
and add environment.yml. No external access integration is needed, so this works on trial accounts.
python -m venv .venv
.venv/Scripts/pip install "streamlit[snowflake]>=1.56" "snowflake-connector-python>=3.3"
# Use a connection defined in ~/.snowflake/connections.toml or config.toml
export SNOWFLAKE_DEFAULT_CONNECTION_NAME=<your_connection> # PowerShell: $env:SNOWFLAKE_DEFAULT_CONNECTION_NAME="<your_connection>"
.venv/Scripts/streamlit run streamlit_app.pyINFER_SCHEMA+CREATE TABLE ... USING TEMPLATE+COPY INTO ... MATCH_BY_COLUMN_NAME(schema-from-header loading)- Snowflake Scripting (
EXECUTE IMMEDIATEloop over tables) - Cortex AI SQL functions:
AI_CLASSIFY,AI_SUMMARIZE_AGG,AI_COUNT_TOKENS - Streamlit in Snowflake (Snowpark session) and local Streamlit (
st.connection("snowflake")) - Snowflake Git integration (
API INTEGRATION+GIT REPOSITORY) linking this repo
Every step, from loading and modelling to the root-cause analysis, the AI classification, the dashboard and this repo, was done by prompting Snowflake Cortex Code in plain English during the workshop. The analysis went from "show me monthly refunds", to "which zone?", to "what changed in early September?", to "does rain make it worse?", to "what are customers saying?". Cortex Code wrote and ran the SQL against live Snowflake data at each step.
Built by Deva Dharshan D at a Snowflake Cortex Code hands-on workshop.