SnowPro Associate: Platform Question 35
Single answer○ PythonA data engineering team needs to load daily JSON files from Amazon S3 into Snowflake using a Python application. The team wants to avoid embedding usernames and passwords in code and also wants Snowflake to manage the cloud credentials used to read the files from S3. Which approach best meets these requirements?
- A
Use the Snowflake Connector for Python with key-pair authentication for the application, and create an external stage that uses a storage integration for S3 access.
- B
Use the Snowflake Connector for Python with basic username/password authentication stored in environment variables, and grant the Python user direct access to the S3 bucket.
- C
Use Snowpark for Python with browser-based SSO for every automated run, and create an internal stage that points to the S3 bucket.
- D
Use the Python application to download files from S3 locally with AWS access keys, then upload them to Snowflake with PUT so Snowflake does not need cloud access.
Show answer and explanation
Correct answer: A
Explanation
For Python-based automation with Snowflake, the Snowflake Connector for Python is commonly used to execute SQL statements such as COPY INTO commands. For secure non-interactive authentication, key-pair authentication is a recommended approach because it avoids embedding passwords in code. For loading data from Amazon S3, Snowflake best practice is to use an external stage with a storage integration. Storage integrations allow Snowflake to securely reference cloud storage without exposing AWS keys in application code or in stage definitions. This aligns with Snowflake guidance on securing client connections and using storage integrations for external cloud storage access.
- A. Correct.
Correct. The Snowflake Connector for Python supports key-pair authentication, which is a strong option for automated workloads because it avoids hard-coding passwords. For S3 access, a storage integration is the Snowflake best practice because it allows Snowflake to access external cloud storage without embedding cloud credentials in stage definitions or application code. This approach satisfies both security requirements in the scenario.
- B. Incorrect.
Incorrect. While environment variables are better than hard-coding credentials directly in source files, this still relies on username/password authentication rather than a stronger non-password option such as key-pair authentication. In addition, 'granting the Python user direct access to the S3 bucket' is not how Snowflake is typically configured to access external cloud storage. Snowflake should use a storage integration or explicitly defined cloud credentials in the stage, with storage integration being the preferred method.
- C. Incorrect.
Incorrect. Browser-based SSO is generally not appropriate for unattended scheduled automation because it requires interactive authentication. Also, an internal stage cannot point to an S3 bucket; internal stages are Snowflake-managed storage locations. External cloud storage such as S3 is accessed through an external stage.
- D. Incorrect.
Incorrect. This can work technically in some workflows, but it does not meet the stated requirement that Snowflake manage the cloud credentials used to read files from S3. In this option, the Python application manages AWS credentials and transfers files itself. It also introduces unnecessary movement of data through the client machine, which is less efficient than letting Snowflake load directly from an external stage.