Simple Data Sharing Application Using Snowflake Native Apps

Turbocharge data sharing with non-technical consumers

Warming hut in Royal Gorge Ski Resort, March 2023

Background

As a sales engineer at Snowflake, I have the privilege of helping my customers brainstorm ideas and find solution to do more with data. One way that makes data more useful is being able to share it in a simple but governed way. A common need I see is the ability to share data with users in other accounts of Snowflake who don’t want to or should not be expected to run SQL or python queries against this data. Typical requirements go like this:

• I want to share my data to account X (a full or reader account of Snowflake)
• I have to meet my consumers where they are — they should be able to simply view a report without writing any code
• My consumers want the ability to download raw data to CSV
• I can’t/don’t want to use any third-party tools

With Snowflake’s new Native Apps framework, you can deliver a solution like this is in an afternoon.

Let’s Create a Data Sharing App!

I picked jail data as my scenario. In the state where I live, county Sheriff’s departments typically run jails and have an on-going need to share a list of “who is in jail” with their partners, such as DA’s offices, police departments, probation departments, county health authorities and so on.

Here’s a prototype of the solution I explain how to build — a simple report deployed in consumer accounts:

Streamlit app to share jail data with consumer accounts (data is synthetic)

To test this scenario, I used Snowflake’s Native Apps tutorial as a starting point. Stepping through this excellent tutorial will give you a good idea of the moving parts involved.

To make the scenario a little more realistic, I added or modified the following steps:

(1) Added a slightly more realistic dataset, using a python function to generate synthetic jail data. Once I got to Step 6 “Add Data Content to Your Application”, I ran the following in the hello_snowflake_package.shared_data schema:

--make synthetic data. This can be run in a Snowsight Worksheet 
USE SCHEMA shared_data;

CREATE OR REPLACE FUNCTION PY_FAKER(a string)
returns STRING
language python
runtime_version = 3.8
packages = ('faker')
handler = 'udf'
AS $$
from faker import Faker
def udf(a):
 fake = Faker()
 if a=='first_name':
 return fake.first_name()
 elif a=='last_name':
 return fake.last_name()
 elif a=='uuid':
 return fake.uuid4()
 elif a=='ssn':
 return fake.ssn()
 else:
 return 'other'
$$;

create or replace table jail_data as 
select 
PY_FAKER('uuid') id,
PY_FAKER('first_name') first_name,
PY_FAKER('last_name') last_name,
PY_FAKER('ssn') ssn,
dateadd(day, -uniform(1, 600, random()), current_date()) booking_date,
uniform(18, 80, random()) age_at_booking
from table(generator(rowcount => 1000)) v 
order by 1;
select * from jail_data;

GRANT USAGE ON SCHEMA shared_data TO SHARE IN APPLICATION PACKAGE hello_snowflake_package;
GRANT SELECT ON TABLE jail_data TO SHARE IN APPLICATION PACKAGE hello_snowflake_package;

synthetic ”who is in jail” data

(2) Added a jail_data_viewto the setup.sql script, in order to make this data available to my native app.

--add these lines to your setup.sql script
CREATE VIEW IF NOT EXISTS code_schema.jail_data_view
  AS SELECT * 
  FROM shared_data.jail_data;
GRANT SELECT ON VIEW code_schema.jail_data_view TO APPLICATION ROLE app_public;

(3) Once I got to Step 8 of the tutorial “Add a Streamlit App to Your Application”, I added one more file in my /streamlit folder, which I called jail_data_share_app.py . The app allows the user to look at a table of data, filter it by the number of days since booking, and export results to CSV.

# contents of jail_data_share_app.py
# Import python packages
import streamlit as st
from datetime import datetime, timedelta
from snowflake.snowpark.context import get_active_session

st.set_page_config(layout="wide")

# Write directly to the app
st.title("Jail Data Share - Streamlit Edition")
st.write(
   """The following data is from the Jail Data in the application package.
      However, the Streamlit app queries this data from a view called
      code_schema.jail_data_view.
   """
)

# Get the current credentials
session = get_active_session()

days_since_booking = st.slider(
    "Days since booking:",
    min_value=0,
    max_value=365,
    value=30,
)

#  Create an example data frame
data_frame = session.sql(f"SELECT * FROM code_schema.jail_data_view \
                           WHERE booking_date between \
                           dateadd('day', -{days_since_booking}, current_date()) and current_date();")

# Execute the query and convert it into a Pandas data frame
queried_data = data_frame.to_pandas()

# Display the Pandas data frame as a Streamlit data frame.
st.dataframe(queried_data, use_container_width=True)

# Function to convert DataFrame to CSV and generate download link
def convert_df_to_csv(df):
    return df.to_csv(index=False).encode('utf-8')

# Download button for CSV
st.download_button(
    label="Download CSV",
    data=convert_df_to_csv(queried_data),
    file_name='my_data.csv',
    mime='text/csv',
)

The setup.sql also needed to be updated to add this streamlit application:

--add these lines to setup.sql
CREATE STREAMLIT code_schema.jail_data_share_app
  FROM '/streamlit'
  MAIN_FILE = '/jail_data_share_app.py'
;

GRANT USAGE ON STREAMLIT code_schema.jail_data_share_app TO APPLICATION ROLE app_public;

(4) Pushed the updated files, including the revised setup.sql , and the new jail_data_share_app.py file, to the package stage.

I then followed the rest of the steps in the tutorial to publish the application and share it with another Snowflake account. When I needed to make small tweaks, I consulted the documentation about the app versioning process.

That’s it! I now have a working prototype of a simple data sharing app that includes both data and a simple report that allows users to export the data to CSV, if they prefer. This pattern of data sharing augments existing data sharing patterns that Snowflake excels at and meets data consumers where they are. If you are looking for more sophisticated examples of Snowflake’s Native Apps, there are so many right here on Medium! For example, check out a paper on the CKAN connector by my colleague Gabriel Mullen.

Notes: As of this writing, Native Apps are in public preview in selected Snowflake regions. Also, the st.download_button is a private preview feature of Streamlit in Snowflake (SiS). Please reach out to your account team if you have questions about leveraging these capabilities.