SnowflakeAssociate levelPage 2 of 4

SnowPro Associate: Platform exam dumps: questions 101 to 200 of 367

Page 2 of the free SnowPro Associate: Platform question bank for the SnowPro® Associate: Platform Certification exam. Questions 101 to 200 are listed below, the first 5 in full with answers and explanations. Back to page 1 for the exam overview and FAQ.

Question bank last updated April 2026

Start timed practice exam Questions 101 to 200

Free SnowPro Associate: Platform practice questions

Questions 101 to 105 of 367

Pick an answer before you open the explanation. Each question also has its own page with a permalink.

SnowPro Associate: Platform Question 101

Single answer● Schemas

A data engineering team created a schema named SALES in the ANALYTICS database and loaded several tables into it. A BI role, BI_ANALYST, already has USAGE on the ANALYTICS database, but analysts still receive an error when they try to query ANALYTICS.SALES.ORDERS. The security administrator wants to grant only the minimum additional access required so the role can query existing tables in the SALES schema, without granting broader privileges than necessary. Which grant should the administrator apply?

A
GRANT USAGE ON SCHEMA ANALYTICS.SALES TO ROLE BI_ANALYST;
B
GRANT SELECT ON DATABASE ANALYTICS TO ROLE BI_ANALYST;
C
GRANT SELECT ON ALL SCHEMAS IN DATABASE ANALYTICS TO ROLE BI_ANALYST;
D
GRANT OWNERSHIP ON SCHEMA ANALYTICS.SALES TO ROLE BI_ANALYST;

Show answer and explanation

Correct answer: A

Explanation

In Snowflake, schemas are namespaces within databases, and object access depends on privileges at multiple levels. To query a table using a fully qualified name, a role needs USAGE on the database, USAGE on the schema, and the relevant object privilege such as SELECT on the table or view. In this scenario, the role already has USAGE on the database, so the minimum additional schema-related privilege is USAGE on the SALES schema. This aligns with Snowflake access control best practices of least privilege. Refer to Snowflake documentation on access control privileges for databases, schemas, and tables, especially the distinction between container privileges like USAGE and object privileges like SELECT.

A. Correct.
Correct. To access objects inside a schema, a role must have USAGE on both the parent database and the schema. Since BI_ANALYST already has USAGE on the ANALYTICS database, granting USAGE on the SALES schema is the minimum additional privilege needed at the schema level to allow object resolution. Assuming the role already has the required object privilege such as SELECT on the table, this is the missing schema-related permission that enables querying existing tables in that schema.
B. Incorrect.
Incorrect. Snowflake does not support granting SELECT on a database as a way to read tables within it. SELECT is granted on objects such as tables and views, not at the database level for all contained objects. This option reflects a common misconception that database privileges cascade to table read access.
C. Incorrect.
Incorrect. This is not a valid Snowflake privilege model. SELECT is not granted on schemas to read all contained tables. In Snowflake, schema privileges such as USAGE allow object name resolution, while object-level privileges like SELECT must be granted on tables or views. A candidate might choose this if they confuse schema/container privileges with object privileges.
D. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and transfers control of the schema. It is far broader than required and violates the requirement to grant minimum necessary access. This would allow the BI role to manage the schema rather than simply access objects within it.

SnowPro Associate: Platform Question 102

Single answer● Schemas

A data engineering team stores raw ingestion tables in the schema PROD_DB.RAW. A contractor role named ETL_CONTRACTOR must load data into existing tables in RAW for the next 2 months, but the security team wants to prevent that role from creating new objects or accessing any other schemas in the database. Which privilege assignment best meets this requirement?

A
Grant USAGE on database PROD_DB, USAGE on schema PROD_DB.RAW, and INSERT on all existing tables in PROD_DB.RAW to the ETL_CONTRACTOR role.
B
Grant OWNERSHIP on schema PROD_DB.RAW to the ETL_CONTRACTOR role so it can load data into tables without access to other schemas.
C
Grant USAGE on schema PROD_DB.RAW and CREATE TABLE on schema PROD_DB.RAW to the ETL_CONTRACTOR role.
D
Grant SELECT, INSERT, UPDATE, and DELETE on database PROD_DB to the ETL_CONTRACTOR role.

Show answer and explanation

Correct answer: A

Explanation

In Snowflake, schema access is controlled hierarchically. A role must have USAGE on the database and USAGE on the schema before it can access objects within that schema. Privileges to act on tables, such as INSERT, are granted on the table objects themselves. To prevent object creation, do not grant CREATE privileges on the schema. This aligns with Snowflake's least-privilege best practice for role design. Relevant Snowflake documentation covers access control privileges for databases, schemas, and tables, including the need for USAGE on parent containers and object-specific privileges for DML operations.

