Easily showcase your store’s sales with dynamic charts using Snowflake in just minutes
How to transform JSON into a convenient view, create a wide view convenient for the presentation, and present the results on a dashboard using Snowflake’s Streamlit.
Whether you operate a small, medium, or enterprise business, chances are you have substantial data stored without a user-friendly interface for viewing. If you’ve migrated or plan to move your data to Snowflake for analytics, here’s a fast tutorial on leveraging Snowflake’s functionality to execute analytics queries and present results through Streamlit’s charts.
For the sake of simplicity in this demonstration, I’ll be creating the raw data tables and it’s data directly in Snowflake. In a real-world scenario, this data would typically be ingested into Snowflake from various sources using tools such as Snowpipe, Airbyte, Fivetran, or other ingestion platforms.
Let’s create a demo database that represents a store database RAW data.
It will include our Products table, Sales Persons table, and a table for sales with a json row for each transaction.
-- Create the store database
CREATE DATABASE IF NOT EXISTS store;
-- Use the store database
USE DATABASE store;
-- Create table for product descriptions and categories
CREATE TABLE products (
product_id VARCHAR(50),
description VARCHAR(255),
category VARCHAR(50)
);
-- Insert some sample product data into the products table
INSERT INTO products (product_id, description, category)
VALUES
('PROD1', 'Laundry Detergent', 'Household'),
('PROD2', 'Dish Soap', 'Household'),
('PROD3', 'Paper Towels', 'Household'),
('PROD4', 'Toilet Paper', 'Household'),
('PROD5', 'Hand Soap', 'Household'),
('PROD6', 'Cleaning Spray', 'Household'),
('PROD7', 'Trash Bags', 'Household'),
('PROD8', 'Sponges', 'Household'),
('PROD9', 'All-Purpose Cleaner', 'Household'),
('PROD10', 'Bleach', 'Household'),
('PROD11', 'Glass Cleaner', 'Household'),
('PROD12', 'Fabric Softener', 'Household'),
('PROD13', 'Toilet Bowl Cleaner', 'Household'),
('PROD14', 'Air Freshener', 'Household'),
('PROD15', 'Dishwasher Detergent', 'Household'),
('PROD16', 'Floor Cleaner', 'Household'),
('PROD17', 'Oven Cleaner', 'Household'),
('PROD18', 'Drain Cleaner', 'Household'),
('PROD19', 'Surface Wipes', 'Household'),
('PROD20', 'Baking Soda', 'Household'),
('PROD21', 'Vinegar', 'Household'),
('PROD22', 'Hand Sanitizer', 'Household'),
('PROD23', 'Aluminum Foil', 'Household'),
('PROD24', 'Plastic Wrap', 'Household'),
('PROD25', 'Ziploc Bags', 'Household'),
('PROD51', 'Yoga Mat', 'Sports'),
('PROD52', 'Dumbbells', 'Sports'),
('PROD53', 'Jump Rope', 'Sports'),
('PROD54', 'Resistance Bands', 'Sports'),
('PROD55', 'Exercise Ball', 'Sports'),
('PROD56', 'Treadmill', 'Sports'),
('PROD57', 'Stationary Bike', 'Sports'),
('PROD58', 'Basketball', 'Sports'),
('PROD59', 'Soccer Ball', 'Sports'),
('PROD60', 'Football', 'Sports'),
('PROD61', 'Tennis Racket', 'Sports'),
('PROD62', 'Golf Clubs Set', 'Sports'),
('PROD63', 'Boxing Gloves', 'Sports'),
('PROD64', 'Swimming Goggles', 'Sports'),
('PROD65', 'Hiking Boots', 'Sports'),
('PROD66', 'Cycling Helmet', 'Sports'),
('PROD67', 'Skateboard', 'Sports'),
('PROD68', 'Surfboard', 'Sports'),
('PROD69', 'Snowboard', 'Sports'),
('PROD71', 'Milk', 'Grocery'),
('PROD72', 'Bread', 'Grocery'),
('PROD73', 'Eggs', 'Grocery'),
('PROD74', 'Butter', 'Grocery'),
('PROD75', 'Cheese', 'Grocery'),
('PROD76', 'Yogurt', 'Grocery'),
('PROD77', 'Fresh Vegetables', 'Grocery'),
('PROD78', 'Fresh Fruits', 'Grocery'),
('PROD79', 'Pasta', 'Grocery'),
('PROD80', 'Rice', 'Grocery'),
('PROD81', 'Cereal', 'Grocery'),
('PROD82', 'Granola Bars', 'Grocery'),
('PROD83', 'Canned Beans', 'Grocery'),
('PROD84', 'Canned Soup', 'Grocery'),
('PROD85', 'Peanut Butter', 'Grocery'),
('PROD86', 'Jam', 'Grocery'),
('PROD87', 'Olive Oil', 'Grocery'),
('PROD88', 'Salt', 'Grocery'),
('PROD89', 'Sugar', 'Grocery'),
('PROD91', 'Laptop', 'Student'),
('PROD92', 'Notebook', 'Student'),
('PROD93', 'Pens', 'Student'),
('PROD94', 'Backpack', 'Student'),
('PROD95', 'Textbooks', 'Student'),
('PROD96', 'Calculator', 'Student'),
('PROD97', 'USB Flash Drive', 'Student'),
('PROD98', 'Headphones', 'Student'),
('PROD99', 'Water Bottle', 'Student'),
('PROD100', 'Snacks', 'Student'),
('PROD101', 'Lunch Box', 'Student'),
('PROD102', 'Coffee Mug', 'Student'),
('PROD103', 'Desk Lamp', 'Student'),
('PROD104', 'Desk Organizer', 'Student'),
('PROD105', 'Planner', 'Student'),
('PROD106', 'Highlighters', 'Student'),
('PROD107', 'Sticky Notes', 'Student'),
('PROD108', 'Folders', 'Student'),
('PROD109', 'Laundry Hamper', 'Student'),
('PROD111', 'Desktop Computer', 'Computers'),
('PROD112', 'Laptop Computer', 'Computers'),
('PROD113', 'Computer Monitor', 'Computers'),
('PROD114', 'Keyboard', 'Computers'),
('PROD115', 'Mouse', 'Computers'),
('PROD116', 'Printer', 'Computers'),
('PROD117', 'External Hard Drive', 'Computers'),
('PROD118', 'USB Hub', 'Computers'),
('PROD119', 'Wireless Router', 'Computers'),
('PROD120', 'Ethernet Cable', 'Computers'),
('PROD121', 'Webcam', 'Computers'),
('PROD122', 'Microphone', 'Computers'),
('PROD123', 'Speakers', 'Computers'),
('PROD124', 'Graphics Card', 'Computers'),
('PROD125', 'RAM (Memory)', 'Computers'),
('PROD126', 'SSD (Solid State Drive)', 'Computers'),
('PROD127', 'Motherboard', 'Computers'),
('PROD128', 'CPU (Processor)', 'Computers'),
('PROD129', 'Computer Case', 'Computers'),
('PROD130', 'Cooling Fan', 'Computers');
-- Create table for salespersons
CREATE TABLE salespersons (
salesperson_id INT,
name VARCHAR(100),
email VARCHAR(100)
);
-- Insert some sample salesperson data into the salespersons table
INSERT INTO salespersons (salesperson_id, name, email)
VALUES
('James Smith', 'james@example.com'),
('Mary Johnson', 'mary@example.com'),
('John Williams', 'john@example.com'),
('Patricia Jones', 'patricia@example.com'),
('Robert Brown', 'robert@example.com'),
('Jennifer Davis', 'jennifer@example.com'),
('Michael Miller', 'michael@example.com'),
('Linda Wilson', 'linda@example.com'),
('William Moore', 'william@example.com'),
('Elizabeth Taylor', 'elizabeth@example.com'),
('David Anderson', 'david@example.com'),
('Barbara Thomas', 'barbara@example.com'),
('Joseph Jackson', 'joseph@example.com'),
('Jessica White', 'jessica@example.com'),
('Richard Harris', 'richard@example.com'),
('Sarah Martin', 'sarah@example.com'),
('Charles Thompson', 'charles@example.com'),
('Karen Garcia', 'karen@example.com'),
('Thomas Martinez', 'thomas@example.com'),
('Kimberly Robinson', 'kimberly@example.com');
-- Create table for sales with a VARIANT column for JSON sales examples
CREATE TABLE sales (
sale_data VARIANT
);
-- Insert some JSON sales examples into the sales table
INSERT INTO sales (sale_data)
SELECT PARSE_JSON(column1)
FROM (
VALUES
('{"sale_id": 1, "product_id": "PROD1", "quantity": 2, "price": 50.00, "salesperson_id": 1 }'),
('{"sale_id": 2, "product_id": "PROD2", "quantity": 1, "price": 30.00, "salesperson_id": 2 }'),
('{"sale_id": 3, "product_id": "PROD3", "quantity": 3, "price": 20.00, "salesperson_id": 3}'),
('{"sale_id": 4, "product_id": "PROD4", "quantity": 4, "price": 15.00, "salesperson_id": 4}'),
('{"sale_id": 5, "product_id": "PROD5", "quantity": 1, "price": 25.00, "salesperson_id": 5}'),
('{"sale_id": 6, "product_id": "PROD6", "quantity": 2, "price": 35.00, "salesperson_id": 6}'),
('{"sale_id": 7, "product_id": "PROD7", "quantity": 1, "price": 40.00, "salesperson_id": 3}'),
('{"sale_id": 8, "product_id": "PROD8", "quantity": 3, "price": 10.00, "salesperson_id": 3}'),
('{"sale_id": 9, "product_id": "PROD9", "quantity": 2, "price": 20.00, "salesperson_id": 3}'),
('{"sale_id": 10, "product_id": "PROD10", "quantity": 1, "price": 15.00, "salesperson_id": 6}'),
('{"sale_id": 11, "product_id": "PROD11", "quantity": 2, "price": 30.00, "salesperson_id": 1}'),
('{"sale_id": 12, "product_id": "PROD12", "quantity": 1, "price": 35.00, "salesperson_id": 2}'),
('{"sale_id": 13, "product_id": "PROD13", "quantity": 3, "price": 25.00, "salesperson_id": 3}'),
('{"sale_id": 14, "product_id": "PROD14", "quantity": 2, "price": 20.00, "salesperson_id": 4}'),
('{"sale_id": 15, "product_id": "PROD10", "quantity": 1, "price": 45.00, "salesperson_id": 5}'),
('{"sale_id": 16, "product_id": "PROD16", "quantity": 4, "price": 30.00, "salesperson_id": 6}'),
('{"sale_id": 17, "product_id": "PROD17", "quantity": 2, "price": 20.00, "salesperson_id": 1}'),
('{"sale_id": 18, "product_id": "PROD8", "quantity": 1, "price": 15.00, "salesperson_id": 1}'),
('{"sale_id": 19, "product_id": "PROD9", "quantity": 3, "price": 25.00, "salesperson_id": 9}'),
('{"sale_id": 20, "product_id": "PROD10", "quantity": 2, "price": 35.00, "salesperson_id": 10}'),
('{"sale_id": 21, "product_id": "PROD2", "quantity": 1, "price": 10.00, "salesperson_id": 2}'),
('{"sale_id": 22, "product_id": "PROD2", "quantity": 2, "price": 20.00, "salesperson_id": 2}'),
('{"sale_id": 23, "product_id": "PROD2", "quantity": 3, "price": 30.00, "salesperson_id": 2}'),
('{"sale_id": 24, "product_id": "PROD2", "quantity": 4, "price": 40.00, "salesperson_id": 4}'));We can see that the store had a few sales, a few products, and a few salespersons.
If we would like to see which products Mary Johnson sold, we will need to have a query like this:
SELECT p.product_id, p.description, p.category
FROM sales s
JOIN salespersons sp ON s.sale_data:salesperson_id = sp.salesperson_id
JOIN products p ON s.sale_data:product_id = p.product_id
WHERE sp.name = 'Mary Johnson';
Now, let’s create a wide view with all sales information. A wide view streamlines analysts’ work by eliminating the need to join multiple tables to access additional information.
CREATE OR REPLACE VIEW wide_sales_view AS
SELECT
s.sale_data:sale_id::INT as sale_id, s.sale_data:product_id::STRING as product_id,
p.description, p.category, s.sale_data:quantity as quantity, s.sale_data:price as price, sp.salesperson_id,
sp.name, sp.email
FROM sales s
JOIN salespersons sp ON s.sale_data:salesperson_id = sp.salesperson_id
JOIN products p ON s.sale_data:product_id = p.product_id;And check the results of the wide view.
Now, let’s create some visualization using Snowflake’s Streamlit App.
Our Streamlit code will be straightforward for this example
# Import python packages
import streamlit as st
from snowflake.snowpark.context import get_active_session
# Write directly to the app
st.title("Our Sales :balloon:")
st.write(
"""Example of showing data using Streamlit
"""
)
# Get the current credentials
session = get_active_session()
df = session.sql("SELECT * from wide_sales_view").collect()
st.table(df)Click the run button to view the results
Now, let’s add some graphs.
# Create a simple bar chart
df = session.sql("SELECT name as SALESPERSON, SUM(price) AS SALES from wide_sales_view GROUP BY SALESPERSON_ID, name").collect()
st.subheader("Sales per presale person")
st.bar_chart(data=df, x="SALESPERSON", y="SALES")
Getting inputs from the user is easy.
userInput = st.text_input('Enter a SALESPERSON ID', 1)
try:
number = int(userInput)
except ValueError:
st.write ("That's not an int!")
number = 0
query = f"SELECT DESCRIPTION AS PRODUCT, CAST(SUM(QUANTITY) AS INT) AS TOTAL_QUANTITY from wide_sales_view WHERE SALESPERSON_ID=? GROUP BY PRODUCT_ID, PRODUCT"df = session.sql(query, params=[number]).collect()
if len(df) != 0:
st.subheader(f"Sales for id {number}")
st.bar_chart(data=df, x="PRODUCT", y="QUANTITY")
else:
st.write("No sales for this person id")Now, you would have an input box in which you can go and change the id and see the chart showing this person's sales. Updated immediately. Or write non-existing id / invalid input and get an error message.
You can find many more UI elements in Streamlit’s documentation.
Now that your dashboard is ready, you can share it with your colleagues. Click the ‘Share button’ and choose the role and the appropriate permissions.
Adding some colors
Now that we’re aware the CEO prefers visually appealing charts, let’s enhance our dashboard with some polished graphics.
Our new query would be:
query = f"""
SELECT
ROW_NUMBER() OVER (ORDER BY NAME) AS DF_IDX,
NAME AS SALESPERSON,
DESCRIPTION AS PRODUCT,
COUNT(*) AS QUANTITY
FROM
wide_sales_view
GROUP BY
NAME, DESCRIPTION
ORDER BY
DF_IDX
"""
df = session.sql(query).collect();
# st.table(df) # for testingLet’s try to follow streamlit documentation
First: Adding the imports for altair. It will assist us in utilizing the data’s columns and indices to determine the chart’s specification
import altair as alt
import pandas as pdpandas_df = pd.DataFrame(df,columns=
["DF_IDX","SALESPERSON","PRODUCT","QUANTITY"]).set_index("DF_IDX")
#CREATE THE ALTAIR CHART DATA
chart = alt.Chart(pandas_df).mark_bar().encode(
x = alt.X("SALESPERSON",title="SALESPERSON",type="nominal")
,y=alt.Y("QUANTITY",title="QUANTITY")
,color= alt.Color("PRODUCT",title="PRODUCT")
)
st.altair_chart(chart,use_container_width=True,theme="streamlit")And Walla, we can now easily see product names and quantities sold by each salesperson.
Summary
In this blog post, I’ve shown how easy it is to start and visualize your data. I walked through transforming JSON into a convenient to-query View, Creating a wide table that will be easy to query for the presentation layer, and using Streamlit to show the results in a nice dashboard within Snowflake and share it with colleagues.
To stay updated on more Snowflake-related posts, follow me at my Medium profile: Eylon’s Snowflake Articles.
I’m Eylon Steiner, Engineering Manager for Infostrux Solutions. You can follow me on LinkedIn.
Subscribe to Infostrux Medium Blog at https://blog.infostrux.com for the most interesting Data Engineering and Snowflake news. Follow Infostrux’s open-source efforts through GitHub.
