Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
Deep dive into the quiz with AI chat providers.
We prepare a focused prompt with your quiz and certificate details so each AI can offer a more tailored, in-depth explanation.
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
0
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 iterate over theemployeesarray column - The lambda expression
i -> i.years_exp > 5correctly references each array element asiand accesses theyears_expfield - It creates a new column
exp_employeescontaining only employees with more than 5 years of experience
Option B is incorrect because:
- It references
exp_employeesin the FILTER condition before it's defined, which is invalid syntax - The column
exp_employeesdoesn't exist yet when the FILTER function tries to use it
Option C is incorrect because:
- It uses
years_exp > 5without referencing the array element variable - The syntax
FILTER(employees, years_exp > 5)would try to filter on a column namedyears_exprather than the field within each array element
Option D is incorrect because:
- It uses a
CASEstatement which doesn't iterate over array elements employees.years_expwould try to access a field on the array itself, not on individual elements- This approach would not filter the array but would return either the entire array or NULL based on some condition
The FILTER function in SQL (specifically in Databricks/Spark SQL) is designed for exactly this type of operation - applying a predicate to each element of an array and returning a new array containing only the elements that satisfy the condition.
Comments
Loading comments...