Lesson 14 Cost & Governance
Theme:

Lab 14 — Tag, Grant, and Forget

Lesson 14 · Data Engineering with Python · ~45 min

By the end of this lab you will have: three cost-tagged queries visible in the local _query_costs table, the access_grants.yaml YAML reviewed for correctness, and one synthetic GDPR deletion run end-to-end against local Iceberg tables with an audit log entry written.

Prerequisites

Step 1 — Tag three queries

Run three queries through the adapter, each with a different team tag. The adapter records (ts, tag, elapsed_s) in _query_costs automatically. 

python -c "
from warehouse.adapter import query
query('SELECT count(*) FROM events', tag='team=analytics;job=row_count')
query('SELECT actor, count(*) FROM events GROUP BY 1', tag='team=growth;job=top_actors')
query('SELECT created_at FROM events LIMIT 1', tag='team=eng;job=probe')
print('Three tagged queries executed.')
"
What just happened? Each call to query() ran the SQL and then inserted a row into _query_costs with the tag and the wall-clock duration. On Snowflake the same tag string would have been sent as ALTER SESSION SET QUERY_TAG = '...' before the SQL.

Step 2 — View the cost attribution (DuckDB fallback)

Aggregate _query_costs to see per-team totals: 

python -c "
from warehouse.adapter import query
df = query(
    \"SELECT regexp_extract(tag, 'team=([^;]+)', 1) AS team, \"
    \"count(*) AS queries, round(sum(elapsed_s), 4) AS total_s \"
    \"FROM _query_costs GROUP BY 1 ORDER BY total_s DESC\"
)
print(df.to_string(index=False))
"

Expected: 3 rows, one per team (analytics, growth, eng).

Snowflake variant (optional, requires a paid account): Open governance/cost_attribution.py and copy the SNOWFLAKE_COST_SQL constant into the Snowflake worksheet UI. Run it. You should see the same shape of output but with credit-dollar columns instead of elapsed seconds.

Step 3 — Review the grants YAML

Open governance/grants.yml and answer these questions before continuing:

  1. Which role(s) can read dim_actors.email?
  2. Which role(s) can write to fact_events?
  3. Which role(s) can run a GDPR deletion against events_raw?

Verify the file parses correctly:

python -c "
import yaml
data = yaml.safe_load(open('governance/grants.yml'))
print('Roles:', list(data['roles']))
print('Masking policies:', [p['column'] for p in data['masking_policies']])
"

Expected output:

Roles: ['analytics_reader', 'pipeline_writer', 'pii_admin']
Masking policies: ['dim_actors.email', 'dim_actors.ip_address']

Answers: only pii_admin can read dim_actors.email; pipeline_writer can write to fact_events; only pii_admin can delete from events_raw.

Step 4 — Set up the GDPR demo data

Check whether a test actor already exists in the local events_raw table: 

python -c "
import duckdb
con = duckdb.connect('warehouse/warehouse.duckdb')
rows = con.execute(\"SELECT count(*) FROM events_raw WHERE actor='target-user'\").fetchall()
print('Existing rows for target-user:', rows[0][0])
"

If the count is 0, insert a few synthetic rows for the demo: 

python -c "
import duckdb
con = duckdb.connect('warehouse/warehouse.duckdb')
con.execute(\"\"\"
    INSERT INTO events_raw (id, actor, repo, created_at)
    VALUES
      (1001, 'target-user', 'demo/repo', current_timestamp),
      (1002, 'target-user', 'demo/repo', current_timestamp)
\"\"\")
con.commit()
print('Inserted 2 rows for target-user.')
"

Step 5 — Run the GDPR deletion

Use the governance module directly: 

python -c "
from governance.gdpr.right_to_be_forgotten import delete_actor
result = delete_actor('target-user')
for tname, n in result.items():
    print(f'deleted {n} rows from {tname}')
"

Expected output:

deleted 2 rows from events_raw
deleted 0 rows from dim_actors
deleted 0 rows from fact_events
The script also calls expire_snapshots() on each table after the deletion. This step physically removes the old Iceberg data files that still contained the rows in previous snapshots. Without it, a time-travel query could recover the data.

Step 6 — Verify the rows are gone

python -c "
import duckdb
con = duckdb.connect('warehouse/warehouse.duckdb')
rows = con.execute(\"SELECT count(*) FROM events_raw WHERE actor='target-user'\").fetchall()
print('Remaining rows:', rows[0][0])
"

Expected: Remaining rows: 0.

Step 7 — Audit-log the deletion

Append a JSON-lines entry to the deletion log. This file is the regulator’s audit trail and must never be deleted in response to a subsequent erasure request. 

python -c "
import json, datetime, os
entry = {
    'ts': datetime.datetime.now(datetime.timezone.utc).isoformat(),
    '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',
}
os.makedirs('governance/gdpr', exist_ok=True)
with open('governance/gdpr/deletion_log.jsonl', 'a') as f:
    f.write(json.dumps(entry) + '\n')
print('Logged:', entry['action'], 'for subject:', entry['subject'])
"

Confirm the log was written: 

python -c "
import json
for line in open('governance/gdpr/deletion_log.jsonl'):
    entry = json.loads(line)
    print(entry['ts'], entry['action'], entry['subject'])
"

What you should leave with

Troubleshooting