Databricks-DE-Pro Data Transformation, Cleansing, Quality Practice Question
A Data Engineer is building a Databricks SQL pipeline that ingests clickstream events from a Delta table. The events table contains a nested column `payload` of type STRUCT with fields `page_id` (STRING), `duration` (INT), and `referrer` (STRING). The engineer needs to flatten the `payload` fields into top-level columns and drop any records where `page_id` is NULL. Which SQL expression accomplishes this transformation while preserving all other columns?
⚠ Common exam trap
A common mix-up: candidates confuse STRUCT field access with array indexing or assuming that explode works on STRUCTs, when it is only for arrays and maps.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
SELECT *, payload.page_id AS page_id, payload.duration AS duration, payload.referrer AS referrer FROM events WHERE payload.page_id IS NOT NULL
The correct solution uses dot notation to access nested STRUCT fields, renames them to top-level columns, and applies a filter on the nested field to drop NULL page_id records. This is the standard and supported way in Databricks SQL to flatten and cleanse nested data while retaining all other columns. The other options misuse functions or syntax not applicable to STRUCT types.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SELECT *, payload.* AS (page_id, duration, referrer) FROM events WHERE payload.page_id IS NOT NULL
Why it's wrong here
While `payload.*` can expand all fields of a STRUCT in some SQL dialects, Databricks SQL does not support aliasing the expanded columns with a column list in this manner. The syntax `AS (page_id, duration, referrer)` is not valid for struct expansion. This would result in a syntax error or unexpected behavior, failing to flatten the nested fields as required.
- ✓
SELECT *, payload.page_id AS page_id, payload.duration AS duration, payload.referrer AS referrer FROM events WHERE payload.page_id IS NOT NULL
Why this is correct
This correctly uses dot notation to extract nested fields from the STRUCT column and renames them as top-level columns. The WHERE clause filters out records where the nested page_id is NULL, preserving all other columns via the wildcard. Databricks SQL supports this syntax for nested data, making it a valid and efficient solution for flattening and cleansing clickstream events.
- ✗
SELECT *, explode(payload) AS (page_id, duration, referrer) FROM events WHERE page_id IS NOT NULL
Why it's wrong here
The `explode` function is designed for arrays or maps, not for STRUCTs. Using it on a STRUCT will not produce the desired columns and will likely raise an error. Additionally, the WHERE clause references `page_id` which is not yet defined in the SELECT, causing a resolution error. This approach is fundamentally incorrect for flattening a STRUCT.
- ✗
SELECT *, payload[0] AS page_id, payload[1] AS duration, payload[2] AS referrer FROM events WHERE payload[0] IS NOT NULL
Why it's wrong here
This uses bracket indexing, which is valid for arrays, not for STRUCT fields. For a STRUCT, you must use dot notation or the `get_field` function. Applying array-style indexing to a STRUCT will cause an error or return NULLs, and it does not correctly reference the nested fields by name, leading to incorrect flattening and filtering.
About these practice questions
Courseiva writes every Databricks-DE-Pro question from scratch — 267 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-DE-Pro practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-DE-Pro exam.