Queries · q123 · Data Protection Officer · week 1, night 4

audit_log actual archiving vs. the stated 730-day archive policy

“audit_log's retention policy looks healthy on paper (active, last run 2025-12-31) — but are rows older than 730 days actually leaving the live table as the 'archive' action implies?”

Manager · asked the questionInes LaurentData Protection Officer. Lawyer by training, previously at a European marketplace. Also reports to the board's audit committee.

Analyst · planned the reportPriyanka DesaiAuditor's mindset: traces individual records end to end to check that the totals are real.

Engineer · wrote the SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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

Ines Laurent writes to Priyanka Desai
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.
1. How are we doing on privacy requests from shoppers? I want to know how many access/erasure/portability requests we're getting, how long they typically take to close, and whether any are overdue. This is the thing the board audit committee will ask about first, and it's also a legal deadline risk, so I need a realistic picture, not just a snapshot.
2. What does our consent picture look like? I'd like a sense of how many of our users have actually opted in versus withdrawn or never given consent, and whether that's changed meaningfully over time. This tells me how exposed we are if we rely on consent for marketing or data use.
3. Are there open compliance issues sitting unresolved, and how serious are they? I want to understand the volume and severity of flagged incidents (things like suspicious data access or retention problems) and how quickly they tend to get closed out. I need to know if we have a backlog building up anywhere.
4. How long are we actually holding onto shopper data, and does it match what our retention rules say it should be? I'm less interested in the rulebook and more in whether reality matches it — are there data types we're sitting on well past when we said we would delete them?
5. Are there any legal holds in place right now, and what do they cover? I want to know how many are active, what they're blocking, and whether any look stale (i.e., should have been released a while ago). This matters for both litigation readiness and for explaining why certain deletions haven't happened.
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
Priyanka Desai plans the night
The analyst · week 1, night 4, request 3 of 3
The pieces from the last three nights line up almost too neatly to leave unverified: the 3 'rejected' DSARs (night 3, report 1) never get completed_at stamped — which inflated my earlier backlog numbers — and their received_at dates (2023-05-26, 2024-07-09, 2025-03-21) sit exactly one day before the 3 active legal holds' placed_at dates (2023-05-27, 2024-07-10, 2025-03-22) for the same three tenants (chargeback disputes). This looks like 'erasure correctly rejected pending an active legal hold,' which would completely reframe item 1 from 'overdue backlog' to 'holds that are now 1-2.5 years stale' — feeding directly into item 5. I want a direct join to confirm the same subject_user_id is involved before I write that into the memo, rather than relying on date coincidence. Separately, night 3's retention landscape report (report 2) surfaced the clearest answer yet to item 4: email_log has a 730-day/delete policy configured for all 120 tenants, but it is active=0 and never_run=120 for every single one — meaning it's pure paperwork, never enforced. I want to size that gap in actual months of retained email data. As a contrast, audit_log looks 'healthy' on paper (active, last_run_at=2025-12-31, action=archive) — I want to check whether old rows are actually leaving the live table, since 'archive' could just as easily mean nothing is really being purged in practice. This is my last night, so I'm spending all 3 slots on the highest-value remaining checks rather than broad new territory.
audit_log actual archiving vs. the stated 730-day archive policy
Question
audit_log's retention policy looks healthy on paper (active, last run 2025-12-31) — but are rows older than 730 days actually leaving the live table as the 'archive' action implies?
One row per
calendar month, based on audit_log.occurred_at
Columns
- month (YYYY-MM)
- audit_log rows still present in month (count)
- days old as of 2026-01-05 (measured from month start)
- exceeds 730-day retention (Y/N)

Ines writes the SQL

The engineer · 2 dry runs on the empty schema, then the submission

Dry run 1 · STATUS: REJECTED

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC

Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

