Check that each deletion ran on schedule, by the job your retention register names

What you will have at the end

A Python script, retention_deletion_check.py, that reads your retention register and the deletion records you export from Snowflake, Databricks and Redshift, all as CSV files. It first runs its input checks, then prints four lists. The same details are in a PDF: get it by email.

1. MISSED RUN. A rule whose named job left a stretch longer than the rule's own cadence with no deletion, with the two dates around the stretch.
2. NOT BY THE NAMED JOB. A deletion on a register table made by someone or something other than the job the register names: a person by hand, or another job.
3. NO RULE. A deletion on a table the register does not list.
4. NO RECORD. A register table, or a stretch of days, for which the exports hold no deletion record. The script never reads that as "nothing was deleted".

This guide checks the deletion records themselves: did each rule's deletion run, on schedule, by the job the register names. It does not look at the rows still in your tables. For rows kept past retention and deleted data that can still be recovered, see our guide on ISO 27001 deletion and masking evidence.

Your own retention policy sets every period in the register. Nothing here says a period is enough for any law, framework or auditor. The script produces evidence, and a person decides what each finding means.

We ran it four ways: on generated records with one of each finding planted, on a clean set, on a set with faults planted in the inputs, and on real exports from a Snowflake account and an Azure Databricks workspace. The Redshift part ran only on generated records in the column shapes AWS documents, because we had no Redshift account.

What you will need

• Python 3 and DuckDB (pip install duckdb); we ran it on duckdb 1.5.6 and 2.0.0.
• A retention register you keep: one row per table, naming the rule, how often its deletion should run, and the job that should run it.
• Read access to each platform's deletion records, as described above, as a user who sees every deletion.
• The period you want evidence for. It is your policy's, not ours.

A platform you do not export simply has no file. Its register rows then show under NO RECORD as NOT CHECKED.

Step 1. Write the register

One row per table, with a three-part name (database.schema.table). cadence_days is how often the deletion should run: a gap longer than that is a missed run. job_type says which field of the deletion record names the job, and job is its value:

• Snowflake: query_tag, user or role.
• Databricks: job_id or user.
• Redshift: user or label (the query label).

table_name,platform,rule_id,policy_reference,retention_days,cadence_days,job_type,job
FINANCE_DB.RETAIL.CALL_RECORDINGS,snowflake,R-102,DS-4.2,365,15,query_tag,retention-purge
main.sales.support_tickets,databricks,R-202,DS-4.4,1095,14,job_id,1002
analytics.public.clickstream,redshift,R-301,DS-4.5,400,7,user,svc_retention

retention_days is checked as a whole number and not otherwise used: this script does not read the rows in your tables, so it never compares them with it. Names are compared without regard to case. Give a retention job a query tag, label or login of its own, so the register can name it.

Step 2. Export each platform's deletion records

Run python3 retention_deletion_check.py --queries to print the export statements below. The comments say what to save each result as and what to write in coverage.csv: one row per export, giving the first and last day that export could see. Write times in UTC as YYYY-MM-DD HH:MM:SS.

-- 1. Snowflake. Needs a role that can read SNOWFLAKE.ACCOUNT_USAGE (it keeps 365 days; latency up to 45 minutes).
--    Without it, INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER keeps about 7 days for one user (same columns).
SELECT query_id, query_text, database_name, schema_name, query_type, user_name, role_name, query_tag,
       execution_status, TO_CHAR(CONVERT_TIMEZONE('UTC', start_time), 'YYYY-MM-DD HH24:MI:SS') AS start_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_type IN ('DELETE', 'TRUNCATE_TABLE')
  AND start_time >= DATEADD('day', -365, CURRENT_TIMESTAMP())
ORDER BY start_time, query_id;
-- Save as snowflake_deletions.csv. In coverage.csv: snowflake, <today minus 364 days> (the first full day), <today>.

-- 2. Databricks. A notebook, once per register table (DESCRIBE HISTORY cannot be a subquery). Keep ALL operations:
--    the earliest row tells the check where each table's history starts.
--    spark.conf.set("spark.sql.session.timeZone", "UTC")
--    for t in TABLES_TO_WATCH:   # every register table, plus any other table you want NO RULE to catch
--        spark.sql(f"DESCRIBE HISTORY {t}").selectExpr(f"'{t}' AS table_name", "version",
--            "date_format(timestamp, 'yyyy-MM-dd HH:mm:ss') AS timestamp", "userName AS user_name", "operation",
--            "job.jobId AS job_id")   # null when no job wrote the commit
--    Save the union as databricks_history.csv. In coverage.csv: databricks, (leave blank), <today>.

