PII Tags, Masking Policies, and the Governance Audit

Level 10: Distributed Compute & Data Operationshard28 minPII taggingcolumn masking policiesrole-based unmaskingcompliance gap analysispattern-based discoveryEXISTS and anti-joins

Audit a catalog for untagged and unmasked PII, then produce the exposure report a governance review asks for, unaided.

Governance is three artifacts

"How do you handle PII in a pipeline" is a governance question with a boringly concrete answer, and the answer is three tables in the catalog. If you can name them and query them, you can hold the whole conversation.

Table

Tag, policy, evidence. Each artifact answers one audit question, and each missing one is a different kind of gap.
artifact	what it declares	the gap when it is missing	audit question
pii_tag on a column	this column holds personal data	nobody knows it is PII, so nothing protects it	which columns look like PII but carry no tag?
masking policy	how a tagged column is masked, and for whom	the tag is a sticky note, values return raw	which tagged columns have no policy?
read log	who read which column, and when	no evidence, so no audit is possible	who read PII a policy did not authorize?

Tag, policy, evidence. Each artifact answers one audit question, and each missing one is a different kind of gap.

The audit questions, in order

Run them in this order, because each one assumes the previous one is clean.

Discovery: what is PII and untagged? You cannot protect what nobody labelled. The cheap version is pattern matching on column names (%email%, %phone%, %ssn%, %name%), which catches the obvious cases and misses col_17. The managed version samples the data itself and classifies it, which is what Amazon Macie does. Either way the output is a list of candidates a human confirms, and every untagged column is invisible to every query in the rest of this lesson. That is the point: the discovery pass is first because the audits downstream all start from the tag.
Coverage: which tagged columns have no masking policy? A tag is metadata, not protection. The policy is what actually rewrites the value on read, and it names one role that sees raw values (a fraud analyst who genuinely needs the shipping address, say). Tagged with no policy is the compliance gap, and it is an anti-join.
Exposure: who read what they should not have? The read log makes the first two auditable. A read is authorized when a masking policy exists for that column and its unmasked_role matches the reader's role. Everything else is exposure: a tagged column with no policy at all (nothing masked it, so the values came back raw), or a tagged column whose policy names a different role, which means the query went at the base column and around the governed view.

Masking is a read-time rewrite

Worth being precise, because candidates blur it. A masking policy does not change the stored data. It rewrites the value as it is returned, per role: a***@example.com for most roles, the raw address for role/fraud_ops. That is why the same query, run by two people, correctly returns two different answers, and why an audit has to know the reader's role and not only the query text.

Common mistake: auditing only the tagged columns and calling the platform clean. The tagged set is the set somebody already thought about. The dangerous column is the one in a table nobody governs, holding an email address under a name no policy will ever match. Discovery runs first and runs again on a schedule, because every new table arrives untagged.

Interview nuance: the answer that lands on "how do you handle PII" is the three artifacts plus the order of the audit: tag it, mask it per role, and prove it with the read log. Adding "and the discovery pass runs on a schedule because new columns arrive untagged" is the sentence that reads as someone who has been through a real review rather than someone who has read the docs.

On a real platform this differs. Here the three artifacts are three SQLite tables. On AWS, Lake Formation holds the tags and the column, row, and cell grants, Macie does the discovery, and CloudTrail plus Athena query history is the read log. Snowflake spells it CREATE MASKING POLICY bound to a column with CURRENT_ROLE() inside it, and ACCESS_HISTORY is the log. Microsoft Fabric calls it dynamic data masking with column-level security. Three artifacts, three vendors, one audit.

Sample data for this example

CREATE TABLE column_catalog (
  table_name  TEXT,
  column_name TEXT,
  data_type   TEXT,
  pii_tag     TEXT     -- the governance tag on the column, NULL when nobody has tagged it
);
INSERT INTO column_catalog (table_name, column_name, data_type, pii_tag) VALUES
  ('customers',       'customer_id',     'INTEGER', NULL),
  ('customers',       'full_name',       'TEXT',    'name'),
  ('customers',       'email_address',   'TEXT',    'email'),
  ('customers',       'phone_number',    'TEXT',    'phone'),
  ('customers',       'signup_date',     'TEXT',    NULL),
  ('customers',       'country',         'TEXT',    NULL),
  ('orders',          'order_id',        'INTEGER', NULL),
  ('orders',          'customer_id',     'INTEGER', NULL),
  ('orders',          'amount_usd',      'REAL',    NULL),
  ('orders',          'ship_address',    'TEXT',    'address'),
  ('orders',          'order_ts',        'TEXT',    NULL),
  ('payments',        'payment_id',      'INTEGER', NULL),
  ('payments',        'customer_id',     'INTEGER', NULL),
  ('payments',        'card_last4',      'TEXT',    'card'),
  ('payments',        'billing_zip',     'TEXT',    'address'),
  ('payments',        'paid_at',         'TEXT',    NULL),
  ('support_tickets', 'ticket_id',       'INTEGER', NULL),
  ('support_tickets', 'requester_email', 'TEXT',    NULL),
  ('support_tickets', 'contact_phone',   'TEXT',    NULL),
  ('support_tickets', 'body_text',       'TEXT',    NULL),
  ('support_tickets', 'created_ts',      'TEXT',    NULL),
  ('marketing_leads', 'lead_id',         'INTEGER', NULL),
  ('marketing_leads', 'work_email',      'TEXT',    NULL),
  ('marketing_leads', 'lead_name',       'TEXT',    'name'),
  ('marketing_leads', 'source',          'TEXT',    NULL);

Worked example (SQL)

-- Tag coverage per table. A table with zero tagged columns is either clean or unexamined,
-- and the catalog cannot tell you which.
SELECT table_name,
       COUNT(*) AS columns_total,
       SUM(CASE WHEN pii_tag IS NOT NULL THEN 1 ELSE 0 END) AS tagged_pii
FROM column_catalog
GROUP BY table_name
ORDER BY tagged_pii DESC, table_name;

Apply

Your turn

The task this lesson builds to.

Write a query that returns every PII-tagged column that no masking policy covers, as (table_name, column_name), ordered by table then column, over column_catalog(table_name, column_name, data_type, pii_tag) and masking_policies(table_name, column_name, policy_name, unmasked_role). This is the compliance gap list.

A column is PII when pii_tag is not NULL, and a policy covers a column when masking_policies holds a row for the same table and column.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a query that returns every PII read that no masking policy authorized, as (user_name, role, table_name, column_name, query_time), oldest read first, over query_log, column_catalog, and masking_policies.

query_log records reads against the base columns. A read is authorized only when a masking policy exists for that exact table and column and its unmasked_role equals the reader's role. Everything else is exposure: a tagged column with no policy at all, or a tagged column whose policy names a different role. Columns carrying no pii_tag are out of scope for this report.

1 automated check is waiting in the workspace.