Discuss Snowflake ARA-C01 Exam Topic 3 Question 66

A company has built a data pipeline using Snowpipe to ingest files from an Amazon S3 bucket. Snowpipe is configured to load data into staging database tables. Then a task runs to load the data from the staging database tables into the reporting database tables.

The company is satisfied with the availability of the data in the reporting database tables, but the reporting tables are not pruning effectively. Currently, a size 4X-Large virtual warehouse is being used to query all of the tables in the reporting database.

What step can be taken to improve the pruning of the reporting tables?

AEliminate the use of Snowpipe and load the files into internal stages using PUT commands.

BIncrease the size of the virtual warehouse to a size 5X-Large.

CUse an ORDER BY <cluster_key (s) > command to load the reporting tables.

DCreate larger files for Snowpipe to ingest and ensure the staging frequency does not exceed 1 minute.

Suggested Answer: C

Effective pruning in Snowflake relies on the organization of data within micro-partitions. By using an ORDER BY clause with clustering keys when loading data into the reporting tables, Snowflake can better organize the data within micro-partitions. This organization allows Snowflake to skip over irrelevant micro-partitions during a query, thus improving query performance and reducing the amount of data scanned12.

Reference =

* Snowflake Documentation on micro-partitions and data clustering2

* Community article on recognizing unsatisfactory pruning and improving it1

Get Premium ARA-C01 Questions as Interactive Web-Based Practice Test or PDF Real Exam Format Detailed Explanations Instant Access Get Your Practice Test

19 comments · newest first

Cherry 10 days ago

Yes, let's go with C!

Gianna 15 days ago

So, C it is then?

Tracie 20 days ago

Exactly! We need efficiency in reporting.

Cherry 25 days ago

Plus, it directly impacts query performance.

Gianna 1 month ago

I see your point. C is more straightforward.

Tracie 1 month ago

I feel like sticking with C is safer.

Cherry 1 month ago

True, but it could complicate ingestion.

Gianna 2 months ago

But what about option D? Larger files might help too.

Tracie 2 months ago

Agreed, C makes sense. It optimizes data retrieval.

Cherry 2 months ago

I think option C is the best choice. ORDER BY can help with pruning.

Audry 2 months ago

A is a bad idea, Snowpipe is super efficient for this use case!

Solange 2 months ago

Snowpipe is great, but maybe we need to rethink the staging strategy.

Kristofer 2 months ago

Wait, larger files for Snowpipe? That seems counterintuitive.

Daren 3 months ago

I disagree, increasing the warehouse size won't fix the pruning issue.

Tamie 3 months ago

C is the way to go! ORDER BY helps with pruning.

Shad 3 months ago

I feel like eliminating Snowpipe would just complicate things more. I don't think option A is the way to go at all.

Jacquelyne 3 months ago

I practiced a similar question where file size affected performance. Maybe option D could be a good approach to improve ingestion efficiency?

Almeta 3 months ago

I'm not entirely sure, but I think increasing the warehouse size might not actually help with pruning. It seems like a waste of resources.

Denise 3 months ago

I remember something about how clustering can help with pruning, so maybe option C is the right choice?

Snowflake ARA-C01 Exam - Topic 3 Question 66 Discussion