Data Governance, Contracts, and Classification Questions

The safe pattern is expand-contract: add the new shape alongside the old one, migrate consumers over on their own schedule while both are live, backfill history so the new shape is complete, validate the two agree, and only remove the old shape once every known consumer has moved past a deadline with a tested rollback plan at every step. Never mutate the old field in place, since anyone still reading it breaks the moment you do.

Expand, migrate, contract

• Expand: add the new field or shape (for example first_name and last_name alongside the old full_name, or a new event version order_placed_v2 alongside order_placed_v1) without touching the old one. Both are populated at write time, either via a dual write or a single write that derives both.
• Migrate: consumers switch to reading the new shape at their own pace, guided by the contract's compatibility rules.
• Contract: once every known consumer is confirmed off the old shape, not just believed to be, the old field or version is removed.

Consumer notification and migration-deadline process

Start from the dataset's registered consumer list, which is exactly why the data-contract and catalog registration discussed elsewhere in this topic matters: you need to know who is downstream before you can safely change anything. Announce the change with the what, the why, the new shape, a concrete migration deadline, and a point of contact, then follow a fixed notification cadence (an initial announcement, a halfway reminder, a one-week-out reminder) rather than one email and silence. Track migration status per consumer explicitly, not assumed.

Backfill and data-parity validation

Historical rows or events written before the expand step will not have the new shape unless you backfill them, so run a backfill job that derives the new field from the old one for existing data. Then validate parity: compare old-shape and new-shape values on a sample, or the full set if it is cheap enough, and confirm they agree everywhere they overlap, ideally as an automated check that blocks the contract phase from proceeding if parity fails.

Tested rollback plan

Before removing the old shape, have a rehearsed way back: keep dual-writing the old field until well past the point you are confident, and know exactly how you would restore it if the new path turns out wrong. A rollback plan that has never been exercised is not a plan; test reverting against a staging dataset, or on a subset of production traffic, before relying on it for real.

Near-zero-downtime pattern for a very large fact table

For a fact table too large to rewrite in place without a long lock or outage, the same expand-contract idea applies at the storage layer: write the new column via a backfill job that runs in batches, for example by primary-key range, alongside ongoing normal writes, rather than a single blocking schema change and full-table rewrite, so the table stays queryable throughout. Table formats built for this, such as Iceberg or Delta Lake (open table formats designed for schema evolution on large tables without a full rewrite), make the "add a column" step itself nearly free, a metadata-only operation, which is why the real cost is almost always the backfill and parity work, not the schema change itself.

Escalating a team that refuses to migrate by the deadline

Go up the chain deliberately, not straight to force. First, a direct conversation with the consuming team about what is blocking them, since sometimes it is a real resourcing conflict rather than refusal. If that stalls, escalate to both teams' managers with the concrete risk (what breaks, when, for whom) stated plainly, and propose either a firm final date or, if the risk is asymmetric, a decision to extend the deprecation window with an agreed cost owner. As a last resort, the producer can dual-write both shapes past the deadline at its own cost, or, if the organization grants that authority, deprecate the old shape anyway with monitoring on the abandoned consumer so its breakage is detected fast and attributed correctly, rather than silently absorbed as an unowned incident.

Worked example

The orders table's status column holds free-text values like "paid" and "shipped", and three downstream jobs each parse it differently, breaking repeatedly on new values. The team splits it into status_enum and status_detail.

1. Expand: add both columns, backfilled from the existing 40 million row table in batches of 100,000 rows keyed by order_id range, and populated going forward on every new write alongside the old status column.
2. Parity check: for every row, confirm that reconstructing status from status_enum and status_detail matches the original value; any mismatch is logged and fixed before proceeding.
3. Notify: the three consumer teams get a deprecation notice naming status, the new columns, and a six-week deadline, with reminders at three weeks and one week left.
4. Migrate: each team switches its queries; the producer marks them done only once confirmed, not assumed.
5. Contract: after the deadline, with all three teams confirmed migrated and the old column unread for two full weeks by query-log audit, status is dropped.
   If, at week five, one team has not started, the producer escalates to that team's manager with the specific date and the specific queries still hitting the old column, rather than unilaterally dropping it on schedule.

