Data Platform Security and Governance

Data Engineering & Modern Data Platforms · Lesson 57 · Modern Data Platforms and Cloud

Lesson Overview

The completion mart is ready to publish on whichever cloud won the last three lessons, and the job that builds it still runs as an account administrator. An analyst granted the mart "so the board works" can also list the raw progress prefix. Athena, serverless SQL, or a BigQuery results table keeps a copy of yesterday's query, including a buyer email someone selected "just this once." The pipeline is green. The boundary is not.

Lessons 54 through 56 assigned owners to workloads. This lesson assigns identities, keys, network paths, and column rules to those workloads. It is the engineering control sheet. It is not the privacy program. Data Governance, Privacy and Compliance takes this sheet and adds retention schedules, consent, access reviews, and the evidence a regulator asks for. That lesson cannot invent the column list after the files already landed. Data Lineage and Metadata Management already defined datasets, jobs, and runs. This lesson requires the catalog names to match, and it attaches classification and the pipeline identity to those names. Contracts between producers and consumers belong later, with the contract lesson in Module 6.

TechLearningPro's sensitive columns are ordinary and enough to design for. email, full_name, and phone exist for support. learner_id is the key the mart is allowed to keep, and it is still an identifier. Payment instruments are prohibited in the lake, a rule from the first lessons of the course. Northline adds seller_id as a boundary between customers, which is row-level security rather than a hidden column.

The same control questions apply on AWS, Azure, and Google Cloud. The product names differ. A team that answers them for one cloud and calls the other two "the same, later" will discover the bypass on the engine it did not test.

Learning Objectives

By the end of this lesson you will be able to:

1. Separate the cloud provider's responsibility for the substrate from your responsibility for identity, grants, keys, and what pipelines write.
2. Give every pipeline a workload identity, and keep human privileges off that identity.
3. Compare least privilege on IAM, on Azure RBAC plus ADLS ACLs, and on Google Cloud IAM, including the grant that silently overrides a finer one.
4. Explain envelope encryption with a customer-managed key, and what rotation changes versus what key destruction does to existing files.
5. Place private connectivity and, where the cloud provides it, a service perimeter, so a stolen credential is not a complete copy plan.
6. Classify TechLearningPro columns into prohibited, restricted, and mart-safe, and enforce that list in the job rather than in a slide.
7. Say why a bare hash of an email is a weak stand-in for a secret, and where a token or a keyed pseudonym has to live.
8. Enforce row and column controls on the table the user can actually query, and test the bypass of a view that filters while the base table remains granted.
9. Scope audit logs to the datasets where a read is an investigation, and record the prefixes you chose not to fully audit.
10. Hand Module 6 a control sheet: identities, keys, paths, classifications, masking point, audit scope, and catalog names aligned with lesson 22.

Prerequisites

• Lessons 54, 55, and 56. The services in those lessons are the things being granted. This lesson does not pick a new engine.
• Lesson 22 for dataset names, owners, and OpenLineage jobs and runs. Classification hangs on those names.
• Data Quality Engineering for the publish gate. A security failure in the job is a failed publish, not a warning comment.
• Lesson 46, because a slot on PostgreSQL is also a privilege on the primary. The replication role is an identity.
• Lesson 47, because a dead-letter topic can contain the prohibited column the main path rejected. Quarantine is a data store.
• The governance lesson in Module 6, so you know which decisions this lesson refuses to pretend are finished.

Detailed Core Concepts

Shared responsibility is a list of owners, not a slogan

The provider operates the physical estate, the hypervisor, and the managed service's patching of its own control plane. You choose who can call the service, which key wraps the data, which network path is legal, which columns are written, and how long a credential lives. A managed warehouse does not review your SELECT * into a world-readable results bucket. A managed Spark job does not notice that its role is an organization administrator. Write the owners down: platform team for workload identities and keys, data owner for the column list, pipeline owner for the projection in code, support lead for the vault that still holds email.

Humans and jobs are different principals

A human identity is a person, usually through a workforce identity provider: IAM Identity Center, Entra ID, or Cloud Identity. A job identity is a role or service account the runtime assumes: a Glue job role, a Redshift COPY role, a Data Factory managed identity, a BigQuery scheduled-query service account, a Dataflow worker service account. The job identity's grants are the prefixes and tables that job reads and writes. They do not include the ability to change IAM, to read the contact vault, or to administer the account.

Long-lived keys in a repository, a connection string in a pipeline parameter, and a laptop profile used "temporarily" by the scheduler are the same defect. The scheduler should assume the job identity. Humans should assume a role that expires, with a second factor the workforce provider already enforces. Break-glass administrator access exists, is logged, and is not the identity in application_default_credentials on a build agent.

