Data Governance, Contracts, and Classification Questions

Data lineage is the recorded trail of where a piece of data came from and every transformation it passed through on the way to where you are looking at it now, a map of what fed into what. It matters for three distinct reasons: it lets you trace a wrong number back to its cause instead of guessing, it lets a stakeholder verify a dashboard's number is trustworthy by seeing its provenance rather than taking it on faith, and it lets you answer a compliance or audit request, "show me everything that fed into this reported figure," without manually reconstructing the pipeline's history.

Why it matters: debugging a wrong number

Without lineage, tracing a bad number backward means manually reading pipeline code and asking around. With it, you follow the recorded chain from the dashboard metric back through each transformation to the source tables, narrowing down where the value diverged from expectation directly, often in minutes instead of hours or days of archaeology.

Why it matters: building trust in a dashboard

A stakeholder who can see, even at a summary level, that a revenue number traces back through a specific, named set of transformations to a specific source table trusts it more than an opaque number with no visible provenance. When the number is questioned, "here is exactly where it comes from" is a much stronger answer than "the pipeline is supposed to be correct."

Why it matters: compliance and audit

Regulators or internal auditors asking "show me what fed into this reported figure" need a defensible, complete answer, and reconstructing that by hand for a complex pipeline is slow and error-prone. Captured lineage turns that into a query rather than an investigation, and provides an auditable record even for figures nobody has looked closely at in months.

Dataset-level versus column-level lineage

Dataset-level lineage tracks dependencies at the table or file level, "table C was built from tables A and B," without saying which specific columns of A and B fed which columns of C. It is cheaper to capture and is enough for coarse-grained impact analysis, such as "if table A changes, table C might be affected."
Column-level lineage tracks the dependency down to individual fields, "column revenue in table C is derived specifically from price and quantity in table A, via this transformation," and not from the other twenty columns table A happens to have. It is more expensive to capture, usually requiring parsing the actual transformation logic rather than just job-level metadata, but it gives a precise answer to "what actually feeds this specific number," which dataset-level lineage can only approximate.

Worked example

An executive dashboard shows quarterly revenue dropped 8% quarter over quarter, and the finance team does not believe it. Dataset-level lineage shows the revenue metric comes from a fct_revenue table, itself built from orders and refunds, which narrows the search to two tables but not further. Column-level lineage shows the dashboard's revenue figure specifically traces to orders.amount_cents minus refunds.refund_amount_cents, aggregated by quarter, and that a transformation two steps upstream started applying a status filter differently after a recent change, causing pending orders to be dropped from the current quarter's total but not the prior quarter's, a data artifact rather than a real revenue drop. Because the lineage was captured at column level, this took one query against the lineage graph instead of re-reading every transformation in the pipeline by hand. The same trace, if requested later by an auditor asking how the reported quarterly revenue figure was derived, is already sitting there as a reusable record rather than something that has to be rebuilt from memory.

Trade-offs and pitfalls

Dataset-level lineage is much cheaper to maintain and is often good enough for impact analysis, such as deciding who to notify before a schema change, so jumping straight to column-level everywhere is over-engineering for teams that mainly need the coarser view; column-level lineage earns its cost specifically for high-stakes, frequently-questioned metrics like revenue or compliance-reported figures. The common pitfall is treating lineage as something you can bolt on retroactively for an audit request; if it is not captured continuously as pipelines run, reconstructing it after the fact for a specific number someone is now questioning is exactly the manual archaeology lineage is supposed to replace.

Backward compatibility means a reader using the new schema can correctly read data written with the old schema. Forward compatibility means a reader using the old schema can correctly read data written with the new schema. Full compatibility requires both at once, so producers and consumers can each upgrade independently, in either order, without breaking. Three concrete strategies that keep old and new consumers working as a schema evolves are: only ever adding nullable or optional fields (never new required ones), never repurposing an existing field's type or meaning, and running automated compatibility checks in CI (continuous integration) before a producer's change ships.

The three compatibility modes

• Backward: new schema reads old data. This lets you evolve the schema and still process previously-written data without a rewrite.
• Forward: old schema reads new data. This lets producers roll out a new schema before every consumer has upgraded, because old consumers keep working against new-format data.
• Full: both hold at once. This is the strictest and safest mode for datasets with many, uncoordinated consumers, because it removes ordering assumptions entirely.

Strategy 1: only ever add nullable or optional fields

