Queries · q324 · Data Protection Officer · week 2, night 1

Portability requests: formal DSAR count vs. unlinked data exports

“Is 'zero portability requests in 3 years' a real zero, or are shopper data exports being fulfilled outside the formal DSAR process (a logging blind spot)?”

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

1. A business event lands on the Data Protection Officer's desk

   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.

   One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events

2. Ines Laurent writes to Priyanka Desai

   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:

   • The three "open" erasure requests aren't really open, they're parked behind legal holds — but we don't actually know if those holds are still justified. If a regulator asked us today why those erasures haven't happened, "disputes" that are 1.5–2.6 years old is not a comfortable answer without evidence.
   • We have a closure-tracking gap (rejected requests never get a completion date) and apparently zero portability requests in three years. Both could be true, or both could be an artifact of how we log things — and I don't want to hand the regulator a number I can't stand behind.
   • The email retention failure is concrete and fixable, but it also means I can't yet answer "what remains after an account is deleted" with confidence for other categories until we know how widespread this is.
   • The consent anomaly (one category showing suspiciously perfect opt-in) undermines my confidence in saying "this is how consent is recorded" to a regulator until it's explained.

   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:

   1. For the three long-running legal holds, can we establish — from whatever evidence exists — whether the underlying disputes are still active, so I can decide whether to push for release or document why they must stand?
   2. Can we get a trustworthy, defensible count of privacy requests received and resolved, including the rejected ones and whether "zero portability requests ever" is real or a logging blind spot — the regulator will ask for exactly this?
   3. How confident can we be in what consent records actually show, given the anomaly you flagged — do we have other categories with the same too-clean pattern, and does that change how I'd describe our consent process to a regulator?
   4. Beyond email, how much shopper data elsewhere is sitting past its stated retention window right now — I need a true current picture of "what remains after deletion," not just the one confirmed violation.

   If all four can't fit, start with 2 and 4 — those go directly into the regulator response.

   Thanks, Ines

   Show the whole note
   A note from the Chief Information Security Officer, Henrik Aalto

   Subject: Security angle for the DPA response

   Hi Priyanka,

   A few things from my side that would strengthen the privacy response:

   1. When a data subject request results in erasure, can we show what login/session and access history remains afterward, and for how long — i.e. do our retention rules actually clear it out, or does it linger?
   2. For consent withdrawals, do we have any evidence that we stop using/collecting the related data promptly (e.g. no further sign-ins or sensitive-data access tied to that account after withdrawal)?
   3. On the completion-time question the DPA is asking about — can we also show whether requests touching account security data (sessions, API/service credentials, access logs) take longer to close than others? That's often where delays hide.

   Happy to compare notes before you send anything to data engineering.

   Henrik

3. Priyanka Desai plans the night

   The analyst · week 2, night 1, request 2 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.

   Read the whole plan
   Portability requests: formal DSAR count vs. unlinked data exports

   Question

   Is 'zero portability requests in 3 years' a real zero, or are shopper data exports being fulfilled outside the formal DSAR process (a logging blind spot)?

   One row per

   linked-to-DSAR status (2 rows: linked, not linked)

   Columns

   • linked to a data_subject_requests record (Y/N)
   • export count
   • distinct subject users (count of distinct subject_user_id)
   • earliest requested_at (date)
   • latest requested_at (date)
   • completed export count (status indicating the export finished)
   • average row_count (for exports where row_count is not null)

