Snowflake recommends adhering to the following best practices when crafting the setup script for an application:
When employing the CREATE command to generate objects within the setup script, it is advisable to use the CREATE OR REPLACE or CREATE IF NOT EXISTS variations of the commands. The setup script might be executed multiple times during installation, upgrades, or error recovery scenarios, such as in cases where a versioned schema may not be empty.
sql
Copy code
-- Example of creating a schema within the setup script
CREATE SCHEMA IF NOT EXISTS app_config;
CREATE TABLE IF NOT EXISTS app_config.params(...);
As the CREATE SCHEMA command doesn't alter the session context, objects must be qualified with the target schema when created. Considerations should be given to potential failures during the setup script execution. Since the script is designed to be idempotent, it automatically reruns if the initial execution encounters an error.
Take into account the implications when granting permissions to application roles within a schema. Utilize the CREATE OR REPLACE statement carefully, especially with procedures, as it replaces the procedure, implicitly removing prior grants. Although grants are restored later in the script, a script failure during execution might temporarily revoke consumer access to the procedure.
sql
Copy code
-- Example of creating or replacing a procedure and granting usage to an application role
CREATE OR REPLACE PROCEDURE app_state.proc()...;
-- Additional CREATE statements
GRANT USAGE ON PROCEDURE app_state.proc()
TO APPLICATION ROLE app_user;
Views on shared content should always be defined in a versioned schema. This ensures that any code accessing the view during an upgrade maintains a consistent view, even if new columns or other attributes are added or removed.
If the setup script necessitates prolonged operations, such as upgrading large state tables, ensure that these updates are compatible with existing running code from the previous version.
These best practices help optimize the robustness and reliability of the setup script throughout the application's lifecycle.
