Data Governance, Contracts, and Classification Questions

Direct answer

Build one central catalog and lineage graph fed by automated harvesters from each source system (the lake, the warehouse, and the ML artifact store), with access control and sensitivity tags attached at the dataset and column level as first-class catalog fields rather than a bolt-on, and retention and deletion policy driven off those same tags so a classification change propagates its consequences automatically. Treat the whole thing as a governance program, not just infrastructure: track adoption (searches, dataset views, and completed access requests through the catalog) so you can tell whether teams are actually using it versus tolerating its existence.

Structured elaboration

Architecture. A metadata harvester runs against each source (lake file listings and table formats, warehouse system tables, the ML artifact store's model and dataset registry), pushing technical metadata (schema, size, last-modified, detected lineage from query and job logs) into a central catalog. The catalog stores this alongside curated business metadata and exposes both through one search surface, regardless of where the underlying data physically lives.

Automatic versus hand-curated metadata. Anything mechanically derivable is automated: schema, row counts, freshness, and lineage edges reconstructed from SQL and pipeline job graphs. Anything requiring business judgment is curated by the dataset's owner: what the dataset means, who to contact, and the sensitivity classification of each field. Automation without curation gives you an accurate but meaningless inventory; curation without automation gives you documentation that goes stale within a quarter. The system needs both, with automation covering the majority so curation effort concentrates on the judgment calls.

Access controls and sensitivity tags. Every dataset and, where it matters, every column, carries a sensitivity tag (for example public, internal, confidential, restricted). Access control policy is defined against the tag, not against the individual dataset, so a newly classified table inherits the correct policy immediately instead of waiting for someone to configure access rules by hand. When a table is derived by joining a confidential source with a public one, the derived table inherits the higher (more restrictive) classification by default.

Retention and deletion hook. The same sensitivity tag (plus a data-category tag, such as "contains personally identifiable information," PII) drives a retention policy: a restricted-and-PII dataset gets a shorter, enforced retention window with automated deletion, while an internal-analytics-only dataset follows a longer default. This only works because the tag lives in the catalog and the retention job reads it from there, rather than retention rules being hand-maintained per dataset.

Automated-lineage-plus-RBAC-plus-quality-enforcement as a rollout. Practically, this ships in that order: get automated lineage and cataloging working first (so there is something to attach policy to), then layer access control enforcement on top of the now-populated catalog, then add automated data-quality checks that block a dataset from being marked "production-ready" in the catalog until they pass. Rolling out RBAC before the catalog exists means access decisions have nothing reliable to key off of.

The same system as a governance program (metric consistency, discoverability, ownership). Beyond the technical architecture, this is also a program with three organizational goals: metric consistency (the same business number is defined once, not recomputed differently by five teams), discoverability (someone new to the org can find the right dataset without asking around), and ownership (every dataset has a named, accountable owner, not an implicit one). The catalog is the shared surface those three goals get measured against.

flowchart TD
    A[Data lake] --> M[Metadata harvesters]
    B[Warehouse] --> M
    C[ML artifact store] --> M
    M --> D[Central catalog + lineage graph]
    D --> E[Access control / sensitivity tags]
    D --> F[Retention and deletion policy hook]
    D --> G[Curated business metadata]
    D --> H[Adoption metrics]

Measuring adoption versus tolerance. Track catalog searches per week, the fraction of active datasets with a confirmed (not default) owner, and the fraction of access requests that go through the catalog's workflow versus a side channel (a Slack message to someone who "just has access"). A catalog that exists but where most access still happens outside it is being tolerated, not adopted.

Trade-offs & pitfalls

The harvesters covering three different source types (lake, warehouse, ML artifact store) each need their own integration, and keeping all three in sync in near-real time is genuinely hard; a common failure is one source's harvester silently falling behind, so the catalog looks complete but is quietly stale for that source. Deriving a joined table's classification as "the more restrictive of its inputs" is the right default but is not always correct (an aggregation can sometimes de-sensitize data, for example, a table of counts by region does not carry the same restriction as the row-level source), so the automatic inheritance rule needs a documented, reviewable override path. Measuring adoption by search volume alone can be gamed or misleading, a team using the catalog to browse but still pulling data through an old side channel looks adopted but is not; the side-channel-usage metric is the one that actually tells you if you have won.

Direct answer

Treat this as three separate problems in sequence: automate discovery of what lineage can be inferred from existing artifacts (query logs, pipeline code, warehouse metadata), route the gaps that automation cannot resolve to manual validation by the people who actually know each table, and then close the loop by making lineage capture a byproduct of how new pipelines get built, not a document someone maintains by hand.

Structured elaboration

What to automate (discovery):

• Parse SQL and pipeline job definitions (dbt models, scheduled queries, ETL/ELT job configs) to reconstruct table-to-table lineage; most warehouses and orchestration tools expose enough structure for this without touching the tables themselves.
• Mine query logs for actual read and write patterns over a trailing window (who queries this table, what does it feed) to surface real usage even where no formal pipeline exists.
• Auto-populate technical metadata (schema, row counts, last-modified, owner inferred from write access patterns) since this is mechanically derivable and does not require a human judgment call.

Where manual validation is still needed:

• Business meaning: automated lineage tells you table A feeds table B, not why, or whether B is still actually used for anything that matters. A human needs to confirm ownership and whether a table is safe to deprecate.
• Ambiguous or contested ownership: when query logs show five teams reading a table and no clear owner writing it, that is a people problem, not a data problem, and needs a named decision, not an algorithm.
• Anything predating the log retention window: lineage for a table last touched two years ago, outside your query-log retention, cannot be reconstructed automatically and has to be validated (or explicitly marked unknown) by hand.

Keeping it from going stale again:

• Make lineage capture automatic at write time for anything new: any pipeline built going forward runs through tooling (dbt, an orchestrator with lineage hooks) that emits lineage as a side effect of deployment, not as a separate documentation task.
• Add a lightweight ownership and staleness check to CI or scheduled review: a table with no confirmed owner after a set period gets flagged, not silently left blank.
• Treat the lineage graph itself as a monitored asset: alert when a new table appears with no discoverable lineage edges, the same way you would alert on a broken pipeline.

Worked example

Start with 400 undocumented tables. Automated SQL and query-log parsing reconstructs lineage edges for roughly 300 of them (tables with active dbt models or a clear write-then-read pattern in the last 90 days of logs), because the structure to infer it mechanically exists. The remaining 100 split into two groups: 60 have some query activity but no clear owner (ambiguous, need a person to claim or deprecate them) and 40 have no query activity at all in the log window (candidates for archival, but need a human sign-off before deletion since "no recent queries" is not proof "no downstream dependency"). The 60 and 40 go into a manual validation backlog, triaged by business criticality rather than attempted all at once. Going forward, any new table created through the standard pipeline tooling gets lineage captured automatically, so the backlog only shrinks, it does not get replenished by new undocumented tables.

Trade-offs & pitfalls

Automated lineage from SQL parsing is only as good as the SQL is parseable, dynamic SQL, stored procedures, and BI-tool-generated queries commonly break naive parsers and produce false negatives that look like "no lineage" rather than "lineage the tool could not see." Trying to manually validate everything at once instead of triaging by criticality burns effort on tables nobody depends on while high-risk tables wait. The most common way this goes stale again is treating the initial discovery project as a one-time cleanup rather than changing how new pipelines get built, six months later the same gap reappears for every table created after the project closed.

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.

Table-level lineage from SQL is a two-step problem: parse each script to find its output table and the tables it reads from, then treat those as edges in a directed graph and run a topological sort to get a valid build order (and to catch cycles, which should never exist in a real DAG but silently corrupt an unvalidated graph).

Approach

1. Extract edges per script. For a script that writes one output table (the common CREATE TABLE x AS SELECT ... shape), regex- or AST-parse for the target table name and every table referenced after FROM or JOIN. A real parser should use a proper SQL AST library (like sqlglot in Python) rather than regex once you have to handle subqueries and CTEs, but the graph-construction and topo-sort logic below is identical either way.
2. Build the dependency graph as output_table -> {input_tables}.
3. Topologically sort using Kahn's algorithm: repeatedly remove nodes with no remaining unresolved dependencies. If nodes remain when the queue empties, there's a cycle.

Worked example (executed)

import re
from collections import defaultdict, deque

def parse_sql_lineage(sql_scripts: dict[str, str]) -> dict[str, set[str]]:
    edges = {}
    create_re = re.compile(r'CREATE\s+(?:OR\s+REPLACE\s+)?TABLE\s+([a-zA-Z0-9_.]+)\s+AS', re.IGNORECASE)
    from_join_re = re.compile(r'\b(?:FROM|JOIN)\s+([a-zA-Z0-9_.]+)', re.IGNORECASE)
    for fname, sql in sql_scripts.items():
        m = create_re.search(sql)
        if not m:
            continue
        output_table = m.group(1).lower()
        inputs = {t.lower() for t in from_join_re.findall(sql)} - {output_table}
        edges.setdefault(output_table, set()).update(inputs)
    return edges

def detect_cycle_and_topo_order(edges: dict[str, set[str]]):
    all_nodes = set(edges.keys())
    for deps in edges.values():
        all_nodes.update(deps)
    indegree = {n: 0 for n in all_nodes}
    adj = defaultdict(set)
    for table, deps in edges.items():
        for dep in deps:
            adj[dep].add(table)
            indegree[table] += 1
    queue = deque(sorted(n for n in all_nodes if indegree[n] == 0))
    order = []
    while queue:
        node = queue.popleft()
        order.append(node)
        for nxt in sorted(adj[node]):
            indegree[nxt] -= 1
            if indegree[nxt] == 0:
                queue.append(nxt)
    has_cycle = len(order) != len(all_nodes)
    return has_cycle, (None if has_cycle else order)

Run against a small 2-branch pipeline (two independent staging tables that merge into a fact table, which then feeds an aggregate):

scripts = {
    'stg_orders.sql': "CREATE TABLE stg_orders AS SELECT * FROM raw.orders",
    'stg_customers.sql': "CREATE TABLE stg_customers AS SELECT * FROM raw.customers",
    'fct_orders.sql': "CREATE TABLE fct_orders AS SELECT o.*, c.region FROM stg_orders o JOIN stg_customers c ON o.customer_id = c.id",
    'agg_region_revenue.sql': "CREATE TABLE agg_region_revenue AS SELECT region, SUM(amount) FROM fct_orders GROUP BY region",
}
edges = parse_sql_lineage(scripts)
has_cycle, order = detect_cycle_and_topo_order(edges)

Result: edges['fct_orders'] == {'stg_orders', 'stg_customers'}, has_cycle == False, and order == ['raw.customers', 'raw.orders', 'stg_customers', 'stg_orders', 'fct_orders', 'agg_region_revenue'] (the two raw sources appear before anything that depends on them, and fct_orders before the aggregate that depends on it). A deliberately cyclic graph (a depends on b, b on c, c on a) correctly returns has_cycle == True and order is None.

Reconciling static parsing with runtime accuracy

Pure static parsing like the above breaks on dynamic SQL (a table name built from a variable), views referenced indirectly, and opaque UDFs. In production you'd combine it with runtime instrumentation: capture the actual tables a query engine touched during execution (Spark's query plan, or a query-log audit table) and reconcile the two, flagging any static edge the runtime never confirmed (probably dead code or a conditional branch that never fires) and any runtime edge the static parse missed (probably dynamic SQL) for manual review.