4. Ines writes the SQL

   The engineer · 2 dry runs on the empty schema, then the submission

   Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

   SELECT
     CASE WHEN dsr.id IS NOT NULL THEN 'Y' ELSE 'N' END AS linked_to_dsar,
     COUNT(*) AS export_count,
     COUNT(DISTINCT de.subject_user_id) AS distinct_subject_users,
     MIN(de.requested_at) AS earliest_requested_at,
     MAX(de.requested_at) AS latest_requested_at,
     COUNT(*) FILTER (WHERE de.status = 'completed') AS completed_export_count,
     AVG(de.row_count) FILTER (WHERE de.row_count IS NOT NULL) AS avg_row_count
   FROM data_exports de
   LEFT JOIN data_subject_requests dsr ON dsr.data_export_id = de.id
   GROUP BY 1
   ORDER BY linked_to_dsar ASC

   Dry run 2 · 3 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

   SELECT  CASE WHEN dsr.id IS NOT NULL THEN 'Y' ELSE 'N' END AS linked_to_dsar,  COUNT(*) AS export_count,  COUNT(DISTINCT de.subject_user_id) AS distinct_subject_users,  MIN(de.requested_at) AS earliest_requested_at,  MAX(de.requested_at) AS latest_requested_at,  MIN(CAST(de.requested_at AS date)) AS earliest_requested_at,  MAX(CAST(de.requested_at AS date)) AS latest_requested_at,  COUNT(*) FILTER (WHERE de.status = 'completed') AS completed_export_count,  AVG(de.row_count) FILTER (WHERE de.row_count IS NOT NULL) AS avg_row_count  ROUND(AVG(de.row_count) FILTER (WHERE de.row_count IS NOT NULL), 1) AS avg_row_countFROM data_exports deLEFT JOIN data_subject_requests dsr ON dsr.data_export_id = de.idGROUP BY 1ORDER BY linked_to_dsar ASCORDER BY linked_to_dsar DESC

   “This query compiles correctly and matches the requested grain, columns, and ordering (Y before N).”

   Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

   “Submitted. One note for Priyanka: the catalog doesn't enumerate fixed status codes for data_exports.status, so I used 'completed' as the "finished" indicator — flagged this in the submission note in case the warehouse uses a different literal value.”

