Data Governance, Contracts, and Classification Questions

Apply the same classification and controls consistently across all four surfaces (SQL, the BI tool, object storage, and notebooks) rather than securing the warehouse well and treating the other three as afterthoughts, since the surface most likely to leak PII (personally identifiable information) in practice is whichever one gets the least attention.

Policies and technical controls, per surface

Automation to catch accidental PII leakage

Policy alone doesn't catch the actual failure mode, which is usually someone unintentionally exporting or printing raw PII while doing legitimate work, not a deliberate breach attempt. Layer in automated detection:

1. Static scanning of notebook code and BI exports. A scheduled or pre-commit scan for the pattern of a raw PII column name (email, ssn) appearing in an unmasked query result or a notebook's saved output cell, flagging it for review rather than silently allowing it.
2. Data loss prevention (DLP) scanning on object storage writes. Any write to a broadly-readable storage prefix gets scanned for PII-shaped content (matching known patterns: emails, national ID formats, card numbers) even if it wasn't tagged as PII at the source; this catches the case where a PII column got joined into a new derived file nobody classified.
3. Anomalous-export alerting. A single analyst exporting a normal-sized daily report is expected behavior; the same analyst exporting a 500,000-row extract from a PII-containing table is not. Alert on volume and frequency outliers rather than trying to block every export outright, since most exports are legitimate.
4. Automatic quarantine, not automatic deletion. When a scan flags a likely leak, move the artifact (the notebook output, the storage object) into a restricted-access quarantine location and notify the owner, rather than deleting it outright; this preserves the evidence needed to confirm whether it was actually a leak, while immediately limiting exposure.

Letting authorized analysts do their real work

The controls above are only sustainable if the default, masked path is fast enough that nobody has an incentive to route around it:

• Governed views and masked notebook connections should cover the large majority of day-to-day analysis without needing an unmask request.
• For the genuine minority of cases needing raw PII (an investigation, a data quality debug session), provide a fast, logged, time-boxed unmask request rather than either blocking it entirely or leaving raw access permanently open.
• Keep the friction concentrated on the risky action (raw export, bulk pull), not on ordinary querying, so the controls don't train people to find workarounds.

Trade-offs & pitfalls

The most common failure in practice is securing the warehouse thoroughly and leaving object storage or notebooks as the gap, because they're viewed as "just infrastructure" rather than a PII surface in their own right. A raw data lake prefix that predates the classification effort, or a notebook server with a long-lived admin credential baked in from years ago, is often the actual leak path even when the warehouse-layer controls look solid on paper. Auditing all four surfaces with the same rigor, rather than assuming the newest or most-visible surface is the only one that matters, is the discipline that prevents this.

Build the sandbox so raw PII (personally identifiable information) never leaves it: analysts get compute next to the data instead of a copy of the data, every result crossing the sandbox boundary is masked or aggregated, and elevated access for a genuine one-off need is granted as a scoped, time-boxed exception rather than a permanent hole in the wall.

What to build in

• Ephemeral runtimes. Each analyst session spins up a fresh, isolated compute environment (a container or short-lived virtual machine) with no persistent local disk beyond an encrypted scratch volume that's wiped when the session ends. This means there's never a laptop or a long-lived server with a cached copy of sensitive data sitting around after the work is done.
• Restricted network egress. The sandbox's network is locked down by default: no outbound internet access, and only explicitly approved destinations (an internal package repository, the data warehouse itself) are reachable. This is what actually prevents raw PII from being copied out via an upload, an email, or a paste into an external service, even if every other control were somehow bypassed.
• Result-level masking. The sandbox's query layer applies the same column masking and row-level filtering used everywhere else in the platform, so what an analyst sees inside their notebook is already governed; a query result is masked or aggregated before it's rendered, not after.
• Audit logging. Every query executed, every file written to the scratch volume, and every session start/stop is logged centrally, tied to the analyst's identity, so the sandbox is fully accountable even though it's designed to make deliberate exfiltration structurally hard.

Handling a legitimate one-off request for elevated access

