Transform Data for Analysis SNOWPRO-ADVANCED-DATA-ANALYST Practice Questions (Page 4)

Questions 16–20

1. 16foundation · easy

   Which Snowflake operation is used to convert multiple columns into key-value pairs, transforming wide data into a long format?

   • ASPLIT
   • BUNPIVOT
   • CFLATTEN
   • DPIVOT
   Reveal Solution Discussion Select an answer first
2. 17foundation · easy

   Which type of JOIN in Snowflake returns only the rows that have matching values in both tables?

   • AFULL OUTER JOIN
   • BINNER JOIN
   • CRIGHT OUTER JOIN
   • DLEFT OUTER JOIN
   Reveal Solution Discussion Select an answer first
4. 18expert · hard

   A finance team runs a daily report that aggregates millions of rows from a large fact table. The report query is complex and takes several minutes. They want to improve performance without changing the underlying data. Which option is most effective?

   • AAdd a clustering key on the fact table.
   • BCreate a regular view to encapsulate the query.
   • CUse a window function to pre-compute the aggregation.
   • DCreate a materialized view that pre-aggregates the data.
   Reveal Solution Discussion Select an answer first
5. 19foundation · easy

   In Snowflake, which SQL construct allows you to create a derived column that assigns a category based on multiple conditions?

   • AGROUP BY clause
   • BCASE expression
   • CJOIN clause
   • DORDER BY clause
   Reveal Solution Discussion Select an answer first
6. 20application · medium

   A data team has a large fact table `sales` with billions of rows. Analysts frequently run queries that filter on `sale_date` and group by `region`. The table is currently not clustered. Which optimization would most likely improve query performance for these common queries?

   • ACreate a clustering key on the `sale_date` column
   • BUse a window function to precompute aggregates
   • CCreate a view that pre-aggregates the data
   • DAdd an index on the `region` column
   Reveal Solution Discussion Select an answer first