Open in app

Sign in

Write

Sign in

Snowflake Native Apps: Accessing Consumer’s Data

In our previous write-up, we introduced the creation of a HelloWorld! Snowflake Native App, which delivered three basic stored procedures to a Snowflake consumer account.

Now, we’ll take it a step further and explore the process of sending data alongside our application to the consumer. By doing so, we’ll provide a data enrichment scenario that combines the consumer’s existing Snowflake data with the data we’re delivering. This introduces new capabilities for native apps and Snowflake’s sharing capabilities and we are excited to see what our customers create.

To begin, we’ll create a new application package with the following steps.

--CREATE NEW APP PACKAGE
CREATE APPLICATION PACKAGE REFERENCEDB;

--ADD VERSION TO NEW APP (SAME AS BEFORE BUT WE WILL ADD NEW OBJECTS)
ALTER APPLICATION PACKAGE REFERENCEDB
  ADD VERSION V1
  USING '@CODEDATABASE.CODESCHEMA.AppCodeStage';

--CREATE THE APP USING THE VERSION ABOVE
CREATE APPLICATION REFERENCEAPP
  FROM APPLICATION PACKAGE REFERENCEDB
  USING VERSION V1
  patch 0
;

This app above is leveraging the identical package and version from our previous iteration — blog post. However, we’ll tailor it by implementing necessary modifications and updates to suit our current requirements.

Provider Dataset

In the current scenario, the provider possesses data via a table and a secure view that they intend to share with a downstream consumer through their application. The provider maintains and manages this dataset as they would typically do as the custodian when sharing data via Snowflake Data Sharing.

Raw Data in MARKET_TRADES table and the secure MV is shared with the consumer

Application accessing Provider’s Data

Before we can send the data downstream to the consumer, we first need to give access to the application. First we will grant access to the database (PRODUCERDATA in this case) to the application package.

-- GIVE THE APP PACKAGE REFERENCE ACCESS TO A LOCAL DATABASE
GRANT REFERENCE_USAGE ON DATABASE PRODUCERDATA TO SHARE IN APPLICATION PACKAGE REFERENCE_APP_PACKAGE;

-- CREATE SCHEMA IN THE APP PACKAGE
CREATE SCHEMA REFERENCE_APP_PACKAGE.SHARED_CONTENT;

-- CREATE PROXY OBJECTS IN APP PACKAGE REFERENCING LOCAL DATA TO SHARE
CREATE VIEW REFERENCE_APP_PACKAGE.SHARED_CONTENT.MARKET_TRADES AS SELECT * FROM PRODUCERDATA.MARKET.MARKET_TRADES;
CREATE VIEW REFERENCE_APP_PACKAGE.SHARED_CONTENT.MINUTE_TICKS AS SELECT * FROM PRODUCERDATA.MARKET.MINUTE_TICKS;

-- GRANT ACCESS TO THE CONTENT TO THE APP PACKAGE (WILL NOT WORK W/O THIS)
GRANT USAGE ON SCHEMA REFERENCE_APP_PACKAGE.SHARED_CONTENT TO SHARE IN APPLICATION PACKAGE REFERENCE_APP_PACKAGE;
GRANT SELECT ON VIEW REFERENCE_APP_PACKAGE.SHARED_CONTENT.MARKET_TRADES TO SHARE IN APPLICATION PACKAGE REFERENCE_APP_PACKAGE;
GRANT SELECT ON VIEW REFERENCE_APP_PACKAGE.SHARED_CONTENT.MINUTE_TICKS TO SHARE IN APPLICATION PACKAGE REFERENCE_APP_PACKAGE;

The above statements will create all the necessary objects in the producers application package which will be used in the consumer account when the app is installed.

Updating the setup.sql Script

Next we will tell the application which objects to use when creating objects on the consumers Snowflake account. Remember when applications are installed or updated they are a clean slate and objects are re-created.

