SnowPro Advanced: Administrator Free Practice Questions
This 15-question practice bank covers core Snowflake administrative concepts including object privileges, compute management, data protection, constraints, automation, cloning, role-based access control, secure views, Time Travel, storage architecture, session parameters, metadata queries, dynamic SQL, and concurrency. Each question tests decision-making in real-world admin scenarios. Work through these to reinforce retrieval: answer each, check explanations, and note where your reasoning differed. Use this guide to deepen understanding of Snowflake's logical separation of storage and compute, the purpose of warehouses as elastic compute clusters, and the nuances of zero-copy clones and Time Travel. Mastery here builds a strong foundation for the ADA-C02 exam.
What this ADA-C02 practice set measures
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.
Object Privileges and Schema Management
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.
- CREATE SCHEMA is a database-level privilege that enables schema creation.
- Privileges can be granted to roles, which are then assigned to users.
- Schema objects (tables, views, stages) inherit schema-level privileges unless overridden.
- To create a table, a user needs both CREATE TABLE on the schema and USAGE on the database.
Compute and Storage Architecture
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.
- Virtual warehouses are required for queries, DML, and loading/unloading data.
- Storage charges are based on compressed data size; compute charges per warehouse runtime.
- Zero-copy clones create instant references to underlying storage without duplicating data.
- Concurrency is achieved by provisioning separate warehouses for different workloads.
Data Integrity and Protection
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.
- NOT NULL prevents NULL values; UNIQUE ensures all values are distinct.
- Time Travel retention is set via RETENTION_TIME parameter (0 to 90 days for standard accounts).
- Secure views enable column-level security by hiding sensitive columns from unauthorized users.
- Constraints are metadata; Snowflake enforces them during DML operations.
Automation, Metadata, and Dynamic Execution
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.
- Snowpipe uses event notifications or scheduled runs to load new files automatically.
- Information Schema queries (e.g., TABLES, COLUMNS) support catalog introspection.
- EXECUTE IMMEDIATE is used in Snowflake Scripting for dynamic SQL execution.
- Session parameters override account-level defaults for the duration of a session.
Practice SnowPro Advanced: Administrator with real flashcards
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
Static practice bank
Start the 15-question diagnostic
The complete question bank is embedded in this pre-rendered page. There is no database request or second content download when you begin.
Which privilege allows a user to create new schemas within a database?
Show hint
Manage database objects and access
Study workflow
Turn one ADA-C02 attempt into a study plan
- 1
Audit Existing Privileges
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.
- 2
Configure Time Travel Retention
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.
- 3
Implement Secure Views for PII
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.
- 4
Automate Ingestion with Snowpipe
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.
- 5
Dynamic SQL in Stored Procedures
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
Questions about this ADA-C02 practice page
Clear boundaries on what the bank covers, how to use it, and where official vendor information still matters.
How does Snowflake's Time Work differ from continuous data protection in other databases?+
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.
What is the difference between SYSADMIN and ACCOUNTADMIN roles in Snowflake?+
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.
Can I use a secure view to mask data rather than hide columns?+
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.
Why does Snowflake recommend using multiple virtual warehouses instead of one large warehouse?+
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.
Can I grant CREATE SCHEMA privilege directly to a user instead of a role?+
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.
Build the next review session
Browse another free bank or use the study strategy guide to turn your misses into spaced review.