PII detection and masking across 14 production pipelines

The problem

Personal data had spread through the warehouse the way it always does. Email addresses, names and addresses arrived in source tables, got copied into marts, joined into reporting tables and exported to tools, and none of it was tagged. A subject access request under UK GDPR meant a manual hunt through the lineage, and the exposure grew with every new model.

What I decided, and why

• Detect, then tag, then mask. Find PII by column-name and value patterns and by following lineage from known sources, record the result as metadata on the dbt model, and apply masking at the model layer so every downstream table inherits it.
• Make it a test, not a review. A dbt test fails any deployment that introduces a column that looks like PII without a tag. The weekly manual check that used to catch some of this became unnecessary, and the problem cannot quietly return.
• Tell the owner, not the channel. Unified error logging across the 14 pipelines, with alerts grouped and routed in Slack to whoever can fix them, rather than one noisy feed everyone mutes.

How it was built

The detection pass ran over the warehouse's column names and sampled values with pattern rules, then walked dbt's lineage graph from the tables already known to hold personal data. Matches were written back as tags in the dbt project, and masking was applied in the models carrying those tags, so no reporting query had to change. The same tags drive the test that guards new deployments.

What it changed

• 1,134 columns across 373 tables tagged and masked.
• A subject access request became a query against the tags instead of a project.
• About six hours a week of manual review gone.
• Pipeline failures now reach the person who owns the pipeline, with context, instead of a shared channel.