Data Governance, Contracts, and Classification Questions

Apply the row-level filter and the column masking at the LOWEST layer an unprivileged query can reach, meaning a masking view plus a row-level-security predicate baked into the view definition itself, and re-apply the same predicate inside any CTE that touches the base table directly. An intermediate CTE that selects from the unmasked base table is a leak, even if the final SELECT looks clean, because anyone who can see the query plan or an intermediate materialization sees the raw data.

Pattern

1. Create a masking view over the sensitive table that both filters rows (tenant isolation) and redacts or tokenizes PII columns.
2. Grant analysts access to the VIEW, never to the base table.
3. Any CTE built on top of the view inherits the masking automatically, because it's reading through the view, not around it.

Worked example (executed)

CREATE VIEW restricted_profiles AS
SELECT user_id, tenant_id,
       'REDACTED' AS email,   -- in a real warehouse this is a role-conditional masking policy/UDF, not a literal
       name
FROM profiles
WHERE tenant_id = 100;        -- row-level security predicate, tied to the session's tenant context

WITH tenant_events AS (
  SELECT event_id, user_id, tenant_id, event_type
  FROM events
  WHERE tenant_id = 100       -- same predicate re-applied here, not inherited automatically from the view
),
joined AS (
  SELECT te.event_id, te.event_type, rp.name, rp.email
  FROM tenant_events te
  JOIN restricted_profiles rp ON te.user_id = rp.user_id AND te.tenant_id = rp.tenant_id
)
SELECT * FROM joined;

Executed against a fixture with two tenants (tenant 100: Alice, tenant 200: Bob), this returns only tenant 100's two events, with email showing REDACTED in every row, including inside the joined CTE, not just the final output.

The adversarial case this pattern prevents

Joining the BASE tables directly, with no view and no tenant predicate, leaks both tenants' raw emails: running SELECT e.event_id, p.email, p.tenant_id FROM events e JOIN profiles p ON e.user_id = p.user_id against the identical fixture returns both Alice's and Bob's real email addresses and tenant IDs in the same result set. That's the exact failure mode this pattern exists to prevent: it's not enough that the FINAL output looks fine in a demo, the query has to be structurally incapable of reaching the raw base table without the predicate and masking applied.

Testing tenant isolation

Write a test that runs the same query as a different tenant's session and asserts zero rows from another tenant ever appear, and a second test that runs it against the RAW base tables (bypassing the view) to confirm that path is actually blocked by grants, not just avoided by convention.

Trade-offs and pitfalls

Views are simple but every consumer must be prevented, at the grant level, from querying the base table directly, or the whole pattern is cosmetic. In a real warehouse (Snowflake, BigQuery) you'd implement the masking with a native row-access policy or masking policy rather than a literal string, which also gives you a single place to audit which roles bypass the mask. Performance is the other real cost: a masking function evaluated per-row on a large fact table can slow down interactive BI queries, which is a legitimate reason some teams pre-materialize a masked copy for heavy read workloads instead of masking at query time.

Detect PII (personally identifiable information) as a mandatory, automated stage every pipeline passes through, not an opt-in step someone has to remember to add, by combining schema tagging, pattern-based rules, and machine learning (ML) classifiers so no single blind spot in one method leaves a gap, and pair detection with a masking strategy that defaults to safe while giving legitimate exceptions a documented, reviewed path.

Detection approaches, combined

No single technique catches everything on its own, so the three are layered rather than treated as alternatives:

1. Schema tagging. Fields already known to be PII (declared at table or event-schema creation, email, ssn, date_of_birth) are tagged in the schema registry at the point of definition. This is the cheapest and most reliable method, but it only catches PII that someone remembered to declare; it says nothing about a new field added later or a free-text field that happens to contain PII incidentally.
2. Regex and pattern matching. Deterministic patterns (an email format, a national ID number's digit structure, a credit card number's format) run against both structured columns and free-text fields, catching PII that landed somewhere undeclared, for example a support-ticket comment field containing a customer's phone number typed in by an agent.
3. ML-based classifiers. Named-entity recognition or a trained classifier scans less-structured content (chat logs, free-text notes) for PII patterns too varied for a fixed regex to catch reliably, a person's name embedded in a sentence, an address written in an unstructured format. This is the most expensive method computationally and the one most prone to false positives, so it's best applied selectively rather than on every record in a high-volume stream.

Running all three, with schema tagging as the free first pass, regex as a cheap second pass, and ML classification reserved for content the first two can't confidently clear, keeps overall detection cost proportional to how much genuinely needs the expensive method.

Covering both raw lake data and curated warehouse tables

The same three-method detection runs at two different points, not just once:

