Pass the Snowflake SnowPro Advanced: Administrator ADA-C01 Questions and answers with CertsForce

According to the Snowflake documentation1, a standard listing is a type of listing that provides free access to the full data product, with no payment required. A standard listing can be added and queried immediately by the consumer, as long as they accept the terms and conditions of the listing. A monetized listing is a type of listing that charges for access to the data product, using the pricing models offered by Snowflake. A monetized listing requires the consumer to provide payment information and agree to the billing terms before accessing the data product. A regional listing is not a type of listing, but a way to specify the regions where the listing is available. A personalized listing is a type of listing that provides limited trial access to the data product, with unlimited access to the full data product available upon request. A personalized listing requires the consumer to request access from the provider and wait for the provider to grant access before accessing the data product. Therefore, the only type of listing that can be added and queried immediately is the standard listing.

A user with the ORGADMIN role can perform the following tasks1:

•Create one or more accounts in the organization.

•View a list of all regions enabled for the organization.

•View usage information for all accounts in the organization.

Option C is incorrect because creating secure views on application tables is not a function of the ORGADMIN role, but rather a function of the roles that have access to the tables and schemas within the accounts. Option E is incorrect because performing zero-copy cloning on account data is not a function of the ORGADMIN role, but rather a function of the roles that have the CLONE privilege on the objects within the accounts. Option F is incorrect because creating a reader account to share data with another organization is not a function of the ORGADMIN role, but rather a function of the roles that have the CREATE SHARE privilege on the objects within the accounts.

The scenario describes a need forfine-grained access controloversensitive customer dataacrossmultiple regions, withfunctional-role-based accessfor analysts. Snowflake recommends applying alayered security modelthat separates raw data from user-facing access and leveragesbuilt-in policy features.

Explanation of Correct Answers:

A. Implement views on top of base tables that exclude or mask sensitive data.

Creatingsecure viewsallows administrators toabstract sensitive fieldsor filter out certain rows and columns.

It enablesrole-based access controlby granting specific roles access only to the secure views.

Common practice is to restrict access to base tables and give users access to views that enforce business logic and data access rules.

B. Implement row access policies and Dynamic Data Masking policies.

Row Access Policiescontrol access at therow level, determining what data a user can see based on their role or session context.

Dynamic Data Maskingallows you tomask sensitive column data(like PII) dynamically based on the accessing role.

Both arecentral features of Snowflake’s fine-grained access control.

Why the other options are incorrect:

C. Include masking rules as part of data ingestion and transformation pipelines.

This isnot a Snowflake-recommended best practicefor access control.

It hardcodes data access rules into ETL/ELT logic, which reduces flexibility and central control.

Also, it masks the data permanently at ingestion time, rather than dynamically at query time.

D. Use a third-party tool to share the data.

Snowflake supports nativeSecure Data Sharing, and using a third-party tool is unnecessary and introduces complexity.

It does not address row/column-level access control within Snowflake itself.

E. Use zero-copy cloning to replicate the database schema and provide access as needed.

Zero-copy cloning is ideal fortesting, development, and backuppurposes, not for controlling access.

It duplicates metadata but doesn’t provide a mechanism for fine-grained, real-time access control.

SnowPro Administrator References:

Row Access Policies Overview

Dynamic Data Masking Overview

Access Control Best Practices

Using Secure Views for Access Control

According to the Snowflake documentation1, pre-signed URLs are required to access external files in a share. A secure view can be used to generate pre-signed URLs for the audio files stored in an external stage and expose them to the consumer account. Option A is incorrect because file URLs alone are not sufficient to access external files in a share. Option C is incorrect because METADATA$FILENAME only returns the file name, not the full path or URL. Option D is incorrect because the stage name and file path are not enough to generate pre-signed URLs.

To enablefederated authentication(akaSSO via SAML 2.0) in Snowflake, the integration with anIdentity Provider (IdP)must be configured. This setup involves configuringexternal authentication via SAML, and Snowflake needs specific information from the IdP.

????Required Information from IdP:

URL Endpoint for SAML Requests (B)

This is often referred to as theSSO URLorSAML 2.0 Endpoint (HTTP).

It's the URL that Snowflake redirects users to for authentication.

In Snowflake's SAML configuration, this is required as the SAML2_ISSUER or SAML2_SSO_URL.

Authentication Certificate (D)

This is theX.509 certificateissued by the IdP.

It's used by Snowflake tovalidate the digital signatureof the SAML assertions sent by the IdP.

It ensures that the SAML response is authentic and not tampered with.

❌Why Other Options Are Incorrect:

A. IdP account details

Not needed. Snowflake doesn’t require credentials or internal details from the IdP. It relies onassertionssent via SAML, not stored accounts.

C. SAML response format