Service-to-service access uses the same idea. The orchestration identity may start the job. It does not need to read every object the job can read. Starting a job and reading the raw enrollment prefix are different actions. Combine them and the scheduler becomes a data reader.

Least privilege, and the coarse grant that wins

On AWS, identity policies and resource policies both apply. A bucket policy can deny a prefix even when a role allows a wider action. Lake Formation, when it is the catalog authority, adds table and column grants. Hybrid mode during a migration means both IAM and Lake Formation can be in the path, and an AccessDenied needs the denying layer, not a broader allow. S3 Block Public Access at the account is the default you should already have. Public means public.

On Azure, a data-plane role such as Storage Blob Data Reader on the account grants the data action. ACLs on a directory do not subtract it. The fine grant is an ACL for identities that lack a broad data role. The daily job should be in the second group. Shared keys bypass Entra ID entirely and should be disabled on the analytics account.

On Google Cloud, IAM allow policies on the bucket, the dataset, and the project are the grant. Uniform bucket-level access keeps object ACLs out. A project-level roles/bigquery.dataViewer is a coarse grant on every dataset in the project, including ones the mart role was not meant to see. Dataset-level grants, and authorized views when the analyst must not hold the base table, are the narrow path. A folder-level role inherited from a sandbox folder will surprise a production project that got moved under it.

The pattern that travels: name the principal, the action, and the resource. "Analysts can read the data platform" is not a grant. "The board group can select published.completion_attempt" is a grant. Write the first as a sentence in the design review and someone will implement it as a project owner role.

Encryption wraps keys, and rotation is not a rewrite

At rest, the services in this module encrypt. Provider-managed keys mean the provider holds the key and you cannot disable it. Customer-managed keys, in KMS, Key Vault, or Cloud KMS, mean you hold an IAM boundary on the key. The file or the block is encrypted with a data key. The data key is encrypted with the customer-managed key. That is envelope encryption. The pipeline needs permission to use the key, kms:Decrypt or the cloud's equivalent, not permission to administer keys.

Rotation creates a new key version. New writes use it. Existing objects remain readable while older versions stay enabled, because their data keys were wrapped with those versions. Rotation does not, by itself, rewrite every Parquet object. A project that promises "annual rotation, so old copies die" has confused rotation with destruction. Scheduling destruction of a key version, or disabling the key, can make the objects undecryptable. That is a data-loss and availability event. Treat destruction as a reviewed change with a backup you have restored under a key you still control. Do not rehearse destruction in production to see what happens.

In transit, clients use TLS to the service endpoint. Private connectivity decides whether that TLS session walks the public internet. PrivateLink and gateway or interface endpoints, Azure Private Endpoints, and Private Service Connect put the API on a private address. A firewall rule that still allows the public endpoint "for convenience" leaves the private path optional. The storage account, the bucket, and the warehouse should refuse the public path when the design says private.

A private endpoint is not an identity grant. A principal on the private network with a broad role can still read the vault. Network and identity are both required. Google Cloud VPC Service Controls add a perimeter: even a valid token, from outside the perimeter, can be refused when it tries to copy BigQuery or Storage data to a project outside the perimeter. AWS and Azure do not ship an identical product. Service control policies, bucket and endpoint policies, Azure firewalls, and resource locks are the controls you combine to get a similar "this token cannot exfiltrate to an unrelated account" property. Test the copy you fear, from a test identity, into a sink that should fail. The test is a denied copy of a synthetic file, performed by the platform team, recorded as evidence. It is not something you discover during an incident.

Columns have three outcomes

learner_id in the mart is a decision, not an accident. The board needs it only if a query is learner-grained. An aggregate by lesson_id for the public Monday slide does not need to be queryable at learner grain by every viewer of the slide. Two views can exist: an aggregate view for the wide audience, and a learner-grain view for a smaller role. Lesson 61 decides the legal basis for keeping the identifier. This lesson makes the wide audience technically unable to select the grain.

Prohibited columns fail the job before a write. Restricted columns are dropped on the way into the mart even if a producer added them to progress "for debugging." The dead-letter path is included. A quarantine file that stores the original payload because it failed parsing will store the card token the parser saw. Quarantine stores the error, the offset, and the keys you allow, or it is encrypted and granted only to the incident role, with a retention measured in days. An open prefix called dlq/ with the raw payload is a second lake.

Hashes, tokens, and keyed pseudonyms

A SHA-256 of an email, with no secret, is computable by anyone who can guess the email. Emails are guessable. The hash is not a privacy control against a dictionary. It is a transformation that feels like security.

