Stored Procedure - Language Selection SQL vs Javascript

 Choosing between LANGUAGE JAVASCRIPT and LANGUAGE SQL (Snowflake Scripting) for stored procedures depends on what you’re trying to accomplish.

Use SQL scripting when:

• You're mostly executing SQL statements.

• You want clean, readable procedures.

• You're doing DML (INSERT, UPDATE, DELETE).

Use JavaScript when:

• You need complex procedural logic not easily expressed in SQL.

• You want to build dynamic SQL more flexibly.

• You want to loop through result sets or arrays.

Snowflake - Stored Procedures [ Error free script - ready to use]

 1. Basic Stored Procedure

create or replace procedure proc_return_str()

  RETURNS VARCHAR

  LANGUAGE javascript

  EXECUTE AS CALLER

  AS '

  return "Hello from the snowflake stored procedure!";

  ';

call proc_return_str();

2. Stored Procedure with Parameters 

CREATE OR REPLACE PROCEDURE proc_withparampass(message VARCHAR)

RETURNS VARCHAR

LANGUAGE SQL

AS

$$

BEGIN

  RETURN 'Hello from Snowflake! You passed: ' || message;

END;

$$;

CALL proc_withparampass('Snowflake Stored Procedures');

3. Stored Procedures with Update SQL statement

CREATE OR REPLACE PROCEDURE apply_bonus(bonus_percentage DECIMAL(5, 2), min_rating INT)

RETURNS VARCHAR

LANGUAGE SQL

AS

$$

DECLARE

  rows_updated INT;

BEGIN

  UPDATE employees

  SET tot_salary = salary * (1 + :bonus_percentage / 100)

  WHERE performance_rating >= :min_rating;

  rows_updated := SQLROWCOUNT;

  RETURN 'Bonus applied to ' || rows_updated || ' employees.';

END;

$$;

CALL apply_bonus(5, 5);

4. Stored Procedures with Return Output as Table

CREATE OR REPLACE PROCEDURE proc_return_table(min_salary DECIMAL(10, 2))

RETURNS TABLE(emp_id INT, salary DECIMAL(10, 2))

LANGUAGE SQL

AS

$$

DECLARE

  res RESULTSET;

BEGIN

  res := (SELECT emp_id, salary FROM employees WHERE salary >= :min_salary ORDER BY salary DESC);

  RETURN TABLE(res);

END;

$$;

CALL proc_return_table(70000);

5. Stored Procedures with Insert SQL statement

CREATE OR REPLACE PROCEDURE proc_calc_writein_insertinothertable(

    start_date DATE,

    end_date DATE,

    in_region STRING

)

RETURNS STRING

LANGUAGE SQL

AS

$$

DECLARE

    v_total_sales NUMBER;

    v_total_orders NUMBER;

    v_avg_order_value NUMBER;

BEGIN

    INSERT INTO SALES_SUMMARY_LOG (

        RUN_TIMESTAMP, SALES_REGION, START_DATE, END_DATE,

        TOTAL_SALES, TOTAL_ORDERS, AVERAGE_ORDER_VALUE, STATUS

    )

    SELECT CURRENT_TIMESTAMP,:in_region, :start_date, :end_date,

        SUM(AMOUNT) AS v_total_sales, COUNT(*) AS v_total_orders, AVG(AMOUNT) AS v_avg_order_value

        ,'SUCCESS'

    FROM SALES

    WHERE ORDER_DATE BETWEEN :start_date AND :end_date

      AND SALES_REGION = :in_region;

      

    RETURN 'Summary calculation completed successfully for region: ' || :in_region ;

EXCEPTION

    WHEN OTHER THEN

        INSERT INTO SALES_SUMMARY_LOG (

            RUN_TIMESTAMP, SALES_REGION, START_DATE, END_DATE,

            TOTAL_SALES, TOTAL_ORDERS, AVERAGE_ORDER_VALUE, STATUS

        )

        VALUES (

            CURRENT_TIMESTAMP, :in_region, :start_date, :end_date,

            NULL, NULL, NULL, 'FAILED'

        );

        RETURN 'An error occurred during summary calculation.';

END;

$$;

CALL proc_calc_writein_insertinothertable('2025-01-02', '2025-01-02', 'West');

6. Stored Procedures with Calculation

Eg: 1

CREATE OR REPLACE PROCEDURE proc_simple_num_calc()

RETURNS NUMBER

LANGUAGE SQL

AS

$$

