Queries · q333 · Data Protection Officer · week 2, night 4
“When an erasure request is marked completed, is the underlying users row actually deactivated or anonymized, or does the account remain fully active?”
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 2 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
select dsr.public_id as dsar_public_id, t.name as tenant_name, dsr.subject_user_id, cast(dsr.completed_at as date) as completed_at, u.status as user_status, cast(u.deleted_at as date) as user_deleted_at, u.updated_at as user_updated_at, cast(u.last_login_at as date) as user_last_login_at from data_subject_requests dsr join tenants t on t.id = dsr.tenant_id join users u on u.tenant_id = dsr.tenant_id and u.id = dsr.subject_user_id where dsr.request_type = 'erasure' and dsr.status = 'completed' order by dsr.completed_at asc, dsr.public_id asc
“Query compiles cleanly. Submitting.”
“Submitted. The report joins data_subject_requests (erasure/completed) to users on tenant_id + subject_user_id, showing only status, deleted_at, updated_at, and last_login_at per user — enough to see whether completing an erasure request actually touches the user row, without exposing email/name.”
As Priyanka Desai saw it, from the small dataset the agents worked on
dsar_public_id | tenant_name | subject_user_id | completed_at | user_status | user_deleted_at | user_updated_at | user_last_login_at -------------------------------------+---------------------+-----------------+--------------+-------------+-----------------+----------------------------+------------------- 0bf5fe16-9c96-4a01-9861-ba58a8c62338 | Grind Collective | 345 | 2020-04-28 | deactivated | 2020-04-28 | 2020-04-28 19:22:51.144924 | 2019-09-30 4531b08c-8444-4f57-aed1-451f6cbf1fed | Ampfeld Elektronik | 1218 | 2021-02-15 | deactivated | 2021-02-15 | 2021-02-15 17:08:35.966168 | 2020-07-21 ddc3341d-2a13-41d3-b194-d7f512b94132 | Marrowby & Vane | 407 | 2021-04-16 | deactivated | 2021-04-16 | 2021-04-16 02:45:58.630788 | 2021-03-03 74bec8f7-3b6e-4350-8535-caee73c055e1 | Marrowby & Vane | 363 | 2021-10-16 | deactivated | 2021-10-16 | 2021-10-16 12:27:24.652039 | 2019-09-05 df4dc623-ddb6-4508-81af-ee136635850b | Cragsley Outfitters | 671 | 2022-02-05 | deactivated | 2022-02-05 | 2022-02-05 01:44:35.9245 | 2021-04-09 cfddd972-efd4-4480-9ab3-12a867c46b79 | Coastline Interiors | 1769 | 2022-06-03 | deactivated | 2022-06-03 | 2022-06-03 15:01:02.294238 | 2021-09-06 f9ddeb61-8a65-4ffc-8a99-2d6746cab8ee | Wiesgrund Wohnen | 1735 | 2022-08-26 | deactivated | 2022-08-26 | 2022-08-26 12:47:21.258026 | 2021-08-30 2554d690-68f1-4e5f-a5bd-aa9ecd805f5b | Midland Safety Wear | 1455 | 2022-09-02 | deactivated | 2022-09-02 | 2022-09-02 12:46:29.129135 | 2021-01-19 3193004e-c781-41fc-9cd9-c2bfb9f01dbe | Studio Vrij | 1519 | 2023-05-15 | deactivated | 2023-05-15 | 2023-05-15 14:13:16.012883 | 2023-01-08 861d7a09-3d30-4361-9726-2cef07e08814 | Gratlinie Outdoor | 2849 | 2023-09-01 | deactivated | 2023-09-01 | 2023-09-01 17:09:25.696903 | 2023-06-25 e3271040-65dc-4131-a4a0-6d9f36d6980f | Ampfeld Elektronik | 1195 | 2023-10-26 | deactivated | 2023-10-26 | 2023-10-26 23:18:00.335536 | 2023-01-11 074e73eb-f889-4b53-a9c6-0fa4b5499b07 | Fennel & Flint | 2556 | 2023-11-22 | deactivated | 2023-11-22 | 2023-11-22 10:58:10.065768 | 2023-10-31 24d61e33-87e8-44e2-9bde-a63f6a1ddaf5 | Spice Route Box | 3607 | 2024-01-05 | deactivated | 2024-01-05 | 2024-01-05 12:34:16.549504 | 2023-12-31 806d5c26-623f-4692-a0a4-812bc1f5d0cc | Ampfeld Elektronik | 4197 | 2024-02-17 | deactivated | 2024-02-17 | 2024-02-17 13:23:06.999926 | 2024-02-08 7f3350f7-03dd-4fdd-a4f1-4c25e3dc35c1 | Marrowby & Vane | 2805 | 2024-02-28 | deactivated | 2024-02-28 | 2024-02-28 21:49:21.419763 | 2024-02-21 1931ed93-6d67-494d-87b9-618f9047e0f7 | Fell & Fjell | 4617 | 2024-04-03 | deactivated | 2024-04-03 | 2024-04-03 08:36:29.912176 | 2024-02-26 d89368e7-9f81-4832-8e7e-8da777ac3441 | Spice Route Box | 2591 | 2024-04-05 | deactivated | 2024-04-05 | 2024-04-05 23:36:13.254768 | 2024-02-11 691ec686-457b-4eba-966f-40194fab69fa | Sorella Skin | 5453 | 2024-05-16 | deactivated | 2024-05-16 | 2024-05-16 09:29:22.193905 | 2024-04-18 a3194bf3-bb13-40d3-872d-b28febd38b01 | Ampfeld Elektronik | 4907 | 2024-05-28 | deactivated | 2024-05-28 | 2024-05-28 02:42:56.618382 | 2024-05-08 9158c9e4-8abc-42b2-9d00-0c14afced0a4 | Kayak Corner | 1792 | 2024-05-31 | deactivated | 2024-05-31 | 2024-05-31 14:54:51.937909 | 2023-03-19 99add429-adf4-44a4-b51b-e668f52d43aa | Coastline Interiors | 2532 | 2024-06-07 | deactivated | 2024-06-07 | 2024-06-07 06:29:42.727346 | 2023-08-31 442f76f1-6417-4666-821a-7bb927f1743a | Cragsley Outfitters | 829 | 2024-06-14 | deactivated | 2024-06-14 | 2024-06-14 22:57:22.965996 | 2021-08-23 a627a640-0eb3-45fe-80c8-fb9ab29758e0 | Little Larch Kids | 6196 | 2024-08-27 | deactivated | 2024-08-27 | 2024-08-27 20:36:18.138635 | 2023-04-22 49a0b5ef-09e3-4e24-8e7f-a481e2b109c5 | Petalcourt Beauty | 3366 | 2024-09-15 | deactivated | 2024-09-15 | 2024-09-15 19:16:56.514915 | 2024-04-29 4f961286-195f-4e5f-a7bd-6c251c3c3488 | Fennel & Flint | 3458 | 2024-10-17 | deactivated | 2024-10-17 | 2024-10-17 20:38:10.344826 | 2024-10-06 840a9b91-c6ce-4439-b3a4-4182c4c62a22 | Studio Vrij | 6300 | 2024-11-12 | deactivated | 2024-11-12 | 2024-11-12 01:53:36.983439 | 2024-10-27 38e27076-1c76-43de-b5d7-c3228e334cd6 | Fennel & Flint | 6376 | 2024-11-14 | deactivated | 2024-11-14 | 2024-11-14 09:26:35.631901 | 2024-10-27 a6d8af68-148a-46e1-9f4d-0c3c16f523b6 | Willow & Wren | 5765 | 2024-11-18 | deactivated | 2024-11-18 | 2024-11-18 16:59:10.993432 | 2024-05-21 6e8fb6f6-1c2f-4219-aad4-d62e5e4f39f6 | Bijoux Lune | 5736 | 2024-11-22 | deactivated | 2024-11-22 | 2024-11-22 16:01:01.18748 | 2024-10-02 642c8a31-9dda-44cc-b8b7-95c39e0c5bff | Fennel & Flint | 7406 | 2025-01-04 | deactivated | 2025-01-04 | 2025-01-04 01:32:19.319491 | 2024-12-15 fc8c1dbc-10e7-468b-84d3-618415e5305a | Marrowby & Vane | 412 | 2025-01-10 | deactivated | 2025-01-10 | 2025-01-10 06:27:09.523289 | 2024-08-02 f0676f94-b6b8-42de-8f06-84f200c42a0b | Cragsley Outfitters | 8111 | 2025-01-31 | deactivated | 2025-01-31 | 2025-01-31 08:58:09.342157 | 2025-01-22 cc524f30-5be6-48a5-96b6-870e6d379aea | Marrowby & Vane | 6617 | 2025-03-29 | deactivated | 2025-03-29 | 2025-03-29 14:01:02.638735 | 2024-12-26 e3a5ef8b-e2e0-41b2-953a-838812131698 | Kontor24 Bürobedarf | 8617 | 2025-04-29 | deactivated | 2025-04-29 | 2025-04-29 08:58:11.182361 | 2025-12-19 80a25dc8-3daa-43f2-b1ee-999985f0f6e5 | Cragsley Outfitters | 8596 | 2025-06-09 | deactivated | 2025-06-09 | 2025-06-09 17:56:47.07364 | 2025-05-21 dc5213a1-e7c7-4882-853b-c8c29593e0da | Paperplane Press | 7512 | 2025-07-09 | deactivated | 2025-07-09 | 2025-07-09 03:45:33.382347 | 2025-01-09 7be56ee5-ad97-46ea-8777-b3ca09d38a57 | Coastline Interiors | 9683 | 2025-08-29 | deactivated | 2025-08-29 | 2025-08-29 08:47:36.496317 | 2025-07-16 571b0925-08eb-47d0-a322-4d916b11c31f | Marrowby & Vane | 10182 | 2025-09-29 | deactivated | 2025-09-29 | 2025-09-29 23:34:39.692133 | 2025-09-08 e7a93251-9905-4243-bce4-d289a7b17144 | Marrowby & Vane | 6138 | 2025-11-10 | deactivated | 2025-11-10 | 2025-11-10 15:18:24.435098 | 2025-09-12 (39 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
select dsr.public_id as dsar_public_id, t.name as tenant_name, dsr.subject_user_id, cast(dsr.completed_at as date) as completed_at, dsr.subject_user_id AS subject_user_id, cast(dsr.completed_at as date) as completed_on, u.status as user_status, cast(u.deleted_at as date) as user_deleted_at, u.updated_at as user_updated_at, cast(u.last_login_at as date) as user_last_login_atfrom data_subject_requests dsrjoin tenants t on t.id = dsr.tenant_idjoin users u on u.tenant_id = dsr.tenant_id and u.id = dsr.subject_user_idwhere dsr.request_type = 'erasure' and dsr.status = 'completed'order by dsr.completed_at asc, dsr.public_id ascorder by dsr.completed_at asc NULLS LAST, dsr.public_id asc NULLS LAST
select dsr.public_id as dsar_public_id, t.name as tenant_name, dsr.subject_user_id AS subject_user_id, cast(dsr.completed_at as date) as completed_on, u.status as user_status, cast(u.deleted_at as date) as user_deleted_at, u.updated_at as user_updated_at, cast(u.last_login_at as date) as user_last_login_at from data_subject_requests dsr join tenants t on t.id = dsr.tenant_id join users u on u.tenant_id = dsr.tenant_id and u.id = dsr.subject_user_id where dsr.request_type = 'erasure' and dsr.status = 'completed' order by dsr.completed_at asc NULLS LAST, dsr.public_id 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: Does completed erasure actually deactivate the core user record? Question: When an erasure request is marked completed, is the underlying users row actually deactivated or anonymized, or does the account remain fully active? Grain: one row per completed erasure request (request_type='erasure', status='completed', 39 rows) Columns: - dsar public_id - tenant name - subject_user_id - completed_at (date) - user status (users.status) - user deleted_at (date, or blank if null) - user updated_at (most recent modification to the user row) - user last_login_at (date, or blank if null) Details: Source: data_subject_requests (request_type='erasure', status='completed') joined to users on tenant_id + id = subject_user_id. Do not pull email/full_name (avoid printing PII we don't need for this check) — status, deleted_at, updated_at and last_login_at are enough to tell whether the fulfillment process ever touches the primary account record at all, versus just closing the request ticket. Order by completed_at ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect dsr.public_id as dsar_public_id, t.name as tenant_name, dsr.subject_user_id, cast(dsr.completed_at as date) as completed_at, u.status as user_status, cast(u.deleted_at as date) as user_deleted_at, u.updated_at as user_updated_at, cast(u.last_login_at as date) as user_last_login_at from data_subject_requests dsr join tenants t on t.id = dsr.tenant_id join users u on u.tenant_id = dsr.tenant_id and u.id = dsr.subject_user_id where dsr.request_type = 'erasure' and dsr.status = 'completed' order by dsr.completed_at asc, dsr.public_id asc
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: dsar_public_id, tenant_name, subject_user_id, completed_at, user_status, user_deleted_at, user_updated_at, user_last_login_at
submitselect dsr.public_id as dsar_public_id, t.name as tenant_name, dsr.subject_user_id, cast(dsr.completed_at as date) as completed_at, u.status as user_status, cast(u.deleted_at as date) as user_deleted_at, u.updated_at as user_updated_at, cast(u.last_login_at as date) as user_last_login_at from data_subject_requests dsr join tenants t on t.id = dsr.tenant_id join users u on u.tenant_id = dsr.tenant_id and u.id = dsr.subject_user_id where dsr.request_type = 'erasure' and dsr.status = 'completed' order by dsr.completed_at asc, dsr.public_id asc
Submitted to tonight's batch. Your work on this request is done.