ADA-C02 Exam Dumps - Snowflake SnowPro Advanced: Administrator Questions and Answers

Snowflake supports secure sharing of unstructured data files through secure views that expose appropriately authorized file-access URLs.

Option B is correct because a secure view can use the GET_PRESIGNED_URL function to generate temporary URLs allowing consumers to retrieve staged files without requiring direct privileges on the provider's underlying stage.

For example:

CREATE SECURE VIEW AUDIO_SHARE_V AS

SELECT

GET_PRESIGNED_URL(

@AUDIO_STAGE,

RELATIVE_PATH,

300

) AS AUDIO_URL

FROM DIRECTORY(@AUDIO_STAGE);

The provider can add this secure view to the share, allowing Company B to retrieve the corresponding audio files.

Option A is ambiguous in ordinary language but technically incorrect when file URL means Snowflake's distinct permanent stage-file URL type. That URL type requires stage-level authorization and is not supported for accessing unstructured files through a consumer's shared secure view.

Option C exposes filenames rather than authenticated or authorized file contents.

Option D identifies file locations but does not provide the consumer with a usable file-access authorization mechanism.

Security qualification: Snowflake also supports scoped URLs using BUILD_SCOPED_FILE_URL. These are generally preferable when consumers can authenticate to Snowflake because they provide more restrictive access. Pre-signed URLs can be accessed by anyone possessing the URL until expiration.

The question should distinguish scoped URLs from pre-signed URLs in a modern examination.

Official Snowflake references: Share Unstructured Data with a Secure View; Introduction to Unstructured Data; GET_PRESIGNED_URL.

Snowflake Time Travel enables administrators to query historical table versions within the configured data-retention period. The AT and BEFORE clauses support recovery based on timestamps, relative time offsets, and statement identifiers.

Option B is correct because AT(TIMESTAMP = > ...) retrieves the table's historical state at the specified timestamp. Since the update occurred at 07:01, querying the table at 07:00 retrieves its preceding state.

Option E is correct because OFFSET specifies a relative number of seconds. At the hypothetical current time of 07:30, -60*30 refers to 07:00, before the update.

Option F is correct because BEFORE with a STATEMENT parameter retrieves the table state immediately before the specified statement completed.

Options A, C, and D use unsupported Time Travel syntax.

Snowflake requires historical data to remain within the table's retention period. Additionally, the STATEMENT parameter must identify a qualifying query executed within the previous 14 days.

Current-version qualification: The scenario is explicitly set on January 1, 2021. These statements are valid in that historical context; they cannot be used today to recover January 2021 data unless an applicable historical recovery mechanism still retains it. Timestamp interpretation also depends on Snowflake's timestamp type and session time zone.

Official Snowflake references: AT | BEFORE; Understanding & Using Time Travel.

Snowflake associates object ownership with a role rather than directly with an individual user. The OWNERSHIP privilege provides full administrative control over an object, including the authority required for most ALTER operations and DROP statements.

The first SQL statement establishes a future ownership grant on tables created in the PROD.WORKING schema. Consequently, when XYZ is created, PROD_WORKING_OWNER receives its ownership according to that future grant.

The subsequent GRANT ROLE statement assigns PROD_WORKING_OWNER to SYSADMIN, establishing a role hierarchy through which SYSADMIN inherits the privileges of the custom owner role.

ACCOUNTADMIN sits above SYSADMIN in Snowflake's standard administrative hierarchy and therefore inherits the relevant ownership privileges.

Thus, PROD_WORKING_OWNER, SYSADMIN, and ACCOUNTADMIN can operate on XYZ with the inherited ownership authority.

Option A is incorrect because the role executing CREATE TABLE does not necessarily retain ownership when an applicable future ownership grant exists.

Option B incorrectly includes SECURITYADMIN. Although SECURITYADMIN possesses MANAGE GRANTS and can administer privilege assignments, that authority does not itself confer the inherited ownership required to directly alter or drop the table.

Option D ignores the explicitly established role hierarchy.

The conclusion assumes the commands execute successfully and no additional conflicting future-grant configuration exists.

Official Snowflake references: GRANT OWNERSHIP; Overview of Access Control; Access Control Privileges.

Snowflake supports replication of databases between accounts belonging to the same organization, including accounts in different cloud providers and regions.

Option A correctly performs two prerequisites. First, an organization administrator enables replication for both the source and destination accounts by calling SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER with ENABLE_ACCOUNT_DATABASE_REPLICATION set to TRUE.

Second, an appropriately authorized account administrator enables replication on PROD_DB and explicitly identifies ACCOUNT2 as an approved destination.

Option B is incorrect because it does not enable replication for ACCOUNT2. Additionally, IGNORE EDITION CHECK is not necessary when the primary account uses Enterprise Edition and the destination uses Standard Edition.

Snowflake reserves that override for specified circumstances involving Business Critical or higher editions and lower-edition destination accounts or related compliance differences.

Option C is incorrect because the required replication features must be enabled for participating accounts.

Option D is incorrect because Snowflake supports database replication from Enterprise Edition to Standard Edition.

