Databricks Data Engineer Professional Question 206
Select 2You are working on a Databricks project for a financial institution. The database contains a 'customers' table with sensitive data, including Social Security Numbers (SSNs). To comply with data privacy regulations, you need to create a dynamic view that masks the SSNs for non-privileged users, while allowing privileged users to see the full data. Which of the following SQL approaches should you use to achieve this?
- A
Use the CASE statement in the SELECT clause to display masked SSNs as 'XXX-XX-####' for non-privileged users and full SSNs for privileged users, based on their role.
- B
Create a dynamic view using the SQL GRANT statement to apply row-level security to filter out SSNs for non-privileged users.
- C
Leverage the CURRENT_USER SQL function within the dynamic view to check the user's role and mask the SSNs accordingly.
- D
Use the REPLACE VIEW statement to update the view dynamically whenever user permissions change.
- E
Configure a Databricks secret scope to store masking rules and apply them directly in the view.
Show answer and explanation
Correct answers: A, C
Explanation
To create dynamic views for data masking, you can use SQL constructs like the CASE statement and the CURRENT_USER function to conditionally mask sensitive data based on user roles or identities. These approaches dynamically adjust the data visibility without requiring manual updates, ensuring compliance with data privacy regulations.
- A. Correct.
This is correct. Using the CASE statement allows you to dynamically mask or reveal SSNs based on user roles within the view's logic.
- B. Incorrect.
This is incorrect. The GRANT statement is used for granting permissions and does not provide the functionality to create dynamic views or apply masking logic.
- C. Correct.
This is correct. The CURRENT_USER function can dynamically determine the user's identity and role, allowing conditional logic for masking sensitive data.
- D. Incorrect.
This is incorrect. The REPLACE VIEW statement is not dynamic and requires manual updates. It is not suitable for implementing dynamic data masking.
- E. Incorrect.
This is incorrect. While a Databricks secret scope can securely store sensitive information, it is not directly applicable for implementing dynamic masking within a view.