Adding a required field breaks any reader that does not know to populate or expect it. Adding an optional field with a sensible default (or that is simply absent when missing) is safe in both directions: old readers ignore it, new readers get a default when it is missing from old data.

Strategy 2: never repurpose an existing field's type or meaning

Changing a field's type (a string ID becoming an integer) or its semantics (a percentage field switching from a 0-1 scale to a 0-100 scale) while keeping the same name is the most dangerous class of change, because it can pass a naive type check while silently corrupting every consumer that assumed the old meaning. If the meaning must change, add a new field with a new name rather than mutating the old one in place.

Strategy 3: run compatibility checks in CI before a producer's change ships

A CI job on the producer's pull request calls the schema registry's compatibility-check endpoint with the proposed schema, evaluated against the subject's configured mode (backward, forward, or full), and fails the build on an incompatible change. This catches the break before merge instead of after a downstream job fails in production.

Parquet and Avro specifics, and where enforcement should live

Avro (a data serialization format commonly used for streaming events) resolves fields by name (with optional aliases) between a writer's schema and a reader's schema, so a rename without an alias breaks resolution even though the field's type is unchanged. Parquet (a columnar file format commonly used in data warehouses and data lakes) stores its own schema per file, so a partitioned table can end up with older files under an older schema footer sitting alongside newer ones; a query engine (or a table format like Iceberg or Delta Lake built for schema evolution) has to reconcile that at read time, and adding a nullable column works cleanly here because older files simply return null for it. Because of this, enforcement should not live only at one point: check it at ingestion (reject or quarantine malformed records), at the processing layer (the CI check above, run against the schema registry or the dbt/SQL model definition), and at the serving layer (the API or dashboard reading the table should not silently accept a shape it was not built for).

Worked example

An Avro event user_signup has fields id (string), email (string), country (string, optional). A developer proposes renaming country to country_code. Under a naive field-name match this is not backward compatible: a reader on the new schema looking for country_code finds nothing in old data and has no default to fall back on. Adding "aliases": ["country"] to the country_code field restores backward compatibility, because Avro's reader can resolve data written under the old field name through the alias. A CI job that runs the registry's compatibility check (in BACKWARD mode) against this change fails before the alias is added and passes after, which is exactly strategy 3 in action.

Trade-offs and pitfalls

Full compatibility is the safest choice for a dataset with many uncoordinated consumers, but it is also the most restrictive: a genuinely breaking change (splitting one field into two) cannot be done in place under full compatibility, and needs a new field or a new schema version alongside the old one rather than an in-place mutation. Backward-only compatibility is common and less restrictive, but it assumes consumers upgrade after producers, which is not always true. The compatibility checker itself only catches structural drift; it cannot catch a field that keeps its type but silently changes meaning, which is exactly why strategy 2 (never repurpose meaning) has to be a human-reviewed rule in the contract, not something automated tooling alone can enforce.

Direct answer. Implement masking as a policy-driven layer between the warehouse and the BI (business intelligence) tool, evaluated at query time based on the requesting user's role, using format-preserving or reversible transformations rather than blank-out so aggregations still compute correctly and a small privileged group can request unmasking through an audited, separate step. Push enforcement into the database or warehouse layer wherever the platform supports it, rather than the BI-tool layer, because a database-layer control also protects anyone who queries the warehouse outside the BI tool, which a BI-tool-only control cannot.

Design constraints from the requirements

• Role-varying: the same column shows a different value depending on who's asking.
• Query-time: no separate masked copy of the table to keep in sync; masking is computed as part of query execution.
• Aggregation-safe: SUM, COUNT, AVG, and GROUP BY on masked columns must still produce correct results, which rules out simple redaction on numeric columns feeding aggregates. Redacting a salary column to NULL breaks AVG(salary); the transform has to hide the display value without disturbing the value the aggregate function actually reads.
• Reversible for a small privileged group: that group needs the real value back on request, which means format-preserving encryption or deterministic tokenization under a controlled key for that class of column, not a one-way hash.

Database layer versus BI-tool layer

Default to the database layer as the source of truth for enforcement, and treat any BI-tool-level control as a convenience on top, not a substitute, because auditability and coverage, the two properties that matter most for a PII (personally identifiable information) control, only come from the database layer regardless of which client connects.

Architecture

