Governance & Cost — Lineage, PII, Attribution

Review boundary — 10 October 2026: The lineage, access-policy and money-allocation fixtures below execute locally without services. OpenLineage and Snowflake examples are source-reviewed references. This is engineering instruction, not legal advice, a compliance certification or an actual erasure/finance audit.

Learning goal: Trace table and column dependencies, classify and enforce access separately, reconcile a tagged cost report, and design a verifiable data lifecycle.

Why this session exists

“Where did this number come from?”, “where can this customer's data flow?” and “which work accounts for the bill?” require evidence, not a list of tables. Governance supplies ownership, classification, lineage, policy and lifecycle decisions. Cost attribution supplies a reconciled account of direct, shared and unallocated spend. These support compliance work but do not by themselves make a company compliant with GDPR, HIPAA or SOC 2.

You will be able to distinguish table/column lineage and extract a dbt dependency graph; classify sensitive fields and enforce row/column access; roll up tagged spend with explicit gaps; choose storage tiers by access/recovery requirements; and draft creation→classification→retention→deletion controls tied to a request queue.

Prerequisites

Understand snapshots/retained files and DQ results. HTTP helps consume metadata APIs, but not all governance interfaces are REST. Identity and authorisation are prerequisites for real enforcement, even when a local fixture receives a role argument.

Optional pre-reading — original resource pointers. Video labels below identify the earlier suggestions, not verified recordings or viewing times. Search-result links do not establish authorship; no video content is endorsed here. Choose what helps before the intuition section; these are not required to finish the exercises.

• Video search: Data Governance in 10 Minutes.
• Video search: OpenLineage + Marquez · End-to-End.
• Video search: Cost Attribution with Query Tags.
• Reading: OpenLineage · Docs — use the job/run/dataset model alongside the artifact-to-lineage exercise; integration and column-facet coverage must be checked separately.
• Reading: GDPR Article 17 — Right to Erasure — an optional text pointer; the lifecycle section works through grounds, exceptions and verification, not unconditional deletion.
• Reading: Cost Per Query in Snowflake — compare with the lesson's direct/shared/unallocated reconciliation; query tags alone are not a complete cost-governance system.

(a) Intuition · 5 min

Think of a library: a catalog lists books and owners, lineage shows how editions and extracts were derived, classification marks restricted material, and access controls decide who can read it. A utility bill is a separate accounting system. A floor label does not meter electricity, and a PII tag does not block a query.

Lineage is not an access log. “Dataset A contributed column X to B” differs from “person Y read B yesterday.” Both matter. A cost query tag names intended ownership, but user-supplied tags can be missing, malformed or misleading; enforce and reconcile their provenance instead of treating them as authentication.

The four things governance actually delivers

Lineage supports provenance and change impact; classification plus lineage supports sensitive-data discovery; lifecycle records support authorised retention/erasure workflows; metering plus allocation policy supports spend attribution. None is automatically complete. Dynamic SQL, UDFs, extracts, notebooks, external processors and copied files can escape instrumentation. Mark a missing edge unknown, not “no data flowed.”

Table lineage is useful for broad impact. Column lineage narrows transformations, renames and sensitive fields, but it still does not identify which rows belong to one person. An erasure workflow also needs subject identity/alias resolution, data-store adapters and verification. An aggregation may or may not remain personal data; automatic taint propagation is a conservative classification rule, not a legal conclusion.

How the industry got here

The durable progression is from manually maintained inventories to automated catalogs, runtime lineage events, producer contracts and enforceable policy. OpenLineage standardises jobs, runs and datasets plus facets; support for a facet does not prove every integration emits complete column lineage. GDPR's storage-limitation and erasure rules are obligations with conditions/exceptions, not a universal 90-day retention rule or a law applying merely because a company has US customers. The original unsupported fines, market history and product claims are not retained as facts.

Lineage as a graph

