Free practice · No sign-up required
15 questions with answer explanations. Choose an answer below to begin.
Which privilege allows a user to create new schemas within a database?
Manage database objects and access
0/15 answered · Bookmark questions to review later. Focus the practice area to use keys 1–9 and Enter.
Learn by doing
Use official documentation and hands-on labs to work through topics you missed.
Prefer to read at your own pace? Work through each question below, then open its answer to check your reasoning. You can link directly to a question or print this reference.
Which privilege allows a user to create new schemas within a database?
CREATE SCHEMA
CREATE TABLE
CREATE SESSION
CREATE STAGE
Correct answer
CREATE SCHEMA
The CREATE SCHEMA privilege enables a user to create new schemas in a database. Other privileges support different actions like creating tables or stages.
What is the primary purpose of a Snowflake warehouse?
To provide compute resources for query execution
To store metadata about database objects
To manage network connectivity
To encrypt data at rest
Correct answer
To provide compute resources for query execution
A warehouse in Snowflake provides isolated compute resources that handle query execution and data processing. It is separate from storage and metadata services.
Which statement best describes Time Travel in Snowflake?
It allows querying historical data within a defined retention period
It permanently deletes old data to save storage
It replicates data to external cloud storage
It compresses data to reduce footprint
Correct answer
It permanently deletes old data to save storage
Time Travel lets you access data as it existed over a recent period, enabling recovery from accidental changes. It does not delete or compress data.
What is the effect of setting a column as NOT NULL in Snowflake?
It prevents NULL values from being inserted into the column
It automatically populates the column with default values
It indexes the column for faster queries
It encrypts the column data
Correct answer
It prevents NULL values from being inserted into the column
A NOT NULL constraint ensures that a column cannot contain NULL values, enforcing data completeness. It does not affect indexing or encryption.
Which feature can be used to automatically load data from an external stage into a table?
Snowpipe
Data Pump
Bulk Loader
Query Accelerator
Correct answer
Snowpipe
Snowpipe provides continuous, automated data loading from internal or external stages. It reacts to new files without requiring manual intervention.
What does a zero-copy clone in Snowflake primarily enable?
Instant duplication of a database or table with minimal storage use
Permanent backup of data to an external location
Encryption of cloned objects
Conversion of data formats during copy
Correct answer
Instant duplication of a database or table with minimal storage use
A zero-copy clone creates a reference copy of a database or table almost instantly, without duplicating storage. Changes to the clone are tracked separately.
Which role is typically used for performing routine administrative tasks in Snowflake?
ACCOUNTADMIN
SYSADMIN
SECURITYADMIN
PUBLIC
Correct answer
SYSADMIN
The SYSADMIN role is commonly assigned for general administrative operations such as managing warehouses and objects. Other roles have more specialized scopes.
What is the purpose of a secure view in Snowflake?
To restrict access to sensitive columns while allowing access to other table data
To store unencrypted backups of tables
To improve query performance for large datasets
To replicate data across regions
Correct answer
To restrict access to sensitive columns while allowing access to other table data
A secure view lets you expose a subset of data while hiding sensitive columns from users who lack appropriate privileges. It enforces column-level security.
Which parameter controls how long historical data is retained in Time Travel?
RETENTION_TIME
TIME_TRAVEL_HOURS
DATA_RETENTION
HISTORY_PERIOD
Correct answer
RETENTION_TIME
The retention period for Time Travel is configured using the RETENTION_TIME parameter, which specifies the number of days data is kept for point-in-time access.
What does the UNIQUE constraint ensure in a table column?
No duplicate values are allowed in the column
The column cannot contain NULL
The column is automatically indexed
The column is encrypted
Correct answer
No duplicate values are allowed in the column
A UNIQUE constraint guarantees that all values in a column are distinct. It does not inherently prevent NULLs unless combined with NOT NULL.
Which statement is true about Snowflake's storage architecture?
Data is stored in compressed, immutable files managed by Snowflake
Users manage underlying storage buckets directly
Storage and compute are always on the same physical nodes
Data must be pre-transformed before loading
Correct answer
Data is stored in compressed, immutable files managed by Snowflake
Snowflake stores data in compressed, immutable micro-partitions. The platform abstracts storage management, separating it from compute resources.
What is the primary use of a session parameter in Snowflake?
To define the behavior of the current user session
To permanently alter database schema
To grant access to other users
To schedule background tasks
Correct answer
To define the behavior of the current user session
Session parameters control client-specific settings such as date formats or statement timeouts for the duration of a session. They do not persist beyond the session.
Which task can be performed using the Snowflake Information Schema?
Query metadata about tables and columns in the current database
Modify data in underlying storage
Manage cloud infrastructure
Monitor real-time query performance
Correct answer
Query metadata about tables and columns in the current database
The Information Schema provides read-only access to metadata about database objects. It is used for reporting and introspection, not for data modification.
What does the EXECUTE IMMEDIATE command enable in Snowflake?
Dynamic execution of SQL statements constructed at runtime
Immediate deletion of temporary objects
Forced encryption of query results
Real-time data replication
Correct answer
Dynamic execution of SQL statements constructed at runtime
EXECUTE IMMEDIATE allows running SQL strings that are built dynamically, which is useful in stored procedures and scripting. It does not affect data deletion or encryption.
Which statement best describes Snowflake's concurrency model?
Multiple virtual warehouses can run queries independently without contention
Only one query can run at a time per account
Concurrency is limited to read operations
All queries share a single fixed compute resource
Correct answer
Multiple virtual warehouses can run queries independently without contention
Snowflake supports multiple virtual warehouses that operate independently, allowing concurrent query execution without resource contention. This enables scalable performance.
Recall the answer before opening each card.
CREATE SCHEMA privilege. This database-level privilege grants the ability to create schemas, separate from table or stage creation.
To provide compute resources for query execution. Warehouses are isolated compute clusters that handle DML, queries, and data loading.
Access data as it existed at a previous point in time within a configurable retention period. It does not delete or compress old data.
It prevents NULL values from being inserted into the column. Enforces data completeness at the column level.
Snowpipe. It continuously loads files as they arrive, using event notifications or scheduled runs.
Instant duplication of a database or table with minimal storage usage. A clone shares underlying data until modifications are made.
SYSADMIN. It manages objects like warehouses and databases but cannot change account-level settings.
To restrict access to sensitive columns while allowing access to other table data. It provides column-level security by hiding certain columns.
RETENTION_TIME. Sets the number of days (0–90) that historical data is kept for point-in-time queries.
No duplicate values are allowed in the column. It enforces distinct values but does not automatically prevent NULLs.
In compressed, immutable micro-partitions managed by Snowflake. Storage is separate from compute and abstracted from users.
To define the behavior of the current user session. Examples include date format, statement timeout, and transaction settings.
Metadata about tables, columns, views, and other objects in the current database. It provides read-only catalog information.
Dynamic execution of SQL statements constructed at runtime. Used in stored procedures and scripting for flexible query building.
Multiple virtual warehouses can run queries independently without resource contention. Each warehouse has isolated compute.
A role is a collection of privileges assigned to users. Users cannot hold privileges directly; they inherit them through roles.
Yes, if the table is transient or permanent. Default is inherited from schema or database. Transient tables default to 0 days.
Any valid SQL statement built as a string, including DDL and DML. It is useful for parameterized object names using IDENTIFIER().
CREATE TABLE privilege on the schema, plus USAGE on the containing database.
Through event notifications (e.g., S3 SQS queues) or by setting AUTO_INGEST = TRUE. It then runs the COPY INTO command.
Read the prompt, commit to an answer, then flip the card. Move through the deck at your own pace and repeat any topic that does not come back quickly.
Card 1 of 20
1 reviewed this session
Connect the concepts
Open a branch to connect a topic with its practice questions and explanations. Use the map to choose your next review topic.
This is an analysis of the practice bank, not a claim about the vendor's live exam blueprint. Use it to identify the knowledge, judgment, and recall patterns exercised here, then verify your coverage against the current official exam guide.
Snowflake uses a granular privilege system to control object access. The CREATE SCHEMA privilege, distinct from CREATE TABLE or CREATE STAGE, allows users to create new schemas within a database. Understanding privilege hierarchy is critical for administration. You must know which roles own objects and how to grant required access without over-provisioning. The practice bank highlights the difference between object-level and schema-level privileges.
Snowflake separates compute (virtual warehouses) from storage. Warehouses provide isolated, scalable compute resources for query execution and data processing. Storage uses compressed, immutable micro-partitions managed entirely by Snowflake. This architecture enables elastic scaling, zero-copy cloning, and automated maintenance. The practice bank tests understanding of concurrency: multiple warehouses run independently without contention.
Constraints like NOT NULL and UNIQUE enforce data quality at the column level. Time Travel provides point-in-time access to historical data for a configurable retention period (RETENTION_TIME). Secure views restrict sensitive column access without masking the underlying table. The practice bank clarifies that Time Travel does not delete old data; it retains immutable snapshots. Understanding these features is essential for compliance and recovery planning.
Snowpipe automates continuous data loading from stages. The Information Schema provides read-only metadata about database objects. EXECUTE IMMEDIATE allows running dynamically constructed SQL, crucial for stored procedures. Session parameters control per-session behavior (e.g., timezone, timeout). These features reduce manual administration and enable advanced scripting.
Study workflow
Start by querying the Information Schema to list all roles and their granted privileges. Use SHOW GRANTS OF ROLE and SHOW GRANTS TO ROLE to identify over‑provisioned access. Ensure only SYSADMIN roles have elevated object management rights and restrict higher roles like ACCOUNTADMIN for emergency use.
Determine the retention period needed for each database or schema. Use ALTER DATABASE or ALTER SCHEMA with RETENTION_TIME set to 0–90 days. For transient tables, set RETENTION_TIME to 0 to avoid extra storage costs. Verify with SHOW PARAMETERS and test by performing a SELECT with AT or BEFORE.
Create a secure view that exposes only non‑sensitive columns. Grant SELECT on the view to relevant roles. Use the SECURE keyword to prevent the view definition from being exposed to end users. Test by querying as a role without direct table access; the view should return data but the underlying table remains hidden.
Define a Snowpipe that references an external stage (e.g., S3). Use CREATE PIPE and specify the COPY INTO statement. Set up event notifications (or use AUTO_INGEST) so new files trigger automatic loading. Monitor pipe status with SYSTEM$PIPE_STATUS and handle errors via notification integration.
Write a stored procedure that accepts table name as input. Inside, use EXECUTE IMMEDIATE to build a query string. Call the procedure to perform operations like dynamic truncation or data movement. Remember to use IDENTIFIER() for object names and test with minimal privileges to avoid SQL injection.
FAQ
Clear boundaries on what the bank covers, how to use it, and where official vendor information still matters.
Time Travel provides point‑in‑time access to historical data for a configurable retention period (up to 90 days). Unlike some databases that rely on transaction logs, Snowflake stores immutable micro‑partitions and deduplicates unchanged data, making restoration instant and storage‑efficient.
SYSADMIN manages objects (warehouses, databases, roles) but cannot alter account‑level parameters or security. ACCOUNTADMIN has full administrative privileges, including creating roles and managing billing. Best practice reserves ACCOUNTADMIN for critical tasks and uses SYSADMIN for routine operations.
Yes, secure views can also mask data by using CASE expressions or functions like HASH. However, Snowflake's Dynamic Data Masking policy is more efficient for column‑level masking. Secure views are ideal when you need to expose different subsets of columns to different roles.
Multiple warehouses allow independent scaling, cost control, and workload isolation. Each warehouse can be sized differently and auto‑suspended, reducing compute costs. This architecture avoids contention because each warehouse has its own compute resources, crucial for concurrency and performance.
Snowflake does not allow privileges to be granted directly to users. All privileges must be granted to roles, and roles are assigned to users. This role‑based access control simplifies management and auditing. To grant CREATE SCHEMA, create a custom role and grant the privilege to it.
Browse another free bank or use the study strategy guide to turn your misses into spaced review.
