Snowflake write-back applications in 5 easy steps with Streamlit

With Streamlit in Snowflake (SiS), it’s incredibly simple

My dearest data community (+ #datafam)! 👋

It’s been ages since I last put words on a Medium post. For those of you new to my ramblings, my name is Tom Christian. I’m a Principal Sales Engineer at Snowflake (❄️), the world’s leading cloud data platform.

What has struck me during my time at Snowflake is the unique pace of innovation in the product, enhanced by exciting acquisitions — such as Streamlit. Coming from an analytics background, this particular acquisition immediately piqued my interest. Easy to build applications? Visually stunning? Count me in.

That excitement compounded itself recently with the news that Streamlit will soon be made available directly within Snowflake [a.k.a. Streamlit in Snowflake or ”SiS” (currently in private preview)]. I couldn’t sit still here; this is something the world needs to get involved in… so time to dust off the old Medium and get to sharing.

To set some context, my skill level with Python is confidently “Stack Overflow proficient.” Or, to put it bluntly, if I can Google (or have ChatGPT auto-complete) code, I’ll survive. I’m by no description a code junkie. With the low-code world of Streamlit, I don’t have to be. Combined with Snowflake, SiS takes it a step further by removing some of the more tedious tasks, such as hosting and security from the build process. All I need is my data in Snowflake, a Streamlit worksheet, import streamlit as st — and I’m off! The potential to rapidly develop applications at the speed of thought is staggering. Applications run the world — and after years of watching people struggle to build them in BI tools, this is a breath of fresh air.

With this potential in front of me, an example immediately came to mind. Write-back in BI tools always felt like an enormously painful task. Partners tend to take it upon themselves to write custom extensions that require additional management, approval, and whitelisting — just to get your pretty dashboard to log a comment within a database. SiS offers a very different experience. Thanks to Snowflake, the application lives in the same location as the data, guarded by the same governance policies and leveraging the same scalable management-free infrastructure. The result? Write-back is EASY. As simple as a handful of reusable Python lines.

To prove its power, we’re going to create a write-back application in SiS and demonstrate its simplicity. If only you’re here for the code, you’ll find it below, but we’ll also be walking through each line in depth to further your understanding — and to set you on a journey of building your own content rapidly:

# STEP 1 : IMPORT PYTHON PACKAGES
import streamlit as st
from snowflake.snowpark.context import get_active_session

# STEP 2 : USE CURRENT SNOWFLAKE SESSION, NO NEED TO MANAGE CREDS!
session = get_active_session() 

# STEP 3 :  SET UP YOUR INPUT FORM
with st.form("update_report"):
    comment_txt = st.text_area('Report comment:')
    comment_dt = st.date_input('Date of report:')
    sub_comment = st.form_submit_button('Submit')

# STEP 4 : WRITE THAT TO A TABLE IN SNOWFLAKE
if sub_comment:
        session.sql(f"""INSERT INTO DB.SCHEMA.TABLE (DATE, USER, COMMENT) 
    VALUES ('{comment_dt}', CURRENT_USER(), '{comment_txt}')""").collect()
        st.success('Success!', icon="✅")

# STEP 5 : PRESENT THE TABLE IN THE APP
q_comments = f"""SELECT * FROM DB.SCHEMA.TABLE ORDER BY 1 desc LIMIT 10"""
df_comments = session.sql(q_comments).to_pandas()
st.dataframe(df_comments, use_container_width=True)

🛠 How to add a write-back element to your own Streamlit application

An example Streamlit application showing a combination of charts, inputs, and write-back

For the purposes of this post, the write-back snippet above features within an example sales application. Whilst checking their daily sales in their thriving food truck business — the code snippet above allows a user to comment in case an external factor (such as the weather) poses an issue for a certain day. For the rest of the article, we’ll walk through how you can add the same functionality to your own Streamlit creations.

Step 1. Import your libraries

When working with SiS, you’ll be coding in a Python worksheet hosted in the Snowflake UI. So your first step is to import the libraries you’ll need. Fortunately, you’ll only need the two packages that are auto-populated when creating an app (hardly a step at all!):

import streamlit as st
from snowflake.snowpark.context import get_active_session

For your reference, the Streamlit library is in charge of the front-end display and management of each widget. Whereby Snowpark takes care of the querying and processing of the data behind it. SiS brings them together in one place. ✌️

Step 2. Use your existing Snowflake session

session = get_active_session()

As in Step 1, this line is kindly populated when creating the Streamlit application. However, it’s an important callout nonetheless. This step houses a “magic” piece that SiS handles on your behalf.

Through the use of the get_active_session() command, the application becomes aware of your role, user, and Snowflake session in flight. No need to manage credentials—Snowflake takes care of that for you.

Step 3. Create your input form

Now to the part where you’ll have to write some code. As part of your write-back application, you’ll need a method of entering something that you’d like to insert into your table of data. In this instance, you can use Streamlit elements to build that entry form.

For Streamlit newbies, by calling and using the Streamlit commands, you’ve created your app. Streamlit will create whatever you ask it to in a dynamic, beautiful, modern style. Check out the docs for the full list of elements.

st.date_input defaults to the current date and is ideal for data entry.

In the example input form, three Streamlit elements are in use: st.text_area, st.date_input, and st.form. Text_area and date_input are simple entry elements corresponding to the data types you’d like to enter. Text_area is free form, whereas date_input pops up with a handy date selector. There is a multitude of configuration options available in the docs, but for this exercise, you can set the title and leave the rest with the default value. Again, the code is remarkably straightforward:

with st.form("update_report"):
	comment_txt = st.text_area('Report comment:')
	comment_dt = st.date_input('Date of report:')

You’ll notice that the input objects are indented within thest.form component. It will both visually group objects together and ensure they’re evaluated together rather than individually as objects. As an input mechanism, this is more efficient than placing the objects by themselves.

With the input sections in place, you’ll want an action element to process them. As you’re using st.form, you’ll be using st.form_submit_button, rather than the standard st.button. As part of your form, the button lives within the form itself to ensure consistency:

with st.form("update_report"):
	comment_txt = st.text_area('Report comment:')
	comment_dt = st.date_input('Date of report:')
	sub_comment = st.form_submit_button('Submit')

However, that just creates the button, the second part of the component lets you define what Streamlit should do when you hit that button. Let’s use a boolean argument to say, “when the button is pressed, do the next set of tasks”:

if sub_comment:

We’re four lines of Python in, and your app is almost complete!

To finish it, you’ll need to tell Streamlit what to do with the unique user entries. The same logic applies to drop downs and more controlled entry values (e.g., select a table and perform an action). Your imagination can run wild once that’s established.

Step 4. Write and trigger SQL

Now that you have your entry elements, it’s time to make your app do something. At the very start of your app (all three lines of code ago), you established your session to Snowflake automatically. Now you get to call it and ask it to perform a SQL task.

session.sql, as the name implies, is the Snowpark component you need to run an SQL command. It’s worth highlighting that session.sql alone isn’t enough to actually run the SQL. You’ll need to append it with a function like .collect() to trigger the execution. This is particularly handy for data engineering tasks whenever you’d like to construct your logic lazily and only execute it as a single block at the end:

if sub_comment:
	session.sql(f"""
	INSERT INTO DB.SCHEMA.TABLE (DATE, USER, COMMENT) 
	VALUES ('{comment_dt}', CURRENT_USER(), '{comment_txt}')"""
	).collect()
	
	st.success('Success!', icon="✅")

Your SQL example assumes there’s a table to insert in place already. SiS will run using a defined Snowflake role and the same governance policies guarding your data in Snowflake today. This means you’ll have to have the correct grants applied to that role to write to the table — or your form will fail to process. As an optional step, you may even want to have a user write to a temporary location for housing new comments prior to processing and validating centrally ahead of logging the results in a guarded location.

The key to your mini app logging anything is the parameterized values within the insert statement. By wrapping a value in curly brackets ‘{}’, you can add your Python variables directly into the SQL statement. Note that the Snowflake function CURRENT_USER() is present here too, letting you log the user that made the comment. As this is Snowflake, you can also add complexity and detail by calling User Defined Functions with predefined logic rather than constructing an SQL within the application.

As a final point, the last line in your example is all ‘extra.’ On the success of a statement, you can provide a prompt to the user, so they know the action was successful. When dealing with application best practices and user behavior, confirmation is more useful than you’d ever imagine. It helps a user avoid repeating themselves. Better yet, Streamlit will allow you to replace that “boring” standard checkmark with balloons — or even Snowflakes — to celebrate your user’s comment-logging achievements. Nothing beats the feeling of accomplishment after the completion of a menial task!

Fun bonus tip: With Unistore arriving in the future, you could replace the table in question with a Hybrid table which serves as an ideal object for single-row entry and retrieval.

Step 5. Present the data back

At this point, you’re technically done. And if I were less passionate, this could’ve been a two-step article instead! To wrap up our example, once a user logs a comment, it’s reflected in your app’s table:

John is a particularly passionate user of comments.

To retrieve data from Snowflake, you’re going to use the same functions from earlier with a marginal tweak:

q_comments = f"""SELECT * FROM DB.SCHEMA.TABLE ORDER BY 1 desc LIMIT 10"""
df_comments = session.sql(q_comments).to_pandas()

st.dataframe(df_comments, use_container_width=True)

You’ve constructed your SQL and used session.sql as before, but this time you’re going to run .to_pandas() as your executing function. That allows you to capture the results of the statement and write them directly into a pandas DataFrame. The benefit here is that the results can then be used in conjunction with any Streamlit widget. 📊

The element you’ll use for this task is st.dataframe to display this table of results on the page. It’s straightforward, auto-sizes the columns to fit the text, can highlight maximum values, and, importantly, presents the data neatly. With Streamlit v1.19.0+, you’ll be able to use editable dataframes to present existing comments, edit values, and write any changes (or new entries) back to a table within Snowflake — all within a single element.

Based on the example you’re working through, the code block housing the data retrieval query is placed after the code for the button. We’re also explicitly not wrapping the SQL function within @st.cache_data, that’ll ensure that the table refreshes upon submission, presenting the new values to users as they enter them.

Wrapping up

At this point, take a deep breath and high-five yourself. You’re done! 👏

You’ve gone from nothing to getting users to enter comments to their heart’s content in minutes (seconds, if you skipped the deep dive). More importantly, the logic behind data retrieval and data entry should form the basis of any app you build with SiS — so I hope you found this useful.

If you have any questions, please post them in the comments below or contact me on LinkedIn or Twitter.

Happy Streamlit-ing [in Snowflake ❄️]! 🎈