liveBook · Manning

Execute immediate from

As explained in Snowflake Data Engineering

EXECUTE IMMEDIATE FROM is a Snowflake SQL command that executes SQL statements from a referenced script file.

EXECUTE IMMEDIATE FROM runs a SQL script stored in a Snowflake stage, including a Git repository stage. It turns version-controlled SQL files into executable setup, deployment, and pipeline-management commands. section 15.3.2 section 15.7

Start by selecting the required role, database, and schema, then fetch the Git repository so the stage contains the latest files. Execute the staged file by passing its @stage path to EXECUTE IMMEDIATE FROM. The executing role needs the appropriate access; an RBAC setup script can grant Git-stage usage to a functional role. section 15.3.2

use role SYSADMIN;
use database ADMIN_DB;
use schema GIT_INTEGRATION;

alter git repository SF_DE fetch;

execute immediate from @SF_DE/branches/main/Chapter_15/Chapter_15_Part2_RBAC.sql;

This baseline fetches the repository and runs an RBAC script from its main branch. section 15.3.2

During development, Snowflake CLI can run a local SQL file with snow sql -f. After review, commit, and merge, use snow sql -q to fetch the repository stage and send an EXECUTE IMMEDIATE FROM query that runs the committed file. This keeps local testing and staged execution aligned around the same scripts. section 15.4.2

Compose a deployment script

A deployment script can call many object-creation scripts with EXECUTE IMMEDIATE FROM. Paths inside that script are relative to the deployment script’s location, allowing one staged command to create the database objects. Keep task activation separate when validation should not start pipelines: execute dedicated resume_tasks.sql or suspend_tasks.sql scripts when needed. section 15.4.3

Listing 15.3 The first few lines of the script that calls other scripts to create all objects
use database BAKERY_DB;
-- EXT schema
execute immediate from '../Snowflake_objects/schemas/ext/stages/create_JSON_ORDERS_STAGE.sql';
execute immediate from '../Snowflake_objects/schemas/ext/tables/create_JSON_ORDERS_EXT.sql';
execute immediate from '../Snowflake_objects/schemas/ext/streams/create_JSON_ORDERS_STREAM.sql';

The listing shows a deployment script selecting a database and calling stage, table, and stream creation scripts through relative paths. section 15.4.3

Use the command at two useful levels:

  • Run one script, such as an RBAC setup or a table-creation file, after fetching the Git repository stage. section 15.3.2 section 15.4.2
  • Run a top-level deployment script that calls individual object scripts, then explicitly resume tasks only when pipeline execution is desired. section 15.4.3

How Snowflake Data Engineering covers it

How deep it goes
Demonstrates fetch-and-execute commands, Snowflake CLI invocation, relative nested paths, and separate task-resume control.
Examples
Uses RBAC setup, JSON_ORDERS_TBL_STG creation, deploy_objects.sql, and resume or suspend task scripts.
What it assumes
Assumes a configured Git repository stage and suitable role, database, schema, and object permissions.
What you can do after
Operate/configure it to fetch staged Git files, execute individual or composed SQL deployments, and control task activation separately.
How it connects
Connects Git repository stages, Snowflake CLI, role-based access control, database change management, and Snowflake tasks.
Cover of Snowflake Data Engineering
liveBookmanning.com
Read Snowflake Data Engineering on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Snowflake Data Engineering. The section links point to the parts of the book it draws on.