Execute Immediate From Stage

Read Time:2 Minute, 42 Second

During this post we are going to discuss EXECUTE IMMEDIATE FROM SQL command which trigger directly from files stored in an internal stage. We can have one or multiple sql files consist of valid Snowflake SQL commands. We can upload these files to the internal staging area and can execute it via running EXECUTE IMMEDIATE FROM << SQL file path >>.

This feature empowers users to effectively manage their Snowflake objects and code deployment. For instance, it enables the execution of a stored script to establish a standardized Snowflake environment across multiple accounts. In practical terms, this means the script could contain instructions to create users, roles, databases, and schemas, ensuring uniformity and efficiency across all new accounts.

Syntax:

EXECUTE IMMEDIATE FROM { absoluteFilePath | relativeFilePath }

The outcome of the EXECUTE IMMEDIATE FROM command includes:

The output of the final statement in the file if all statements are executed successfully.
If any statement in the file fails, it returns the corresponding error message.
In case of any error within a statement, the entire EXECUTE IMMEDIATE FROM command fails and returns the error message of the specific failed statement.

Technical implementation:

1. SQL Files Details:

Rather than having one simpler SQL file, I have developed multiple sql files for respective functionality. Later on I have encapsulated inside the MASTER_SCRIPT.sql. So instead of running the files individually we would be running only master script and it will take care of all other files.

DB_WH_CREATE.sql: Responsible for creation of new database i.e. STAGE_DB and new WH i.e. STAGE_WH.
CREATE_ROLE_DB_OWNERSHIP.sql: Responsible for the creation of Roles and Grant the OWNERSHIP and other required privileges on DB and WH.
- Two Roles created COLLECTION_REP and COLLECTION_FLM.
- COLLECTION_REP is lower in hierarchy
- Grant OWNERSHIP on Database to COLLECTION_FLM Role.
- Grant Read only privileges on new WH to COLLECTION_REP.
CREATE_USER_GRANT_PRIV.sql:
- Creates the new users.
- Assign the Roles.
- Grant Future Privileges to the Role i.e. COLLECTION_REP, lower in hierarchy
- Using FLM as owner, Create a new Table i.e. INVOICE.
- To insert the data into table another INSERT_INVOICE.sql is called. This shows that we can call another sql file from the parent sql file

INSERT_INVOICE.sql: Called from above .sql file and responsible for insertion to the INVOICE table.
CREATE_PROCEDURE.sql: This will create the procedure inside the STAGE_DB database via FLM Role.
MASTER_SCRIPT.sql : Responsible for binding the above sql file and execute in certain sequence order.

2. Create Stage and upload the files: Create SCRIPT_STAGE stage inside the DEMO_DB and upload all the sql files to the scripts folder.