SOL-C01 · Question #315
SOL-C01 Question #315: Real Exam Question with Answer & Explanation
The correct answer is B: Convert the dictionary to a SQL string by iterating over key-value pairs, quoting strings and formatting dates appropriately, then use filter() with the constructed SQL string. Option C is the best and most secure approach. It leverages Snowpark functions col' and 'lit' to create the filter conditions. The col' function represents a column in the DataFrame, and creates a literal value. This avoids SQL injection vulnerabilities and correctly handles diff
Question
You are developing a Snowflake Notebook to perform data transformations using Snowpark. As part of the transformation, you need to filter a DataFrame based on a dynamically generated SQL expression. You have a Python dictionary 'filter_conditionS where keys are column names and values are the filter values. You want to construct a SQL 'WHERE clause from this dictionary and apply it to the DataFrame. However, the value types are mixed (strings, integers, dates). Which of the following approaches best handles the various data types and securely constructs the filter expression? A. B. C. D. E.
Options
- AIterate over the dictionary and use the filter() method with a Column object constructed using col() and lit() functions, handling type casting for each value type
- BConvert the dictionary to a SQL string by iterating over key-value pairs, quoting strings and formatting dates appropriately, then use filter() with the constructed SQL string
- CUse the pandas DataFrame filter functionality after converting the Snowpark DataFrame to pandas, then convert back to Snowpark DataFrame
- DCreate a stored procedure that accepts the filter conditions as parameters and returns the filtered results as a new DataFrame
Explanation
Option C is the best and most secure approach. It leverages Snowpark functions col' and 'lit' to create the filter conditions. The col' function represents a column in the DataFrame, and creates a literal value. This avoids SQL injection vulnerabilities and correctly handles different data types because Snowpark handles the data type conversion and quoting appropriately. Using `reduce" and "operator.and_' , we combine all conditions. Options A, B, D and E are all vulnerable to SQL injection because they directly construct the SQL string with user- provided values without proper escaping or parameterization. Additionally, options A, B, D and E have shortcomings for different
Topics
Community Discussion
No community discussion yet for this question.