Databricks Data Engineer Professional Question 210
Single answerA company stores sensitive customer information in a Databricks Delta table, and they want to implement data masking to ensure that only authorized users can view the full details of the data. You are tasked with creating a dynamic view that masks the email column by partially redacting it (e.g., displaying only the first three characters followed by '***@domain.com') for non-privileged users. Which of the following correctly implements this requirement?
- A
CREATE OR REPLACE VIEW masked_customer_data AS SELECT id, CASE WHEN is_member('privileged_users') THEN email ELSE CONCAT(SUBSTRING(email, 1, 3), '***@domain.com') END AS email FROM customer_data;
- B
CREATE OR REPLACE VIEW masked_customer_data AS SELECT id, CASE WHEN is_member('non_privileged_users') THEN CONCAT(SUBSTRING(email, 1, 3), '***@domain.com') ELSE email END AS email FROM customer_data;
- C
CREATE OR REPLACE VIEW masked_customer_data AS SELECT id, CASE WHEN is_member('privileged_users') THEN email ELSE 'REDACTED' END AS email FROM customer_data;
- D
CREATE OR REPLACE VIEW masked_customer_data AS SELECT id, email FROM customer_data WHERE is_member('privileged_users');
Show answer and explanation
Correct answer: A
Explanation
Dynamic views in Databricks can be used to implement row-level and column-level security by leveraging user roles and functions like is_member(). The correct answer dynamically applies the data masking logic for non-privileged users while allowing full access to privileged users, ensuring compliance with the requirement. This approach is commonly used to protect sensitive data in shared environments while maintaining flexibility in access control.
- A. Correct.
This option correctly uses
is_member('privileged_users')to check if the user belongs to the privileged group. If true, the full email is displayed; otherwise, the email is dynamically masked by showing only the first three characters and appending '***@domain.com'. This satisfies the requirement for data masking based on user roles. - B. Incorrect.
This option incorrectly applies the masking logic to members of the
non_privileged_usersgroup. The requirement is to display the full email only to privileged users, not the other way around. - C. Incorrect.
This option applies a masking logic that displays 'REDACTED' for non-privileged users. While it masks the data, it does not meet the specific requirement of showing the first three characters followed by '***@domain.com'.
- D. Incorrect.
This option filters the data to only include users in the
privileged_usersgroup, which does not satisfy the requirement of providing masked data for non-privileged users. It also fails to create a dynamic view for role-based access.