[2024] Understanding Setup Script for Snowflake Native Apps
Executive Summary:
- Native Apps Setup Script Overview
- Restrictions in Native Apps Setup Script
- Application Objects Visibility
- Setting up logs in Setup Scripts
- Setup Scripts Modularity
- Understanding Application Roles
- Best Practices
Native Apps Setup Script Overview
The setup scripts basically contains the SQL statements which runs whenever the application gets created on a snowflake account due to the following:
- For consumer when they installs/upgrades the Native App
- For Provider when they test the application package
This script runs SQL statements that are created within the application object that are required by the app. Objects can be anything, databases, stored procedures, views, tables, application roles, etc.
The setup script is a mandate file which is required inside the manifest.yml
Restrictions in Native Apps Setup Script
There are certain restrictions inside the native app’s setup script:
- We cannot run USE <statements> inside setup script. For instance USE DATABASE / USE SCHEMA / USE ROLE / USE SECONDARY ROLE
- We can’t invoke UDFs or Stored Procedures with EXECUTE AS CALLER
- We cannot create any UDFs or Stored Procedures that are snowpark based or includes import files from named stage.
Application Objects Visibility
Objects that are created by the setup scripts are internal to the application. These objects are invisible/inaccessible to the consumers directly.
Users with the role that owns the application can grant application roles. In the setup script, we can define the application roles to which we can grant the object access. User that granted with the application roles can access these objects.
Setting Up Logs in Setup Script
We can define the log level messages using the below commands (Snowflake Scripting) inside the setup script whenever the script executes.
- SYSTEM$LOG
- SYSTEM$LOG_<level>
Setup Scripts Modularity
We can make setup scripts modular for large and complex applications where we have long list of code that needs to be deployed.
Provider account can create a primary setup script that calls multiple secondary scripts. This provides flexibility to create different types of modules such as setup scripts separated for views/ tables, different objects etc.
This can be done using EXECUTE IMMEDIATE FROM command which we can have in the primary setup script, and they will execute the secondary scripts which resides inside the stage.
Limitations while creating modular setup scripts can be:
- We cannot have event logging enabled for the scripts that runs via execute immediate statement.
- We cannot call files which are in the encrypted external stages in consumer account.
- EXECUTE IMMEDIATE FROM is supported only in a setup script. Using this command outside the setup script is not supported.
Understanding Application Roles
- Whenever the application creates object within itself, consumer has no privileges on those objects. and when application creates objects outside the application, for instance database, then its visible only to the ACCOUNTADMIN.
- Application roles are created only inside the application, just like the database roles. However, we can grant privileges on the objects that are outside to the application in the application roles.
- Application roles should be created by the setup script when the application is installed and are automatically granted to the application owner’s role, who then can grant appropriate application roles to other roles in the consumer account.
Application roles are the only type of role that can be created within an application. You can’t create Database roles within application.
Best Practices
- Always prefer to use idempotent forms of create statement (create or replace / create if not exists) so that whenever the script run multiple times during the installation or upgrade, it does not cause failures if the object already exists.
- Define the schema names while creating the objects. for instance (create table schema.table_name) . If a new schema gets created, it will not change the session context.
- Even though the native apps does not prohibit creating the objects outsize the application object, we should not create objects that are outside application object but refers to the application object. This can cause problems in the consumer account when they install native apps.
- Write script to handle possible failures. For instance, if you have a script which contains multiple procedures (create or replace) then if the script fails somewhere in the middle, then the previously granted privileges will be removed. so for such instance, we should add grant usage* statements right after every DDL to handle such possible failures.
Setup Script Example
CREATE APPLICATION ROLE admin;
CREATE APPLICATION ROLE user;
GRANT APPLICATION ROLE user TO APPLICATION ROLE admin;
CREATE OR ALTER VERSIONED SCHEMA app_code;
GRANT USAGE ON SCHEMA app_code TO APPLICATION ROLE admin;
GRANT USAGE ON SCHEMA app_code TO APPLICATION ROLE user;
CREATE OR REPLACE PROCEDURE app_code.config_app(...)
GRANT USAGE ON PROCEDURE app_code.config_app(..)
TO APPLICATION ROLE admin;
CREATE OR REPLACE FUNCTION app_code.add(x INT, y INT)
GRANT USAGE ON FUNCTION app_code.add(INT, INT)
TO APPLICATION ROLE admin;
GRANT USAGE ON FUNCTION app_code.add(INT, INT)
TO APPLICATION ROLE user;You may also like to read
About Me:
Hi there! I am Divyansh Saxena
I am an experienced Cloud Data Engineer with a proven track record of success in Snowflake Data Cloud technology. Highly skilled in designing, implementing, and maintaining data pipelines, ETL workflows, and data warehousing solutions. Possessing advanced knowledge of Snowflake’s features and functionality, I am a Snowflake Data Superhero & Snowflake Snowpro Core SME. With a major career in Snowflake Data Cloud, I have a deep understanding of cloud-native data architecture and can leverage it to deliver high-performing, scalable, and secure data solutions.
Follow me on Medium for regular updates on Snowflake Best Practices and other trending topics:
Also, I am open to connecting all data enthusiasts across the globe on LinkedIn:
