Queries · q282 · Head of Risk and Payments · week 2, night 3
“Are the pre-2023 dates in change_history, compliance_events, and data_subject_requests a handful of outlier rows, or a systematic chunk of data — given the warehouse's history is only supposed to start 2023-01-01?”
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 2
Subject: Re: Questionnaire memo — good work, a few threads to pull
Claire,
This is a strong memo, and the gaps you flagged are exactly the kind of thing I'd rather surface now than have a prospect's security team find later. A few things stand out and I want to understand them better before we finalize anything.
The February 2025 login spike is the one that worries me most. A 10x jump in failures with no corresponding rise in flagged suspicious activity is a real detection gap, not just a reporting curiosity — if that was a credential-stuffing attempt, I need to know whether it actually got anywhere.
Questions for this week:
Keep the role/permission logging gap and the broken query as noted — no need to keep spending slots chasing those; I'll raise the first with engineering myself and we can note the second as a known limitation in the memo.
Thanks, Aylin
The analyst · week 2, night 3, request 1 of 3
Night 2 results, read critically:
Report 1 (corrected near-miss) now sums cleanly: 98 (tenant,email) pairs, 6 succeeded within 1h, 2 within 24h, 12 later in the month, and 78 (79.6%) never succeeded at all in February. That's an important nuance for Aylin: the overwhelming majority of repeat-failers simply never got in — this looks more like failed/abandoned attempts than a wave of accounts being compromised. Only 8 pairs (across the same handful of merchants) are genuine near-misses worth a closer look.
Report 2 (incidents): clean and reassuring — only 8 compliance_events total in 3 years (4 high, 4 medium), 100% resolved, nothing open, average resolution 7-18 days. Good answer for question #3, no further digging needed.
Report 3 (retention exposure) raised a new problem I have to resolve before telling Aylin/Rosa anything: compliance_events (earliest 2021-10-23), data_subject_requests (earliest 2020-04-24), and change_history (earliest 2021-11-10) all show records predating 2023-01-01 — but the company history is only supposed to start 2023-01-01 (per the system cutover). access_log and login_attempts correctly start right at 2023-01-01/02. This is either a handful of bad/seed timestamps or something more systematic, and I won't quote "this data goes back to 2020" without checking which it is first.
Tonight (3 slots): (1) a quick year-bucketed row-count diagnostic on the three tables with impossible pre-2023 dates, to see if it's a handful of outliers or a real pattern; (2) a pattern diagnostic on the Feb 2025 spike at the 3 affected merchants (user-agent diversity and day-of-month concentration) to give Aylin a firmer read on bug-vs-attack, since last night's volume numbers (many distinct emails and IPs, very low max-per-account) already lean toward a technical issue rather than credential stuffing; (3) a quarterly history of failed-login rates at those same 3 merchants, to see whether Feb 2025 was an isolated event or part of a recurring pattern — relevant since these are the same merchants already flagged elsewhere for support/response problems.
The engineer · 1 dry run on the empty schema, then the submission
select table_name, year, row_count from ( select 'change_history' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count from change_history group by extract(year from occurred_at) union all select 'compliance_events' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count from compliance_events group by extract(year from occurred_at) union all select 'data_subject_requests' as table_name, extract(year from received_at)::int as year, count(*) as row_count from data_subject_requests group by extract(year from received_at) ) t order by table_name, year
“Query compiles cleanly. Submitting.”
“Submitted. The query unions yearly row counts for all three tables keyed on their respective timestamp columns, covering every year actually present in the data (including pre-2023) through 2025, ordered by table_name then year — so Claire can directly compare pre-2023 counts against 2023–2025 volumes to judge whether it's noise or a systematic issue.”
As Claire Dubois saw it, from the small dataset the agents worked on
table_name | year | row_count ----------------------+------+---------- change_history | 2021 | 1 change_history | 2022 | 1 change_history | 2023 | 5535 change_history | 2024 | 12528 change_history | 2025 | 13034 compliance_events | 2021 | 1 compliance_events | 2023 | 2 compliance_events | 2024 | 1 compliance_events | 2025 | 5 data_subject_requests | 2020 | 1 data_subject_requests | 2021 | 4 data_subject_requests | 2022 | 7 data_subject_requests | 2023 | 10 data_subject_requests | 2024 | 23 data_subject_requests | 2025 | 15 (15 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
select table_name, year, row_countfrom ( select 'change_history' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count select 'change_history' as table_name, CAST(extract(year from occurred_at) AS int) as year, count(*) as row_count from change_history group by extract(year from occurred_at) union all select 'compliance_events' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count select 'compliance_events' as table_name, CAST(extract(year from occurred_at) AS int) as year, count(*) as row_count from compliance_events group by extract(year from occurred_at) union all select 'data_subject_requests' as table_name, extract(year from received_at)::int as year, count(*) as row_count select 'data_subject_requests' as table_name, CAST(extract(year from received_at) AS int) as year, count(*) as row_count from data_subject_requests group by extract(year from received_at)) torder by table_name, yearorder by table_name NULLS LAST, year NULLS LAST
select table_name, year, row_count from ( select 'change_history' as table_name, CAST(extract(year from occurred_at) AS int) as year, count(*) as row_count from change_history group by extract(year from occurred_at) union all select 'compliance_events' as table_name, CAST(extract(year from occurred_at) AS int) as year, count(*) as row_count from compliance_events group by extract(year from occurred_at) union all select 'data_subject_requests' as table_name, CAST(extract(year from received_at) AS int) as year, count(*) as row_count from data_subject_requests group by extract(year from received_at) ) t order by table_name NULLS LAST, year 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: Row counts by year for tables with pre-2023 timestamps Question: Are the pre-2023 dates in change_history, compliance_events, and data_subject_requests a handful of outlier rows, or a systematic chunk of data — given the warehouse's history is only supposed to start 2023-01-01? Grain: one row per combination of table_name (change_history, compliance_events, data_subject_requests) and calendar year of its relevant timestamp (occurred_at for change_history/compliance_events, received_at for data_subject_requests), from the earliest year present through 2025 Columns: - table_name - year - row_count Details: Sources: change_history (occurred_at), compliance_events (occurred_at), data_subject_requests (received_at). Purpose: last night's retention-exposure report showed earliest dates of 2021-10-23, 2020-04-24, and 2021-11-10 for these three tables respectively, which is impossible given the stated 2023-01-01 history start — need to see whether pre-2023 rows are a tiny handful (likely bad seed/migration timestamps, safe to treat as noise) or a meaningful share (a real data-quality issue to escalate). Order by table_name, year ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect table_name, year, row_count from ( select 'change_history' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count from change_history group by extract(year from occurred_at) union all select 'compliance_events' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count from compliance_events group by extract(year from occurred_at) union all select 'data_subject_requests' as table_name, extract(year from received_at)::int as year, count(*) as row_count from data_subject_requests group by extract(year from received_at) ) t order by table_name, year
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: table_name, year, row_count
submitselect table_name, year, row_count from ( select 'change_history' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count from change_history group by extract(year from occurred_at) union all select 'compliance_events' as table_name, extract(year from occurred_at)::int as year, count(*) as row_count from compliance_events group by extract(year from occurred_at) union all select 'data_subject_requests' as table_name, extract(year from received_at)::int as year, count(*) as row_count from data_subject_requests group by extract(year from received_at) ) t order by table_name, year
Submitted to tonight's batch. Your work on this request is done.