Requesting Snowflake Native App References in a Consumer Account

In my previous post about Snowflake Native Apps, I demonstrated how to maintain a mapping table when the mapping table is included as part of the application. In this post, I’ll show a similar Native App for maintaining a mapping table that already exists in a consumer account.

Photo by Cristian Grecu on Unsplash

In data warehousing we often come across the situation where users want to enter data manually, for example, to map data from one value to another for grouping or reporting purposes. In my post Maintaining a Mapping Table with a Snowflake Native App, I demonstrated how to build a Snowflake Native App that enables users to edit simple mapping data through a Streamlit user interface and write the resulting data to a table in Snowflake. The table was included as part of the application and stored in the application database.

Users sometimes have mapping tables already in place and want just a front-end application to maintain them. Or they want to expose the mapping tables to other applications, such as reporting tools, and want to store the tables in their own account. In such cases, the Snowflake Native App must request access to the table in the consumer account. After the consumer approves the request, the application can write to the table.

Access to a table in a consumer account is granted through a reference. We use references to authorize access on objects to an application that does not have access to those objects by default.

Let’s quickly recreate the application for maintaining a mapping table from my previous post (Maintaining a Mapping Table with a Snowflake Native App) because we will use it as a starting point for the new application.

Maintaining a mapping table in the consumer account

The new application for maintaining a mapping table will read from and write to a table in the consumer account. We must create this table in the consumer account before the application can use it (in this demo, we use the same Snowflake account for both the consumer and the provider, but we still want a separate database to represent the consumer account). Let’s create a database called MASTER_DATA_DB, a schema called MAPPINGS, and a table called MAP_1 with some initial data:

CREATE DATABASE MASTER_DATA_DB;
CREATE SCHEMA MAPPINGS;
CREATE TABLE MAP_1 (FROM_CODE VARCHAR, TO_CODE VARCHAR);
INSERT INTO MAP_1 VALUES 
('101', 'Group A'),
('102', 'Group A'),
('205', 'Group C');

We will modify the native app that maintains a mapping table so that it will request access to the MAP_1 table in the consumer account.

According to the Snowflake documentation, we specify the access request in the manifest.yml file by adding a references tag. Under this tag, we define the following:

• the name of the reference, for example consumer_map. We will refer to the consumer table by this reference name because we don’t know the name of the actual table in the consumer account.
• the label
• the description
• the privileges that will be requested from the consumer account for this table. We are requesting SELECT, INSERT, UPDATE, and DELETE because we want to read and write to the consumer table.
• the object type, which is TABLE in our application
• the multi-valued parameter, which is set to FALSE in our application because we are requesting a single reference, the TABLE in the consumer account
• the name of the callback procedure that will be executed when the consumer account registers the reference. You can find an example of a callback stored procedure for a reference in the Snowflake documentation here. You must add this stored procedure to the application setup script.

This is the references tag we will add to the manifest.yml file:

references:
  - consumer_map:
      label: "Consumer mapping table"
      description: "A table that exists in the consumer account"
      privileges:
        - SELECT
        - INSERT
        - UPDATE
        - DELETE
      object_type: TABLE
      multi_valued: false
      register_callback: config.register_single_reference

To the setup.sql script, we will add the REGISTER_SINGLE_REFERENCE stored procedure and grant usage on this stored procedure to the APP_PUBLIC role (the contents of the stored procedure is the same as in the Snowflake documentation):

CREATE PROCEDURE CONFIG.REGISTER_SINGLE_REFERENCE(
  ref_name STRING, operation STRING, ref_or_alias STRING)
  RETURNS STRING
  LANGUAGE SQL
  AS $$
    BEGIN
      CASE (operation)
        WHEN 'ADD' THEN
          SELECT SYSTEM$SET_REFERENCE(:ref_name, :ref_or_alias);
        WHEN 'REMOVE' THEN
          SELECT SYSTEM$REMOVE_REFERENCE(:ref_name);
        WHEN 'CLEAR' THEN
          SELECT SYSTEM$REMOVE_REFERENCE(:ref_name);
      ELSE
        RETURN 'unknown operation: ' || operation;
      END CASE;
      RETURN NULL;
    END;
  $$;

GRANT USAGE 
  ON PROCEDURE CONFIG.REGISTER_SINGLE_REFERENCE(STRING, STRING, STRING) 
  TO APPLICATION ROLE APP_PUBLIC;

We must also make changes to the simple_mapping_st.py Streamlit app. In the original version of the application, the mapping data was stored in the application in a table called SIMPLE_MAPPING_APP.CORE.SIMPLE_MAP. In the new version of the application, the table that stores the mapping data will be accessed by a reference. Instead of naming the table in the code, we will use reference('consumer_map') to reference the table. We don’t have to know the actual name of the table in the consumer account because Snowflake will figure it out once the consumer account grants the reference.

Here is the updated user interface code, written in Python and using the Streamlit framework:

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

