DAA-C01 Question 195
Single answerAsynchronous Stored ProcedureA data analyst team has a JavaScript stored procedure that refreshes several independent summary tables used by BI dashboards. To reduce end-to-end runtime, they want the procedure to submit the refresh SQL statements concurrently and then continue only after all of them finish successfully. Which approach should they use inside the stored procedure?
- A
Create multiple Statement objects, call execute() on each one, and Snowflake will run them in parallel automatically within the procedure session.
- B
Use snowflake.createStatement(...) for each SQL command and submit each one with the asynchronous child job capability, then explicitly wait for each child job to complete before proceeding.
- C
Wrap all refresh statements in a single BEGIN ... END block so Snowflake can optimize the block into parallel execution automatically.
- D
Open multiple database sessions from the JavaScript stored procedure and assign one refresh statement to each session.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use asynchronous child jobs within the stored procedure. In Snowflake, JavaScript stored procedures normally execute statements sequentially. When you need concurrent execution for independent work, such as refreshing multiple summary tables, the supported pattern is to submit those statements as asynchronous child jobs and then explicitly coordinate completion before proceeding. This is practical for data analyst workloads that orchestrate multiple independent refresh tasks.
Why this matters in practice: if the summary table refreshes do not depend on one another, running them asynchronously can reduce wall-clock runtime compared with waiting for each refresh in sequence. However, the procedure should still verify that each child job has completed successfully before downstream steps run, especially if BI dashboards depend on all summary tables being current.
This aligns with Snowflake documentation and best practices around JavaScript stored procedures and asynchronous child jobs: asynchronous execution must be explicitly requested; it is not inferred from having multiple statements or wrapping them in a block. Similarly, stored procedures do not expose a general-purpose pattern for opening multiple separate sessions manually to simulate concurrency.
- A. Incorrect.
Incorrect. Calling execute() on Statement objects runs statements synchronously in the stored procedure flow. Simply creating multiple statements does not make them run concurrently. A common misconception is that independent SQL statements inside a procedure are parallelized automatically, but standard execution is sequential unless asynchronous child jobs are used.
- B. Correct.
Correct. Snowflake supports asynchronous child jobs from stored procedures so a procedure can start multiple independent SQL operations without waiting for each one immediately. This is the right pattern when the work is independent and the procedure must later synchronize by checking or waiting for completion before moving on. This matches the team's requirement to reduce runtime while ensuring all refreshes complete successfully before continuing.
- C. Incorrect.
Incorrect. A procedural or transactional block groups statements logically, but it does not cause Snowflake to execute independent SQL statements in parallel automatically. Candidates may choose this because database engines can optimize SQL, but Snowflake does not parallelize separate statements in a block in the way described here.
- D. Incorrect.
Incorrect. JavaScript stored procedures in Snowflake do not provide a mechanism to manually open and manage multiple independent database sessions from within the procedure for this purpose. The supported approach for parallel work from a stored procedure is asynchronous child jobs, not session management by the procedure author.