COF-C03 Question 245
Single answerSnowflake connectorsA data engineering team is building a Python application that loads customer data into Snowflake every 15 minutes. The security team requires that database credentials are not embedded in the code, and the operations team wants the application to minimize repeated authentication overhead during frequent inserts. Which approach should the team use?
- A
Use the Snowflake Connector for Python with a connection definition stored in a connections.toml file and reuse the same connection for multiple operations before closing it
- B
Use SnowSQL inside the Python application for each INSERT statement because SnowSQL automatically pools connections for Python programs
- C
Use the Snowflake Connector for Python and hard-code the username and password in the script so that each execution can reconnect quickly
- D
Use the Python connector, but open and close a new Snowflake session for every row inserted to ensure better performance and stronger security
Show answer and explanation
Correct answer: A
Explanation
For Snowflake-based applications, the correct connector should match the programming language used by the application. In this scenario, the Snowflake Connector for Python is the appropriate choice. Snowflake supports storing connection definitions outside of code, including in a connections.toml file for supported client tooling, which aligns with best practices for avoiding embedded credentials. In addition, repeatedly creating sessions introduces avoidable latency and overhead. Reusing a connection for multiple statements is generally more efficient for frequent operations such as periodic batch loads. SnowSQL is primarily intended as a CLI tool rather than an application connector. This question tests practical understanding of Snowflake connectors, secure credential handling, and efficient session usage consistent with Snowflake client connectivity best practices.
- A. Correct.
Correct. The Snowflake Connector for Python is the appropriate connector for a Python application. Storing connection details in a supported configuration file such as connections.toml helps avoid embedding credentials directly in source code. Reusing a connection for multiple operations reduces the overhead of repeatedly authenticating and creating new sessions, which is more efficient for frequent batch inserts.
- B. Incorrect.
Incorrect. SnowSQL is a command-line client, not the recommended connector for implementing database interactions inside a Python application. It does not serve as a Python connection pooling layer for application code, so this option misrepresents its purpose.
- C. Incorrect.
Incorrect. Hard-coding credentials in application code is a poor security practice and directly violates the stated requirement. Although the Python connector is valid, embedding usernames and passwords in scripts increases operational and security risk.
- D. Incorrect.
Incorrect. Opening and closing a new session for every inserted row adds unnecessary connection and authentication overhead and typically degrades performance. It also does not improve security compared to using secure externalized connection configuration and reusing the session appropriately.