Current-version qualification: These commands represent the legacy database-replication workflow. Snowflake now supports replication groups and recommends group-based replication for broader business-continuity configurations. The ORGADMIN role is also being phased out in favor of GLOBALORGADMIN in a dedicated organization account.

Official Snowflake references: SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER; ALTER DATABASE; Introduction to Replication and Failover Across Multiple Accounts.

Snowflake separates the privileges required to discover listings, request access, create imported databases, and query data received through secure sharing.

Option C is correct because ACCOUNTADMIN has the necessary administrative authority to request and receive data through a Data Exchange.

Option D is correct because Snowflake supports delegating these capabilities to custom roles that hold the global IMPORT SHARE and CREATE DATABASE privileges.

IMPORT SHARE permits a role to access incoming shares made available to the consumer account. CREATE DATABASE permits creation of the database object used to expose the shared data.

For example:

GRANT IMPORT SHARE ON ACCOUNT

TO ROLE DATA_CONSUMER;

GRANT CREATE DATABASE ON ACCOUNT

TO ROLE DATA_CONSUMER;

```

Option A is incorrect because SYSADMIN does not automatically receive IMPORT SHARE merely through its standard system-role membership.

Option B is similarly incorrect because SECURITYADMIN's administrative authority does not automatically confer the privileges required to consume shares.

Option E references inappropriate privilege terminology and does not describe a valid combination for receiving Data Exchange data.

Once the shared database is created, access can be delegated through the applicable imported-database privileges.

Snowflake explicitly documents ACCOUNTADMIN and roles holding IMPORT SHARE together with CREATE DATABASE as the supported consumer-access models.

Official Snowflake references: Access a Data Exchange; Grant Privileges to Other Roles; Enable Non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks.

Snowflake Marketplace enables providers to publish data products that consumers can discover and access through secure data-sharing mechanisms.

Option B is the intended answer because a standard, freely accessible listing allows authorized consumers to obtain the available dataset without a personalized approval process.

After adding the listing to their account, consumers can query the imported database using appropriate privileges and a virtual warehouse.

Option A refers to a monetized listing, where access to the complete data product can depend on purchase authorization, an accepted offer, or a trial arrangement.

Option C does not identify a corresponding access model that inherently guarantees immediate availability. Regional availability affects whether the data product has already been fulfilled to the consumer's region.

Option D refers to a personalized listing, which normally involves requesting access from the provider before the complete data product becomes available.

Current-version qualification: Snowflake's contemporary listing documentation generally describes this capability as a public free listing rather than a standard listing.

Also, immediate access is conditional on the product being available in the consumer's region and the consumer having the required privileges. Cross-region fulfillment may introduce a delay.

Thus, B remains the intended examination answer, but its terminology should be modernized.

Official Snowflake references: Create and Publish a Listing; Access and Install Listings as a Consumer; About Data Exchange.

Snowflake database replication uses a primary-secondary architecture in which the source database provides data and object definitions, while the secondary database receives synchronized snapshots through explicit or scheduled refresh operations.

The refresh command must be executed in the Snowflake account containing the secondary database.

In this scenario, PROD_AWS_EAST is the primary database located in AWS_EAST. PROD_AWS_WEST is the secondary database located in AWS_WEST.

Therefore, the appropriate operation is executed from the AWS_WEST account:

ALTER DATABASE PROD_AWS_WEST REFRESH;

Option B correctly identifies both the account location and SQL command.

Options A and C use REFRESH DATABASE, which is not the documented syntax for refreshing a secondary Snowflake database.

Option D uses the correct ALTER DATABASE syntax but identifies the wrong account. The refresh must be initiated against the secondary database rather than the primary source account.

The refresh synchronizes supported data and object changes from the primary database. Snowflake also permits refresh operations to be scheduled through tasks where appropriate.

Current-version qualification: The command remains supported for legacy database replication, but Snowflake recommends replication and failover groups for newer business-continuity architectures. A production design should also establish recovery point objectives, appropriate refresh schedules, and replication monitoring.

Official Snowflake references: ALTER DATABASE — REFRESH; Replicating Databases Across Multiple Accounts; Introduction to Database Replication Across Multiple Accounts.

Snowflake network policies restrict inbound access by evaluating the network origin of authentication requests against configured allowed and blocked network rules or IP address lists.

Network policies can be assigned at multiple scopes, including the account, user, and security integration levels. Snowflake applies an explicit precedence model when multiple policies could govern the same authentication request.

An account-level policy establishes a general restriction applicable to users without more specific applicable policies.

A policy assigned directly to a user takes precedence over the account-level policy. Consequently, the user-level policy determines the permitted and prohibited network locations for that user rather than combining its conditions with those of the account-level policy.

For example, an organization might configure a restrictive account-wide policy while assigning an approved individual user a separate policy that permits access through a designated remote-access gateway.

Option A reverses the documented precedence order. Option C incorrectly claims that user-level network policies are unsupported. Option D incorrectly assumes that simultaneous assignments create a configuration error.

An additional consideration is that network policies assigned to security integrations can take precedence over both user-level and account-level policies. This distinction becomes important when configuring federated authentication or OAuth integrations.

Official Snowflake references: Controlling Network Traffic with Network Policies: Network Policy Precedence.