COF-C03 Question 213
Single answerCreate and use stagesA data engineering team needs to load daily CSV files from an Amazon S3 bucket into a Snowflake table. The security team does not allow hard-coding cloud credentials in SQL statements, and the engineers want a reusable object that can be referenced by multiple COPY INTO commands over time. Which approach should the team use?
- A
Create an external stage that points to the S3 bucket and uses a storage integration, then reference that stage in COPY INTO commands.
- B
Create an internal named stage and configure it with the S3 bucket URL so Snowflake can pull files directly from Amazon S3.
- C
Use COPY INTO with an inline S3 URL and embed AWS access keys in the CREDENTIALS clause each time, because named stages cannot be reused across loads.
- D
Create a temporary table that stores the S3 bucket path and file names, then use COPY INTO from that table to avoid creating a stage.
Show answer and explanation
Correct answer: A
Explanation
To load files from Amazon S3 into Snowflake in a secure and reusable way, the team should create an external stage and associate it with a storage integration. External stages reference files in external cloud storage, while internal stages are for files stored within Snowflake. Storage integrations are the recommended approach for delegating secure access to S3 without placing credentials directly in SQL statements. This design supports repeated use across multiple COPY INTO commands and aligns with Snowflake best practices for staged data loading. Relevant Snowflake documentation includes the sections on CREATE STAGE, external stages, storage integrations, and loading data from Amazon S3 using COPY INTO.
- A. Correct.
Correct. An external stage is the appropriate Snowflake object for referencing files stored in cloud storage such as Amazon S3. Using a storage integration is a best practice because it avoids embedding cloud credentials in SQL and provides a reusable, governed way to access the bucket. The stage can then be reused by multiple COPY INTO commands.
- B. Incorrect.
Incorrect. Internal named stages store files inside Snowflake-managed storage, not in an external S3 bucket. You cannot configure an internal stage with an S3 URL for Snowflake to read directly from Amazon S3. This option confuses internal stages with external stages.
- C. Incorrect.
Incorrect. COPY INTO can read directly from an S3 URL with inline credentials, but this does not meet the requirement to avoid hard-coded credentials. It is also incorrect that named stages cannot be reused; reusability is one of their main benefits.
- D. Incorrect.
Incorrect. COPY INTO loads data from files in stages or directly from supported cloud locations, not from a table containing file paths. A temporary table does not replace a stage and would not provide Snowflake with file access for loading.