Idempotent Setup Scripts and Schema Changes for Native App Upgrades

1.Why the setup script must survive being run again

A Snowflake Native App gets its objects from one file: the setup script named in the manifest. The script runs when a consumer installs or upgrades the app, and when a provider creates or upgrades the app to test the application package. It only supports SQL, and it cannot run USE DATABASE, USE SCHEMA, USE ROLE or USE SECONDARY ROLES. Because CREATE SCHEMA does not change the session context either, you have to qualify every object with its target schema.

The key fact for upgrades is that the script can run several times. Snowflake's best-practice guidance says it can be run multiple times during installation and upgrade, and that after an error some objects might already exist, especially ones in a versioned schema. A versioned schema that was already created for this version is not recreated or deleted on the retry. So the second run starts with leftovers from the first run, and the documentation tells providers to plan for the script running again automatically after an initial failure.

That is why Snowflake asks for idempotent changes only. CREATE and ALTER statements should be written so they can run multiple times without errors or unintended side effects. The rest of this lesson applies that rule to two decisions: where each object lives, and which form of CREATE builds it.

Sources12

2.Stateless code in versioned schemas, stateful data in regular schemas

Before writing a single CREATE, the provider has to decide whether each component keeps its state from one version or patch to the next. Stateless objects such as stored procedures, UDFs and Streamlit apps are recreated for every version or patch and only need to exist for that version's lifetime. Stateful objects, such as a table holding consumer configuration, must keep their contents through an upgrade. From the consumer's side, code is replaced on every upgrade and data is kept.

The framework provides a special schema type for stateless objects. A versioned schema works like a regular schema but can hold multiple versions of objects created by different app versions. Internally it contains one subschema per app version, but a consumer only sees the objects that match the version they have installed. Versioned schemas exist only inside an application object and can only be created by the setup script.

Always create a versioned schema with CREATE OR ALTER VERSIONED SCHEMA so that it stays compatible across versions and patches. Some things are not supported inside a versioned schema: Snowpark Container Services, tasks, tags, masking policies, grants and future grants, and clone operations. You also cannot drop one, because that would drop every version of its objects and affect queries still running on older versions. Application roles, tasks, tags, masking policies and services go in a normal schema.

CREATE OR ALTER  ?  SCHEMA stateless_object;
CREATE FUNCTION IF NOT EXISTS stateless_object.add(x int, y int)
  RETURNS INT
  LANGUAGE SQL
  AS $$ x + y $$;

Stateful tables are where schema changes happen. Snowflake's example creates a config table that persists across versions. On a first install, CREATE TABLE IF NOT EXISTS builds the table. On an upgrade from a version that had no modified_on column, the table already exists, so the ALTER adds the column. On a retry, both statements do nothing.

CREATE SCHEMA IF NOT EXISTS stateful_object;

CREATE TABLE IF NOT EXISTS stateful_object.config (
  config_param STRING,
  config_value STRING,
  default_value STRING,
  modified_on  TIMESTAMP);

ALTER TABLE stateful_object.config
  ADD COLUMN IF NOT EXISTS modified_on TIMESTAMP;

Sources34

3.Choosing CREATE variants without losing grants, roles or rows

Snowflake says to always use CREATE OR REPLACE, CREATE IF NOT EXISTS or CREATE OR ALTER, whichever fits, so that an upgrade does not fail on an object that already exists. These forms are not interchangeable. CREATE OR REPLACE is only recommended for stateless objects such as functions and procedures, never for stateful objects such as tables, because replacing a table would throw away the data the upgrade is supposed to keep.

CREATE OR REPLACE can hurt stateless objects too. Replacing a procedure silently removes the privileges previously granted on it. If the script fails between the CREATE OR REPLACE and the GRANT that restores access, consumers can lose access to the procedure. If the failure can't be fixed by retrying, for example a syntax error, access stays broken until a new version or patch restores the grant.

CREATE OR REPLACE PROCEDURE app_state.proc()...;
GRANT USAGE ON PROCEDURE app_state.proc()
  TO APPLICATION ROLE app_user;

Application roles need even more care, because they are not versioned. Dropping a role, or revoking a grant between versions, can stop the app working or lock consumers out. Use CREATE APPLICATION ROLE IF NOT EXISTS, not CREATE OR REPLACE APPLICATION ROLE. The OR REPLACE form drops and recreates the role, and any account-level roles granted to it in earlier versions would then have to be granted again.

Data written by the script needs its own safeguard. An upgrade can be retried many times, so an INSERT that seeds rows has to guard against duplicates. Finally, each version's script must be self-contained. If v2.0 created a table and v3.0 alters it, v3.0 must contain both the CREATE TABLE and the ALTER TABLE, so a consumer who installs v3.0 directly still gets every object.

Sources12

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.CREATE OR REPLACE is the safest idempotent choice for every object in a setup script.Why is that wrong?

   It is only recommended for stateless objects. On tables it destroys preserved data, on procedures it drops existing grants, and on application roles it forces account-level grants to be redone.

   Covered in Choosing CREATE variants without losing grants, roles or rows

2. 2.Putting a configuration table in a versioned schema protects its data during upgrades.Why is that wrong?

   Versioned schemas are for stateless objects that get recreated every version. Data that must survive an upgrade goes in a regular schema.

   Covered in Stateless code in versioned schemas, stateful data in regular schemas