SELECT  to_char(date_trunc('month', occurred_at), 'YYYY-MM') AS month,  strftime(date_trunc('month', occurred_at), '%Y-%m') AS month,  COUNT(*) AS rows_in_live_table,  (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) AS days_old_as_of_report_date,  CASE WHEN (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retentionFROM audit_logWHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'GROUP BY date_trunc('month', occurred_at)ORDER BY date_trunc('month', occurred_at) ASC

“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 counts live audit_log rows per month from 2023-01 through 2025-12, with days-old measured from each month's start to 2026-01-05, and a Y/N flag for exceeding the 730-day policy threshold — directly supporting the DPO's question of whether old rows are actually leaving the live table.”

The report comes back overnight

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

month   | rows_in_live_table | days_old_as_of_report_date | exceeds_730_day_retention
--------+--------------------+----------------------------+--------------------------
2023-12 |               3589 |                        766 | Y                        
2024-01 |               3731 |                        735 | Y                        
2024-02 |               8448 |                        704 | N                        
2024-03 |               4067 |                        675 | N                        
2024-04 |               3474 |                        644 | N                        
2024-05 |               3688 |                        614 | N                        
2024-06 |               3588 |                        583 | N                        
2024-07 |               3991 |                        553 | N                        
2024-08 |               4273 |                        522 | N                        
2024-09 |               4000 |                        491 | N                        
2024-10 |               4299 |                        461 | N                        
2024-11 |               3824 |                        430 | N                        
2024-12 |               4547 |                        400 | N                        
2025-01 |               4187 |                        369 | N                        
2025-02 |               8786 |                        338 | N                        
2025-03 |               4543 |                        310 | N                        
2025-04 |               4097 |                        279 | N                        
2025-05 |               4486 |                        249 | N                        
2025-06 |               3955 |                        218 | N                        
2025-07 |               4525 |                        188 | N                        
2025-08 |               4777 |                        157 | N                        
2025-09 |               5030 |                        126 | N                        
2025-10 |               4709 |                         96 | N                        
2025-11 |               4446 |                         65 | N                        
2025-12 |               4902 |                         35 | N                        
(25 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:
- q325Retention reality check beyond email: events, job_runs, page_views, system_health_checks
It builds on:
- q119Retention policy landscape: distinct rules by entity table

It becomes benchmark query q123

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

spellingcast (2×)
placeholderyear month
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  strftime(date_trunc('month', occurred_at), '%Y-%m') AS month,  {{year_month(date_trunc('month', occurred_at))}} AS month,  COUNT(*) AS rows_in_live_table,  (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) AS days_old_as_of_report_date,  CASE WHEN (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention  (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) AS days_old_as_of_report_date,  CASE WHEN (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retentionFROM audit_logWHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'GROUP BY date_trunc('month', occurred_at)ORDER BY date_trunc('month', occurred_at) ASCORDER BY date_trunc('month', occurred_at) ASC NULLS LAST

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

SELECT
  strftime(date_trunc('month', occurred_at), '%Y-%m') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC NULLS LAST

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC NULLS LAST

SELECT
  to_char(CAST(date_trunc('month', occurred_at) AS timestamp), 'YYYY-MM') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC NULLS LAST

SELECT
  formatDateTime(date_trunc('month', occurred_at), '%Y-%m') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - CAST(date_trunc('month', occurred_at) AS date)) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC NULLS LAST

DataFusionrow 1: col 1: "2023-12" vs "YYYY-MM"

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

Ines's whole session, 9 turns

The request, as Ines received it

Report request from Priyanka Desai (analyst for the Data Protection Officer):

Title: audit_log actual archiving vs. the stated 730-day archive policy
Question: audit_log's retention policy looks healthy on paper (active, last run 2025-12-31) — but are rows older than 730 days actually leaving the live table as the 'archive' action implies?
Grain: one row per calendar month, based on audit_log.occurred_at
Columns:
- month (YYYY-MM)
- audit_log rows still present in month (count)
- days old as of 2026-01-05 (measured from month start)
- exceeds 730-day retention (Y/N)
Details: Source: audit_log (the live table, not audit_log_archive), platform-wide, grouped by date_trunc('month', occurred_at) or joined to calendar_days, spanning 2023-01 through 2025-12. Use literal date 2026-01-05 for age math. The policy says rows older than 730 days should be archived (moved out of audit_log into audit_log_archive) and most tenants show the job as active with last_run_at = 2025-12-31, so if months older than 730 days (roughly before 2024-01) still show substantial row counts here, that means the archive step isn't actually clearing the live table in practice despite looking healthy on the policy/run-log level. Order by month ascending.

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

Ines calls dry_run

SELECT
  to_char(date_trunc('month', occurred_at), 'YYYY-MM') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC

The warehouse answers

STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

Ines calls dry_run

SELECT
  strftime(date_trunc('month', occurred_at), '%Y-%m') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, rows_in_live_table, days_old_as_of_report_date, exceeds_730_day_retention

InesQuery compiles and matches the requested grain and columns. Submitting.

Ines calls submit

SELECT
  strftime(date_trunc('month', occurred_at), '%Y-%m') AS month,
  COUNT(*) AS rows_in_live_table,
  (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) AS days_old_as_of_report_date,
  CASE WHEN (DATE '2026-01-05' - date_trunc('month', occurred_at)::date) > 730 THEN 'Y' ELSE 'N' END AS exceeds_730_day_retention
FROM audit_log
WHERE occurred_at >= DATE '2023-01-01' AND occurred_at < DATE '2026-01-01'
GROUP BY date_trunc('month', occurred_at)
ORDER BY date_trunc('month', occurred_at) ASC

The warehouse answers

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

InesSubmitted. The report counts live `audit_log` rows per month from 2023-01 through 2025-12, with days-old measured from each month's start to 2026-01-05, and a Y/N flag for exceeding the 730-day policy threshold — directly supporting the DPO's question of whether old rows are actually leaving the live table.

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

Ines's transcript (263 KB) · Priyanka Desai's transcript (894 KB) · Ines Laurent's transcript (263 KB) · the night's plan (5 KB) · the night's reports (8 KB) · the week's memo (3 KB)

audit_log actual archiving vs. the stated 730-day archive policy

Ines Laurent writes to Priyanka Desai

Priyanka Desai plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q123

Everything, as recorded