Snowflake query history with Streamlit

Use Snowflake query history with Streamlit to monitor the changes in a Table.

One of the best features of Snowflake is query history. It allows us to view and restore the history of a table for upto 90 days. But if you want to see which columns in a table have changed and what the previous values were, it can be little cumbersome. This demo shows how you can do that with streamlit.

First, let's create a database and a table in Snowflake

CREATE DATABASE TIMETRAVEL_DEMO;

We use the cereals.csv from dagster. Upload the file into Snowflake from the admin panel.

Update the table and check query history

Next, we apply some updates to the cereals table.

UPDATE cereals SET sodium = 215 WHERE name = 'Bran Flakes';
UPDATE cereals SET protein = protein+1 WHERE protein = 4;
UPDATE cereals SET protein = 2 WHERE name = 'Apple Jacks';
UPDATE cereals SET protein = 5 WHERE name = 'Apple Jacks';
UPDATE cereals SET protein = 4 WHERE name = 'Apple Jacks';
UPDATE cereals SET protein = 3 WHERE name = 'Apple Jacks';
UPDATE cereals SET calories = 110 WHERE name = 'Almond Delight';
UPDATE cereals SET calories = 130 WHERE name = 'Almond Delight';
UPDATE cereals SET calories = 135 WHERE name = 'Almond Delight';
UPDATE cereals SET calories = 130 WHERE name = 'Almond Delight';

Now, let's use query history to check the history. The below query will show all the

SELECT start_time
    ,  query_text
FROM TABLE(TIMETRAVEL_DEMO.INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_type = 'UPDATE'
ORDER BY start_time desc

Now, we create a view that we can use in our Streamlit app

Since this is just a demo on how to achieve this, I've taken certain liberties on how the view is created. In production we should definitely be more careful.

We filter for query_type = 'UPDATE', since we only want to see the changes in the table. Then we extract the column_name that was changed, it's value, the condition used and the new_value

CREATE VIEW timetravel_demo.public.cereals_history as (

SELECT (start_time::date)             AS  date
    ,  SPLIT_PART(query_text,' ',3)   AS  column_name
    ,  SPLIT_PART 
        ( SPLIT_PART 
          ( query_text,' ',5),'\n',0) AS  new_value
    ,  SPLIT_PART(query_text,' ',6)   AS  condition
    ,  SPLIT_PART(query_text,' ',8)   AS  condition_value
    ,  query_text

FROM  TABLE(TIMETRAVEL_DEMO.INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_type = 'UPDATE'
ORDER BY start_time desc
)

For the query, UPDATE cereals SET protein = 2 WHERE name = 'Apple Jacks'; The above view will return:

protein as the column_name
2 as the new_value
name as condition
Apple Jacks as condition_value

Now, we prepare the Streamlit app,

If you don't know how to setup Streamlit, please follow this excellent guide from them

we create a file called timetravel_demo.py

import streamlit as st

conn = st.experimental_connection('snowpark')

df = conn.query('select * from timetravel_demo.public.cereals_history;', ttl=600)

option = st.selectbox(
    'Select Column?',
    (df["COLUMN_NAME"].unique())
    
    )
st.write('You selected:', option)

st.table(df.loc[df['COLUMN_NAME'] == option])

Now, we can run the demo.

streamlit run timetravel_demo.py

Select the column for which you want to see the history.