st.title("Simple Mapping Editor")

# Get the current session context
session = get_active_session()

# Create a form
with st.form("mapping_form"):
    st.caption("Add or update the mappings in the table below")
    #  Create a data frame from a table
    df = session.sql("SELECT FROM_CODE, TO_CODE FROM reference('consumer_map')")
    df.collect()
    # Convert it into a Pandas data frame
    pdf = df.to_pandas()
    edited_pdf = st.experimental_data_editor(pdf, use_container_width=True, num_rows="dynamic")
    submit_button = st.form_submit_button("Submit")

if submit_button:
    try:
        session.write_pandas(edited_pdf, 'SIMPLE_MAP', overwrite = True)
        session.sql('GRANT SELECT ON TABLE CORE.SIMPLE_MAP TO APPLICATION ROLE APP_PUBLIC').collect()
        session.sql("delete from reference('consumer_map')").collect()
        session.sql("insert into reference('consumer_map') select from_code, to_code from CORE.SIMPLE_MAP").collect()
        st.write('Data saved to table.')
    except:
        st.write('Error saving to table.')

As compared to the previous version of the application, we must make some changes in the Streamlit code. The data frame editor was initially populated using a SQL SELECT statement that selects data from the SIMPLE_MAP mapping table which was local to the application. We must change the table name to reference('consumer_map') because we will retrieve the data from the referenced table in the consumer account instead from the local table.

The user can add and update data in the data frame editor and once they are done, they press the Submit button. In the previous version of the application, the contents of the edited data frame were written back to the SIMPLE_MAP mapping table that stored data in the application database using the overwrite = True option. This option is used so that the data in the table is overwritten with the new data.

We already know from the first version of the application that when data from a data frame is written to a Snowflake table using the overwrite option, the data is first written to a temporary table, then the target table is dropped and the temporary table renamed to the target table. This behavior is fine when the data is written to the application database, because the APP_PUBLIC role that executes the application has sufficient privileges to create and drop tables in the application database.

However, when working with a table in the consumer account, the behind-the-scenes behavior is to create and drop a temporary table in the consumer account. This will produce an error because the application role wasn’t granted privileges to create and drop tables in the consumer account. Although we could request the privileges from the consumer (here is documentation about requesting global privileges for native apps from consumer accounts), we don’t want to request more privileges than are actually needed by the application, which is to read from and write to a mapping table.

A different solution is to write the data from the data frame that the user edited in the application to a local table in the application, then use SQL commands to write the data from the local table in the application to the referenced table in the consumer account. In this application, I use the DELETE and INSERT commands, but a MERGE command would also work.

Once we are done editing all files, we can put the files into the SIMPLE_MAPPING_APP_STAGE internal Snowflake stage and recreate the application, setting the DEBUG_MODE parameter to TRUE while we are still developing and testing:

DROP APPLICATION simple_mapping_app;

CREATE APPLICATION simple_mapping_app
  FROM APPLICATION PACKAGE simple_mapping_app_pck
  USING '@simple_mapping_app_pck.STAGE_CONTENT.simple_mapping_app_stage';

ALTER APPLICATION simple_mapping_app SET DEBUG_MODE = TRUE;

We can view all references that are defined in the application by executing the SHOW REFERENCES command:

SHOW REFERENCES IN APPLICATION simple_mapping_app;

The output of the command is a list of references defined in the application, in our case the consumer_map reference.

We can now test the application, but before we can execute the Streamlit user interface, we must grant the references.

Granting privileges in the consumer account

We grant the references to the consumer table by clicking the Security icon as in the following screen:

We can see on this screen that the application is requesting DELETE, INSERT, SELECT, and UPDATE privileges on the Consumer mapping table.

Let’s click the Add button, then the +Select Data drop-down list where we can navigate to the MASTER_DATA_DB database, the MAPPINGS schema, and choose the MAP_1 table.

After clicking the Done button, we can go to the Streamlit user interface and add some data in the mapping editor:

Then click the Submit button which should save the data to the consumer table.

We can then check that the data was written to the consumer table:

SELECT FROM_CODE, TO_CODE
FROM MASTER_DATA_DB.MAPPINGS.MAP_1;

Here is the result from the SELECT statement:

We can execute the SHOW REFERENCES command again to view all references that are defined in the application along with the objects to which they are mapped:

SHOW REFERENCES IN APPLICATION simple_mapping_app;

The output of the command is a list of references defined in the application, in our case the consumer_map reference, but now that the reference has already been granted, the output also shows the MAP_1 table to which the reference is mapped.

Other ways of granting references to objects in the consumer account

When a Snowflake Native App needs references to objects in the consumer account, we must always define the references in the manifest.yml file.

In addition to granting references via the user interface as shown here, there are other ways of granting references to objects in the consumer account. For example, the consumer can execute the grants as SQL commands if they know the reference names, as documented here. Another option is to code the request for references in the Streamlit user interface, as documented here.