DB Olympics

Queries · q119 · Data Protection Officer · week 1, night 3

Retention policy landscape: distinct rules by entity table

“What retention rules actually exist across entity tables, collapsing the one-row-per-tenant duplication, so I can see real coverage and variation?”

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 SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

  1. 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

  2. Priyanka Desai plans the night

    The analyst · week 1, night 3, request 2 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.

    Retention policy landscape: distinct rules by entity table
    Question
    What retention rules actually exist across entity tables, collapsing the one-row-per-tenant duplication, so I can see real coverage and variation?
    One row per
    distinct combination of (entity_schema, entity_table, retention_days, action_on_expiry)
    Columns
    • entity schema
    • entity table
    • retention days
    • action on expiry
    • tenant count using this exact combination
    • active count (is_active = true)
    • never-run count (last_run_at is null)

  3. Bea 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
  t0.entity_schema AS entity_schema,
  t0.entity_table AS entity_table,
  t0.retention_days AS retention_days,
  t0.action_on_expiry AS action_on_expiry,
  COUNT(DISTINCT t0.tenant_id) AS tenant_count,
  COALESCE(SUM(CASE WHEN t0.is_active THEN 1 ELSE 0 END), 0) AS active_count,
  COALESCE(SUM(CASE WHEN t0.last_run_at IS NULL THEN 1 ELSE 0 END), 0) AS never_run_count
FROM retention_policies t0
GROUP BY
  t0.entity_schema,
  t0.entity_table,
  t0.retention_days,
  t0.action_on_expiry
ORDER BY
  t0.entity_table ASC,
  tenant_count DESC,
  t0.entity_schema ASC,
  t0.retention_days ASC,
  t0.action_on_expiry ASC

    “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 report groups retention_policies by (entity_schema, entity_table, retention_days, action_on_expiry), with tenant_count as the distinct tenant count per combination, and active_count / never_run_count as row-level counts within that combination — flagged in the note since a tenant's active/last_run status could vary even when the days/action match.”

  4. The report comes back overnight

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

    entity_schema | entity_table         | retention_days | action_on_expiry | tenant_count | active_count | never_run_count
--------------+----------------------+----------------+------------------+--------------+--------------+----------------
audit         | audit_log            |            730 | archive          |          120 |          120 |               9
comms         | email_log            |            730 | delete           |          120 |            0 |             120
analytics     | events               |           1095 | delete           |          120 |          120 |               9
ops           | job_runs             |             90 | delete           |          120 |          120 |               9
analytics     | page_views           |           1095 | delete           |          120 |          120 |               9
ops           | system_health_checks |             35 | delete           |          120 |          120 |               9
(6 rows)

  5. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Later queries that build on this report:

    It builds on:

  6. It becomes benchmark query q119

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

    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (5×)
    The benchmark text, against the submitted SQL
    SELECT  t0.entity_schema AS entity_schema,  t0.entity_table AS entity_table,  t0.retention_days AS retention_days,  t0.action_on_expiry AS action_on_expiry,  COUNT(DISTINCT t0.tenant_id) AS tenant_count,  COALESCE(SUM(CASE WHEN t0.is_active THEN 1 ELSE 0 END), 0) AS active_count,  COALESCE(SUM(CASE WHEN t0.last_run_at IS NULL THEN 1 ELSE 0 END), 0) AS never_run_countFROM retention_policies t0GROUP BY  t0.entity_schema,  t0.entity_table,  t0.retention_days,  t0.action_on_expiryORDER BY  t0.entity_table ASC,  tenant_count DESC,  t0.entity_schema ASC,  t0.retention_days ASC,  t0.action_on_expiry ASC  t0.entity_table ASC NULLS LAST,  tenant_count DESC NULLS FIRST,  t0.entity_schema ASC NULLS LAST,  t0.retention_days ASC NULLS LAST,  t0.action_on_expiry ASC NULLS LAST

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

    SELECT
  t0.entity_schema AS entity_schema,
  t0.entity_table AS entity_table,
  t0.retention_days AS retention_days,
  t0.action_on_expiry AS action_on_expiry,
  COUNT(DISTINCT t0.tenant_id) AS tenant_count,
  COALESCE(SUM(CASE WHEN t0.is_active THEN 1 ELSE 0 END), 0) AS active_count,
  COALESCE(SUM(CASE WHEN t0.last_run_at IS NULL THEN 1 ELSE 0 END), 0) AS never_run_count