The email rename does not break a correctly recorded edge. The ID path matters too: removing one email field may leave a linkable customer identifier. The graph produces candidate locations to investigate, not proof that every row in each destination concerns the subject or must be deleted.

The governance stack

A catalog records schema, owner, sensitivity and purpose. Lineage records transformations at table/column level; access history records reads/writes. Classification combines producer declarations, scanning and steward review. Policy enforcement belongs at the actual database/API/export boundary with deny-by-default behaviour. Cost attribution joins metering, usage, trustworthy tags and rate-card/allocation rules. Lifecycle services track retention, legal holds, requests, replicas, backups and proof of completed actions.

Cost attribution: two-step pattern

Tag work at the driver/job boundary, then roll up measured cost—but those two steps are not the whole invoice. Record team, pipeline, environment and job/run identity with a documented schema. Validate tags, keep an explicit unallocated bucket, reconcile against metering and finally against the contractual bill including adjustments, discounts and timing.

Snowflake QUERY_ATTRIBUTION_HISTORY.CREDITS_ATTRIBUTED_COMPUTE attributes query-execution compute and excludes warehouse idle time; query acceleration is separate. WAREHOUSE_METERING_HISTORY includes compute/cloud-services measurements, and its total is not necessarily billed credits because cloud-services adjustments are separate. These views have latency. Summing elapsed query time double-counts overlap and ignores resource weighting; it is not exact chargeback.

Reference SQL, not run on Snowflake:

alter session set QUERY_TAG = 'team=ml/pipeline=lifetime_value/env=prod';
 
select coalesce(q.query_tag, 'UNALLOCATED') as tag,
       sum(a.credits_attributed_compute) as query_compute_credits,
       sum(coalesce(a.credits_used_query_acceleration, 0)) as acceleration_credits
from snowflake.account_usage.query_attribution_history a
left join snowflake.account_usage.query_history q on q.query_id = a.query_id
where a.start_time >= '2026-09-01'::timestamp_tz
  and a.start_time < '2026-10-01'::timestamp_tz
group by 1;

This reports raw tags, not validated teams or a reconciled invoice. A production report must define timezone/cross-period queries, view completeness, missing tags, shared idle compute, storage/transfer/serverless services and rates. Showback informs owners; chargeback requires agreement on allocation and adjustments. A budget alert is not a hard spending cap. Timeouts, size/concurrency limits, auto-suspend and resource monitors cover different cost sources and can interrupt legitimate work.

Governance modes: catalog-first vs producer-first

Catalog-first discovery helps a legacy estate but requires permissions and can lag or misclassify. Producer-first contracts expose owner/schema/tags at creation, but declarations can be wrong and propagation must be implemented. A hybrid inventories existing data and enforces critical new paths, with reviewed gaps rather than a claim of complete coverage on day one.

A catalog UI, API and request queue are useful starting components, not a sufficient compliance platform. The queue needs identity verification, legal/purpose assessment, approved actions, idempotent adapters, retries, evidence and terminal status. Governance is operational metadata plus enforcement, not paperwork alone.

Deriving the cost-growth mechanism

Suppose each of n teams creates one derived dataset for every other team. That hypothetical directed dependency pattern has n(n-1) edges: 3 teams yield 6, 10 yield 90. It demonstrates a possible combinatorial workload, not a law that spend grows quadratically with headcount. Real reuse, schedules and workloads can be sparse or centrally consolidated.

If schedules outlive their consumers and nobody owns deprecation risk, recurring spend can persist unnoticed. Ownership and lineage make it possible to identify redundant work; query optimisation lowers the cost of retained work. Neither replaces the other. Enforce creation metadata, review stale usage with retention requirements, and require an owner-approved deprecation window rather than deleting everything not read for ninety days.