flowchart LR
    A[Warehouse: raw sensitive columns] --> B[Masking policy engine]
    B -->|role check at query time| C[Query result: masked or real value]
    C --> D[BI tool: dashboards, ad hoc queries]
    C --> E[Direct SQL clients]
    F[Privileged unmask request] --> G[Key/token service, audited]
    G --> B
    B --> H[Audit log: who saw what, when]

Privileged users don't hold a standing "see everything" role; they issue a scoped unmask request gated by a separate authorization check and a key or token service, and every request is logged with who, which column, which row, and when, so an auditor can answer "who saw the real value and why" without relying on the requester's own account.

Worked example. A customers table has a phone_number column. The masking policy shows analysts the last four digits with the rest replaced by X (XXX-XXX-1234); a COUNT(DISTINCT customer_id) tile still works because masking operates on the display value, not on the join key, which stays untouched. A fraud analyst with the privileged role calls the unmask function on one flagged row, which decrypts the format-preserving-encrypted value with a key scoped to that role, and the access is written to the audit log with the analyst's identity, the row's primary key, and a timestamp.

Trade-offs and pitfalls

• Column-level masking alone doesn't stop inference: filtering on unmasked quasi-identifiers together (WHERE city = 'X' AND age = 34) can narrow a masked-name row to one person; policy review has to look beyond single-column rules.
• Reversible tokenization makes key management as security-critical as the data itself; losing control of the key is equivalent to losing the masking.
• BI-tool-only masking is faster to ship but creates a false sense of security once anyone with warehouse credentials, a new hire, a data engineer debugging a pipeline, can query around it; that's the most common wrong turn in this design.

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.

This is best answered with a specific real incident, structured with situation, task, action, and result: what broke, how you noticed, what you did to fix it in the moment, and specifically what changed afterward so the same class of break cannot recur silently. Interviewers are listening for the detection method and the systemic fix, not just "we patched it and moved on."

What a strong version of this story covers

• Situation: what the schema change was, a renamed column, an added or removed field, or a changed type, and what it broke: a dashboard number, a downstream job, or a model feature.
• Detection: how you found out, ideally something better than a stakeholder complaining. Strong answers cite a signal: a failed job, an alert, a data-quality check, or, if it was a person, an honest account of what made that painful.
• Restoring compatibility: what specifically you did to unbreak it, whether rolling back the producer's schema, adding a compatibility shim such as an alias or a view mapping old to new, or fixing the consumer to handle the new shape, and why you chose that path over the alternatives.
• Prevention: the concrete change afterward, such as a CI (continuous integration) compatibility check, a registered consumer list, an alert on schema drift, or a data contract. The strongest answers name a specific mechanism, not "we communicated better."

Why the detection method matters most to the interviewer

Anyone can describe fixing a broken pipeline. What separates a senior answer is the detection story: did you find out from a monitoring signal you or your team built, or did a downstream team tell you it had already been wrong for two days? The gap between those two is the actual argument for the lineage, contracts, and CI checks covered elsewhere in this topic, so a strong story naturally reflects that the fix was not just restoring the data but making silent breaks detectable.

Worked example story skeleton

Situation: a reporting job downstream of an orders table started producing negative revenue on a subset of rows after a colleague renamed discount_amount to discount_cents and changed its unit from dollars to cents in the same change, without updating the one dbt (a SQL-based data transformation tool) model that consumed it.
Task: restore correct numbers on the executive dashboard before the next morning's review, and figure out why nobody caught this before it shipped.
Action: noticed the anomaly because a downstream data-quality check flagged revenue going negative, which under the business logic should never happen; traced it through the model's SQL to the renamed, rescaled column; reverted the producer's column name and unit as an immediate fix, then worked with the producing team to make the rename properly, dual-writing both the old and new column names for a transition window, with the unit clearly named in the new column (discount_cents) to avoid the "same name, silently different unit" trap.
Result: the dashboard numbers were restored the same day. Afterward, the team added an automated CI schema-diff check on that table's producing pipeline that flags renamed or retyped columns and requires an explicit sign-off before merge, plus a naming convention requiring a unit suffix on any monetary column, adopted team-wide.

Trade-offs and pitfalls

The weak version of this story stops at "we fixed the data and told the team to communicate better," which is not a systemic fix and interviewers will probe it. The other pitfall is picking a story where the detection was purely someone complaining; if that is the true story, still tell it, but be honest that the gap was a missing automated check, and make the "what changed afterward" part name that check specifically.