DB Olympics

Queries · q117 · Data Protection Officer · week 1, night 2

Retention policy inventory: what rules exist and where

“What data-retention policies has Tidewick actually configured, for which entity tables, with what retention windows and expiry actions?”

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 SQLViktorOld-school warehouse engineer: explicit JOIN chains straight to the base tables, derived tables, GROUP BY with CASE pivots, few CTEs.

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 2, request 3 of 3

    Report 1 showed overall low DSAR volume but exposed a real red flag for Ines: 3 requests have sat open since 2023-05, 2024-07, and 2025-03 (now 0.5–2.5 years old) with no completion — these are the "legal deadline risk" she asked about, and the aggregate monthly view hides who/what they are. Also portability requests were 0 in every single month across 3 years, worth confirming in the drill-down rather than assuming it's a data artifact. Report 2 was truncated at 50 rows mid-way through marketing_email's monthly series (2024-02), and marketing_email showed a real declining opt-in trend (99.4%→94.4%) that I need to see through to 2025-12; analytics_cookies showed a suspiciously perfect 100% opt-in with zero withdrawals ever recorded across 36 months, which I'm deferring rather than burning a slot on tonight, but will flag/verify later if time allows. Report 3 came back clean — only 6 compliance_events total, all resolved, no backlog, no unresolved aging — so I consider Ines's question 3 answered for now and am moving a slot to question 4 (retention vs. policy), starting with a cheap inventory of what retention_policies actually exist before committing to the harder actual-vs-policy comparison next.

    Retention policy inventory: what rules exist and where
    Question
    What data-retention policies has Tidewick actually configured, for which entity tables, with what retention windows and expiry actions?
    One row per
    retention_policies record
    Columns
    • tenant name
    • policy name
    • entity schema
    • entity table
    • retention days
    • action on expiry
    • is active
    • last run at

  3. Viktor 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
  t.name AS tenant_name,
  rp.name AS policy_name,
  rp.entity_schema,
  rp.entity_table,
  rp.retention_days,
  rp.action_on_expiry,
  rp.is_active,
  rp.last_run_at
FROM retention_policies rp
JOIN tenants t ON t.id = rp.tenant_id
ORDER BY rp.entity_table, t.name, rp.id
    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. The query lists every configured retention policy (active and inactive) joined to tenant names, at one row per policy, ordered by entity_table then tenant name as requested.”

  4. The report comes back overnight

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

    tenant_name                   | policy_name         | entity_schema | entity_table | retention_days | action_on_expiry | is_active | last_run_at        
------------------------------+---------------------+---------------+--------------+----------------+------------------+-----------+--------------------
Alpine Loft                   | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Ampere & Co.                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Ampfeld Elektronik            | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Atelier Nouveau               | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Aurum & Ash                   | audit log retention | audit         | audit_log    |            730 | archive          | true      | NULL               
Ballycorran Industrial Supply | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Barkley & Co.                 | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Basecamp Nord                 | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Bijoux Lune                   | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Brennquell Kaffee             | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Brewline Coffee               | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Brick & Bobbin                | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Buchstabe                     | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Bytefield                     | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Castell Fasteners             | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Chocolaterie Mireille         | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Cinder Peak Sports Co.        | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Circuit Harbor                | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Coastline Interiors           | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Copper Lane                   | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Courtside Collective          | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Cragsley Outfitters           | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Deskline Supplies             | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Dewdrop Studio                | audit log retention | audit         | audit_log    |            730 | archive          | true      | NULL               
Driftwood Home                | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Feather & Fin                 | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Fell & Fjell                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Fennel & Flint                | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Fern & Fable                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Fjordline Wear                | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Folio & Fern                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Glasshouse Grooming           | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Goldkind                      | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Gratlinie Outdoor             | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Greyfriars Supply             | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Grind Collective              | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Halden Boots                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | NULL               
Hanse Industriebedarf         | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Harbour Swim                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Hautnah                       | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Haven Linen                   | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Honeycomb Larder              | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Hound Hamper                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Hus & Hem                     | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Inkwell & Quire               | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Juniper Apothecary            | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Kabelwerk                     | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Kaffeekontor Nord             | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Kayak Corner                  | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
Kestrel Denim                 | audit log retention | audit         | audit_log    |            730 | archive          | true      | 2025-12-31 03:00:00
(720 rows, first 50 printed)

  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:

  6. It becomes benchmark query q117

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

    • made explicita selected t.col gets AS col, its standard name made explicit (6×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
    The benchmark text, against the submitted SQL
    SELECT  t.name AS tenant_name,  rp.name AS policy_name,  rp.entity_schema,  rp.entity_table,  rp.retention_days,  rp.action_on_expiry,  rp.is_active,  rp.last_run_at  rp.entity_schema AS entity_schema,  rp.entity_table AS entity_table,  rp.retention_days AS retention_days,  rp.action_on_expiry AS action_on_expiry,  rp.is_active AS is_active,  rp.last_run_at AS last_run_atFROM retention_policies rpJOIN tenants t ON t.id = rp.tenant_idORDER BY rp.entity_table, t.name, rp.idORDER BY rp.entity_table NULLS LAST, t.name NULLS LAST, rp.id NULLS LAST

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

    SELECT
  t.name AS tenant_name,
  rp.name AS policy_name,
  rp.entity_schema AS entity_schema,
  rp.entity_table AS entity_table,
  rp.retention_days AS retention_days,
  rp.action_on_expiry AS action_on_expiry,
  rp.is_active AS is_active,
  rp.last_run_at AS last_run_at
FROM retention_policies rp
JOIN tenants t ON t.id = rp.tenant_id
ORDER BY rp.entity_table NULLS LAST, t.name NULLS LAST, rp.id NULLS LAST
    1 ms10 ms100 msHyper3.0 msDuckDB2.6 msGizmoSQL5.2 msclickhouse_brew68.4 msFirebolt15.1 msDataFusion6.5 msClickHouse15.2 ms

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

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

Title: Retention policy inventory: what rules exist and where
Question: What data-retention policies has Tidewick actually configured, for which entity tables, with what retention windows and expiry actions?
Grain: one row per retention_policies record
Columns:
- tenant name
- policy name
- entity schema
- entity table
- retention days
- action on expiry
- is active
- last run at
Details: Source: retention_policies joined to tenants for the name. No filtering — list every configured policy (active and inactive) so we can see coverage gaps as well as configured rules. This is a lookup/inventory step to identify which entity tables have a policy before comparing actual record ages against the stated retention_days in a follow-up report. Order by entity_table, then tenant name.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Viktor calls dry_run
      SELECT
  t.name AS tenant_name,
  rp.name AS policy_name,
  rp.entity_schema,
  rp.entity_table,
  rp.retention_days,
  rp.action_on_expiry,
  rp.is_active,
  rp.last_run_at
FROM retention_policies rp
JOIN tenants t ON t.id = rp.tenant_id
ORDER BY rp.entity_table, t.name, rp.id
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, policy_name, entity_schema, entity_table, retention_days, action_on_expiry, is_active, last_run_at
    • Viktor calls submit
      SELECT
  t.name AS tenant_name,
  rp.name AS policy_name,
  rp.entity_schema,
  rp.entity_table,
  rp.retention_days,
  rp.action_on_expiry,
  rp.is_active,
  rp.last_run_at
FROM retention_policies rp
JOIN tenants t ON t.id = rp.tenant_id
ORDER BY rp.entity_table, t.name, rp.id
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • ViktorSubmitted. The query lists every configured retention policy (active and inactive) joined to tenant names, at one row per policy, ordered by entity_table then tenant name as requested.

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

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