Editable Snowflake Tables in Streamlit (a Demo)

One of the most common requests I hear is “Can we please have the ability to make updates in a Snowflake table through the UI”. With the latest release of Streamlit (and namely the experimental_data_editor), we can do exactly that.

There are numerous reasons organisations need this functionality. For example, sometimes a select few people need to update reference data for downstream processing. Or maybe there are data quality issues that require a quick fix. Or even budget and planning use cases.

In this post, I will take you through a very rudimentary example of how this functionality can be achieved. This is only for illustrative purposes (this is still an experimental feature), but you could easily take this code as a base to accomodate larger datasets, or even incorporate “Streams and Tasks” to track the changes made to the edited table.

Demo Time

So the first step is to get some data for our demo. With Snowflake’s Data Marketplace, this is very straightforward. I have chosen the S&P ESG Scores (S&P500 Sample) from our friends at ESG Book.

Once I clicked the “Get” button, the data was live and ready for me to query in my Snowflake account.

The next step is to create a clone of the dataset. Data from the marketplace is Read-Only by design, so we need our own Database, Schema and Table for our interactive copy. I have named the table ESG_SCORES_DEMO, which will be referenced in my streamlit code.

CREATE database ESG_SCORES_SP500_DEMO;
CREATE schema SCORING;

CREATE table ESG_SCORES_SP500_DEMO.SCORING.ESG_SCORES_DEMO as
    (SELECT * FROM ESG_SCORES_SP500_SAMPLE.SCORING.TRIAL_SCO_ESG_262);

Next, I created a python file called “editable_tables.py”, and a “creds.json” file in a folder on my local computer. The creds.json file holds the information to connect to Snowflake so it is not hardcoded in the app. It has the following structure

{
    "account": "Your Account Identifier",
    "user": "User that has access to DB and ability to write table",
    "password": "",
    "warehouse": "",
    "database": "ESG_SCORES_SP500_DEMO",
    "schema": "SCORING"
  }

Next I wrote the following python code. We are able to get this prototype running in under 40 lines of code

import pandas as pd
import json
import streamlit as st
import time
from snowflake.snowpark import Session

import pandas as pd
import json
import streamlit as st
from snowflake.snowpark import Session
import time

if 'snowflake_connection' not in st.session_state:
    # connect to Snowflake
    with open('creds.json') as f:
        connection_parameters = json.load(f)
    st.session_state.snowflake_connection = Session.builder.configs(connection_parameters).create()
    session = st.session_state.snowflake_connection
else:
    session = st.session_state.snowflake_connection

st.set_page_config(layout="centered", page_title="Data Editor", page_icon="🧮")
st.title("Snowflake Table Editor ❄️")
st.caption("This is a demo of the `st.experimental_data_editor`.")

def get_dataset():
    # load messages df
    df = session.table("ESG_SCORES_DEMO")

    return df

dataset = get_dataset()

with st.form("data_editor_form"):
    st.caption("Edit the dataframe below")
    edited = st.experimental_data_editor(dataset, use_container_width=True, num_rows="dynamic")
    submit_button = st.form_submit_button("Submit")

if submit_button:
    try:
        #Note the quote_identifiers argument for case insensitivity
        session.write_pandas(edited, "ESG_SCORES_DEMO", overwrite=True, quote_identifiers=False)
        st.success("Table updated")
        time.sleep(5)
    except:
        st.warning("Error updating table")
    #display success message for 5 seconds and update the table to reflect what is in Snowflake
    st.experimental_rerun()

Run Through

Below is a screenshot of the original dataset in my Snowsight UI

Let’s say, hypothetically, I want to change the domiciled country for the first row (“A. O. Smith Corporation”) to GBR. I first start the app in my terminal by typing:

streamlit run editable_tables.py

I will see the app running in my browser

Next, I go to the selected cell, update it, hit enter and click submit

Now if I go back to the Snowsight UI and look at the table again, I can see the data has updated!

Wrap Up

From the above example, you can see we have used some of Snowflake’s new features to create a functional app prototype, namely:

• Utilising Streamlit to create a prototype app for internal business purposes in a few lines of code
• Utilising the Snowpark APIs to interact with Snowflake concisely in Python
• Utilising the Marketplace to easily and instantly obtain data without ETL

Feel free to use the code above and modify it! As mentioned previously, this is just a quick prototype, but could easily be enhanced by giving user feedback on which rows have changed (change tracking), search capabilities, etc!