Solutions — Homework 14

Lesson 14 · Data Engineering with Python

Task 1 — Most expensive team last week

DuckDB form

SELECT regexp_extract(tag, 'team=([^;]+)', 1) AS team,
       SUM(elapsed_s)                          AS total_seconds
FROM _query_costs
WHERE ts >= now() - INTERVAL 7 DAY
  AND tag LIKE 'team=%'
GROUP BY 1
ORDER BY total_seconds DESC
LIMIT 1;

Snowflake form

SELECT team, total_credits
FROM (
  SELECT
    REGEXP_SUBSTR(query_tag, 'team=([^;]+)', 1, 1, 'e', 1) AS team,
    SUM(credits_used_cloud_services) AS total_credits
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP)
    AND query_tag LIKE 'team=%'
  GROUP BY 1
)
ORDER BY total_credits DESC
LIMIT 1;

Common mistake: filtering by a literal date string such as WHERE ts >= '2026-05-19'. The report must roll forward automatically; use a relative date expression (now() - INTERVAL 7 DAY or DATEADD(DAY, -7, CURRENT_TIMESTAMP)) so it works correctly every time it runs, not just on the day you wrote it.

Task 2 — New `data_scientist` role

Add the following block inside the roles: mapping in governance/grants.yml:

  data_scientist:
    description: "ML feature engineering — read-only on facts + features"
    grants:
      - { table: fact_events,   privileges: [SELECT] }
      - { table: feature_store, privileges: [SELECT] }

The full roles: section after the change should look like this:

roles:
  analytics_reader:
    description: "Read-only on dim_ and fact_ tables"
    grants:
      - { table: dim_events,  privileges: [SELECT] }
      - { table: dim_actors,  privileges: [SELECT] }
      - { table: fact_events, privileges: [SELECT] }
  pipeline_writer:
    description: "Pipeline runner — writes to dim_/fact_ tables, no DDL"
    grants:
      - { table: dim_events,  privileges: [SELECT, INSERT, UPDATE, DELETE] }
      - { table: dim_actors,  privileges: [SELECT, INSERT, UPDATE, DELETE] }
      - { table: fact_events, privileges: [SELECT, INSERT, UPDATE, DELETE] }
  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] }
  data_scientist:
    description: "ML feature engineering — read-only on facts + features"
    grants:
      - { table: fact_events,   privileges: [SELECT] }
      - { table: feature_store, privileges: [SELECT] }

Common mistake: granting [SELECT, INSERT] “to be safe”. Least privilege means exactly that — the least access needed to do the job. A data scientist reading feature data has no reason to write to it, and over-provisioning creates an audit finding the moment it appears in a review.

Task 3 — Declined deletion request

import json, datetime, os

entry = {
    "ts": datetime.datetime.now(datetime.timezone.utc).isoformat(),
    "action": "gdpr_decline",
    "subject": "octocat-public",
    "reason": "Subject is a fictional public-figure mascot; data falls under journalistic exemption per GDPR Art. 85",
    "legal_basis": "Art. 85 + national press law",
    "operator": "data-protection-officer",
}
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 decline for:", entry["subject"])

Common mistake: not logging why the request was declined. A decline is a legal decision, not a system action, and it must be defensible to a supervisory authority on request. If your log entry says only

"action":
    "gdpr_decline"

without a reason and a legal_basis, you cannot demonstrate that the decision was justified. Every decline entry in the deletion log must include both fields.

Course complete. The sample-pipeline now covers the full production-grade ETL lifecycle: extract → transform → quality → orchestration → SQL → dbt → DuckDB → Iceberg → warehouse adapters → observability → schema governance → cost & GDPR. Streaming is the natural next step. See the sibling Cloud Infrastructure & DevOps course currently in development on this platform.

Solutions — Homework 14

Task 1 — Most expensive team last week

DuckDB form

Snowflake form

Task 2 — New data_scientist role

Task 3 — Declined deletion request

Task 2 — New `data_scientist` role