-- 3. Redshift. AWS documents seven days of log history for its STL views and states no period for SYS_QUERY_HISTORY,
--    so plan for about seven: run this at least weekly and add every export's rows to one file. One coverage.csv
--    row per export: redshift, <first day it reached>, <day you ran it>. Run it as a superuser (or a user with
--    SYSLOG ACCESS UNRESTRICTED): other users see only their own rows.
SELECT query_id, TRIM(username) AS username, TRIM(database_name) AS database_name, TRIM(query_label) AS query_label,
       TRIM(query_type) AS query_type, TRIM(status) AS status,
       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time, TRIM(query_text) AS query_text
FROM sys_query_history
WHERE LOWER(query_text) LIKE '%delete%' OR LOWER(query_text) LIKE '%truncate%'   -- broad on purpose; the script picks out the deletions
ORDER BY start_time, query_id;
-- Save as redshift_deletions.csv (times in UTC).

An example coverage.csv. We assume about seven days of Redshift history, so a month is five weekly exports, one row each (two are shown):

platform,covered_from,covered_to
snowflake,2026-10-01,2026-10-31
databricks,,2026-10-31
redshift,2026-10-01,2026-10-07
redshift,2026-10-08,2026-10-14

Databricks is different: history is per table, so the script reads each table's own first history row to see where its record starts, and the Databricks row only needs a covered_to (the day you exported). For Databricks to catch a deletion on a table the register does not list, export the history of that table too. A table you leave out cannot appear under NO RULE.

The script rejects a coverage row that claims more days than the platform keeps from one export: 365 for Snowflake's ACCOUNT_USAGE and 7 for Redshift (our assumption above). If your Redshift audit logs go to Amazon S3 or you copy the system tables elsewhere, build the export from that source and split the coverage into rows of 7 days or fewer.

Step 3. How each list is decided

