Streamlining Object/Code Deployment in Snowflake with EXECUTE IMMEDIATE FROM

Imagine you’re in the world of data, working with Snowflake, the data cloud platform. Have you ever wished for a seamless way to keep your code organized within Snowflake and effortlessly deploy objects using Snowflake scripts, all while connecting to a specific stage location? Well, here’s some exciting news: Snowflake’s newest addition, EXECUTE IMMEDIATE FROM @<STAGE_FILE>, is about to make your dreams come true.

Understanding “EXECUTE IMMEDIATE FROM”

The EXECUTE IMMEDIATE FROM feature allows you to execute SQL statements specified in a file stored in a Snowflake stage. This file can contain SQL statements or even Snowflake Scripting blocks. Essentially, it provides a way to control the deployment and management of your Snowflake objects and code.

When you use EXECUTE IMMEDIATE FROM, it returns the result of the last statement in the file if all statements are executed successfully. However, if any statement in the file encounters an error, the entire EXECUTE IMMEDIATE FROM command fails and returns the error message of the failed statement.

Usage Tips:

Nested Execution: You can include EXECUTE IMMEDIATE FROM statements within other files, but there’s a limit. The maximum nested file execution depth is 100.

Relative Paths: If you use EXECUTE IMMEDIATE FROM statements within another file, you can specify relative file paths. These paths are evaluated with respect to the stage and file path of the parent file. If the relative file path starts with “/”, it begins at the root directory of the stage where the parent file is located.

File Size: The file you want to execute cannot exceed 10MB in size, and it must be uncompressed.

Currently, for Snowflake accounts in Google Cloud Platform (GCP) or Microsoft Azure regions

The file to be executed must be encoded in UTF-8 and must be uncompressed

Ready to try it out? Here’s a simplified hands-on approach:

Craft the Main Script: Start by creating a main script that will coordinate everything

-- file name MAIN_SCRIPT.sql

CREATE SCHEMA IF NOT EXISTS SF.STG_SCRIPTS_EXEC;
-- SCRIPT TO EXECUTE CREATE TABLES
EXECUTE IMMEDIATE FROM @F_STORAGE/SCRIPTS/CREATE.sql
-- SCRIPT TO CREATE STORED PROCEDURES
EXECUTE IMMEDIATE FROM @F_STORAGE/SCRIPTS/SP.sql

Create Other Scripts: Develop the individual scripts you want to execute as part of the main script

-- filename CREATE.sql
create or replace TABLE STG_SCRIPTS_EXEC.USER (
 ROLE VARCHAR(16777216),
 NAME VARCHAR(16777216),
 CREATED_ON TIMESTAMP_LTZ(6),
 DELETED_ON TIMESTAMP_LTZ(6)
);    

--Table USER successfully created.

--filename SP.sql
CREATE OR REPLACE PROCEDURE STG_SCRIPTS_EXEC.SP()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
 var c_status = `STORED PROC RAN SUCCESSFULLY`;
 return c_status;
$$
;

--Function SP successfully created.

Stage and Upload: Set up a Snowflake stage and upload your script files to it.

CREATE STAGE IF NOT EXISTS  F_STORAGE
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE'); 

--Stage area F_STORAGE successfully created.

Execute: Run a Snowflake query that includes the EXECUTE IMMEDIATE FROM @<STAGE_FILE> command.

EXECUTE IMMEDIATE from @F_STORAGE/SCRIPTS/MAIN_SCRIPT.sql

Once the master script runs, it will return the output of the last query executed as part of the script.

With EXECUTE IMMEDIATE FROM, you have a powerful tool for managing and deploying code efficiently. It simplifies the process of executing SQL statements and Scripting blocks stored in stage files, making your Snowflake workflow even more streamlined. Give it a try and see how it can benefit your Snowflake projects!

:-)