FROM retention_policies t0
GROUP BY
  t0.entity_schema,
  t0.entity_table,
  t0.retention_days,
  t0.action_on_expiry
ORDER BY
  t0.entity_table ASC NULLS LAST,
  tenant_count DESC NULLS FIRST,
  t0.entity_schema ASC NULLS LAST,
  t0.retention_days ASC NULLS LAST,
  t0.action_on_expiry ASC NULLS LAST
    1 ms10 ms100 msHyper2.9 msDuckDB8.6 msGizmoSQL8.2 msclickhouse_brew13.8 msFirebolt8.8 msDataFusion27.2 msClickHouse3.4 ms

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

  7. 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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Priyanka Desai (analyst for the Data Protection Officer):

Title: Retention policy landscape: distinct rules by entity table
Question: What retention rules actually exist across entity tables, collapsing the one-row-per-tenant duplication, so I can see real coverage and variation?
Grain: one row per distinct combination of (entity_schema, entity_table, retention_days, action_on_expiry)
Columns:
- entity schema
- entity table
- retention days
- action on expiry
- tenant count using this exact combination
- active count (is_active = true)
- never-run count (last_run_at is null)
Details: Source: retention_policies. Group by (entity_schema, entity_table, retention_days, action_on_expiry) instead of listing one row per tenant, since last night's report showed the same audit_log/730/archive combination repeated across ~120 tenants and got truncated before reaching other entity tables. This should surface every entity table that has a policy (likely a handful: audit_log, access_log, data_exports, consent_records, etc.) and flag any table where retention_days or action_on_expiry varies across tenants. Order by entity_table, then tenant_count descending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      SELECT
  t0.entity_schema AS entity_schema,
  t0.entity_table AS entity_table,
  t0.retention_days AS retention_days,
  t0.action_on_expiry AS action_on_expiry,
  COUNT(DISTINCT t0.tenant_id) AS tenant_count,
  COALESCE(SUM(CASE WHEN t0.is_active THEN 1 ELSE 0 END), 0) AS active_count,
  COALESCE(SUM(CASE WHEN t0.last_run_at IS NULL THEN 1 ELSE 0 END), 0) AS never_run_count
FROM retention_policies t0
GROUP BY
  t0.entity_schema,
  t0.entity_table,
  t0.retention_days,
  t0.action_on_expiry
ORDER BY
  t0.entity_table ASC,
  tenant_count DESC,
  t0.entity_schema ASC,
  t0.retention_days ASC,
  t0.action_on_expiry ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: entity_schema, entity_table, retention_days, action_on_expiry, tenant_count, active_count, never_run_count
    • BeaQuery compiles cleanly. Submitting.
    • Bea calls submit
      SELECT
  t0.entity_schema AS entity_schema,
  t0.entity_table AS entity_table,
  t0.retention_days AS retention_days,
  t0.action_on_expiry AS action_on_expiry,
  COUNT(DISTINCT t0.tenant_id) AS tenant_count,
  COALESCE(SUM(CASE WHEN t0.is_active THEN 1 ELSE 0 END), 0) AS active_count,
  COALESCE(SUM(CASE WHEN t0.last_run_at IS NULL THEN 1 ELSE 0 END), 0) AS never_run_count
FROM retention_policies t0
GROUP BY
  t0.entity_schema,
  t0.entity_table,
  t0.retention_days,
  t0.action_on_expiry
ORDER BY
  t0.entity_table ASC,
  tenant_count DESC,
  t0.entity_schema ASC,
  t0.retention_days ASC,
  t0.action_on_expiry ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report groups `retention_policies` by (entity_schema, entity_table, retention_days, action_on_expiry), with tenant_count as the distinct tenant count per combination, and active_count / never_run_count as row-level counts within that combination — flagged in the note since a tenant's active/last_run status could vary even when the days/action match.

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

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