Snowflake ADA-C01 Exam Dumps

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.

According to the Snowflake documentation1, when a role is dropped, ownership of all objects owned by the dropped role is transferred to the role that is directly above the dropped role in the role hierarchy. This is to ensure that there is always a single owner for each object in the system.

1: Drop Role | Snowflake Documentation

Scenario:

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

Query ID and timestamp are known.

Goal: restore a copy of the table as it existed right before the 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 uses Zero-Copy Cloning + Time Travel.

The BEFORE (STATEMENT => ...) clause restores the exact state of the table before the TRUNCATE ran.

Creating a clone ensures 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's 15 hours before the 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 Reference:

Cloning with Time Travel

Time Travel with Statement ID

According to the Understanding & Using Time Travel documentation, Time Travel is a feature that allows you to query, clone, and restore historical data in tables, schemas, and databases for up to 90 days. To meet the requirements of the scenario, the following best practices should be followed:

* The fact and dimension tables should have the same DATA_RETENTION_TIME_IN_DAYS. This parameter specifies the number of days for which the historical data is preserved and can be accessed by Time Travel. To ensure that the fact and dimension tables can be reverted to a consistent state in case of any anomalies in the latest load, they should have the same retention period. Otherwise, some tables may lose their historical data before others, resulting in data inconsistency and quality issues.

* The fact and dimension tables should be cloned together using the same Time Travel options to reduce potential referential integrity issues with the restored data. Cloning is a way of creating a copy of an object (table, schema, or database) at a specific point in time using Time Travel. To ensure that the fact and dimension tables are cloned with the same data set, they should be cloned together using the same AT or BEFORE clause. This will avoid any referential integrity issues that may arise from cloning tables at different points in time.

The other options are incorrect because:

* Related data should not be placed together in the same schema. Facts and dimension tables should each have their own schemas. This is not a best practice for Time Travel, as it does not affect the ability to query, clone, or restore historical data. However, it may be a good practice for data modeling and organization, depending on the use case and design principles.

* The DATA_RETENTION_TIME_IN_DAYS should be kept at the account level and never used for lower level containers (databases and schemas). This is not a best practice for Time Travel, as it limits the flexibility and granularity of setting the retention period for different objects. The retention period can be set at the account, database, schema, or table level, and the most specific setting overrides the more general ones. This allows for customizing the retention period based on the data needs and characteristics of each object.

* Only TRANSIENT tables should be used to ensure referential integrity between the fact and dimension tables. This is not a best practice for Time Travel, as it does not affect the referential integrity between the tables. Transient tables are tables that do not have a Fail-safe period, which means that they cannot be recovered by Snowflake after the retention period ends. However, they still support Time Travel within the retention period, and can be queried, cloned, and restored like permanent tables. The choice of table type depends on the data durability and availability requirements, not on the referential integrity.