Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
A data engineer needs to apply custom logic to identify employees with more than 5 years of experience in array column employees in table stores. The custom logic should create a new column exp_employees that is an array of all of the employees with more than 5 years of experience for each row. In order to apply this custom logic at scale, the data engineer wants to use the FILTER higher-order function.
Which code block successfully completes this task?
Real Exam
Community
KKeng
A
SELECT store_id, employees,
FILTER (employees, i -> i.years_exp > 5) AS exp_employees
FROM stores;
SELECT store_id, employees,
FILTER (employees, i -> i.years_exp > 5) AS exp_employees
FROM stores;
B
SELECT store_id, employees,
FILTER (exp_employees, i -> i.years_exp > 5) AS exp_employees
FROM stores;
SELECT store_id, employees,
FILTER (exp_employees, i -> i.years_exp > 5) AS exp_employees
FROM stores;
C
SELECT store_id, employees,
FILTER (employees, years_exp > 5) AS exp_employees
FROM stores;
SELECT store_id, employees,
FILTER (employees, years_exp > 5) AS exp_employees
FROM stores;
D
SELECT store_id, employees,
CASE WHEN employees.years_exp > 5 THEN employees ELSE NULL END AS exp_employees
FROM stores;
SELECT store_id, employees,
CASE WHEN employees.years_exp > 5 THEN employees ELSE NULL END AS exp_employees
FROM stores;
Explanation:
Explanation
Option A is correct because:
- It properly uses the
FILTERhigher-order function to filter array elements - The lambda expression
i -> i.years_exp > 5correctly references each array element asiand accesses itsyears_expfield - It filters the
employeesarray column and creates a new arrayexp_employeescontaining only employees with more than 5 years of experience
Option B is incorrect because:
- It references
exp_employeesin theFILTERfunction before it's defined, creating a circular reference error - The column
exp_employeesdoesn't exist yet when trying to filter it
Option C is incorrect because:
- It uses
years_exp > 5without referencing the array element - In lambda expressions, you need to specify the element variable (like
i) to access its fields
Option D is incorrect because:
- It uses a
CASEstatement which doesn't work correctly with array elements employees.years_expis invalid syntax sinceemployeesis an array, not a scalar value with ayears_expfield- CASE statements work on scalar values, not arrays
The FILTER function in Spark SQL is specifically designed for filtering array elements based on a condition, making it the appropriate choice for this task.
Comments
Loading comments...