-- SAME AS PRIOR, THE THREE FUNCTIONS WE CREATED & PROVIDED PREVIOUSLY
GRANT USAGE ON PROCEDURE CORE.HELLO() TO DATABASE ROLE APP_PUBLIC;
GRANT USAGE ON PROCEDURE CORE.SUM(INT, INT) TO DATABASE ROLE APP_PUBLIC;
GRANT USAGE ON PROCEDURE CORE.MULTIPLY(INT, INT) TO DATABASE ROLE APP_PUBLIC;

-- WILL CREATE A NEW VERSIONED SCHEMA TO BE USED FOR PUBLIC OBJECTS
CREATE OR ALTER VERSIONED SCHEMA SHARED_DATA;
GRANT USAGE ON SCHEMA SHARED_DATA TO DATABASE ROLE APP_PUBLIC; -- PROVIDES PUBLIC ACCESS

-- USE OBJECTS FROM THE APPLICATION PACKAGE AND CREATE 
-- THE APPLICABLE OBJECTS IN THE INSTALLED APP
CREATE VIEW SHARED_DATA.MARKET_TRADES AS SELECT * FROM SHARED_CONTENT.MARKET_TRADES;
CREATE VIEW SHARED_DATA.MINUTE_TICKS AS SELECT * FROM SHARED_CONTENT.MINUTE_TICKS;

-- GRANT THEM TO APP_PUBLIC SO THE CONSUMER CAN ACCESS THEM
GRANT SELECT ON VIEW SHARED_DATA.MARKET_TRADES TO DATABASE ROLE APP_PUBLIC;
GRANT SELECT ON VIEW SHARED_DATA.MINUTE_TICKS TO DATABASE ROLE APP_PUBLIC;

When the consumer installs the application they will be able to query the data we provide with the application. We will deliver the application to a consumer similar to the previous blog post via a Private Listing.

App Delivery and Data Querying

We have successfully delivered the application via private listing (same as previous blog post), and the consumer has installed the app to view our datasets. Sharing data is one of Snowflake’s core strengths, and in this case, we have simply packaged our data products within an application for ease of delivery. Few new capabilities have been utilized.

Next step, we will take this further and enrich the consumer’s local Snowflake data with the data we have delivered as an app producer while also providing the logic to do so.

Data Shared Alongside Application Logic

Adding Patches to Versions

Incrementally we will be making changes to the application. Not all changes need new versions but may need new patches. To add a new patch, update the setup.sql script, application logic and then create the patch via SQL as below.

ALTER APPLICATION PACKAGE REFERENCE_APP_PACKAGE 
ADD PATCH 
FOR VERSION V1
USING '@CODEDATABASE.CODESCHEMA.AppCodeStage';

-- Snowflake will return the following
-- Patch 1 added to version 'V1' of application package 'REFERENCE_APP_PACKAGE'.
Listed Versions (max 2) with respective patches

Once we’ve added a new patch it may be a good idea depending on where we are with our app release lifecycle to update the applications directive. When the application is installed it will be installed with the version and patch outlined in the directive.

-- SET APPLICATIONS DEFAULT DIRECTIVE
ALTER APPLICATION PACKAGE REFERENCE_APP_PACKAGE
  SET DEFAULT RELEASE DIRECTIVE
  VERSION = V1
  patch = 1;

-- THE CONSUMER OR IF TESTING LOCALLY YOU NEED TO UPDATE THE APPLICATINS
-- YOU MAY SPECIFY WHICH VERSION & PATCH OR GO WITH THE DEFAULT DIRECTIVE 
ALTER APPLICATION REFERENCEAPP 
UPGRADE 
USING 
    VERSION V1
    PATCH 1;

NOTE: We are reusing the same setup.sql script and just incrementally making updates. You may choose to use different scripts and update the manifest.yml file accordingly.

Building a Grading Function

