1. Understanding Stored Procedures in Snowflake

A stored procedure is a collection of SQL statements that execute in a defined sequence. These statements can include INSERT, DELETE, UPDATE, SELECT, and other SQL operations. Stored procedures help automate and simplify repetitive database tasks.

Why Use Stored Procedures?

Stored procedures are useful when:

1. Executing Multiple SQL Statements Together

1. 📌 Example: Processing salary calculations in payroll

   A payroll system needs to update employee salaries, apply bonuses, and deduct taxes in a single execution.

    BEGIN;  -- Start transaction
   
    UPDATE employees 
    SET salary = salary * 1.05  -- Apply a 5% raise to all employees
    WHERE department = 'Sales';
   
    UPDATE employees 
    SET bonus = 1000  -- Give a $1000 bonus to managers
    WHERE role = 'Manager';
   
    UPDATE employees 
    SET salary = salary - (salary * 0.10)  -- Deduct 10% tax
    WHERE salary > 50000;
   
    COMMIT;  -- Save all changes
   

   Why? Ensures all updates are applied together, preventing partial updates if an error occurs.

   ---

   2. Dynamically Generating SQL Statements

   📌 Example: Creating queries based on user input

   A reporting system generates an SQL query based on a user-selected department.

    DECLARE @department NVARCHAR(50) = 'Sales';  
   
    DECLARE @sql NVARCHAR(MAX);  
    SET @sql = 'SELECT * FROM employees WHERE department = ''' + @department + '''';  
   
    EXEC sp_executesql @sql;
   

   Why? Helps build flexible queries dynamically based on user input.

   ---

   3. Running SQL Statements Conditionally

   📌 Example: Checking order status before updating shipping details

   Before updating an order’s shipping details, check if the order is paid.

    IF EXISTS (SELECT 1 FROM orders WHERE order_id = 101 AND status = 'Paid')
    BEGIN  
        UPDATE orders  
        SET shipping_status = 'Shipped', shipped_date = GETDATE()  
        WHERE order_id = 101;  
    END  
    ELSE  
    BEGIN  
        PRINT 'Order is not paid yet. Cannot update shipping details.'  
    END;
   

   Why? Prevents changes to orders that are not yet paid.

   ---

   4. Running SQL Statements in Loops

   📌 Example: Processing multiple records in batches

   Processing customer records in batches of 100 to avoid overloading the system.

    DECLARE @batchSize INT = 100;  
    DECLARE @offset INT = 0;  
   
    WHILE @offset < (SELECT COUNT(*) FROM customers)  
    BEGIN  
        UPDATE customers  
        SET processed = 1  
        WHERE id IN (SELECT id FROM customers ORDER BY id OFFSET @offset ROWS FETCH NEXT @batchSize ROWS ONLY);  
   
        SET @offset = @offset + @batchSize;  
    END;
   

   Why? Helps process large datasets efficiently without locking the entire table.

---

Stored Procedures in Snowflake

Unlike traditional databases, Snowflake allows stored procedures to be written in SQL, JavaScript, Scala, Java, and Python.

Snowflake Scripting

When stored procedures are written entirely in SQL, they are known as Snowflake Scripting.

---

Creating a Stored Procedure in Snowflake

Stored procedures are created using the CREATE PROCEDURE statement.

Basic Syntax:

CREATE OR REPLACE PROCEDURE procedure_name(parameters)
RETURNS return_type
LANGUAGE SQL 
EXECUTE AS CALLER
AS
$$
DECLARE variable_name DATA_TYPE;
BEGIN
   -- SQL statements go here
END;
$$;

Example 1: Basic Stored Procedure

Let’s create a stored procedure to return the total number of rows in a given table.

CREATE OR REPLACE PROCEDURE get_row_count(tab_name STRING)
RETURNS INTEGER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE row_count INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR SELECT COUNT(*) AS count FROM IDENTIFIER(:tab_name);
BEGIN
    FOR row_variable IN c1 DO
        row_count := row_variable.count;
    END FOR;
    RETURN row_count;
END;
$$;

Explanation:

• We declare a cursor (c1) to fetch the count of rows from the given table.

• We loop through the cursor and assign the count to row_count.

• The final value is returned as output.

---

Executing a Stored Procedure

Stored procedures are executed using the CALL statement.

Syntax:

CALL procedure_name(parameters);

Example 2: Executing the Procedure

CALL get_row_count('employees');

Output: The procedure returns the total number of rows in the employees table.

---

Stored Procedure Structure

A stored procedure in Snowflake consists of four main sections:

1. Create Section

Defines the procedure’s name, parameters, return type, and language.

CREATE OR REPLACE PROCEDURE procedure_name(param1 DATATYPE, param2 DATATYPE)
RETURNS DATATYPE
LANGUAGE SQL
EXECUTE AS CALLER

2. Declaration Section

Declares variables, cursors, result sets, and exceptions.

DECLARE emp_id INTEGER;
DECLARE emp_name STRING;
DECLARE cur CURSOR FOR SELECT id, name FROM employees;

3. Body Section

Contains the logic, SQL statements, and loops.

BEGIN
   FOR record IN cur DO
      INSERT INTO processed_employees VALUES (record.id, record.name);
   END FOR;
END;

4. Exception Handling

Handles errors and exceptions.

EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RETURN SQLERRM;
    WHEN OTHER ERROR THEN
        RETURN SQLERRM;

---

Dropping a Stored Procedure

To delete a stored procedure, use the DROP PROCEDURE command.

Syntax:

DROP PROCEDURE procedure_name(parameters);

Example 3: Dropping a Procedure

DROP PROCEDURE get_row_count(STRING);

Note: The parameter data type must match the procedure definition.

---

Advanced Concepts in Snowflake Stored Procedures

1. Using Loops in Procedures

Loops allow repetitive execution of statements.

BEGIN
   FOR rec IN (SELECT id FROM employees) DO
      UPDATE employees SET status = 'Active' WHERE id = rec.id;
   END FOR;
END;

2. Conditional Execution (IF Statements)

Stored procedures can include conditional logic.

BEGIN
   IF :salary > 50000 THEN
      RETURN 'High Salary';
   ELSE
      RETURN 'Low Salary';
   END IF;
END;

3. Using Snowflake Procedures for Automation

Stored procedures are useful for data cleaning, ETL pipelines, and periodic updates.

Example 4: Automating Daily Sales Report

CREATE OR REPLACE PROCEDURE daily_sales_report()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
   INSERT INTO sales_summary 
   SELECT region, SUM(sales) FROM sales_data GROUP BY region;
   RETURN 'Daily report generated';
END;
$$;

---

Best Practices for Using Stored Procedures in Snowflake

1. When working with SQL, following best practices ensures clarity, performance, security, and maintainability. Below are detailed explanations with examples for each best practice.

   ---

   1. Use Descriptive Names

   Why? Clear and meaningful names make it easier to understand SQL procedures, functions, tables, and columns.

   📌 Bad Example (Unclear Naming)

    CREATE PROCEDURE proc1 (@sal DECIMAL)  
    AS  
    BEGIN  
        UPDATE emp SET sal = sal * 1.1 WHERE sal > @sal;  
    END;
   

   📌 Good Example (Descriptive Naming)

    CREATE PROCEDURE apply_salary_increase (@min_salary DECIMAL)  
    AS  
    BEGIN  
        UPDATE employees  
        SET salary = salary * 1.1  
        WHERE salary > @min_salary;  
    END;
   

   ✅ Clearly explains what the procedure does.

   ---

   2. Optimize Performance Using Indexing and Clustering Keys

   Why? Proper indexing speeds up searches, while clustering keys optimize data storage.

   📌 Bad Example (No Index, Slow Search Performance)

    SELECT * FROM orders WHERE customer_id = 12345;
   

   📌 Good Example (Indexed Column for Faster Lookups)

    CREATE INDEX idx_customer_id ON orders (customer_id);
   
    SELECT * FROM orders WHERE customer_id = 12345;
   

   ✅ Indexing reduces query execution time significantly.

   📌 Using a Clustering Key for Faster Table Access

    CREATE TABLE sales (
        sale_id INT PRIMARY KEY,
        customer_id INT,
        sale_date DATE,
        amount DECIMAL(10,2),
        CLUSTER BY (sale_date)  -- Optimizes storage for date-based queries
    );
   

   ✅ Improves performance for queries filtering by sale_date.

   ---

   3. Handle Exceptions Properly

   Why? Prevents SQL scripts from stopping execution due to unexpected errors.

   📌 Bad Example (No Error Handling, Script Stops on Error)

    INSERT INTO payments (order_id, amount) VALUES (NULL, 100);
   

   📌 Good Example (Using TRY...CATCH for Error Handling)

    BEGIN TRY  
        INSERT INTO payments (order_id, amount) VALUES (NULL, 100);
    END TRY  
    BEGIN CATCH  
        PRINT 'Error: A NULL order_id is not allowed';  
    END CATCH;
   

   ✅ Prevents script failure and provides useful error messages.

   ---

   4. Use Parameterized Queries to Avoid SQL Injection

   Why? Protects against malicious user inputs that can alter queries.

   📌 Bad Example (Vulnerable to SQL Injection)

    DECLARE @query NVARCHAR(MAX);
    SET @query = 'SELECT * FROM users WHERE username = ''' + @user_input + '''';
    EXEC(@query);  -- Dangerous!
   

   📌 Good Example (Using Parameterized Queries for Security)

    sqlCopyEditDECLARE @user_input NVARCHAR(100);  
    SET @user_input = 'john_doe';  
   
    EXEC sp_executesql  
        N'SELECT * FROM users WHERE username = @username',  
        N'@username NVARCHAR(100)',  
        @user_input;
   

   ✅ Prevents SQL injection attacks by safely handling user input.

   ---

   5. Avoid Excessive Loops – Use Bulk Operations

   Why? Loops can slow down performance, while bulk operations process data efficiently.

   📌 Bad Example (Looping Through Records One by One, Slow Execution)

    DECLARE @id INT;  
   
    DECLARE cur CURSOR FOR  
    SELECT customer_id FROM customers;  
   
    OPEN cur;  
    FETCH NEXT FROM cur INTO @id;  
   
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        UPDATE customers SET status = 'Active' WHERE customer_id = @id;  
        FETCH NEXT FROM cur INTO @id;  
    END;  
   
    CLOSE cur;  
    DEALLOCATE cur;
   

   📌 Good Example (Bulk Update Instead of Looping, Faster Execution)

    UPDATE customers  
    SET status = 'Active'  
    WHERE last_purchase_date > '2023-01-01';
   

   ✅ Significantly faster than processing records one by one.

Snowflake SQL Procedures and Best Practices for Data Management

Snowflake provides stored procedures to automate complex tasks, making data management efficient, scalable, and repeatable. Below, we will cover essential procedures for automated data loads, Slowly Changing Dimensions (SCD), schema migrations, and deployment in a Snowflake environment with real-world examples.

---

1. Procedure for Automated Data Loads

Automating data ingestion helps keep staging and target tables up to date. The procedure below automates a full data load from an external stage to a Snowflake table.

📌 Example: Automating file ingestion from AWS S3 into Snowflake

sqlCopyEditCREATE OR REPLACE PROCEDURE load_data()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    COPY INTO target_table
    FROM @my_s3_stage
    FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

    RETURN 'Data Load Successful';
END;
$$;

✅ This procedure loads data from S3 into target_table and can be scheduled using Snowflake Tasks.

---

2. SCD Type-1 Implementation

Slowly Changing Dimension Type-1 (SCD-1) replaces old data with new data without tracking historical changes.

📌 Example: Overwriting existing records

sqlCopyEditCREATE OR REPLACE PROCEDURE update_scd1()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    MERGE INTO customer_dim AS target
    USING staging_customer AS source
    ON target.customer_id = source.customer_id
    WHEN MATCHED THEN 
        UPDATE SET target.customer_name = source.customer_name,
                   target.email = source.email;

    RETURN 'SCD Type-1 Updated Successfully';
END;
$$;

✅ Existing customer records are updated with new values without maintaining historical data.

---

3. SCD Type-2 Implementation

SCD Type-2 maintains historical changes by keeping multiple versions of a record with effective date tracking.

📌 Example: Implementing SCD Type-2 in Snowflake

sqlCopyEditCREATE OR REPLACE PROCEDURE update_scd2()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    MERGE INTO customer_dim AS target
    USING staging_customer AS source
    ON target.customer_id = source.customer_id AND target.current_flag = TRUE
    WHEN MATCHED AND target.customer_name <> source.customer_name THEN 
        UPDATE SET target.current_flag = FALSE, target.end_date = CURRENT_DATE
    WHEN NOT MATCHED THEN 
        INSERT (customer_id, customer_name, email, start_date, end_date, current_flag)
        VALUES (source.customer_id, source.customer_name, source.email, CURRENT_DATE, NULL, TRUE);

    RETURN 'SCD Type-2 Updated Successfully';
END;
$$;

✅ This procedure tracks changes by adding new rows and deactivating old ones.

---

4. Procedure for One-to-One View Creation

Automatically creating views for each table in a schema ensures consistency.

📌 Example: Generating views dynamically for each table in a schema

sqlCopyEditCREATE OR REPLACE PROCEDURE create_views(schema_name STRING)
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE table_name STRING;
BEGIN
    FOR table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = schema_name) DO
        EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || table_name || '_view AS SELECT * FROM ' || table_name;
    END FOR;

    RETURN 'Views Created Successfully';
END;
$$;

✅ This procedure creates a view for every table in a specified schema.

---

5. Procedure for Dropping All Tables from a Schema

Removing tables from a schema in a controlled manner prevents orphaned objects.

📌 Example: Dropping all tables in a schema

sqlCopyEditCREATE OR REPLACE PROCEDURE drop_all_tables(schema_name STRING)
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE table_name STRING;
BEGIN
    FOR table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = schema_name) DO
        EXECUTE IMMEDIATE 'DROP TABLE ' || schema_name || '.' || table_name;
    END FOR;

    RETURN 'All Tables Dropped Successfully';
END;
$$;

✅ This procedure removes all tables in a given schema dynamically.

---

6. Procedure for Dropping All Views from a Schema

Cleaning up views ensures better schema management.

📌 Example: Dropping all views in a schema

sqlCopyEditCREATE OR REPLACE PROCEDURE drop_all_views(schema_name STRING)
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE view_name STRING;
BEGIN
    FOR view_name IN (SELECT table_name FROM information_schema.views WHERE table_schema = schema_name) DO
        EXECUTE IMMEDIATE 'DROP VIEW ' || schema_name || '.' || view_name;
    END FOR;

    RETURN 'All Views Dropped Successfully';
END;
$$;

✅ This helps remove unnecessary views from a schema.

---

7. Procedure for Migrating Table DDL from One Database to Another

This is useful when transferring schema structures across environments.

📌 Example: Extracting and executing DDL in a new database

sqlCopyEditCREATE OR REPLACE PROCEDURE migrate_table(source_db STRING, target_db STRING, table_name STRING)
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE ddl_statement STRING;
BEGIN
    SELECT get_ddl('TABLE', source_db || '.' || table_name) INTO ddl_statement;
    EXECUTE IMMEDIATE 'USE DATABASE ' || target_db;
    EXECUTE IMMEDIATE ddl_statement;

    RETURN 'Table DDL Migrated Successfully';
END;
$$;

✅ This migrates table structures dynamically.

---

8. Procedure for Automating Full Load from Staging to Target Tables

📌 Example: Full data load automation

sqlCopyEditCREATE OR REPLACE PROCEDURE full_load()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    TRUNCATE TABLE target_table;
    INSERT INTO target_table SELECT * FROM staging_table;

    RETURN 'Full Load Completed';
END;
$$;

✅ Completely refreshes the target table with staging data.

---

9. Procedure for Automating Incremental Load from Staging to Target Tables

📌 Example: Processing only new records

sqlCopyEditCREATE OR REPLACE PROCEDURE incremental_load()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    MERGE INTO target_table AS target
    USING staging_table AS source
    ON target.id = source.id
    WHEN MATCHED THEN UPDATE SET target.data = source.data
    WHEN NOT MATCHED THEN INSERT (id, data) VALUES (source.id, source.data);

    RETURN 'Incremental Load Completed';
END;
$$;

✅ Processes only new and updated records, reducing overhead.

---

10. Procedure for Sample Data Setup in Lower Environments

📌 Example: Copying limited sample data

sqlCopyEditCREATE OR REPLACE PROCEDURE setup_sample_data()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    INSERT INTO lower_env.customers SELECT * FROM prod.customers SAMPLE(10);

    RETURN 'Sample Data Inserted';
END;
$$;

✅ Moves 10% of production data to a lower environment for testing.

---

11.Automating One-to-One View Creation in Snowflake Using Stored Procedures

What is a One-to-One View?

A one-to-one (1:1) view in Snowflake is a direct mapping of a table, meaning the view selects all columns from the table without any modifications. These views are useful for:
✔ Abstracting the underlying table structure
✔ Providing controlled access (e.g., limiting access to certain columns)
✔ Enhancing security by hiding sensitive data

Why Automate View Creation?

In large databases with hundreds of tables, manually creating views for each table is inefficient. Instead, we can use a stored procedure to automatically generate views for all tables within a schema.

---

Stored Procedure for Automating One-to-One View Creation

This stored procedure:

1. Fetches all tables from a specified schema

2. Dynamically generates a CREATE VIEW statement for each table

3. Executes the SQL to create the views

Example: Automatic 1:1 View Creation for All Tables in a Schema

sqlCopyEditCREATE OR REPLACE PROCEDURE create_1_1_views(schema_name STRING, view_schema STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE table_name STRING;
DECLARE view_name STRING;
DECLARE create_view_sql STRING;

BEGIN
    -- Loop through each table in the given schema
    FOR table_name IN 
        (SELECT table_name FROM information_schema.tables WHERE table_schema = schema_name)
    DO
        -- Define the view name (Appending _VIEW to table name)
        LET view_name = view_schema || '.' || table_name || '_VIEW';

        -- Construct the dynamic SQL to create the view
        LET create_view_sql = 'CREATE OR REPLACE VIEW ' || view_name || ' AS SELECT * FROM ' || schema_name || '.' || table_name;

        -- Execute the dynamically generated SQL
        EXECUTE IMMEDIATE create_view_sql;
    END FOR;

    RETURN 'Views Created Successfully';
END;
$$;

---

How This Works

1. The procedure loops through all tables in the specified schema.

2. It constructs a dynamic SQL statement for creating views.

3. It executes the SQL using EXECUTE IMMEDIATE.

4. The views are created in the view schema with the naming convention <table_name>_VIEW.

---

Running the Procedure

To create views for all tables in the schema source_schema and store them in view_schema, run:

sqlCopyEditCALL create_1_1_views('source_schema', 'view_schema');

This will generate one-to-one views for every table in source_schema and store them in view_schema.

---

Example Scenario

Tables in source_schema:

Generated Views in view_schema:

---

Extending This Procedure

• Exclude specific tables: Modify the SELECT query in the loop to filter out certain tables.

• Restrict columns: Instead of SELECT *, manually specify columns for security purposes.

• Automate execution: Schedule the procedure using Snowflake Tasks to keep views updated.

12.Stored Procedure to Drop Tables and Views

sqlCopyEditCREATE OR REPLACE PROCEDURE drop_objects_from_schema(schema_name STRING)
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE object_list RESULTSET;
DECLARE object_name STRING;
DECLARE object_type STRING;
DECLARE sql_command STRING;

BEGIN
    -- Step 1: Get all tables and views from the schema
    LET object_list = (SELECT table_name, table_type 
                       FROM INFORMATION_SCHEMA.TABLES 
                       WHERE table_schema = :schema_name);

    -- Step 2: Loop through each object and construct the DROP statement
    FOR record IN object_list DO
        LET object_name = record.table_name;
        LET object_type = record.table_type; -- 'BASE TABLE' for tables, 'VIEW' for views

        -- Construct the DROP statement
        IF object_type = 'BASE TABLE' THEN
            LET sql_command = 'DROP TABLE ' || :schema_name || '.' || :object_name;
        ELSE
            LET sql_command = 'DROP VIEW ' || :schema_name || '.' || :object_name;
        END IF;

        -- Step 3: Execute the DROP command
        EXECUTE IMMEDIATE :sql_command;
    END FOR;

    RETURN 'All tables and views in schema ' || :schema_name || ' have been dropped successfully.';
END;
$$;

---

How It Works

1. The procedure retrieves all objects from the schema.

2. It loops through each table/view and dynamically creates a DROP statement.

3. The EXECUTE IMMEDIATE command runs the dynamically generated SQL.

---

Example Usage

To drop all tables and views in the SALES schema:

sqlCopyEditCALL drop_objects_from_schema('SALES');

This will generate and execute:

sqlCopyEditDROP TABLE SALES.orders;
DROP TABLE SALES.customers;
DROP VIEW SALES.sales_report;
DROP VIEW SALES.top_customers;

---

Enhancements

• Selective Deletion: Modify the procedure to delete only tables or views, based on an input parameter.

• Logging & Exception Handling: Capture errors when attempting to drop non-existent objects.

Example: Handling Non-Existent Objects

sqlCopyEditEXCEPTION
    WHEN statement_error THEN
        RETURN 'Error: Unable to drop object ' || :object_name || ' in schema ' || :schema_name;

---

13.Stored Procedure to Migrate Tables and Views

sqlCopyEditCREATE OR REPLACE PROCEDURE migrate_ddl(source_schema STRING, target_schema STRING)
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE object_list RESULTSET;
DECLARE object_name STRING;
DECLARE object_type STRING;
DECLARE ddl_statement STRING;
DECLARE new_ddl_statement STRING;

BEGIN
    -- Step 1: Get all tables and views from the source schema
    LET object_list = (SELECT table_name, table_type 
                       FROM INFORMATION_SCHEMA.TABLES 
                       WHERE table_schema = :source_schema);

    -- Step 2: Loop through each object and extract DDL
    FOR record IN object_list DO
        LET object_name = record.table_name;
        LET object_type = record.table_type; -- 'BASE TABLE' for tables, 'VIEW' for views

        -- Extract DDL statement
        LET ddl_statement = (SELECT GET_DDL(object_type, :source_schema || '.' || :object_name));

        -- Replace source schema with target schema
        LET new_ddl_statement = REPLACE(ddl_statement, :source_schema, :target_schema);

        -- Step 3: Execute the modified DDL in the target schema
        EXECUTE IMMEDIATE :new_ddl_statement;
    END FOR;

    RETURN 'All tables and views from ' || :source_schema || ' have been migrated to ' || :target_schema || '.';
END;
$$;

---

How It Works

1. The procedure retrieves all objects from the source schema.

2. It extracts the DDL definition using GET_DDL().

3. It replaces the schema reference with the target schema.

4. The modified DDL is executed in the target schema.

---

Example Usage

To migrate tables and views from DEV_SCHEMA to PROD_SCHEMA:

sqlCopyEditCALL migrate_ddl('DEV_SCHEMA', 'PROD_SCHEMA');

This will generate and execute:

sqlCopyEditCREATE TABLE PROD_SCHEMA.orders (...);
CREATE TABLE PROD_SCHEMA.customers (...);
CREATE VIEW PROD_SCHEMA.sales_report AS SELECT ... FROM PROD_SCHEMA.orders;

---

Enhancements

• Selective Migration: Modify the procedure to migrate only tables or views, based on an input parameter.

• Logging & Exception Handling: Capture errors when attempting to create objects in the target schema.

• Exclude Specific Tables/Views: Allow the exclusion of certain objects based on user input.

Example: Handling Errors

sqlCopyEditEXCEPTION
    WHEN statement_error THEN
        RETURN 'Error: Unable to migrate object ' || :object_name || ' from ' || :source_schema || ' to ' || :target_schema;

14.Automating Full and Incremental Load in Snowflake Using Stored Procedures

Overview

To efficiently manage data in Snowflake, we automate:
✅ Full Load – Replaces the entire target table with new data from the staging table.
✅ Incremental Load – Only inserts new records or updates existing ones without deleting historical data.

Step 1: Create Database and Schema

Before implementing the automation, ensure that the required database and schemas exist.

sqlCopyEditCREATE DATABASE IF NOT EXISTS EMP;
CREATE SCHEMA IF NOT EXISTS EMP.STAGING;
CREATE SCHEMA IF NOT EXISTS EMP.HRDATA;
CREATE SCHEMA IF NOT EXISTS EMP.PROCS;
CREATE SCHEMA IF NOT EXISTS EMP.WORK;

Step 2: Create Error Logging Table

This will capture errors encountered during execution.

sqlCopyEditCREATE TABLE EMP.WORK.SP_ERROR_LOGS
(PROC_NAME VARCHAR, ERROR_TYPE VARCHAR, ERROR_CODE VARCHAR, ERROR_MESSAGE VARCHAR, SQL_STATE VARCHAR);

Step 3: Create Staging and Target Tables

sqlCopyEditCREATE OR REPLACE TABLE emp.staging.employees (
    employee_id NUMBER(6),
    first_name VARCHAR(20),
    last_name VARCHAR(25) NOT NULL,
    email VARCHAR(25) NOT NULL,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    dept_id NUMBER(4)
);

CREATE OR REPLACE TABLE emp.hrdata.employees (
    employee_id NUMBER(6),
    first_name VARCHAR(20),
    last_name VARCHAR(25) NOT NULL,
    email VARCHAR(25) NOT NULL,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    dept_id NUMBER(4),
    PRIMARY KEY (employee_id)
);

---

Step 4: Automate Full Load

The full load procedure truncates the target table and reloads it completely.

sqlCopyEditCREATE OR REPLACE PROCEDURE emp.procs.full_load()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    -- Log the procedure start
    INSERT INTO EMP.WORK.SP_ERROR_LOGS (PROC_NAME, ERROR_TYPE, ERROR_MESSAGE)
    VALUES ('full_load', 'INFO', 'Starting full load process');

    -- Truncate the target table before loading new data
    TRUNCATE TABLE emp.hrdata.employees;

    -- Load data from staging to target
    INSERT INTO emp.hrdata.employees
    SELECT * FROM emp.staging.employees;

    -- Log success
    INSERT INTO EMP.WORK.SP_ERROR_LOGS (PROC_NAME, ERROR_TYPE, ERROR_MESSAGE)
    VALUES ('full_load', 'INFO', 'Full load completed successfully');

    RETURN 'Full Load Completed Successfully';
END;
$$;

Executing the Full Load Procedure

sqlCopyEditCALL emp.procs.full_load();

✅ This will clear all records in hrdata.employees and reload fresh data from staging.employees.

---

Step 5: Automate Incremental Load (Upsert – Merge)

The incremental load procedure updates existing records and inserts new ones from staging.

sqlCopyEditCREATE OR REPLACE PROCEDURE emp.procs.incremental_load()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
    -- Log procedure start
    INSERT INTO EMP.WORK.SP_ERROR_LOGS (PROC_NAME, ERROR_TYPE, ERROR_MESSAGE)
    VALUES ('incremental_load', 'INFO', 'Starting incremental load process');

    -- Merge operation: Update existing records, insert new records
    MERGE INTO emp.hrdata.employees AS target
    USING emp.staging.employees AS source
    ON target.employee_id = source.employee_id
    WHEN MATCHED THEN
        UPDATE SET
            target.first_name = source.first_name,
            target.last_name = source.last_name,
            target.email = source.email,
            target.phone_number = source.phone_number,
            target.hire_date = source.hire_date,
            target.job_id = source.job_id,
            target.salary = source.salary,
            target.commission_pct = source.commission_pct,
            target.manager_id = source.manager_id,
            target.dept_id = source.dept_id
    WHEN NOT MATCHED THEN
        INSERT (
            employee_id, first_name, last_name, email, phone_number, 
            hire_date, job_id, salary, commission_pct, manager_id, dept_id
        )
        VALUES (
            source.employee_id, source.first_name, source.last_name, source.email, source.phone_number, 
            source.hire_date, source.job_id, source.salary, source.commission_pct, source.manager_id, source.dept_id
        );

    -- Log success
    INSERT INTO EMP.WORK.SP_ERROR_LOGS (PROC_NAME, ERROR_TYPE, ERROR_MESSAGE)
    VALUES ('incremental_load', 'INFO', 'Incremental load completed successfully');

    RETURN 'Incremental Load Completed Successfully';
END;
$$;

Executing the Incremental Load Procedure

sqlCopyEditCALL emp.procs.incremental_load();

✅ This procedure:

• Updates existing employee records if they already exist in hrdata.employees.

• Inserts new employee records if they don’t exist in hrdata.employees.

---

Step 6: Automate the Load with Snowflake Tasks

We can schedule these procedures to run automatically using Snowflake tasks.

Task for Full Load (Runs Daily at Midnight)

sqlCopyEditCREATE OR REPLACE TASK emp.procs.full_load_task
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 0 * * * UTC'
AS
CALL emp.procs.full_load();

Task for Incremental Load (Runs Every Hour)

sqlCopyEditCREATE OR REPLACE TASK emp.procs.incremental_load_task
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
CALL emp.procs.incremental_load();

Enable the Tasks

sqlCopyEditALTER TASK emp.procs.full_load_task RESUME;
ALTER TASK emp.procs.incremental_load_task RESUME;

✅ Now, the full load runs daily at midnight, and the incremental load runs every hour.

---

Step 7: Error Handling and Debugging

You can check for errors using:

sqlCopyEditSELECT * FROM emp.work.sp_error_logs ORDER BY SQL_STATE DESC;

If a procedure fails, it will log the error details in sp_error_logs.

---

Final Summary

15. Code Deployment Using GitHub and Schema Change

📌 Example: Automating schema deployments with GitHub

1. Store SQL scripts in GitHub repository.

2. Use Liquibase or Flyway for version control.

3. Automate deployments via CI/CD pipeline.

✅ Ensures controlled and trackable schema changes across environments.

---

Conclusion

By implementing stored procedures, we can:
✔ Automate data loads and schema migrations
✔ Improve performance using SCD-1 and SCD-2
✔ Maintain consistent schema across databases
✔ Implement CI/CD for structured deployments

---

2. Understanding Variables, Session Variables, and Execute Immediate in Snowflake

Introduction

Variables in Snowflake play an important role in dynamic SQL execution, data manipulation, and control flow within stored procedures and scripting. This guide covers:

• Regular Variables (used inside procedures)

• Session Variables (persist for a session)

• Execute Immediate (for dynamic SQL execution)

---

Variables in Snowflake

Variables in Snowflake are used inside stored procedures or scripts to store and manipulate values.

How to Declare a Variable?

Variables are declared using the DECLARE statement.

Example: Declaring a Variable

DECLARE customer_name STRING;
DECLARE order_count INTEGER DEFAULT 0;

• customer_name stores a string.

• order_count is an integer with a default value of 0.

---

Assigning Values to Variables

Values can be assigned using:

1. := Operator

2. DEFAULT Keyword

3. SELECT INTO Statement

Example 1: Assigning Values Using :=

DECLARE price NUMBER(10,2);
BEGIN
    LET price := 99.99;
    RETURN price;
END;

Example 2: Assigning Default Values

DECLARE discount NUMBER DEFAULT 10;

Example 3: Assigning Values Using SQL Query

DECLARE total_sales NUMBER;
BEGIN
    SELECT SUM(sales_amount) INTO :total_sales FROM sales_table;
    RETURN total_sales;
END;

• Here, total_sales is calculated dynamically.

---

Session Variables

Session variables persist for the entire session but are NOT available outside it.

Declaring a Session Variable

Session variables are declared using SET.

SET order_id = 1001;
SET customer_name = 'Jane Doe';
SET (B, C, city) = (100, 35, 'Hyderabad');

• order_id holds an integer.

• customer_name stores a string.

• Multiple variables (B, C, city) are assigned values in one command.

---

Accessing Session Variables

Session variables are accessed using $.

SELECT $order_id, $customer_name;

Example: Using Session Variables in SQL Query

SELECT * FROM orders WHERE order_id = $order_id;

---

Key Differences Between Regular and Session Variables

---

Using Variables in SQL Statements

Variables must be prefixed with : when used inside SQL queries (Binding Variables).

Example: Concatenating Strings

DECLARE first_name STRING DEFAULT 'John';
DECLARE last_name STRING DEFAULT 'Doe';
DECLARE full_name STRING;
BEGIN
    LET full_name := first_name || ' ' || last_name;
    RETURN full_name;
END;

Example: Using Variables in SELECT Statement

DECLARE total_sales NUMBER;
BEGIN
    SELECT SUM(sales) INTO :total_sales FROM sales_data;
    RETURN total_sales;
END;

• The : before total_sales indicates a binding variable.

---

Execute Immediate in Snowflake

EXECUTE IMMEDIATE allows dynamic SQL execution in Snowflake. It is used when:

• The SQL query is not known in advance.

• You need to construct queries dynamically.

• Running DDL (Data Definition Language) commands dynamically.

---

How to Use Execute Immediate?

Example: Creating a Table Dynamically

EXECUTE IMMEDIATE
$$
    CREATE TABLE dynamic_table (
        id INT,
        name STRING
    );
$$;

• This dynamically creates a table without writing static SQL.

Example: Inserting Data Dynamically

EXECUTE IMMEDIATE
$$
    INSERT INTO orders (order_id, customer_name)
    VALUES (1001, 'Alice');
$$;

Example: Using Execute Immediate in a Stored Procedure

CREATE OR REPLACE PROCEDURE create_table_dynamically(tab_name STRING)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    EXECUTE IMMEDIATE
    $$ CREATE TABLE IDENTIFIER(:tab_name) (id INT, name STRING) $$;
    RETURN 'Table Created Successfully';
END;
$$;

Explanation:

1. The procedure takes a table name as a parameter.

2. It creates a table dynamically using EXECUTE IMMEDIATE.

Calling the Procedure

CALL create_table_dynamically('customer_data');

This creates a table named customer_data.

---

5. Using Variables with Execute Immediate

Example: Assigning Values Dynamically

EXECUTE IMMEDIATE
$$
DECLARE revenue NUMBER(10,2);
BEGIN
    LET revenue := 5000.50;
    RETURN revenue;
END;
$$;

• Here, a variable revenue is declared, assigned a value, and returned.

---

6. Handling Errors with Execute Immediate

Snowflake supports error handling inside dynamic SQL execution.

Example: Handling Errors in Execute Immediate

EXECUTE IMMEDIATE
$$
BEGIN
    EXECUTE IMMEDIATE $$ SELECT * FROM non_existing_table $$;
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RETURN 'An error occurred in the statement!';
    WHEN OTHER ERROR THEN
        RETURN 'A general error occurred!';
END;
$$;

• If the table doesn’t exist, the error is caught and a friendly message is returned.

---

7. Real-World Use Cases

Use Case 1: Generating Daily Sales Report

A retail company needs to generate a sales report every day.

CREATE OR REPLACE PROCEDURE generate_daily_sales_report()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    EXECUTE IMMEDIATE
    $$ INSERT INTO daily_sales SELECT CURRENT_DATE, SUM(sales) FROM orders WHERE order_date = CURRENT_DATE $$;
    RETURN 'Daily Sales Report Generated Successfully';
END;
$$;

---

Use Case 2: Managing Dynamic User Roles

A corporate database needs to create roles dynamically based on input.

CREATE OR REPLACE PROCEDURE create_role_dynamically(role_name STRING)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    EXECUTE IMMEDIATE
    $$ CREATE ROLE IDENTIFIER(:role_name) $$;
    RETURN 'Role Created Successfully';
END;
$$;

Calling the Procedure

CALL create_role_dynamically('Analyst');

This dynamically creates a role named "Analyst".

---

Best Practices for Using Variables and Execute Immediate

1. Use Session Variables for Global Parameters

   • Example: Store user preferences in a session.

2. Avoid Hardcoded Values in Dynamic SQL

   • Use binding variables (:) for security.

3. Handle Errors in Dynamic Queries

   • Use EXCEPTION blocks to catch errors.

4. Use IDENTIFIER(:variable_name)

   • When dynamically referencing tables or columns.

5. Optimize Performance with Auto-Suspend

   • Ensure warehouses do not run indefinitely.

---

Conclusion

Understanding variables, session variables, and Execute Immediate in Snowflake helps in writing flexible, dynamic, and optimized SQL scripts.

✅ Regular Variables are used inside procedures.
✅ Session Variables persist across queries in a session.
✅ Execute Immediate allows dynamic query execution.
✅ Error Handling ensures smooth execution.
✅ Real-World Use Cases include reports, user role management, and automation.

3. Understanding Table Literals and Identifiers in Snowflake

Introduction

In Snowflake, sometimes we need to use variables or parameters as table names dynamically. This is where Table Literals and Identifiers come into play. Table Literals help in referencing tables inside SQL statements, while Identifiers allow referencing various database objects dynamically.

---

Table Literals in Snowflake

What are Table Literals?

Table Literals allow us to dynamically pass a table name to a query instead of hardcoding it. This is useful when working with dynamic SQL queries where the table name needs to be determined at runtime.

Syntax:

TABLE( <string_literal> | <session_variable> | <bind_variable> )

• string_literal: The actual table name or fully qualified table name.

• session_variable: A variable storing the table name within a session.

• bind_variable: A variable bound at runtime.

Imagine you are running a shop and keep your sales records in different tables based on the year. You have:

• sales_2023

• sales_2024

• sales_2025

Now, let’s say you want to check sales for a particular year, but you don’t want to manually type the table name every time. Instead, you can store the table name in a variable and use Table Literals to dynamically refer to the correct table in your SQL query.

Real-World Example

Suppose you work for an e-commerce website that tracks customer orders in different tables for each year. If a customer service agent wants to check orders for 2023, you would normally run:

SELECT * FROM sales_2023;

But what if they want to check sales for 2024 or 2025? Instead of changing the table name manually, you can use Table Literals to make your query dynamic.

Using Table Literals in SQL

Instead of hardcoding the table name, we store it in a variable and pass it dynamically.

SET my_table = 'sales_2024';
SELECT * FROM TABLE($my_table);

This makes your SQL flexible and reusable—you can change the table name without modifying the query!

Key Features of Table Literals:

• They are used to pass the name of a table dynamically.

• They appear in the FROM clause of a SQL statement.

• They accept scalar values as input.

• They return a set of zero or more rows.

• They can be used as a source of rows in a FROM clause.

• They are similar to a table function TABLE().

Examples of Table Literals:

-- Using a string literal as a table name
SELECT * FROM TABLE('table_name');

-- Using a fully qualified table name
SELECT * FROM TABLE('db_name.schema_name.table_name');

-- Using a session variable
SET myvar = 'mytable';
SELECT * FROM TABLE($myvar);

-- Using a bind variable
SELECT * FROM TABLE(?);
SELECT * FROM TABLE(:bind_variable);

Incorrect Usage of Table Literals:

-- These statements are incorrect in Snowflake
SELECT * FROM $myvar;  -- Incorrect
SELECT * FROM ?;        -- Incorrect
SELECT * FROM :bind_variable;  -- Incorrect

Real-World Example:

Imagine a scenario where an ETL process loads data into different tables dynamically every day. Instead of writing separate queries for each table, a Table Literal can be used to dynamically select data from the table corresponding to the current date.

SET current_table = 'sales_20240214';
SELECT * FROM TABLE($current_table);

---

Identifiers in Snowflake

What are Identifiers?

Identifiers in Snowflake are used to refer to database objects dynamically, such as:

• Tables

• Schemas

• Databases

• Functions

Unlike Table Literals, which are restricted to the FROM clause, Identifiers can be used in any type of SQL statement.

Syntax:

IDENTIFIER( <string_literal> | <session_variable> | <bind_variable> | <SQL Variable> )

Why Use Identifiers?

• They allow dynamic referencing of various Snowflake objects.

• They support variables and session variables.

• They make SQL queries more flexible and reusable.

• They allow executing dynamic SQL statements efficiently.

Examples of Identifiers in Snowflake:

-- Creating a database dynamically
CREATE OR REPLACE DATABASE IDENTIFIER('my_db');

-- Setting a schema name as a variable
SET schema_name = 'my_db.my_schema';

-- Using an identifier to switch schema dynamically
USE SCHEMA IDENTIFIER($schema_name);

-- Dropping a table dynamically
DROP TABLE IDENTIFIER(?);

-- Describing a table using an identifier
DESCRIBE TABLE IDENTIFIER(:table_name);

-- Counting records dynamically from a table
SELECT COUNT(*) AS total_records FROM IDENTIFIER(:table_name);

Real-World Example:

Consider a multi-tenant application where each client has their own schema. Instead of writing separate queries for each schema, Identifiers allow querying dynamically.

SET client_schema = 'client_A_schema';
USE SCHEMA IDENTIFIER($client_schema);
SELECT * FROM IDENTIFIER('orders');

This approach allows developers to switch schemas dynamically without modifying the actual query.

---

Key Differences Between Table Literals and Identifiers

---

Conclusion

Understanding Table Literals and Identifiers in Snowflake is essential for writing dynamic, flexible, and reusable SQL queries.

• Use Table Literals when dynamically passing table names in the FROM clause.

• Use Identifiers for dynamically referencing schemas, databases, tables, and functions.

By leveraging these features, Snowflake users can build scalable and efficient data pipelines that handle dynamic queries with ease.

4.Branching Constructs in Snowflake Scripting

Branching constructs help us make decisions in a program based on certain conditions. Snowflake scripting supports two main types of branching constructs:

1. IF Statement

2. CASE Statement

---

1. IF Statement

The IF statement checks a condition and executes a block of code if the condition is true. If the first condition is false, it checks the next condition using ELSEIF, and if no conditions are true, it executes the ELSE block.

Syntax:

IF (Condition1) THEN
    < Statements to be executed >
ELSEIF (Condition2) THEN
    < Statements to be executed >
ELSEIF (Condition3) THEN
    < Statements to be executed >
ELSE
    < Statements to be executed >
END IF;

Example:

Imagine you are calculating discounts based on the total amount of a customer's purchase.

DECLARE total_amount NUMBER DEFAULT 500;
DECLARE discount NUMBER;

BEGIN
    IF total_amount > 1000 THEN
        LET discount := 20;  -- 20% discount for orders above 1000
    ELSEIF total_amount > 500 THEN
        LET discount := 10;  -- 10% discount for orders between 500-1000
    ELSE
        LET discount := 5;   -- 5% discount for orders below 500
    END IF;

    RETURN discount;
END;

📌 Explanation:

• If the total amount is greater than 1000, a 20% discount is applied.

• If it's between 500 and 1000, a 10% discount is given.

• Otherwise, a 5% discount is applied.

---

2. CASE Statement

The CASE statement is an alternative to IF-ELSEIF conditions. It goes through a set of conditions and executes the block of code where the first condition is true.

Syntax:

CASE
    WHEN <Condition1> THEN <Value or Expression>
    WHEN <Condition2> THEN <Value or Expression>
    WHEN <Condition3> THEN <Value or Expression>
    ELSE <Value or Expression>
END;

Example:

You are categorizing employees based on their years of experience.

DECLARE years_experience NUMBER DEFAULT 7;
DECLARE category STRING;

BEGIN
    CASE
        WHEN years_experience >= 10 THEN
            LET category := 'Senior';
        WHEN years_experience >= 5 THEN
            LET category := 'Mid-Level';
        ELSE
            LET category := 'Junior';
    END CASE;

    RETURN category;
END;

📌 Explanation:

• If an employee has 10+ years of experience, they are categorized as Senior.

• If they have 5-9 years, they are Mid-Level.

• If they have less than 5 years, they are Junior.

---

Looping Constructs in Snowflake Scripting

Loops allow us to execute a block of code multiple times. Snowflake scripting supports four types of loops:

1. FOR Loop

2. WHILE Loop

3. REPEAT Loop

4. LOOP with BREAK

---

1. FOR Loop

The FOR loop runs a block of code a fixed number of times.

Syntax:

FOR variable IN start_value .. end_value DO
    < Statements to be executed >
END FOR;

Example:

Generating a list of numbers from 1 to 5.

BEGIN
    FOR i IN 1..5 DO
        PRINT 'Number: ' || i;
    END FOR;
END;

📌 Explanation:
This loop runs 5 times, printing numbers 1 to 5.

---

2. WHILE Loop

The WHILE loop keeps running until a specified condition becomes false.

Syntax:

WHILE ( condition ) DO
    < Statements to be executed >
END WHILE;

Example:

You want to process a payment until the balance reaches zero.

DECLARE balance NUMBER DEFAULT 500;
DECLARE payment NUMBER DEFAULT 100;

BEGIN
    WHILE balance > 0 DO
        LET balance := balance - payment;
        PRINT 'Remaining Balance: ' || balance;
    END WHILE;
END;

📌 Explanation:

• Starts with 500.

• Deducts 100 in each loop iteration.

• Stops when balance is 0.

---

3. REPEAT Loop

The REPEAT loop runs at least once and then checks the condition.

Syntax:

REPEAT
    < Statements to be executed >
UNTIL ( condition ) 
END REPEAT;

Example:

You need to generate random numbers until you get a number greater than 90.

DECLARE random_num NUMBER;

BEGIN
    REPEAT
        LET random_num := RANDOM(1, 100);
        PRINT 'Generated Number: ' || random_num;
    UNTIL random_num > 90
    END REPEAT;
END;

📌 Explanation:

• Generates a random number.

• If the number is ≤ 90, it keeps running.

• Stops when it exceeds 90.

---

4. LOOP with BREAK

The LOOP statement runs indefinitely until a BREAK condition is met.

Syntax:

LOOP 
    < Statements to be executed >
    ....
    BREAK;
    ....
END LOOP;

Example:

Simulating a countdown timer.

DECLARE counter NUMBER DEFAULT 10;

BEGIN
    LOOP
        PRINT 'Countdown: ' || counter;
        LET counter := counter - 1;

        IF counter = 0 THEN
            BREAK;
        END IF;
    END LOOP;

    PRINT 'Time is up!';
END;

📌 Explanation:

• Prints numbers from 10 to 1.

• Stops when it reaches 0.

---

Summary Table

---

Final Thoughts

Branching and looping constructs simplify complex logic in Snowflake scripting.
By mastering these, you can automate data processing, optimize queries, and build dynamic workflows efficiently.

Let me know if you need any additional explanations or custom examples!

5.Cursors and Result sets in Snowflake

Cursors in Snowflake are essential tools for processing query results row-by-row within stored procedures or scripts. They enable iterative operations on data, which is particularly useful for complex transformations or conditional logic that can't be efficiently handled with set-based operations. Result sets represent the data retrieved by a query, and cursors act as pointers to traverse these result sets.

---

Understanding Cursors

A cursor is a database object that allows you to:

• Retrieve rows from a query result (result set).

• Process individual rows sequentially.

• Perform operations like updates, inserts, or deletions on a row-by-row basis.

While Snowflake excels at set-based operations (optimized for bulk data processing), cursors are valuable for scenarios requiring per-row logic, such as:

• Conditional branching based on row values.

• Integrating with external systems row by row.

• Iterating through dynamic query results.

---

How to Use Cursors in Snowflake

Cursors follow a lifecycle: Declaration → Open → Fetch → Close.
Below is a step-by-step guide with examples:

---

1.Declare a Cursor

Cursors are declared in the DECLARE section of a Snowflake Scripting block or using LET for dynamic assignment.

Static Cursor (Fixed Query)

sql

Copy

DECLARE
  cur_static CURSOR FOR 
    SELECT employee_id, name FROM employees WHERE department = 'Engineering';

Dynamic Cursor (Using LET)

sql

Copy

BEGIN
  LET query := 'SELECT * FROM products WHERE category = ?';
  LET cur_dynamic CURSOR FOR query; -- Query defined dynamically
END;

---

2. Open a Cursor

Opening a cursor initializes it and binds parameters (if any). This step is optional for static cursors (they auto-open) but required for dynamic cursors.

sql

Copy

OPEN cur_dynamic USING ('Electronics'); -- Bind parameter to dynamic cursor

---

3. Fetch Rows

Use loops (FOR, WHILE) to fetch rows. The FETCH command retrieves rows into variables.

Example with FOR Loop (Auto-Fetch)

sql

Copy

DECLARE
  id INTEGER;
  name VARCHAR;
  cur CURSOR FOR SELECT employee_id, name FROM employees;
BEGIN
  FOR row_variable IN cur DO
    id := row_variable.employee_id;
    name := row_variable.name;
    -- Process row (e.g., log or transform data)
    RETURN id || ': ' || name;
  END FOR;
END;

Manual Fetching

sql

Copy

DECLARE
  cur CURSOR FOR SELECT price FROM products;
  price_value FLOAT;
BEGIN
  OPEN cur;
  FETCH cur INTO price_value; -- Fetch one row
  WHILE (SQLCODE = 0) DO -- Loop until no more rows
    -- Process price_value
    FETCH cur INTO price_value;
  END WHILE;
  CLOSE cur;
END;

---

4. Close the Cursor

Explicitly close cursors opened with OPEN to free resources. Static cursors in FOR loops auto-close.

sql

Copy

CLOSE cur_dynamic;

---

Dynamic Cursors with EXECUTE IMMEDIATE

For fully dynamic SQL (e.g., queries built at runtime), use EXECUTE IMMEDIATE:

sql

Copy

DECLARE
  sql_text VARCHAR := 'SELECT * FROM sales WHERE region = ?';
  res RESULTSET;
  cur CURSOR;
BEGIN
  res := (EXECUTE IMMEDIATE :sql_text USING ('West'));
  cur := res; -- Assign result set to cursor
  FOR row IN cur DO
    -- Process each row
  END FOR;
END;

---

Best Practices

1. Avoid Overuse: Cursors can be slow for large datasets. Prefer set-based operations where possible.

2. Filter Early: Narrow down result sets in the cursor query (e.g., use WHERE clauses) to minimize iterations.

3. Batch Processing: Use FETCH with LIMIT to process rows in batches for better performance.

4. Explicitly Close Cursors: Ensure resources are released promptly.

---

Conclusion

Cursors in Snowflake provide granular control over result sets, enabling row-by-row processing when necessary. While they should be used sparingly in favor of bulk operations, they are indispensable for specific use cases. Always pair them with efficient query design and resource management to maintain performance.

6.Exception Handling in Snowflake Scripting

What is Exception Handling?

Exception handling in Snowflake Scripting helps manage errors that occur during script execution. If an error occurs while executing a statement, Snowflake raises an exception and stops execution unless the error is handled properly.

How Exceptions Work in Snowflake?

• If an error occurs while executing a SQL statement, Snowflake raises an exception.

• An exception prevents subsequent statements from executing.

• Exception handlers can be defined in the script to manage errors gracefully.

• If a handler is found for an exception, the script executes the handler’s logic; otherwise, the error stops execution.

Types of Exceptions

1. Built-in Exceptions

Snowflake provides two built-in exceptions:

• Snowflake provides two built-in exceptions to handle errors in SQL execution:

  ---

  1. STATEMENT_ERROR

  Raised when a SQL statement encounters an execution error.

  📌 Example 1: Dropping a non-existent table

    DROP TABLE non_existent_table;
  

  Error Message:

    SQL compilation error: Table 'NON_EXISTENT_TABLE' does not exist
  

  ✅ Fix: Check if the table exists before dropping it:

    DROP TABLE IF EXISTS non_existent_table;
  

  📌 Example 2: Inserting incorrect data type into a column

    CREATE TABLE employees (id INT, name STRING, salary FLOAT);
    INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 'abc');
  

  Error Message:

    Numeric value 'abc' is not recognized
  

  ✅ Fix: Ensure the correct data type is used:

    INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000);
  

  📌 Example 3: Dividing by zero in a query

    SELECT 100 / 0;
  

  Error Message:

    Division by zero error
  

  ✅ Fix: Use NULLIF to avoid division by zero errors:

    SELECT 100 / NULLIF(0, 0);
  

  ---

  2. EXPRESSION_ERROR

  Raised when an invalid expression is used in a SQL statement.

  📌 Example 1: Assigning a VARCHAR to a FLOAT column

    CREATE TABLE products (id INT, price FLOAT);
    INSERT INTO products (id, price) VALUES (1, 'ten');
  

  Error Message:

    Numeric value 'ten' is not recognized
  

  ✅ Fix: Ensure only numerical values are inserted:

    INSERT INTO products (id, price) VALUES (1, 10.99);
  

  📌 Example 2: Using an aggregate function incorrectly

    SELECT SUM(salary) FROM employees WHERE department = 'HR' GROUP BY name;
  

  Error Message:

    Expression 'name' must be part of an aggregate function or GROUP BY clause
  

  ✅ Fix: Ensure correct GROUP BY usage:

    SELECT name, SUM(salary) FROM employees WHERE department = 'HR' GROUP BY name;
  

  📌 Example 3: Using an undefined variable in a SQL expression

    SELECT salary * bonus FROM employees;
  

  Error Message:

    Column 'bonus' does not exist
  

  ✅ Fix: Ensure all referenced columns exist:

    SELECT salary * 0.10 AS bonus FROM employees;
  

  ---

  How to Handle These Errors in Snowflake

  You can use EXCEPTION HANDLING with BEGIN...EXCEPTION blocks in Snowflake procedures to catch these errors.

  📌 Example: Handling Errors in a Procedure

    CREATE OR REPLACE PROCEDURE safe_drop_table(table_name STRING)
    RETURNS STRING
    LANGUAGE SQL  
    AS  
    $$  
    BEGIN  
        BEGIN  
            EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;  
            RETURN 'Table dropped successfully';  
        EXCEPTION  
            WHEN STATEMENT_ERROR THEN  
                RETURN 'Error: Table does not exist or another issue occurred';  
        END;  
    END;  
    $$;
  

  ✅ Now calling this procedure won't fail if the table does not exist:

    CALL safe_drop_table('non_existent_table');
  

  ---

  Summary

  | Exception Type | Cause | Example | Fix | | --- | --- | --- | --- | | STATEMENT_ERROR | SQL execution error | Dropping a non-existent table, inserting incorrect data types | Check existence before executing, use correct data types | | EXPRESSION_ERROR | Invalid expression | Assigning VARCHAR to FLOAT, incorrect use of aggregate functions | Ensure correct data types and function usage |

2. User-Defined Exceptions

In addition to built-in exceptions, users can define custom exceptions in Snowflake scripting.

Declaring an Exception

A user-defined exception is declared using the EXCEPTION keyword.

DECLARE my_exception EXCEPTION (-20002, 'Custom Exception Occurred');

• The error code should be between -20999 to -20001.

• The exception name acts as an identifier that can be used in error handling.

Using Exception Handlers

Exception handlers can be defined inside an EXCEPTION block within a procedure or script.

Example: Handling Different Types of Errors

BEGIN
    -- SQL statements that may raise an error
    DELETE FROM employees WHERE emp_id = 100;
EXCEPTION
    WHEN statement_error THEN
        RETURN OBJECT_CONSTRUCT('Error Type', 'STATEMENT ERROR', 'SQLCODE', sqlcode, 'SQLERRM', sqlerrm, 'SQLSTATE', sqlstate);
    WHEN expression_error THEN
        RETURN OBJECT_CONSTRUCT('Error Type', 'EXPRESSION ERROR', 'SQLCODE', sqlcode, 'SQLERRM', sqlerrm, 'SQLSTATE', sqlstate);
    WHEN my_exception THEN
        RETURN OBJECT_CONSTRUCT('Error Type', 'MY_EXCEPTION', 'SQLCODE', sqlcode, 'SQLERRM', sqlerrm, 'SQLSTATE', sqlstate);
    WHEN OTHER THEN
        RETURN OBJECT_CONSTRUCT('Error Type', 'OTHER ERROR', 'SQLCODE', sqlcode, 'SQLERRM', sqlerrm, 'SQLSTATE', sqlstate);
END;

Exception Handling Components

1. SQLCODE: A 5-digit signed integer that represents the error code. For user-defined exceptions, the error code must be between -20999 to -20001.

2. SQLERRM: A message describing the error.

3. SQLSTATE: A 5-character code based on the ANSI SQL standard for classifying errors.

How Exception Handling Works?

1. The script executes SQL statements inside the BEGIN...END block.

2. If an error occurs, Snowflake looks for a matching EXCEPTION handler.

3. If a matching handler is found, the script executes the handler’s logic and prevents the script from stopping abruptly.

4. If no handler is found, execution stops, and the error message is displayed.

Best Practices for Exception Handling in Snowflake

• Always define exception handlers for critical transactions to avoid script failures.

• Use meaningful error codes and messages for user-defined exceptions.

• Log error details using SQLCODE, SQLERRM, and SQLSTATE to help diagnose issues.

• Handle specific exceptions separately before using WHEN OTHER to catch generic errors.

Conclusion

Exception handling is an essential feature in Snowflake scripting that ensures smooth execution of SQL scripts by managing errors effectively. By understanding and implementing exception handling correctly, you can prevent unexpected failures and improve the robustness of your scripts.

7.Transactions in Snowflake

Understanding Transactions

A transaction is a sequence of SQL statements executed as a single unit. If any statement within the transaction fails, the entire transaction can be rolled back, preventing partial updates. Transactions ensure data consistency and integrity.

How Transactions Work in Snowflake?

• By default, Snowflake auto-commits SQL statements.

• If multiple statements need to be committed together, they must be grouped into a transaction.

• A transaction can be explicitly started using BEGIN TRANSACTION.

• It can be explicitly ended using either COMMIT (to save changes) or ROLLBACK (to undo changes).

Transaction Control Statements

1. BEGIN TRANSACTION

This command starts a transaction and groups subsequent statements into a single unit.

sqlCopyEditBEGIN TRANSACTION;

2. COMMIT

This command saves all changes made within the transaction permanently.

sqlCopyEditCOMMIT;

3. ROLLBACK

This command undoes all changes made within the transaction.

sqlCopyEditROLLBACK;

Example of a Transaction

sqlCopyEditBEGIN TRANSACTION;
    UPDATE employees SET salary = salary + 5000 WHERE department = 'HR';
    DELETE FROM employees WHERE emp_id = 105;
COMMIT;

• If both statements execute successfully, the changes are committed.

• If an error occurs in any statement, the transaction can be rolled back.

Rollback on Failure

If any statement within the transaction fails, all statements in that transaction are automatically rolled back to maintain consistency.

Example of Failure Handling

sqlCopyEditBEGIN TRANSACTION;
    UPDATE employees SET salary = salary + 5000 WHERE department = 'HR';
    DELETE FROM employees WHERE emp_id = 105;
    INSERT INTO employees(emp_id, name) VALUES (106); -- Missing required columns, causes failure
ROLLBACK; -- Automatically executed if any statement fails

• The failure in the INSERT statement rolls back all previous changes.

• No salary updates or deletions occur.

DDL Statements in Transactions

DDL (Data Definition Language) statements like CREATE, DROP, and ALTER do not follow transactions. If executed inside a transaction, they are treated as separate transactions and committed immediately.

Example

sqlCopyEditBEGIN TRANSACTION;
    UPDATE employees SET salary = salary + 5000 WHERE department = 'HR';
    CREATE TABLE backup_employees AS SELECT * FROM employees;
COMMIT;

• The UPDATE statement is part of the transaction.

• The CREATE TABLE statement is auto-committed immediately and cannot be rolled back.

Key Takeaways

• Transactions group multiple SQL statements to ensure data consistency.

• Use BEGIN TRANSACTION to start a transaction.

• Use COMMIT to save changes and ROLLBACK to undo changes.

• If a transaction fails, all statements within it are automatically rolled back.

• DDL statements (CREATE, DROP, ALTER) are committed immediately, even inside a transaction.

By implementing transactions effectively, you can prevent data inconsistencies, ensure rollback on errors, and maintain reliable database operations in Snowflake.

8.User-Defined Functions (UDFs) in Snowflake

User-Defined Functions (UDFs) in Snowflake allow you to encapsulate custom logic for reuse in SQL queries, enhancing modularity and simplifying complex operations. Snowflake supports two types of UDFs: SQL UDFs (written in SQL) and JavaScript UDFs (written in JavaScript). Additionally, UDFs can be scalar (return a single value) or tabular (return a table).

---

Types of UDFs

1. SQL UDFs

• Scalar: Return a single value per row.

• Syntax:

  sql

  Copy

    CREATE OR REPLACE FUNCTION function_name (arguments)
    RETURNS return_type
    AS
    $$
      -- SQL logic using arguments
      SELECT expression;
    $$
    ;
  

• Example:

  sql

  Copy

    CREATE FUNCTION add_tax(price FLOAT)
      RETURNS FLOAT
      AS
      $$
        SELECT price * 1.07; -- Adds 7% tax
      $$
    ;
    -- Usage:
    SELECT add_tax(100); -- Returns 107.0
  

2. JavaScript UDFs

• Scalar: Return a single value using JavaScript logic.

• Syntax:

  sql

  Copy

    CREATE OR REPLACE FUNCTION function_name (arguments)
    RETURNS return_type
    LANGUAGE JAVASCRIPT
    AS
    $$
      // JavaScript logic
      return value;
    $$
    ;
  

• Example:

  sql

  Copy

    CREATE FUNCTION reverse_string(s VARCHAR)
      RETURNS VARCHAR
      LANGUAGE JAVASCRIPT
      AS
      $$
        return S.split("").reverse().join("");
      $$
    ;
    -- Usage:
    SELECT reverse_string('Snowflake'); -- Returns 'ekalfwonS'
  

3. Tabular UDFs (Table Functions)

• Return a table (multiple rows/columns) using the RETURNS TABLE clause.

• Syntax:

  sql

  Copy

    CREATE OR REPLACE FUNCTION function_name (arguments)
    RETURNS TABLE (column1 TYPE, column2 TYPE, ...)
    AS
    $$
      -- SQL query returning a table
      SELECT col1, col2 FROM ...
    $$
    ;
  

• Example:

  sql

  Copy

    CREATE FUNCTION get_employees(dept VARCHAR)
      RETURNS TABLE (id INTEGER, name VARCHAR)
      AS
      $$
        SELECT employee_id, name 
        FROM employees 
        WHERE department = dept
      $$
    ;
    -- Usage:
    SELECT * FROM TABLE(get_employees('Sales'));
  

4. Secure UDFs

• Protect the function’s logic from being viewed by unauthorized users.

• Add the SECURE keyword during creation:

  sql

  Copy

    CREATE SECURE FUNCTION secure_function(...)
  

---

Key Features

1. Overloading: Multiple UDFs can share the same name but differ in arguments.

2. Dependencies: Use SHOW FUNCTIONS or DESCRIBE FUNCTION to view dependencies.

3. Error Handling: JavaScript UDFs can include try/catch blocks.

---

When to Use UDFs

• Reusable Logic: Encapsulate frequently used calculations (e.g., tax, discounts).

• Complex Transformations: String manipulation, JSON parsing, or custom aggregations.

• Cross-Table Logic: Simplify joins or filters repeated across queries.

• Data Validation: Enforce business rules (e.g., email format checks).

---

Best Practices

1. Optimize for Performance:

   • Use SQL UDFs for better performance (Snowflake optimizes them).

   • Avoid JavaScript UDFs for large datasets (slower execution).

2. Avoid Nested UDFs: Deeply nested UDFs can complicate debugging.

3. Use Scalar UDFs Sparingly: They process rows individually, which can be slow.

4. Leverage Caching: Snowflake caches UDF results for repeated inputs.

---

Limitations

• JavaScript UDFs cannot execute external APIs or access the file system.

• UDFs cannot perform DML operations (use stored procedures instead).

• Tabular UDFs cannot be nested in subqueries.

---

Example: Combined SQL and JavaScript UDF

sql

Copy

-- SQL UDF to calculate discounted price
CREATE FUNCTION apply_discount(price FLOAT, discount_rate FLOAT)
  RETURNS FLOAT
  AS
  $$
    SELECT price * (1 - discount_rate)
  $$
;

-- JavaScript UDF to generate a UUID
CREATE FUNCTION generate_uuid()
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS
  $$
    return Math.random().toString(36).substring(2, 15);
  $$
;

-- Usage in a query:
SELECT 
  product_id,
  apply_discount(price, 0.1) AS discounted_price,
  generate_uuid() AS uuid
FROM products;

---

---