ADA-C01 Question 243
Select 23.3 Given a scenario, stage data in Snowflake.A retail company receives hourly CSV files from an external partner in Amazon S3. The files must be loaded into Snowflake within minutes of arrival. Security policy requires that Snowflake access only a specific S3 path and that cloud credentials are not embedded in user-managed scripts. The administrator also wants to validate file formatting before production loads begin. Which TWO actions should the administrator take to meet these requirements?
- A
Create a STORAGE INTEGRATION for Amazon S3, then create an external stage that references the integration and the specific S3 URL/prefix.
- B
Create an internal named stage and use PUT to upload the files from the S3 bucket into Snowflake.
- C
Define a reusable FILE FORMAT object for the partner's CSV structure and associate it with the external stage or COPY INTO command.
- D
Embed the AWS access key ID and secret key directly in the external stage definition so Snowflake can read from S3 without additional objects.
- E
Create a temporary table first, because Snowflake requires staged files to be associated with a table before their format can be validated.
Show answer and explanation
Correct answers: A, C
Explanation
The best solution is to use an external stage on Amazon S3 backed by a STORAGE INTEGRATION, plus a reusable FILE FORMAT object for the CSV files. This satisfies the security requirement by avoiding embedded AWS credentials and restricting Snowflake access to the required S3 location. It also supports operational best practices by separating storage access configuration from file parsing logic. Before loading into production tables, the administrator can validate formatting using Snowflake loading features such as COPY INTO ... VALIDATION_MODE or by testing against staged files with the defined file format. Snowflake documentation and best practices for external stages emphasize using STORAGE INTEGRATION for S3 access and FILE FORMAT objects for reusable parsing definitions.
- A. Correct.
Correct. For secure access to Amazon S3 without embedding cloud credentials, Snowflake best practice is to use a STORAGE INTEGRATION. The integration creates a trust relationship between Snowflake and AWS IAM, and the external stage can be scoped to a specific S3 URL or prefix. This aligns with the requirement to limit access to a specific path and avoid storing credentials in scripts or stage definitions.
- B. Incorrect.
Incorrect. PUT uploads files from a local client machine to an internal stage; it does not transfer data directly from an S3 bucket into Snowflake. Because the source files already reside in Amazon S3 and need near-real-time access, an external stage is the appropriate staging mechanism. This option reflects a common misconception that PUT can ingest directly from cloud object storage.
- C. Correct.
Correct. A FILE FORMAT object should be created for the CSV structure so the administrator can standardize parsing rules such as delimiter, header handling, quote handling, and null treatment. This supports validation before production loads, for example by using COPY INTO with VALIDATION_MODE or querying staged files with the defined format. Reusable file formats are a Snowflake best practice for maintainability and consistency.
- D. Incorrect.
Incorrect. Although older patterns allowed cloud credentials in stage definitions, this does not meet the stated security requirement. Embedding AWS keys in stage definitions or scripts increases operational and security risk. Snowflake recommends STORAGE INTEGRATION for external stages because it avoids direct credential management and supports controlled access.
- E. Incorrect.
Incorrect. Snowflake does not require creating a temporary table before validating staged files or defining file formats. File formats can be created independently, and validation can be performed during COPY operations or by inspecting staged data. This option confuses table design with staging and file parsing configuration.