AWS Certified Data Engineer – AssociateData Governance and SecurityMedium
A global e-commerce platform uses Amazon Redshift for its analytical data warehouse. Due to GDPR regulations, specific customer attributes (e.g., email addresses, phone numbers) must be masked for users who do not have explicit permission to view them, while still allowing other users (e.g., customer support) to see the full, unmasked data. The solution must be performant and not require significant changes to existing ETL processes. How should a data engineer implement this requirement in Redshift?
- ACreate separate Redshift clusters for masked and unmasked data.
- BCreate views with SQL functions to mask sensitive columns based on user roles.
- CUtilize Redshift Spectrum to query masked data from S3.
- DImplement column-level encryption on the sensitive columns.
Show answer & explanationAnswer & explanation
Correct answer: B. Create views with SQL functions to mask sensitive columns based on user roles.
Creating views with SQL functions (e.g., `SUBSTRING`, `RPAD`, `MD5`) allows for data masking at query time. These views can be granted to specific user roles, ensuring that only authorized users see unmasked data, without altering the underlying data or ETL processes.
Why the other options are wrong
- A. Creating separate clusters is complex, costly, and difficult to synchronize, violating the 'not require significant changes' clause.
- C. Redshift Spectrum queries data in S3; while masking can be applied, it doesn't directly solve the dynamic masking requirement within Redshift for data already loaded.
- D. Column-level encryption encrypts the data at rest, but decrypting it for authorized users and masking for others requires complex application logic, not a native Redshift solution for dynamic masking.
Redshift Data Masking with Views
Redshift data masking can be implemented using SQL views that apply masking functions (e.g., `SUBSTRING`, `RPAD`, `MD5`) to sensitive columns. Access to these views can be controlled via user roles.
- Provides dynamic data masking at query time.
- Does not alter the underlying source data.
- Access to masked/unmasked views can be controlled via Redshift user permissions.
Memory trick: Views Veil Vital Information Visually.