When support must find a person and the mart must not contain the email, store the email in a vault table granted to the support role, and store a random token on the learner record if a non-support system must point at the vault without seeing the address. The token is meaningless without the vault. The vault's identity is the control.

When two datasets must join on a person without holding the email, a keyed HMAC, with the key in KMS or Key Vault or Cloud KMS and usable only by the job that creates the pseudonym, is a deterministic pseudonym. Anyone who can use the key can re-identify. The key's grant list is therefore the re-identification list. Keep it shorter than the analyst list. Do not put the HMAC key in the pipeline repository. Do not use a project-wide secret that every notebook can read.

Lesson 61 may still call the pseudonym personal data. The engineering control does not wait for that classification to drop the raw email from the mart.

Row and column controls live on the table the user can query

Masking in a BI tool, while the same user can query the base table, is a display preference. The enforcement point is the engine that accepts the query.

• Redshift: row-level security policies and column masking attached to the table and the role. The portal role does not also receive a superuser or a sys:superuser equivalent, and it does not receive the base table through a second schema.
• Dedicated SQL pool and Fabric warehouse, following the SQL pattern: a security policy and an inline table-valued predicate on seller_id, or the warehouse's row-level security, plus dynamic data masking only if the mask cannot be avoided by selecting the underlying column through another grant.
• BigQuery: a row access policy using the caller's identity, and policy tags or column data policies for sensitive columns. The analyst does not hold roles/bigquery.dataViewer on the base table if an authorized view is how they are supposed to see a subset.

The predicate uses the session identity. It does not use a parameter the client passes, such as a seller_id in the query text that the client can set to someone else's id. A policy that filters seller_id = @requested teaches the portal to ask for any seller. A policy that filters seller_id = session_seller() teaches the engine to ignore the request.

Test with two synthetic sellers. As seller A, select rows where seller_id is B. The result is empty. As the finance role, the same select returns the synthetic row. Record the test next to the publish. A view definition in git without this test is a comment.

Column-level grants follow the same shape. If email is masked for analysts, confirm an analyst select of that column returns the mask or an authorization error, and confirm a join to the vault is denied. Masking that returns NULL while a second table in the same dataset still has the email is a scavenger hunt you just assigned to every reader.

Audit is scoped, or it is noise and a bill

Management events, the control-plane changes, stay on: who changed a bucket policy, who disabled a key, who granted a dataset. Data-access events, the reads of objects and queries, are the expensive ones. Turn them on for the contact vault, the enrollment prefix, and the Northline order table. Think carefully before you turn full read auditing on for every progress object. The volume can cost more than the mart and can bury the vault read you needed. The honest design records the gap: "progress prefix object GETs are not individually audited; query history on the mart is retained; vault reads are audited." Lesson 61 can challenge the gap. It should not have to discover the gap during an investigation.

Query logs in Athena workgroups, Synapse, and BigQuery are part of this story. They contain the SQL text. SQL text can contain a pasted email in a WHERE clause. Treat query logs as restricted data, with retention and a grant of their own.

Results buckets and temporary Spark directories are copies. Expire them. Grant them as narrowly as the output deserves. A world-readable results prefix undoes a careful dataset grant.

The catalog is the handoff, not a second vocabulary

Lesson 22 named datasets as namespace plus name, and jobs as the things that read and write them. Glue, Microsoft Purview, and Dataplex should use those names. A scanner that invents dbo.tbl_completion_final2 beside tlp.completion_attempt gives Module 6 two graphs. The catalog entry for the mart carries the owner, the grain, the definition version, the classification of each column, and the pipeline identity that writes it. OpenLineage runs remain the record of a particular morning. The catalog remains the record of what the dataset is.

This lesson does not deploy a new lineage backend and does not write a privacy notice. The control sheet below is the artifact lesson 61 is allowed to assume exists.

Architecture and Design Diagrams

Identities meet data only on the edges you drew. The analyst has no edge to raw progress or to the vault.

mermaidCopy
flowchart TB
  subgraph people [Principals]
    JOB["Job identity"]
    BOARD["Board group"]
    SUPPORT["Support group"]
    ADMIN["Break-glass admin"]
  end
  subgraph stores [Stores]
    RAW["Raw progress"]
    MART["Published mart"]
    VAULT["Contact vault"]
    LOGS["Audit and query logs"]
  end
  JOB -->|"read and write projected columns"| RAW
  JOB -->|"write grain only"| MART
  BOARD -->|"select"| MART
  SUPPORT -->|"select"| VAULT
  ADMIN -->|"control plane, logged"| LOGS
  JOB -->|"read write use"| KEY["Customer-managed key"]
  BOARD -->|"use denied"| KEY

Network placement for the same platform. The public endpoint is absent on purpose.