Trade-offs and pitfalls

Dual-writing costs extra write capacity and code complexity for the transition window, and skipping the parity-validation step to save time is the most common way this pattern still causes an incident, because it hides the exact case where the old and new shapes silently disagree. The other common failure is trusting "nobody should still be using this" instead of measuring it via query logs or access records before dropping the old shape; assumed-dead consumers are the ones that actually break.

Direct answer. Make deletion tractable by isolating personal identifiers into a single dedicated table keyed by a pseudonymized ID from the start, so every other system, including the data lake, the warehouse, streaming topics, derived and denormalized tables, and ML (machine learning) training sets, references the pseudonym instead of storing the real identifier. A deletion request then becomes deleting or crypto-shredding one row plus fanning out a tombstone event, instead of hunting through every table in the company. For systems that can't practically delete in place, such as backups and some ML training artifacts, apply irreversible pseudonymization or crypto-shredding, and produce an auditable receipt for every system confirming completion, not just a log line saying the job ran.

Schema pattern that makes this tractable. Assign every subject a stable pseudonymized ID, a random or hashed identifier with no reversible mapping exposed outside the identity system, at first contact. Store the mapping between the pseudonym and any real identifying attributes (name, email, phone, address) in exactly one isolated table, access-controlled separately from everything else. Every other table, topic, and derived dataset references the pseudonym, never the real identifier directly; if a downstream system needs to display a real name, it looks it up from the isolated table at render time rather than copying the name into its own storage. Deletion then reduces to deleting or crypto-shredding (discarding the encryption key so the ciphertext becomes permanently unreadable) the one row in the isolated table, after which the pseudonym everywhere else becomes an opaque, non-personal identifier.

Identifying every place the data lives, and how each is handled

• Data lake, raw and curated zones: tag every dataset containing the isolated PII (personally identifiable information) table or a denormalized copy of it at ingestion; a deletion event triggers a rewrite of affected partitions, not a soft flag.
• Warehouse: derived tables should already reference the pseudonym only, so the mapping table gets the delete; any warehouse table that denormalized real attributes directly needs its own remediation job.
• Backups and snapshots: cannot practically be edited in place. Either let old backups expire naturally within a documented retention window, disclosed as part of the deletion SLA (service-level agreement), meaning the data becomes irretrievable in production but persists in an inaccessible backup until it rolls off, or encrypt the isolated PII table with a per-subject key and crypto-shred the key so the backup contains only permanently unreadable ciphertext.
• Streaming topics: publish a deletion or tombstone event keyed by the pseudonym to a compacted topic so consumers apply the same removal; topics that already stored real attributes directly need a targeted purge or key rotation, which is exactly why the isolation pattern should be enforced at the producer level going forward.
• Derived, aggregated, and denormalized tables: an aggregate that already summed or counted a subject's contribution can't be un-summed for one individual without recomputation. Aggregates where the subject's contribution isn't separable, for example "total monthly signups" among thousands, generally don't need per-subject reconstruction since no personal data is retrievable from them; but any denormalized table that copied the subject's raw attributes directly, the exact anti-pattern the isolated-table design avoids, must be identified and remediated as its own class of hard case, because the schema pattern didn't protect it in the first place.
• ML training sets: a model trained on data containing the subject's attributes is the hardest case, since deleting the raw training row doesn't remove what the model already learned. If the training pipeline referenced the pseudonym and looked up attributes at training time, deletion cascades the same way as any other pseudonym-keyed dataset. If raw PII was baked directly into stored training files, those artifacts must be identified and deleted, and the affected model scheduled for retraining on its normal cadence; exact "unlearning" from an already-trained model without retraining isn't something that can be reliably promised, so a scheduled retrain, not an in-place model edit, is the honest answer.

