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 of the following code blocks successfully completes this task?

Real Exam

Community

KKeng

Last updated: January 13, 2026 at 09:03

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;

SELECT
    store_id,
    employees,
    FILTER (exp_employees, years_exp > 5) AS exp_employees
FROM stores;

SELECT
    store_id,
    employees,
    FILTER (exp_employees, years_exp > 5) AS exp_employees
FROM stores;

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;

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;

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;

Explanation:

Explanation

Option A is correct because it properly uses the FILTER higher-order function with the correct syntax:

FILTER(employees, i -> i.years_exp > 5) - This correctly:
- Takes the employees array column as input
- Uses a lambda function i -> i.years_exp > 5 where i represents each element in the array
- Accesses the years_exp field of each employee object using i.years_exp
- Returns a new array containing only employees with more than 5 years of experience

Why the other options are incorrect:

Option B: Incorrect because it tries to filter exp_employees which doesn't exist yet (it's the column being created). Also, it doesn't use proper lambda syntax to access the years_exp field.
Option C: Incorrect because it doesn't use lambda function syntax. The expression years_exp > 5 would try to reference a column named years_exp which doesn't exist at the table level.
Option D: Incorrect because it uses a CASE statement which would evaluate the entire array, not individual elements within the array. This would not filter individual employees within the array.
Option E: Incorrect because it tries to filter exp_employees which doesn't exist yet (it's the column being created), similar to Option B.

Key Concepts:

The FILTER higher-order function in Spark SQL takes an array and a lambda function that returns a boolean for each element.
Lambda functions use the syntax element -> expression where element represents each item in the array.
When dealing with struct/object arrays, you need to use dot notation to access fields (e.g., i.years_exp).

Powered ByGPT-5.2

Comments

Loading comments...