mermaidCopy
flowchart LR
  subgraph private [Private network]
    RUN["Pipeline runtime"]
    PG["PostgreSQL"]
  end
  RUN --> PG
  RUN --> PEP["Private endpoint"]
  ANALYST["Analyst on private path"] --> PEP
  PEP --> CLOUD["Object store and warehouse"]

A token presented from outside that path fails the endpoint policy or the perimeter, even when the token's IAM would have allowed the read on an open endpoint. Identity and path are both closed.

Technical Explanation

What the job is allowed to do on a normal morning

The scheduler assumes tlp-completion-job. The job reads raw/progress/event_date=... and writes curated or the warehouse staging table. It can use the data key for those resources. It cannot list the vault container, cannot set IAM, and cannot disable the key. A forbidden column aborts the task before the write, which lesson 17 would call a failed publish. The failure page goes to the producer owner, because the platform job did the right thing. Retrying with a role that can "just drop the check" is the incident.

Dead-letter records written by Connect or by a load reject path use the same projection. If the original bytes must be kept for a parser bug, they land in a restricted prefix with the incident role and a short lifecycle, encrypted with the customer-managed key. The default log level of the job does not print the row.

What an analyst session is allowed to do

The board group selects the published view. The view does not include email. The group's grants do not include the base staging table, the raw external table, or the vault. Query text is logged in the workgroup or the job history that the platform team can read and the company-wide intern group cannot. A result file expires.

An investigator who needs one raw day gets a time-bound elevation to the investigator role, which can read a single date prefix and still cannot read the vault. Elevation expires. A permanent investigator grant on all raw history is how a debugging need became a standing copy of behavior data. Lesson 61 sets the review cadence. This lesson makes the standing grant visible because it is a different role from the board.

Keys and a failed decrypt

A pipeline that suddenly cannot decrypt is an availability incident. Causes that belong in the runbook: the key policy no longer names the job role, the key was disabled, the grant was removed, or the job is in a region the key does not allow. The response is to restore the grant or re-enable the key version you still have, not to copy the data to an unencrypted bucket so the morning can finish. A destroyed key version without another copy of the data is not a runbook step. It is a restore from a backup wrapped by a key you still control, rehearsed before you need it.

Provider-managed encryption avoids that class of outage and also avoids your ability to deny the key. Choose customer-managed keys for the vault and for raw enrollment because you may need to show who used the key and because you may need a hard stop. Choose them for the mart when the same requirement applies. Write the choice on the control sheet. "Encrypted by default" without naming the key is how both choices happen in different buckets.

Row-level security evaluation

The engine binds the session to a seller. The policy adds a predicate the user cannot remove by editing the SQL. A WHERE seller_id = 'someone_else' cannot widen the predicate. It can only narrow it. If your test as seller A can see seller B, the policy is not bound to that table, the user is exempt, or the user is querying a different object than the one you protected. Exemptions for "admin" roles are the usual hole. Finance is an explicit, tested exemption. The break-glass admin is not a daily reader of the portal.

Dynamic masking has a cousin hole. A mask on email in a view, with the column also present unmasked in a table the same role can join, is not a mask. Search the grants for the column, not the view text.

Catalog and lineage alignment

When the Glue table, the Purview asset, or the Dataplex entry is created, its name is the lesson 22 dataset name. The classification facet or the catalog tag uses the three outcomes: prohibited columns are absent, restricted columns are tagged restricted, mart columns are tagged with the grain. A pipeline run emits an OpenLineage event with the same dataset name, so a morning's merge points at the same node the catalog describes. Module 6's governance lesson can then attach retention to a node that exists. A scanner that guesses schemas from files is welcome as a detector of drift, and unwelcome as the author of the mart's classification. Drift, such as a new email column on raw progress, is a failed job, not a new catalog type to accept.

What this lesson leaves undone on purpose

Retention periods, the legal basis for processing learner_id, consent for a new use of progress events, the cadence of access reviews, data subject requests, and the mapping of these controls to a named regulation are lesson 61. Producer contracts and schema compatibility are the contract lesson. This lesson's failure mode is a platform that looks governed because a catalog product was purchased, while the job role can still read the vault. Buy the catalog if it stores the sheet. Do not count the purchase as the sheet.

Practical Implementation or Design Walkthrough

Problem

Produce the control sheet for TechLearningPro's completion publish and implement the projection and the grant shape. Northline's seller test is included because the same engines serve it. No new warehouse is introduced. Synthetic rows only. The walkthrough uses AWS IAM JSON because it is concrete, then states the Azure and Google Cloud equivalents so the sheet is not single-cloud.

Projection

pythonCopy
"""Column gate for the completion job. Synthetic names only."""

