Mask PII and extract email domains

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Data Quality. About 16 minutes. Part of the Pro drill bank.

Personal data (PII) such as emails and phone numbers must be protected before analysts see it. Typical steps are to keep only the useful part (the email domain), hide most of a phone number, and replace the email with a hash so you can still count unique people without seeing who they are.

The table customers has id, email and phone. Both email and phone can be NULL. Return 4 columns. id stays as it is. domain is the part of the email after the @, in lowercase. phone_masked is the phone number with every digit replaced by * except the last 4 digits, and other characters such as dashes stay. email_hash is the SHA-256 hash (as a hex text) of the lowercase email. If the input is NULL, the output is NULL. The editor creates the table customers and ends with result = customers. Change the last line to build your answer. Useful functions: regexp_extract, regexp_replace, lower and sha2.

Requirements

  • domain is the lowercase text after @ in the email.
  • phone_masked replaces every digit with *, except the last 4 digits.
  • email_hash is sha2(lower(email), 256).
  • NULL in gives NULL out. Return id, domain, phone_masked, email_hash in result.

Constraints

  • Hash the lowercase email, so [email protected] and [email protected] give the same hash.
  • Dashes and other non-digit characters in the phone number stay unchanged.
  • The hash is 64 hex characters long (SHA-256).

Examples

Input: customers id | email | phone 1 | [email protected] | 9876543210 2 | [email protected] | 555-123-4567 3 | NULL | 1234567890 4 | [email protected] | NULL Output: id | domain | phone_masked | email_hash 1 | example.com | ****3210 | b7e0d8372a47f54bbefeb251ab9ac1e9e6b2d1983263de10196101be3961ed23 2 | mail.co.uk | *-*-4567 | a8020a1ba920a63f7594994158b9003e0a99847bbdca6f78e129c455ea23447d 3 | NULL | ****7890 | NULL 4 | example.com | NULL | c42f5d0033a838d1fd7175a5c0a93acae479330b37bfd307e7fbe62ffae16029 Ann and Cy share the domain example.com (lowercased). The dashes in 555-123-4567 stay and only the last 4 digits remain visible. NULL email or phone gives NULL.

Topics: lakebench, pyspark, regexp_extract, regexp_replace, sha2, pii.

More PySpark interview questions · All interview problems · Learn data engineering

intermediate

Mask PII and extract email domains

Interview-style drill: Extract the email domain, mask all but the last four phone digits and hash the email.

`customers` has `id`, `email` and `phone`, both of which can be NULL. Return: - `id` - `domain`: the part of the email after `@`, in lowercase - `phone_masked`: the phone number with every digit replaced by `*` except the last 4 digits; other characters (dashes) stay - `email_hash`: the SHA-256 hash (hex string) of the lowercase email A NULL input gives a NULL output. Assign the DataFrame to `result`.