SnowPro Associate: Platform Question 191
Single answer● StagesA data engineering team loads daily CSV files from an Amazon S3 bucket into Snowflake. The files contain sensitive customer data and must be loaded by multiple ETL processes across different schemas. The team wants a reusable stage definition so they do not need to repeat the S3 URL and credentials in every load command. They also want to apply a named file format consistently. Which approach best meets these requirements?
- A
Create a named external stage that references the S3 bucket and the named file format, then use COPY INTO from that stage in each ETL process.
- B
Create a table stage for each target table and configure it with the S3 bucket URL and named file format so COPY INTO can reuse the definition.
- C
Create a user stage for the ETL service account and grant all schemas access to that user stage for shared loading.
- D
Use the PUT command to upload the S3 files into an internal named stage, because PUT can transfer files directly from S3 into Snowflake stages.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to create a named external stage. In Snowflake, stages are objects used to reference file locations for data loading and unloading. External stages are specifically used for cloud storage locations such as Amazon S3, Google Cloud Storage, and Azure Blob Storage. A named stage is reusable and can include a FILE_FORMAT parameter so COPY INTO statements remain consistent and easier to manage. This is a best practice for maintainability and security, especially when multiple ETL jobs need to load from the same location. By contrast, table stages and user stages are internal stages and do not point to S3. Also, PUT is only for uploading local files to internal stages, not for ingesting directly from cloud object storage. Snowflake documentation on CREATE STAGE, internal versus external stages, and PUT/COPY behavior supports this design.
- A. Correct.
Correct. A named external stage is designed for reusable access to external cloud storage such as Amazon S3. It can store the storage location, access credentials or storage integration, and optionally a named file format. This allows multiple COPY INTO commands across schemas and processes to reference the same stage definition without repeating connection details.
- B. Incorrect.
Incorrect. Table stages are internal stages automatically associated with a specific table. They do not reference external locations like S3 and are not intended as reusable shared definitions across multiple schemas. This option confuses internal table stages with external named stages.
- C. Incorrect.
Incorrect. User stages are internal stages tied to a specific Snowflake user. They are not intended to provide shared access across multiple schemas and do not define external S3 locations. This is a common misconception because user stages are convenient for personal file loading, not shared production ingestion.
- D. Incorrect.
Incorrect. PUT uploads files from a local client machine to an internal stage in Snowflake. It does not pull files directly from Amazon S3 into Snowflake. For S3-based loading, Snowflake uses external stages and COPY INTO, typically with credentials or a storage integration.