Databricks Data Engineer Professional Question 209
Single answerA Databricks data engineer is tasked with creating a dynamic view to enforce data masking on a customer table. The table contains sensitive data such as 'email' and 'phone_number'. Only users with the role 'admin' should see the full data, while others should see masked values. Which of the following SQL implementations correctly achieves this requirement?
- A
CREATE OR REPLACE VIEW customer_view AS SELECT email, phone_number FROM customer;
- B
CREATE OR REPLACE VIEW customer_view AS SELECT CASE WHEN current_user() = 'admin' THEN email ELSE '@example.com' END AS email, CASE WHEN current_user() = 'admin' THEN phone_number ELSE '******' END AS phone_number FROM customer;
- C
CREATE OR REPLACE VIEW customer_view AS SELECT email, phone_number FROM customer WHERE current_user() = 'admin';
- D
CREATE OR REPLACE VIEW customer_view AS SELECT email, phone_number FROM customer WHERE current_database() = 'admin';
Show answer and explanation
Correct answer: B
Explanation
The correct implementation must dynamically determine the current user's role and apply conditional logic to display either the full data (for admin users) or masked data (for non-admin users). Option 2 achieves this by using the CASE statement to apply data masking based on the current_user() function, fulfilling the requirement.
- A. Incorrect.
This option does not implement any data masking logic. It simply selects the columns as they are, which does not meet the requirement of masking sensitive data for non-admin users.
- B. Correct.
This option correctly applies conditional logic to mask sensitive data based on the current user's role. Non-admin users see masked values, while admin users see the full data.
- C. Incorrect.
This option only allows admin users to view the entire table, and it does not provide any masked view for non-admin users. This violates the requirement to show masked values to non-admin users.
- D. Incorrect.
This option incorrectly uses the current_database() function, which is unrelated to user roles, to filter data. It does not implement any masking and does not meet the requirements.