Queries · q323 · Data Protection Officer · week 2, night 1
“What is the defensible, auditable count of privacy (DSAR) requests by type and status, so we can reconcile against the ~48 figure quoted elsewhere and the 60 we found last week before briefing the regulator?”
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 1, request 1 of 3
Ines said to lead with priorities 2 and 4 if slots are tight, so tonight is entirely those two. For priority 2, I'm not just re-stating last week's 60-request total — Claire Dubois (Risk & Payments) posted ~48 requests company-wide in the same channel, which doesn't match my count, so before anything goes to the regulator I need an auditable type-x-status breakdown that either reconciles the two numbers or pinpoints exactly where they diverge. I'm also treating "zero portability requests ever" as a hypothesis to test rather than a fact: since data_subject_requests.data_export_id links to data_exports, I can check whether export jobs are actually being run for shoppers outside the formal DSAR table — if so, that's a logging blind spot, not a true zero. For priority 4, last week I only checked 2 of the 6 policy-covered tables (email_log confirmed broken, audit_log mostly fine with a narrow gap); tonight I'm closing out the remaining four (events, job_runs, page_views, system_health_checks) at a compact one-row-per-table grain so Ines has the full current picture of "what persists past its stated window," not just the one confirmed violation. I'm deferring the legal-hold evidence question (priority 1), the consent anomaly (priority 3), and Henrik's three asks to night 2, since those need either external evidence (disputes) or more design thought (what "touches security data" means for a request) that I don't want to rush tonight.
The engineer · 1 dry run on the empty schema, then the submission
SELECT request_type, status, count(*) AS request_count, min(received_at)::date AS earliest_received_at, max(received_at)::date AS latest_received_at, round( percentile_cont(0.5) WITHIN GROUP ( ORDER BY extract(epoch FROM (completed_at - received_at)) / 86400.0 ) FILTER (WHERE completed_at IS NOT NULL), 1 ) AS median_days_to_close FROM data_subject_requests GROUP BY request_type, status ORDER BY request_type, status
“Query compiles cleanly with the right columns. Submitting now.”
“Submitted. The report groups data_subject_requests by (request_type, status) exactly as stored, with count, earliest/latest received_at, and median days-to-close (NULL where no rows have completed_at) — full history, no tenant filter, ordered by type then status so Priyanka can trace how the ~48 and ~60 figures reconcile against the ground truth.”
As Priyanka Desai saw it, from the small dataset the agents worked on
request_type | status | request_count | earliest_received_at | latest_received_at | median_days_to_close --------------+-------------+---------------+----------------------+--------------------+--------------------- access | completed | 11 | 2022-08-25 | 2025-09-01 | 5.8 access | in_progress | 1 | 2025-12-29 | 2025-12-29 | NULL erasure | completed | 39 | 2020-04-24 | 2025-10-18 | 8.1 erasure | received | 1 | 2025-12-06 | 2025-12-06 | NULL erasure | rejected | 3 | 2023-05-26 | 2025-03-21 | NULL rectification | completed | 5 | 2021-09-23 | 2024-06-26 | 13.9 (6 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 request_type, status, count(*) AS request_count, min(received_at)::date AS earliest_received_at, max(received_at)::date AS latest_received_at, round( percentile_cont(0.5) WITHIN GROUP ( ORDER BY extract(epoch FROM (completed_at - received_at)) / 86400.0 ) FILTER (WHERE completed_at IS NOT NULL), 1 ) AS median_days_to_close CAST(min(received_at) AS date) AS earliest_received_at, CAST(max(received_at) AS date) AS latest_received_at, {{percentile_cont_filter(0.5, {{seconds_between(received_at, completed_at)}} / 86400.0, completed_at IS NOT NULL)}} AS median_days_to_closeFROM data_subject_requestsGROUP BY request_type, statusORDER BY request_type, statusORDER BY request_type NULLS LAST, status NULLS LAST
SELECT request_type, status, count(*) AS request_count, CAST(min(received_at) AS date) AS earliest_received_at, CAST(max(received_at) AS date) AS latest_received_at, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (completed_at - received_at)) AS NUMERIC(18,6)) / 86400.0) FILTER (WHERE completed_at IS NOT NULL) AS median_days_to_close FROM data_subject_requests GROUP BY request_type, status ORDER BY request_type NULLS LAST, status NULLS LAST
SELECT request_type, status, count(*) AS request_count, CAST(min(received_at) AS date) AS earliest_received_at, CAST(max(received_at) AS date) AS latest_received_at, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN completed_at IS NOT NULL THEN CAST(EXTRACT(EPOCH FROM (completed_at - received_at)) AS NUMERIC(18,6)) / 86400.0 END) AS median_days_to_close FROM data_subject_requests GROUP BY request_type, status ORDER BY request_type NULLS LAST, status NULLS LAST
SELECT request_type, status, count(*) AS request_count, CAST(min(received_at) AS date) AS earliest_received_at, CAST(max(received_at) AS date) AS latest_received_at, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(CAST(EXTRACT(EPOCH FROM (completed_at - received_at)) AS NUMERIC(18,6)) / 86400.0 AS DOUBLE PRECISION)) FILTER (WHERE completed_at IS NOT NULL) AS median_days_to_close FROM data_subject_requests GROUP BY request_type, status ORDER BY request_type NULLS LAST, status NULLS LAST
SELECT request_type, status, count(*) AS request_count, CAST(min(received_at) AS date) AS earliest_received_at, CAST(max(received_at) AS date) AS latest_received_at, quantileExactInclusiveIf(0.5)(toFloat64(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', received_at, completed_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0), completed_at IS NOT NULL) AS median_days_to_close FROM data_subject_requests GROUP BY request_type, status ORDER BY request_type NULLS LAST, status 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: Privacy request volume reconciliation: counts by type and status Question: What is the defensible, auditable count of privacy (DSAR) requests by type and status, so we can reconcile against the ~48 figure quoted elsewhere and the 60 we found last week before briefing the regulator? Grain: one row per (request_type, status) combination, across full history, all tenants Columns: - request_type - status - count - earliest received_at (date) - latest received_at (date) - median days to close (completed_at minus received_at, in days, for rows where completed_at is not null; blank if none) Details: Source: data_subject_requests, no date or tenant filter (full history, platform-wide, matching what last week's reports covered). Group by request_type and status exactly as stored (do not normalize casing) so we can see if any unexpected type/status strings exist. This is meant to be the single ground-truth table that explains how other reported totals (e.g. Risk & Payments' ~48) might be a subset of ours (e.g. excluding a status or request_type) — order by request_type, then status, so all rows for a type are grouped together. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT request_type, status, count(*) AS request_count, min(received_at)::date AS earliest_received_at, max(received_at)::date AS latest_received_at, round( percentile_cont(0.5) WITHIN GROUP ( ORDER BY extract(epoch FROM (completed_at - received_at)) / 86400.0 ) FILTER (WHERE completed_at IS NOT NULL), 1 ) AS median_days_to_close FROM data_subject_requests GROUP BY request_type, status ORDER BY request_type, status
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: request_type, status, request_count, earliest_received_at, latest_received_at, median_days_to_close
submitSELECT request_type, status, count(*) AS request_count, min(received_at)::date AS earliest_received_at, max(received_at)::date AS latest_received_at, round( percentile_cont(0.5) WITHIN GROUP ( ORDER BY extract(epoch FROM (completed_at - received_at)) / 86400.0 ) FILTER (WHERE completed_at IS NOT NULL), 1 ) AS median_days_to_close FROM data_subject_requests GROUP BY request_type, status ORDER BY request_type, status
Submitted to tonight's batch. Your work on this request is done.