Lesson 14 — Cost & Governance

Who pays, who reads, and how you delete one user’s data.

Three production concerns

All three concerns centre on the same word: who. Build systems that can answer it in under an hour.

Warehouse cost attribution

Warehouse queries cost money — Snowflake charges per credit, BigQuery charges per terabyte scanned, and your cloud bill arrives as one undifferentiated total. Without tagging, you cannot tell whether Analytics or Growth is responsible for the spike on Friday afternoon.

The solution is to tag every query at the boundary: the single Python function that issues SQL against the warehouse. One tag, one place, zero missed queries.

Query tags as the cost-attribution primitive

from warehouse.adapter import query

# Every call carries a tag — three components, semicolon-delimited
query(
    "SELECT count(*) FROM fact_events",
    tag="team=analytics;job=daily_report;run_id=a1b2c3",
)
query(
    "SELECT actor, count(*) FROM events GROUP BY 1",
    tag="team=growth;job=top_actors;run_id=d4e5f6",
)

The warehouse records the tag alongside each query in its history view. A GROUP BY team on that view gives you dollars per team per day.

The cost attribution SQL

-- Snowflake: per-team cost over the last 7 days
SELECT
  REGEXP_SUBSTR(query_tag, 'team=([^;]+)', 1, 1, 'e', 1) AS team,
  COUNT(*)                  AS query_count,
  SUM(credits_used_cloud_services) * 3.00 AS estimated_usd
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND query_tag LIKE 'team=%'
GROUP BY 1
ORDER BY estimated_usd DESC;

Works on Snowflake’s QUERY_HISTORY view. DuckDB has no native credit-cost metadata — the adapter records wall-clock seconds in a local _query_costs table as a fallback.

Table-level access controls

analytics_reader:
  description: "Read-only on dim_ and fact_ tables"
  grants:
    - { table: dim_events,  privileges: [SELECT] }
    - { table: fact_events, privileges: [SELECT] }

pii_admin:
  description: "Only role allowed to read PII columns or run GDPR deletes"
  grants:
    - { table: dim_actors,  privileges: [SELECT, DELETE] }
    - { table: events_raw,  privileges: [SELECT, DELETE] }

Grants are declared in governance/grants.yml — the YAML is the source of truth. A deploy script (out of scope) reads it and applies grants via the warehouse’s IAM. The YAML file itself is the audit trail: every change appears in the git diff.

Column-level masking for PII

• dim_actors.email — masked unless the session role is pii_admin
• dim_actors.ip_address — masked unless the session role is pii_admin
• Snowflake: masking policies attached to the column in DDL
• BigQuery: column-level ACLs in the table schema
• DuckDB: no native masking — the grants.yml is documentation only for local dev

The “who touched this row” audit story

Every warehouse logs query history. Combine three sources to answer any audit question in under an hour:

GDPR Article 17 — right to be forgotten

A user submits a deletion request. You have 30 days to comply. The obligation covers every system that holds their data — the data warehouse, Iceberg snapshots, and backups.

• Soft-delete is not enough — flipping a deleted=true flag leaves the data physically present and queryable by a sufficiently privileged user
• You need a hard delete: remove the rows and then remove the backing storage files
• For backups: either delete from backups or document that backups cannot be individually queried (most regulators accept the latter)

Iceberg snapshots and hard-delete

Iceberg keeps old data files for time-travel. After a DELETE statement, the rows are gone from the current snapshot — but the old data files still exist on disk, and a time-travel query can reach them.

The two-step hard-delete:

1. tbl.delete(EqualTo("actor", subject)) — removes matching rows from the current snapshot
2. tbl.expire_snapshots() — physically removes old data files that still contained those rows

Only after both steps is the data genuinely gone.

The deletion code path

def delete_actor(actor: str) -> dict[str, int]:
    """Hard-delete all rows for actor across every Iceberg table."""
    catalog = load_catalog("gharchive_local")
    deleted = {}
    for tname in ("events_raw", "dim_actors", "fact_events"):
        tbl = catalog.load_table(tname)
        before = tbl.scan().to_pandas().shape[0]
        tbl.delete(EqualTo("actor", actor))      # step 1
        after = tbl.scan().to_pandas().shape[0]
        deleted[tname] = before - after
        tbl.expire_snapshots()                    # step 2
    return deleted

Idempotent — re-running after a successful deletion is a no-op.

Audit log of deletions

Every GDPR delete must itself be logged (Article 17 requires you to demonstrate compliance). The deletion log is an append-only file — and it is the one file you do NOT delete in response to a subsequent request. It is the regulator’s audit trail.

{
  "ts": "2026-05-26T12:00:00Z",
  "action": "gdpr_delete",
  "subject": "target-user",
  "tables_affected": ["events_raw", "dim_actors", "fact_events"],
  "rows_deleted": {"events_raw": 2, "dim_actors": 0, "fact_events": 0},
  "operator": "lab-14-step-7"
}

What we’ll do in lab

• Step 1 — Tag three queries with different teams; inspect _query_costs
• Step 2 — View the per-team cost attribution from the DuckDB fallback table
• Step 3 — Review governance/grants.yml; identify which role can read PII
• Step 4 — Seed a test actor into events_raw
• Step 5 — Run the GDPR deletion script against the local Iceberg tables
• Step 6 — Verify the rows are gone; audit-log the deletion

Schema diff in the sample pipeline

 sample-pipeline/
   extract/
   transform/
   quality/
   airflow/
   dbt/
   iceberg/
   warehouse/
+    adapter.py         # tag kwarg added for cost attribution
   observability/
   schema_evolution/
+  governance/          # NEW — L14
+    __init__.py
+    cost_attribution.py
+    grants.yml
+    gdpr/
+      __init__.py
+      right_to_be_forgotten.py

Final state matches the course spec’s §3 pipeline inventory exactly.

Recap & course complete

• Cost attribution — tag at the boundary; GROUP BY team on QUERY_HISTORY
• Access — declarative YAML is the source of truth; deploy scripts apply it; git history is the audit trail
• GDPR — hard-delete (not soft-delete) + expire_snapshots() + append-only deletion log