Building AI-Powered Analytics with Snowflake Cortex and dbt
How to transform customer conversations into competitive advantage
"How many of you have thousands of customer interactions, reviews, support tickets just sitting in your data platform but struggle to extract actionable insights from them?"
Executive Summary
Customer intelligence has evolved from basic reporting to AI-powered insight generation that can predict churn, identify upsell opportunities, and automatically process multilingual feedback in real-time. In our recent Snowflake-dbt Labs webinar, we demonstrated how organisations can build comprehensive customer intelligence platforms using Snowflake Cortex AI, dbt, and interactive Streamlit in Snowflake dashboards.
This post serves as your comprehensive guide to implementing these AI-powered customer analytics solutions, covering everything from architecture decisions to practical implementation patterns. Whether you're a data engineer looking to leverage AI functions in your transformations or a business leader seeking to understand the art of the possible, this guide will show you how to turn customer conversations into competitive intelligence.
Key Takeaways:
Modern customer intelligence requires AI-native data pipelines that can process unstructured text at scale
Snowflake Cortex AISQL (LLM functions) integrate seamlessly into dbt workflows for sentiment analysis and multilingual processing
Cross-departmental analytics platforms deliver measurable ROI by enabling proactive customer relationship management
The combination of Snowflake + dbt + Streamlit provides a complete solution from data ingestion to business user consumption
"It's just SQL that understands human language."
The Customer Intelligence Imperative
Every day, your customers are telling you exactly what they think about your products, services, and experiences. They're sharing feedback through support tickets, product reviews, social media interactions, and direct communications. The challenge isn't collecting this data, it's transforming the unstructured chaos of human communication into actionable business intelligence.
Traditional approaches to customer analytics focus on structured metrics: purchase history, demographic data, and behavioural patterns. While valuable, these approaches miss the nuanced insights hidden in customer conversations. When a customer writes "The product works fine, but the setup process was frustrating," traditional analytics might categorise this as a positive interaction based on the overall sentiment. An AI-powered approach recognises both the product satisfaction and the process improvement opportunity.
The modern customer intelligence platform we'll explore addresses three fundamental challenges:
Scale: Processing an unlimited number of customer interactions, reviews, and support tickets daily
Complexity: Understanding sentiment, intent, and emotion across multiple languages and channels
Speed: Delivering insights fast enough to enable proactive customer relationship management
Architecture: The Modern Customer Intelligence Stack
Our customer intelligence platform follows a modern data architecture that transforms raw customer conversations into strategic business insights through three distinct processing layers:
Staging Layer: The foundation where raw JSON customer data is cleaned, structured, and prepared for transformation. This includes customer profiles, interaction logs, product reviews, and support tickets.
Fact Layer: Core business entities enriched with Snowflake Cortex AISQL LLM functions. This is where the magic happens; unstructured text becomes sentiment scores, multilingual reviews get translated, and customer personas are classified.
Analytics Layer: Business logic that combines AI-enhanced facts into actionable insights. Customer churn risk scores, upsell opportunities, and cross-channel sentiment trends are calculated here.
Entity Relationship Design
The foundation of our customer intelligence platform is built on a well-designed data model that derives customer signals such as sentiment trend, churn risk, and upsell opportunity using the relationships between customers, their interactions, reviews, and support experiences:
Figure 1: ERD showing the core data model with customers at the centre.
dbt Transformation Pipeline
The power of dbt lies in its ability to create clear, testable, and maintainable data transformations. Our pipeline follows software engineering best practices with modularity and proper dependency management:
Figure 2: dbt DAG showing the transformation pipeline.
Technology Stack Integration
The power of this approach lies in how these technologies work together:
Snowflake provides the scalable data platform with native AI functions
dbt orchestrates transformations and applies software engineering best practices to analytics
Streamlit delivers interactive dashboards that business users actually want to use
Cortex Analyst enables natural language querying for ad-hoc analysis
Deep Dive: AI-Powered Data Transformations
Sentiment Analysis at Scale
The heart of our customer intelligence platform is the systematic application of Cortex LLM functions throughout the data pipeline. Let's examine how Snowflake Cortex transforms raw customer interactions:
-- Customer Interactions with AI-Enhanced Sentiment
SELECT
i.interaction_id,
i.customer_id,
i.interaction_date,
i.agent_id,
i.interaction_type,
i.interaction_notes,
-- AI-powered sentiment analysis
SNOWFLAKE.CORTEX.SENTIMENT(i.interaction_notes) AS sentiment_score
FROM {{ ref('stg_customer_interactions') }} i
WHERE i.interaction_notes IS NOT NULL
This simple SQL transformation adds profound analytical capabilities. The SNOWFLAKE.CORTEX.SENTIMENT function returns a score between -1 (very negative) and 1 (very positive), enabling quantitative analysis of qualitative feedback.
Multilingual Processing Pipeline
Global businesses need to process customer feedback in multiple languages. Our product review model demonstrates how Cortex AI handles this automatically:
-- Multilingual Review Processing
SELECT
r.review_id,
r.customer_id,
r.review_text,
r.review_language,
-- Sentiment analysis in original language
SNOWFLAKE.CORTEX.SENTIMENT(r.review_text) AS sentiment_score,
-- Automatic translation to English for unified analysis
CASE
WHEN CONTAINS(LOWER(r.review_language), 'german')
THEN SNOWFLAKE.CORTEX.TRANSLATE(r.review_text, 'de', 'en')
WHEN CONTAINS(LOWER(r.review_language), 'french')
THEN SNOWFLAKE.CORTEX.TRANSLATE(r.review_text, 'fr', 'en')
WHEN CONTAINS(LOWER(r.review_language), 'spanish')
THEN SNOWFLAKE.CORTEX.TRANSLATE(r.review_text, 'es', 'en')
-- Additional language support...
ELSE r.review_text
END AS review_text_english
FROM {{ ref('stg_product_reviews') }} r
This approach maintains the original context while enabling unified English-language analysis across all customer feedback, regardless of the original language.
Customer Persona Classification
The analytics layer combines multiple AI-enhanced signals to create comprehensive customer profiles:
-- AI-Powered Customer Persona Signals
SELECT
cb.customer_id,
cb.lifetime_value,
st.avg_sentiment,
st.sentiment_trend,
-- AI-derived persona classification
CASE
WHEN st.avg_sentiment > 0.5 AND COALESCE(tpat.ticket_count, 0) <= 1
THEN 'Satisfied'
WHEN st.avg_sentiment < -0.1 AND COALESCE(tpat.ticket_count, 0) >= 1
THEN 'Frustrated'
WHEN st.sentiment_volatility > 0.95
THEN 'Mixed'
WHEN st.sentiment_trend > 0.3
THEN 'Improving'
ELSE 'Neutral'
END AS derived_persona,
-- Predictive churn risk scoring
CASE
WHEN st.avg_sentiment < -0.1 AND COALESCE(tpat.ticket_count, 0) >= 1
THEN 'High'
WHEN st.avg_sentiment < 0 OR st.sentiment_trend < 0
THEN 'Medium'
ELSE 'Low'
END AS churn_risk
FROM {{ ref('stg_customers') }} cb
LEFT JOIN {{ ref('sentiment_trends') }} st USING (customer_id)
LEFT JOIN {{ ref('ticket_patterns') }} tpat USING (customer_id)
This model demonstrates how AI functions enable sophisticated customer segmentation based on behavioural patterns rather than just demographic data.
Business Impact: Cross-Departmental Value Creation
The Complete Customer Intelligence Experience
Our Streamlit in Snowflake app provides a comprehensive view of customer intelligence across all business functions. Here's what the complete solution looks like in action:
Figure 3: Complete Customer Intelligence Hub demonstration built with Streamlit in Snowflake
Marketing: Precision Targeting and Campaign Optimisation
Marketing teams gain unprecedented insight into customer sentiment patterns, enabling:
Sentiment-Driven Segmentation: Instead of broad demographic targeting, campaigns can focus on customers with specific sentiment profiles. A "win-back" campaign might target customers with declining sentiment scores, while advocacy programs focus on consistently positive customers.
Multilingual Campaign Intelligence: Global marketing teams can analyse campaign effectiveness across different languages and regions, identifying cultural preferences and regional sentiment patterns.
Content optimisation: Understanding which product features generate positive vs. negative sentiment helps inform content strategy and messaging priorities.
Sales: Proactive Relationship Management
Sales organisations transform from reactive to proactive with AI-powered customer intelligence:
Churn Prevention: The platform identifies at-risk customers before they express intent to leave, enabling proactive retention conversations. A customer with declining sentiment scores and increasing support tickets triggers automated alerts to account managers.
Upsell Opportunity Identification: Customers with high satisfaction scores and positive sentiment trends represent prime upsell opportunities. The system automatically surfaces these accounts for expansion conversations.
Conversation Intelligence: Sales teams gain context about customer sentiment before every interaction, enabling more effective and empathetic conversations.
Support: Intelligent Ticket Routing and Escalation
Customer support becomes more efficient and effective:
Sentiment-Aware Routing: High-priority tickets from customers with negative sentiment histories are automatically routed to senior agents, while positive customers receive standard routing.
Proactive Outreach: The system identifies customers with deteriorating sentiment patterns, enabling proactive support outreach before issues escalate to formal tickets.
Agent Performance Insights: Understanding how different agents impact customer sentiment helps optimise training and performance management.
Finance: Revenue Risk and Opportunity Forecasting
Finance teams gain predictive insights into revenue patterns:
Churn-Based Revenue Forecasting: Traditional forecasting models are enhanced with sentiment-based churn probability, providing more accurate revenue projections.
Customer Lifetime Value optimisation: Understanding the relationship between sentiment and retention enables more sophisticated CLV calculations and investment decisions.
Cost-Benefit Analysis: The platform quantifies the ROI of customer experience investments by tracking sentiment improvements and their impact on retention and expansion.
Implementation Guide: From Zero to Customer Intelligence
📚 **Complete Implementation Repository**: All code, configurations, and step-by-step instructions are available in the dbt-cortex-analytics GitHub repo.
Phase 1: Foundation Setup
Snowflake Environment Configuration
Setup your Snowflake environment with the necessary databases, schemas and permissions:
Figure 4: Snowflake worksheet showing the SQL commands for setting up the customer intelligence database, schemas, and warehouses.
-- Core infrastructure setup
CREATE OR REPLACE DATABASE DBT_CORTEX_LLMS;
CREATE SCHEMA IF NOT EXISTS STAGE;
CREATE SCHEMA IF NOT EXISTS ANALYTICS;
CREATE SCHEMA IF NOT EXISTS SEMANTIC_MODELS;
-- Optimized warehouse for AI workloads
CREATE OR REPLACE WAREHOUSE CORTEX_WH
WITH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;
dbt Cloud Integration via Partner Connect
Leverage Snowflake's Partner Connect to automatically configure dbt Cloud with proper permissions and connections. This eliminates manual setup complexity and ensures security best practices:
Figure 5: Snowflake Partner Connect interface showing the seamless integration with dbt Cloud
Data Loading Infrastructure Establish file formats and stages for customer data ingestion:
-- JSON processing configuration
CREATE OR REPLACE FILE FORMAT JSON_FORMAT
TYPE = 'JSON'
STRIP_OUTER_ARRAY = TRUE
COMPRESSION = 'AUTO';
-- Staging area for raw data
CREATE OR REPLACE STAGE RAW_DATA_STAGE;
Phase 2: Core Data Pipeline
dbt Cloud Project Configuration
Once connected through Partner Connect, configure your dbt Cloud project with the appropriate settings and dependencies:
Figure 6: dbt Cloud project configuration interface
Staging Layer Implementation Build dbt models that clean and structure raw customer data. The staging layer focuses on data quality and consistency without business logic.
Fact Layer with AI Enhancement Implement the AI-powered fact models that apply Cortex functions to customer interactions, reviews, and support tickets. This is where unstructured text becomes quantified insights.
Analytics Layer Development Create business logic models that combine AI-enhanced facts into customer personas, churn risk scores, and trend analysis.
Phase 3: Business Intelligence Layer
dbt Cloud Studio IDE Development
The dbt Cloud Studio IDE provides a comprehensive web-based development environment that streamlines the entire analytics engineering workflow. This integrated development environment combines code editing, version control, testing, and documentation in a single interface optimized for dbt development.
Key Studio IDE Features Include:
AI-Powered Code Generation: dbt Copilot generates SQL code from natural language prompts and automatically creates documentation, tests, and semantic models with a single click
Real-Time Compilation: Preview and compile your dbt models instantly to validate Cortex AI function syntax before execution
Integrated Version Control: Commit changes, manage branches, and resolve merge conflicts directly within the IDE
Interactive DAG Visualization: Explore model dependencies and data lineage in real-time as you build your customer intelligence pipeline
Figure 8: dbt Cloud IDE showing the complete data flow from source systems through staging, fact, and analytics layers.
Streamlit in Snowflake App Deploy an interactive app with six analytical views:
Overview: Executive KPIs and sentiment trends
Segmentation: Customer persona analysis and value segments
Sentiment Experience: Cross-channel sentiment tracking
Product Feedback: Multilingual review analysis
Support Operations: Ticket analytics and resolution metrics
Cortex Analyst: Natural language querying interface
Cortex Analyst Semantic Model
Configure the semantic model for natural language querying:
Figure 9: Snowflake semantic model yaml, enabling natural language querying through Cortex Analyst.
Advanced Capabilities: Extending the Platform
Natural Language Querying with Cortex Analyst and Intelligent Visualisation
One of the most powerful features of the Streamlit App is the combination of natural language querying through Cortex Analyst with our Visualisation Assistant – an AI-powered system that automatically generates the most appropriate charts based on your data characteristics and analytical intent.
The Complete Query-to-Insight Workflow
When you ask a question like "Show me customers with declining sentiment in the last quarter", the app orchestrates a sophisticated multi-step process:
Natural Language Processing: Cortex Analyst interprets your question and generates optimised SQL
Data Analysis: The system analyses the returned dataset's structure, patterns, and statistical properties
Intent Recognition: Our AI engine determines your analytical goal (comparison, distribution, trend analysis, etc.)
Intelligent Chart Selection: The Visualisation Assistant recommends the most effective chart type
Automated Rendering: The optimal visualisation is generated with accessibility features and interactive elements
The Visualisation Assistant: AI-Powered Chart Intelligence
The Visualisation Assistant in our Streamlit in Snowflake App implements automated data visualisation, combining established data visualisation principles with machine learning to deliver contextually appropriate charts.
Intent-Aware Chart Selection
The Visualisation Assistant uses natural language processing to understand your analytical intent and match it with the most effective visualisation:
Distribution Queries ("show distribution of sentiment scores") → Histogram with statistical overlays
Comparison Queries ("compare average sentiment by persona") → Bar charts with confidence intervals
Trend Analysis ("sentiment trends over time") → Line charts with trend lines and forecasting
Relationship Exploration ("correlation between sentiment and churn risk") → Scatter plots with correlation coefficients
Composition Analysis ("breakdown of customers by risk level") → Pie charts for small categories, bar charts for larger sets
Real-World Example: Customer Sentiment Analysis
Here's how the complete system works with typical questions:
Example 1: Comparative Analysis
User Query: "Compare average sentiment scores across different interaction types"
System Response:
Cortex Analyst generates SQL that aggregates sentiment scores by interaction channel (email, chat, phone, social)
Data Analysis identifies categorical comparison with 13 interaction types and continuous sentiment values
Intent Recognition detects: Primary intent = "categorical comparison"
Chart Recommendation: Bar chart with confidence intervals
Figure 10. The Visualisation Assistant automatically selects a bar chart for categorical comparison.
Example 2: Temporal Trend Analysis
User Query: "Show customer interaction trends over time by day"
System Response:
Cortex Analyst generates SQL aggregating daily interaction volumes with temporal functions
Data Analysis identifies time series data with 180 daily observations
Intent Recognition detects: Primary intent = "trend analysis"
Chart Recommendation: Line chart with trend overlay
Figure 11. The Visualisation Assistant automatically generates a line chart for temporal analysis, complete with trend line, confidence bands, and intelligent axis formatting.
Real-Time vs. Batch Processing Considerations
The architecture supports both real-time and batch processing patterns:
Batch Processing (Recommended for most use cases):
Daily dbt runs process new customer interactions
Cost-effective for most analytical workloads
Sufficient for strategic decision-making
Near Real-Time Processing (For operational use cases):
Snowpipe / Snowpipe Streaming data ingestion for support tickets, customer interactions, product feedback etc.
Materialize your dbt models using Dynamic Tables
Real-time AI-powered sentiment scoring for live chat interactions
Instant multilingual translation and sentiment analysis for global support teams
Immediate churn risk alerts based on interaction patterns and sentiment trends
Dynamic customer persona classification as behaviour patterns evolve for real-time customer activation
Proactive upsell opportunity identification through conversation analysis
models:
<resource-path>:
+materialized: dynamic_table
+on_configuration_change: apply | continue | fail
+target_lag: downstream | <time-delta>
+snowflake_warehouse: <warehouse-name>
+refresh_mode: AUTO | FULL | INCREMENTAL
+initialize: ON_CREATE | ON_SCHEDULE
dbt_project.yaml example: dbt Dynamic Table configuration for real-time customer intelligence processing.
Getting Started: Your Next Steps
Getting Started
Explore the Quickstart: Access the (unofficial) Customer Intelligence Hub Quickstart to get hands on with the solution
Watch the Webinar: Review our Snowflake-dbt Labs webinar to see a live demo and discussion of real-world examples
Implement with Your Data: Connect your existing customer interaction sources (CRM, support systems, review platforms) to the solution framework
Conclusion: The Future of Customer Intelligence
The convergence of AI-powered data processing and modern analytics platforms represents a fundamental shift in how organisations understand and respond to customer needs. By combining Snowflake Cortex AISQL LLM functions with dbt's transformation capabilities and Streamlit's interactive dashboards, we've created a platform that transforms customer conversations into competitive advantages.
The architecture we've explored isn't just about technology, it's about enabling organisations to become more responsive, more empathetic, and more successful in their customer relationships. When marketing teams can identify sentiment patterns, sales teams can proactively address concerns, support teams can route tickets intelligently, and finance teams can forecast with sentiment-enhanced models, the entire organisation becomes more customer-centric.
View this platform not as a reporting tool, but as a foundation for cultural transformation. Organisations that embrace AI-powered customer intelligence will find themselves better positioned to thrive in an increasingly competitive marketplace.
The tools and techniques are available today. The question isn't whether AI-powered customer intelligence will transform your industry, it's whether your organisation will lead or follow that transformation.
Ready to build your own Customer Intelligence Hub?
💬 Join the Discussion: Connect with me on LinkedIn or follow Cloudy Data