Some analysis genuinely needs raw, unmasked values, for example validating a specific customer's data quality issue, or an investigation that requires seeing an actual identifier rather than a masked one. The process:

1. Request with justification. The analyst submits a request naming the specific dataset, the specific columns, and the business reason, not a blanket "give me raw access."
2. Approval from the data owner, not from IT. The person who owns the dataset (per the classification and ownership model) approves or denies, since they're best positioned to judge whether the stated reason justifies the exposure.
3. Scoped, time-boxed grant. Approval issues a credential valid for a specific dataset, a specific time window (for example 4 hours), and inside the same sandboxed, egress-restricted environment, not a general unmasking of the analyst's account.
4. Automatic expiry, no manual revocation needed. The elevated grant expires on its own; nobody has to remember to revoke it, which is the step that gets forgotten in ad-hoc processes.
5. The elevated session is logged more heavily than a normal session. Every query during the elevated window is retained longer and flagged for a lightweight post-hoc review, so the exception is auditable after the fact even though it wasn't blocked in the moment.

flowchart LR
    A[Analyst requests elevated access] --> B{Data owner approves?}
    B -- No --> C[Denied, use masked sandbox as normal]
    B -- Yes --> D[Time-boxed scoped credential issued]
    D --> E[Elevated session inside same egress-restricted sandbox]
    E --> F[Credential auto-expires]
    F --> G[Post-hoc review of elevated session's query log]

Trade-offs & pitfalls

The temptation is to make the elevated-access process heavyweight enough to discourage all but the most urgent requests, which backfires: if the approved path is slow and painful, analysts with a genuine one-off need start finding workarounds (screenshotting a colleague's screen, asking someone with standing access to run the query for them) that leave no audit trail at all. A fast, logged, narrowly-scoped exception path is safer in practice than a slow one, because it keeps the legitimate need inside the system that's actually being monitored.

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.

The review process should be a small set of required, right-sized artifacts checked by the people actually accountable for the risk they cover, with a fast default turnaround and a fast-track that's explicitly harder to use than the default path, not easier, so it doesn't become the path everyone takes to skip review.

Gates and required artifacts

1. A data-flow diagram: what data enters the project, where it's stored, what transforms touch it, and where it exits (a third-party API, a downstream team, an external report). This is the artifact that makes every other review possible, because you can't assess privacy or security risk without knowing where the data actually goes.
2. A privacy impact assessment (PIA): does the project touch personal or sensitive data, what's the legal basis for processing it, what's the retention period, and does it introduce a new use of existing data that wasn't originally consented to. This gate exists because privacy risk is invisible in code review; the PIA forces someone to state it explicitly.
3. A lightweight threat model: a short, structured pass (for example, using a simplified STRIDE-style checklist: spoofing, tampering, repudiation, information disclosure, denial of service, elevation of privilege) over the data-flow diagram to identify the two or three most plausible ways this project's data could be exposed, altered, or abused, not an exhaustive security audit.

Who reviews and turnaround SLA

• Data-flow diagram: reviewed by the data engineering or platform team, because they're accountable for how the diagram interacts with existing infrastructure. Turnaround: same-week, since this gate is mostly mechanical (does the diagram match reality) and shouldn't be a bottleneck.
• Privacy impact assessment: reviewed by privacy or legal (or a designated privacy champion on a smaller team), with a stated SLA (service-level agreement) of a few business days for a standard project, longer only if the PIA surfaces a genuinely novel use of data that needs deeper legal analysis.
• Threat model: reviewed by security or a security-trained engineer, same turnaround window as the PIA, run in parallel rather than sequentially so the two reviews don't stack into a multi-week wait.

Fast-track for genuinely urgent fixes, without it becoming the default

The fast-track's job is to unblock a real emergency (a live incident fix, a legally mandated deletion) without weakening the gate for everything else, and the way to keep it from becoming the default escape hatch is to make it MORE visible and MORE accountable than the standard path, not less:

• Fast-track requires a named sponsor (an engineering or security lead, not the requester) who personally attests to the urgency and is on record for that decision.
• Fast-track ships with a mandatory retroactive review, completed within a short fixed window (for example, one week) after the fact, covering exactly the same artifacts the standard path requires up front. The project isn't exempt from the gates, it's deferred, and the deferral itself gets logged.
• Fast-track usage is visible and tracked (a running count reviewed monthly by whoever owns the governance program), so a pattern of "everything is an emergency" becomes visible to leadership quickly rather than being an invisible, team-by-team habit.

Trade-offs and pitfalls

The common wrong turn is making the fast-track easier to use than the standard path (a single Slack approval versus a written review), which guarantees it becomes the default; teams optimize for the path of least resistance, not the path that was intended for their situation. The other pitfall is a threat model that tries to be exhaustive for every project regardless of risk; a lightweight checklist applied consistently catches more real issues over time than a heavyweight process people route around because it's too slow to use honestly.

Before running anything, establish purpose and minimum necessary scope; the questions and safety checks exist to answer one thing, whether this request can be satisfied with less exposure than "raw production access to emails and account balances," and the answer is almost always yes.

Questions to ask before deciding

• What business decision or problem actually needs this data? A request framed as "I need customer emails and balances" is usually really "I need to understand churn by balance tier" or similar; the underlying need often doesn't require raw PII (personally identifiable information) at all.
• Would aggregated or masked data answer the question? If the stakeholder needs a trend, a distribution, or a count, an aggregate query or a masked extract satisfies it without ever exposing a raw email or an individual balance.
• Does the requester have an approved role for this data, and is there a standing access-request process they skipped? If a governed workflow already exists for this kind of request, a one-off ad-hoc query is the wrong path even if the requester is well-intentioned.
• How long is the output retained, and where does it go? A one-time analysis that lives in a shared spreadsheet indefinitely is a very different risk than a query result viewed once and discarded.

Safety checks before granting anything

• Require the request in writing (a ticket, not a verbal ask), naming the specific columns, the specific rows or filters, and the stated purpose, so there is a record.
• Default to the least-identifying version that still answers the question: masked emails, rounded or bucketed balances, aggregated counts, in that order of preference.
• Route the actual query execution through an audited path (a service account or reviewed script) rather than handing the requester direct table access.
• Time-box any access granted so it doesn't quietly become standing access after the immediate need passes.

When to refuse or redirect

Refuse or redirect to an approved workflow when any of the following hold:

• The requester cannot articulate a specific business decision the raw data would inform (a vague "just want to look around" is a refusal, not a judgment call).
• The same question can be answered with an aggregate or masked view; in that case granting raw access is unnecessary exposure, not a convenience.
• The output would land somewhere uncontrolled, an unmanaged spreadsheet, a personal drive, a chat message, regardless of how legitimate the underlying need is.
• The requester's role has no documented business need for this dataset category at all, in which case the right move is redirecting them to whoever does own that access decision (a data steward or the dataset owner), not personally deciding to grant it.

Documenting the decision so it's defensible later

Record, at the time of the decision, not reconstructed afterward: who requested it, what was requested, what was actually granted (and how it differed from the raw request, if it did), the stated business justification, who approved it, and the retention/deletion plan for any output. This record should live in the same system used for other access decisions (a ticketing system tied to the governance process), not in an email thread that's hard to find during an audit.

Worked example

A marketing stakeholder asks for "a list of customer emails and their account balances" to plan a win-back campaign for high-balance accounts. Instead of granting the raw request: ask what threshold defines "high-balance" (say, above $5,000), then provide an aggregated count of accounts above that threshold segmented by region, with a separate, governed campaign-send workflow (one the marketing team already uses for outreach) handling the actual emails, rather than the stakeholder receiving a raw export of addresses and balances they'd have to handle themselves. The business need (identify and reach a segment) is fully met without a raw PII export ever existing.

Trade-offs & pitfalls

The main pitfall is treating "no" as the only safe answer and creating a reputation for blocking legitimate work; that pushes requesters toward informal workarounds that leave no audit trail. The safer default in practice is redirect-and-satisfy: find the narrower path that meets the actual need, rather than a flat refusal, so people keep bringing requests through the front door.