• What counts as a deletion. A successful statement whose first word, after any leading comments, is DELETE or TRUNCATE (Redshift makes FROM optional after DELETE), or, on Databricks, a history row whose operation is DELETE or TRUNCATE. A refused or failed attempt is counted in the population line and is not a deletion. A deletion that removed no rows still counts as a run: the script reads that a deletion ran, not how many rows it removed.
• MISSED RUN. Only deletions by the named job count toward the schedule; a deletion by hand does not. Within each stretch of days the exports cover, the script lists every gap between consecutive deletions by the named job (and between the stretch's ends and its first or last deletion) that is longer than cadence_days. Days are UTC calendar days. Days no export covers are never counted as missed; they go to NO RECORD.
• NOT BY THE NAMED JOB. Any deletion on a register table in the period whose actor does not match the job in the register, grouped by who did it, with first and last day and a count. A blank actor field does not match.
• NO RULE. A deletion on a table the register does not list. A table in exceptions.csv (a reason is required; a trailing * matches a prefix) is left out.
• NO RECORD. A platform with no usable file (NOT CHECKED), days inside the period that no export's coverage reaches, and, on Databricks, days before a table's first history row when that row is not version 0 (the earlier history has expired or was not exported). If the first row is version 0, the table did not exist before it and nothing is missing.
• Input checks come first. Unreadable files, missing columns, blank cells, unknown platforms, job types a platform does not have, duplicate tables, names that are not three-part, dates not written as YYYY-MM-DD, times not written YYYY-MM-DD HH:MM:SS, statuses left blank, table names the script cannot resolve, and coverage that claims more than the platform keeps. If any fails, the lists are marked INCOMPLETE and the exit code is 2. A row with a bad value is left out and counted, never guessed.
• Fingerprints. Each input file's SHA-256 and a SHA-256 of the deletion events used are printed, so a re-run shows whether the export changed. Each run writes its own run-<UTC time> folder with findings.csv and inputs.csv and deletes nothing.

Step 6. Run 3: faults planted in the inputs

An input check that cannot fail is no check. --plant inputs writes a register and exports with one fault for each input check: a blank cell, an unknown platform, a cadence that is not a number, a job type Databricks does not have, a table name with one part, the same table twice, a Databricks header misspelled so a column is missing, a coverage row that claims 31 days from one Redshift export, a coverage row that ends before it starts, a date not written as YYYY-MM-DD, an exception with no reason, a Snowflake deletion with a time in another format, one with no status, one whose table name cannot be resolved, and one that names its table only when it runs (DELETE FROM IDENTIFIER(:tbl)).

python3 retention_deletion_check.py --make-demo demo-inputs --plant inputs
python3 retention_deletion_check.py --dir demo-inputs --start 2026-10-01 --end 2026-10-31

INPUT CHECKS
  [FAIL] 15 problem(s) in the inputs
      - register.csv line 8: table_name is blank; rule_id is blank
      - register.csv line 9: platform 'oracle' is not one of snowflake, databricks, redshift
      - register.csv line 10: cadence_days 'weekly' is not a whole number of days, 1 or more
      - register.csv line 11: job_type 'query_tag' is not one of job_id, user for databricks
      - register.csv line 12: table_name 'orders' is not a three-part name (database.schema.table)
      - register.csv line 13: table FINANCE_DB.RETAIL.ORDER_ARCHIVE already has a rule on line 2
      - databricks_history.csv: no column named timestamp
      - coverage.csv line 9: claims 31 days from one export; this check allows 7 for redshift (take exports more often and add one row per export)
      - coverage.csv line 10: covered_from is after covered_to
      - coverage.csv line 11: covered_from '01/10/2026' is not written as YYYY-MM-DD
      - exceptions.csv line 3: needs a platform (snowflake, databricks, redshift), a table name and a reason
      - snowflake_deletions.csv: 1 deletion row(s) with a start time not written as YYYY-MM-DD HH:MM:SS (UTC), left out
      - snowflake_deletions.csv: 1 row(s) touching a register table have no status, left out
      - snowflake_deletions.csv: 1 deletion row(s) with a table named only when the statement runs (IDENTIFIER(...)), so the table is unknown, left out
      - snowflake_deletions.csv: 1 deletion row(s) touching a register table name no table this check can resolve to database.schema.table, left out

The script reports each fault, leaves the bad rows out, and marks the result INCOMPLETE. Because the Databricks file could not be used, its two rules show as NOT CHECKED rather than clean:

4. NO RECORD  (2)
  Days or tables the exports cannot speak for. Never read as 'nothing was deleted'.
  R-202 | databricks | main.sales.support_tickets | policy DS-4.4
      NOT CHECKED: no usable databricks_history.csv was read, so there is no deletion record to read (this is not 'nothing was deleted')  [2026-10-01 to 2026-10-31; 0 deletion(s)]
  R-201 | databricks | main.sales.web_sessions | policy DS-4.3
      NOT CHECKED: no usable databricks_history.csv was read, so there is no deletion record to read (this is not 'nothing was deleted')  [2026-10-01 to 2026-10-31; 0 deletion(s)]

SUMMARY: 0 missed run, 0 not by the named job, 0 no rule, 2 no record
RESULT: INCOMPLETE: an input check failed: the lists above may be wrong or short

The exit code was 2. We also ran the script on an unreadable register, on a folder with no register, on a missing coverage.csv, and on an export with no coverage row; each failed an input check and none crashed.

Step 7. Run 4: real exports from a Snowflake account and an Azure Databricks workspace

The generated runs only show that the script reads files. This run makes real deletions on real platforms, exports their records with the statements in Step 2 (or what the sandbox allows), and runs the download on the result. The records are real; the tables and the register are ours, made for this run in a sandbox schema.

Snowflake (Enterprise Edition)

We created four tables in a schema of the database CONTENT_LOOP_SANDBOX and put twelve rows in each. Then we deleted rows from two of them in a session with the query tag retention-job (the "job"), deleted from one of those again with the tag unset (by hand), and deleted from a fourth table that has no rule.

> CREATE SCHEMA IF NOT EXISTS CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER
status
----------------------------------------------------------------------
Schema CHECK_DELETION_AGAINST_RETENTION_REGISTER successfully created.

> USE SCHEMA CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER
status
--------------------------------
Statement executed successfully.

> CREATE OR REPLACE TABLE CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.ORDER_ARCHIVE (id NUMBER, created_at DATE)
status
-----------------------------------------
Table ORDER_ARCHIVE successfully created.

> ALTER SESSION SET QUERY_TAG = 'retention-job'
status
--------------------------------
Statement executed successfully.

> DELETE FROM order_archive WHERE created_at < DATEADD('day', -730, CURRENT_DATE())
number of rows deleted
----------------------
4

> DELETE FROM CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.call_recordings WHERE created_at < DATEADD('day', -730, CURRENT_DATE())
number of rows deleted
----------------------
4

> ALTER SESSION UNSET QUERY_TAG
status
--------------------------------
Statement executed successfully.

> DELETE FROM call_recordings WHERE created_at < DATEADD('day', -365, CURRENT_DATE())
number of rows deleted
----------------------
4

> DELETE FROM scratch_export WHERE id < 3
number of rows deleted
----------------------
3

A read the sandbox role is refused. Our sandbox role cannot read SNOWFLAKE.ACCOUNT_USAGE. This is the error word for word. A reader needs a role that can read that schema. We could not run the Step 2 statement, so we have not tested it.

-- The read our sandbox role is refused (a reader needs a role that can read SNOWFLAKE.ACCOUNT_USAGE):
ERROR: 002003 (02000): SQL compilation error:
Schema 'SNOWFLAKE.ACCOUNT_USAGE' does not exist or not authorized. Your primary role CONTENT_LOOP_ROLE or one of your secondary roles must have USAGE or any other privilege granted on SCHEMA SNOWFLAKE.ACCOUNT_USAGE.

So we ran the INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER form instead. It has the same columns, covers about seven days, and returns only this user's statements (the export below filters to the sandbox schema and to the time this run began, because earlier test runs are in the same history). The coverage row for this export is 7 days, not 365.

> SELECT query_id, query_text, database_name, schema_name, query_type, user_name, role_name, query_tag, execution_status, TO_CHAR(CONVERT_TIMEZONE('UTC', start_time), 'YYYY-MM-DD HH24:MI:SS') AS start_time FROM TABLE(CONTENT_LOOP_SANDBOX.INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(USER_NAME => 'CONTENT_LOOP_RUNNER', END_TIME_RANGE_START => '2026-11-05 19:07:33+00:00'::TIMESTAMP_LTZ, RESULT_LIMIT => 10000)) WHERE query_type IN ('DELETE', 'TRUNCATE_TABLE') AND schema_name = 'CHECK_DELETION_AGAINST_RETENTION_REGISTER' ORDER BY start_time, query_id
QUERY_ID                             | QUERY_TEXT                                                                                                                                         | DATABASE_NAME        | SCHEMA_NAME                               | QUERY_TYPE | USER_NAME           | ROLE_NAME         | QUERY_TAG     | EXECUTION_STATUS | START_TIME
-------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+-------------------------------------------+------------+---------------------+-------------------+---------------+------------------+--------------------
01c836bb-0720-c1a7-0000-067104635b3a | DELETE FROM order_archive WHERE created_at < DATEADD('day', -730, CURRENT_DATE())                                                                  | CONTENT_LOOP_SANDBOX | CHECK_DELETION_AGAINST_RETENTION_REGISTER | DELETE     | CONTENT_LOOP_RUNNER | CONTENT_LOOP_ROLE | retention-job | SUCCESS          | 2026-11-05 19:07:38
01c836bb-0720-c1a7-0000-067104635b3e | DELETE FROM CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.call_recordings WHERE created_at < DATEADD('day', -730, CURRENT_DATE()) | CONTENT_LOOP_SANDBOX | CHECK_DELETION_AGAINST_RETENTION_REGISTER | DELETE     | CONTENT_LOOP_RUNNER | CONTENT_LOOP_ROLE | retention-job | SUCCESS          | 2026-11-05 19:07:38
01c836bb-0720-b6a8-0000-067104634c42 | DELETE FROM call_recordings WHERE created_at < DATEADD('day', -365, CURRENT_DATE())                                                                | CONTENT_LOOP_SANDBOX | CHECK_DELETION_AGAINST_RETENTION_REGISTER | DELETE     | CONTENT_LOOP_RUNNER | CONTENT_LOOP_ROLE |               | SUCCESS          | 2026-11-05 19:07:39
01c836bb-0720-c177-0000-06710463351e | DELETE FROM scratch_export WHERE id < 3                                                                                                            | CONTENT_LOOP_SANDBOX | CHECK_DELETION_AGAINST_RETENTION_REGISTER | DELETE     | CONTENT_LOOP_RUNNER | CONTENT_LOOP_ROLE |               | SUCCESS          | 2026-11-05 19:07:39

Wrote 4 row(s) to snowflake_deletions.csv

Databricks (Azure Databricks, Premium, serverless, Unity Catalog)

We created two Delta tables in the schema content_loop.check_deletion_against_retention_register, inserted rows and deleted rows from each, then read DESCRIBE HISTORY for each table.

> CREATE SCHEMA IF NOT EXISTS content_loop.check_deletion_against_retention_register
(done)

> SELECT current_user() AS runner
runner
------------------------------------
94d0d5fd-0163-4380-86f4-60838753527e

> CREATE OR REPLACE TABLE content_loop.check_deletion_against_retention_register.web_sessions (id BIGINT, created_at DATE)
(done)

> INSERT INTO content_loop.check_deletion_against_retention_register.web_sessions SELECT id, date_sub(current_date(), CAST(id * 100 AS INT)) FROM range(12)
num_affected_rows | num_inserted_rows
------------------+------------------
12                | 12

> DELETE FROM content_loop.check_deletion_against_retention_register.web_sessions WHERE created_at < date_sub(current_date(), 730)
num_affected_rows
-----------------
4

> DESCRIBE HISTORY content_loop.check_deletion_against_retention_register.web_sessions   (the export keeps these five columns)
version | timestamp           | userName                             | operation               | job
--------+---------------------+--------------------------------------+-------------------------+-----
0       | 2026-11-05 19:01:58 | 94d0d5fd-0163-4380-86f4-60838753527e | CREATE OR REPLACE TABLE | NULL
1       | 2026-11-05 19:02:10 | 94d0d5fd-0163-4380-86f4-60838753527e | WRITE                   | NULL
2       | 2026-11-05 19:02:16 | 94d0d5fd-0163-4380-86f4-60838753527e | DELETE                  | NULL

> DESCRIBE HISTORY content_loop.check_deletion_against_retention_register.support_tickets   (the export keeps these five columns)
version | timestamp           | userName                             | operation               | job
--------+---------------------+--------------------------------------+-------------------------+-----
0       | 2026-11-05 19:02:19 | 94d0d5fd-0163-4380-86f4-60838753527e | CREATE OR REPLACE TABLE | NULL
1       | 2026-11-05 19:02:21 | 94d0d5fd-0163-4380-86f4-60838753527e | WRITE                   | NULL
2       | 2026-11-05 19:02:24 | 94d0d5fd-0163-4380-86f4-60838753527e | DELETE                  | NULL

Wrote 6 row(s) to databricks_history.csv
Wrote register.csv and coverage.csv (Databricks runner, a service principal id: 94d0d5fd-0163-4380-86f4-60838753527e)

Our statements did not run from a job, so job is NULL in every row. That gives a real case for each outcome: a register rule that names the runner's own user passes, and a rule that names a job id the history never shows is a real NOT BY THE NAMED JOB. We could not show a row that a job wrote. The history schema page says only that job is populated for commits written from a Lakeflow job; it does not list the fields of that struct, so the export in Step 2 reads job.jobId as our assumption. Check it against one row of your own history before you rely on it.

The download on those exports

The register for this run, written to name the sandbox's own objects. Three Snowflake rules and two Databricks rules; the Databricks rule D-1 names the runner's user, and D-2 names a job id that never ran:

table_name,platform,rule_id,policy_reference,retention_days,cadence_days,job_type,job
CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.ORDER_ARCHIVE,snowflake,S-1,sandbox policy 1,730,7,query_tag,retention-job
CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.CALL_RECORDINGS,snowflake,S-2,sandbox policy 2,365,7,query_tag,retention-job
CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.SESSION_LOG,snowflake,S-3,sandbox policy 3,90,3,query_tag,retention-job
content_loop.check_deletion_against_retention_register.web_sessions,databricks,D-1,sandbox policy 4,90,7,user,94d0d5fd-0163-4380-86f4-60838753527e
content_loop.check_deletion_against_retention_register.support_tickets,databricks,D-2,sandbox policy 5,365,7,job_id,555111

python3 retention_deletion_check.py --dir real-exports --start 2026-10-30 --end 2026-11-05

INPUT CHECKS
  [ok] 0 problem(s) in the inputs

POPULATION (what each export held)
  snowflake: 4 row(s); 0 not a deletion, 0 refused or failed (not counted), 0 bad time, 0 outside the period; 4 deletion(s) in the period, 3 on register tables
  databricks: 6 row(s); 4 not a deletion, 0 refused or failed (not counted), 0 bad time, 0 outside the period; 2 deletion(s) in the period, 2 on register tables
  redshift: not exported (NOT CHECKED)
  deletion events used: sha256 2823e6c93372fe8a1c04c8f96dba1a467ad71981fc8ba057f9d4601c5b5ed57c

1. MISSED RUN  (1)
  A rule whose named job left a stretch longer than its cadence with no deletion.
  S-3 | snowflake | CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.SESSION_LOG | policy sandbox policy 3
      6 days from 2026-10-30 to 2026-11-05 with no deletion by the named job (query tag retention-job) in between; the rule runs every 3  [2026-10-30 to 2026-11-05]

2. NOT BY THE NAMED JOB  (2)
  A deletion on a register table by someone or something else.
  D-2 | databricks | content_loop.check_deletion_against_retention_register.support_tickets | policy sandbox policy 5
      deletion not by the named job (job id 555111): user 94d0d5fd-0163-4380-86f4-60838753527e, job id (blank)  [2026-11-05; 1 deletion(s)]
  S-2 | snowflake | CONTENT_LOOP_SANDBOX.CHECK_DELETION_AGAINST_RETENTION_REGISTER.CALL_RECORDINGS | policy sandbox policy 2
      deletion not by the named job (query tag retention-job): user CONTENT_LOOP_RUNNER, role CONTENT_LOOP_ROLE, query tag (blank)  [2026-11-05; 1 deletion(s)]

3. NO RULE  (1)
  A deletion on a table the register does not list.
  snowflake | content_loop_sandbox.check_deletion_against_retention_register.scratch_export
      deletion on a table the register does not list, by 1 user(s)  [2026-11-05; 1 deletion(s)]

4. NO RECORD  (0)
  Days or tables the exports cannot speak for. Never read as 'nothing was deleted'.
  none

SUMMARY: 1 missed run, 2 not by the named job, 1 no rule, 0 no record
RESULT: FINDINGS: each finding is for a person to explain; it is evidence, not a verdict

Each finding in Run 4 is one we caused. S-3's table was never deleted from, so with a rule that runs every three days over a six-day record it is a MISSED RUN. S-2's table was deleted once by the tagged job and once by hand, and the hand deletion is listed. The fourth table has no rule. D-2 names a job that never ran, so its deletion is listed. D-1 and S-1 pass. The Databricks tables were created that day, so their history starts at version 0 and the script finds no missed run on them yet, which is correct: it cannot know about days before the table existed.

Run it on your own data

To run retention_deletion_check.py on your own data, change nothing in the script. Save your register as register.csv and your exports under the file names listed at the top of the script, in one folder, with the column headings shown there and every time written YYYY-MM-DD HH:MM:SS in UTC. Add one row to coverage.csv for each export you took. Then run the script with --dir set to that folder and --start and --end set to the first and last day of your period.

We tested those steps on a second, separate set of generated exports built the way a reader's would be: other database, schema and table names, names in mixed case, a name written with quotes, SQL text that starts with a comment and runs over several lines, a register saved with the byte-order mark Excel adds, blank query tags and job ids where a platform has none, and a refused attempt by someone else. It also held one planted fault for each of the four lists. The script read every file with no input problem, left the refused attempt out, and printed one finding in each list. That test is also generated data.

Download retention_deletion_check.py

When this is not enough

• Rows left in a table past retention, and deleted data that can still be recovered. This script reads deletion records, not tables; see our ISO 27001 deletion and masking guide for those.
• Deletions made outside the three platforms: files, extracts, backups and copies in other systems.
• A period longer than a platform keeps. If you did not export in time, the days are gone and show as NO RECORD. The fix is a schedule that exports and keeps every run's files.
• Deciding what the register should say, how long to keep each kind of data, and what a finding means. That is for you, your lawyer and your auditor.