What you will be able to do
- Choose between CREATE OR REPLACE, CREATE IF NOT EXISTS and CREATE OR ALTER for each object in a setup script so it can safely run again
- Change a stateful table's schema without losing consumer rows, grants or application roles
- Plan versions and patches so each version is compatible with the one before it (n/n-1)
- Protect consumer state while old and new code run together, or when an upgrade fails
Key concept
Idempotent setup script — A setup script that reaches the same correct end state however many times it runs, whether on a fresh install, an upgrade, or a retry after a failure partway through. Every statement has to cope with an object that already exists, a column already added, or a row already inserted.
1.Why a setup script must survive being run again
Every installation and every upgrade of a Native App runs the setup script for the target version or patch. In production that script is not running against an empty application. It runs inside thousands of consumer accounts that already hold tables full of consumer data, application roles granted to consumer roles, and code from the previous version that may still be running. Maintaining a production app mostly means writing that script so it does no harm, whatever state it finds.
Retries make this harder. If the setup script fails during installation, Snowflake reruns it from the beginning, not from the statement that failed. Everything before the failure has already happened. A versioned schema created for this version is neither recreated nor deleted, so the objects in it may already exist when the second run reaches them. A statement that only works on its first run, such as a plain CREATE of an object that now exists or an INSERT that adds the same seed row again, turns one transient failure into an error or into duplicate data.
The rule that follows is idempotence: every CREATE, ALTER and INSERT must be safe to run more than once with no errors and no unintended side effects. The rest of this page applies that rule to code objects, to stateful tables, and to the window when two versions of the app are live at the same time.
Checkpoint 1 of 9· Check yourself
A Native App's setup script seeds a config table with a plain INSERT early on. During installation, a statement near the end of the script fails with a transient error. What happens when Snowflake retries?
A failed setup script is rerun from the beginning, not from the failed statement. The earlier run is not undone, and a versioned schema already created for this version is neither recreated nor deleted. Any statement that only works the first time, such as a plain INSERT of a seed row, causes an error or duplicate data on the retry.
“If the setup script fails during installation, Snowflake reruns the setup script from the beginning.”Source: docs.snowflake.com
2.Choosing CREATE OR REPLACE, CREATE IF NOT EXISTS or CREATE OR ALTER
Snowflake recommends particular CREATE forms for setup scripts: CREATE OR REPLACE and CREATE IF NOT EXISTS, plus CREATE OR ALTER for stateful objects that support it. Each one handles an existing object differently, and the correct choice depends on whether the object holds state. Destructive statements such as DROP TABLE CASCADE or TRUNCATE TABLE are not on this list, because they discard the very rows a stateful object exists to keep.
| Form | Use it for | Effect on an existing object |
|---|---|---|
| CREATE OR REPLACE | Stateless objects (procedures, functions, Streamlit apps) in a versioned schema | Recreates the object with the new definition, which also removes privileges previously granted on it |
| CREATE IF NOT EXISTS | Stateful objects that don't support CREATE OR ALTER, followed by idempotent ALTER statements | Leaves the object untouched: its definition, grants and rows stay as they were |
| CREATE OR ALTER | Stateful objects that support it, stating the complete target state | Moves the object to the stated definition; omitted properties can be reset, and omitted columns and their data can be dropped |
Code needs CREATE OR REPLACE because CREATE IF NOT EXISTS never changes an object that is already there. If a procedure was created with IF NOT EXISTS, a rerun that finds it keeps the old body, so a bug fix in the new script is silently ignored. Use IF NOT EXISTS for things whose contents you want to keep, such as a configuration table. Use OR REPLACE for things you want to overwrite, such as procedure logic. CREATE OR ALTER is useful but comes with a warning: because it describes the complete target state, a column you leave out of the statement can be dropped along with its data.
CREATE OR REPLACE PROCEDURE app_state.proc()...;
GRANT USAGE ON PROCEDURE app_state.proc()
TO APPLICATION ROLE app_user;The fix is to keep CREATE OR REPLACE and put the GRANT immediately after every place the object is defined. That way, no failure point leaves the replaced object without its grant for longer than one statement, and every rerun issues the grant again. Removing the GRANT does not work, because privileges do not survive a replace. Switching to IF NOT EXISTS does not work either, because it freezes the procedure body. The cost of getting this wrong is high. If the failure cannot be fixed by retrying, such as a syntax error, consumers lose access to the procedure until a new version or patch restores the grant.
Checkpoint 2 of 9· Match them up
An upgrade's setup script has to handle objects that already exist. Match each CREATE form to the object it suits and to what it does when the object already exists.
Tap a term, then the definition that fits it.
Stateless code uses CREATE OR REPLACE so that new logic replaces the old, which means its GRANT must be issued again. Stateful objects use IF NOT EXISTS plus idempotent ALTERs to keep their contents. CREATE OR ALTER moves an object to the full stated definition, so anything left out of the statement can be lost.
“For stateful objects that don’t support CREATE OR ALTER, use CREATE IF NOT EXISTS followed by idempotent ALTER statements.”Source: docs.snowflake.com
Checkpoint 3 of 9· Exam question
A provider's setup script creates a stored procedure using CREATE OR REPLACE PROCEDURE. During a customer's upgrade, the script fails after this statement runs but before granting usage to the application role, and Snowflake reruns the script from the beginning. The provider wants to guarantee the application role never loses access to the procedure across any rerun. Which approach should the provider use?
Correct answer: A — Keep CREATE OR REPLACE PROCEDURE and re-issue the GRANT USAGE statement immediately after every occurrence of the procedure definition in the setup script
- A. CREATE OR REPLACE drops and recreates the object, clearing previously granted privileges, so re-issuing the grant immediately after every recreate guarantees the application role's access survives any rerun.
- B. Never modifying the procedure body across upgrades defeats the purpose of shipping updated logic in a new version, so this is the wrong tool even though it avoids the grant issue.
- C. Privileges do not persist automatically through a recreate; the underlying object identity changes, so previously granted privileges are lost and must be reapplied.
- D. Setup scripts do not support wrapping statements in a manual transaction to preserve grants this way; grants must be explicitly re-issued after each recreate.
Checkpoint 4 of 9· Exam question
Which statements accurately describe the difference between CREATE OR REPLACE and CREATE IF NOT EXISTS when used in a Native App setup script? (Select all that apply)(Select 3)
Correct answers: A, B, E — CREATE OR REPLACE recreates the object every run, discarding previously granted privileges; CREATE IF NOT EXISTS leaves an already-existing object untouched, preserving its current privileges and data; Using CREATE OR REPLACE on a procedure requires re-granting privileges afterward to restore consumer access
- A. Correct: recreating the object each run means any privileges granted on the prior instance of the object are lost and must be reapplied.
- B. Correct: because the object is skipped when it already exists, its current grants and data remain intact across reruns.
- C. Incorrect: Snowflake also recommends CREATE IF NOT EXISTS and CREATE OR ALTER as idempotent forms, so CREATE OR REPLACE is not the only option.
- D. Incorrect: because the statement is skipped entirely when the object exists, any changes to the body in a new version are never applied through this form.
- E. Correct: since CREATE OR REPLACE recreates the object and clears prior grants, the setup script must re-grant privileges to restore consumer access.
Checkpoint 5 of 9· Exam question
Which of the following are command forms Snowflake recommends for writing idempotent setup script statements that can run safely across multiple installation and upgrade attempts? (Select all that apply)(Select 3)
Correct answers: A, B, C — CREATE OR REPLACE; CREATE IF NOT EXISTS; CREATE OR ALTER
- A. Correct: recreating the object each run is one of the recommended idempotent forms, provided grants are reapplied afterward.
- B. Correct: skipping creation when the object already exists is a recommended idempotent form for objects that should not change.
- C. Correct: modifying an existing object in place while preserving it is a recommended idempotent form, notably for versioned schemas.
- D. Incorrect: dropping objects with cascade in a setup script is destructive and not part of Snowflake's idempotency recommendations.
- E. Incorrect: truncating data on every run would destroy state each time the script executes and is not an idempotency recommendation.
- F. Incorrect: this form recreates a table from a query result each run, which is not among the recommended idempotent create forms for setup scripts.
3.Changing stateful tables without losing rows, grants or roles
Stateful objects, such as a configuration table or data the app has collected, have to keep their contents across every version. These objects live in a regular schema, so the setup script must create them on a fresh install and modify them on an upgrade, all with the same statements. The pattern is CREATE ... IF NOT EXISTS with the full current definition, followed by ALTER ... IF NOT EXISTS for each change made since earlier versions.
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;On a new install, the CREATE builds the table with modified_on already in it, and the ALTER does nothing. On an upgrade from a version without that column, the CREATE does nothing and the ALTER adds the column. Existing rows are kept in both cases. Because the CREATE schema statement does not change the session context, every object name is schema-qualified.
Each version's script must also be self-contained. A consumer might install v3 directly, without ever running v2. So if v2 created a table and v3 alters it, the v3 script needs both the CREATE TABLE and the ALTER TABLE. Taking the ALTER from v3 and leaving the CREATE in v2 breaks every fresh install.
Two more kinds of state are easy to damage on a retry:
- Rows. Seed or reference rows inserted by the script are inserted again when an upgrade is retried, so guard each INSERT against duplicates.
- Application roles. Application roles are not versioned, so dropping one, or revoking a grant between versions, can break the app or lock consumers out. Use CREATE APPLICATION ROLE IF NOT EXISTS and never CREATE OR REPLACE APPLICATION ROLE. Replacing the role drops it, and every account-level role that consumers had granted to it would then need to be granted again.
Because the grants sit on different sides. The provider's own script issues the procedure's GRANT and can issue it again straight after the replace. The grants that consumers made of account-level roles to the application role are not in the script at all, so a replaced role loses them and only the consumer can restore them.
Checkpoint 6 of 9· Check yourself
A provider is reviewing the v3 setup script of a Native App before release. Which of these changes would harm consumers who upgrade?
Application roles are not versioned. OR REPLACE drops the role and creates it again, so every account-level role that consumers granted to it is lost and has to be granted again. The other three changes are correct practice: a self-contained CREATE plus an idempotent ALTER, an INSERT guarded against duplicates, and IF NOT EXISTS for the role.
“Avoid using CREATE OR REPLACE APPLICATION ROLE. Instead, use CREATE APPLICATION ROLE IF NOT EXISTS.”Source: docs.snowflake.com
Sources1
4.Planning upgrade paths: n/n-1 compatibility for versions and patches
Each release channel holds at most two versions at a time, so no more than two versions are ever live together (Version assignment constraints (two live versions)). That limit sets the compatibility contract. Each new version must work with the state and the running code of the version immediately before it. With v1 and v2 live, v2 must be compatible with v1. When v3 arrives, it must be compatible with v2, but it owes v1 nothing. Snowflake makes sure that only v2 is installed in a consumer account before it upgrades that account to v3.
The two-version limit also decides when an old version can be retired. Removing a version from a channel requires every consumer to have upgraded off it, and all running code from that version to have finished, in every consumer account. A single failed upgrade anywhere blocks the removal until the provider fixes the cause. Plan for this: v3 cannot be added to a channel that already holds v1 and v2 until v1 is fully drained.
Patches follow a stricter rule because they get no such guard. Snowflake does not limit how many patches of a version can be active, so patch 0, patch 3 and patch 5 of one version may all be running in different accounts at once. Tables and columns therefore have to stay compatible across all of them. In practice, put state changes (new tables, added or altered columns) in a new version, and keep patches for bug fixes and small features that do not change state.
| Aspect | Version | Patch |
|---|---|---|
| How many can be live | Two per release channel | No limit on active patches |
| Must be compatible with | The immediately previous version only | Every other patch of the same version |
| State changes (tables, columns) | Allowed, and this is where they belong | Avoid; limit to bug fixes and minor features |
Checkpoint 7 of 9· Check yourself
Your app has v1 and v2 live in its DEFAULT release channel, and a few consumer accounts are still on v1. The next release adds a column to a state table. Which release plan is correct?
State changes belong in a new version, not in a patch, because any number of patches can be active at once. A channel holds only two versions, so v1 has to be fully drained before v3 can be added. After that, v3 only has to be compatible with v2.
“because only two versions of an app can exist at one time, version v3 does not have to be compatible with version v1.”Source: docs.snowflake.com
5.Protecting consumer state while old and new code overlap
An upgrade does not switch every query over at one instant. While it runs, code from the old version and code from the new version can both be executing (How an app upgrade happens), and safeguards are needed for that window. The main safeguard is the split between Versioned schemas vs regular schemas. Code objects in a versioned schema are replaced for each version, while tables in a regular schema persist. That lets each version use its own code against the same consumer data. Version pinning completes the picture: a long-running query started by v1 keeps using the v1 objects until it finishes, even after v2 has become current.
Pinning protects code, not shared data, so three practices cover the rest:
- Views on shared content go in a versioned schema. Code that reads a view during the upgrade then gets a consistent view for its own version, and column additions or removals are handled the same way.
- Long state migrations stay compatible with old code. If the script has to upgrade a large state table, v1 code that is still running must keep working against the table while it changes.
- Versioned schemas use CREATE OR ALTER VERSIONED SCHEMA, which keeps them compatible across versions and patches. They are never dropped, because a drop would remove every version's objects, including the ones that running queries depend on.
A failed upgrade is meant to leave the consumer exactly where they were. If the setup script or the target version initializer fails, Snowflake does not activate the target version's objects in versioned schemas, and the previous version stays active. That guarantee covers only versioned objects, which is one more reason a half-run script must not have damaged anything in a regular schema.
Services are the exception to this rollback, because they live outside versioned schemas (Snowpark Container Services in Native Apps (compute pools, services, endpoints)). To keep services in step with the app version, the setup script runs CREATE SERVICE or ALTER SERVICE with the new version's specification file. That upgrade starts as soon as the command is accepted and runs asynchronously. If a later step then fails, the service may already be on the new specification while the app has stayed on the old version. Two tools close the gap. SYSTEM$WAIT_FOR_SERVICES pauses the script until the named services are READY, one of them FAILED, or a timeout passes. A version initializer kept in a versioned schema handles the failure case: when the new version fails, the previous version's initializer is called and can run ALTER SERVICE to revert the services.
SELECT SYSTEM$WAIT_FOR_SERVICES(600, 'services.web_ui', 'services.worker', 'services.aggregation');Checkpoint 8 of 9· Fill the gap
The setup script has just run ALTER SERVICE for three services. You want the script to hold until every service is READY, one of them is FAILED, or 600 seconds pass. Which function completes the statement?
SELECT ? (600, 'services.web_ui', 'services.worker', 'services.aggregation');SYSTEM$WAIT_FOR_SERVICES pauses the setup script until the named services are READY, one of them is FAILED, or the timeout runs out. This matters because CREATE SERVICE and ALTER SERVICE run asynchronously. A fixed sleep or a one-time status read does not wait on the services' actual state.
Source: docs.snowflake.comCheckpoint 9 of 9· Check yourself
During an upgrade from v1 to v2, the setup script runs ALTER SERVICE with the v2 specification file. A later statement in the script then fails. What state is the consumer's app most likely in?
A failed upgrade leaves the previous version's objects in versioned schemas active. Services sit outside versioned schemas, though, and start upgrading as soon as the command is accepted. That is why the previous version's initializer, kept in a versioned schema, is used to revert them.
“the service might already use the specification for the target app version while the previous app version remains active.”Source: docs.snowflake.com
Sources
(5)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“If the setup script fails during installation, Snowflake reruns the setup script from the beginning.”
↩︎ Why a setup script must survive being run again“If a versioned schema has already been created for this version it is not recreated or deleted.”
↩︎ Why a setup script must survive being run again“use CREATE IF NOT EXISTS followed by idempotent ALTER statements.”
↩︎ Choosing CREATE OR REPLACE, CREATE IF NOT EXISTS or CREATE OR ALTER“Omitted properties can be reset, and omitted table columns and their data can be dropped.”
↩︎ Choosing CREATE OR REPLACE, CREATE IF NOT EXISTS or CREATE OR ALTER“Use ALTER TABLE ADD COLUMN IF NOT EXISTS to ensure columns are added only if they do not already exist.”
↩︎ Changing stateful tables without losing rows, grants or roles“Each version of the app must be complete and independent.”
↩︎ Changing stateful tables without losing rows, grants or roles“When inserting rows, implement safeguards to prevent duplicate rows if unintended, as upgrades may be retried multiple times.”
↩︎ Changing stateful tables without losing rows, grants or roles“Application roles are not versioned.”
↩︎ Changing stateful tables without losing rows, grants or roles“if an app has versions v1 and v2, v2 must be compatible with v1.”
↩︎ Planning upgrade paths: n/n-1 compatibility for versions and patches“State changes should only be made when updating the version of an app.”
↩︎ Planning upgrade paths: n/n-1 compatibility for versions and patches“The previous version remains active.”
↩︎ Protecting consumer state while old and new code overlap“If a later part of the app upgrade fails, the service might already use the specification for the target app version”
↩︎ Protecting consumer state while old and new code overlap“For stateless objects, use CREATE OR REPLACE.”
↩︎ Exam trap 1“Patches should focus on bug fixes or minor feature additions without involving state modifications.”
↩︎ Exam trap 2“because only two versions of an app can exist at one time, version v3 does not have to be compatible with version v1.”
↩︎ Exam trap 3“Avoid using CREATE OR REPLACE APPLICATION ROLE. Instead, use CREATE APPLICATION ROLE IF NOT EXISTS.”
↩︎ Exam trap 4“For stateful objects that don’t support CREATE OR ALTER, use CREATE IF NOT EXISTS followed by idempotent ALTER statements.”
↩︎ Checkpoint“Avoid using CREATE OR REPLACE APPLICATION ROLE. Instead, use CREATE APPLICATION ROLE IF NOT EXISTS.”
↩︎ Checkpoint“because only two versions of an app can exist at one time, version v3 does not have to be compatible with version v1.”
↩︎ Checkpoint“the service might already use the specification for the target app version while the previous app version remains active.”
↩︎ Checkpoint - 2.
“The setup script can be run multiple times during installation and upgrade.”
↩︎ Why a setup script must survive being run again“the CREATE OR REPLACE statement replaces an existing procedure, which implicitly removes privileges that had been previously granted to that procedure.”
↩︎ Choosing CREATE OR REPLACE, CREATE IF NOT EXISTS or CREATE OR ALTER“the consumer cannot access the procedure until the app is upgraded to a new version or patch and the grant is restored.”
↩︎ Choosing CREATE OR REPLACE, CREATE IF NOT EXISTS or CREATE OR ALTER“Always define views on shared content in a versioned schema”
↩︎ Protecting consumer state while old and new code overlap“ensure that these updates are compatible with existing running code from the previous version.”
↩︎ Protecting consumer state while old and new code overlap“The setup script can be run multiple times during installation and upgrade.”
↩︎ Key concept - 3.
“Snowflake ensures that only v2 is currently installed in a consumer account before upgrading to v3.”
↩︎ Planning upgrade paths: n/n-1 compatibility for versions and patches“Providers cannot remove the previous version of the app from the release channel until all running code from the previous version has completed.”
↩︎ Planning upgrade paths: n/n-1 compatibility for versions and patches“the Snowflake Native App Framework does not enforce any restrictions on the number of active patches running.”
↩︎ Planning upgrade paths: n/n-1 compatibility for versions and patches - 4.
“The query continues to use the V1 objects until it finishes.”
↩︎ Protecting consumer state while old and new code overlap“You should always include the CREATE OR ALTER version of this command to ensure that versioned schemas are compatible across versions and patches.”
↩︎ Protecting consumer state while old and new code overlap - 5.https://docs.snowflake.com/en/developer-guide/native-apps/tutorials/na-upgrade-tutorialOfficial docs
“If the setup script or the version initializer of the new version fails, then the version initializer of the previous version is called.”
↩︎ Protecting consumer state while old and new code overlap