-- SQLite fixture tables: inventory(name,owner), access_log(name,access_day).
-- Include never-read inventory entries, which access history alone omits.
select i.name, i.owner, max(a.access_day) as last_seen
from inventory i left join access_log a on a.name = i.name
group by i.name, i.owner
having max(a.access_day) is null or max(a.access_day) < '2026-07-10';

These are review candidates, not deletion authorisation; access logs may be incomplete and rarely used disaster-recovery data may be essential.

Every dataset has an owner

Use a maintained team/on-call alias with an accountable escalation path, not only a person who may leave. Classification must propagate through newly added edges and tags applied after the graph exists. Cycles require a terminating fixed-point algorithm. A single pass at edge creation misses late tags and out-of-order dependency registration. Declassification after masking/aggregation needs an explicit reviewed transformation rule rather than automatic removal of sensitivity.

Centralised, federated, or shared standards

Central review provides consistency but can bottleneck domain work. Federation gives domains context and autonomy but can fragment definitions. A useful hybrid centralises minimum interface/policy rules and federates implementation/ownership. Enforceable contracts, consumer tests and evidence matter; neither a central team nor a wiki guarantees compliance. Choose based on organisational constraints, not a mandatory architecture for every company size.

(c) Hands-on · 25 min

Column lineage, policy and reconciled spend

This exact standard-library program uses a graph with late/out-of-order edges, conservative tag propagation and decimal money. It performs no erasure and authenticates nobody. It computes candidate locations and policy decisions for supplied test identities; the production boundary must derive trusted identity attributes itself.

# exact-program: governance_model
from collections import defaultdict, deque
from decimal import Decimal
 
EDGES = {
    ('stg.email', 'fact.email'), ('fact.email', 'dash.email'),
    ('raw.email', 'stg.email'), ('raw.id', 'stg.id'),
}
 
def propagate(edges, initial):
    tags = defaultdict(set, {k: set(v) for k, v in initial.items()})
    changed = True
    while changed:
        changed = False
        for source, target in sorted(edges):
            before = len(tags[target])
            tags[target].update(tags[source])
            changed |= len(tags[target]) != before
    return dict(tags)
 
 
def affected_columns(edges, roots):
    graph = defaultdict(set)
    for source, target in edges:
        graph[source].add(target)
    seen, todo = set(roots), deque(roots)
    while todo:
        for target in graph[todo.popleft()]:
            if target not in seen:
                seen.add(target); todo.append(target)
    return sorted(seen)
 
 
def can_read(tags, region, role):
    if role not in {'analyst', 'support', 'admin'}:
        return False
    if 'eu_only' in tags and region != 'EU':
        return False  # admin is not a residency bypass
    if 'pii.email' in tags and role not in {'support', 'admin'}:
        return False
    return True
 
 
def visible_row(row, allowed_region):
    return row.get('region') == allowed_region  # separate row policy
 
 
def team(tag):
    try:
        pairs = [part.split('=', 1) for part in tag.split('/')]
        if any(len(p) != 2 or not p[0] or not p[1] for p in pairs):
            return 'UNALLOCATED'
        if len({k for k, _ in pairs}) != len(pairs):
            return 'UNALLOCATED'
        return dict(pairs).get('team') or 'UNALLOCATED'
    except AttributeError:
        return 'UNALLOCATED'
 
 
def allocate(runs, bill):
    totals = defaultdict(lambda: Decimal('0'))
    for tag, credits, rate in runs:
        totals[team(tag)] += Decimal(credits) * Decimal(rate)
    residual = Decimal(bill) - sum(totals.values())
    if residual < 0:
        raise ValueError('attribution exceeds bill: investigate periods/overlap/rates')
    totals['SHARED_UNALLOCATED'] += residual
    assert sum(totals.values()) == Decimal(bill)
    return dict(totals)
 
