S054 · Governance & Cost — Lineage, PII, Attribution

Module M05: Data Engineering · Session 54 of 130 · Track: DE 💰

What you'll be able to do after this session

• Explain data governance & cost attribution to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S053 — Data Quality — Freshness, Volume, Schema, Distribution

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · What is Data Governance? — IBM Technology — 8-min whiteboard covering the whole scope in plain English.
• 🎥 Deep dive (30–60 min) · Data Governance in Practice — DataHub Project — LinkedIn engineers walk through lineage, PII tagging, and access control in a real open-source tool.
• 🎥 Hands-on demo (10–20 min) · Snowflake Cost Management in 15 minutes — the fastest way to see how cost attribution actually looks in a warehouse.
• 📖 Canonical article · GDPR — official text of Article 17 "Right to erasure" — the law that forces every data team to know where every user row lives.
• 📖 Book chapter / tutorial · DataHub docs — Governance concepts — 20-minute read on ownership, glossary, tags, domains.
• 📖 Engineering blog · Airbnb — How Airbnb built Dataportal / Amundsen — the origin story of internal data catalogs; still shapes the field.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: The unsexy work that keeps companies out of court.

Think of a giant public library with 10 million books. Governance is the librarian's answer to five questions any regulator, exec, or auditor can walk in and ask at any moment: who owns this?, who can read it?, where did it come from?, who else depends on it?, and how much did it cost me last month? If you can't answer those in seconds, you don't have a data platform — you have a landfill with wifi.

Governance splits into three practical buckets:

1. Lineage & catalog — a map of every table, its owner, its columns, and what upstream/downstream depends on it.
2. Privacy & access — knowing which columns hold PII (personal data), tagging them, and enforcing who can see what. GDPR / CCPA / HIPAA / DPDP (India) turn this from "nice to have" into "€20M fines."
3. Cost attribution — assigning cloud spend back to the team / project / query that generated it, so the CFO doesn't just see "$4M for Snowflake" but "$800k for growth, $600k for finance,$2.6M for ML training."

Real-life analogy: it's the difference between an apartment you rent alone (nobody cares who's watching TV) and a shared house of 30 people (you need chore charts, name tags on food, and a shared bill split — otherwise chaos and lawsuits). As your data platform grows past ~10 tables and ~5 users, governance is the chore chart that keeps the place liveable.

Forever gotcha: the day you need lineage is the day someone asks "does this GDPR delete-user request touch table X?" — and if you haven't been building lineage all along, you're stuck grepping SQL files for 3 weeks. Governance is a habit, not a project.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Worked example — a GDPR "delete user 12345" request:

1. Ticket lands: user 12345 exercises right-to-erasure.
2. Query the catalog: "which tables have a user_id column?" → 47 tables.
3. Filter by "contains PII tag personal_identifier" → 12 tables (the others are anonymised aggregates).
4. For each: run parametrised delete script; log the deletion.
5. Verify via lineage: are any downstream tables cached copies? → yes, 3 marts. Trigger a rebuild.
6. Reply to user within 30 days as required by GDPR Article 17.

Without a catalog + lineage, step 2 alone is a week of grepping. With them, it's a 30-minute script.

Cost attribution — how you split the bill:

The magic wand: tag everything at creation time. Untagged resources are impossible to attribute later — CFO gets a bill nobody owns, and it becomes "central IT overhead."

PII taxonomy (the four tiers everyone uses):

• Restricted: SSN, credit card, health records, passwords → encrypted at rest, tokenised, tiny access list.
• Confidential: email, phone, address, DOB → column-level access controls, masking for analysts.
• Internal: internal user IDs, purchase history → default employee access with audit.
• Public: aggregates, marketing content → anyone.

Every column in your warehouse should carry one of these tags. Tools like DataHub, Unity Catalog, and Purview auto-scan for PII patterns and suggest tags.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Build a mini catalog + lineage + PII tagger in ~60 lines of Python. This is exactly what the "expensive" tools do, just with a UI on top.

python -m venv .venv && source .venv/bin/activate
pip install duckdb sqlglot

# governance_demo.py
import duckdb, re, json, sqlglot
from collections import defaultdict
 
con = duckdb.connect(":memory:")
 
# 1. Set up two source tables with mixed sensitivity
con.execute("""
CREATE TABLE raw_users(user_id INT, email VARCHAR, ssn VARCHAR, country VARCHAR);
CREATE TABLE raw_orders(order_id INT, user_id INT, amount DECIMAL(10,2));
INSERT INTO raw_users VALUES (1,'a@x.com','111-22-3333','US'),(2,'b@x.com','222-33-4444','IN');
INSERT INTO raw_orders VALUES (100,1,25.50),(101,2,99.90);
""")
 
# 2. Define transformation SQL (like a dbt model)
transformation = """
CREATE TABLE mart_revenue_by_country AS
SELECT u.country, SUM(o.amount) AS revenue
FROM raw_users u JOIN raw_orders o ON o.user_id = u.user_id
GROUP BY u.country;
"""
con.execute(transformation)
 
# 3. Auto-tag PII by regex on column names
PII_PATTERNS = {
    "restricted": [r"ssn", r"credit.?card", r"password"],
    "confidential": [r"email", r"phone", r"address", r"dob"],
    "internal": [r"user_?id", r"account_?id"],
}
def classify(col: str) -> str:
    for tier, pats in PII_PATTERNS.items():
        if any(re.search(p, col, re.I) for p in pats):
            return tier
    return "public"
 
catalog = {}
for tbl in ["raw_users", "raw_orders", "mart_revenue_by_country"]:
    cols = con.execute(f"DESCRIBE {tbl}").fetchall()
    catalog[tbl] = {c[0]: {"type": c[1], "tier": classify(c[0])} for c in cols}
 