PROHIBITED = frozenset({"card_token", "pan", "cvv", "payment_payload"})
RESTRICTED = frozenset({"email", "full_name", "phone"})
MART_COLUMNS = (
    "learner_id",
    "lesson_id",
    "attempt_number",
    "completed_at",
    "event_date",
    "definition_version",
)


def columns_for(destination: str, incoming: list[str]) -> list[str]:
    prohibited = sorted(PROHIBITED.intersection(incoming))
    if prohibited:
        raise RuntimeError(f"prohibited columns: {prohibited}")
    if destination == "mart":
        leaked = sorted(RESTRICTED.intersection(incoming))
        # Drop restricted columns if a producer added them, and record the leak.
        # Presence of a prohibited column is fatal. Presence of email on raw
        # progress is a producer defect the job refuses to carry forward.
        allowed = [c for c in MART_COLUMNS if c in incoming or c in ("event_date", "definition_version")]
        if leaked:
            # The caller writes this list to the run log without values.
            print(f"dropped_restricted={leaked}")
        return allowed
    if destination == "vault":
        return [c for c in incoming if c in RESTRICTED or c == "learner_id"]
    raise RuntimeError(f"unknown destination: {destination}")

The mart destination never returns email. The vault destination never returns a payment column, because prohibited columns already aborted the function. Logging the dropped names is safe. Logging a sample row is not. The dead-letter writer calls the same function and may store event_id plus the error class, not the payload, unless the incident prefix is the destination and its grants match the vault's tightness.

Job policy shape

This identity can read raw progress and write the curated progress prefix. It cannot list the whole account, and it cannot touch the vault bucket. The account id is an example.

jsonCopy
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadRawProgress",
      "Effect": "Allow",
      "Action": ["s3:GetObject"],
      "Resource": "arn:aws:s3:::tlp-data-prod/raw/progress/*"
    },
    {
      "Sid": "ListRawProgress",
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": "arn:aws:s3:::tlp-data-prod",
      "Condition": {"StringLike": {"s3:prefix": ["raw/progress/*"]}}
    },
    {
      "Sid": "WriteCuratedDay",
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:DeleteObject", "s3:GetObject"],
      "Resource": "arn:aws:s3:::tlp-data-prod/curated/progress_event/*"
    },
    {
      "Sid": "UseDataKey",
      "Effect": "Allow",
      "Action": ["kms:Decrypt", "kms:GenerateDataKey"],
      "Resource": "arn:aws:kms:us-east-1:111122223333:key/example-key-id"
    }
  ]
}

There is no s3:*, no kms:*, and no vault bucket. DeleteObject is on the curated prefix because an idempotent overwrite removes the previous objects of that day. It is not granted on raw/. On Azure the same shape is an ACL: read and execute on raw/progress, write on the curated directory, no data-plane role on the account, no role on the vault container. On Google Cloud it is roles/storage.objectViewer on the raw prefix via a managed folder or a dedicated bucket, and object creator on the curated bucket, plus roles/cloudkms.cryptoKeyEncrypterDecrypter on that key only. Prefer a dedicated raw bucket if prefix conditions are awkward in the cloud you are on. A separate bucket is a clearer boundary than a clever condition.

The board group's warehouse grant is SELECT on published.completion_attempt only. On BigQuery that is a dataset the group can read, containing the authorized view, with the base dataset absent from their roles. On Redshift it is a group with select on the view and no superuser. On the dedicated SQL pool it is a user in a role that the security policy applies to.

Seller test

Create two synthetic sellers, seller_a and seller_b, and one synthetic order each. Bind the portal role for A to the policy. Run, as A:

sqlCopy
SELECT seller_id, order_id
FROM published.order_line
WHERE event_date = DATE '2026-11-10';

The predicate is on the session, so this query does not ask for a seller. It returns only A's row. Then explicitly:

sqlCopy
SELECT seller_id, order_id
FROM published.order_line
WHERE event_date = DATE '2026-11-10'
  AND seller_id = 'seller_b';

Zero rows. The finance role's repeat of the second query returns the synthetic B row. Store the three results in the change record. If the first query returns both sellers, stop the publish of the portal. Widening the role to debug the test is not a fix.

Control sheet for this walkthrough

Fill the table from the concepts section with these concrete values before Module 6 starts: job name tlp-completion-job, board group, support group, key id, private endpoint name, the prohibited and restricted sets from the Python, the seller test date, the audit datasets, the written gap on progress GETs, and the catalog name tlp.completion_attempt matching the OpenLineage dataset. A sheet with blank key and blank identity is a diagram.

Enterprise Real-World Scenarios

TechLearningPro's results prefix was the leak

