SnowPro Associate: Platform Question 59
Single answer○ Create objects (for example: databases, schemas, stages)A data engineering team needs to load CSV files from an Amazon S3 bucket into a new Snowflake table. A Snowflake administrator has already created a new database named RAW_DB and granted the engineer a role with the ability to create objects in that database. The engineer wants to organize all ingestion-related objects in a dedicated schema and create a reusable stage definition that points to the S3 location. Which sequence of SQL statements best meets this requirement?
- A
CREATE SCHEMA RAW_DB.INGEST; CREATE STAGE RAW_DB.INGEST.S3_STAGE URL='s3://company-data/inbound/' STORAGE_INTEGRATION = my_s3_int;
- B
CREATE DATABASE RAW_DB.INGEST; CREATE STAGE S3_STAGE LOCATION='s3://company-data/inbound/';
- C
CREATE SCHEMA INGEST IN DATABASE RAW_DB; CREATE EXTERNAL STAGE S3_STAGE ON 's3://company-data/inbound/';
- D
CREATE STAGE RAW_DB.INGEST.S3_STAGE ON BUCKET='company-data/inbound'; CREATE SCHEMA RAW_DB.INGEST;
Show answer and explanation
Correct answer: A
Explanation
The correct approach is to create a schema inside the existing database and then create an external stage inside that schema. In Snowflake, databases contain schemas, and schemas contain objects such as tables, views, and stages. For external stages that point to Amazon S3, the standard syntax uses CREATE STAGE
- A. Correct.
Correct. In Snowflake, a schema is created within a database, and a stage can then be created within that schema using a fully qualified name such as RAW_DB.INGEST.S3_STAGE. For an external stage pointing to Amazon S3, using URL='s3://...' with a configured STORAGE_INTEGRATION is valid and aligns with Snowflake best practices for secure access to cloud storage.
- B. Incorrect.
Incorrect. RAW_DB already exists as a database, so CREATE DATABASE RAW_DB.INGEST is invalid because databases do not contain other databases. Also, Snowflake stages use the URL parameter for external locations, not LOCATION in this form for creating a stage.
- C. Incorrect.
Incorrect. The syntax is not valid Snowflake SQL. Snowflake uses CREATE SCHEMA RAW_DB.INGEST or CREATE SCHEMA INGEST; after setting the current database, but not CREATE SCHEMA INGEST IN DATABASE RAW_DB in this form. Also, CREATE EXTERNAL STAGE and ON 's3://...' are not valid Snowflake stage creation syntax.
- D. Incorrect.
Incorrect. The object creation order is wrong because the schema must exist before creating a stage inside it. In addition, ON BUCKET='...' is not valid Snowflake syntax for stage creation. Snowflake external stages reference cloud storage with URL='s3://...'.