5. The report comes back overnight

   As Priyanka Desai saw it, from the small dataset the agents worked on

   linked_to_dsar | export_count | distinct_subject_users | earliest_requested_at | latest_requested_at | completed_export_count | avg_row_count
   ---------------+--------------+------------------------+-----------------------+---------------------+------------------------+--------------
   Y              |           11 |                     11 | 2022-08-25            | 2025-09-01          |                      0 |        1456.1
   (1 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q327Data export fulfillment audit: are access/portability exports actually completing?

   It builds on:

   • q112Data subject requests: monthly volume, closure time, and overdue backlog

7. It becomes benchmark query q324

   Changed only where the benchmark needs the same result on every system

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
   The benchmark text, against the submitted SQL

   SELECT  CASE WHEN dsr.id IS NOT NULL THEN 'Y' ELSE 'N' END AS linked_to_dsar,  COUNT(*) AS export_count,  COUNT(DISTINCT de.subject_user_id) AS distinct_subject_users,  MIN(CAST(de.requested_at AS date)) AS earliest_requested_at,  MAX(CAST(de.requested_at AS date)) AS latest_requested_at,  COUNT(*) FILTER (WHERE de.status = 'completed') AS completed_export_count,  ROUND(AVG(de.row_count) FILTER (WHERE de.row_count IS NOT NULL), 1) AS avg_row_count  AVG(CAST(de.row_count AS NUMERIC(18,6))) FILTER (WHERE de.row_count IS NOT NULL) AS avg_row_countFROM data_exports deLEFT JOIN data_subject_requests dsr ON dsr.data_export_id = de.idGROUP BY 1ORDER BY linked_to_dsar DESCORDER BY linked_to_dsar DESC NULLS FIRST

   As each system received it; highlighted, the parts spelled for that system

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   SELECT
     CASE WHEN dsr.id IS NOT NULL THEN 'Y' ELSE 'N' END AS linked_to_dsar,
     COUNT(*) AS export_count,
     COUNT(DISTINCT de.subject_user_id) AS distinct_subject_users,
     MIN(CAST(de.requested_at AS date)) AS earliest_requested_at,
     MAX(CAST(de.requested_at AS date)) AS latest_requested_at,
     COUNT(*) FILTER (WHERE de.status = 'completed') AS completed_export_count,
     AVG(CAST(de.row_count AS NUMERIC(18,6))) FILTER (WHERE de.row_count IS NOT NULL) AS avg_row_count
   FROM data_exports de
   LEFT JOIN data_subject_requests dsr ON dsr.data_export_id = de.id
   GROUP BY 1
   ORDER BY linked_to_dsar DESC NULLS FIRST

   Scale factor 1000, c8a.24xlarge, after the last load. q324 on the start page, with every configuration.

8. Everything, as recorded

   The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

   Ines's whole session, 9 turns

   • The request, as Ines received it

     Report request from Priyanka Desai (analyst for the Data Protection Officer):
     
     Title: Portability requests: formal DSAR count vs. unlinked data exports
     Question: Is 'zero portability requests in 3 years' a real zero, or are shopper data exports being fulfilled outside the formal DSAR process (a logging blind spot)?
     Grain: one row per linked-to-DSAR status (2 rows: linked, not linked)
     Columns:
     - linked to a data_subject_requests record (Y/N)
     - export count
     - distinct subject users (count of distinct subject_user_id)
     - earliest requested_at (date)
     - latest requested_at (date)
     - completed export count (status indicating the export finished)
     - average row_count (for exports where row_count is not null)
     Details: Source: data_exports LEFT JOIN data_subject_requests ON data_subject_requests.data_export_id = data_exports.id. 'Linked' = a matching data_subject_requests row exists (regardless of that row's request_type); 'not linked' = no such row. Platform-wide, full history, no date filter. If the 'not linked' row has a meaningful count, that indicates shopper data exports happen outside the DSAR table entirely, which would mean request_type='portability' showing 0 in data_subject_requests is a process/logging gap rather than genuine zero demand. Order by linked_to_dsar (Y first, then N).
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Ines calls dry_run

     SELECT
       CASE WHEN dsr.id IS NOT NULL THEN 'Y' ELSE 'N' END AS linked_to_dsar,
       COUNT(*) AS export_count,
       COUNT(DISTINCT de.subject_user_id) AS distinct_subject_users,
       MIN(de.requested_at) AS earliest_requested_at,
       MAX(de.requested_at) AS latest_requested_at,
       COUNT(*) FILTER (WHERE de.status = 'completed') AS completed_export_count,
       AVG(de.row_count) FILTER (WHERE de.row_count IS NOT NULL) AS avg_row_count
     FROM data_exports de
     LEFT JOIN data_subject_requests dsr ON dsr.data_export_id = de.id
     GROUP BY 1
     ORDER BY linked_to_dsar ASC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: linked_to_dsar, export_count, distinct_subject_users, earliest_requested_at, latest_requested_at, completed_export_count, avg_row_count

   • Ines calls dry_run

     SELECT
       CASE WHEN dsr.id IS NOT NULL THEN 'Y' ELSE 'N' END AS linked_to_dsar,
       COUNT(*) AS export_count,
       COUNT(DISTINCT de.subject_user_id) AS distinct_subject_users,
       MIN(CAST(de.requested_at AS date)) AS earliest_requested_at,
       MAX(CAST(de.requested_at AS date)) AS latest_requested_at,
       COUNT(*) FILTER (WHERE de.status = 'completed') AS completed_export_count,
       ROUND(AVG(de.row_count) FILTER (WHERE de.row_count IS NOT NULL), 1) AS avg_row_count
     FROM data_exports de
     LEFT JOIN data_subject_requests dsr ON dsr.data_export_id = de.id
     GROUP BY 1
     ORDER BY linked_to_dsar DESC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: linked_to_dsar, export_count, distinct_subject_users, earliest_requested_at, latest_requested_at, completed_export_count, avg_row_count

   • InesThis query compiles correctly and matches the requested grain, columns, and ordering (Y before N).
   • Ines calls submit

     SELECT
       CASE WHEN dsr.id IS NOT NULL THEN 'Y' ELSE 'N' END AS linked_to_dsar,
       COUNT(*) AS export_count,
       COUNT(DISTINCT de.subject_user_id) AS distinct_subject_users,
       MIN(CAST(de.requested_at AS date)) AS earliest_requested_at,
       MAX(CAST(de.requested_at AS date)) AS latest_requested_at,
       COUNT(*) FILTER (WHERE de.status = 'completed') AS completed_export_count,
       ROUND(AVG(de.row_count) FILTER (WHERE de.row_count IS NOT NULL), 1) AS avg_row_count
     FROM data_exports de
     LEFT JOIN data_subject_requests dsr ON dsr.data_export_id = de.id
     GROUP BY 1
     ORDER BY linked_to_dsar DESC

   • The warehouse answers

     Submitted to tonight's batch. Your work on this request is done.

   • InesSubmitted. One note for Priyanka: the catalog doesn't enumerate fixed status codes for `data_exports.status`, so I used `'completed'` as the "finished" indicator — flagged this in the submission note in case the warehouse uses a different literal value.

   Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

   Ines's transcript (264 KB) · Priyanka Desai's transcript (894 KB) · Ines Laurent's transcript (263 KB) · the night's plan (5 KB) · the night's reports (4 KB) · the week's memo (3 KB)