Queries · q281 · Head of Risk and Payments · week 2, night 2

How far back does data go in tables without a retention policy?

“For the five tables that currently have no configured retention policy, how far back does the oldest data actually go, and how much of it is already older than our one known retention benchmark (audit_log's 730-day policy)?”

Manager · asked the questionAylin DemirHead of Risk and Payments. Previously in fraud operations at a bank. Responsible for payment performance and for keeping bad actors out.

Analyst · planned the reportClaire DuboisTime-series minded: weekly and daily trends, before and after, seasonality.

Engineer · wrote the SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

A business event lands on the Head of Risk and Payments's desk
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.
One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events
Aylin Demir writes to Claire Dubois
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:
1. On the February 2025 spike — did it cluster around a handful of accounts, merchants, or sources, or was it spread evenly? And did any of the accounts that failed repeatedly go on to log in successfully shortly after? That tells us whether this was noise or an actual near-miss.
2. On the three unresolved rejection dates — rather than retrying the same technical path, can we establish, using whatever reliable timestamp we do have, roughly when each decision was made relative to the request date? I don't need the exact figure if it's not retrievable — I need to know whether we were plausibly within the deadline or not, so we can decide how to word that answer.
3. On flagged incidents (the one we deferred) — now that we're another week in, I want this one: how many incidents have been flagged, how severe, how long they typically take to close, and whether anything has sat open a long time. This is likely to come up in the same breath as the login spike.
4. On the tables with no retention policy — before I escalate this as a gap, I want to know how exposed we actually are: roughly how far back does that data go today? If it's a few months, this is a minor cleanup; if it's three years, it's a bigger conversation with Rosa.
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
Claire Dubois plans the night
The analyst · week 2, night 2, request 3 of 3
Night 1 results need a careful read before I pass anything to Aylin:

Report 1 (concentration by merchant) is clean and striking: 3 merchants — Cragsley Outfitters (4,407 failed, 88.1% fail rate), Marrowby & Vane (1,978 failed, 86.1%), Studio Vrij (783 failed, 84.2%) — account for ~7,168 of the ~7,429 failed attempts we found last week (96.5%), while the next-highest merchant had just 17 failed attempts. These are the exact same three merchants Support Ops called out last week as having both the heaviest ticket volume and worst response times. But the pattern doesn't look like a credential-stuffing attack: distinct_emails_with_any_failed_attempt is nearly as high as total failed attempts (e.g. Cragsley: 2,786 distinct emails for 4,407 fails, max 8 fails on any single email, max 9 from any single IP, spread across 1,903 distinct IPs). That's wide, shallow failure across many accounts and many sources at once — much more consistent with a broken login flow/client bug affecting those 3 merchants specifically than with an external attack.

Report 2 (near-miss outcomes) is internally broken and I will not pass these numbers on: percentages are computed against a fixed denominator of 102, but the bucket counts (42+12+89+452=595) don't come close to summing to 102, and the engineer's note confirms email was matched globally rather than scoped per tenant (login_attempts.email is only unique per tenant) — so the "cohort" silently grew across tenants mid-query while the percentage base didn't update. This is exactly the kind of actor/identity mismatch Rosa flagged in week 1, now showing up in a different table. I'm retrying tonight with tenant-scoped (tenant_id, email) pairs and mutually-exclusive buckets that must sum to the true cohort size.

Report 3 (DSAR via updated_at) looks solid and resolves Aylin's question #2: all 3 rejected DSARs show updated_at well inside their 30-day due_at window (8.7, 3.8, and 23.7 days after receipt respectively) — plausibly within deadline, with the usual caveat that updated_at isn't a verified "decision" timestamp.

Tonight (3 slots): (1) retry the near-miss report correctly; (2) incidents/compliance_events volume, severity, and resolution time (question #3, deferred from last week); (3) how far back the five retention-policy-less tables actually go, to size the exposure for question #4.
How far back does data go in tables without a retention policy?
Question
For the five tables that currently have no configured retention policy, how far back does the oldest data actually go, and how much of it is already older than our one known retention benchmark (audit_log's 730-day policy)?
One row per
table, for access_log, change_history, data_subject_requests, compliance_events, and login_attempts, across all tenants
Columns
- table_name
- earliest_record_date (min of the relevant timestamp column: occurred_at for access_log/change_history/compliance_events/login_attempts, received_at for data_subject_requests)
- days_since_earliest_record (as of 2025-12-31)
- total_row_count
- row_count_older_than_730_days
- pct_older_than_730_days

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 table_name, earliest_record_date, days_since_earliest_record, total_row_count,
       row_count_older_than_730_days,
       ROUND(100.0 * row_count_older_than_730_days / NULLIF(total_row_count, 0), 2) AS pct_older_than_730_days
FROM (
  SELECT 'access_log' AS table_name,
         MIN(occurred_at)::date AS earliest_record_date,
         (DATE '2025-12-31' - MIN(occurred_at)::date) AS days_since_earliest_record,
         COUNT(*) AS total_row_count,
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END) AS row_count_older_than_730_days
  FROM access_log

  UNION ALL

  SELECT 'change_history' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM change_history

  UNION ALL

  SELECT 'compliance_events' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM compliance_events

  UNION ALL

  SELECT 'login_attempts' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM login_attempts

  UNION ALL

  SELECT 'data_subject_requests' AS table_name,
         MIN(received_at)::date,
         (DATE '2025-12-31' - MIN(received_at)::date),
         COUNT(*),
         SUM(CASE WHEN received_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM data_subject_requests
) t
ORDER BY table_name

“Query 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 query unions the five tables (access_log, change_history, compliance_events, login_attempts on occurred_at; data_subject_requests on received_at), computing earliest date, days since earliest (vs. 2025-12-31), total rows, rows older than 730 days, and the percentage — one row per table, ordered by table_name.”

The report comes back overnight

As Claire Dubois saw it, from the small dataset the agents worked on

table_name            | earliest_record_date | days_since_earliest_record | total_row_count | row_count_older_than_730_days | pct_older_than_730_days
----------------------+----------------------+----------------------------+-----------------+-------------------------------+------------------------
access_log            | 2023-01-02           |                       1094 |            8502 |                          2157 |                   25.37
change_history        | 2021-11-10           |                       1512 |           31099 |                          5537 |                    17.8
compliance_events     | 2021-10-23           |                       1530 |               9 |                             3 |                   33.33
data_subject_requests | 2020-04-24           |                       2077 |              60 |                            22 |                   36.67
login_attempts        | 2023-01-01           |                       1095 |          186320 |                         45321 |                   24.32
(5 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q282Row counts by year for tables with pre-2023 timestamps
It builds on:
- q075Retention policy coverage for audit and compliance-relevant tables

It becomes benchmark query q281

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

spellingcast (10×)
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results

The benchmark text, against the submitted SQL

SELECT table_name, earliest_record_date, days_since_earliest_record, total_row_count,       row_count_older_than_730_days,       ROUND(100.0 * row_count_older_than_730_days / NULLIF(total_row_count, 0), 2) AS pct_older_than_730_days       100.0 * row_count_older_than_730_days / NULLIF(total_row_count, 0) AS pct_older_than_730_daysFROM (  SELECT 'access_log' AS table_name,         MIN(occurred_at)::date AS earliest_record_date,         (DATE '2025-12-31' - MIN(occurred_at)::date) AS days_since_earliest_record,         CAST(MIN(occurred_at) AS date) AS earliest_record_date,         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)) AS days_since_earliest_record,         COUNT(*) AS total_row_count,         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END) AS row_count_older_than_730_days  FROM access_log  UNION ALL  SELECT 'change_history' AS table_name,         MIN(occurred_at)::date,         (DATE '2025-12-31' - MIN(occurred_at)::date),         CAST(MIN(occurred_at) AS date),         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)),         COUNT(*),         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)  FROM change_history  UNION ALL  SELECT 'compliance_events' AS table_name,         MIN(occurred_at)::date,         (DATE '2025-12-31' - MIN(occurred_at)::date),         CAST(MIN(occurred_at) AS date),         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)),         COUNT(*),         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)  FROM compliance_events  UNION ALL  SELECT 'login_attempts' AS table_name,         MIN(occurred_at)::date,         (DATE '2025-12-31' - MIN(occurred_at)::date),         CAST(MIN(occurred_at) AS date),         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)),         COUNT(*),         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)  FROM login_attempts  UNION ALL  SELECT 'data_subject_requests' AS table_name,         MIN(received_at)::date,         (DATE '2025-12-31' - MIN(received_at)::date),         CAST(MIN(received_at) AS date),         (DATE '2025-12-31' - CAST(MIN(received_at) AS date)),         COUNT(*),         SUM(CASE WHEN received_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)  FROM data_subject_requests) tORDER BY table_nameORDER BY table_name NULLS LAST

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

