Supply Chain Monitor

Inspiration

Detroit runs on its supply chain. A single unreliable supplier doesn't just cause a defect -- it cascades into warranty claims, production delays, and eroded customer trust. Today, procurement teams manually review supplier scorecards in spreadsheets, cross-referencing quality reports with warranty complaints and open purchase orders. By the time a pattern emerges, millions of dollars in parts are already in transit.

We asked: what if Snowflake could watch the entire supply chain continuously and flag risk before it becomes a recall?

What it does

Supply Chain Risk Monitor is a continuously refreshing risk intelligence pipeline built entirely on Snowflake. It:

• Classifies 8,000 defects across 400 suppliers using AI_CLASSIFY, categorizing free-text descriptions into risk tiers (safety-critical, performance, cosmetic, wear-and-tear)
• Scores customer sentiment on 30,000 warranty claim verbatims using SENTIMENT, surfacing which suppliers generate the angriest customers
• Computes a composite risk score (0--100) per supplier by blending five weighted signals: AI-classified risk, defect volume, quality rating, warranty volume, and negative sentiment
• Flags at-risk purchase orders -- every open PO from a warning- or critical-risk supplier, totaling $207.4B in exposure across 16,408 orders
• Alerts every 60 minutes when critical-risk suppliers remain unmitigated
• Snapshots risk scores daily at 6 AM ET for trend tracking
• Generates on-demand AI risk narratives per supplier using AI_COMPLETE (llama3.1-8b)
• Delivers everything through a five-tab Streamlit dashboard with dark/light theme toggle: Executive Overview, Manager Dashboard, Supplier Deep Dive, Data Explorer, and Alerts & System

How we built it

The entire project was built through conversation with CoCo (Cortex Code) across a single hackathon day, without switching to another IDE.

Pipeline architecture

1. SUPPLIER_QUALITY_SCORE -- a Dynamic Table (TARGET_LAG 1 hour, FULL refresh) that joins suppliers, defects, warranty claims, and purchase orders, running AI_CLASSIFY on every defect and SENTIMENT on every warranty verbatim to produce a composite risk score per supplier (400 rows)
2. AT_RISK_PURCHASE_ORDERS -- a second Dynamic Table chained from the first, joining open POs against supplier risk scores (16,408 rows)
3. SUPPLIER_RISK_ALERT -- an Alert on a 60-minute schedule that fires when critical-risk suppliers exist with non-suspended status, logging to ALERT_HISTORY
4. RISK_SCORE_SNAPSHOT -- a Task on a daily CRON schedule (6 AM ET) that snapshots all scores to RISK_SCORE_HISTORY for trend analysis
5. UNMANAGED_RISK_COUNT -- a Data Metric Function that counts critical/warning suppliers lacking mitigation
6. SUPPLY_CHAIN_MONITOR -- a Streamlit-in-Snowflake app with five tabs, Vega-Lite charts, color-coded risk tables, multi-filter controls, CSV export, and an AI_COMPLETE button that generates on-demand supplier risk narratives

Composite risk formula

Risk = 0.30 × AI_Risk + 0.20 × Defect_Volume + 0.20 × Inventory_Quality + 0.15 × Warranty_Volume + 0.15 × Negative_Sentiment

$$\text{Risk} = 0.30 \times \text{AI} + 0.20 \times \text{Defects} + 0.20 \times \text{Quality} + 0.15 \times \text{Warranty} + 0.15 \times \text{Sentiment}$$

Each component is normalized to 0--100. Final score maps to risk levels:

8 Snowflake features used

Challenges we faced

• Dynamic Table role limitations -- DTs run as the owner role and cannot use secondary roles, so we had to grant USAGE and SELECT on every source schema directly to ATTENDEE_ROLE
• DMF on Dynamic Tables -- ALTER TABLE ... SET DATA_METRIC_FUNCTION failed on DTs, so we embedded the equivalent logic directly in the Streamlit app's Alerts & System tab
• st.rerun() crash -- The SiS runtime does not support st.rerun() despite reporting Streamlit 1.52.2. We had to revert to st.experimental_rerun() after a production crash
• FULL refresh cost -- Both chained DTs resolved to FULL refresh mode due to complex joins, meaning every refresh reprocesses all 8,000 AI_CLASSIFY and 30,000 SENTIMENT calls
• Cross-origin QA -- Iframe constraints in Snowsight prevented automated tab-switching during QA testing -- each click triggers a Streamlit rerun that resets to the default tab

What we learned

CoCo can go from an empty Snowflake account to a deployed, production-hardened pipeline through conversation. Chained Dynamic Tables handle refresh dependencies automatically -- no orchestration code needed. AI_CLASSIFY and SENTIMENT run directly inside SQL, eliminating the need for a separate Python service. Having two AI agents (CoCo sessions) working in parallel -- one on code, one on QA and documentation -- was effective but required careful coordination to avoid stepping on each other's changes.

How Baked Is It: 4/5 (Usable by Others)

All pipeline objects are deployed and running on real data. The dashboard renders live data from chained Dynamic Tables with error handling, loading spinners, empty state guards, and input sanitization. Alerts and Tasks are active. Dark/light theme works across all 5 tabs. CSV export is functional. AI-generated risk narratives work on demand.

The main gap to a 5 would be role-based access control, email/Slack alert delivery, and automated testing.

Built With

• ai
• ai-classify
• ai-complete
• ai-sentiment
• alerts
• cortex
• data
• dynamic
• functions
• metric
• python
• snowflake
• sql
• streamlit
• tables
• tasks