SanjayLakhanpal

In the era of data-driven decision-making, understanding customer sentiment is crucial for businesses aiming to enhance products and services. Snowflake Cortex, with its powerful AI and machine learning capabilities, enables organizations to analyze text data like customer reviews directly within the Snowflake Data Cloud. When paired with Coalesce.io’s low-code data transformation platform, this process becomes accessible to data teams of all skill levels, streamlining the creation of AI-powered pipelines. In this blog, we explore a practice use case for training: performing sentiment analysis on customer reviews using Snowflake Cortex and Coalesce.io, leveraging a dataset of 1,020 reviews.

Use Case Overview

This practice use case simulates an e-commerce company analyzing customer feedback for five products: Wireless Headphones, Smartphone Case, Laptop Stand, Bluetooth Speaker, and USB Charger. The goal is to determine the sentiment (positive, negative, or neutral) of each review using Snowflake Cortex’s SENTIMENT function and build a scalable pipeline in Coalesce.io to automate the process. With a dataset of 1,020 reviews, this exercise is ideal for data analysts, engineers, or business intelligence professionals looking to practice AI-driven analytics.

Objectives

• Use Snowflake Cortex to analyze the sentiment of customer reviews.
• Build a low-code data pipeline in Coalesce.io to process and store results.
• Generate actionable insights, such as identifying products with negative feedback.
• Provide a hands-on training scenario for data teams.

Why Use Snowflake Cortex and Coalesce.io?

Snowflake Cortex offers fully managed AI functions, including SENTIMENT, which scores text from -1 (negative) to 1 (positive). By keeping data and computation within Snowflake, Cortex ensures security, governance, and performance without external dependencies.

Coalesce.io simplifies pipeline development with its drag-and-drop interface and pre-built nodes. The Cortex package in the Coalesce Marketplace provides ready-to-use nodes for functions like SENTIMENT, enabling rapid deployment without complex SQL coding.

Together, they empower teams to:

• Build AI-driven pipelines quickly.
• Democratize access to advanced analytics for non-technical users.
• Scale workflows seamlessly with Snowflake’s compute power.

Setting Up the Environment

Prerequisites

• A Snowflake account with Cortex functions enabled.
• A Coalesce.io account connected to your Snowflake environment.
• Permissions to create tables, load data, and use Cortex functions.
• A dataset of 1,020 customer reviews (CUSTOMER_REVIEWS).

Dataset

The dataset contains 1,020 reviews across five products, stored in the TRAINING_DB.CUSTOMER_ANALYTICS.CUSTOMER_REVIEWS table with columns:

• REVIEW_ID (1 to 1,020)
• PRODUCT_NAME (e.g., Wireless Headphones)
• REVIEW_TEXT (e.g., “Amazing sound quality!”)
• REVIEW_DATE (April 1–30, 2025)

The reviews include a balanced mix of sentiments (~40% positive, 30% negative, 30% neutral).

Step-by-Step Guide: Building the Pipeline in Coalesce.io

Step 1: Prepare the Data

• Create the Table in Snowflake (if not already done):

CREATE DATABASE IF NOT EXISTS TRAINING_DB;
CREATE SCHEMA IF NOT EXISTS CUSTOMER_ANALYTICS;
CREATE TABLE TRAINING_DB.CUSTOMER_ANALYTICS.CUSTOMER_REVIEWS (
    REVIEW_ID INT,
    PRODUCT_NAME STRING,
    REVIEW_TEXT STRING,
    REVIEW_DATE DATE
); 

• Load the Dataset:

1. Use the SQL INSERT statements generated by the Python script (see original use case) to populate the table with 1,020 records.
2. Alternatively, upload a CSV file to a Snowflake stage and load it using COPY INTO.

Step 2: Set Up Coalesce.io

• Connect to Snowflake: In Coalesce.io, configure your Snowflake connection with the appropriate credentials.

• Install the Cortex Package: Navigate to the Coalesce Marketplace. Install the “Snowflake Cortex” package, which adds nodes for functions like SENTIMENT.

• Open a Workspace: Click the Launch button to open the development environment.

• Add a Source Node: Add a Source Node into the pipeline canvas. Connect it to the CUSTOMER_REVIEWS table (TRAINING_DB.CUSTOMER_ANALYTICS_STAGING.CUSTOMER_REVIEWS).

• Add the Cortex Function Node: Right-click on the source node, then select Add Node. From the Cortex menu, choose the Cortex Function node..

• Edit Cortext Fucntion Node: Double-click the Cortex Function node, rename it to "CUSTOMER_REVIEWS", and choose the desired target storage location.

• Appy Sentiment Analysis: In the Transformation pane, add a new attribute named SENTIMENT_SCORE and set its data type to Number(5,2). Then, from the Cortex Packages section on the right, choose Sentiment and select SENTIMENT_SCORE as the column name from the available options.

• Categorize Sentiments: Add another attribute named SENTIMENT_CATEGORY in the Transform Node to classify sentiments based on scores. Use the following CASE statement in the Transform pane:.

 CASE
          WHEN SENTIMENT_SCORE > 0.5 THEN 'Positive'
          WHEN SENTIMENT_SCORE < -0.5 THEN 'Negative' 
          ELSE 'Neutral' 
END

• Create a Target Node: Click the Create button to create the table in the specified target schema.

• Validate and Deploy: In Coalesce.io, click Validate Run to check the pipeline for errors. Once validation is complete, click the Run button to execute the pipeline. Coalesce will then run the node and populate the table with customer review sentiments, including the corresponding sentiment categories.

Step 4: Analyze the Results

1. View Sentiment Scores: Query the REVIEW_SENTIMENT table in Snowflake:

Benefits of Using Coalesce.io

• No Coding Required: The drag-and-drop interface eliminates the need for manual SQL, making the pipeline accessible to non-technical users.
• Rapid Development: Build and deploy the pipeline in under 30 minutes.
• Scalability: Handle large datasets with Snowflake’s compute power.

Learning Outcomes

By completing this use case, you’ll:

• Master the use of Snowflake Cortex’s SENTIMENT function for text analysis.
• Gain hands-on experience building low-code pipelines in Coalesce.io.
• Learn to interpret sentiment scores and translate them into business insights.
• Practice creating scalable workflows for real-world applications.

Best Practices

• Test Incrementally: Start with a subset of reviews (e.g., 100) to validate the pipeline before processing all 1,020 records.
• Monitor Costs: Cortex functions use Snowflake credits. Use Snowflake cost tracking to optimize compute usage.
• Version Control: Use Coalesce’s Git integration to manage pipeline versions.
• Data Quality: Ensure REVIEW_TEXT is non-null to avoid errors in Cortex functions.

Next Steps

• Expand the Dataset: Add more reviews or include additional products to test scalability.
• Automate with Schedules: Set up Coalesce jobs to run the pipeline daily for new reviews.
• Visualize Insights: Connect the REVIEW_SENTIMENT table to a BI tool like Tableau for interactive dashboards.

Conclusion

This practice use case demonstrates how Snowflake Cortex and Coalesce.io empower data teams to unlock customer insights through sentiment analysis. By leveraging Cortex’s AI capabilities and Coalesce’s low-code platform, you can build robust, scalable pipelines with minimal effort. Whether you’re a data engineer or a business analyst, this workflow offers a practical way to practice AI-driven analytics and drive business value.