Trade-offs and pitfalls

A regex parser is fast to write but silently misclassifies anything it doesn't expect (a CTE, a subquery in the FROM clause, a table name with an unusual character). It will confidently produce a WRONG graph rather than failing loudly, which is worse than not having lineage at all if people start trusting it. A production system should use a real SQL parser (sqlglot, sqlparse, or the query engine's own parse-plan output) and treat any script the parser can't confidently handle as a flagged gap, not a silent skip.

Direct answer. Build the RACI (a framework naming who is Responsible for doing the work, Accountable for the outcome, Consulted before a decision, and Informed after) around one principle: the team closest to the data's origin owns correctness of the data itself, the central data platform team owns the shared infrastructure and cross-cutting standards, and downstream consumers are consulted on anything that could break their use case and informed of everything else. Escalation when nobody agrees who's Accountable needs a standing tie-breaker defined in advance, not an ad hoc argument each time it comes up.

RACI across the five activities

Who typically sits in each role: the producer team is usually the engineering team owning the source system generating the data, for example the checkout service team for order events; the central data platform team runs the warehouse, catalog, schema registry, and access-control tooling; downstream consumers are analytics, BI (business intelligence), or ML (machine learning) teams building on top of the data.

Escalation when nobody agrees who's Accountable. The RACI matrix has to be paired with a named, standing tie-breaker before a dispute happens, not negotiated during one. A practical pattern: the central data platform team is the default Accountable owner for any activity the matrix doesn't clearly assign, until a specific producer team formally takes ownership through onboarding. Disputes escalate to a defined forum, a data governance council or the platform team's engineering manager together with the producer's engineering manager, with a stated response-time expectation, so "who owns this" doesn't sit unresolved while an incident is active.

