COF-C03 Question 212
Single answerCreate and use stagesA data engineering team loads daily CSV files from an Amazon S3 bucket into a Snowflake table. Security policy requires that long-term cloud credentials are not embedded in SQL statements, and operations wants the stage definition to be reusable across multiple COPY INTO commands. Which approach should the team use to meet these requirements?
- A
Create an external stage that references the S3 URL and a named STORAGE INTEGRATION, then use that stage in COPY INTO commands.
- B
Create an internal named stage and point it to the S3 bucket URL so Snowflake can read the files without additional configuration.
- C
Use COPY INTO directly from the S3 URL each time and include AWS_KEY_ID and AWS_SECRET_KEY in the statement so the credentials are explicit.
- D
Create a temporary table stage and attach the S3 bucket credentials to the table so all sessions can reuse it.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to create an external stage that points to the Amazon S3 location and uses a named STORAGE INTEGRATION. In Snowflake, stages are database objects used to reference data files for loading and unloading. Internal stages are for Snowflake-managed storage, while external stages reference cloud storage locations such as S3, Azure Blob Storage, or Google Cloud Storage. A storage integration allows Snowflake to securely delegate authentication to the cloud provider without hard-coding credentials in SQL statements. This improves security, simplifies administration, and supports reuse across multiple COPY INTO operations. Snowflake documentation for CREATE STAGE, external stages, and storage integrations identifies storage integrations as the recommended best practice for secure access to external cloud storage.
- A. Correct.
Correct. An external stage is used to reference files stored in external cloud storage such as Amazon S3. Using a named STORAGE INTEGRATION is the recommended approach because it avoids embedding cloud credentials in SQL and provides a reusable object that can be referenced by multiple COPY INTO commands. This aligns with Snowflake best practices for secure and maintainable access to external storage.
- B. Incorrect.
Incorrect. Internal stages store files inside Snowflake-managed storage. They do not point directly to an external S3 bucket. To access S3 files, the team must use an external stage, not an internal named stage.
- C. Incorrect.
Incorrect. While COPY INTO can reference an S3 location directly, embedding AWS credentials in statements violates the stated security requirement and is not the preferred long-term design. This approach is less secure and less reusable than creating an external stage with a storage integration.
- D. Incorrect.
Incorrect. There is no concept of a temporary table stage that can be configured to directly use an S3 bucket with attached cloud credentials for broad reuse. Table stages are implicit internal stages associated with tables and are not designed as reusable external S3 access definitions.