Unsalted Email Hashing and Untagged Export to an External Agency
A retailer's data platform applies policy tags to BigQuery columns containing email, phone, and address. A marketing analyst creates a scheduled query that joins a tagged customers table to an untagged orders table and writes results to a materialized view. The view is not tagged. A downstream export to Google Sheets runs daily and shares 12,000 rows with an external agency. During an audit, the privacy team finds that the email_hash column in the view uses unsalted SHA-256 of lowercased email, and the agency has matched 4,100 rows to its own identity graph. The platform has column-level lineage but only for dbt models, not for ad hoc scheduled queries. The company has a 72-hour breach-notification obligation and a policy that PII must not leave the governed project. The data team must contain the issue and prevent recurrence.
1. What are your first containment and assessment steps in the 72-hour window?
Read the worked answer
Contain first: disable the scheduled query and the Sheets export, revoke the agency's access to the sheet, and preserve the view definition, query logs, export logs, and row counts as evidence. Do not delete the view or the sheet until the privacy and legal teams have captured what was shared. Assess scope: 12,000 rows exported daily, but determine how many days the export ran and whether the agency received cumulative files; the 4,100 matched rows are a lower bound on re-identification, not the total exposed. Identify the data subjects and data elements: email hash, and whatever else the join selected from the orders table. Because the hash is unsalted SHA-256 of lowercased email, treat it as pseudonymous, not anonymous; the agency's match proves it is re-identifiable. Determine jurisdiction and contractual notification duties, and whether the agency is a processor or a controller. Produce a timeline with first export, discovery, containment, and notification decision. The assessment should drive notification, not the other way around.
2. Why did policy tags fail, and what control design would prevent recurrence?
Read the worked answer
Policy tags are enforced on the tagged columns, but the analyst's query created a new derived column and a new object that inherited no tag. The materialized view was untagged, and enforcement followed the object, not the lineage. The fix is layered. First, require classification at creation: scheduled queries and views must declare a data class, and a derived column inherits the highest class of its inputs unless an approved de-identification function is used. Second, enforce at the project boundary with policy that blocks export of tagged or derived-sensitive data to Sheets or external projects, rather than relying on column tags alone. Third, extend lineage to ad hoc scheduled queries so a sensitive source is visible downstream. Fourth, replace unsalted hashing with a keyed pseudonymization function managed in a KMS, and prohibit raw email from entering the marketing project. Fifth, add a periodic scan that samples exports for re-identification risk. Tags are necessary but not sufficient; the control must follow data derivation and egress.
3. What should the incident report say about the effectiveness of the existing controls, and what metric would you track?
Read the worked answer
The report should state plainly that column-level policy tags worked as configured and still failed the policy objective, because the control was object-scoped and the data was derived into an untagged object. Avoid framing it as analyst error; the analyst used the tools available, and the platform allowed a tagged source to produce an untagged export. Note the lineage gap: dbt models were covered, scheduled queries were not, so the sensitive path was invisible. Track a coverage metric, not just an incident count: percentage of scheduled queries and views with a declared data class, percentage of sensitive sources with downstream lineage to egress, and number of exports containing re-identifiable data. Add a time-to-containment metric for privacy incidents. Report the 4,100 matched rows as confirmed re-identification and the 12,000 daily rows as the exposure envelope, with the exact number of days. Include the remedial actions, owners, and dates, and state the residual risk until keyed pseudonymization and egress policy are live.