ARA-C01 Question 324
Single answerSQL APIA software company is building a stateless microservice that submits ad hoc SQL requests to Snowflake by using the Snowflake SQL API. Some requests may run for several minutes, and the microservice platform can restart containers at any time. The architects need a design that prevents duplicate execution after a restart and allows the service to retrieve results later without keeping the original HTTP connection open. Which approach best meets these requirements?
- A
Generate a unique requestId for each logical SQL request, submit the statement through the SQL API with that requestId, and after a restart use the same requestId to safely retry and then poll for completion/results by statement handle.
- B
Use a larger warehouse and synchronous SQL API calls so the original HTTP request is less likely to time out, eliminating the need to handle retries after a restart.
- C
Wrap each SQL statement in an explicit transaction and rely on transaction rollback to prevent duplicate execution if the microservice resubmits the same statement after a restart.
- D
Open a single persistent session for all microservice instances and cache the session token centrally so any instance can reconnect and fetch the result set without tracking statement handles.
Show answer and explanation
Correct answer: A
Explanation
For the Snowflake SQL API, the most appropriate pattern for resilient, stateless services is to use a client-generated requestId for idempotency and the returned statement handle for asynchronous polling and result retrieval. This helps prevent accidental duplicate execution when the client retries after process restarts, timeouts, or transient network failures. It also aligns with the SQL API model, where clients can submit work and later query status/results without preserving the original connection. Snowflake documentation for the SQL API describes request deduplication through requestId and retrieval of execution status/results using statement handles/partitioned results. Architecturally, this is preferable to trying to depend on long-lived sessions or assuming infrastructure failures will not occur.
- A. Correct.
Correct. The Snowflake SQL API supports idempotent request handling by using a client-supplied requestId. This is the key mechanism for a stateless service that may need to retry safely after a network issue or process restart. The SQL API also returns a statement handle that can be used to check execution status and retrieve results later, which fits asynchronous processing without requiring the original HTTP connection to remain open.
- B. Incorrect.
Incorrect. Increasing warehouse size may reduce execution time for some workloads, but it does not solve the architectural problem of duplicate submission after a restart. The SQL API design should assume failures and retries can happen regardless of warehouse size. Synchronous request handling also does not remove the need for idempotency or later result retrieval.
- C. Incorrect.
Incorrect. Transactions help control commit/rollback behavior for a unit of work, but they do not provide request-level deduplication for API retries. If a statement is resubmitted after a restart, Snowflake still needs a way to recognize it as the same logical request. That is what requestId is for. Also, many ad hoc API requests may be single statements where transaction wrapping does not address duplicate execution risk.
- D. Incorrect.
Incorrect. Designing around a shared persistent session is not the best fit for a stateless, restart-prone microservice. It creates operational and security complexity and does not remove the need to track query execution independently. The SQL API is designed to support asynchronous execution tracking through statement handles rather than requiring a long-lived shared session model.