SnowPro Associate: Platform Question 155
Single answer○ Use the PUBLIC SCHEMAA Snowflake administrator creates a new database named FINANCE_DB for an analytics team. Immediately afterward, a developer with the required database and schema privileges creates a table by running the following statements:
USE DATABASE FINANCE_DB; CREATE TABLE monthly_close (id NUMBER, close_date DATE);
The developer does not explicitly specify a schema in the CREATE TABLE statement. In which schema will the table be created, assuming no other schema has been set in the session?
- A
In FINANCE_DB.PUBLIC, because each new database includes a default PUBLIC schema and unqualified object creation uses the current schema context
- B
In SNOWFLAKE.PUBLIC, because PUBLIC is a shared system schema used when no schema is specified
- C
The statement will fail, because Snowflake requires a schema name for all CREATE TABLE statements
- D
In FINANCE_DB.INFORMATION_SCHEMA, because metadata and newly created objects are stored together by default
Show answer and explanation
Correct answer: A
Explanation
This question tests understanding of how Snowflake uses the PUBLIC schema in practice. Each standard Snowflake database contains a PUBLIC schema and an INFORMATION_SCHEMA. PUBLIC is intended as a general-purpose schema and is often the default schema context when a database is selected and no other schema is explicitly set. As a result, creating an object with an unqualified name after USE DATABASE FINANCE_DB typically places the object in FINANCE_DB.PUBLIC, assuming the session context resolves there and the user has the needed privileges. INFORMATION_SCHEMA is for metadata queries, not for storing user objects. Best practice in production environments is often to use explicitly named schemas rather than relying on PUBLIC or implicit session context, to improve clarity and governance.
- A. Correct.
Correct. In Snowflake, a newly created database includes standard schemas such as PUBLIC and INFORMATION_SCHEMA. When a session uses a database and no different schema has been explicitly selected, unqualified object names commonly resolve to the PUBLIC schema in that database context. Therefore, CREATE TABLE monthly_close ... creates FINANCE_DB.PUBLIC.monthly_close.
- B. Incorrect.
Incorrect. SNOWFLAKE is a separate system database, and its schemas are not used as a default location for user-created tables in another database. PUBLIC is not a global shared schema across all databases.
- C. Incorrect.
Incorrect. Snowflake does not require fully qualified names for CREATE TABLE statements when the current database and schema context are available. A user can create objects with unqualified names if the active context resolves the target schema and the role has sufficient privileges.
- D. Incorrect.
Incorrect. INFORMATION_SCHEMA is a read-only schema used for metadata and views about database objects. User tables are not created there. This option reflects a common confusion between metadata schemas and writable user schemas.