Absorbed example: dashboard-specific RACI

• Metric definition (what "active user" or "revenue" means): Accountable sits with the business or product owner who requested the metric, Responsible is the BI analyst who implements the formal definition, Consulted includes finance or analytics leadership for anything feeding external reporting, Informed is everyone using the dashboard.
• Dataset changes (a schema or source change the dashboard depends on): Responsible is the producer or data engineering team making the change, Accountable is the central data platform team approving it against compatibility rules, Consulted is the dashboard's BI analyst for an impact check before the change ships, Informed is the dashboard's end users.
• Visual design: Responsible is the BI analyst building the dashboard, Accountable is the BI analyst's team lead or a style-guide owner, Consulted is the requesting business stakeholder, Informed is end users after publish.
• Deployment: Responsible is the BI analyst as publisher, Accountable is the BI platform admin controlling promotion to production workspaces, Consulted is the data platform team if new sources are involved, Informed is the stakeholder distribution list.
• Maintenance, ongoing accuracy, freshness, retirement candidacy: Responsible is the BI analyst who owns the dashboard, Accountable is that analyst's manager or a BI governance lead, Consulted is downstream users when a change affects them, Informed is everyone else.

Trade-offs and pitfalls

• A RACI with more than one party marked Accountable per row is the most common authoring mistake and defeats the point; Accountable must be exactly one name or role per activity, everything else can have multiple.
• A RACI matrix not revisited after a reorganization quietly goes stale, since "producer team" and "central platform team" are organizational roles that move people around them; review it on a cadence, not only when a dispute exposes it as outdated.
• The most common escalation failure isn't a missing RACI, it's a RACI everyone signed off on in a document nobody remembers exists during an actual incident; the escalation path needs to be discoverable in the moment, not just documented once.