Stored Procedures: JavaScript, Python & Transaction Management

Architecture Diagram 1: Stored Procedure Execution Model

Architecture Diagram 2: Transaction Management Flow

Architecture Diagram 3: SP Execution Architecture

Architecture Diagram 4: Transaction Management Flow

Architecture Diagram 5: Error Handling Patterns

Detailed Explanation

What are Stored Procedures?

Named objects containing SQL and procedural code (JavaScript, Python, SQL) that encapsulate complex business logic into reusable callable units.

Execution Model

• Client → Cloud Services (validate, parse, assign warehouse) → Virtual Warehouse executes
• Supports JavaScript, Python (Snowpark), and SQL languages

JavaScript Stored Procedures

• Built-in snowflake object for SQL statement execution
• snowflake.createStatement() with parameter binding
• Effective for ETL, data validation, complex transformations
• Process results procedurally via resultSet objects

Python Stored Procedures (Snowpark)

• Leverage Snowpark DataFrame API for data manipulation
• Full Python ecosystem: NumPy, pandas, scikit-learn
• Session object for connection management
• Automatic optimization: DataFrame ops → efficient SQL

Transaction Management

• Default isolation: READ COMMITTED
• SNAPSHOT isolation for consistent reads throughout transaction

Error Handling Patterns

1. Basic — WHEN OTHER THEN log + RAISE
2. Specific errors — EXPRESSION_ERROR, SQL_NOT_FOUND, etc.
3. Retry logic — exponential backoff (1s → 2s → 4s → 8s)
4. SAVEPOINT — partial rollback without losing all work

Key Takeaway: Keep procedures focused on single responsibilities. Always include EXCEPTION blocks. Use transactions for multi-step operations.

Key Concepts Table

Code Examples

-- Example 1: Basic JavaScript procedure
CREATE OR REPLACE PROCEDURE get_customer_stats(customer_id VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    var sql = `SELECT COUNT(*) as order_count, 
                      SUM(amount) as total_amount
               FROM orders 
               WHERE customer_id = '${customer_id}'`;
    
    var stmt = snowflake.createStatement({sqlText: sql});
    var result = stmt.execute();
    
    if (result.next()) {
        return `Orders: ${result.getColumnValue(1)}, ` +
               `Total: $${result.getColumnValue(2)}`;
    }
    return 'No data found';
$$;

-- Example 2: JavaScript procedure with transaction
CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account VARCHAR,
    to_account VARCHAR,
    amount NUMBER
)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    try {
        // Begin transaction
        snowflake.createStatement({sqlText: "BEGIN TRANSACTION"}).execute();
        
        // Debit source account
        snowflake.createStatement({
            sqlText: `UPDATE accounts SET balance = balance - ${amount} 
                      WHERE account_id = '${from_account}'`
        }).execute();
        
        // Credit destination account
        snowflake.createStatement({
            sqlText: `UPDATE accounts SET balance = balance + ${amount} 
                      WHERE account_id = '${to_account}'`
        }).execute();
        
        // Commit transaction
        snowflake.createStatement({sqlText: "COMMIT"}).execute();
        
        return 'Transfer successful';
    } catch (err) {
        // Rollback on error
        snowflake.createStatement({sqlText: "ROLLBACK"}).execute();
        return 'Transfer failed: ' + err.message;
    }
$$;

-- Example 3: Python procedure with Snowpark
CREATE OR REPLACE PROCEDURE process_data_snowpark()
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sum_, count

def main(session: Session):
    # Read data
    df = session.table("raw_data")
    
    # Transform data
    result = df.filter(col("status") == "active") \
               .group_by("category") \
               .agg(
                   count("*").alias("record_count"),
                   sum_(col("amount")).alias("total_amount")
               ) \
               .collect()
    
    # Write results
    session.create_dataframe(result).write.mode("overwrite") \
        .save_as_table("processed_data")
    
    return {"status": "success", "rows_processed": len(result)}
$$;

-- Example 4: SQL procedure with error handling
CREATE OR REPLACE PROCEDURE safe_insert(
    table_name VARCHAR,
    data VARIANT
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    error_msg VARCHAR;
BEGIN
    -- Attempt insert
    INSERT INTO IDENTIFIER(table_name) 
    SELECT * FROM TABLE(FLATTEN(input => :data));
    
    RETURN 'Insert successful';
    
EXCEPTION
    WHEN EXPRESSION_ERROR THEN
        RETURN 'Data type mismatch error';
    WHEN OTHER THEN
        error_msg := SQLERRM;
        INSERT INTO error_log (procedure_name, error_message, timestamp)
        VALUES ('safe_insert', :error_msg, CURRENT_TIMESTAMP());
        RETURN 'Insert failed: ' || :error_msg;
END;
$$;

-- Example 5: Procedure with retry logic
CREATE OR REPLACE PROCEDURE retry_operation(
    operation_sql VARCHAR,
    max_retries INTEGER DEFAULT 3
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    retry_count INTEGER DEFAULT 0;
    wait_time INTEGER DEFAULT 1000;
    result VARCHAR;
BEGIN
    WHILE (retry_count < max_retries) DO
        BEGIN
            -- Execute operation
            EXECUTE IMMEDIATE :operation_sql;
            RETURN 'Operation successful';
            
        EXCEPTION
            WHEN OTHER THEN
                retry_count := retry_count + 1;
                IF (retry_count < max_retries) THEN
                    -- Exponential backoff
                    CALL SYSTEM$WAIT(:wait_time);
                    wait_time := wait_time * 2;
                END IF;
        END;
    END WHILE;
    
    RETURN 'Operation failed after ' || :max_retries || ' retries';
END;
$$;

-- Example 6: Call procedure
CALL get_customer_stats('CUST-001');
CALL transfer_funds('ACC-001', 'ACC-002', 1000.00);
CALL process_data_snowpark();
CALL safe_insert('target_table', PARSE_JSON('[{"id": 1, "name": "test"}]'));

Performance Metrics

Best Practices

1. Keep procedures focused: Design each procedure for a single responsibility. Avoid monolithic procedures that handle multiple unrelated tasks.

2. Implement comprehensive error handling: Always include EXCEPTION blocks with specific error handling. Log errors for debugging and monitoring.

3. Use transactions for consistency: Wrap multi-step operations in transactions to ensure atomicity. Use SAVEPOINTs for partial rollback scenarios.

4. Validate input parameters: Check parameter types and values at procedure start. Return meaningful errors for invalid inputs.

5. Minimize SQL statements: Reduce the number of SQL statements within procedures. Use set-based operations instead of row-by-row processing.

6. Use appropriate language: Choose JavaScript for ETL and validation, Python for data science and ML, SQL for simple business logic.

7. Implement retry logic: Add retry mechanisms for transient failures. Use exponential backoff to avoid overwhelming systems.

8. Document procedures: Include comments explaining purpose, parameters, and return values. Maintain procedure documentation separately.

9. Test thoroughly: Test procedures with various inputs, including edge cases and error conditions. Verify transaction behavior.

10. Monitor performance: Track execution times, SQL statement counts, and error rates. Optimize slow procedures based on metrics.

See Also

• Access Control — Procedure security and access control
• Snowpipe Ingestion — Automated ingestion procedures
• Optimization Techniques — Procedure optimization
• Time Travel & Data Cloning — Automated recovery procedures
• PySpark Iceberg Tables — Spark stored procedure patterns
• Data Warehouse Concepts — Data warehouse design principles