• At the raw lake ingestion point, detection tags incoming data as it lands, before anyone builds a curated table on top of it, so a downstream table doesn't inherit an undetected PII field from an upstream source that was never scanned.
• At the curated warehouse layer, detection re-runs against derived and joined tables specifically because a join or transformation can combine otherwise-innocuous columns into a newly PII-bearing result (a table joining a customer ID with a separately-innocuous demographic table becomes identifying once joined), which a source-only scan would miss entirely.

Covering both streaming and batch pipelines

• Streaming (for example Kafka, Flink, or Kinesis). Schema tagging and regex matching run inline, on the hot path, since they're cheap enough not to meaningfully add latency; ML classification, being more expensive, runs asynchronously on a side path (writing flagged content to a review queue) rather than blocking the main stream, so a slow classifier doesn't become a throughput bottleneck for the whole pipeline.
• Batch (for example Spark or a scheduled warehouse job). All three methods can run inline as part of the batch job itself, since batch jobs already tolerate longer per-record processing time; this is also where full re-scans of existing tables (not just new data) happen periodically, catching PII in data that landed before detection was in place.

flowchart TB
    subgraph Streaming
        SIN[Streaming ingestion] --> SSCHEMA[Schema tag check, inline]
        SSCHEMA --> SREGEX[Regex check, inline]
        SREGEX --> SML[ML classifier, async side path]
    end
    subgraph Batch
        BIN[Batch ingestion / scheduled scan] --> BALL[Schema + regex + ML, inline]
    end
    SSCHEMA --> POLICY[Policy engine]
    SREGEX --> POLICY
    SML --> POLICY
    BALL --> POLICY
    POLICY --> MASK[Masking / redaction applied]
    POLICY --> LAKE[(Raw lake, tagged)]
    POLICY --> WH[(Curated warehouse, tagged)]

Masking or redaction strategy once something is found

The action taken depends on the field's classification tier and its downstream use, not a single blanket rule:

• Confirmed structured PII (a tagged or high-confidence regex match on a known field type): masked by default in any view accessible outside the field's originating team, using the same tiered masking approach used elsewhere in the platform (partial mask, full redaction, or tokenization depending on sensitivity).
• PII detected in free text by the ML classifier: redacted (the specific span replaced with a placeholder like [NAME] or [PHONE]) rather than the whole record being dropped, preserving the non-sensitive content around it for analysis.
• Low-confidence ML detections: routed to a review queue rather than auto-masked or auto-ignored, since ML detections carry meaningfully more uncertainty than a schema tag or a regex match.

Handling false positives and legitimate exceptions

