Queries · q329 · Data Protection Officer · week 2, night 3

Consent purpose audit: full history and the analytics_cookies anomaly

“Across the full history, how many consent purposes do we actually track, and is the analytics_cookies 'always 100% opted in, zero withdrawals' pattern unique or does it appear elsewhere?”

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 3, request 1 of 3

   Two good resolutions from last night: the data_exports status audit shows all 11 exports have completed_at AND file_url populated — they did fulfill, they're just labeled 'expired' because the download link's retention window has since passed, so I'm closing out the "did access requests actually deliver data" concern. The pre-2023 DSAR rows show created_at exactly equal to received_at for all 12 — that's the opposite of what I'd expect from a deliberate migration backfill (where insertion would postdate the original event), so I can't confidently call this "verified legitimate backfill"; I'm flagging it as an open inconsistency with the documented 2023-01-01 history start that needs an answer from engineering, not more SQL from me. The legal-hold evidence (report 3) is useful but indirect: 2 of 3 accounts already show a partial refund 6–12 months ago with zero ticket activity since, suggesting the disputes may already be effectively resolved while the hold was never released — worth flagging to Ines as "likely stale, recommend review," with the caveat that I matched by "most recent payment for that user," not a guaranteed link to the specific disputed payment. With priorities 1, 2, and 4 now substantially covered, tonight I'm moving to the untouched items: priority 3 (consent reliability — is the analytics_cookies anomaly isolated or systemic?) and Henrik's two unaddressed security asks (what persists in sessions/logins/access_log after erasure; do we actually stop marketing to someone who withdrew consent). Henrik's third ask (closure time for security-data requests) still has no clean way to tag which DSARs touch security data, so I'm deferring it to the final night or flagging it as not currently answerable.

   Read the whole plan
   Consent purpose audit: full history and the analytics_cookies anomaly

   Question

   Across the full history, how many consent purposes do we actually track, and is the analytics_cookies 'always 100% opted in, zero withdrawals' pattern unique or does it appear elsewhere?

   One row per

   distinct consent_records.purpose value, full history, no date filter

   Columns

   • purpose
   • total records
   • distinct users
   • granted count
   • withdrawn count
   • expired count
   • pct of distinct users with at least one withdrawn or expired record ever
   • earliest occurred_at
   • latest occurred_at

4. Felix 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
     cr.purpose,
     COUNT(*) AS total_records,
     COUNT(DISTINCT cr.user_id) AS distinct_users,
     COUNT(*) FILTER (WHERE cr.status = 'granted') AS granted_count,
     COUNT(*) FILTER (WHERE cr.status = 'withdrawn') AS withdrawn_count,
     COUNT(*) FILTER (WHERE cr.status = 'expired') AS expired_count,
     ROUND(
       100.0 * (
         SELECT COUNT(DISTINCT sub.user_id)
         FROM consent_records sub
         WHERE sub.purpose = cr.purpose
           AND sub.status IN ('withdrawn', 'expired')
       ) / NULLIF(COUNT(DISTINCT cr.user_id), 0)
     , 2) AS pct_users_ever_withdrawn_or_expired,
     MIN(cr.occurred_at) AS earliest_occurred_at,
     MAX(cr.occurred_at) AS latest_occurred_at
   FROM consent_records cr
   GROUP BY cr.purpose
   ORDER BY total_records DESC, cr.purpose

   “Query compiles and matches the requested grain and columns. 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 groups consent_records by purpose across all time (no 2023–2025 restriction), giving total records, distinct users, granted/withdrawn/expired counts, the share of distinct users who ever had a withdrawn-or-expired record, and the earliest/latest occurred_at per purpose — directly answering whether only 2 purposes exist and whether the "never withdrawn" pattern is unique to analytics_cookies.”

5. The report comes back overnight

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

   purpose           | total_records | distinct_users | granted_count | withdrawn_count | expired_count | pct_users_ever_withdrawn_or_expired | earliest_occurred_at       | latest_occurred_at        
   ------------------+---------------+----------------+---------------+-----------------+---------------+-------------------------------------+----------------------------+---------------------------
   analytics_cookies |          7404 |           7404 |          7404 |               0 |             0 |                                 0.0 | 2019-01-19 10:05:46.410929 | 2025-12-31 22:53:16.158384
   marketing_email   |          4981 |           4550 |          4550 |             431 |             0 |                                9.47 | 2019-01-12 22:13:48.933265 | 2025-12-31 22:53:16.158384
   (2 rows)

6. What came of it

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

   It builds on:

   • q113Consent trend by purpose and month: grants, withdrawals, and net opt-in rate

