Queries · q069 · Head of Risk and Payments · week 1, night 2
“Do the data subject requests that look 'open and overdue' by timestamp actually carry an open status, or is that an artifact of missing completed_at values on closed requests — and is the due_at SLA window itself consistent?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Rosa Van den Berg, Head of Data
An enterprise prospect sent a security and compliance questionnaire covering account security, access control, privacy requests, data retention and audit trails. Sales wants answers backed by evidence from our own systems, not by policy documents.
The manager, at the start of week 1
Subject: Evidence we need for the security/compliance questionnaire (this week's priority)
Hi Claire,
This questionnaire is going to a serious enterprise prospect, and Sales wants it backed by evidence from our own systems, not by our policy wording. Rosa and I are co-owning this, so your reports will feed both the questionnaire answers themselves and a broader readiness review Rosa's side is doing. Given you only get a handful of report slots a night, let's be selective and prioritize what a security reviewer will actually probe on. In my own words, here's what I need to know:
Given the slot constraints, please treat questions 3 and 1 as the top two for this week — those are the ones Sales says the prospect calls out by name. If we only get through three of the five, drop 5 first and fold it into next week.
One more thing: since this report touches account security and personal data, please keep the output at a safe aggregate level — no need to name individual customers or staff unless I specifically ask.
Let's sync briefly before you lock in Thursday night's batch so nothing gets wasted on a bounced or bundled request.
Thanks, Aylin
Hi Claire,
Before we hand evidence to this prospect, I'd want us to be confident the underlying records are actually solid enough to stand behind in writing. A few things from my side:
If any of these raise flags on your end, I'd rather we know before it goes into a customer-facing answer than after.
Thanks, Rosa
The analyst · week 1, night 2, request 1 of 3
Reading night 1 critically before spending more slots:
Report 1 (DSAR): Volumes are tiny (0-4/month) but several months show "still_open_overdue" = 1, including one from as far back as 2023-05 and 2024-07, still unresolved as of 2025-12-31. That is a serious red flag for the exact claim Aylin expects to be pressed on — unless it's a data-definition artifact. The engineer's note says "completed" = completed_at IS NOT NULL, bypassing the status column entirely. If a request was closed as e.g. "rejected" or "cancelled" without completed_at being populated, it would wrongly show as "still open and overdue" forever. I need to reconcile against the actual status column before we can honestly tell Aylin whether these are real breaches or a timestamp/status mismatch (this is exactly Rosa's "timestamp trust" concern).
Report 2 (Access): My own request had a flawed proxy — I treated roles.is_system (="built-in, cannot be deleted role") as "elevated/admin access," but 100% of active staff (548/548) hold a system role, which only makes sense if is_system marks a default baseline role every user gets, not a privilege tier. I need to re-derive "elevated access" from actual permissions/role_permissions data instead. Also notable: only 2 staff users total are deactivated and zero are suspended/invited across 120 tenants and 3 years — worth separately checking whether access is actively managed at all (grants/revocations happening over time) rather than static.
Report 3 (Audit log continuity): Mostly reassuring — audit_log volume is steady (~3-5k/month) after the Jan 2024 migration from archive-only logging, actor_user_id nulls track almost exactly with system/api actor_type (so nulls aren't unexplained — they're mostly non-human actors, which is a decent signal for Rosa's "one actor one identity" concern). One thing to flag: all of 2023's audit activity lives only in audit_log_archive (live table has 0 rows pre-Dec-2023) — that's an artifact of the archival job's cutoff, not a gap, but I should keep unioning both tables going forward rather than relying on audit_log alone in any future report.
Tonight (3 slots), I'm staying on Aylin's top two priorities to fix these data-quality issues before we write anything down: (1) reconcile the DSAR "open/overdue" numbers against the actual status column and check SLA-window (due_at) consistency; (2) replace the flawed is_system access proxy with a real permission-category-based view of elevated access by staff status; (3) check whether role grants/revocations are actively happening over time (evidence access is "reviewed/current") and whether those changes are attributable to identifiable actors. I'm holding off on priority #2 (MFA/login security) and #4 (retention) until night 3, and will sync with Aylin before Thursday as agreed.
The engineer · 1 dry run on the empty schema, then the submission
SELECT status, request_type, COUNT(*) AS count_requests, COUNT(completed_at) AS count_with_completed_at, COUNT(*) - COUNT(completed_at) AS count_without_completed_at, SUM(CASE WHEN completed_at IS NULL AND due_at < TIMESTAMP '2025-12-31 00:00:00' THEN 1 ELSE 0 END) AS count_overdue_open, ROUND(100.0 * SUM(CASE WHEN due_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_due_at_null, ROUND(AVG(CASE WHEN due_at IS NOT NULL THEN EXTRACT(EPOCH FROM (due_at - received_at)) / 86400.0 END), 1) AS avg_sla_window_days, MIN(CASE WHEN completed_at IS NULL THEN received_at END) AS oldest_open_received_at FROM data_subject_requests WHERE received_at >= DATE '2023-01-01' AND received_at < DATE '2026-01-01' GROUP BY status, request_type ORDER BY status, request_type
“Query compiles cleanly. Submitting.”
“Submitted. The report groups all DSARs (2023‑01‑01 through 2025‑12‑31 received dates) by status and request_type, and for each group shows total count, how many have/lack completed_at, how many are null-completed_at with due_at before 2025‑12‑31 (the "overdue open" check), the share missing due_at, the average SLA window in days, and the earliest received_at among still-open (null completed_at) rows — which will directly show whether "overdue" rows cluster under genuinely open statuses or under closed/completed statuses with missing timestamps.”
As Claire Dubois saw it, from the small dataset the agents worked on
status | request_type | count_requests | count_with_completed_at | count_without_completed_at | count_overdue_open | pct_due_at_null | avg_sla_window_days | oldest_open_received_at ------------+---------------+----------------+-------------------------+----------------------------+--------------------+-----------------+---------------------+--------------------------- completed | access | 9 | 9 | 0 | 0 | 0.0 | 30.0 | NULL completed | erasure | 31 | 31 | 0 | 0 | 0.0 | 30.0 | NULL completed | rectification | 3 | 3 | 0 | 0 | 0.0 | 30.0 | NULL in_progress | access | 1 | 0 | 1 | 0 | 0.0 | 30.0 | 2025-12-29 04:46:25.41114 received | erasure | 1 | 0 | 1 | 0 | 0.0 | 30.0 | 2025-12-06 01:50:30.669686 rejected | erasure | 3 | 0 | 3 | 3 | 0.0 | 30.0 | 2023-05-26 18:29:11.494494 (6 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, request_type, COUNT(*) AS count_requests, COUNT(completed_at) AS count_with_completed_at, COUNT(*) - COUNT(completed_at) AS count_without_completed_at, SUM(CASE WHEN completed_at IS NULL AND due_at < TIMESTAMP '2025-12-31 00:00:00' THEN 1 ELSE 0 END) AS count_overdue_open, ROUND(100.0 * SUM(CASE WHEN due_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_due_at_null, ROUND(AVG(CASE WHEN due_at IS NOT NULL THEN EXTRACT(EPOCH FROM (due_at - received_at)) / 86400.0 END), 1) AS avg_sla_window_days, 100.0 * SUM(CASE WHEN due_at IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS pct_due_at_null, AVG(CASE WHEN due_at IS NOT NULL THEN {{seconds_between(received_at, due_at)}} / 86400.0 END) AS avg_sla_window_days, MIN(CASE WHEN completed_at IS NULL THEN received_at END) AS oldest_open_received_atFROM data_subject_requestsWHERE received_at >= DATE '2023-01-01' AND received_at < DATE '2026-01-01'GROUP BY status, request_typeORDER BY status, request_typeORDER BY status NULLS LAST, request_type NULLS LAST
SELECT status, request_type, COUNT(*) AS count_requests, COUNT(completed_at) AS count_with_completed_at, COUNT(*) - COUNT(completed_at) AS count_without_completed_at, SUM(CASE WHEN completed_at IS NULL AND due_at < TIMESTAMP '2025-12-31 00:00:00' THEN 1 ELSE 0 END) AS count_overdue_open, 100.0 * SUM(CASE WHEN due_at IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS pct_due_at_null, AVG(CASE WHEN due_at IS NOT NULL THEN CAST(EXTRACT(EPOCH FROM (due_at - received_at)) AS NUMERIC(18,6)) / 86400.0 END) AS avg_sla_window_days, MIN(CASE WHEN completed_at IS NULL THEN received_at END) AS oldest_open_received_at FROM data_subject_requests WHERE received_at >= DATE '2023-01-01' AND received_at < DATE '2026-01-01' GROUP BY status, request_type ORDER BY status NULLS LAST, request_type NULLS LAST
SELECT status, request_type, COUNT(*) AS count_requests, COUNT(completed_at) AS count_with_completed_at, COUNT(*) - COUNT(completed_at) AS count_without_completed_at, SUM(CASE WHEN completed_at IS NULL AND due_at < TIMESTAMP '2025-12-31 00:00:00' THEN 1 ELSE 0 END) AS count_overdue_open, 100.0 * SUM(CASE WHEN due_at IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS pct_due_at_null, AVG(CASE WHEN due_at IS NOT NULL THEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', received_at, due_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0 END) AS avg_sla_window_days, MIN(CASE WHEN completed_at IS NULL THEN received_at END) AS oldest_open_received_at FROM data_subject_requests WHERE received_at >= DATE '2023-01-01' AND received_at < DATE '2026-01-01' GROUP BY status, request_type ORDER BY status NULLS LAST, request_type 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 Claire Dubois (analyst for the Head of Risk and Payments): Title: DSAR backlog reconciliation by status and request type Question: Do the data subject requests that look 'open and overdue' by timestamp actually carry an open status, or is that an artifact of missing completed_at values on closed requests — and is the due_at SLA window itself consistent? Grain: one row per combination of data_subject_requests.status and request_type, across all tenants, all received_at dates from 2023-01-01 through 2025-12-31 Columns: - status (raw status column value) - request_type - count_requests - count_with_completed_at - count_without_completed_at - count_overdue_open (completed_at is null AND due_at < 2025-12-31) - pct_due_at_null (share of this group where due_at is null) - avg_sla_window_days (avg of due_at - received_at in days, where due_at not null) - oldest_open_received_at (earliest received_at among rows with completed_at null, else blank) Details: Source: data_subject_requests. This directly tests last night's finding that several months showed requests still open/overdue years later — need to see whether those rows' status column says something like 'completed'/'closed'/'rejected' (i.e. a timestamp-population gap) versus a genuinely open status like 'in_progress'/'received'. Also check whether due_at - received_at is a consistent SLA window per request_type (e.g. always ~30 days) as a timestamp-trust check, or whether due_at is frequently null/inconsistent. All tenants combined. Order by status, then request_type. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT status, request_type, COUNT(*) AS count_requests, COUNT(completed_at) AS count_with_completed_at, COUNT(*) - COUNT(completed_at) AS count_without_completed_at, SUM(CASE WHEN completed_at IS NULL AND due_at < TIMESTAMP '2025-12-31 00:00:00' THEN 1 ELSE 0 END) AS count_overdue_open, ROUND(100.0 * SUM(CASE WHEN due_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_due_at_null, ROUND(AVG(CASE WHEN due_at IS NOT NULL THEN EXTRACT(EPOCH FROM (due_at - received_at)) / 86400.0 END), 1) AS avg_sla_window_days, MIN(CASE WHEN completed_at IS NULL THEN received_at END) AS oldest_open_received_at FROM data_subject_requests WHERE received_at >= DATE '2023-01-01' AND received_at < DATE '2026-01-01' GROUP BY status, request_type ORDER BY status, request_type
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: status, request_type, count_requests, count_with_completed_at, count_without_completed_at, count_overdue_open, pct_due_at_null, avg_sla_window_days, oldest_open_received_at
submitSELECT status, request_type, COUNT(*) AS count_requests, COUNT(completed_at) AS count_with_completed_at, COUNT(*) - COUNT(completed_at) AS count_without_completed_at, SUM(CASE WHEN completed_at IS NULL AND due_at < TIMESTAMP '2025-12-31 00:00:00' THEN 1 ELSE 0 END) AS count_overdue_open, ROUND(100.0 * SUM(CASE WHEN due_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_due_at_null, ROUND(AVG(CASE WHEN due_at IS NOT NULL THEN EXTRACT(EPOCH FROM (due_at - received_at)) / 86400.0 END), 1) AS avg_sla_window_days, MIN(CASE WHEN completed_at IS NULL THEN received_at END) AS oldest_open_received_at FROM data_subject_requests WHERE received_at >= DATE '2023-01-01' AND received_at < DATE '2026-01-01' GROUP BY status, request_type ORDER BY status, request_type
Submitted to tonight's batch. Your work on this request is done.