Verifiable proof for an auditor. A central deletion service issues a deletion event with a unique request ID and records which systems must acknowledge. Each downstream system, lake job, warehouse job, streaming consumer, backup rotation, writes a signed completion receipt back (system name, request ID, timestamp, rows or partitions affected) to an audit log the service owns. That audit log, not each team's word, is what's shown to an auditor: request R, acknowledged complete by N of N required systems, with receipts. Periodically, not just per request, a scan across systems for the deleted pseudonym confirms it doesn't resurface, for example from a delayed backfill job re-ingesting an old file, and any resurfacing is treated as its own incident.

flowchart TD
    A[Deletion request: subject S] --> B[Deletion service]
    B --> C[Isolated PII table: delete or crypto-shred row]
    B --> D[Tombstone event: pseudonym P]
    D --> E[Data lake jobs]
    D --> F[Warehouse jobs]
    D --> G[Streaming consumers]
    D --> H[ML training pipeline: flag for retrain]
    E --> I[Signed completion receipt]
    F --> I
    G --> I
    H --> I
    I --> J[Audit log: request R, N/N acknowledged]

Worked example. Subject S requests deletion. The deletion service deletes S's row from the isolated pii_identity table (keyed by pseudonym P) and publishes a tombstone for P to a compacted topic. A warehouse job that joined orders to pii_identity for display purposes now resolves no name for P, and needs no rewrite, since the orders table itself only ever stored P, never the name. A denormalized legacy export table that copied customer_name directly two years ago, before the isolation pattern was enforced, is flagged separately by the catalog tag and requires a one-off remediation job. The ML training pipeline had ingested pii_identity fields directly into a stored feature file for a churn model; that file is identified, the row purged, and the churn model queued for its next scheduled retrain rather than promised an immediate in-place fix. Each of the four affected systems, warehouse, streaming, legacy export, and ML pipeline, writes a completion receipt, and the deletion service shows 4/4 acknowledged in the audit log as the auditor-facing proof.

Trade-offs and pitfalls

• The isolated-PII-table pattern only works going forward and for systems built to reference the pseudonym; retrofitting it onto years of tables that denormalized real attributes directly is a real migration project, not a policy memo.
• Backups are the honest limitation almost every design has to disclose: either accept a documented backup-expiry window as part of the deletion SLA, or invest in per-subject encryption keys so backups can be crypto-shredded, which adds real key-management overhead.
• The most common wrong turn is declaring deletion done when the primary systems are clean but derived, denormalized copies and ML artifacts were never inventoried; a completion receipt from every known system is only as good as the catalog's completeness in knowing which systems exist.

Direct answer

Build a federated catalog layer that harvests metadata from each provider's native catalog (AWS Glue Data Catalog, Google Cloud's Data Catalog, and Azure Purview, now branded Microsoft Purview) into one normalized index, treat one provider's tag as authoritative source of truth per dataset for ownership and sensitivity rather than trying to keep three independently-editable copies in sync, and resolve description conflicts with an explicit, ranked rule set rather than a silent overwrite.

Structured elaboration

