Snowflake ADA-C01 Exam - 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?

ANo, because joins are not supported by materialized views.

BNo, because the optimizer might decide against it.

CNo, because the materialized view may not be up-to-date.

DYes, because materialized views are always faster.

Show Suggested 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.

by Ludivina at Jan 26, 2025, 08:41 AM

Limited Time Offer

25%

Off

Get Premium ADA-C01 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Lelia

4 months ago

I agree with B, the optimizer can be unpredictable sometimes.

upvoted 0 times

...

Nan

5 months ago

Definitely not A, joins are tricky but not unsupported.

upvoted 0 times

...

Tran

5 months ago

Wait, are materialized views really always faster?

upvoted 0 times

...

Margery

5 months ago

C makes sense too, outdated views can mess things up.

upvoted 0 times

...

Gearldine

5 months ago

I think B is right, the optimizer has its own rules.

upvoted 0 times

...

Madelyn

6 months ago

I thought materialized views were supposed to be faster, but that doesn’t mean they’re always used. D seems too confident.

upvoted 0 times

...

Tequila

6 months ago

I’m not sure, but I feel like there was a practice question about how joins work with materialized views. Maybe A is correct?

upvoted 0 times

...

Brandon

6 months ago

I remember something about materialized views not being updated in real-time, which makes me lean towards C.

upvoted 0 times

...

Geraldo

6 months ago

I think the optimizer might not always choose to rewrite the query, so B could be right.

upvoted 0 times

...

Novella

6 months ago

I'm pretty confident the answer is C. The materialized view may not be up-to-date, so Snowflake won't automatically rewrite the query to use it.

upvoted 0 times

...

Sylvie

6 months ago

Okay, let me break this down. Materialized views don't support joins, so that rules out option A. The optimizer might choose not to use the materialized view, so option B seems more likely.

upvoted 0 times

...

Pilar

6 months ago

Hmm, I'm a bit confused. I know materialized views can improve performance, but I'm not sure if the optimizer will always use them in this case.

upvoted 0 times

...

Dominic

6 months ago

This is a tricky one. I'll need to think carefully about the capabilities and limitations of materialized views in Snowflake.

upvoted 0 times

...

Jesusa

11 months ago

As a Snowflake expert, I can tell you that the answer is definitely B. The optimizer is the real MVP here, and it won't always choose the materialized view, even if it matches the query. Trust the process, folks!

upvoted 0 times

...

Anika

11 months ago

Haha, choice D is about as realistic as a unicorn flying through a rainbow. Nah, I'll go with C. Gotta watch out for those stale materialized views, you know?

upvoted 0 times

Laura

10 months ago

Definitely, choice D sounds like a dream. C is the more practical option.

upvoted 0 times

...

Ivan

10 months ago

Yeah, stale materialized views can cause some issues. C is the way to go.

upvoted 0 times

...

Tresa

10 months ago

I agree, choice D does sound too good to be true. C seems like the safer bet.

upvoted 0 times

...

Glenna

12 months ago

D sounds too good to be true. Materialized views are faster, but they don't magically make every query faster. I'll go with B, the optimizer knows best.

upvoted 0 times

Robt

10 months ago

User4: Yeah, I'll go with B too. Let's trust the optimizer.

upvoted 0 times

...

Carisa

10 months ago

User3: I'm not sure, but I think B is the safest option.

upvoted 0 times

...

Ma

10 months ago

User2: I agree, I'll go with B, the optimizer knows best.

upvoted 0 times

...

Glenna

11 months ago

User1: I think D sounds too good to be true. Materialized views are faster, but they don't magically make every query faster.

upvoted 0 times

...

Noemi

1 year ago

I think C is the correct answer. The materialized view may not always be up-to-date, so Snowflake won't always rewrite the query to use it. I've seen this happen in practice.

upvoted 0 times

Ben

11 months ago

That's true, the optimizer might decide against using a materialized view even if it matches the definition.

upvoted 0 times

...

Ettie

12 months ago

I agree, C is the correct answer. Materialized views may not be up-to-date.

upvoted 0 times

...

Millie

1 year 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