SnowPro Advanced Architect

By Snowflake · Question Mode

QuestionQ26

Performance, Cost, and Resource Optimization

An Architect has been asked to address a performance issue involving a transformation in a data pipeline. The issue started when the transformation logic for the SALES table was modified to add additional dimensions to aggregations (DEPARTMENT_ID, REGION_ID, DEALER_ID).

New sales regions have been added, and the data volume has doubled. The table now processes 20 million records, whereas it previously processed 10 million. Runtime has risen from 5 minutes to more than 20 minutes. Warehouse spilling has been identified as a possible cause.

How can performance be improved?

Choose two
Explanation

When an aggregation’s intermediate data exceeds warehouse memory, Snowflake spills data to local or remote storage, substantially slowing execution. Increasing the virtual warehouse provides more memory and local storage capacity, while dividing the processing into smaller steps reduces the amount of intermediate data that must fit in memory at one time.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!