DECLARE

    profit NUMBER(38, 2) DEFAULT 0.0; -- Variable declared with a default value

    cost NUMBER(38, 2);              -- Variable declared without a default value (will be NULL initially)

BEGIN

    cost := 100.0;

    LET revenue NUMBER(38, 2) DEFAULT 110.0; -- LET can also be used inside BEGIN...END

    profit := revenue - cost;

    RETURN profit;

END;

$$;

CALL proc_simple_num_calc();

Eg: 2

CREATE OR REPLACE PROCEDURE calculate_revenue()

RETURNS NUMBER

LANGUAGE SQL

AS

$$

BEGIN

    LET sales_amount NUMBER(38, 2) := 500.0; -- Declared and initialized using :=

    LET discount_rate NUMBER(38, 2) DEFAULT 0.10; -- Declared and initialized using DEFAULT

    LET net_revenue NUMBER(38, 2);

    net_revenue := sales_amount * (1 - discount_rate);

    RETURN net_revenue;

END;

$$;

CALL calculate_revenue();

7. Stored Procedure with Cursor & Get count of a table:

CREATE OR REPLACE PROCEDURE proc_cursor_getcountofthetable(table_name STRING)

RETURNS INT

LANGUAGE SQL

AS

$$

DECLARE

  sql_text STRING;

  rs RESULTSET;

  row_count INT DEFAULT 0;

  C1 CURSOR FOR SELECT COUNT(*) FROM customer_src;

BEGIN

  OPEN C1;

  FETCH C1 INTO row_count;

  CLOSE C1;

  IF (row_count > 4) THEN

    RETURN 'Success';

  ELSE

    RETURN 'Failed';

  END IF;

END;

$$;

CALL proc_cursor_getcountofthetable('customer_src');

8. Stored Procedure with Execute Immediate & Dynamic SQL:

CREATE OR REPLACE PROCEDURE proc_with_ex_immediate_dynamic_sql(

    minimum_price NUMBER(12,2),

    maximum_price NUMBER(12,2))

  RETURNS TABLE (order_id NUMBER(38,0), SALES_REGION VARCHAR, ORDER_DATE DATE, amount NUMBER(38, 0))

--Give all the columns from the table, with exact data type and sequence  

  LANGUAGE SQL

AS

$$

DECLARE

  rs RESULTSET;

  query VARCHAR DEFAULT 'SELECT * FROM SALES WHERE amount > ? AND amount < ?';

BEGIN

  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));

  RETURN TABLE(rs);

END;

$$

;

CALL proc_with_ex_immediate_dynamic_sql(1000,1500);

DBT - Models

Models are where your developers spend most of their time within a dbt environment. Models are primarily written as a select statement and saved as a .sql file. While the definition is straightforward, the complexity of the execution will vary from environment to environment. Models will be written and rewritten as needs evolve and your organization finds new ways to maximize efficiency.

SQL is the language most dbt users will utilize, but it is not the only one for building models. Starting in version 1.3, dbt Core and dbt Cloud support Python models. Python models are useful for training or deploying data science models, complex transformations, or where a specific Python package meets a need — such as using the dateutil library to parse dates.

DBT - Models and modern workflows

The top level of a dbt workflow is the project. A project is a directory of a .yml file (the project configuration). The project file tells dbt the project context, and the models let dbt know how to build a specific data set.

DBT - Jinja

SQL files can contain Jinja, a lightweight templating language. Using Jinja in SQL provides a way to use control structures in your queries. For example, if statements and for loops. It also enables repeated SQL to be shared through macros.SQL files can contain Jinja, a lightweight templating language. Using Jinja in SQL provides a way to use control structures in your queries. For example, if statements and for loops. It also enables repeated SQL to be shared through macros.

DBT Core vs. DBT Cloud

dbt is offered through two interfaces: dbt Core and dbt Cloud.

dbt Core is an open-source library that implements most of the functionality of dbt. It has a command-line interface (the dbt command you will come to love) that you can use to manage data transformations in your projects.

dbt Cloud is an enterprise solution for teams. On top of the CLI, dbt Cloud also provides a more user-friendly web-based IDE. With it, you don’t have to worry about database connections and editing YAML files so much (as you will see in the coming sections).

dbt Cloud also offers additional features like job scheduling, advanced integrations and high priority support.

What is DBT

Data Build Tool or dbt is built to transform data, and is therefore, the T in an ELT pipeline. I mentioned ELT because it is designed to work after data has been loaded, and is ready for transformation. Additionally, out of the box, it cannot connect with multiple databases, and depends on data that has been loaded, or otherwise accessible to the target database executing the dbt steps.