ARA-C01 Question 325
Single answerSnowSQLA data platform team uses SnowSQL in a CI/CD pipeline to promote DDL changes across environments. The pipeline must run non-interactively on a Linux build agent, authenticate securely without embedding user passwords in scripts, and execute a file containing multiple SQL statements. The team also wants the job to fail immediately if any statement in the script fails so that downstream deployment steps do not continue. Which approach best meets these requirements?
- A
Configure key-pair authentication for the SnowSQL user, invoke SnowSQL with the connection details and the SQL file, and enable exit-on-error behavior so the process stops on the first failed statement.
- B
Use username/password authentication stored directly in the shell script, invoke SnowSQL interactively, and review the terminal output at the end of the pipeline to determine whether any statements failed.
- C
Use SSO through a browser-based login for the service account, run the SQL file with SnowSQL, and rely on autocommit being enabled to ensure the process exits immediately on errors.
- D
Store the SQL commands in a worksheet in Snowsight, call SnowSQL only to open the session, and let the CI/CD agent detect failures by checking whether any rows were returned.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use SnowSQL in a fully automated, non-interactive manner with key-pair authentication and a script file, while enabling fail-fast behavior. In practice, SnowSQL supports secure authentication patterns suitable for automation, including key-pair authentication, which is commonly preferred over embedding passwords in scripts. SnowSQL also supports executing SQL from files and provides options/settings to stop execution when an error is encountered so CI/CD tooling can detect failure and halt subsequent stages. This is consistent with Snowflake guidance for SnowSQL automation, secure authentication, and scripting in operational workflows. Relevant Snowflake documentation areas include SnowSQL client usage, authentication methods such as key-pair authentication, and SnowSQL connection/configuration options for batch execution and error handling.
- A. Correct.
Correct. For automated, non-interactive execution, key-pair authentication is a common best practice because it avoids hard-coding passwords and supports service-style access from build agents. SnowSQL can execute a script file containing multiple statements, and enabling exit-on-error behavior ensures the client stops when a statement fails and returns a non-zero exit condition for the pipeline. This aligns with secure automation and predictable deployment failure handling.
- B. Incorrect.
Incorrect. Although SnowSQL can use username/password authentication, embedding passwords in scripts is not a secure approach for CI/CD automation. In addition, interactive execution is unsuitable for unattended pipelines. Manually reviewing output after the run does not satisfy the requirement to fail immediately and prevent downstream steps from continuing automatically.
- C. Incorrect.
Incorrect. Browser-based SSO is generally not appropriate for a headless CI/CD build agent because it requires interactive authentication flow. Also, autocommit controls transaction commit behavior, not whether SnowSQL exits immediately on a failed statement in a script. A candidate might choose this option because SSO is secure for human users, but it is not the best fit for non-interactive service execution.
- D. Incorrect.
Incorrect. Snowsight worksheets are not the right mechanism for automated script execution from SnowSQL in a deployment pipeline. SnowSQL is designed to run SQL directly or from files. Checking whether rows were returned is also not a reliable way to detect DDL failures, since many valid DDL statements do not return result sets in a way that maps to pipeline success criteria.