SELECT table_name, earliest_record_date, days_since_earliest_record, total_row_count,
       row_count_older_than_730_days,
       100.0 * row_count_older_than_730_days / NULLIF(total_row_count, 0) AS pct_older_than_730_days
FROM (
  SELECT 'access_log' AS table_name,
         CAST(MIN(occurred_at) AS date) AS earliest_record_date,
         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)) AS days_since_earliest_record,
         COUNT(*) AS total_row_count,
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END) AS row_count_older_than_730_days
  FROM access_log

  UNION ALL

  SELECT 'change_history' AS table_name,
         CAST(MIN(occurred_at) AS date),
         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM change_history

  UNION ALL

  SELECT 'compliance_events' AS table_name,
         CAST(MIN(occurred_at) AS date),
         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM compliance_events

  UNION ALL

  SELECT 'login_attempts' AS table_name,
         CAST(MIN(occurred_at) AS date),
         (DATE '2025-12-31' - CAST(MIN(occurred_at) AS date)),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM login_attempts

  UNION ALL

  SELECT 'data_subject_requests' AS table_name,
         CAST(MIN(received_at) AS date),
         (DATE '2025-12-31' - CAST(MIN(received_at) AS date)),
         COUNT(*),
         SUM(CASE WHEN received_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM data_subject_requests
) t
ORDER BY table_name NULLS LAST

