Deal of The Day! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions

Snowflake Exam ADA-C01 Topic 5 Question 32 Discussion

Actual exam question for Snowflake's ADA-C01 exam
Question #: 32
Topic #: 5
[All ADA-C01 Questions]

If the query matches the definition, will Snowflake always dynamically rewrite the query to use a materialized view?

Show Suggested Answer Hide Answer
Suggested Answer: B

According to the Snowflake documentation1, a transient table is a type of table that does not support Time Travel or Fail-safe, which means that it does not incur any storage costs for maintaining historical versions of the data or backups for disaster recovery. A transient table can be dropped at any time, and the data is not recoverable. A transient table can also have a retention time of 0 days, which means that the data is deleted immediately after the table is dropped or truncated. Therefore, creating the staging table as a transient table with a retention time of 0 days can minimize the storage costs and maximize the performance, as the data is only loaded and transformed once, and then deleted after the production tables are populated. Option A is incorrect because creating the staging table as an external table, which references data files stored in a cloud storage location, can incur additional costs and complexity for data transfer and synchronization, and may not provide the best performance for data loading and transformation. Option C is incorrect because creating the staging table as a temporary table, which is automatically dropped when the session ends or the user logs out, can cause data loss or inconsistency if the session is interrupted or terminated before the production tables are populated. Option D is incorrect because creating the staging table as a permanent table, which supports Time Travel and Fail-safe, can incur additional storage costs for maintaining historical versions of the data and backups for disaster recovery, and may not provide the best performance for data loading and transformation.


Contribute your Thoughts:

Millie
4 days ago
I'm pretty sure the answer is B. The optimizer might decide against using the materialized view, even if the query matches its definition. Gotta trust the optimizer, am I right?
upvoted 0 times
...
Bea
4 days ago
I'm not sure, but I think D is also a possibility because materialized views are generally faster.
upvoted 0 times
...
Erin
5 days ago
I agree with Skye, I think the optimizer might decide against using a materialized view, so the answer is B.
upvoted 0 times
...
Skye
11 days ago
I think the answer is C, because materialized views may not always be up-to-date.
upvoted 0 times
...

Save Cancel