if __name__ == '__main__':
    tags = propagate(EDGES, {'raw.email': {'pii.email', 'eu_only'}})
    columns = affected_columns(EDGES, {'raw.email'})
    assert columns == ['dash.email', 'fact.email', 'raw.email', 'stg.email']
    assert all(tags[c] == {'pii.email', 'eu_only'} for c in columns)
    assert not can_read(tags['dash.email'], 'US', 'admin')
    assert can_read(tags['dash.email'], 'EU', 'support')
    assert not visible_row({'region': 'US'}, 'EU')
    runs = [('team=ml/pipeline=ltv', '1200', '3'),
            ('team=ml/pipeline=fraud', '450', '3'),
            ('team=analytics/pipeline=exec', '280', '3'),
            ('team=marketing/pipeline=daily', '90', '3'),
            ('team=platform/pipeline=metadata', '40', '3'),
            (None, '10', '3')]
    costs = allocate(runs, '6500')
    assert costs == {'ml': Decimal('4950'), 'analytics': Decimal('840'),
                     'marketing': Decimal('270'), 'platform': Decimal('120'),
                     'UNALLOCATED': Decimal('30'), 'SHARED_UNALLOCATED': Decimal('290')}
    print(columns, costs)

Anatomy of the script

Four email-lineage columns are discovered, not the original incorrect claim of five. Name/ID fields can form additional sensitive paths. propagate iterates until no finite tag set changes, so late tags and cycles converge. affected_columns visits each reachable column once and returns candidates, not a subject-specific row list. can_read denies unknown roles and does not let admin bypass residency. Row filtering is a distinct function, rather than mislabelling a column/role check “row-level security.”

At the assumed three-dollar rate, ML receives 4,950, analytics 840, marketing 270, platform 120 and missing tags 30. A 6,500 source total leaves 290 explicitly shared/unallocated. These reconcile exactly; they are not real Snowflake prices or an invoice. Negative residuals fail instead of hiding a double count. Decimal arithmetic avoids binary floating-point drift, while contractual currency rounding remains a separate policy.

Policy completeness and cost denominators

The core above assumes a finite graph, known classification vocabulary, trusted identity attributes and nonnegative finite decimal inputs. can_read checks only the two named restrictions; an empty tag set is not evidence that a field is public, and an unknown tag is not automatically enforced. visible_row is a separate equality predicate, not an authenticator. Apply both column and row decisions, reject unknown classification or missing identity, and record the policy version used. The following wrapper makes these local fixture assumptions explicit; the Boolean review flag must come from a trusted catalog, never a request parameter.

# exact-function: reviewed_access (after governance_model)
def reviewed_access(tags, row, region, role, classification_reviewed):
    if (classification_reviewed is not True or not isinstance(tags, set) or
            not tags <= {'public', 'pii.email', 'eu_only'} or not tags or
            region not in {'EU', 'US'} or not isinstance(row, dict)):
        return False
    return can_read(tags, region, role) and visible_row(row, region)

A reviewed public field still needs its row-region permission. EU support can read a reviewed EU email row; US admin cannot override eu_only; an unreviewed empty tag set and a new unimplemented financial classification both deny. This is an access-decision model, not a real residency guarantee: service location, exports, storage, purpose and transfer rules need separate enforcement.

For the spend fixture, tagged named teams total 6,180; measured direct spend including missing tags totals 6,210; the declared source bill is 6,500. Therefore named attribution covers 6180 / 6500 ≈ 95.08% of the bill, not 6180 / 6210 ≈ 99.52%. The latter answers a different question: completeness within measured direct spend. The gap is 30 missing-tag direct spend plus 290 residual. The identity balances, but reconciliation alone does not tell us whether the residual is idle compute, missing ingestion, rate differences or adjustments. Do not allocate it to a team without an approved rule.

This input wrapper preserves the exact core while rejecting NaN/infinity and negative usage/rates. Unknown, malformed or reserved-bucket query tags remain UNALLOCATED; a team registry containing a reserved accounting name is rejected. It is a deliberately nonnegative invoice model; credits/refunds must be separate signed adjustment records in a real ledger, not silently passed as negative usage.

