Snowflake ADA-C01 - SnowPro Advanced Administrator

The SIMULATED_DATA_SHARING_CONSUMER session parameter allows a data provider to test the integrity of secure views based on the account that is accessing that view2. By setting this parameter to the name of the consumer account, the data provider can query the secure view and see the results that a user in the consumer account will see2. This helps to ensure that sensitive data in a shared database is not exposed to unauthorized users1. The other options are not valid session parameters in Snowflake3

❗Scenario:

A TRUNCATE command was accidentally run on a frequently modified table.

Query ID and timestamp are known.

Goal: restore acopyof the table as it existedright beforethe problematic statement, without affecting the current table.

✅Why Option D is Correct:

sql

CopyEdit

CREATE TABLE RESTORE_TABLE CLONE CURRENT_TABLE

BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

This usesZero-Copy Cloning + Time Travel.

The BEFORE (STATEMENT => ...) clause restores theexact state of the tablebefore the TRUNCATE ran.

Creating acloneensures the original table remains untouched for integrity checks before merging data back.

❌Why Others Are Incorrect:

A. BEFORE (timestamp => '2021-12-12 00:00')

Wrong timestamp: that’s15 hours beforethe truncate happened. Too early; may lose needed updates.

B. SELECT * FROM CURRENT_TABLE before (statement => …)

Syntax is invalid: SELECT can’t use BEFORE (STATEMENT => ...) directly like this.

C. INSERT INTO CURRENT_TABLE SELECT * FROM CURRENT_TABLE before (statement => …)

Same syntax issue. Also risky — directly inserting into the original table without validating the data first.

📘SnowPro Administrator References:

Cloning with Time Travel

Time Travel with Statement ID

To retrievea list of schemas deletedwithin the last2 daysfrom the DB1 database, you need ametadata viewthat includeshistorical data, includingdropped (deleted) objects.

Let’s review the options:

✅B. SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA

This is thecorrect choicebecause:

It includesmetadata for all schemas, evendeleted ones, within theretention period.

It contains a DELETED column and a DELETED_ON timestamp column.

You can filter rows with:

sql

CopyEdit

SELECT *

FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA

WHERE DELETED IS TRUE

AND DELETED_ON >= DATEADD(DAY, -2, CURRENT_TIMESTAMP())

AND CATALOG_NAME = 'DB1';

❌A. SHOW SCHEMAS IN DATABASE DB1;

Only showscurrent(active) schemas — doesnotinclude deleted schemas.

❌C. DB1.INFORMATION_SCHEMA.SCHEMATA

Like option A, this view only includesactive schemasin the current database.

No info on deleted schemas is retained.

❌D. SNOWFLAKE.ACCOUNT_USAGE.DATABASES

This metadata view tracksdatabases, not individualschemas.

📘SnowPro Administrator References:

SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA documentation

Metadata includes both active and deleted schemas (within retention window).