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

Questions 21–25

1. 21foundation · easy

   In Snowflake, which feature helps improve query performance by physically organizing data in a table based on one or more columns?

   • AClustering keys
   • BMaterialized views
   • CIndexes
   • DQuery tags
   Reveal Solution Discussion Select an answer first
2. 22expert · hard

   A data pipeline loads customer records into a table `customers_raw`. The `email` column contains duplicates with different casing (e.g., 'John@Example.com' and 'john@example.com'). The analyst needs to deduplicate the table, keeping only one row per email (case-insensitive), and prefer the row with the most recent `updated_at` timestamp. Which query correctly deduplicates?

   • ASELECT * FROM customers_raw GROUP BY LOWER(email);
   • BSELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY updated_at DESC) AS rn FROM customers_raw) WHERE rn = 1;
   • CSELECT * FROM customers_raw QUALIFY ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) = 1;
   • DSELECT DISTINCT LOWER(email), * FROM customers_raw;
   Reveal Solution Discussion Select an answer first
4. 23foundation · easy

   In Snowflake, which operation transforms rows into columns, converting data from a long format to a wide format?

   • AGROUP BY
   • BUNPIVOT
   • CPIVOT
   • DJOIN
   Reveal Solution Discussion Select an answer first
5. 24foundation · easy

   What is a view in Snowflake?

   • AA type of materialized table that stores aggregated data
   • BA saved SQL query that acts like a virtual table
   • CA physical copy of data stored separately
   • DA table that is automatically indexed for performance
   Reveal Solution Discussion Select an answer first
6. 25foundation · easy

   Which Snowflake function is used to extract a value from a JSON object stored in a VARIANT column?

   • AGET_PATH
   • BSPLIT_PART
   • CCONCAT
   • DSUBSTRING
   Reveal Solution Discussion Select an answer first