Creating Insurance data Dashboards using Streamlit on Snowflake

Streamlit apps embedded in Snowflake can be used for various purposes, such as building interactive dashboards, deploying machine learning models, and creating data analysis tools. These apps can be easily shared with other team members, making it easier to collaborate on data projects. Streamlit apps embedded in Snowflake also provide a seamless user experience, allowing users to access and interact with apps directly from within the Snowflake platform.

Embedding Streamlit apps in Snowflake offers several benefits, including: simplified development and deployment, improved performance and scalability, enhanced security, and reduced costs.

Embedding Streamlit apps in Snowflake is a powerful way to build and share interactive data applications. It offers many benefits, including simplified development and deployment, improved performance and scalability, enhanced security, and reduced costs. Additionally, embedding Streamlit apps in Snowflake can lead to increased collaboration, improved user experience, and reduced development time.

Creating your data structure

For this example, we are creating and populating with the following tables: homeownership_rate, life_insurance, auto_insurance_premiums, and auto_insurance_premiums_2023.

homeownership_rate table:

CREATE OR REPLACE TABLE homeownership_rate ( 
year int, 
percent float 
);

life_insurance table:

CREATE OR REPLACE TABLE life_insurance (
 year int,
 own float,
 dont_own float
);

auto_insurance_premiums table:

CREATE OR REPLACE TABLE auto_insurance_premiums (MI float,RI float,NV float,FL float,NJ float,DE float,CT float,OG float,NY float,MD float,KY float,LA float,UT float,AZ float,MA float,IL float,SC float,CO float,HI float,MN float,MO float,NH float,WA float,GA float,CA float,VA float,OK float,AL float,WV float,TX float,NC float,MS float,NM float,AR float,KS float,OH float,AK float,NE float,IM float,PA float,ME float,TN float,MT float,WI float,ND float,IA float,ID float,VT float,WY float,SD float,year int);

auto_insurance_premiums_2023 table:

CREATE OR REPLACE TABLE auto_insurance_premiums_2023 (
 us_state VARCHAR(2),
 state VARCHAR(99),
 capital VARCHAR(99), 
 premium float,
 pop int, 
 lat float, 
 lon float,
 Region VARCHAR(22), 
 Division VARCHAR(22) 
);

Don’t forget to populate the tables with sample data and let’s move to the Streamlit apps.

Streamlit Apps

It is possible to create a Streamlit directly on the Snowsight interface. You just have to inform the warehouse, database, and schema in which the app will be deployed.

Whitin, a Streamlit app, can create different graphs and dashboards supporting various visual analyses.

Line Charts

Using the function get_active_sessionO it is possible to get the session object to the current Snowflake connection and run queries. The results of the queries can be saved as data frames. From there, it would be Streamlit manipulation, in this example, generate a line graph with the method line_chart().

import streamlit as st
import pandas as pd
from snowflake.snowpark.context import get_active_session
session = get_active_session()
df = session.sql("select * from homeownership_rate ")
#st.write(df)
st.line_chart(data=df,x='YEAR',y='PERCENT' )

This chart makes it possible to observe how home ownership (including mortgages) in the US dropped since the 2008 economic crisis and has been recovering since.e.

Another example can be seen as follows:

import streamlit as st
import pandas as pd
from snowflake.snowpark.context import get_active_session
session = get_active_session()
df = session.sql("select * from life_insurance")
#st.write(df)
st.line_chart(data=df,x='YEAR',y=['OWN','DONT_OWN'] )

In this other example, it is possible to observe that in recent years, the percentage of Americans who don’t own a home (not including mortgage plans) surpassed the number of homeowners.

As a last example, we can observe a line chart with multiple columns comparing auto insurance premiums for each US state.

import streamlit as st
import pandas as pd
from snowflake.snowpark.context import get_active_session
session = get_active_session()
df = session.sql("select * from auto_insurance_premiums")
#st.write(df)
st.line_chart(data=df,x='YEAR',y=['MI','RI','NV','FL','NJ','DE','CT','OG','NY','MD','KY','LA','UT','AZ','MA','IL','SC','CO','HI','MN','MO','NH','WA','GA','CA','VA','OK','AL','WV','TX','NC','MS','NM','AR','KS','OH','AK','NE','IM','PA','ME','TN','MT','WI','ND','IA','ID','VT','WY','SD'] )

As you can notice, the two outliers for this graph are Michigan and Florida. Its high rates of criminality explain Michigan values in comparison to other states and Florida for 2021 and 2022. It is possible to observe also that all premiums came from a higher plateau during the COVID-19 pandemic and eased off more recently.

Bar charts

Line charts are ideal for analyzing trends throughout time. However, if you intend to compare values, a bar chart should be your weapon of choice.

import streamlit as st
import pandas as pd
from snowflake.snowpark.context import get_active_session
session = get_active_session()
df = session.sql("select * from auto_insurance_premiums_2023 ")
#st.write(df)
st.bar_chart(data=df,x='US_STATE',y='PREMIUM')

With this graph, it is possible to perceive more clearly that most US states charge an auto premium below $1,000, having Michigan as an outlier of over $2,000.

Aggregating Data

You may often have too much data to plot and analyze at once. If this is the case, you should summarize it using an aggregation function. It can be done directly into the SQL query using a group by clause or a Python function, as follows:

import streamlit as st
import pandas as pd
from snowflake.snowpark.context import get_active_session
session = get_active_session()
df = session.sql("select * from auto_insurance_premiums_2023 ")
#st.write(df)
agg = df.group_by('DIVISION').mean('PREMIUM')
st.bar_chart(data=agg,x='DIVISION',y='AVG(PREMIUM)')

On the aggerated graph, you can observe averages of auto premiums by division/region.

Plotting Maps

Lastly, it is possible to plot maps using Streamlit functions directly, on the example here, st.map():

import streamlit as st
import pandas as pd 
from snowflake.snowpark.context import get_active_session
session = get_active_session()
df = session.sql("select * from auto_insurance_premiums_2023")
st.map(df)

As the data has lat and lon columns corresponding to the location of each state capital, it is possible to see them all on the map below:

Final Thoughts

Streamlit is a Python library that makes creating and sharing interactive web apps for machine learning and data science easy. Snowflake is a cloud-based data warehouse that offers high performance, scalability, and security. Embedding Streamlit apps directly into Snowflake can combine the best of both worlds to build and share powerful data applications. Examples of Streamlit apps in Snowflake include data science dashboards, machine learning models, and data analysis tools.

The current limitations of Streamlit in Snowflake include the lack of support for custom components and the Seaborn and Matlibplot libraries. The Snowflake team is working on some updates to address these limitations and improve the overall Streamlit experience for Snowflake users.