A. Correct.
Correct. To work with objects in a schema, a role needs USAGE on the parent database and the schema. To load data into existing tables, the role also needs the appropriate table privilege, such as INSERT, on those tables. This combination limits access to the RAW schema and does not allow creation of new objects because no CREATE privilege on the schema is granted.
B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would allow the role to fully control the schema, including granting privileges and managing objects. This violates least-privilege principles and exceeds the requirement to only load data into existing tables.
C. Incorrect.
Incorrect. CREATE TABLE on a schema explicitly allows creation of new tables in that schema, which the security team wants to prevent. Also, USAGE on the database is still required to access objects in the schema.
D. Incorrect.
Incorrect. Database-level object privileges like SELECT, INSERT, UPDATE, and DELETE are not granted in this broad way to satisfy access to tables in a specific schema. The role would also need USAGE on the database and schema, and this option would be far too broad because it targets the entire database rather than only the RAW schema.

SnowPro Associate: Platform Question 103

Single answer● Schemas

A data engineering team maintains a database named ANALYTICS with a schema named STAGING that contains several transient tables used for short-term processing. To reduce risk, the team wants to prevent developers from accidentally dropping the STAGING schema while still allowing them to create and replace objects inside it during deployments. Which action should the Snowflake administrator take?

A
Create the schema as a managed access schema.
B
Create the schema with WITH MANAGED ACCESS and DATA_RETENTION_TIME_IN_DAYS = 0.
C
Create the schema with the OBJECT_VISIBILITY property set to PRIVATE.
D
Create the schema with the TRANSIENT property enabled.

Show answer and explanation

Correct answer: A

Explanation

In Snowflake, schemas are logical containers for database objects, and administrators commonly use schema properties to support governance and workload patterns. A managed access schema is the key schema feature for tighter administrative control because privilege management is performed by the schema owner or a role with MANAGE GRANTS, rather than by individual object owners. This supports controlled deployment processes and reduces the chance of unintended privilege sprawl.

There is no schema property whose purpose is to make a schema impossible to drop. Preventing accidental drops is primarily addressed through role design, least-privilege access, and ownership control. Transient schemas are useful for staging or temporary data because they are intended for objects that do not require the same level of data protection as permanent objects, but transient does not provide drop protection. Similarly, data retention settings affect Time Travel, not schema deletion permissions.

This aligns with Snowflake documentation on CREATE SCHEMA, managed access schemas, transient schemas, and access control best practices.

A. Correct.
Correct. A managed access schema centralizes privilege management so that object owners do not automatically control grant decisions on objects within the schema; only the schema owner or a role with MANAGE GRANTS can manage object privileges. This is useful when an administrator wants tighter control over a schema and its objects during team deployments. While this does not literally make a schema undroppable, it is the valid schema-level governance feature among the options and best aligns with reducing accidental administrative actions through controlled access patterns.
B. Incorrect.
Incorrect. WITH MANAGED ACCESS is a valid schema property, but DATA_RETENTION_TIME_IN_DAYS = 0 does not prevent a schema from being dropped. Retention affects Time Travel availability for supported objects, not whether the schema itself can be deleted. A candidate might choose this option because transient objects are often paired with lower retention for cost control, but that does not address accidental schema drops.
C. Incorrect.
Incorrect. OBJECT_VISIBILITY is not a standard schema property used to protect schemas from being dropped in Snowflake. This distractor targets the misconception that there is a schema setting similar to visibility controls in other platforms. Snowflake schema management focuses on ownership, privileges, and schema types such as managed access or transient.
D. Incorrect.
Incorrect. A transient schema reduces data protection costs by limiting Fail-safe for objects created in it, but it does not prevent the schema from being dropped. This is a common misconception because transient schemas are often used for temporary or staging workloads, yet their purpose is storage lifecycle and recovery behavior, not drop protection.

SnowPro Associate: Platform Question 104

Single answer● Schemas

A data engineering team is reorganizing objects in a Snowflake database. They created a schema named ANALYTICS in the SALES database and loaded several permanent tables into it. Later, they realize the schema was created with the wrong owner role, and they also want to avoid dropping the schema because the contained tables must be preserved. Which action should they take to address the ownership issue while keeping the schema and its objects in place?

A
Use ALTER SCHEMA SALES.ANALYTICS RENAME TO new_schema_name to change the schema owner without affecting contained objects.
B
Use GRANT OWNERSHIP ON SCHEMA SALES.ANALYTICS TO ROLE <new_role> to transfer ownership of the schema.
C
Drop the schema with CASCADE and recreate it under the correct role; the permanent tables will remain in Time Travel and automatically reconnect to the recreated schema.
D
Clone the schema to a new schema owned by the correct role, then the original schema ownership will automatically update to match the clone.