print("=== CATALOG ===")
print(json.dumps(catalog, indent=2))
 
# 4. Extract column-level lineage from the SQL using sqlglot
parsed = sqlglot.parse_one(transformation, dialect="duckdb")
lineage = defaultdict(list)
for select in parsed.find_all(sqlglot.exp.Select):
    for proj in select.selects:
        target = proj.alias_or_name
        for col in proj.find_all(sqlglot.exp.Column):
            lineage[f"mart_revenue_by_country.{target}"].append(
                f"{col.table}.{col.name}")
 
print("\n=== COLUMN LINEAGE ===")
for k, v in lineage.items():
    print(f"  {k}  <-  {v}")
 
# 5. GDPR simulation: "delete user 1"
print("\n=== GDPR delete for user_id=1 ===")
for tbl, cols in catalog.items():
    if "user_id" in cols:
        n_before = con.execute(f"SELECT COUNT(*) FROM {tbl}").fetchone()[0]
        con.execute(f"DELETE FROM {tbl} WHERE user_id = 1")
        n_after = con.execute(f"SELECT COUNT(*) FROM {tbl}").fetchone()[0]
        print(f"  {tbl}: {n_before} -> {n_after} rows (removed {n_before - n_after})")
    else:
        print(f"  {tbl}: no user_id column; needs manual review via lineage")
 
# 6. Downstream refresh (would trigger dbt/Airflow in real life)
con.execute("DROP TABLE mart_revenue_by_country;")
con.execute(transformation)
print("\nRebuilt mart_revenue_by_country from cleaned sources.")

What to observe:

1. The catalog is just a dict — every "enterprise data catalog" starts this way, plus a UI + collaboration features.
2. PII auto-tagging via column names catches ~70% of PII in most orgs; the rest needs value-based scanning (regex on data).
3. sqlglot gives you free column-level lineage from any SQL — no proprietary parser needed.
4. GDPR delete finds all 2 source tables with user_id, but the mart has no user_id — you need lineage to know to rebuild it.
5. Running DELETE doesn't automatically refresh downstream — that's why lineage + orchestration must connect.

Try this modification: change the transformation SQL to SELECT u.email, ... (leaking PII into the mart). Re-run and watch the catalog now flag mart_revenue_by_country.email as confidential. That's how a governance tool catches "PII leaking into an aggregate table" bugs.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Real orgs build governance across three tools that must integrate:

• Catalog — DataHub (LinkedIn OSS), Unity Catalog (Databricks), Purview (Microsoft), AWS Glue Data Catalog, Atlan, Collibra. Provides the "what/who/where" API.
• Access enforcement — Apache Ranger, Unity Catalog, Snowflake RBAC + masking policies, Immuta, BigQuery IAM + policy tags. Column-level and row-level security.
• Cost management — Snowflake's RESOURCE_MONITORS + query tags, BigQuery reservations, AWS Cost Explorer with allocation tags, OpenCost for Kubernetes, Vantage / CloudZero as third-party layers.

War stories:

• The €20M GDPR fine. A telco couldn't prove they'd deleted a user's data because a shadow copy lived in a Redshift snapshot nobody catalogued. Regulator saw undeleted rows in an audit sample → fine. Lesson: catalog all copies (snapshots, backups, S3 dumps, dev clones), not just prod tables.
• The runaway Snowflake bill. A poorly-written recursive CTE ran on an XL warehouse for 14 hours before someone noticed. $18,000 for one query. Fix: `RESOURCE_MONITOR` at the warehouse level with a hard$/day suspend threshold, and per-team query tags so incidents get billed to the right cost centre.
• The PII in JSON. Team stored a metadata JSON column that "usually doesn't have PII" — until someone dumped {"passport": "..."} into it. Column tagging based on name missed it. Fix: value-scanning tools (Snowflake's Sensitive Data Classifier, AWS Macie) that sample rows and detect PII patterns in strings/JSON.
• The lineage lie. Team manually maintained a lineage doc in Confluence; it drifted 6 months out of date. When a producer changed a schema, downstream broke silently. Fix: lineage must be automatically extracted from dbt manifests, query logs, and Airflow DAGs — never hand-written.
• The "the model is confidential" ML incident. Data scientist trained on PII columns, saved model to a public S3 bucket. Model weights can leak training data. Lesson: PII classification must extend to derived artefacts (models, feature stores, embeddings), not just tables.

How top teams handle it: every table has a mandatory owner field (enforced at creation), every warehouse query is tagged (enforced by the SQL proxy or dbt), PII is auto-classified on ingest, and a monthly "cost + governance review" surfaces top 10 expensive queries, top 10 untagged resources, and top 10 tables with stale ownership.

Gotcha to memorise: governance is a culture problem more than a tech problem. The best DataHub deployment fails if engineers don't fill in table descriptions. Bake it into PR templates ("does this new table have an owner + description + PII classification?") — that's where the win comes from.

(e) Quiz + exercise · 10 min

1. What are the three main pillars of data governance?
2. Why is column-level lineage necessary for GDPR compliance in a modern warehouse?
3. What's the difference between column-name-based PII tagging and value-scan-based tagging? When does each catch things the other misses?
4. In Snowflake, what mechanism lets the CFO attribute a $50k query to a specific team?
5. Why is manually-maintained lineage always worse than auto-extracted lineage?

Stretch (connects to S053 — Data Quality): You detect a distribution anomaly (spike in null rate) on a mart. Walk through — using catalog + lineage — how you'd trace it upstream to the root cause and notify the correct owner team, without paging everyone.

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is data governance? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S055): HTTP Fundamentals — Verbs, Status Codes, Headers, Caching
• Previous (S053): Data Quality — Freshness, Volume, Schema, Distribution
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.