• False positives (a regex matching a numeric ID that happens to look like a phone number, or an ML classifier flagging a business name as a person's name): a lightweight review workflow lets a data owner mark a flagged field as a confirmed false positive, which suppresses future flags for that specific field or pattern rather than requiring a manual override on every occurrence.
• Legitimate exceptions (a field that is technically PII-shaped but has an approved, narrow business use, for example a fraud-detection model that genuinely needs raw device identifiers): handled through the same exception-request process used for other restricted-data access elsewhere in governance, an explicit, time-boxed, logged approval, not a permanent bypass flag quietly set once and forgotten.
• Feedback loop. Confirmed false positives and approved exceptions feed back into tuning the regex patterns and retraining or recalibrating the ML classifier's confidence thresholds over time, so the false-positive rate should trend down as the system accumulates review history, rather than staying static.

Trade-offs & pitfalls

The most common mistake is running detection only at ingestion and treating a table as permanently cleared once scanned, which misses both new PII introduced by later joins/transformations and PII in data that existed before detection was deployed. Periodic re-scanning of curated tables, not just one-time ingestion-point scanning, is what catches both gaps, at the cost of ongoing compute spent re-checking data that, most of the time, hasn't changed in its PII profile since the last scan.

Classify CRM columns by what they reveal, grant access per role against that classification rather than per table, and mask or tokenize on export by default so leaving the platform is never the path of least resistance for raw PII (personally identifiable information).

Classification scheme for the CRM columns

Using contacts and accounts tables as the concrete example:

Column-level access policy per role

The row-level filter for sales reps ("own assigned accounts only") is enforced the same way as the column policy: as a predicate attached to the underlying table or a secure view, not as a client-side filter the reporting tool could be misconfigured to skip.

Masking or tokenization for exports

Any export (a CSV download, a scheduled report to an external tool, an API pull) goes through a masking layer by default:

• Confidential columns default to masked on export. Email becomes domain-only (***@acme.com), phone becomes last-4 visible. A sales rep exporting their own account list for a call sheet needs raw values, so an explicit "export unmasked" action is available but is logged with the requester, timestamp, and row count, distinct from the default masked export.
• Restricted columns default to tokenized, not just masked, on export. A tokenized value (a random surrogate replacing the real value, with the mapping held in a separate, tightly access-controlled vault) preserves the ability to join or de-duplicate records by the token without ever putting the raw restricted value into an exported file. Reversing a token back to the real value requires a separate, audited request against the vault, not just re-running the export.
• Bulk exports get an extra gate. A single row export (one contact's card) and a 50,000-row bulk export carry very different risk, so exports above a configurable row threshold require a second approval regardless of role, since bulk exfiltration is the actual threat this control exists to catch.

Trade-off between tightening this and keeping sales and analytics productive

Tightening column access and masking exports protects the company from a breach or a compliance violation, but every added control point (an approval step, a masked-by-default value, a token that needs unmasking) adds latency to a sales rep who needs a phone number to make a call right now, or an analyst who needs to validate an anomaly against a real record.

The trade-off resolves cleanly along one axis: default to restrictive, make the unrestricted path fast for the common case. A sales rep's own assigned accounts should never feel gated (row-level access plus unmasked confidential fields, no extra click), because that is their actual job. What gets gated is the exceptional case: bulk export, cross-territory access, or restricted-tier fields. If the common case is fast and only the exceptional case has friction, sales and analytics stay productive while the actual risk surface (bulk exfiltration, restricted-field misuse) is where the friction concentrates. Getting this backwards, adding friction to the common case while leaving bulk export easy, is the pattern that both angers users and fails to reduce real risk.

Produce both forms from the same raw PII (personally identifiable information) at the point of ingestion rather than deriving one from the other later, gate re-identification behind a key-management process that requires an approved, logged request, and let each form's retention window be set independently since a debugging need and a general-analytics need don't expire on the same schedule.

Producing both forms

At ingestion, a raw PII field (say, a customer email) feeds two parallel transformations:

• Reversible pseudonymization: encrypt the value with a per-purpose data encryption key (DEK), or replace it with a tokenized surrogate whose mapping lives in a secure vault. Either way, the original value is recoverable, but only through a controlled unwrap or lookup operation, not by inspecting the pseudonymized value itself.
• Irreversible anonymization: apply a one-way transformation, typically a salted cryptographic hash, or for values needing broader generalization, a k-anonymity-style bucketing. No process, however privileged, can recover the original value from this form; it exists specifically so it never needs to be protected as strictly as the reversible form.

Producing both at ingestion, from the same source, means the general-analytics path (which should use the anonymized form) and the ML training path (which may need the pseudonymized form; see below) never share a table that mixes reversible and irreversible representations of the same field, which keeps the access-control boundary between them unambiguous.

Applying this where PII feeds an ML training pipeline

An ML (machine learning) model often needs some individual-level granularity to learn useful patterns, more than an irreversibly anonymized, heavily bucketed field would preserve, but rarely needs the literal raw PII value. The pseudonymized (reversible) form is usually the right input for training: the model trains on a stable per-customer token or encrypted value that preserves individual-level distinctness (so the model can learn per-customer patterns) without the training pipeline ever handling raw PII directly. If a downstream serving or analysis step genuinely doesn't need individual-level distinctness at all (a model diagnostic report aggregated by cohort, for example), that step should consume the anonymized form instead, not the pseudonymized one, since there's no reason to expose even a reversible representation where an irreversible one would do.

Managing keys so re-identification is possible only through an approved, logged process

• Separate key custody from data custody. The vault holding the token-to-original mapping (or the DEKs used for reversible encryption) is a distinct system from the data store holding the pseudonymized values, with its own, narrower access-control list; having read access to the pseudonymized dataset should never imply the ability to unwrap it.
• Re-identification requires an explicit request, not standing access. Even someone with legitimate occasional need for re-identification (a fraud investigator, a data engineer debugging a pipeline issue) holds no permanent key access; each use is a discrete, logged request against the vault, approved by the data owner or a compliance reviewer, scoped to the specific records needed.
• Every unwrap operation is logged immutably, recording who requested it, which records were unwrapped, and the stated reason, so a re-identification event is always attributable and auditable after the fact, not just prevented in the moment.
• Key rotation is independent of re-identification approval. DEKs or token-vault keys are rotated on their own schedule (for example annually, or immediately upon a suspected compromise) using envelope encryption (a master key wraps the per-purpose DEKs), so rotating keys doesn't require re-processing the entire dataset, only re-wrapping the DEKs themselves.

How retention windows interact with which form is kept where

Retention should be set per form, not as one blanket rule for "this customer's data":

• Raw PII: retained only as long as strictly necessary for the immediate processing purpose, then deleted; it should not persist once both derived forms exist and the immediate purpose is served.
• Reversible (pseudonymized) form, plus its key mapping: retained as long as there's a legitimate ongoing need for individual-level distinctness (active model training, an open investigation window), with the key mapping's deletion being what actually achieves practical erasure. Deleting the vault's mapping for a given token converts that token into effectively irreversible data going forward, without needing to rewrite every downstream table that stores the token; this is the mechanism that makes honoring a right-to-erasure request tractable even when the token has propagated into many derived datasets.
• Irreversible (anonymized) form: since it carries no path back to the individual, it can be retained longer, often indefinitely, for aggregate trend analysis, without needing to track it against an individual retention clock at all.

flowchart TB
    RAW[Raw PII at ingestion] --> PSEUDO[Reversible pseudonymization]
    RAW --> ANON[Irreversible anonymization]
    RAW -->|deleted after immediate purpose| DEL[Raw deleted]
    PSEUDO --> VAULT[(Key / token vault, separate access control)]
    PSEUDO --> MLTRAIN[ML training pipeline]
    ANON --> GENANALYTICS[General analytics, aggregate reporting]
    VAULT -->|approved, logged unwrap request only| REID[Re-identification result]
    ERASE[Erasure request] -->|delete this customer's key/token mapping| VAULT

Trade-offs & pitfalls

The common mistake is deriving the anonymized form from the pseudonymized form instead of both from the raw value independently; if the anonymized form is just "the pseudonymized value, hashed again," then compromising the pseudonymization key chain can, depending on the transformation, also compromise the supposedly-irreversible form. Deriving both directly from the original raw value at ingestion, with the raw value then deleted, avoids that chained dependency and keeps the anonymized form's irreversibility guarantee independent of whether the pseudonymization vault is ever compromised.

The realistic options range from simple deterministic hashing (fast, but weak against a determined attacker with the same hashing scheme) to secure multi-party computation or a trusted intermediary (strong protection, but higher cost and latency), and the right choice depends heavily on whether the join is a one-time internal operation or a recurring join with an external, less-trusted party.

The options

One-time internal join between two warehouse tables

For a one-time join entirely inside your own infrastructure (two tables you already control, joined once for an internal analysis), deterministic hashing is usually sufficient and by far the fastest to implement: both tables live in the same trust boundary already, so the main residual risk is an internal analyst reversing the hash, which is mitigated by using a strong, secret, non-reused salt and restricting who can run the join query at all. Reaching for private set intersection (PSI) or an enclave here is usually overkill: those techniques exist to protect data from a party you don't fully trust, and in a one-time internal join, both sides of the join are already inside the same access-control boundary.

Live join an external, less-trusted partner needs to perform

This is a fundamentally different trust situation: the partner is a separate organization, the join may run repeatedly (not once), and the partner should learn nothing about your non-matching records, and ideally nothing about your raw identifiers at all, matching or not. Deterministic hashing is a poor fit here specifically because the partner could run a dictionary attack against your hashed values using their own guesses at the underlying identifiers, since hashing doesn't require your cooperation to attack once they have the hash. Private set intersection is the better-fitted tool for this case: it's designed exactly for two parties who don't trust each other to learn only the intersection (or an aggregate over it), without either party ever seeing the other's raw or hashed identifier set directly. A centrally-issued token from a mutually trusted third party is a workable alternative if such a trusted intermediary already exists in the relationship (a data clean room provider, for example), trading some of PSI's cryptographic guarantees for operational simplicity.

Trade-offs: performance versus re-identification protection

The general pattern across all four options is that stronger protection costs more in latency, engineering complexity, or both:

• Hashing is cheapest and fastest but its protection depends entirely on salt secrecy and identifier entropy, both of which degrade over time or under a sustained attack.
• Tokenization shifts the trust requirement onto the token-issuing party and its vault, which is a single well-defined thing to secure well, rather than the identifier itself.
• PSI removes the need to trust any single party with the raw data at all, at the cost of protocol overhead that makes it a better fit for periodic batch joins than a continuously-updating live feed.
• A secure enclave gets close to native performance for the join computation itself but pushes the trust question onto the enclave's attestation chain and the complexity of securely feeding data in and getting results out.

Trade-offs & pitfalls

The most common mistake is applying the internal-join answer (plain salted hashing) to the external-partner case because it's already built and it's fast; that reasoning ignores that the threat model changed, not just the join mechanics. The choice of technique should follow from who else can see the hashed or tokenized value and what they're motivated to do with it, not from which technique is already implemented elsewhere in the stack.