COF-C03 Question 217
Single answerExternal stagesA data engineering team loads daily CSV files from an Amazon S3 bucket into Snowflake. The files are stored in a path such as s3://company-ingest/sales/daily/. To meet security requirements, the team must avoid embedding long-term cloud credentials directly in ETL scripts, and they want to reuse the same S3 location across many COPY INTO commands. Which approach best meets these requirements?
- A
Create an external stage that points to the S3 URL and configure it to use a storage integration, then reference the stage in COPY INTO commands.
- B
Create an internal named stage and use PUT to upload the S3 files into Snowflake before each load.
- C
Use COPY INTO with the S3 URL directly in every command and hard-code the AWS access key ID and secret access key in each script.
- D
Create a temporary table that stores the S3 bucket path and let Snowflake automatically resolve cloud credentials during COPY INTO execution.
Show answer and explanation
Correct answer: A
Explanation
For recurring loads from cloud object storage, Snowflake best practice is to create a named external stage and associate it with a storage integration. External stages provide a reusable reference to the external location, including the URL and optional file format settings. Storage integrations allow Snowflake to securely access cloud storage without exposing long-term credentials in user code. This approach is commonly recommended in Snowflake documentation for secure and maintainable access to S3, Azure Blob Storage, and Google Cloud Storage. While direct COPY INTO from an external URL is possible, embedding credentials inline is less secure and harder to manage. Internal stages are for files stored within Snowflake rather than for directly referencing S3-hosted data.
- A. Correct.
Correct. An external stage is designed to reference data stored outside Snowflake, such as in Amazon S3. Using a storage integration is the recommended approach because it avoids embedding cloud credentials in SQL statements or ETL code, centralizes access management, and allows the same named stage to be reused across many COPY INTO commands. This is the most secure and maintainable option for repeated loads from the same external location.
- B. Incorrect.
Incorrect. An internal named stage stores files inside Snowflake-managed storage and is populated with commands such as PUT, which uploads local files. PUT does not upload directly from S3 into an internal stage. This option adds unnecessary movement of data and does not match the requirement to reuse an external S3 location directly.
- C. Incorrect.
Incorrect. Snowflake does support loading directly from an S3 URL in a COPY INTO statement, and credentials can be specified inline. However, hard-coding AWS keys in ETL scripts is specifically what the team wants to avoid. This approach is less secure and less maintainable than using a storage integration with an external stage.
- D. Incorrect.
Incorrect. A table cannot be used to provide cloud authentication for external data access. Snowflake does not automatically discover credentials from a table value during COPY INTO execution. Access to S3 for external stages or direct external loads must be configured through supported credential mechanisms such as storage integrations or, in some cases, explicit credentials.