CertSafari
    Snowflake SnowPro Specialty: Native Apps· Lessons

    Domain 4 · Lesson 12/12

    Idempotent setup scripts and safe upgrades for Native Apps

    Maintain production applications.

    18 min read
    9.67% of exam
    5 sources
    Published 21 Oct 2026
    Docs as of 18 Oct 2026

    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?

    Sources12

    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.

    What each recommended CREATE form does to an object that already exists
    FormUse it forEffect on an existing object
    CREATE OR REPLACEStateless objects (procedures, functions, Streamlit apps) in a versioned schemaRecreates the object with the new definition, which also removes privileges previously granted on it
    CREATE IF NOT EXISTSStateful objects that don't support CREATE OR ALTER, followed by idempotent ALTER statementsLeaves the object untouched: its definition, grants and rows stay as they were
    CREATE OR ALTERStateful objects that support it, stating the complete target stateMoves 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.

    A replaced procedure loses its grants, so the GRANT has to come right after the CREATEsql
    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.

    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?

    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)

    0 of 3 selected

    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)

    0 of 3 selected

    Sources21

    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.

    One script that installs the table fresh or adds the missing column on upgradesql
    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.

    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?

    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.

    Versions and patches as upgrade units
    AspectVersionPatch
    How many can be liveTwo per release channelNo limit on active patches
    Must be compatible withThe immediately previous version onlyEvery other patch of the same version
    State changes (tables, columns)Allowed, and this is where they belongAvoid; 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?

    Sources13

    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.

    Hold the setup script until the upgraded services are ready, have failed, or 600 seconds passsql
    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');

    Checkpoint 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?

    Sources4215

    Sources

    (5)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 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
      “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. 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. 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. 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. 5.
      “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

    Ready to test yourself?

    Practise the 35 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.