Harvesting into a normalized model. Each cloud catalog (AWS Glue Data Catalog, Google Cloud's Data Catalog, and Azure Purview) has its own schema and terminology for the same underlying concepts (a "table" and a "database" mean roughly the same thing across all three, but field names differ). A harvester per provider maps each into one normalized schema (dataset, owner, sensitivity tag, technical schema, lineage) so the unified search layer queries one consistent model instead of three different ones.

Keeping ownership and sensitivity tags synchronized. The reliable pattern is single-source-of-write per dataset: whichever cloud actually hosts a given dataset is the authoritative source for its native catalog metadata, and the unified layer treats that provider's tag as the source of truth, propagating it outward read-only to the unified index rather than allowing edits in the unified layer to be independently pushed back into three systems (which is where sync conflicts actually originate). A scheduled re-harvest (not a one-time sync) keeps the unified index from drifting from the source of truth as the underlying tag changes.

Resolving conflicts when the same dataset is described differently in two systems. This mainly comes up when a dataset is mirrored or referenced across providers (a table replicated from AWS into a BigQuery-based warehouse, cataloged separately in both). The resolution rule needs an explicit precedence, for example: the system that owns the canonical write path for the data wins for sensitivity classification (since that determines who is actually allowed to touch the source data), while the most recently updated description wins for the human-readable definition (since a fresher definition is more likely to be accurate). Automatic resolution should flag, not silently overwrite, a conflict where the two systems' sensitivity tags actually disagree, since that specific disagreement (one system says internal, another says confidential, for the same underlying data) is a real risk signal worth a human look, not just noise to average away.

flowchart TD
    A[AWS Glue Data Catalog] --> D[Federated search index]
    B[Google Cloud Data Catalog] --> D
    C[Azure Purview] --> D
    D --> E[Conflict resolution + merge rules]
    E --> F[Unified ownership and sensitivity record]
    F --> A
    F --> B
    F --> C

Worked example

A customer_events dataset exists natively in AWS (source of truth, tagged confidential in AWS Glue Data Catalog because it contains raw device identifiers) and is also mirrored into a Google BigQuery dataset for a separate analytics team, cataloged there as internal because the mirroring team was not aware of the sensitivity of the source columns. The unified catalog's harvesters pull both records. The conflict-resolution rule (canonical source wins for sensitivity) flags this: the mirrored copy's internal tag disagrees with the source's confidential tag for what is fundamentally the same data. Rather than silently picking one, the unified catalog surfaces the mirrored dataset as confidential (inherited from source, conflicts with local tag) and opens a review task for the mirroring team, which is the actual finding this whole system exists to catch.

Trade-offs & pitfalls

Treating "most recently updated wins" as the rule for sensitivity tags (instead of only for descriptions) is a common and dangerous shortcut, it lets a careless recent edit silently downgrade a genuinely sensitive dataset's classification. Harvesting on a schedule rather than via native change events introduces a lag window where the unified catalog can show stale information, which matters most exactly when a sensitivity tag has just been tightened for a real reason. And normalizing three providers' different metadata models into one schema always loses some provider-specific nuance, a Purview-specific classification label with no clean AWS or Google Cloud equivalent has to be mapped to the nearest normalized tier, which is a lossy translation the unified layer needs to document, not hide.

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.

The core requirement, answering "what breaks if I change this column" quickly for tables, dashboards, and scheduled jobs, is a graph-traversal problem, finding all descendants of a node, and a native graph database fits it far better than a relational schema, because impact analysis on deep chains means multi-hop traversal, which relational joins handle increasingly poorly as chain depth grows. The design stores nodes for each dataset, column, and job, and edges for produces or depends-on relationships, and keeps the graph current via the same static-plus-runtime lineage-capture mechanisms discussed elsewhere in this topic, updated incrementally on every pipeline run rather than recomputed from scratch.

Metadata model

Nodes: datasets (tables, dashboards, scheduled jobs, and optionally columns for column-level precision), each carrying metadata such as owner, classification, and last-updated timestamp. Edges: directed dependency relationships, for example "job J reads table A and writes table B" becomes A to J to B, typed by relationship (produces, reads, schedules) so a query can filter, such as "downstream dashboards only" versus "downstream jobs only." A proposed change to a source table's column becomes a query: start at that column's node, traverse all outgoing edges transitively, and return every table, dashboard, and scheduled job reachable, answering "what breaks downstream" across all three artifact types in one traversal rather than three separate queries against different systems.

Storage choice: graph database versus relational store

A relational store can technically represent this, an edges table of from_id, to_id, but answering "everything downstream of X, arbitrarily many hops deep" means a recursive query or repeated self-joins, and query cost grows with chain depth and fan-out in a way that gets slow specifically on the deep chains and thousands-of-datasets scale this design targets. A native graph database stores adjacency directly and is built for exactly this traversal pattern, index-free adjacency, so following an edge is a direct pointer-hop rather than a join, meaning impact-analysis queries stay efficient as chains get deep, which is the specific property relational storage lacks here. The trade-off: a graph database is another system to operate, and for simple, shallow lineage, most datasets only a few hops from their sources, a relational or even a simple adjacency-list approach might be sufficient. The graph-database choice earns its complexity specifically because this design's stated requirement is fast traversal over deep chains at thousands-of-datasets scale, including cases where a catalog user interface needs a consistently fast response even when a single dataset's downstream footprint spans on the order of ten thousand connected nodes, a workload graph databases are built for and relational recursive-join traversal degrades on as depth and fan-out grow.

Keeping the graph current as pipelines change

The graph is only useful if it reflects reality, so it needs continuous updates rather than periodic rebuilds: every pipeline run, a dbt (a SQL-based data transformation tool) model build, a Spark (a distributed data-processing engine) job, or an Airflow (a workflow orchestration tool) task, emits a lineage event on completion, using the static-plus-runtime capture approach discussed elsewhere in this topic, and the graph is updated incrementally, adding or replacing the edges that job is responsible for, rather than recomputing the whole graph from scratch on every change. For a source-table schema change specifically, a rename, drop, or type change, the impact-analysis query runs against the current graph before the change ships, as part of the same CI (continuous integration) compatibility check discussed elsewhere in this topic, so "what breaks" is answered proactively rather than discovered after the fact. For very high-volume environments, both batch pipelines and streaming jobs, potentially processing on the order of a petabyte a year of underlying data, need to emit lineage events on their own schedule: streaming jobs emit incrementally, per micro-batch or checkpoint, rather than waiting for a job that conceptually never finishes, and batch jobs emit once per run.

flowchart TD
  A[Source table: proposed change] --> Q{Impact-analysis query: traverse graph}
  Q --> T[Downstream tables]
  Q --> D[Downstream dashboards]
  Q --> J[Downstream scheduled jobs]
  P1[dbt/SQL run] -->|emits lineage event| G[(Graph store)]
  P2[Spark job] -->|emits lineage event| G
  P3[Streaming job, per micro-batch] -->|emits lineage event| G
  G --> Q

Worked example

A warehouse tracks 15,000 datasets with lineage chains up to 12 hops deep from raw ingestion to executive dashboard. An engineer proposes dropping a column, orders.legacy_discount_flag, from a source table. The impact-analysis query starts at that column's node and traverses all outgoing edges: it finds two downstream tables that select it directly, one dbt model that references it in a conditional expression, found via the static SQL parse, zero Spark jobs, and one dashboard, promo_dashboard, that queries one of those two tables. The query returns all four affected artifacts, two tables, one dbt model, and one dashboard, in a single traversal from one starting node, letting the engineer notify promo_dashboard's owner and the two table owners before the drop ships, rather than after someone notices the dashboard broke. Because the graph is updated incrementally on every pipeline run rather than recomputed nightly, this query reflects lineage current as of the last run of each upstream job, not a stale weekly snapshot.

Trade-offs and pitfalls

Operating a graph database is real added infrastructure, a new system with its own scaling and backup story, that is not justified for a small warehouse with shallow lineage; the choice earns its keep specifically at the deep-chain, many-thousands-of-datasets scale this design targets. The main correctness pitfall is letting the graph drift from reality when a job fails to emit its lineage event silently, whether from a bug or a manual out-of-pipeline script; the graph then confidently answers "nothing downstream" for an edge it never learned about, which is worse than an admittedly stale graph, so a reconciliation check, periodically diffing the graph's known edges against actual query logs or table access patterns, is worth running to catch missed emissions.