최신 SnowPro Advanced DAA-C01 무료샘플문제:

1. You are examining sales data for an e-commerce company in Snowflake. You notice a recurring dip in sales every Wednesday. The 'SALES' table contains columns: 'order_id', 'order date' , 'product_id', 'quantity', 'price'. Which of the following SQL queries and analytical techniques would be the MOST effective for diagnosing the reasons behind this weekly sales dip, assuming no external promotional activity always happens on Wednesdays?

A) Run a query to calculate the average daily sales for each day of the week and visualize the results to confirm the dip on Wednesdays. Use "DAYOFWEEK' function for this.
B) Analyze sales patterns by 'product_id' and day of the week to see if specific product categories are driving the overall dip on Wednesdays. Use 'PIVOT' operator for cross-tabulation.
C) Check if there are any scheduled maintenance windows or system outages occurring on Wednesdays that might be affecting order processing. Use 'INFORMATION SCHEMA.LOAD HISTORY' to look for any loads failing on wednesdays
D) Investigate shipping times by day of the week to determine if longer shipping times on orders placed earlier in the week are affecting Wednesday sales. Join ' SALES table with 'SHIPPING' table and calculate difference between 'order_date' and 'ship_date' .
E) Review recent code deployments related to the e-commerce platform and identify if any changes were implemented on Tuesdays or Wednesdays that could be impacting sales.

2. You are analyzing sales data for a retail company. The 'sales' table contains columns 'product id' (INT), 'sale date' (DATE), and 'sale_amount' (NUMBER). You need to calculate the percentage contribution of each product's sales to the total sales on each day. You want the result to include 'sale date', 'product_id', 'sale_amount', 'daily total' , and 'percentage_contribution'. Which of the following Snowflake queries achieves this correctly?

A) Option C
B) Option B
C) Option A
D) Option D
E) Option E

3. You need to create a dashboard for a logistics company to track delivery performance. The dashboard should display the following information: (1) Total number of deliveries per day, (2) Percentage of deliveries completed on time, (3) Average delivery time per city, (4) Number of deliveries exceeding the SLA (Service Level Agreement) by more than 1 hour. Which of the following chart combinations would be MOST effective to display this information in a clear and concise manner?

A) Line chart for total deliveries per day, pie chart for percentage of on-time deliveries, bar chart for average delivery time per city, and a table showing deliveries exceeding SLA
B) Time series chart for total deliveries per day, gauge chart for percentage of on-time deliveries, a heat grid showing city vs average delivery time, and a bullet chart for deliveries exceeding SLA target.
C) Area chart for total deliveries per day, scorecard for percentage of on-time deliveries, geographical map with color-coded cities for average delivery time, and a bar chart for deliveries exceeding SLA.
D) Bar chart for total deliveries per day, scorecard for percentage of on-time deliveries, geographical map with color-coded cities for average delivery time, and a table showing deliveries exceeding SLA.
E) Time series chart for total deliveries per day, scorecard for percentage of on-time deliveries, geographical map with color-coded cities for average delivery time, and a table showing deliveries exceeding SLA.

4. A data analyst needs to process a large JSON payload stored in a VARIANT column named 'payload' in a table called 'raw events' The payload contains an array of user sessions, each with potentially different attributes. Each session object in the array has a 'sessionld' , 'userld' , and an array of 'eventS. The events array contains objects with 'eventType' and 'timestamp'. The analyst wants to use a table function to flatten this nested structure into a relational format for easier analysis. Which approach is most efficient and correct for extracting and transforming this data?

A) Use LATERAL FLATTEN with multiple levels of nesting, specifying 'path' for each level and directly selecting the desired attributes.
B) Create a recursive UDF (User-Defined Function) in Python to traverse the nested JSON and return a structured result, then call this UDF in a SELECT statement.
C) Utilize a Snowpark DataFrame transformation with multiple 'explode' operations and schema inference to flatten the nested structure and load data into a new table.
D) Load the JSON data into a temporary table, then write a series of complex SQL queries with JOINs and UNNEST operations to flatten the data.
E) Employ a combination of LATERAL FLATTEN and Snowpark DataFrames, using LATERAL FLATTEN to partially flatten the JSON and then Snowpark to handle the remaining complex transformations and data type handling.

5. You are tasked with analyzing website clickstream data stored in a Snowflake table called 'clickstream_events'. Each row represents a click event and contains a 'session_id' , and a 'properties' column of type VARIANT that stores key-value pairs related to the event (e.g., '(page': '[product/123', 'element': You need to extract the 'page' and 'element' values from the 'properties' column and identify the most common 'page'-'element' combinations for each 'session_id'. Furthermore you need to limit the results of your data to the top 5 pages element pair. How can this task be accomplished using Snowflake table functions and analytical functions?

A) Use multiple LATERAL FLATTEN calls, one for 'page' and one for 'element', then JOIN the results on and use QUALIFY ROW_NUMBER() OVER (PARTITION BY 'session_id' ORDER BY COUNT( ) DESC) 5.
B) First create a view that flattens the JSON column using LATERAL FLATTEN, then select from this view to perform the group by and ranking operations.
C) Extract 'page' and 'element' using 'properties:page' and 'properties:element' directly in the SELECT statement, then use GROUP BY 'session_id', 'page', 'element' and QUALIFY ROW NUMBER() OVER (PARTITION BY 'session_id' ORDER BY COUNT( ) DESC) 5.
D) Use LATERAL FLATTEN to extract the keys and values from the 'properties' column, then use GROUP BY 'session_id', 'key', 'value' and COUNT( ) to find the most frequent combinations.
E) Create a UDF to parse the 'properties' VARIANT and return a table with 'page' and 'element columns, then JOIN this UDF's output with the original table and use QUALIFY ROW NUMBER() OVER (PARTITION BY 'session_id' ORDER BY COUNT( ) DESC) 5.

질문과 대답: