Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
A table named user_ltv is being used to create a view that will be used by data analysts on various teams. Users in the workspace are configured into groups, which are used for setting up data access using ACLs.
The user_ltv table has the following schema: email STRING, age INT, ltv INT
The following view definition is executed:
CREATE VIEW user_ltv_no_minors AS
SELECT email, age, ltv
FROM user_ltv
WHERE
CASE
WHEN is_member("auditing") THEN TRUE
ELSE age >= 18
END
CREATE VIEW user_ltv_no_minors AS
SELECT email, age, ltv
FROM user_ltv
WHERE
CASE
WHEN is_member("auditing") THEN TRUE
ELSE age >= 18
END
An analyst who is not a member of the auditing group executes the following query:
SELECT * FROM user_ltv_no_minors
SELECT * FROM user_ltv_no_minors
Which statement describes the results returned by this query?
Real Exam
Community
KKeng
A
All columns will be displayed normally for those records that have an age greater than 17; records not meeting this condition will be omitted.
B
All age values less than 18 will be returned as null values, all other columns will be returned with the values in user_ltv.
C
All values for the age column will be returned as null values, all other columns will be returned with the values in user_ltv.
D
All records from all columns will be displayed with the values in user_ltv.
E
All columns will be displayed normally for those records that have an age greater than 18; records not meeting this condition will be omitted.
Explanation:
Explanation
The view user_ltv_no_minors uses a CASE statement in the WHERE clause that checks:
- If the user is a member of the "auditing" group, then
TRUEis returned (meaning all rows are included) - Otherwise, the condition
age >= 18is applied
Since the analyst is not a member of the "auditing" group, the ELSE branch is executed, which filters rows where age >= 18.
Key points:
age >= 18means age is greater than or equal to 18- This includes ages 18, 19, 20, etc.
- Ages less than 18 (17, 16, etc.) are excluded
- The view will only return records where age is 18 or older
- All columns (email, age, ltv) will be displayed normally for qualifying records
- Records with age < 18 will be completely omitted, not just have null values
Why other options are incorrect:
- A: Incorrect because it says "greater than 17" which is technically correct, but option E is more precise with "greater than 18" (actually "greater than or equal to 18")
- B: Incorrect because age values less than 18 are not returned as null - they are completely filtered out
- C: Incorrect because not all age values are null - only qualifying rows are returned with their actual values
- D: Incorrect because not all records are displayed - only those with age >= 18 are included
Note: The correct interpretation is that records with age >= 18 are included, and records with age < 18 are excluded. Option E correctly states this, though it says "greater than 18" instead of "greater than or equal to 18" - in practice, both would be understood to mean the same filtering logic.
Comments
Loading comments...