Queries · q327 · Data Protection Officer · week 2, night 2
“Is 'zero completed data exports' real, or is 'completed' simply the wrong status string for this table — and are shoppers who filed access requests actually receiving their data?”
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 2, request 2 of 3
Two things from last night need resolving before I trust the numbers for the regulator: (1) report 1 showed DSAR received_at dates as early as 2020-04-24 and 2022-08-25 — before the stated 2023-01-01 warehouse history start — which could be legitimate backfilled compliance records carried over from the pre-migration system, or could be a data quality artifact; I need to see these specific rows and compare received_at to created_at to tell which. (2) report 2 showed all 11 data_exports linked to a DSAR, but completed_export_count was 0, and the engineer flagged uncertainty about whether 'completed' is even the right status string for that table — if genuinely zero exports ever finished, that's a real fulfillment failure (shoppers who filed access requests may never have actually received their data), separate from the "portability doesn't exist as a request_type" question I already closed. Both are quick, cheap diagnostics. With priorities 2 and 4 now largely resolved (volumes reconcile to 60 pending the pre-2023 check; retention is fine everywhere except email_log, confirmed last night), I'm using my third slot to start priority 1 for Ines: looking for whatever internal evidence exists (payment status, support ticket activity) on the 3 held accounts, since there's no explicit "dispute" table and the legal holds aren't linked to a specific order/payment — I have to reconstruct it from the subject_user_id.
The engineer · 1 dry run on the empty schema, then the submission
SELECT status, COUNT(*) AS export_count, MIN(requested_at) AS earliest_requested_at, MAX(requested_at) AS latest_requested_at, COUNT(completed_at) AS count_with_completed_at, COUNT(file_url) AS count_with_file_url, ROUND(AVG(row_count) FILTER (WHERE row_count IS NOT NULL), 2) AS avg_row_count FROM data_exports GROUP BY status ORDER BY export_count DESC, status ASC
“Query compiles cleanly.”
“Submitted. The report groups data_exports by its literal status value (no assumption about which string means "done"), with counts, date range, and how often completed_at/file_url are actually populated per status — which will directly show whether "completed" is just a naming mismatch or a real fulfillment gap.”
As Priyanka Desai saw it, from the small dataset the agents worked on
status | export_count | earliest_requested_at | latest_requested_at | count_with_completed_at | count_with_file_url | avg_row_count --------+--------------+----------------------------+----------------------------+-------------------------+---------------------+-------------- expired | 11 | 2022-08-25 23:06:26.754811 | 2025-09-01 06:56:20.570772 | 11 | 11 | 1456.09 (1 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
SELECT status, COUNT(*) AS export_count, MIN(requested_at) AS earliest_requested_at, MAX(requested_at) AS latest_requested_at, COUNT(completed_at) AS count_with_completed_at, COUNT(file_url) AS count_with_file_url, ROUND(AVG(row_count) FILTER (WHERE row_count IS NOT NULL), 2) AS avg_row_count AVG(CAST(row_count AS NUMERIC(18,6))) FILTER (WHERE row_count IS NOT NULL) AS avg_row_countFROM data_exportsGROUP BY statusORDER BY export_count DESC, status ASCORDER BY export_count DESC NULLS FIRST, status ASC NULLS LAST
SELECT status, COUNT(*) AS export_count, MIN(requested_at) AS earliest_requested_at, MAX(requested_at) AS latest_requested_at, COUNT(completed_at) AS count_with_completed_at, COUNT(file_url) AS count_with_file_url, AVG(CAST(row_count AS NUMERIC(18,6))) FILTER (WHERE row_count IS NOT NULL) AS avg_row_count FROM data_exports GROUP BY status ORDER BY export_count 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: Data export fulfillment audit: are access/portability exports actually completing? Question: Is 'zero completed data exports' real, or is 'completed' simply the wrong status string for this table — and are shoppers who filed access requests actually receiving their data? Grain: one row per data_exports.status value Columns: - status - count - earliest requested_at - latest requested_at - count with completed_at not null - count with file_url not null (artifact actually produced) - average row_count (for rows where row_count is not null) Details: Source: data_exports, platform-wide, full history (all 11 rows from last night's linked set, in case there are more unlinked ones this surfaces as well). Group by status exactly as stored. This resolves whether last night's completed_export_count = 0 was a wrong assumption about the status string, or whether it's genuinely true that no export has ever been marked complete — in which case we need to check whether file_url/completed_at tell a different, more reassuring story. Order by count descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT status, COUNT(*) AS export_count, MIN(requested_at) AS earliest_requested_at, MAX(requested_at) AS latest_requested_at, COUNT(completed_at) AS count_with_completed_at, COUNT(file_url) AS count_with_file_url, ROUND(AVG(row_count) FILTER (WHERE row_count IS NOT NULL), 2) AS avg_row_count FROM data_exports GROUP BY status ORDER BY export_count DESC, status ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: status, export_count, earliest_requested_at, latest_requested_at, count_with_completed_at, count_with_file_url, avg_row_count
submitSELECT status, COUNT(*) AS export_count, MIN(requested_at) AS earliest_requested_at, MAX(requested_at) AS latest_requested_at, COUNT(completed_at) AS count_with_completed_at, COUNT(file_url) AS count_with_file_url, ROUND(AVG(row_count) FILTER (WHERE row_count IS NOT NULL), 2) AS avg_row_count FROM data_exports GROUP BY status ORDER BY export_count DESC, status ASC
Submitted to tonight's batch. Your work on this request is done.