Snowflake Scripting provides procedural SQL blocks for encapsulating data transformations in stored procedures. A procedure can be invoked as one unit by a task or another orchestration mechanism instead of repeatedly issuing individual statements. section 4.3
A Snowflake Scripting-based procedure can combine:
- SQL transformations such as merging staged records into a target table. section 4.3
- Programming logic, including branching, looping, exception handling, and logging. section 4.3
- Scheduled or dependency-aware execution through Snowflake tasks. section 1.1.2
Encapsulating an order load
A stored procedure named LOAD_CUSTOMER_ORDERS can encapsulate a MERGE from ORDERS_COMBINED_STG into CUSTOMER_ORDERS_COMBINED, updating matched orders and inserting new ones. A task or other orchestrator can then call the procedure when new staged data arrives. section 4.3
Listing 4.5 Merging customer orders from the staging table into the target table
merge into CUSTOMER_ORDERS_COMBINED tgt
using ORDERS_COMBINED_STG as src
on src.customer = tgt.customer
and src.delivery_date = tgt.delivery_date
and src.baked_good_type = tgt.baked_good_type
when matched then
update set tgt.quantity = src.quantity,
tgt.source_file_name = src.source_file_name,
tgt.load_ts = current_timestamp()
when not matched then
insert (customer, order_date, delivery_date,
baked_good_type, quantity, source_file_name, load_ts)
values(src.customer, src.order_date, src.delivery_date,
src.baked_good_type, src.quantity, src.source_file_name,
current_timestamp());
Inspect the executable MERGE baseline that the stored procedure encapsulates, including match conditions, updates, inserts, and load timestamps. section 4.3
