Queries · q120 · Data Protection Officer · week 1, night 3
“How many legal holds are currently active, what do they cover, and are any stale (placed long ago with no recent review or release)?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 1
Subject: A few starting questions on privacy & data handling
Hi Priyanka,
Welcome aboard, and thank you for taking these on this week. As I get oriented, here's what I'd like to understand first. Given the slot constraints, please treat these as priorities in order — if only three fit this week, start from the top and we'll pick up the rest next week.
For now, could you scope the first request for me? I'd like to see it broken down in a way that shows me trend over the last several months, not just a single total — I want to catch whether we're improving or falling behind.
Let me know if any of these need to be split further once you see what the data looks like.
Thanks, Ines
The analyst · week 1, night 3, request 3 of 3
Report 1's big finding: all 3 of the long-overdue DSARs are status='rejected' yet have completed_at = NULL — my original monthly report (night 1) treated 'completed_at IS NULL' as the sole "still open" signal, which may be wrongly counting rejected (i.e., actually-decided) requests as backlog. Before I hand Ines a number, I need to know whether 'rejected' is a systemic gap (completed_at never set on rejections) or an isolated failure on these 3 — that reshapes the whole answer to her question 1, so it's the top priority tonight. Report 3 (retention policy inventory) was dominated by one audit_log policy repeated per tenant and got truncated at 50 of 720 rows before showing any other entity_table — I need a properly aggregated view of what policies actually exist before I can check reality against them (item 4). With only this night and one more left, I'm also opening item 5 (legal holds) tonight since it looks like a single, self-contained report I can likely close in one shot, leaving night 4 free for the retention actual-vs-policy comparison and any remaining loose ends.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS tenant_name, lh.name AS hold_name, LEFT(lh.reason, 200) AS reason_truncated, lh.entity_table AS entity_table_scope, lh.subject_user_id, lh.is_active, CAST(lh.placed_at AS date) AS placed_at_date, CAST(lh.released_at AS date) AS released_at_date, CASE WHEN lh.is_active THEN (DATE '2026-01-05' - CAST(lh.placed_at AS date)) END AS days_since_placed FROM legal_holds lh JOIN tenants t ON t.id = lh.tenant_id ORDER BY lh.is_active DESC, lh.placed_at ASC, lh.id ASC
“Query compiles cleanly with the expected columns and grain. Submitting now.”
“Submitted. Summary of what the report does:
- Grain: one row per legal_holds record.
- Columns: tenant name, hold name, truncated reason (200 chars), entity table scope, subject user id, is_active, placed_at date, released_at date (blank if still active), and days since placed (computed only for active holds, using the fixed date 2026-01-05).
- Ordering: active holds first, then oldest placed_at first within each group — so any stale, long-open holds surface at the top.
- Note to analyst: I didn't impose a manual row limit; the standard reporting cap (50 rows) and its automatic total-row count will tell us whether the active+released holds exceed that and warrant a narrower follow-up query (e.g., active-only).”
As Priyanka Desai saw it, from the small dataset the agents worked on
tenant_name | hold_name | reason_truncated | entity_table_scope | subject_user_id | is_active | placed_at_date | released_at_date | days_since_placed ---------------------+---------------------------+------------------------------------------+--------------------+-----------------+-----------+----------------+------------------+------------------ Marrowby & Vane | Chargeback dispute 841484 | Payment dispute pending with the card ne | NULL | 1933 | true | 2023-05-27 | NULL | 954 Rue Lafayette Studio | Chargeback dispute 214867 | Payment dispute pending with the card ne | NULL | 5911 | true | 2024-07-10 | NULL | 544 Cragsley Outfitters | Chargeback dispute 206882 | Payment dispute pending with the card ne | NULL | 6446 | true | 2025-03-22 | NULL | 289 (3 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS tenant_name, lh.name AS hold_name, LEFT(lh.reason, 200) AS reason_truncated, lh.entity_table AS entity_table_scope, lh.subject_user_id, lh.is_active, lh.subject_user_id AS subject_user_id, lh.is_active AS is_active, CAST(lh.placed_at AS date) AS placed_at_date, CAST(lh.released_at AS date) AS released_at_date, CASE WHEN lh.is_active THEN (DATE '2026-01-05' - CAST(lh.placed_at AS date)) END AS days_since_placedFROM legal_holds lhJOIN tenants t ON t.id = lh.tenant_idORDER BY lh.is_active DESC, lh.placed_at ASC, lh.id ASCORDER BY lh.is_active DESC NULLS FIRST, lh.placed_at ASC NULLS LAST, lh.id ASC NULLS LAST
SELECT t.name AS tenant_name, lh.name AS hold_name, LEFT(lh.reason, 200) AS reason_truncated, lh.entity_table AS entity_table_scope, lh.subject_user_id AS subject_user_id, lh.is_active AS is_active, CAST(lh.placed_at AS date) AS placed_at_date, CAST(lh.released_at AS date) AS released_at_date, CASE WHEN lh.is_active THEN (DATE '2026-01-05' - CAST(lh.placed_at AS date)) END AS days_since_placed FROM legal_holds lh JOIN tenants t ON t.id = lh.tenant_id ORDER BY lh.is_active DESC NULLS FIRST, lh.placed_at ASC NULLS LAST, lh.id 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: Legal holds: active scope, coverage, and staleness Question: How many legal holds are currently active, what do they cover, and are any stale (placed long ago with no recent review or release)? Grain: one row per legal_holds record Columns: - tenant name - hold name - reason (truncated) - entity table scope (if set) - subject user id (if set, scope is a specific data subject) - is active - placed at (date) - released at (date, blank if still active) - days since placed (2026-01-05 minus placed_at, for active holds only) Details: Source: legal_holds joined to tenants for the name. Include all rows (active and released) so released ones give context on typical hold duration, but order so active holds print first, oldest placed_at first, to surface any that look stale (placed many months/years ago and never released). Use literal date 2026-01-05 for age math, not CURRENT_DATE. If the table has close to or more than 50 rows, note the total count so we know whether this needs a narrower follow-up. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name AS tenant_name, lh.name AS hold_name, LEFT(lh.reason, 200) AS reason_truncated, lh.entity_table AS entity_table_scope, lh.subject_user_id, lh.is_active, CAST(lh.placed_at AS date) AS placed_at_date, CAST(lh.released_at AS date) AS released_at_date, CASE WHEN lh.is_active THEN (DATE '2026-01-05' - CAST(lh.placed_at AS date)) END AS days_since_placed FROM legal_holds lh JOIN tenants t ON t.id = lh.tenant_id ORDER BY lh.is_active DESC, lh.placed_at ASC, lh.id ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, hold_name, reason_truncated, entity_table_scope, subject_user_id, is_active, placed_at_date, released_at_date, days_since_placed
submitSELECT t.name AS tenant_name, lh.name AS hold_name, LEFT(lh.reason, 200) AS reason_truncated, lh.entity_table AS entity_table_scope, lh.subject_user_id, lh.is_active, CAST(lh.placed_at AS date) AS placed_at_date, CAST(lh.released_at AS date) AS released_at_date, CASE WHEN lh.is_active THEN (DATE '2026-01-05' - CAST(lh.placed_at AS date)) END AS days_since_placed FROM legal_holds lh JOIN tenants t ON t.id = lh.tenant_id ORDER BY lh.is_active DESC, lh.placed_at ASC, lh.id ASC
Submitted to tonight's batch. Your work on this request is done.