Scale factor 1000, c8a.24xlarge, after the last load. q281 on the start page, with every configuration.

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 Claire Dubois (analyst for the Head of Risk and Payments):

Title: How far back does data go in tables without a retention policy?
Question: For the five tables that currently have no configured retention policy, how far back does the oldest data actually go, and how much of it is already older than our one known retention benchmark (audit_log's 730-day policy)?
Grain: one row per table, for access_log, change_history, data_subject_requests, compliance_events, and login_attempts, across all tenants
Columns:
- table_name
- earliest_record_date (min of the relevant timestamp column: occurred_at for access_log/change_history/compliance_events/login_attempts, received_at for data_subject_requests)
- days_since_earliest_record (as of 2025-12-31)
- total_row_count
- row_count_older_than_730_days
- pct_older_than_730_days
Details: Sources: access_log, change_history, data_subject_requests, compliance_events, login_attempts (one SELECT per table, unioned into a single result keyed by table_name). Use 2025-12-31 as the fixed reference date. Purpose: size how exposed we are before escalating the 'no retention policy' finding to Rosa — if these tables only go back a few months this is minor, if they go back close to our full 3-year history (like audit_log's archive) it's a bigger conversation. Order by table_name.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Felix calls dry_run

SELECT table_name, earliest_record_date, days_since_earliest_record, total_row_count,
       row_count_older_than_730_days,
       ROUND(100.0 * row_count_older_than_730_days / NULLIF(total_row_count, 0), 2) AS pct_older_than_730_days
FROM (
  SELECT 'access_log' AS table_name,
         MIN(occurred_at)::date AS earliest_record_date,
         (DATE '2025-12-31' - MIN(occurred_at)::date) AS days_since_earliest_record,
         COUNT(*) AS total_row_count,
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END) AS row_count_older_than_730_days
  FROM access_log

  UNION ALL

  SELECT 'change_history' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM change_history

  UNION ALL

  SELECT 'compliance_events' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM compliance_events

  UNION ALL

  SELECT 'login_attempts' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM login_attempts

  UNION ALL

  SELECT 'data_subject_requests' AS table_name,
         MIN(received_at)::date,
         (DATE '2025-12-31' - MIN(received_at)::date),
         COUNT(*),
         SUM(CASE WHEN received_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM data_subject_requests
) t
ORDER BY table_name

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: table_name, earliest_record_date, days_since_earliest_record, total_row_count, row_count_older_than_730_days, pct_older_than_730_days

FelixQuery compiles cleanly. Submitting.

Felix calls submit

SELECT table_name, earliest_record_date, days_since_earliest_record, total_row_count,
       row_count_older_than_730_days,
       ROUND(100.0 * row_count_older_than_730_days / NULLIF(total_row_count, 0), 2) AS pct_older_than_730_days
FROM (
  SELECT 'access_log' AS table_name,
         MIN(occurred_at)::date AS earliest_record_date,
         (DATE '2025-12-31' - MIN(occurred_at)::date) AS days_since_earliest_record,
         COUNT(*) AS total_row_count,
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END) AS row_count_older_than_730_days
  FROM access_log

  UNION ALL

  SELECT 'change_history' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM change_history

  UNION ALL

  SELECT 'compliance_events' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM compliance_events

  UNION ALL

  SELECT 'login_attempts' AS table_name,
         MIN(occurred_at)::date,
         (DATE '2025-12-31' - MIN(occurred_at)::date),
         COUNT(*),
         SUM(CASE WHEN occurred_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM login_attempts

  UNION ALL

  SELECT 'data_subject_requests' AS table_name,
         MIN(received_at)::date,
         (DATE '2025-12-31' - MIN(received_at)::date),
         COUNT(*),
         SUM(CASE WHEN received_at < (DATE '2025-12-31' - INTERVAL '730 days') THEN 1 ELSE 0 END)
  FROM data_subject_requests
) t
ORDER BY table_name

The warehouse answers

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

FelixSubmitted. The query unions the five tables (access_log, change_history, compliance_events, login_attempts on `occurred_at`; data_subject_requests on `received_at`), computing earliest date, days since earliest (vs. 2025-12-31), total rows, rows older than 730 days, and the percentage — one row per table, ordered by table_name.

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 (265 KB) · Claire Dubois's transcript (838 KB) · Aylin Demir's transcript (263 KB) · the night's plan (6 KB) · the night's reports (4 KB) · the week's memo (3 KB)

How far back does data go in tables without a retention policy?

A business event lands on the Head of Risk and Payments's desk

Aylin Demir writes to Claire Dubois

Claire Dubois plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q281

Everything, as recorded