Executive Summary
For enterprises managing large-scale physical distributions, tracking the “Last Mile” is a critical business function. We developed a Cortex Analyst Agent within Snowflake that connects an internal data warehouse directly to business users. By automating the ingestion of UPS API data and layering it with a conversational AI agent, we enabled non-technical staff to query package statuses and delivery performance using natural language natively within the Snowflake environment.
The Challenge: The “Internal Data Bottleneck”
The organization ships thousands of packages daily to customers. While the shipping data was successfully pulled via API and stored in the Snowflake Data Warehouse, accessing it presented several hurdles:
Manual Tracking Friction
Customer Support and Operations teams had to manually check tracking numbers or wait for static daily reports.
The “Request-Response” Delay
Data analysts were overwhelmed with repetitive, ad-hoc questions regarding shipment status and transit delays.
Data Accuracy & Hallucinations
Standard LLMs often fail to understand specific logistics schemas or column names, making them unreliable for generating accurate SQL for sensitive shipping data.
The Solution: Agentic Text-to-SQL for Logistics
We shifted the workflow from “Request a Report” to “Ask the Data” by implementing a specialized AI Agent within the Snowflake AI Data Cloud.
Phase 1: The Data Pipeline
- Automated Ingestion: We built a robust pipeline to hit the Carrier API (UPS), fetching granular package details including Status, Timestamps, Exception Codes, and Delivery Confirmations.
- Warehouse Integration: This data was normalized and stored in a dedicated “Shipping & Logistics” schema within the Snowflake Data Warehouse, serving as the single source of truth.
Phase 2: Building & “Training” the Agent
Instead of a simple chatbot, we deployed a Cortex Analyst Agent designed for high-precision retrieval:
- The Semantic Layer: We “trained” the agent by creating a YAML semantic model. We defined specific business logic for terms like “Late,” “Delivered,” and “Pending” based on the company’s internal rules.
- Metadata Enrichment: We provided the agent with detailed descriptions for every column in the shipping tables. This ensured the agent knew exactly which primary keys and attributes to reference for “package status” queries.
- Natural Language Processing: Using Snowflake’s native capabilities, the agent was configured to translate human questions into precise, executable SQL queries against the live warehouse data.
Phase 3: Native Validation
- The system was tested and refined directly within Snowflake Snowsight (Worksheets & AI Studio). By interacting with the agent natively, we verified the SQL generation and reasoning logic in real-time without needing an external application layer.
Tech Stack Overview
| Component | Technology |
| Data Platform | Snowflake |
| AI Engine | Snowflake Cortex Analyst (Agentic Text-to-SQL) |
| Metadata Layer | YAML-based Semantic Modeling |
| Security & Governance | Snowflake Native RBAC |
The Impact & Results
- 95% Faster Query Response: What previously required a manual SQL request now takes less than 10 seconds via a natural language query.
- Zero Data Exfiltration: Because testing and execution happened natively in Snowflake, sensitive shipping data never left the secure corporate perimeter.
- Self-Service Empowerment: Non-technical stakeholders can now ask complex questions like, “Show me all packages in the Northeast with a ‘Delivery Exception’ status,” and receive real-time data instantly.
- High Precision: By using a Semantic Model instead of a generic chatbot, we achieved near-perfect accuracy in SQL generation for logistics-specific queries.
