Stored Procedures: JavaScript, Python & Transaction Management
Architecture Diagram 1: Stored Procedure Execution Model
┌─────────────────────────────────────────────────────────────────────────────┐
│ STORED PROCEDURE EXECUTION MODEL │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ CLIENT APPLICATION │
│ ══════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ CALL my_procedure('param1', 'param2'); │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Client Driver │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ 1. Parse procedure call │ │ │ │
│ │ │ │ 2. Serialize parameters │ │ │ │
│ │ │ │ 3. Send to Snowflake │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ SNOWFLAKE CLOUD SERVICES │
│ ════════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Query Processing Service │ │ │
│ │ │ │ │ │
│ │ │ 1. Validate procedure exists and user has EXECUTE privilege │ │ │
│ │ │ 2. Parse procedure body (JavaScript/Python/SQL) │ │ │
│ │ │ 3. Check for parameter type mismatches │ │ │
│ │ │ 4. Assign virtual warehouse for execution │ │ │
│ │ │ 5. Route to appropriate compute cluster │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ VIRTUAL WAREHOUSE (Compute) │
│ ════════════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ JavaScript Runtime Environment │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ // JavaScript Procedure Execution │ │ │ │
│ │ │ │ var sql = snowflake.createStatement({ │ │ │ │
│ │ │ │ sqlText: "SELECT * FROM table1", │ │ │ │
│ │ │ │ binds: [param1, param2] │ │ │ │
│ │ │ │ }); │ │ │ │
│ │ │ │ var result = sql.execute(); │ │ │ │
│ │ │ │ while (result.next()) { │ │ │ │
│ │ │ │ // Process each row │ │ │ │
│ │ │ │ } │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ # Python Runtime Environment │ │ │ │
│ │ │ │ # Uses Snowpark for DataFrame operations │ │ │ │
│ │ │ │ from snowflake.snowpark import Session │ │ │ │
│ │ │ │ session = Session.builder.configs({...}).create() │ │ │ │
│ │ │ │ df = session.table("table1") │ │ │ │
│ │ │ │ result = df.filter(col("id") > 10).collect() │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Transaction Manager │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ BEGIN TRANSACTION; │ │ │ │
│ │ │ │ ├── SQL Statement 1 ──▶ Result Set │ │ │ │
│ │ │ │ ├── SQL Statement 2 ──▶ Result Set │ │ │ │
│ │ │ │ ├── SQL Statement 3 ──▶ Error ──▶ ROLLBACK │ │ │ │
│ │ │ │ └── COMMIT (if all succeed) │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ RESULT SET │
│ ═══════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Return Value: │ │ │
│ │ │ • Single value (scalar) │ │ │
│ │ │ • Result set (table) │ │ │
│ │ │ • Both (scalar + result set) │ │ │
│ │ │ • NULL (no return) │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Architecture Diagram 2: Transaction Management Flow
┌─────────────────────────────────────────────────────────────────────────────┐
│ TRANSACTION MANAGEMENT FLOW │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ SCENARIO: ETL Process with Multiple Operations │
│ ═══════════════════════════════════════════════ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ PROCEDURE: load_daily_sales() │ │
│ │ │ │
│ │ Step 1: BEGIN TRANSACTION │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ State: ACTIVE │ │ │
│ │ │ Operations tracked: 0 │ │ │
│ │ │ Locks acquired: None │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Step 2: INSERT INTO staging_sales (100,000 rows) │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ State: ACTIVE │ │ │
│ │ │ Operations tracked: 1 │ │ │
│ │ │ Rows affected: 100,000 │ │ │
│ │ │ Locks: INSERT lock on staging_sales │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Step 3: UPDATE sales_fact SET ... FROM staging_sales │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ State: ACTIVE │ │ │
│ │ │ Operations tracked: 2 │ │ │
│ │ │ Rows affected: 50,000 (matched) + 30,000 (inserted) │ │ │
│ │ │ Locks: UPDATE lock on sales_fact │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Step 4: INSERT INTO audit_log (transaction details) │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ State: ACTIVE │ │ │
│ │ │ Operations tracked: 3 │ │ │
│ │ │ Rows affected: 1 │ │ │
│ │ │ Locks: INSERT lock on audit_log │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Step 5: COMMIT │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ State: COMMITTED │ │ │
│ │ │ Operations committed: 3 │ │ │
│ │ │ All changes made permanent │ │ │
│ │ │ Locks released │ │ │
│ │ │ Visible to other transactions │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ ERROR SCENARIO: │
│ ═══════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Step 3: UPDATE fails (constraint violation) │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ State: FAILED │ │ │
│ │ │ Error: Duplicate key violation │ │ │
│ │ │ Operations tracked: 2 │ │ │
│ │ │ Rows affected by Step 2: NOT YET VISIBLE │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ROLLBACK executed │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ State: ROLLED BACK │ │ │
│ │ │ All operations undone │ │ │
│ │ │ 100,000 rows inserted in Step 2: REVERTED │ │ │
│ │ │ Table restored to pre-transaction state │ │ │
│ │ │ Locks released │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ ISOLATION LEVELS: │
│ ═════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ READ COMMITTED (Default): │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ • Sees only committed data from other transactions │ │ │
│ │ │ • Each statement sees snapshot at statement start │ │ │
│ │ │ • Non-repeatable reads possible │ │ │
│ │ │ • Phantom reads possible │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ SNAPSHOT ISOLATION: │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ • Sees snapshot at transaction start │ │ │
│ │ │ • Consistent reads throughout transaction │ │ │
│ │ │ • No non-repeatable reads │ │ │
│ │ │ • No phantom reads │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Architecture Diagram 3: Error Handling Patterns
┌─────────────────────────────────────────────────────────────────────────────┐
│ ERROR HANDLING PATTERNS │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ PATTERN 1: Basic Error Handling │
│ ═══════════════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ BEGIN │ │ │
│ │ │ -- Main logic │ │ │
│ │ │ INSERT INTO target_table SELECT * FROM source_table; │ │ │
│ │ │ │ │ │
│ │ │ EXCEPTION │ │ │
│ │ │ WHEN OTHER THEN │ │ │
│ │ │ -- Log error │ │ │
│ │ │ INSERT INTO error_log (error_message, timestamp) │ │ │
│ │ │ VALUES (:sqlerrm, CURRENT_TIMESTAMP()); │ │ │
│ │ │ │ │ │
│ │ │ -- Re-raise error │ │ │
│ │ │ RAISE; │ │ │
│ │ │ END; │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ PATTERN 2: Specific Error Handling │
│ ═════════════════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ BEGIN │ │ │
│ │ │ -- Main logic │ │ │
│ │ │ COPY INTO target_table FROM @stage; │ │ │
│ │ │ │ │ │
│ │ │ EXCEPTION │ │ │
│ │ │ WHEN EXPRESSION_ERROR THEN │ │ │
│ │ │ -- Handle data type errors │ │ │
│ │ │ RETURN 'Data type mismatch'; │ │ │
│ │ │ │ │ │
│ │ │ WHEN SQL_NOT_FOUND THEN │ │ │
│ │ │ -- Handle table not found │ │ │
│ │ │ RETURN 'Table does not exist'; │ │ │
│ │ │ │ │ │
│ │ │ WHEN NULL_VALUE_NOT_ALLOWED THEN │ │ │
│ │ │ -- Handle null constraint violations │ │ │
│ │ │ RETURN 'Null value not allowed'; │ │ │
│ │ │ │ │ │
│ │ │ WHEN OTHER THEN │ │ │
│ │ │ -- Handle all other errors │ │ │
│ │ │ RETURN 'Unexpected error: ' || :sqlerrm; │ │ │
│ │ │ END; │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ PATTERN 3: Retry Logic with Exponential Backoff │
│ ═══════════════════════════════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ DECLARE │ │ │
│ │ │ retry_count INTEGER DEFAULT 0; │ │ │
│ │ │ max_retries INTEGER DEFAULT 3; │ │ │
│ │ │ wait_time INTEGER DEFAULT 1000; -- milliseconds │ │ │
│ │ │ │ │ │
│ │ │ BEGIN │ │ │
│ │ │ WHILE (retry_count < max_retries) DO │ │ │
│ │ │ BEGIN │ │ │
│ │ │ -- Attempt operation │ │ │
│ │ │ INSERT INTO target_table SELECT * FROM source_table; │ │ │
│ │ │ RETURN 'Success'; │ │ │
│ │ │ │ │ │
│ │ │ 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 'Failed after ' || max_retries || ' retries'; │ │ │
│ │ │ END; │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
A stored procedure is a named object containing SQL and procedural code (JavaScript, Python, SQL) that can be executed on-demand. It encapsulates complex business logic, data validation, and multi-step operations into reusable units callable from applications, tasks, or other procedures.
Transaction isolation controls how concurrent transactions interact. Snowflake uses READ COMMITTED by default (each statement sees committed data at statement start). SNAPSHOT isolation provides consistent reads throughout a transaction, preventing non-repeatable reads and phantom reads.
Keep procedures focused on single responsibilities. Always include EXCEPTION blocks with specific error handling. Use transactions for multi-step operations to ensure atomicity. Minimize SQL statements — use set-based operations over row-by-row processing.
- Languages: JavaScript (ETL/validation), Python/Snowpark (data science), SQL (simple logic)
- Transactions: BEGIN → execute operations → COMMIT (or ROLLBACK on error)
- Error handling: EXCEPTION blocks with specific error types for targeted recovery
- Retry logic: Exponential backoff for transient failures
- Performance: SQL procedures fastest, JavaScript moderate, Python best for complex data processing
Detailed Explanation
Stored Procedure Fundamentals
Snowflake stored procedures are named objects containing SQL and procedural code that can be executed on-demand. They support multiple languages including JavaScript, Python (via Snowpark), and SQL. Stored procedures enable encapsulation of complex business logic, data validation, and multi-step operations into reusable units that can be called from applications, tasks, or other procedures.
The execution model follows a client-server architecture. When a procedure is called, the client sends the procedure name and parameters to Snowflake's cloud services layer. The services layer validates permissions, parses the procedure body, and routes execution to an assigned virtual warehouse. The warehouse executes the procedural code, which can include SQL statements, loops, conditionals, and error handling.
JavaScript Stored Procedures
JavaScript procedures use Snowflake's built-in JavaScript runtime, which provides a familiar syntax for web developers. The runtime includes the snowflake object for executing SQL statements, resultSet objects for processing query results, and standard JavaScript features like variables, loops, and functions.
JavaScript procedures are particularly effective for ETL operations, data validation, and complex data transformations. They can execute multiple SQL statements, process results procedurally, and implement business logic that would be difficult or impossible in pure SQL. The snowflake.createStatement() method creates SQL statements with parameter binding, while execute() runs them and returns result sets.
Python Stored Procedures (Snowpark)
Python procedures leverage the Snowpark library, which provides a DataFrame API for data manipulation. Snowpark enables Python developers to write data processing logic using familiar pandas-like syntax while executing entirely within Snowflake. This eliminates the need to extract data for processing in external Python environments.
Snowpark procedures can use the full Python ecosystem, including NumPy, pandas, scikit-learn, and custom libraries. The Session object provides connection management and DataFrame operations, while session.table() creates DataFrames from existing tables. Snowpark automatically optimizes DataFrame operations and translates them into efficient SQL execution plans.
Transaction Management
Snowflake supports transaction management within stored procedures using BEGIN, COMMIT, and ROLLBACK statements. Transactions ensure that multiple operations execute atomically—all succeed or all fail. This is critical for data consistency in ETL processes, where partial updates could leave data in an inconsistent state.
Transaction isolation levels control how concurrent transactions interact. Snowflake uses READ COMMITTED isolation by default, where each statement sees only committed data from other transactions. SNAPSHOT isolation provides consistent reads throughout a transaction, preventing non-repeatable reads and phantom reads.
Error Handling Patterns
Robust stored procedures require comprehensive error handling to manage unexpected conditions gracefully. Snowflake provides EXCEPTION blocks that catch errors and execute recovery logic. Specific exception types (EXPRESSION_ERROR, SQL_NOT_FOUND, NULL_VALUE_NOT_ALLOWED) enable targeted handling for different failure modes.
Common error handling patterns include logging errors to audit tables, retrying transient failures with exponential backoff, and returning meaningful error messages to callers. The RAISE statement re-throws caught exceptions, while RETURN exits the procedure with a status value.
Key Concepts Table
| Feature | JavaScript | Python (Snowpark) | SQL |
|---|---|---|---|
| Execution Model | Interpreter | Interpreter | Compiled |
| Data Access | SQL statements | DataFrames | Direct SQL |
| Library Support | Limited | Full Python ecosystem | None |
| Performance | Good | Excellent (optimized) | Best |
| Use Case | ETL, validation | Data science, ML | Simple logic |
| Transaction Operation | Syntax | Description |
|---|---|---|
| BEGIN | BEGIN TRANSACTION | Start transaction |
| COMMIT | COMMIT | Save all changes |
| ROLLBACK | ROLLBACK | Undo all changes |
| SAVEPOINT | SAVEPOINT sp1 | Create checkpoint |
| ROLLBACK TO | ROLLBACK TO sp1 | Restore to checkpoint |
| Exception Type | Common Cause | Typical Response |
|---|---|---|
| EXPRESSION_ERROR | Type mismatch | Validate input types |
| SQL_NOT_FOUND | Missing object | Check object existence |
| NULL_VALUE_NOT_ALLOWED | Constraint violation | Handle NULLs explicitly |
| DUPLICATE_KEY | Unique constraint | Update or skip duplicates |
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
| Metric | Target | Warning | Critical |
|---|---|---|---|
| Procedure Execution Time | < 30s | 30-120s | > 120s |
| SQL Statement Count | < 10 | 10-50 | > 50 |
| Transaction Duration | < 60s | 60-300s | > 300s |
| Error Rate | < 1% | 1-5% | > 5% |
| Retry Success Rate | > 90% | 70-90% | < 70% |
Best Practices
-
Keep procedures focused: Design each procedure for a single responsibility. Avoid monolithic procedures that handle multiple unrelated tasks.
-
Implement comprehensive error handling: Always include EXCEPTION blocks with specific error handling. Log errors for debugging and monitoring.
-
Use transactions for consistency: Wrap multi-step operations in transactions to ensure atomicity. Use SAVEPOINTs for partial rollback scenarios.
-
Validate input parameters: Check parameter types and values at procedure start. Return meaningful errors for invalid inputs.
-
Minimize SQL statements: Reduce the number of SQL statements within procedures. Use set-based operations instead of row-by-row processing.
-
Use appropriate language: Choose JavaScript for ETL and validation, Python for data science and ML, SQL for simple business logic.
-
Implement retry logic: Add retry mechanisms for transient failures. Use exponential backoff to avoid overwhelming systems.
-
Document procedures: Include comments explaining purpose, parameters, and return values. Maintain procedure documentation separately.
-
Test thoroughly: Test procedures with various inputs, including edge cases and error conditions. Verify transaction behavior.
-
Monitor performance: Track execution times, SQL statement counts, and error rates. Optimize slow procedures based on metrics.
See Also
- PySpark Iceberg - Spark stored procedure patterns
- Delta Lake on Databricks - Delta Lake UDF patterns
- Data Warehouse Concepts - Data warehouse design principles