Cost & Governance

Lesson 14 · Data Engineering with Python · ~15 min read

1. The three production concerns

When a data platform moves from prototype to production, three governance concerns emerge that cannot be deferred any longer: cost, access, and audit. They are distinct problems with distinct tooling, but they share a common thread — every one of them is ultimately a question about who.

Cost asks: who is spending the warehouse credits? A Snowflake warehouse bills per credit consumed; a BigQuery job bills per terabyte scanned. The invoice arrives as a single number for the whole organisation. Without attribution, the engineering manager cannot answer why the bill doubled last Thursday, and the data team cannot make trade-offs between query efficiency and delivery speed.

Access asks: who is allowed to read or write which tables? Raw event data often contains PII — email addresses, IP addresses, user handles. Not every analyst needs access to every column. A role taxonomy that grants the minimum necessary privileges is not just a security best practice; it is a GDPR compliance requirement.

Audit asks: who touched this row, and what does the regulation demand that you do about it? Audit questions arise from security incidents (“did this contractor read the executive salary table?”), data quality investigations (“which job wrote those null values on Tuesday?”), and regulatory requests (“delete all data for this user within 30 days”). Answering them quickly requires that the audit trail was built proactively, not reconstructed after the fact.

2. Cost attribution patterns

The universal primitive for warehouse cost attribution is the query tag: a short string attached to every SQL statement before it is executed. The warehouse records the tag in its query-history metadata, and you aggregate it to produce per-team cost reports.

The tag schema adopted by the gharchive sample pipeline follows a three-part convention:

team=<name>;job=<name>;run_id=<uuid>

The team component identifies the organisational owner; job identifies the specific pipeline or report; run_id is a correlation handle that can link the query tag back to an Airflow run ID, a CI build, or a manual invocation. Semicolons delimit the parts, and a regex like team=([^;]+) extracts any component without a dedicated parser.

The critical discipline is to apply the tag at the warehouse adapter boundary — the single Python function that issues SQL. Tagging in the adapter means that every query is tagged, including queries issued by library code that the developer did not write. Tagging at call sites instead is the classic mistake: you remember to tag the important queries and miss the cheap probing queries that accumulate into a surprisingly large fraction of the bill.

Cross-warehouse portability is straightforward because each major warehouse has an equivalent mechanism. Snowflake exposes the tag in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY.QUERY_TAG; BigQuery exposes job labels in INFORMATION_SCHEMA.JOBS.labels; Redshift records query groups in STL_QUERY. DuckDB has no billing metadata at all, so the adapter falls back to recording wall-clock seconds in a local _query_costs table — not a cost proxy, but sufficient for understanding relative query weight during development.

3. Declarative table-level access

The conventional approach to warehouse access control is to run GRANT SELECT ON dim_events TO analytics_reader statements imperatively, often in an onboarding runbook or a migration script. The problem is drift: over months, the grants that have been applied to the warehouse accumulate a history that no single document reflects. When an auditor asks “which roles can read dim_actors?”, the answer requires querying the warehouse’s live metadata, which does not version-control well.

The declarative alternative is to store the complete desired grant state in a YAML file that lives in the repository alongside the pipeline code. A deploy script reads the file and applies the grants idempotently — it is the same pattern as infrastructure-as-code or dbt schema tests. The YAML file becomes the single source of truth, and every change to it is visible in a pull request diff.

The role taxonomy in the gharchive pipeline reflects a principle of least privilege. The analytics_reader role has SELECT on the dimension and fact tables it needs for dashboarding. The pipeline_writer role has INSERT, UPDATE, and DELETE on the same tables but not DROP or ALTER. The pii_admin role is the only role permitted to read PII columns or execute GDPR deletion queries; it is granted to a small, audited list of individuals.

Least privilege serves two purposes simultaneously: it limits blast radius when credentials are compromised, and it creates a defence-in-depth layer for PII protection that operates independently of application-level access controls.

4. Column-level controls for PII

Table-level grants are coarse: they either permit or deny the entire row. Most production data models contain tables where most columns are not sensitive but a handful are — an events table might expose actor, repo, and created_at freely while treating email and ip_address as restricted.

The dim_actors table in the gharchive pipeline has exactly this shape. The email and ip_address columns are declared in governance/grants.yml under a masking_policies key:

masking_policies:
  - column: dim_actors.email
    policy: mask_unless_role_in([pii_admin])
  - column: dim_actors.ip_address
    policy: mask_unless_role_in([pii_admin])

On Snowflake, this corresponds to a dynamic data masking policy applied to the column in DDL. On BigQuery, it corresponds to column-level access control in the table’s IAM configuration. On DuckDB, there is no native masking capability; the YAML is documentation only, and the enforcement responsibility falls on the application code that constructs queries. For a local development environment this is acceptable; production workloads must run on a warehouse that enforces masking at the engine level.

A useful heuristic: treat every string column that arrives from an external API as potentially PII until you have explicitly reviewed it. With gharchive data, actor names are public-by-definition (GitHub usernames are displayed publicly), but the email addresses that some event types expose are not.

5. The audit story

An audit question — “who read the salary table last Tuesday?” or “which pipeline wrote these null values?” — requires correlating events from three independent log sources.

The first source is the warehouse’s query history. Snowflake’s QUERY_HISTORY view records every SQL statement with the user who executed it, the warehouse that ran it, the start and end time, the bytes scanned, and the query tag. BigQuery’s INFORMATION_SCHEMA.JOBS records analogous information. This gives you read-level attribution: you can answer “which user ran a query against dim_actors at 14:32 UTC on Tuesday?” with a single SQL statement.

The second source is the OpenLineage event stream set up in Lesson 12. OpenLineage records every transformation run: which input datasets were consumed, which output dataset was produced, and at what time. This is the write-level complement to the query-history read-level: it answers “which pipeline run wrote the rows that were read at 14:32?”

The third source is the git history of the pipeline code combined with dbt run metadata. This answers “what SQL was the pipeline executing at that time?” — crucial for distinguishing “the pipeline wrote null values because of a bug in commit abc123” from “the upstream data source sent nulls”.

The join key across all three sources is (time, dataset, user). With all three sources intact and queryable, a typical audit question resolves in under an hour. With only one or two sources, it can take days or be impossible. Build all three from the start; they are cheap to maintain but expensive to reconstruct retroactively.

6. GDPR right-to-be-forgotten

Article 17 of the General Data Protection Regulation grants individuals the right to have their personal data erased. The obligation attaches when the data is no longer necessary for the original purpose, when consent is withdrawn, or on explicit request. The controller (your organisation) has 30 days to act from the date of the request.

The first and most important distinction is between soft deletion and hard deletion. A soft delete sets a deleted=true flag on a row; the row remains physically present in the table and in storage. A sufficiently privileged query reads it just as easily as before the deletion. Regulators have made clear — and supervisory authority guidance is consistent on this point — that soft-deletion does not satisfy Article 17.

Hard deletion requires that the bytes are physically removed from storage. For a conventional relational database this is straightforward: DELETE followed by a VACUUM removes the rows and reclaims the space. For an Iceberg table, the story is more complex.

Iceberg maintains a snapshot history for time-travel: every write produces a new snapshot, and old data files are retained until explicitly expired. A DELETE WHERE actor = ? removes the matching rows from the current snapshot, but old snapshots still reference the original data files. A time-travel query (SELECT ... FOR VERSION AS OF <old_snapshot_id>) returns the deleted rows.

The hard-delete procedure for Iceberg is therefore a two-step:

tbl.delete(EqualTo("actor", subject))  # removes rows from current snapshot
tbl.expire_snapshots()                  # physically removes old data files

The governance/gdpr/right_to_be_forgotten.py script in the sample pipeline implements exactly this pattern across all three tables that can contain actor data: events_raw, dim_actors, and fact_events.

Every deletion must be logged. Article 17 requires you to be able to demonstrate to a supervisory authority that the deletion occurred. The deletion log is an append-only JSON-lines file. Each entry records the timestamp, the subject (the actor value), which tables were affected, and how many rows were removed. The deletion log itself is never deleted in response to a subsequent request — it is the compliance evidence, not the personal data.

The shape of a compliant log entry:

{
  "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": "data-protection-officer"
}

7. Common pitfalls

References

• Snowflake QUERY_HISTORY view documentation
• BigQuery INFORMATION_SCHEMA.JOBS documentation
• AWS Lake Formation column-level access control
• Apache Iceberg — expire snapshots
• GDPR Article 17 — Right to erasure (EUR-Lex)
• datacontract.com — open governance template