Snowflake adheres toSAML 2.0 standard, and expects a compliant format. There's no need to specify format explicitly — it’s part of the standard protocol.

E. IdP encryption key

Not required by Snowflake. Snowflake verifies SAML assertions viasignature validation, not encryption using the IdP’s private key.

????SnowPro Administrator References:

Snowflake Documentation — Federated Authentication Setup

https://docs.snowflake.com/en/user-guide/security-fed-auth-use

https://docs.snowflake.com/en/user-guide/security-fed-auth-config

Required IdP Metadata for Snowflake SAML Configuration:

SAML2_SSO_URL: SAML 2.0 POST binding endpoint

SAML2_X509_CERT: Public cert used to validate IdP signatures

The EXPLAIN command returns the logical execution plan for a query, which shows the upper bound estimates for the number of partitions and bytes that might be scanned by the query1. However, these estimates do not account for the runtime optimizations that Snowflake performs to improve the query performance and reduce the resource consumption2. One of these optimizations is join pruning, which eliminates unnecessary partitions from the join inputs based on the join predicates2. This can result in fewer partitions and bytes scanned than the estimates from the EXPLAIN output3. Therefore, the actual partitions scanned value in the Query Profile can be lower than the partitionsAssigned value in the EXPLAIN output4.

Scenario: You want to rollback changes due to a problematic query (e.g., accidental data modification or corruption). Snowflake provides two powerful tools:

✅B. CLONE ... BEFORE (STATEMENT => 'query_id')

This usesTime Travel + Zero-Copy Cloning.

You canclone a table as it existed before a specific query.

It creates a full copy of the table's state at that momentwithout duplicating storage.

Example:

CREATE TABLE prd_table_bkp CLONE prd_table

BEFORE (STATEMENT => '01a2b3c4-0000-0000-0000-123456789abc');

✅D. ALTER TABLE ... SWAP WITH ...

Once you've cloned the backup, you canswap itwith the live table.

This is afast, atomic operation— ideal for rollback.

Example:

ALTER TABLE prd_table SWAP WITH prd_table_bkp;

❌Why the Other Options Are Incorrect:

A. SELECT SYSTEM$CANCEL_QUERY(...)

Cancels a currently running query — doesn’t help if the query already executed and caused damage.

C. CREATE TABLE ... AS SELECT * FROM RESULT_SCAN(...)

Reconstructsresults, not the original table.

Only captures output rows, not full table state.

Not ideal for rollback.

E. Contact Snowflake Support to retrieve Fail-safe data

Fail-safe is for disaster recovery only, and only accessible by Snowflake support.

It’snotintended for routine rollback or recovery and has a 7-day fixed retention (non-configurable).

????SnowPro Administrator References:

Zero-Copy Cloning with Time Travel

ALTER TABLE SWAP

System Functions – SYSTEM$CANCEL_QUERY

Fail-safe Overview

According to the Snowflake documentation1, you can change a managed access schema to a regular schema using the ALTER SCHEMA statement with the DISABLE MANAGED ACCESS keywords. This will disable the managed access feature on the schema and revert the access control to the default behavior. Option B is incorrect because dropping and recreating the schema will also delete all the objects and metadata in the schema, which is not necessary to disable the managed access. Option C is incorrect because revoking the privileges on the future tables from the role is not required to disable the managed access. Option D is incorrect because there is no WITHOUT MANAGED ACCESS option in the CREATE SCHEMA statement.

According to the Snowflake documentation1, a multi-cluster warehouse is a virtual warehouse that consists of multiple clusters of compute resources that can scale up or down automatically to handle the concurrency and performance needs of the queries submitted to the warehouse. A multi-cluster warehouse has a minimum and maximum number of clusters that can be specified by the administrator. Option A is a possible solution to address the issues happening once a month, as it allows the administrator to use a task to increase the cluster size for the time period that the more complex queries are running and another task to reduce the size of the cluster once the complex queries complete. This way, the warehouse can have more resources available to handle the complex queries without reaching the maximum limit of 10 clusters, and then return to the normal cluster size to save costs. Option B is another possible solution to address the issues happening once a month, as it allows the administrator to have the group running the complex monthly queries use a separate appropriately-sized warehouse to support their workload. This way, the warehouse can isolate the complex queries from the standard workload and avoid queue time and resource contention. Option C is not a recommended solution to address the issues happening once a month, as it would increase the costs and complexity of managing the multi-cluster warehouse, and may not solve the underlying problem of inefficient queries. Option D is a good practice to improve the performance of the queries, but it is not a direct solution to address the issues happening once a month, as it requires analyzing and optimizing the complex queries using clustering keys or materialized views, which may not be feasible or effective in all cases. Option E is not a recommended solution to address the issues happening once a month, as it would increase the costs and waste resources by starting more clusters than needed for the standard workload.