Functions can provide valuable insights and analysis for businesses that deal with financial data. High value functions can help businesses make more informed decisions about their investments and financial strategies. With accurate and up-to-date grading information, businesses can optimize their portfolios, identify opportunities for growth, and minimize risks. Overall, a grading function can provide a powerful tool for businesses looking to maximize their financial performance.

First we will build a look-up function. One where the consumer provides a SYMBOL and the grade is provided. We complete that by adding the function logic in our setup.sql. This function is created when the app is installed.

-- FUNCTION TO LOOK UP GRADE FOR 1 SPECIFIC SYMBOL
create or replace procedure CORE.LOOKUP_GRADE(SYMBOL VARCHAR)
returns integer
language javascript
as     
$$  
    var lookupSQL = `select grade from SHARED_CONTENT.STOCK_GRADES where symbol = '${SYMBOL}'`;    
    var grade_result = snowflake.createStatement( {sqlText: lookupSQL} ).execute();
    grade_result.next();
    var grade = grade_result.getColumnValue(1);
    
    return grade; 
$$;

GRANT USAGE ON PROCEDURE CORE.LOOKUP_GRADE(VARCHAR) TO DATABASE ROLE APP_PUBLIC;

The consumer can call the function like normal and receive a grade for that ticker.

Expanding the Scenario

We’re taking our grading function to the next level by enabling customers with large datasets in Snowflake to access it through our Application. Consumers will provide their own datasets for the Application to utilize.

In order to make this happen, we’ve updated our set up script to create a table that will store the graded portfolios, which our Application will have logic on how to populate this data.

-- NEW VIEW WITH GRADES DATA
CREATE VIEW SHARED_DATA.STOCK_GRADES AS SELECT * FROM SHARED_CONTENT.STOCK_GRADES; -- OUR GRADES INFORMATION
GRANT SELECT ON VIEW SHARED_DATA.STOCK_GRADES TO DATABASE ROLE APP_PUBLIC;

-- NEW TABLE WHERE GRADED PORTFOLIOS WILL LIVE
CREATE TABLE IF NOT EXISTS ENRICH.PORTFOLIO_GRADE
  (RUNDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP(), PORTFOLIO_NAME VARCHAR, GRADE NUMBER);

-- MAKE IT AVAILABLE TO THE CONSUMER
GRANT SELECT ON TABLE ENRICH.PORTFOLIO_GRADE TO DATABASE ROLE APP_PUBLIC;

With this above functionality, consumers can easily read and access the graded portfolios, allowing for a more streamlined and efficient workflow.

Accessing Consumer Information

In our application design, we provide the consumer with a dedicated space to store their portfolios by creating a table in the application. This table, defined by the application logic in our setup.sql script, will serve as a central repository for the consumer’s information, easily accessible by the application and the consumer. This table will have SELECT and INSERT grants to the table.

-- CREATE THE TABLE ONCE THE APPLICATION IS INSTALLED
CREATE TABLE IF NOT EXISTS ENRICH.PORTFOLIOS (PORTFOLIO_NAME VARCHAR, SYMBOL VARCHAR);

-- GIVE READ ACCESS TO THE CONSUMER
GRANT SELECT ON TABLE ENRICH.PORTFOLIOS TO DATABASE ROLE APP_PUBLIC;

-- GIVE WRITE ACCESS TO THE CONSUMER
GRANT INSERT ON TABLE ENRICH.PORTFOLIOS TO DATABASE ROLE APP_PUBLIC;

The data in this table does not leave the consumers Snowflake account. The application runs on the consumers account and that is where the table lives.
The Provider has no access to the data in this table.

Consumer Writing Data to App Table

With the above given rights the consumer will have the ability to READ and WRITE to the ENRICH.PORTFOLIOS table. Free to insert or bulk load data as necessary.

INSERT INTO ENRICH.PORTFOLIOS VALUES ('SPY', '<< TICKER >>');

