DB Olympics

Queries · q326 · Data Protection Officer · week 2, night 2

Pre-2023 data subject requests: backfill or data quality issue?

“Are the data subject requests received before 2023-01-01 legitimate historical/backfilled compliance records, or a data quality artifact?”

Manager · asked the questionInes LaurentData Protection Officer. Lawyer by training, previously at a European marketplace. Also reports to the board's audit committee.
Analyst · planned the reportPriyanka DesaiAuditor's mindset: traces individual records end to end to check that the totals are real.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

    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 2, request 1 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.

    Pre-2023 data subject requests: backfill or data quality issue?
    Question
    Are the data subject requests received before 2023-01-01 legitimate historical/backfilled compliance records, or a data quality artifact?
    One row per
    data_subject_requests record with received_at before 2023-01-01
    Columns
    • public_id
    • tenant name
    • request_type
    • status
    • received_at (timestamp)
    • completed_at (timestamp, blank if none)
    • created_at (when this row was written into our system)
    • days between received_at and created_at
    • notes (truncated to 200 chars)

  4. Bea writes the SQL

    The engineer · 1 dry run on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    SELECT
  t0.public_id AS public_id,
  t1.name AS tenant_name,
  t0.request_type AS request_type,
  t0.status AS status,
  t0.received_at AS received_at,
  t0.completed_at AS completed_at,
  t0.created_at AS created_at,
  ROUND(EXTRACT(EPOCH FROM (t0.created_at - t0.received_at)) / 86400.0, 1) AS days_received_to_created,
  LEFT(COALESCE(t0.notes, ''), 200) AS notes
FROM data_subject_requests t0
JOIN tenants t1 ON t1.id = t0.tenant_id
WHERE t0.received_at < DATE '2023-01-01'
ORDER BY t0.received_at ASC, t0.public_id ASC

    “Compiles cleanly. Submitting.”

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

    “Submitted. The report returns every data_subject_requests row with received_at < 2023-01-01, ordered by received_at, with days_received_to_created as the key diagnostic: a large positive value indicates the row was written well after the historical date (consistent with a legitimate migration backfill), while a near-zero value would flag it as likely a data quality artifact.”

  5. The report comes back overnight

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

    public_id                            | tenant_name         | request_type  | status    | received_at                | completed_at               | created_at                 | days_received_to_created | notes                                   
