Snowflake ADA-C02 Practice Exam
SnowPro Advanced: Administrator ADA-C02
The SnowPro Advanced: Administrator certification validates advanced knowledge and skills used to apply data cloud administrative principles on Snowflake: managing accounts, security and governance, maintaining database objects and virtual warehouses, monitoring and tuning performance, sharing data and replicating it for disaster recovery. The ADA-C02 exam covers six domains: Security, RBAC and User Administration (31%), Account Management and Data Governance (18%), Data and Object Management (15%), Performance Monitoring and Tuning (20%), Data Sharing and Snowflake Marketplace (7%), and Disaster Recovery, Backup and Data Replication (9%). This practice exam mirrors its scenario style with single-answer, multiple-select and drag-and-drop questions.
814 questions in the bank
Question bank updated : questions, answers, explanations and chapters.
Certification Overview
- Exam name: SnowPro Advanced Administrator ADA-C02
- Vendor: Snowflake
- Exam code: ADA-C02
- Duration: 115 minutes
- Total questions: about 65
- Official passing score: 750 on a scale of 0-1000 (source)
- Our practice exam pass mark: 75%
Who Should Take This Exam?
Snowflake Administrator, Data Cloud Administrator, Cloud Infrastructure Administrator, Database Administrator, Cloud Data Administrator, Data Platform Engineer, Application Developer, Technical Lead
Prerequisites
Active SnowPro Core certification. Snowflake recommends 2 or more years of hands-on experience administering Snowflake, with fluency in ANSI and Snowflake extended SQL.
Topics Covered
- Snowflake Security, RBAC, and User Administration
- Account Management & Data Governance
- Data and Object Management
- Performance Monitoring & Tuning
- Data Sharing and Snowflake Marketplace
- Disaster Recovery, Backup & Data Replication
Question Types
- Multiple Choice (Single Answer)
- Multiple Choice (Multiple Answers)
- Drag and Drop
SnowPro Advanced Administrator ADA-C02 Practice Questions
Our question bank contains 814+ practice questions for this certification. Sample questions from each exam chapter. Expand a question to see the answer choices. With full access, you get unlimited practice exams with randomized questions from our full question bank.
Snowflake Security, RBAC, and User Administration
Help-desk staff must create users and roles and manage the users they create, but must not hold the MANAGE GRANTS privilege. Which system-defined role should their role be granted?
- A. SECURITYADMIN
- B. ACCOUNTADMIN
- C. USERADMIN Correct answer
- D. SYSADMIN
Option C is correct because USERADMIN holds CREATE USER and CREATE ROLE and owns what it creates, so it can manage those users and roles without the global MANAGE GRANTS privilege. SECURITYADMIN (A) and ACCOUNTADMIN (B) both carry MANAGE GRANTS, and SYSADMIN (D) is meant for warehouses and databases, not user administration.
To stop users from reusing any of their last 12 passwords, which password policy property should the administrator set?
- A. PASSWORD_MIN_AGE_DAYS = 12
- B. PASSWORD_MAX_AGE_DAYS = 12
- C. PASSWORD_LOCKOUT_TIME_MINS = 12
- D. PASSWORD_HISTORY = 12 Correct answer
Option D is correct because PASSWORD_HISTORY sets how many previous passwords Snowflake remembers and refuses to accept again. PASSWORD_MIN_AGE_DAYS (A) sets how soon a password can be changed again, PASSWORD_MAX_AGE_DAYS (B) sets when it expires, and PASSWORD_LOCKOUT_TIME_MINS (C) controls how long a locked-out user waits.
Schema SALES.RAW holds tables on which role ANALYST has SELECT through GRANT SELECT ON ALL TABLES IN SCHEMA, and ANALYST also has USAGE on the schema itself. An engineer runs CREATE SCHEMA SALES.RAW_DEV CLONE SALES.RAW, and ANALYST must query the cloned tables. Which statement is all that is still needed?
- A. GRANT USAGE ON SCHEMA SALES.RAW_DEV TO ROLE ANALYST Correct answer
- B. GRANT OWNERSHIP ON SCHEMA SALES.RAW_DEV TO ROLE ANALYST COPY CURRENT GRANTS
- C. GRANT SELECT ON ALL TABLES IN SCHEMA SALES.RAW_DEV TO ROLE ANALYST
- D. GRANT SELECT ON FUTURE TABLES IN SCHEMA SALES.RAW_DEV TO ROLE ANALYST
Option A is correct because a cloned schema keeps the privileges granted on its child objects, so ANALYST still has SELECT on the cloned tables, but privileges on the schema itself are not copied and USAGE must be granted again. Options C and D repeat SELECT grants the clone already carries, and B would hand ownership of the schema to ANALYST, which is far more than the task needs.
Support staff must be able to suspend and resume warehouse REPORTING_WH and to view its current and past queries, but must not be able to run queries on it or resize it. Which TWO privileges should be granted on the warehouse? (Select TWO).
- A. OPERATE on REPORTING_WH Correct answer
- B. USAGE on REPORTING_WH
- C. MONITOR on REPORTING_WH Correct answer
- D. MODIFY on REPORTING_WH
- E. OWNERSHIP on REPORTING_WH
Options A and C are correct because OPERATE lets a role suspend and resume the warehouse, and MONITOR lets it see current and past queries on it. USAGE (B) would let the staff run queries, MODIFY (D) allows resizing and other changes, and OWNERSHIP (E) gives full control.
Account Management & Data Governance
A finance lead who holds the ORGADMIN role wants to see every account in the organization, with its region and edition, without writing SQL. Where in Snowsight is this list found?
- A. Data, then Databases, then the SNOWFLAKE database, after switching to ORGADMIN
- B. Admin, then Cost Management, after switching the active role to ORGADMIN
- C. Admin, then Accounts, after switching the active role to ORGADMIN Correct answer
- D. Governance and security, then Trust Center, after switching the active role to ORGADMIN
Option C is correct because the organization administrator switches to ORGADMIN and opens Admin, then Accounts, which lists every account with its region and edition. The SNOWFLAKE database, Cost Management and the Trust Center are for other purposes and do not provide this account list.
A provisioning script must build the account value for ALTER SHARE ... ADD ACCOUNTS so that it names the consumer's own account in the current organization. Which expression produces the identifier in the form used inside SQL statements?
- A. CURRENT_ACCOUNT_NAME() || '.' || CURRENT_ORGANIZATION_NAME(), the reversed order
- B. CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME(), the hyphenated form
- C. CURRENT_ACCOUNT() || '.' || CURRENT_REGION(), the locator with its region
- D. CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME() Correct answer
Option D is correct because statements such as ALTER SHARE ... ADD ACCOUNTS take the organization name and account name joined by a period. The hyphenated form is used in connection settings, and the locator with a region is the legacy format, so options B and C do not fit the SQL form, and option A reverses the order.
Finance asks how much of the prepaid capacity commitment is left today, broken down by the contract's currency balance rather than by credits used per account. Which ORGANIZATION_USAGE view reports this?
- A. WAREHOUSE_METERING_HISTORY, which lists credits per warehouse
- B. REMAINING_BALANCE_DAILY Correct answer
- C. RATE_SHEET_DAILY, which lists the price per credit
- D. CONTRACT_ITEMS, which lists the line items purchased
Option B is correct because REMAINING_BALANCE_DAILY reports the remaining balance of the capacity commitment in the contract currency for each day. WAREHOUSE_METERING_HISTORY shows credits used, RATE_SHEET_DAILY shows the price per credit, and CONTRACT_ITEMS lists the purchased line items rather than a balance.
Failover between accounts and a stable connection URL for applications are planned across three regions. Which TWO capabilities require the participating accounts to belong to the same organization? (Select TWO).
- A. A zero-copy clone of a table into a database of the other account
- B. A reader account created with CREATE MANAGED ACCOUNT
- C. A connection object used for Client Redirect Correct answer
- D. A failover group that copies account objects to a target account Correct answer
- E. A direct share of tables to a consumer account in the same region
Options C and D are correct because a connection object used for Client Redirect and a failover group both connect accounts that belong to the same organization. A zero-copy clone cannot span accounts at all, a reader account created with CREATE MANAGED ACCOUNT is a provider-owned consumer account rather than an organization member, and a direct share to another account in the same region needs no organization link.
Data and Object Management
The on-call role OPS_ROLE must be able to change the size and the AUTO_SUSPEND setting of warehouse REPORT_WH, but must not be able to drop the warehouse or transfer its ownership. Which grant meets the requirement?
- A. GRANT MODIFY ON WAREHOUSE report_wh TO ROLE ops_role Correct answer
- B. GRANT MONITOR ON WAREHOUSE report_wh TO ROLE ops_role
- C. GRANT USAGE ON WAREHOUSE report_wh TO ROLE ops_role WITH GRANT OPTION
- D. GRANT OPERATE ON WAREHOUSE report_wh TO ROLE ops_role
Option A is correct because MODIFY on a warehouse allows ALTER WAREHOUSE changes such as resizing and setting AUTO_SUSPEND, while dropping the warehouse and transferring ownership stay with the owner role. Option B (MONITOR) only allows viewing usage, option D (OPERATE) only allows suspend, resume and abort, and option C (USAGE) only allows running queries, so none of them permits a size or AUTO_SUSPEND change.
Doubling the size of the warehouse that runs scheduled COPY INTO statements did not speed up the Snowpipe pipe on the same stage, and pipe latency stays the same. What explains this?
- A. A pipe is pinned to an X-Small warehouse in the account, and customers cannot resize that internal warehouse
- B. A pipe keeps the warehouse size that was current when it was created and ignores later ALTER WAREHOUSE changes
- C. A pipe borrows the warehouse of its owner role only when staged files exceed 100 MB, and these staged files are smaller than that
- D. Snowpipe loads files on serverless compute that Snowflake manages, so a customer warehouse size does not apply to the pipe Correct answer
Option D is correct because Snowpipe loads files on serverless compute that Snowflake manages, so the size of a customer warehouse has no influence on pipe latency. Options A, B and C describe pipe behaviours that do not exist: a pipe does not use a fixed X-Small warehouse, does not remember a warehouse size, and does not borrow the owner role's warehouse.
CREATE WAREHOUSE DEV_WH is run with no optional properties. Which TWO default values does Snowflake apply? (Select TWO).
- A. SCALING_POLICY = ECONOMY
- B. AUTO_RESUME = TRUE Correct answer
- C. INITIALLY_SUSPENDED = TRUE
- D. AUTO_SUSPEND = 300
- E. WAREHOUSE_SIZE = XSMALL Correct answer
Options B and E are correct because a CREATE WAREHOUSE with no optional properties creates an X-Small warehouse that has AUTO_RESUME = TRUE. The default scaling policy is STANDARD, not ECONOMY, a new warehouse is not suspended unless INITIALLY_SUSPENDED = TRUE is given, and AUTO_SUSPEND defaults to 600 seconds when created through SQL (300 is the Snowsight form default).
Performance Monitoring & Tuning
Your team's report lists ORDERS, ORDER_ITEMS and PRODUCTS in a comma-separated FROM clause, and the WHERE clause relates only ORDERS to ORDER_ITEMS. The Query Profile shows a CartesianJoin node producing billions of rows. Which TWO changes fix the cause? (Select TWO).
- A. Add a clustering key on ORDERS.order_id so the join can prune its inputs
- B. Enable the query acceleration service on the warehouse that runs the report
- C. Add the missing predicate that relates PRODUCTS to ORDER_ITEMS on product_id Correct answer
- D. Rewrite the FROM clause with explicit JOIN ... ON syntax so each relationship is stated Correct answer
- E. Resize the warehouse from Large to 2X-Large so the multiplied rows are processed sooner
Options C and D are correct because the CartesianJoin comes from PRODUCTS having no relating predicate, so adding the PRODUCTS to ORDER_ITEMS condition removes the multiplication, and explicit JOIN ... ON syntax makes every relationship visible so none is left out. Clustering ORDERS, query acceleration and a larger warehouse do not remove the cross product, they only process the multiplied rows.
Your export script reads TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) for the last six hours on a busy account and receives exactly 100 rows. Which argument setting returns the most statements from one call?
- A. RESULT_LIMIT => 10000 Correct answer
- B. RESULT_LIMIT => 100000
- C. END_TIME_RANGE_START => DATEADD('hour', -6, CURRENT_TIMESTAMP())
- D. END_TIME_RANGE_END => DATEADD('hour', -6, CURRENT_TIMESTAMP())
Option A is correct because the INFORMATION_SCHEMA QUERY_HISTORY table function returns 100 rows by default and RESULT_LIMIT accepts values up to 10000, so 10000 is the largest number of statements one call can return. Option B is outside the allowed range. Options C and D only move the time window, and they do not raise the row cap that stopped the output at exactly 100.
In the Profile Overview of a Query Profile, which time category covers the coordination between the processes that work on one operator in parallel?
- A. Initialization
- B. Network Communication
- C. Local Disk IO
- D. Synchronization Correct answer
Option D is correct because Synchronization is the Profile Overview category for time spent coordinating the parallel processes that work on one operator. Initialization is setup time before processing starts, Network Communication is time waiting on data transfer over the network, and Local Disk IO is time the processing was blocked by local disk access.
Data Sharing and Snowflake Marketplace
After creating a database from a partner's share, an analyst runs a SELECT with AT(OFFSET => -3600) on a shared table to compare it with an hour earlier. What happens?
- A. The query fails, because Time Travel is not supported on a database created from a share Correct answer
- B. The query succeeds for a point inside the Time Travel retention period set by the provider
- C. The query succeeds for a point inside the retention period set on the consumer account
- D. The query succeeds when the analyst's role has been granted IMPORTED PRIVILEGES on the database
Option A is correct because Time Travel is not supported on a database created from a share, so AT or BEFORE clauses fail no matter what retention the provider or consumer has. Options B and C assume a retention period applies, and IMPORTED PRIVILEGES in option D only controls read access.
Before importing the inbound share SALES_SH offered by provider account ACME.PROVIDER1, the consumer's administrator wants to see which objects it contains, without creating a database. Which statement does this?
- A. SHOW DATABASES LIKE 'SALES_SH'
- B. SHOW SHARES LIKE 'SALES_SH'
- C. DESC SHARE ACME.PROVIDER1.SALES_SH Correct answer
- D. SHOW GRANTS TO SHARE ACME.PROVIDER1.SALES_SH
Option C is correct because DESC SHARE with the provider account and share name lists the objects in an inbound share without creating a database. SHOW DATABASES only lists databases that already exist, SHOW SHARES lists the shares but not their contents, and SHOW GRANTS TO SHARE is run by the provider on its own outbound share.
The provider's secure view must return all rows to consumer sessions that hold the shared database role GLOBAL_ANALYST and only EU rows to every other session. Which TWO actions meet this? (Select TWO).
- A. Test IS_DATABASE_ROLE_IN_SESSION('GLOBAL_ANALYST') in the secure view's filter predicate Correct answer
- B. Filter on CURRENT_ACCOUNT() so that each consumer account sees its own mapped rows
- C. Test IS_ROLE_IN_SESSION('GLOBAL_ANALYST') against an account role in the consumer account
- D. Grant the database role GLOBAL_ANALYST to the share so consumers receive it Correct answer
- E. Test CURRENT_ROLE() = 'GLOBAL_ANALYST' in the secure view's filter predicate
Options A and D are correct. IS_DATABASE_ROLE_IN_SESSION in the secure view predicate tests whether the consumer session holds the shared database role, and the database role must be granted to the share so consumers receive it. CURRENT_ACCOUNT() does not separate users inside one account, and IS_ROLE_IN_SESSION or CURRENT_ROLE() test account roles that the consumer controls, so they do not work for shared database roles.
Disaster Recovery, Backup & Data Replication
The disaster recovery operator role in the secondary account must refresh the secondary failover group and promote it during an outage, without being able to change the group's allowed databases. Which TWO privileges on that failover group does the role need? (Select TWO).
- A. OWNERSHIP of the failover group
- B. MODIFY on the failover group
- C. FAILOVER on the failover group Correct answer
- D. CREATE FAILOVER GROUP on the account
- E. REPLICATE on the failover group Correct answer
Options C and E are correct because refreshing a secondary failover group needs the REPLICATE privilege on it, and promoting it to primary needs the FAILOVER privilege. OWNERSHIP (A) would also work but includes the right to change the group, which the stem excludes, MODIFY (B) only changes group properties such as the allowed databases and does not allow refresh or promotion, and CREATE FAILOVER GROUP (D) is an account-level privilege for creating new groups.
ORDERS_DB is replicated with ALTER DATABASE ... ENABLE REPLICATION to two accounts of the organization. The account myorg.analytics_eu must stop receiving it while the other target keeps refreshing. What does the database owner run in the primary account?
- A. ALTER DATABASE orders_db DISABLE FAILOVER TO ACCOUNTS myorg.analytics_eu
- B. ALTER DATABASE orders_db DISABLE REPLICATION TO ACCOUNTS myorg.analytics_eu Correct answer
- C. ALTER DATABASE orders_db DISABLE REPLICATION, leaving out the TO ACCOUNTS clause
- D. ALTER SHARE orders_share REMOVE ACCOUNTS = myorg.analytics_eu
Option B is correct because DISABLE REPLICATION with a TO ACCOUNTS clause, run on the primary database, removes only the named target from the replication list and leaves the other target refreshing. Option A is the older failover syntax and does not stop replication, option C without TO ACCOUNTS would disable replication to every account, and option D only changes a share, which is a different mechanism.
Replication group RG_CORE already replicates to the account myorg.dr_west. A second target, myorg.dr_east, is added, and the existing secondary must keep refreshing without interruption. Which statement, run in the source account, authorizes the new target?
- A. ALTER DATABASE core_db ENABLE REPLICATION TO ACCOUNTS myorg.dr_east
- B. CREATE REPLICATION GROUP rg_core AS REPLICA OF myorg.prod.rg_core
- C. ALTER REPLICATION GROUP rg_core ADD myorg.dr_east TO ALLOWED_DATABASES
- D. ALTER REPLICATION GROUP rg_core ADD myorg.dr_east TO ALLOWED_ACCOUNTS Correct answer
Option D is correct because a replication group authorizes its targets in ALLOWED_ACCOUNTS, so ALTER REPLICATION GROUP ... ADD account TO ALLOWED_ACCOUNTS in the source account adds the second target without touching the existing secondary. Option C names the wrong list (ALLOWED_DATABASES holds databases, not accounts), option A is database-level replication, and option B is run in the target account to create a secondary group.
Free Study Guide
Read the free Snowflake ADA-C02 study guide: every exam topic explained in plain English, with diagrams, exam tips and the mistakes to avoid.
More Snowflake Practice Exams
Practice with realistic mock exams to prepare for your Snowflake certification.