The mart was clean. Email never landed in completion_attempt. An investigator with a wide Athena or BigQuery grant ran a query against a raw enrollment extract that still had email, and the query result wrote to the default results bucket. That bucket was readable by the analytics group. The board did not show the email. The results prefix did.

The fix is four controls, all on the sheet. Enrollment raw is not granted to investigators by default. The exploration workgroup's results prefix is a restricted bucket with a short lifecycle, granted to the same people who may run those queries, not to the board group. Query text logging is restricted. The job that builds enrollment raw drops email into the vault dataset and fails if card_token appears. The OpenLineage dataset for the extract lists email as restricted so a scanner that sees the column in the raw file raises drift instead of documenting a new public field.

Lesson 61 receives the sheet and decides how long the vault may keep an address, and how a learner's request is executed. The platform team does not wait for that policy to close the results bucket.

Northline's semantic model felt like security

Instructors use a BI model filtered to their seller_id. The warehouse login used by the BI tool is a service account with select on the full order_line table. Any instructor who can reach that login, or any second tool that uses it, sees every seller. The filter is a report setting.

The engine policy is bound to the instructor's own identity, or the BI tool passes the instructor's identity through to the warehouse so the policy sees the person and not a shared login. Shared service accounts for a multi-tenant portal defeat row-level security. Finance keeps a separate identity, tested. The semantic-model filter can stay as a usability hint. It is not the control, and the control sheet says so in one sentence the next audit can find.

A second failure appears at the dead-letter topic for bad order files. The payload includes the buyer email. The topic's ACL matches the raw order prefix, not the world. Capture files in the lake follow the vault retention, not the mart retention. Module 6 will set the number of days. The identity that can read those files is already small.

Best Practices

• One workload identity per pipeline, assumed by the scheduler, without permission to change IAM or to read the vault.
• Name principals, actions, and resources. Replace every "analytics can read the platform" sentence before it becomes a role.
• Remember the coarse grant that wins: an Azure data-plane role over ACLs, a project-level dataset reader over an authorized view, a second schema grant beside a careful view.
• Customer-managed keys for the vault and enrollment raw. Rotation creates versions. Destruction is a data-loss change you rehearse against a backup.
• Private endpoints with the public path closed. Test a synthetic copy that must fail.
• Fail the job on prohibited columns. Drop restricted columns on the mart path. Include the dead letter.
• Do not treat an unsalted hash of an email as a control. Use a vault, or a keyed pseudonym whose key almost nobody can use.
• Bind row filters to the session identity. Test seller A against seller B's rows.
• Audit the vault and the admin plane. Write down the high-volume prefixes you are not fully auditing.
• Use lesson 22's dataset names in Glue, Purview, or Dataplex. Hand the completed sheet to lesson 61.

Common Mistakes and Anti-Patterns

The job runs as administrator. Every bucket and every grant is reachable from a compromised dependency. The morning will still publish. The blast radius is the account. Use the prefix policy.

A shared login for the portal. Row-level security sees one principal and cannot tell instructors apart. Per-instructor identity, or the control is a BI filter.

A filtering view plus select on the base table. Users will query the base table, or a future tool will. Grant the view, not the table.

A client-supplied seller id inside the predicate. The portal asks for a seller and the engine believes it. Bind the session.

SHA-256 of email stored on the mart as "anonymized." A dictionary of emails recreates the column. The raw address was the sensitive value, and the hash is a lookup key. Use a vault or a keyed pseudonym.

Key destruction as a cleanup task. Old objects stop decrypting. Rotation was the task you meant. Destruction requires a backup under a key you still have.

Public endpoint left open beside a private endpoint. The private path is optional. Close the public path and test.

Dead letter stores the original payload in an open prefix. The prohibited column the job rejected is now durable and readable. Quarantine keys and error classes, or lock the prefix down like the vault.

Full data-access logging on every progress object, or no logging anywhere. One drowns the signal and the budget. The other leaves the vault unaudited. Scope it and write the gap.

A catalog product with generated names. Lineage points at tbl_final2 and the pipeline writes tlp.completion_attempt. Module 6 governs the wrong node. Align the names before you call the platform governed.

Troubleshooting and Design Review

AccessDenied after a grant you just added. Identify the layer that denied: identity policy, bucket policy, Lake Formation, ACL versus RBAC, VPC Service Controls, or a key policy. Read the error's service. Adding AdministratorAccess before you know the layer is how the job's privilege grows.

The job cannot decrypt this morning. Check whether the key is disabled, the key policy dropped the role, or the region condition excludes the runtime. Restore the intended grant. Do not copy to an unencrypted bucket to finish the publish.