-------------------------------------+---------------------+---------------+-----------+----------------------------+----------------------------+----------------------------+--------------------------+-----------------------------------------
0bf5fe16-9c96-4a01-9861-ba58a8c62338 | Grind Collective    | erasure       | completed | 2020-04-24 22:06:57.286184 | 2020-04-28 19:22:51.144924 | 2020-04-24 22:06:57.286184 |                      0.0 |                                         
4531b08c-8444-4f57-aed1-451f6cbf1fed | Ampfeld Elektronik  | erasure       | completed | 2021-01-23 02:55:03.615711 | 2021-02-15 17:08:35.966168 | 2021-01-23 02:55:03.615711 |                      0.0 |                                         
ddc3341d-2a13-41d3-b194-d7f512b94132 | Marrowby & Vane     | erasure       | completed | 2021-04-11 23:28:00.777156 | 2021-04-16 02:45:58.630788 | 2021-04-11 23:28:00.777156 |                      0.0 |                                         
81cb284d-fa6d-4e79-a51f-fdc9751181c9 | Linden & Loom       | rectification | completed | 2021-09-23 02:23:50.083272 | 2021-11-10 23:23:48.284517 | 2021-09-23 02:23:50.083272 |                      0.0 | Customer asked to correct their name and
74bec8f7-3b6e-4350-8535-caee73c055e1 | Marrowby & Vane     | erasure       | completed | 2021-10-14 04:22:31.788939 | 2021-10-16 12:27:24.652039 | 2021-10-14 04:22:31.788939 |                      0.0 |                                         
df4dc623-ddb6-4508-81af-ee136635850b | Cragsley Outfitters | erasure       | completed | 2022-01-31 05:23:11.927364 | 2022-02-05 01:44:35.9245   | 2022-01-31 05:23:11.927364 |                      0.0 |                                         
cfddd972-efd4-4480-9ab3-12a867c46b79 | Coastline Interiors | erasure       | completed | 2022-05-31 18:23:29.874953 | 2022-06-03 15:01:02.294238 | 2022-05-31 18:23:29.874953 |                      0.0 |                                         
bedcac86-0871-457a-82e8-900fbc81b15b | Coastline Interiors | rectification | completed | 2022-07-17 05:06:49.81761  | 2022-07-21 08:14:49.470638 | 2022-07-17 05:06:49.81761  |                      0.0 | Customer asked to correct their name and
f9ddeb61-8a65-4ffc-8a99-2d6746cab8ee | Wiesgrund Wohnen    | erasure       | completed | 2022-08-15 14:29:36.076473 | 2022-08-26 12:47:21.258026 | 2022-08-15 14:29:36.076473 |                      0.0 |                                         
2554d690-68f1-4e5f-a5bd-aa9ecd805f5b | Midland Safety Wear | erasure       | completed | 2022-08-25 20:01:46.884542 | 2022-09-02 12:46:29.129135 | 2022-08-25 20:01:46.884542 |                      0.0 |                                         
9d2b79e8-cda0-4cb2-9d5c-12b4629b1428 | Brennquell Kaffee   | access        | completed | 2022-08-25 22:06:26.754811 | 2022-08-27 13:04:32.510653 | 2022-08-25 22:06:26.754811 |                      0.0 |                                         
1ad45648-063f-4e1f-b4dc-374c76223fb3 | Brennquell Kaffee   | access        | completed | 2022-11-20 04:39:40.390136 | 2022-12-16 13:57:08.602069 | 2022-11-20 04:39:40.390136 |                      0.0 |                                         
(12 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:

    It builds on:

  7. It becomes benchmark query q326

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

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
    • placeholderseconds between
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    The benchmark text, against the submitted SQL
    SELECT  t0.public_id AS public_id,  t1.name AS tenant_name,  t0.request_type AS request_type,  t0.status AS status,  t0.received_at AS received_at,  t0.completed_at AS completed_at,  t0.created_at AS created_at,  ROUND(EXTRACT(EPOCH FROM (t0.created_at - t0.received_at)) / 86400.0, 1) AS days_received_to_created,  {{seconds_between(t0.received_at, t0.created_at)}} / 86400.0 AS days_received_to_created,  LEFT(COALESCE(t0.notes, ''), 200) AS notesFROM data_subject_requests t0JOIN tenants t1 ON t1.id = t0.tenant_idWHERE t0.received_at < DATE '2023-01-01'ORDER BY t0.received_at ASC, t0.public_id ASCORDER BY t0.received_at ASC NULLS LAST, t0.public_id ASC NULLS LAST

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

    SELECT
  t0.public_id AS public_id,
  t1.name AS tenant_name,
  t0.request_type AS request_type,
  t0.status AS status,
  t0.received_at AS received_at,
  t0.completed_at AS completed_at,
  t0.created_at AS created_at,
  CAST(EXTRACT(EPOCH FROM (t0.created_at - t0.received_at)) AS NUMERIC(18,6)) / 86400.0 AS days_received_to_created,
  LEFT(COALESCE(t0.notes, ''), 200) AS notes
FROM data_subject_requests t0
JOIN tenants t1 ON t1.id = t0.tenant_id
WHERE t0.received_at < DATE '2023-01-01'
ORDER BY t0.received_at ASC NULLS LAST, t0.public_id ASC NULLS LAST
    10 ms100 msHyper14.9 msDuckDB12.0 msGizmoSQL13.8 msclickhouse_brew34.4 msFirebolt23.6 msDataFusion35.7 msClickHouse16.8 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q326 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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Priyanka Desai (analyst for the Data Protection Officer):

Title: Pre-2023 data subject requests: backfill or data quality issue?
Question: Are the data subject requests received before 2023-01-01 legitimate historical/backfilled compliance records, or a data quality artifact?
Grain: one row per data_subject_requests record with received_at before 2023-01-01
Columns:
- public_id
- tenant name
- request_type
- status
- received_at (timestamp)
- completed_at (timestamp, blank if none)
- created_at (when this row was written into our system)
- days between received_at and created_at
- notes (truncated to 200 chars)
Details: Source: data_subject_requests filtered to received_at < '2023-01-01', platform-wide, full history. The key diagnostic is the gap between received_at and created_at: a large gap (row created well after 2023, e.g. during a migration) supports 'legitimate backfill with original historical date preserved'; a near-zero gap would be suspicious since the new platform/warehouse history is stated to start 2023-01-01. Order by received_at ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      SELECT
  t0.public_id AS public_id,
  t1.name AS tenant_name,
  t0.request_type AS request_type,
  t0.status AS status,
  t0.received_at AS received_at,
  t0.completed_at AS completed_at,
  t0.created_at AS created_at,
  ROUND(EXTRACT(EPOCH FROM (t0.created_at - t0.received_at)) / 86400.0, 1) AS days_received_to_created,
  LEFT(COALESCE(t0.notes, ''), 200) AS notes
FROM data_subject_requests t0
JOIN tenants t1 ON t1.id = t0.tenant_id
WHERE t0.received_at < DATE '2023-01-01'
ORDER BY t0.received_at ASC, t0.public_id ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: public_id, tenant_name, request_type, status, received_at, completed_at, created_at, days_received_to_created, notes
    • BeaCompiles cleanly. Submitting.
    • Bea calls submit
      SELECT
  t0.public_id AS public_id,
  t1.name AS tenant_name,
  t0.request_type AS request_type,
  t0.status AS status,
  t0.received_at AS received_at,
  t0.completed_at AS completed_at,
  t0.created_at AS created_at,
  ROUND(EXTRACT(EPOCH FROM (t0.created_at - t0.received_at)) / 86400.0, 1) AS days_received_to_created,
  LEFT(COALESCE(t0.notes, ''), 200) AS notes
FROM data_subject_requests t0
JOIN tenants t1 ON t1.id = t0.tenant_id
WHERE t0.received_at < DATE '2023-01-01'
ORDER BY t0.received_at ASC, t0.public_id ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report returns every data_subject_requests row with `received_at < 2023-01-01`, ordered by received_at, with `days_received_to_created` as the key diagnostic: a large positive value indicates the row was written well after the historical date (consistent with a legitimate migration backfill), while a near-zero value would flag it as likely a data quality artifact.

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

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