# exact-function: checked_costs (after governance_model)
def checked_costs(runs, bill, known_teams):
    reserved = {'UNALLOCATED', 'SHARED_UNALLOCATED'}
    if (not isinstance(known_teams, set) or known_teams & reserved or
            any(not isinstance(t, str) or not t for t in known_teams)):
        raise ValueError('invalid team registry')
    bill = Decimal(bill)
    if not bill.is_finite() or bill < 0:
        raise ValueError('invalid source total')
    checked = []
    for tag, credits, rate in runs:
        credits, rate = Decimal(credits), Decimal(rate)
        if not credits.is_finite() or not rate.is_finite() or credits < 0 or rate < 0:
            raise ValueError('invalid usage or rate')
        name = team(tag)
        valid_tag = tag if name in known_teams else None
        checked.append((valid_tag, credits, rate))
    totals = allocate(checked, bill)
    named = sum((totals.get(t, Decimal(0)) for t in known_teams), Decimal(0))
    coverage = named / bill if bill else None  # zero total: undefined, not 100%
    return totals, coverage

Worked finance question: if the bill rose from 5,000 to 6,500, that is a 30% increase, using the earlier bill as denominator. ML's 4,950 is a current share, not proof ML caused the increase; compare its earlier matched-period amount. Missing query-attribution rows (including short queries excluded by the captured vendor view) and delayed data remain coverage gaps. Freeze the reporting timezone, period boundaries, metering completeness and rate version before comparing periods. Currency rounding and allocation of shared services need explicit finance approval.

From dbt artifacts to OpenLineage

manifest.json includes dbt nodes, sources and depends_on.nodes. Extracting those produces table/model dependencies, not inferred column transformations. compiled_code alone is not a universal SQL lineage parser. An adapter/integration can emit observed run events and column-lineage facets where supported. Validate namespace/name identity, run IDs and transformation coverage before trusting the graph.

# exact-function: dbt_dependency_edges (artifact inspection only)
def dbt_dependency_edges(manifest):
    resources = {**manifest.get('nodes', {}), **manifest.get('sources', {})}
    edges = []
    for target, node in resources.items():
        for source in node.get('depends_on', {}).get('nodes', []):
            if source not in resources:
                raise ValueError('dependency absent from captured artifact')
            edges.append((source, target))
    return sorted(edges)

For a column facet, the important relationship is output field → inputFields, each identifying namespace, dataset and field. Example fragment, not a complete sent RunEvent:

{
  "fields": {
    "email_lower": {
      "inputFields": [
        {"namespace": "warehouse", "name": "raw.customers", "field": "email"}
      ]
    }
  }
}

A complete event also needs the schema-defined producer/schema URL, event time/type, job, run and dataset envelope. Store events with observed version and coverage; missing input fields stay unknown. No OpenLineage service or actual dbt project graph was executed here.

Residency, classification and access exercise

Apply eu_only to a source after all edges exist, rerun propagation, then test US analyst, US admin and EU support. Expected decisions are deny, deny and allow for the sensitive email field. Now add a row-region filter; being allowed to read a column must not expose another region's rows. In real systems use authenticated identity, least privilege, purpose rules and database-enforced policies across all query/export paths. Tags are policy inputs, not permission grants by themselves.

(d) Production reality · 15 min

Data lifecycle and erasure are workflows

The original text incorrectly treated pseudonymisation as removal from GDPR and asserted a general ninety-day maximum. GDPR Article 5(1)(e) ties identifiable storage to necessary purposes, with specified safeguards/exceptions; Article 17 has erasure grounds and exceptions, including relevant legal obligations. Pseudonymised but reidentifiable data remains personal data. A hash or dropped email is not automatically anonymisation. Legal review sets applicability, purpose, lawful basis and actual retention duties.

A defensible engineering workflow:

1. Verify the requester and map subject IDs/aliases without exposing them in logs.
2. Inventory live tables, history, replicas, caches, search/feature stores, exports and processors; record unknown coverage and owner acknowledgements.
3. Assess applicable erasure grounds, retention duties and legal holds. Separate authorised actions from held records and explain exceptions through the approved process.
4. Execute idempotent per-store deletion/redaction/retention actions with request identity, checkpoints and retries. Prevent re-ingestion from resurrecting erased data under an appropriate suppression design.
5. Handle backups with an approved access/expiry/restore-suppression policy. Do not blindly disable table retention safeguards or destroy required recovery/legal evidence.
6. Verify outcomes and retain minimal audit proof, including incomplete/held actions. A queue item is complete only when its declared scope has been reconciled.

Crypto-shredding destroys keys to make ciphertext inaccessible, but copies, key backups, derived plaintext, granularity and recoverability matter. It is not automatically acceptable erasure for every circumstance. Ask the responsible legal/security owner to approve the design rather than claiming general regulatory acceptance.

Hot, warm and cold are access/recovery choices

Hot serves frequent low-latency access; warm trades some access convenience for cost; cold/archive can impose retrieval delay, request charges and minimum-duration rules. These are workload labels, not a rule that every warehouse is hot or every object store warm. Compute total storage plus retrieval, early-deletion and restore costs. Keep frequently read 100-GB data accessible; a rarely restored compliance archive may fit colder storage if recovery and retention requirements allow it.

Failure analyses and finance review

No company settlement, invented internal platform or unexpected-bill anecdote is asserted. Missing lineage makes impact/erasure scope uncertain; untagged expensive queries create an attribution gap; deleting only the primary row leaves copies/history. Mitigate with measured instrumentation coverage, trusted ownership tags, limits and verified lifecycle adapters—not a promise that one graph traversal solves regulation.

For an illustrative 40% bill increase, produce matched-period reconciled totals, direct query/service contributors, team deltas including unallocated/shared buckets, warehouse sizing/idle/concurrency evidence, and expensive-query recommendations tied to plans. Distinguish price changes from usage changes and metering from billed adjustments. A long duration alone is not an exact per-query cost.

Where this shows up in the rest of the plan

Snapshots identify inspected inputs; DQ supplies quality evidence; authorisation governs actual access; ML feature/training pipelines need their own lineage and purpose controls. Capstone deliverables should include an evidence-backed ownership/cost/lifecycle account, not merely an attractive graph.

(e) Recall + stretch · 10 min

Failure exercise and worked answer

A shared warehouse is idle half the day: query runtime is not the bill. Attribute measured query work, account explicitly for idle/shared services, apply approved rates/adjustments, and reconcile the residual. The local 6,500 fixture demonstrates the accounting identity without fabricating a vendor invoice.

Explain-out-loud test

Explain catalog versus lineage versus access history, trace the four email columns, deny the US admin under the residency rule, and reconcile direct plus unallocated spend.

What comes next

Next: S055 · HTTP Fundamentals — verbs, status codes, headers, caching. HTTP and API contracts expose many of these controls to clients, but an API label does not supply governance. Hub: The 6-Month Learning Plan.

Study route: intuition → visual walkthrough → hands-on → production failure analysis → recall. Use the section times as planning cues, not a completion guarantee; pause to work through the checks. Optional pre-reading is separate.

Sources and review boundary

OpenLineage object model, column lineage facet, Snowflake query attribution, warehouse metering, GDPR as adopted, official legislation.gov.uk reproduction, OWASP authorisation. The inherited evidence capture's direct EUR-Lex fetch returned empty bytes. The accepted legal source is the official UK legislation host’s reproduction of the EU regulation as adopted, not its currently amended UK GDPR text; the batch evidence records that identity and limitation. No actual user data, cloud account, access-policy deployment, deletion request or invoice was processed.