Data Subject Rights and Request Handling Questions

Requirements:

• Delete user-level identifiers on request while preserving aggregate campaign metrics (clicks, conversions) and auditability.
• Low-latency reporting not required; correctness and provable deletion are critical.
• Retention windows, pseudonymization, and verifiable deletes.

High-level architecture:

• Ingest -> Raw Event Store (append-only, immutable, encrypted) -> Identity Service (pseudonymization) -> Aggregation Pipeline -> Aggregate Warehouse / OLAP -> Reporting UI
• Audit log and Delete Service operate against Raw Event Store + Identity Service.

Key components and strategy:

1. Pseudonymization

• On ingest, replace direct identifiers (email, device ID) with a one-way keyed hash (HMAC) using a rotating key stored in KMS. Store mapping (hash -> user token) only in an ephemeral Identity Service with strict ACLs and TTL; do NOT persist plaintext identifier in downstream stores.
• Keep raw PII only in the append-only Raw Event Store for a short, legally-allowed retention (encrypted, access-controlled).

2. Deletion (Right to be Forgotten)

• Delete request triggers Delete Service:
  • Remove PII from Raw Event Store (logical tombstone + secure wiping depending on storage).
  • Delete mapping in Identity Service so future hash resolution is impossible.
  • Emit "delete" events into aggregation pipeline to subtract or invalidate user-level contributions for any active cohorts within retention window.

3. Aggregation and retention windows

• Maintain aggregations at multiple granularities (daily/hourly by campaign, cohort) in the Aggregate Warehouse. Aggregations include two forms:
  • Count-based aggregates (non-PII): safe to keep beyond deletion if built from pseudonymized IDs and updated on delete events.
  • Contribution logs: lightweight per-aggregate provenance mapping (hashed-id -> contribution) kept only for the configurable retention window required to support deletes. After window expires, purge provenance logs; aggregates remain immutable.
• Use incremental aggregation with idempotent upserts and "remove" operations derived from delete events.

4. Historical reporting and audits

• For auditability, keep an append-only Audit Ledger (signed, tamper-evident) recording delete requests, keys rotated, timestamps, and delete propagation confirmations.
• For historical reports prior to deletion: produce reports from aggregates that have been adjusted by delete events. If legal requirement allows anonymized historical statistics, ensure they cannot be re-identified (apply k-anonymity/min thresholds, noise if required).
• Provide auditors a restricted view of the Audit Ledger plus aggregate deltas demonstrating that contributions were removed; do not expose PII.

Trade-offs and considerations:

• Keeping per-aggregate provenance enables precise deletes but increases storage and complexity — limit retention window to balance compliance and cost.
• Key management: rotate keys and track which key generated which HMAC; when deleting, ensure old keys are destroyed if required.
• Performance: deletion propagation is eventual; for strict guarantees, run synchronous delete for active cohorts.
• Verification: include deterministic tests that replay delete events into a test aggregate to prove correctness.

Edge cases:

• Cross-device linking: remove all mapped identifiers by querying Identity Service mapping before purge.
• Backups and cold storage: ensure backups are subject to same deletion workflow and have encryption keys revoked or data scrubbed.

This approach ensures provable removal of identifiable data while preserving useful aggregate metrics and an auditable trail.

Requirements & constraints:

• Delete or irreversibly anonymize personal data tied to a Subject Access/Right-to-Be-Forgotten (RTBF) request across analytics (GA/Amplitude), marketing (HubSpot), and the warehouse.
• Preserve aggregated analytics that do not allow re-identification.
• Maintain immutable audit trail (who/when/what) and verifiable proof of deletion.

1. Discovery (locate copies)

• Centralize identifiers: user_id, email, device_id, client_id, hashed identifiers.
• Run discovery jobs:
  • Warehouse: SQL search across tables and exported datasets (examples below).
  • Third-party: query GA/Amplitude/HubSpot APIs for matching IDs/tags.
• Record all locations in a deletion-job manifest (JSON) with time-stamped hash.

Example SQL to find PII:

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema='prod' AND column_name IN ('email','user_id','device_id');

2. Deletion vs anonymization policy

• If data required for legal/transactional records: pseudonymize (replace PII with irreversible token) and archive minimal retention with access controls.
• For analytics/marketing: delete user-level records (events, profiles). For warehouse rows used in aggregates, overwrite PII fields and remove direct identifiers, keeping non-identifying metrics.

3. Implementation steps

• Orchestrator service (e.g., Airflow/Lambda) takes manifest and:
  a) Calls vendor APIs to delete profiles/events (store vendor response).
  • Amplitude/GAnalytics: use their user deletion endpoints and request export receipts.
  • HubSpot: use Contacts API DELETE or GDPR endpoints.
    b) Runs warehouse jobs:
  • DELETE FROM events WHERE user_id = :id;
  • UPDATE users SET email = NULL, user_hash = SHA256(user_id || salt) WHERE user_id = :id;
  • For partitioned/historical datasets, run safe rewrite jobs (CTAS) that exclude/obfuscate rows.
    c) Rotate/expire any tokens/session caches and purge message queues.

Python pseudocode for vendor call:

resp = requests.post("https://api.amplitude.com/gdpr/deletion", json={"user_id": uid}, headers=hdrs)
log_vendor_response(resp.json())

4. Preserve aggregated analytics

• Use aggregation-before-deletion pattern: compute and materialize aggregates (e.g., daily counts) that include the user, or maintain strictly aggregate tables that never include raw identifiers.
• Recompute affected aggregates if deletions change totals: either decrement counters atomically or re-aggregate partition(s) excluding the user. Store versioned aggregates for audit.

5. Audit trail & compliance evidence

• Immutable log for each action: manifest id, actor (system or operator), timestamp, scope (tables, vendor), commands, checksums before/after, vendor receipts, job run-id.
• Store logs in WORM storage (append-only S3 + Glacier with object lock) and send signed receipts to requestor.
• Provide deletion certificate endpoint that returns manifest and cryptographic proof (hash chain) of actions.

6. Verification & monitoring

• Post-deletion verification jobs: re-run discovery queries and vendor lookups to confirm absence.
• Automated SLA timers: if vendor deletion is async, escalate if not completed within X days.
• Daily dashboards for outstanding RTBFs and audit status.

7. Safety, testing & rollback

• Test thoroughly in staging with realistic scrubbing and maintain canary lanes.
• Never expose raw PII in logs; log only hashes and job IDs.
• Rollback: deletions are generally irreversible; use legal-approved archival for transactional data if retention required.

Trade-offs & notes:

• Real-time deletion across many systems is complex; prefer near-real-time with strong SLA and verifiable receipts.
• For very large datasets, batch rewrite and re-aggregation are costlier but necessary to avoid leakage.
• Keep a living runbook and automate as much as possible to reduce human error.

This design gives an auditable, automated pipeline: discover → manifest → vendor + warehouse execution → verify → record immutable proof.