ARA-C01 Question 323
Single answerSQL APIA company is building a serverless integration that submits ad hoc SQL statements to Snowflake from an external application using the Snowflake SQL API. The application cannot maintain long-lived database connections, and the architects want each request to be self-contained, securely authenticated, and easy to correlate in Snowflake history for troubleshooting. Which approach best meets these requirements?
- A
Use the SQL API with key pair authentication, send the SQL statement in the request body, and include a requestId so the application can safely retry and correlate requests.
- B
Use the SQL API only with username and password authentication, and rely on the TCP session to preserve context between retries.
- C
Use the SQL API with OAuth, but require the application to open a persistent JDBC connection so multi-statement execution can be tracked across requests.
- D
Use the SQL API with external browser authentication, and omit request identifiers because Snowflake automatically deduplicates repeated POST requests.
Show answer and explanation
Correct answer: A
Explanation
For a serverless or event-driven application, the Snowflake SQL API is appropriate because it allows SQL execution over HTTPS without a persistent connection. In this scenario, the architecture requires self-contained requests and secure non-interactive authentication, which makes key pair authentication a strong fit. A requestId should be included so retries can be handled safely and requests can be correlated during troubleshooting. This is particularly valuable when dealing with transient failures or network interruptions in distributed systems. Snowflake documentation for the SQL API emphasizes stateless request patterns, support for programmatic authentication methods such as key pair authentication and OAuth, and mechanisms for polling and correlating statement execution. By contrast, options that depend on persistent JDBC sessions or interactive browser-based authentication do not align with SQL API design or with a serverless integration pattern.
- A. Correct.
Correct. The Snowflake SQL API is designed for stateless, HTTPS-based execution of SQL without requiring a persistent client connection. Key pair authentication is a supported authentication method for programmatic access and is well suited to server-to-server integrations. Including a requestId is important for idempotent retry handling and request correlation. This aligns with best practices for applications that may need to retry requests due to network interruptions or transient failures.
- B. Incorrect.
Incorrect. The SQL API is not limited to username/password authentication; supported authentication methods include key pair authentication and OAuth. Also, relying on a TCP session to preserve context contradicts the SQL API's stateless design. The scenario explicitly states the application cannot maintain long-lived connections, so this approach does not fit the requirement.
- C. Incorrect.
Incorrect. OAuth can be used with the SQL API, but the statement about requiring a persistent JDBC connection is wrong. JDBC is a separate connectivity mechanism from the SQL API. The SQL API is specifically intended for HTTPS request/response interactions without maintaining a database session through a persistent driver connection. Tracking execution across requests is handled through API constructs such as handles and request identifiers, not by forcing JDBC usage.
- D. Incorrect.
Incorrect. External browser authentication is intended for interactive user sign-in flows, not typical serverless service-to-service integrations. Omitting request identifiers is also a mistake because Snowflake does not simply deduplicate repeated POST requests automatically without the client providing the proper request identifier semantics. This option combines an unsuitable authentication pattern with an incorrect assumption about retry behavior.