Putting it All Together

With the portfolio information table and the portfolio grades table in place, the missing piece is the logic to seamlessly integrate them. Once we create this integration, the application will be able to effectively provide portfolio grades to the consumer based on the provided information, making it a complete and powerful solution.

-- FUNCTION TO LOOK UP GRADE FOR PORTFOLIOS STORED IN THE APP TABLE
create or replace procedure CORE.PORTFOLIO_GRADE()
returns string
language javascript
as     
$$  
    var lookupSQL = `INSERT INTO ENRICH.PORTFOLIO_GRADE (
                        SELECT current_date AS RUNDATE, PORTFOLIO_NAME, AVG(GRADE) AS GRADE
                        FROM SHARED_CONTENT.STOCK_GRADES SG 
                            JOIN ENRICH.PORTFOLIOS P ON SG.SYMBOL = P.SYMBOL
                        GROUP BY 2
                    );`;    
    var grade_result = snowflake.createStatement( {sqlText: lookupSQL} ).execute();
    grade_result.next();
    var grade = grade_result.getColumnValue(1);
    
    return 'PLEASE CHECK THE PORTFOLIOS_GRADE TABLE IN THE APPLICATION'; 
$$;

GRANT USAGE ON PROCEDURE CORE.PORTFOLIO_GRADE() TO DATABASE ROLE APP_PUBLIC;

By invoking the function call CORE.PORTFOLIO_GRADE(), the consumer can initiate the grading process for their portfolios. The function will calculate the grades based on the average logic and store the results in the ENRICH.PORTFOLIO_GRADE table. This functionality provides the consumer with a convenient and efficient way to enrich their data and gain valuable insights.

After defining our application objects (shown below), we have carefully merged information from the data provider with data provided by the consumer. The resulting table is a comprehensive data set that the consumer can easily query whenever needed. Our application architecture is designed to seamlessly blend both data sources to provide a unified view of the data.

The beauty of the above scenario lies in its seamless access and organization of relevant objects and datasets. The application is equipped with all the necessary information, while the consumer can easily read and write in their own designated table. The merging of data happens within a single cohesive platform, ensuring security in all cases.

Securing Sensitive Data: Protecting in App Information

In some cases, the application may need access to a particular dataset that the data provider prefers not to share with the consumer. To address this, we have developed a solution that conceals the STOCK_GRADES table from the consumer while allowing the application to execute the PORTFOLIO_GRADE() function and produce graded portfolios.

This approach strikes a balance between data confidentiality and functionality, ensuring that the application can operate effectively while protecting sensitive data.

-- REMOVING APP_PUBLIC ACCESS TO SENSITIVE INFORMATION ENSURES DATA IS SECURED
-- GRANT SELECT ON VIEW SHARED_DATA.STOCK_GRADES TO DATABASE ROLE APP_PUBLIC; -- OUR GRADES INFORMATION

By REMOVING access to DATABASE ROLE APP_PUBLIC we make the object(s) hidden and the application still maintains access.

In a real world scenario — access would have never been granted to sensitive objects.

Data Enrichment

One of the most popular use cases for customers who receive data from providers is to enrich their existing data in Snowflake. Data enrichment for wealth management firm that uses Snowflake to manage their client data. They may receive daily or streaming data feeds from various sources such as market data providers, custodians, and other financial institutions.

To provide more insights data providers could deliver data enrichment logic alongside their valuable datasets for their consumers to combine their internal client data with the external data feeds to create a more holistic view of their clients’ investments. For example, they could use the external data feeds to calculate market indices or benchmark their clients’ portfolios against industry standards. The firm can make more informed investment decisions and provide better advice to their clients.

Snowflake
Native App
Apps
Application

--

--

Marius Ndini
Snowflake Builders Blog: Data Engineers, App Developers, AI/ML, & Data Science

Written by Marius Ndini

26 Followers
Writer for

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams