Overview

What You’ll Learn

What You’ll Build

Prerequisites

Snowflake Setup

Snowflake User Interface

Download Scripts and Create Worksheet from SQL

Manage Semi-Structured Data

Run the scripts in a Snowsight or VSCode

Create Snowflake Database and Warehouse

Grant Privileges on Snowflake Objects

Create Snowflake Stages and Native Tables

Load data into Raw Tables

DataLake Integration

Create the necessary AWS Configuration

Download the Customer Reviews files to your laptop

Upload the folder from your laptop to the S3 bucket.

Take a note of your AWS Account ID.

USE DATABASE frostbyte_tasty_bytes;
USE SCHEMA raw_customer;

CREATE or REPLACE STORAGE INTEGRATION frostbyte_tasty_bytes.raw_customer.int_tastybytes_truckreviews
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<your AWS account ID>:role/<give a new name for IAM role>' -- ex: snow_s3_access_role
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('s3://<name of your S3 bucket>');



CREATE OR REPLACE FILE FORMAT frostbyte_tasty_bytes.raw_customer.ff_csv
    TYPE = 'csv'
    SKIP_HEADER = 1   
    FIELD_DELIMITER = '|';

CREATE OR REPLACE STAGE frostbyte_tasty_bytes.raw_customer.stg_truck_reviews
    STORAGE_INTEGRATION = int_tastybytes_truckreviews
    URL = 's3://<name of your S3 bucket>/'
    FILE_FORMAT = ff_csv;

-- You will need the output of these values in AWS CloudFormation in Next Steps
DESC INTEGRATION frostbyte_tasty_bytes.raw_customer.int_tastybytes_truckreviews; 

You need following values from Storage Integration for next steps

Launch the AWS CloudFormation

Copy the values below

Validate AWS access by running below SQL in Snowflake

 --- Test if your AWS Storage is Accessible 
SELECT   SYSTEM$VALIDATE_STORAGE_INTEGRATION('<integration_name>',    's3://<bucket>/',    'validate_all.txt', 'all'); 

Output should show success for READ and LIST

Create Snowflake managed Iceberg Tables to access Datalake and query reviews data

USE ROLE ACCOUNTADMIN;
USE DATABASE  frostbyte_tasty_bytes;
USE SCHEMA raw_customer;
--- Test if your AWS Storage is Accessible 
SELECT   SYSTEM$VALIDATE_STORAGE_INTEGRATION('<integration_name>',    's3://<bucket>/',    'validate_all.txt', 'all'); 


CREATE OR REPLACE EXTERNAL VOLUME vol_tastybytes_truckreviews
    STORAGE_LOCATIONS =
        (
            (
                NAME = 'reviews-s3-volume'
                STORAGE_PROVIDER = 'S3'
                STORAGE_BASE_URL = 's3://<bucket_name>' --s3://jnanreviews 
                STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<aws-account-id>:role/<snow_role>' --ex:snow_s3_access_role 
                STORAGE_AWS_EXTERNAL_ID = 'external_id' -- enter your external id 
            )
            
        )ALLOW_WRITES=true; 

-- Create Iceberg Tables to track metadata 
CREATE OR REPLACE ICEBERG TABLE iceberg_truck_reviews
        (
        source_name VARCHAR,
        quarter varchar,
        order_id BIGINT,
        truck_id INT,
        language VARCHAR, 
        review VARCHAR,
        primary_city VARCHAR,
        customer_id varchar,
        year date,
        month date,
        truck_brand VARCHAR,
        review_date date
        )
        CATALOG = 'SNOWFLAKE'
        EXTERNAL_VOLUME = 'vol_tastybytes_truckreviews'
        BASE_LOCATION = 'reviews-s3-volume'; 


-- Insert  Metadata from External Files 
INSERT INTO iceberg_truck_reviews
(
        source_name,
        quarter,
        order_id,
        truck_id,
        language, 
        review,
        primary_city ,
        customer_id ,
        year ,
        month ,
        truck_brand ,
        review_date 
)
SELECT 
       SPLIT_PART(METADATA$FILENAME, '/', 4) as source_name,
       CONCAT(SPLIT_PART(METADATA$FILENAME, '/', 2),'/' ,SPLIT_PART(METADATA$FILENAME, '/', 3)) as quarter,
       $1 as order_id,
       $2 as truck_id,
       $3 as language,
       $5 as review,
       $6 as primary_city, 
       $7 as customer_id,
       $8 as year,
       $9 as month,
       $10 as truck_brand,
       DATEADD(day,-UNIFORM(0,180,RANDOM()),CURRENT_DATE()) as review_date
FROM @stg_truck_reviews 
(FILE_FORMAT => 'FF_CSV',
PATTERN => '.*reviews.*[.]csv') 
;


-- Create a view on the Iceberg Reviews, and run Cortex AI to extract Sentiment
USE SCHEMA analytics;

-- We have non-english reviews from global customers
SELECT order_id, quarter, truck_id, language, source_name, primary_city, truck_brand , review, review_date from frostbyte_tasty_bytes.raw_customer.iceberg_truck_reviews  where language !='en' order by review_date desc;

-- Snowflake Cortex makes it easy for us to translate and extract sentiment out of unstructured data
CREATE OR REPLACE VIEW  frostbyte_tasty_bytes.analytics.product_unified_reviews as             
    SELECT order_id, quarter, truck_id, language, source_name, primary_city, truck_brand , snowflake.cortex.sentiment(review) , review_date  from frostbyte_tasty_bytes.raw_customer.iceberg_truck_reviews  where language='en'
    UNION    
    SELECT order_id, quarter, truck_id, language, source_name, primary_city, truck_brand , snowflake.cortex.sentiment(snowflake.cortex.translate(review,language,'en')), review_date  from frostbyte_tasty_bytes.raw_customer.iceberg_truck_reviews where language !='en';


select * from frostbyte_tasty_bytes.analytics.product_unified_reviews limit 100;

-- Sentiment Grouped By City and Brand 

CREATE OR REPLACE VIEW  frostbyte_tasty_bytes.analytics.product_sentiment AS 
SELECT primary_city, truck_brand, avg(snowflake.cortex.sentiment(review_date)) as avg_review_sentiment 
FROM frostbyte_tasty_bytes.analytics.product_unified_reviews
group by primary_city, truck_brand;


-- Query the average the query sentiment by city and brand 
select * from frostbyte_tasty_bytes.analytics.product_sentiment limit 10;

Connect Tableau to Snowflake

Visualize Data

Click on any suggestion. It may take time for Tableau Agent to index your data. See how it will build the visualization for you, and also give you feedback in the agent chat itself.

Publish

Next, navigate to Tableau Pulse. Click ‘New Metric Definition’.

Clean Up

USE ROLE accountadmin;

DROP FUNCTION IF EXISTS frostbyte_tasty_bytes.analytics.fahrenheit_to_celsius(NUMBER(35,4));
DROP FUNCTION IF EXISTS frostbyte_tasty_bytes.analytics.inch_to_millimeter(NUMBER(35,4));

--USE ROLE sysdmin;
DROP DATABASE IF EXISTS frostbyte_tasty_bytes;   
DROP DATABASE IF EXISTS frostbyte_weathersource;
DROP WAREHOUSE IF EXISTS demo_build_wh; 
DROP WAREHOUSE IF EXISTS tasty_de_wh;
DROP WAREHOUSE IF EXISTS tasty_bi_wh;

Conclusion and Resources

What You Learned

Resources