Queries · q334 · Data Protection Officer · week 2, night 4
“Is deleted_at a field that's actually populated for any users platform-wide, or is account deactivation simply not wired up at all (context for the erasure finding)?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 2; shared with Henrik Aalto, Chief Information Security Officer
A data protection authority asked how we handle privacy: how many privacy requests we receive and how fast we complete them, how consent is recorded, and what remains after an account is deleted.
The manager, at the start of week 2
Subject: Week 2 — DPA inquiry changes our priorities
Priyanka,
Good work — this is exactly the kind of digging I wanted. A few things surprised me and now matter more because of the regulator inquiry (which I'm handling jointly with Henrik Aalto, CISO — our response needs to be accurate and defensible, not just reassuring).
What struck me:
So, for this week, please prioritize work that lets us answer the DPA's three questions honestly: volume and speed of privacy requests, how consent is captured and whether it's reliable, and what data persists post-deletion. That's the deliverable Henrik and I need to brief the board audit committee before we respond.
My questions:
If all four can't fit, start with 2 and 4 — those go directly into the regulator response.
Thanks, Ines
Subject: Security angle for the DPA response
Hi Priyanka,
A few things from my side that would strengthen the privacy response:
Happy to compare notes before you send anything to data engineering.
Henrik
The analyst · week 2, night 4, request 3 of 3
Last night's post-erasure report surfaced something I need to pin down on this final night: several "remaining" login_attempts/sessions dates are not just old pre-erasure history lingering — they occur well AFTER the erasure's completed_at (e.g. Marrowby & Vane, Cragsley Outfitters, and Studio Vrij subjects all show login attempts dated 2025-02-08 to 2025-02-11, and one Kontor24 Bürobedarf subject shows a session on 2025-12-02, seven months after its 2025-04-29 completion). The Feb 2025 cluster across multiple unrelated erased accounts lines up exactly with Noah Fischer's (CISO) channel post about a 10x login-failure spike in Feb 2025 that wasn't caught by alerts — this could mean that spike included attempts against accounts that should have already been locked down by erasure, which is squarely relevant to both the regulator response and Henrik's security angle. I want hard evidence (count, success/fail, exact timing) rather than eyeballing. I also realized I've never actually checked whether "completed" erasure touches the core users record at all (status/deleted_at) — all my evidence so far is about downstream history tables, not whether the primary account was deactivated, which is the more fundamental test of whether erasure does anything to account access. This is my last night, so I'm spending all 3 slots closing out Henrik's post-erasure footprint question definitively rather than opening new territory; the other items (1, 2, 3, 4, and Henrik's consent/closure-time asks) are already answered from the prior three nights.
The engineer · 1 dry run on the empty schema, then the submission
with user_status_stats as ( select status, count(*) as total_users, count(deleted_at) as users_with_deleted_at, max(deleted_at) as most_recent_deleted_at from users group by status ) select status, total_users, users_with_deleted_at as count_with_deleted_at_not_null, round(100.0 * users_with_deleted_at / nullif(total_users, 0), 2) as pct_with_deleted_at_set, most_recent_deleted_at from user_status_stats order by total_users desc, status asc
“Query compiles cleanly. Submitting.”
“Submitted. The report groups users by status platform-wide (no tenant/date filters) and shows total users, how many have deleted_at populated, the percentage, and the most recent deleted_at per status — giving Priyanka the baseline to compare against the 39 erasure-subject cases.”
As Priyanka Desai saw it, from the small dataset the agents worked on
status | total_users | count_with_deleted_at_not_null | pct_with_deleted_at_set | most_recent_deleted_at ------------+-------------+--------------------------------+-------------------------+--------------------------- active | 11731 | 0 | 0.0 | NULL deactivated | 41 | 39 | 95.12 | 2025-11-10 15:18:24.435098 suspended | 36 | 0 | 0.0 | NULL (3 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
with user_status_stats as ( select status, count(*) as total_users, count(deleted_at) as users_with_deleted_at, max(deleted_at) as most_recent_deleted_at from users group by status)select status, total_users, users_with_deleted_at as count_with_deleted_at_not_null, round(100.0 * users_with_deleted_at / nullif(total_users, 0), 2) as pct_with_deleted_at_set, 100.0 * users_with_deleted_at / nullif(total_users, 0) as pct_with_deleted_at_set, most_recent_deleted_atfrom user_status_statsorder by total_users desc, status ascorder by total_users desc NULLS FIRST, status asc NULLS LAST
with user_status_stats as ( select status, count(*) as total_users, count(deleted_at) as users_with_deleted_at, max(deleted_at) as most_recent_deleted_at from users group by status ) select status, total_users, users_with_deleted_at as count_with_deleted_at_not_null, 100.0 * users_with_deleted_at / nullif(total_users, 0) as pct_with_deleted_at_set, most_recent_deleted_at from user_status_stats order by total_users desc NULLS FIRST, status asc NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Priyanka Desai (analyst for the Data Protection Officer): Title: Is users.deleted_at used anywhere on the platform? Question: Is deleted_at a field that's actually populated for any users platform-wide, or is account deactivation simply not wired up at all (context for the erasure finding)? Grain: one row per users.status value, platform-wide, full history Columns: - status - total users - count with deleted_at not null - pct with deleted_at set - most recent deleted_at observed Details: Source: users, platform-wide, no tenant filter, no date filter. This gives the baseline: if deleted_at is essentially never populated for anyone regardless of status (not just the 39 erasure subjects), that points to a platform-wide gap in how account deactivation is recorded rather than something specific to the DSAR erasure workflow. Order by total users descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runwith user_status_stats as ( select status, count(*) as total_users, count(deleted_at) as users_with_deleted_at, max(deleted_at) as most_recent_deleted_at from users group by status ) select status, total_users, users_with_deleted_at as count_with_deleted_at_not_null, round(100.0 * users_with_deleted_at / nullif(total_users, 0), 2) as pct_with_deleted_at_set, most_recent_deleted_at from user_status_stats order by total_users desc, status asc
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: status, total_users, count_with_deleted_at_not_null, pct_with_deleted_at_set, most_recent_deleted_at
submitwith user_status_stats as ( select status, count(*) as total_users, count(deleted_at) as users_with_deleted_at, max(deleted_at) as most_recent_deleted_at from users group by status ) select status, total_users, users_with_deleted_at as count_with_deleted_at_not_null, round(100.0 * users_with_deleted_at / nullif(total_users, 0), 2) as pct_with_deleted_at_set, most_recent_deleted_at from user_status_stats order by total_users desc, status asc
Submitted to tonight's batch. Your work on this request is done.