Show answer and explanation

Correct answer: B

Explanation

The correct solution is to transfer ownership with GRANT OWNERSHIP ON SCHEMA. In Snowflake, OWNERSHIP is a special privilege that controls the object and can be transferred to another role. This is the standard method for correcting which role owns an existing schema without dropping or recreating it. RENAME only changes object names, and CLONE creates a new object rather than modifying the original. Dropping and recreating a schema is unnecessary and risky when the goal is simply to correct administrative control. This aligns with Snowflake documentation on access control and object privileges, where OWNERSHIP transfer is the supported way to reassign control of schemas and other securable objects.

A. Incorrect.
Incorrect. RENAME changes only the schema name, not its owner. This is a common misconception because ALTER commands can modify many object properties, but ownership changes in Snowflake are handled with GRANT OWNERSHIP, not RENAME.
B. Correct.
Correct. In Snowflake, ownership is a privilege, and transferring it is done with GRANT OWNERSHIP ON SCHEMA. This changes the owning role without requiring the schema to be dropped, allowing the schema and its contained objects to remain in place. This is the appropriate administrative action when the schema was created under the wrong role.
C. Incorrect.
Incorrect. Dropping a schema with CASCADE removes the schema and its objects. While dropped objects may be recoverable during retention periods using Time Travel, they do not automatically reconnect to a newly recreated schema. This approach is disruptive and unnecessary for simply correcting schema ownership.
D. Incorrect.
Incorrect. Cloning creates a separate copy of the schema and its objects at a point in time, but it does not change ownership of the original schema. The original and cloned schemas are distinct objects, and cloning is not the mechanism for fixing ownership on an existing schema.

SnowPro Associate: Platform Question 105

Single answer● Tables

A data engineering team loads clickstream data into a Snowflake table named EVENTS_RAW. The table receives millions of rows per day, and analysts frequently run queries filtered by EVENT_DATE and USER_ID. Performance has become inconsistent as the table grows. The team wants to improve query pruning without changing application logic or manually repartitioning data. Which action is the MOST appropriate?

A
Define a clustering key on EVENT_DATE and USER_ID for the EVENTS_RAW table
B
Convert EVENTS_RAW into a temporary table so Snowflake can optimize scans for active sessions
C
Create a primary key on EVENT_DATE and USER_ID so Snowflake physically orders rows by those columns
D
Replace the table with an external table so partitions in cloud storage handle pruning automatically

Show answer and explanation

Correct answer: A

Explanation

For large Snowflake tables, query performance often depends on effective micro-partition pruning. When queries repeatedly filter on specific columns, a clustering key can help Snowflake better organize table data over time so fewer micro-partitions are scanned. This is a practical option when a large table experiences inconsistent performance as data volume increases. By contrast, standard primary key constraints in Snowflake do not enforce physical row ordering or clustering. Temporary tables are for session-specific use cases, not persistent production datasets. External tables are intended for data that remains in external storage and are not the preferred solution for optimizing a native Snowflake table used for high-volume analytics. This aligns with Snowflake documentation and best practices on table types, constraints, and clustering for large tables.

A. Correct.
Correct. Defining a clustering key on columns commonly used in selective filters, such as EVENT_DATE and USER_ID, can improve micro-partition pruning as the table grows. This is especially useful for very large tables with frequent filtering on the same columns. It does not require application query changes, and Snowflake manages table storage internally rather than requiring manual repartitioning.
B. Incorrect.
Incorrect. Temporary tables are session-scoped and are intended for transient, short-lived data. They do not provide a performance optimization mechanism for large persistent workloads, and converting a production raw events table into a temporary table would make the data unavailable outside the session.
C. Incorrect.
Incorrect. In Snowflake, primary key constraints are generally informational unless using features such as hybrid tables. For standard tables, declaring a primary key does not physically sort or cluster the data, so it would not improve pruning in the way described.
D. Incorrect.
Incorrect. External tables are designed for querying data stored outside Snowflake, such as files in cloud storage. They are not a replacement for a heavily queried internal table when the goal is to improve pruning of Snowflake-managed table storage. External tables also typically do not provide the same query performance characteristics as native tables.

Timed practice exam

Take a SnowPro Associate: Platform practice test under exam conditions

65 questions in 85 minutes, drawn from this bank, with a score report and a per-question review when you finish.

Start timed exam

SnowPro Associate: Platform practice questions 101 to 200 of 367

Every question has a page with the answer and explanation. Numbers are stable, so you can bookmark or share them. The bank is split into 4 pages of up to 100 questions.