Seller A sees seller B. Confirm which object was queried, whether A is exempt, and whether the BI tool used a shared login. The SQL text of the test belongs in the ticket. Widening access to "reproduce more easily" destroys the evidence.

Email appears in a results bucket. Find the workgroup or the job's destination dataset. The mart can be clean while the results are not. Expire the prefix, restrict the grant, and remove the raw table from that identity.

A new column arrives on raw progress. The projection fails the publish or drops a restricted name and alerts. If the publish succeeded and the column is in the mart, the projection was not on the path that wrote. Find the second writer. Lessons 54 through 56 allowed only one.

Audit logs do not show a read someone reported. Check whether that prefix was in the written gap. If the vault was in the gap, that is a design error. Turn vault auditing on and treat the gap as an incident for that dataset. If the read was a progress object GET and the gap says those are not logged, use query history and the pipeline run instead, and be honest about what you cannot see.

The catalog shows a different classification than the job. The scanner accepted a new schema. The job is the authority for prohibited columns. Reconcile the names. Do not let the scanner's acceptance become the contract.

Design review questions:

• What is the job identity, and can it change IAM or read the vault?
• Which grant is coarse enough to override the fine one?
• Which key wraps the vault, and who can destroy versions?
• Is the public endpoint closed, and did a synthetic copy fail?
• Where is email actually readable, including results, logs, and dead letters?
• What is the seller test, and when did it last pass?
• Which OpenLineage name is on the catalog entry?
• Which rows of the control sheet are still blank?

Interview Questions and Answers

Q1. Why is a pipeline's administrator role an incident even when the job is correct?

The job's code may project columns perfectly and still be one stolen task credential away from every bucket the administrator can read. I want the credential to read raw progress and write curated progress, and to use one key. A compromised dependency should not be able to read the contact vault or to edit IAM. Least privilege is the blast radius. Correct SQL is not a substitute.

Q2. What does rotating a customer-managed key do to objects already written?

Rotation adds a version. New wraps use the new version. Existing objects stay encrypted under the data keys wrapped by the older version, and they keep decrypting while those versions remain enabled. Rotation is not a rewrite, and it does not make old copies unreadable. Disabling or destroying key material can make them unreadable. I treat destruction as a data-loss change with a tested backup under a key I still control.

Q3. Why is an unsalted SHA-256 of an email a weak control on the mart?

Emails are low-entropy and guessable. Anyone can hash candidates and join to the mart. The column still functions as a lookup key for the address. I keep the address in a vault granted to support, or I use a pseudonym keyed by a secret almost nobody can use. I expect the privacy lesson to decide whether the pseudonym is still personal data. I do not wait for that decision to remove the raw email.

Q4. Where must masking and row filters be enforced?

On the engine that answers the query, on the object the user is actually granted. A BI filter, a view the user can bypass by selecting the base table, or a mask on one copy of the column while another copy sits in the same grant, does not hold. I test as the restricted identity and I look for a second grant path. For sellers, the predicate uses the session identity, not a seller id the client submits.

Q5. How is a private endpoint different from an identity grant?

The private endpoint decides which network path can reach the API. The grant decides which principal can perform which action. A broad role on a private network can still read the vault. A valid token from the public internet can still read it if the public endpoint is open. I close the public path and keep the role narrow. On Google Cloud I add a perimeter so a token outside the perimeter cannot copy data to an outside project. On the other clouds I combine org policies, firewalls, and resource policies, and I test a synthetic copy that has to fail.

Q6. What does VPC Service Controls add that a firewall rule does not?

A firewall controls packets and addresses. A stolen token used from an allowed address, or a copy API that the firewall does not understand as "exfiltration," can still move data. A perimeter policy can deny the service call that copies BigQuery or Storage out of the projects inside the perimeter, based on identity and project, not only on IP. I still want the private path. The perimeter is the control that makes the token insufficient. I verify it with a synthetic object and a test identity, and I record the deny.

Q7. How do you audit reads of enrollment without logging every progress GET?

I turn data-access audit on for the enrollment prefix and the contact vault, and I keep admin-change logs everywhere. I retain warehouse query history, and I treat the SQL text as restricted. I explicitly do not buy object-level GET logs for every compacted progress file if that volume buries the signal, and I write that gap on the control sheet so an investigation does not assume the evidence exists. If someone later needs those GETs, that is a sheet change with a cost, not a surprise.

Q8. What does this lesson hand to the compliance lesson, and what does it leave there?

I hand identities, the key and who can destroy it, the network path and the synthetic test, the column outcomes, the enforcement point, the seller test, the audit scope and the written gaps, and catalog names that match OpenLineage. Lesson 61 attaches retention, consent, access-review cadence, the legal basis for learner_id, and regulatory evidence. I do not write those as if a catalog purchase finished them. I also do not leave the column list blank for that lesson to invent.