7. It becomes benchmark query q329

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • made explicita selected t.col gets AS col, its standard name made explicit
   • 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  cr.purpose,  cr.purpose AS purpose,  COUNT(*) AS total_records,  COUNT(DISTINCT cr.user_id) AS distinct_users,  COUNT(*) FILTER (WHERE cr.status = 'granted') AS granted_count,  COUNT(*) FILTER (WHERE cr.status = 'withdrawn') AS withdrawn_count,  COUNT(*) FILTER (WHERE cr.status = 'expired') AS expired_count,  ROUND(    100.0 * (      SELECT COUNT(DISTINCT sub.user_id)      FROM consent_records sub      WHERE sub.purpose = cr.purpose        AND sub.status IN ('withdrawn', 'expired')    ) / NULLIF(COUNT(DISTINCT cr.user_id), 0)  , 2) AS pct_users_ever_withdrawn_or_expired,  100.0 * (    SELECT COUNT(DISTINCT sub.user_id)    FROM consent_records sub    WHERE sub.purpose = cr.purpose      AND sub.status IN ('withdrawn', 'expired')  ) / NULLIF(COUNT(DISTINCT cr.user_id), 0) AS pct_users_ever_withdrawn_or_expired,  MIN(cr.occurred_at) AS earliest_occurred_at,  MAX(cr.occurred_at) AS latest_occurred_atFROM consent_records crGROUP BY cr.purposeORDER BY total_records DESC, cr.purposeORDER BY total_records DESC NULLS FIRST, cr.purpose NULLS LAST

   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
     cr.purpose AS purpose,
     COUNT(*) AS total_records,
     COUNT(DISTINCT cr.user_id) AS distinct_users,
     COUNT(*) FILTER (WHERE cr.status = 'granted') AS granted_count,
     COUNT(*) FILTER (WHERE cr.status = 'withdrawn') AS withdrawn_count,
     COUNT(*) FILTER (WHERE cr.status = 'expired') AS expired_count,
     100.0 * (
       SELECT COUNT(DISTINCT sub.user_id)
       FROM consent_records sub
       WHERE sub.purpose = cr.purpose
         AND sub.status IN ('withdrawn', 'expired')
     ) / NULLIF(COUNT(DISTINCT cr.user_id), 0) AS pct_users_ever_withdrawn_or_expired,
     MIN(cr.occurred_at) AS earliest_occurred_at,
     MAX(cr.occurred_at) AS latest_occurred_at
   FROM consent_records cr
   GROUP BY cr.purpose
   ORDER BY total_records DESC NULLS FIRST, cr.purpose NULLS LAST

   • ClickHouserow 1: col 7: "0" vs ""

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

   Felix's whole session, 7 turns

   • The request, as Felix received it

     Report request from Priyanka Desai (analyst for the Data Protection Officer):
     
     Title: Consent purpose audit: full history and the analytics_cookies anomaly
     Question: Across the full history, how many consent purposes do we actually track, and is the analytics_cookies 'always 100% opted in, zero withdrawals' pattern unique or does it appear elsewhere?
     Grain: one row per distinct consent_records.purpose value, full history, no date filter
     Columns:
     - purpose
     - total records
     - distinct users
     - granted count
     - withdrawn count
     - expired count
     - pct of distinct users with at least one withdrawn or expired record ever
     - earliest occurred_at
     - latest occurred_at
     Details: Source: consent_records, platform-wide, full history (not restricted to 2023-2025, in case any purpose has pre-2023 rows similar to what we just found in data_subject_requests). Group by purpose. This confirms whether there are really only 2 purposes in the system (as the row counts implied last week) and gives a definitive, auditable answer on whether 'never withdrawn' is isolated to analytics_cookies or a broader pattern that would undermine describing our consent capture as reliable to a regulator. Order by total records descending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Felix calls dry_run

     SELECT
       cr.purpose,
       COUNT(*) AS total_records,
       COUNT(DISTINCT cr.user_id) AS distinct_users,
       COUNT(*) FILTER (WHERE cr.status = 'granted') AS granted_count,
       COUNT(*) FILTER (WHERE cr.status = 'withdrawn') AS withdrawn_count,
       COUNT(*) FILTER (WHERE cr.status = 'expired') AS expired_count,
       ROUND(
         100.0 * (
           SELECT COUNT(DISTINCT sub.user_id)
           FROM consent_records sub
           WHERE sub.purpose = cr.purpose
             AND sub.status IN ('withdrawn', 'expired')
         ) / NULLIF(COUNT(DISTINCT cr.user_id), 0)
       , 2) AS pct_users_ever_withdrawn_or_expired,
       MIN(cr.occurred_at) AS earliest_occurred_at,
       MAX(cr.occurred_at) AS latest_occurred_at
     FROM consent_records cr
     GROUP BY cr.purpose
     ORDER BY total_records DESC, cr.purpose

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: purpose, total_records, distinct_users, granted_count, withdrawn_count, expired_count, pct_users_ever_withdrawn_or_expired, earliest_occurred_at, latest_occurred_at

   • FelixQuery compiles and matches the requested grain and columns. Submitting.
   • Felix calls submit

     SELECT
       cr.purpose,
       COUNT(*) AS total_records,
       COUNT(DISTINCT cr.user_id) AS distinct_users,
       COUNT(*) FILTER (WHERE cr.status = 'granted') AS granted_count,
       COUNT(*) FILTER (WHERE cr.status = 'withdrawn') AS withdrawn_count,
       COUNT(*) FILTER (WHERE cr.status = 'expired') AS expired_count,
       ROUND(
         100.0 * (
           SELECT COUNT(DISTINCT sub.user_id)
           FROM consent_records sub
           WHERE sub.purpose = cr.purpose
             AND sub.status IN ('withdrawn', 'expired')
         ) / NULLIF(COUNT(DISTINCT cr.user_id), 0)
       , 2) AS pct_users_ever_withdrawn_or_expired,
       MIN(cr.occurred_at) AS earliest_occurred_at,
       MAX(cr.occurred_at) AS latest_occurred_at
     FROM consent_records cr
     GROUP BY cr.purpose
     ORDER BY total_records DESC, cr.purpose

   • The warehouse answers

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

   • FelixSubmitted. The report groups `consent_records` by `purpose` across all time (no 2023–2025 restriction), giving total records, distinct users, granted/withdrawn/expired counts, the share of distinct users who ever had a withdrawn-or-expired record, and the earliest/latest `occurred_at` per purpose — directly answering whether only 2 purposes exist and whether the "never withdrawn" pattern is unique to `analytics_cookies`.

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

   Felix's transcript (260 KB) · Priyanka Desai's transcript (894 KB) · Ines Laurent's transcript (263 KB) · the night's plan (6 KB) · the night's reports (14 KB) · the week's memo (3 KB)