DAA-C01 Question 196
Single answerAsynchronous Stored ProcedureA data analyst team uses a JavaScript stored procedure to orchestrate daily KPI calculations in Snowflake. The procedure launches several independent SQL statements to build temporary intermediate tables for different business units, and the team wants these statements to run in parallel to reduce total runtime. They also need the procedure to wait until all parallel work finishes before returning a final status message. Which approach should the team use?
- A
Within the JavaScript stored procedure, create Statement objects and call executeAsync() for each independent SQL statement, then use the returned query IDs to monitor completion before returning.
- B
Use CALL WITH to invoke the stored procedure asynchronously from SQL; all SQL statements inside the procedure will then run in parallel automatically.
- C
Wrap the SQL statements in a single transaction and call execute() repeatedly; Snowflake will detect independent statements and parallelize them inside the transaction.
- D
Convert the stored procedure to a SQL UDF and use multiple SELECT statements inside the UDF body, because UDFs support asynchronous execution more efficiently than stored procedures.
Show answer and explanation
Correct answer: A
Explanation
For Snowflake stored procedure orchestration, the practical pattern for parallel work is to use a JavaScript stored procedure and launch asynchronous child jobs with executeAsync() for independent SQL statements. The procedure can then monitor those child jobs and wait until they complete before returning a status. This is useful when multiple transformations or intermediate table builds do not depend on one another. By contrast, standard execute() is synchronous, procedure invocation syntax does not automatically parallelize internal logic, and UDFs are not designed for procedural orchestration. This aligns with Snowflake documentation and best practices for JavaScript stored procedures and asynchronous child jobs: use asynchronous execution only where statements are truly independent, and explicitly manage completion before consuming results or signaling success.
- A. Correct.
Correct. In Snowflake JavaScript stored procedures, asynchronous child jobs are created by calling executeAsync() on Statement objects. This is the supported way to start independent SQL statements without blocking on each one immediately. To ensure the procedure does not finish too early, the procedure should track the returned query IDs (or child job handles) and check for completion before returning a final result. This matches the requirement to run independent work in parallel and then wait for all jobs to finish.
- B. Incorrect.
Incorrect. CALL WITH can be used to invoke procedures with named arguments, but it does not make the procedure's internal SQL statements run asynchronously or in parallel. Asynchronous execution inside a stored procedure requires explicit use of the supported asynchronous child job pattern in JavaScript stored procedures rather than relying on the procedure invocation syntax.
- C. Incorrect.
Incorrect. Calling execute() runs each statement synchronously. Even if the statements are independent, Snowflake does not automatically parallelize sequential execute() calls inside a stored procedure just because they are in the same transaction. In fact, wrapping everything in one transaction can add unnecessary constraints and does not provide asynchronous behavior.
- D. Incorrect.
Incorrect. A SQL UDF is not a replacement for a stored procedure when orchestration, DDL/DML execution, or asynchronous child job management is required. UDFs are intended for returning computed values within SQL expressions and do not provide the procedural control flow and asynchronous statement execution capabilities needed for this scenario.