Q9. A row-level policy compares seller_id to a query parameter the portal sends. What is wrong, and what do you bind instead?

The caller chooses the parameter, so the caller chooses the seller. The policy has to compare seller_id to an identity the engine derived from the session, a group mapping, or a signed context the user cannot edit. I test with seller A's credentials and a predicate that asks for seller B, and I expect zero rows. Finance is a separate identity with an explicit exemption, also tested.

Q10. How should catalog entries line up with OpenLineage so Purview, Dataplex, and Glue do not become a second language?

The dataset name in the catalog is the namespace and name from lesson 22, the same string the pipeline emits on the run. Classification tags hang on that node. Scanners may detect drift, such as an email column appearing on raw progress, and that drift fails the job. Scanners do not get to publish tbl_final2 as the governed object while the job writes tlp.completion_attempt. One name, or Module 6 governs a picture.

Q11. A dead-letter topic receives the original event when parsing fails. How do you keep a payment field out of it?

I do not write the original payload to a general-purpose dead-letter prefix. I write the offset or event_id, the error class, and the columns that already passed the prohibited-column check. If the raw bytes are required to fix a parser, they go to an incident prefix with vault-grade grants, the customer-managed key, and a short lifecycle. The main job's projection and the dead-letter writer share one column policy. A green consumer and an open dlq/ bucket is a second lake.

Hands-On Exercises

Exercise: Control sheet and a bypass test

Problem statement. Using synthetic data only, write the control sheet for the completion mart and for a two-seller order table. Implement the column gate. Implement a session-bound filter, or describe the exact engine policy if you cannot run Redshift, Synapse, or BigQuery, and show a test that would fail if the filter were a query parameter. Do not use real personal data. Do not create a broad admin role "to get the lab working."

Tasks.

1. Fill the control sheet: identities, key choice, network, column outcomes, enforcement point, audit scope, catalog name, and the rows you are explicitly leaving for lesson 61.
2. Run the projection function on a schema that includes email and on one that includes card_token. Show a dropped-column result and a failed result.
3. Grant a board identity the published object only. Attempt to read raw or the vault with that identity and record the deny.
4. As seller A, select seller B's synthetic order. Record zero rows. Show a second statement, as finance, that can see both synthetic sellers.
5. Name one results prefix or temporary bucket and its lifecycle and grant.
6. Write the OpenLineage dataset name and the catalog name on one line and make them identical.

Expected deliverables. Control sheet, projection evidence, deny evidence, seller-test evidence, results-bucket note, catalog name.

Evaluation rubric.

Lesson Summary

The provider runs the substrate. You run identity, grants, keys, paths, and the columns that land. Jobs assume a narrow workload identity. Humans assume expiring roles. The coarse grant, a data-plane RBAC role, a project-wide reader, a second schema, beats the careful ACL or the careful view, so the careful one has to be the only one. Customer-managed keys wrap data keys for the vault and for enrollment. Rotation adds versions. Destruction can erase practical access to history. Private endpoints with the public path closed, and a perimeter or an equivalent tested deny, sit beside identity rather than replacing it.

Prohibited columns fail the job, including the dead letter. Restricted columns stay in a vault. A hash of an email is not a vault. Row and column rules bind the session on the object the user can query, and seller A is tested against seller B. Audit covers the vault, the admin plane, and query text, and it documents the high-volume gap instead of pretending every GET is kept. Catalog names match lesson 22.

Data Platform Cost and Performance Optimization prices the idle warehouse, the wide scan, and the audit logs you just scoped. Data Governance, Privacy and Compliance is where retention, consent, and access reviews attach to this sheet. Arrive there with the sheet filled in. The capstone will be asked which identity can read the mart and which identity can read the vault, and a blank row is the wrong answer.

Further Reading

• AWS IAM best practices and the S3 security guide, including Block Public Access and bucket policies.
• AWS KMS envelope encryption for customer-managed keys, grants, and rotation.
• AWS Lake Formation and Redshift row-level security in the Redshift database developer guide.
• Azure Data Lake Storage access control and Azure Key Vault.
• Row-level security for the SQL predicate pattern used by a dedicated SQL pool.
• Google Cloud IAM, Cloud KMS, VPC Service Controls, and BigQuery column-level and row-level security in the BigQuery documentation.
• OpenLineage for the dataset names this lesson refuses to fork, and lesson 22 for how this course emits them.
• Lesson 61's brief in the production module: retention, consent, and access reviews start from the sheet this lesson builds, and they are not repeated here.