Check recipe · Deletion and masking evidence

ISO 27001 deletion and masking evidence for Snowflake, Databricks and Redshift

DuckDB2026-11-01Script and PDF downloadable

The short answer

Deleted does not mean gone. Hand your ISO 27001 auditor three lists from Snowflake, Databricks and Redshift exports: rows past retention, deleted data still recoverable, tagged columns with no mask. The auditor decides what they show. The controls are Annex A 8.10 (Information deletion) and 8.11 (Data masking). A free script prints them; we ran it on made-up records.

The two controls, and what this page does not say

ISO/IEC 27001:2022 and its guidance, ISO/IEC 27002:2022, are paid documents. We have not read them. So this page names two Annex A controls by id and title only: 8.10, Information deletion, and 8.11, Data masking. It does not say what either one requires, and it does not know what your auditor will ask for.

What it can do is answer the question your auditor asks, with code. You write your own policy: how long each table may keep rows, how long deleted data may stay recoverable, and who may see each kind of data in the clear. Your auditor decides whether that policy is enough. The script shows whether the platforms' own records agree with what you wrote.

That needs three lists, because in a warehouse a deleted row is not necessarily gone. What each platform keeps after a delete, from its own documentation:

  • Snowflake. Time Travel keeps historical data for the table's retention period: for permanent tables on Enterprise Edition and higher, any value from 0 up to 90 days (Snowflake: Time Travel (read 2026-11-01)). When it ends, the data moves into Fail-safe, a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake (Snowflake: Fail-safe (read 2026-11-01)). Transient and temporary tables have no Fail-safe period (Snowflake: temporary and transient tables (read 2026-11-01)).
  • Databricks. VACUUM removes data files that are no longer in the table's latest state once they pass a retention threshold; The default retention threshold for vacuum is 7 days (Databricks: Remove unused data files with VACUUM (read 2026-11-01)). Until a VACUUM runs after that threshold has passed, the old files from a delete stay in storage, and The ability to query table versions older than the retention period is lost after running VACUUM (Databricks: Remove unused data files with VACUUM (read 2026-11-01)). The threshold is the table property delta.deletedFileRetentionDuration, which defaults to 7 days (Databricks: VACUUM (SQL) (read 2026-11-01)). On a table with deletion vectors, a DELETE only marks rows: their data stays in the table's current files until REORG TABLE ... APPLY (PURGE) rewrites them (OPTIMIZE and auto-compaction can rewrite some of those files, but the page guarantees only REORG rewrites all of them), and a later VACUUM removes the old files (Databricks: deletion vectors (read 2026-11-01)). Whether a table has them on by default Depends on workspace admin settings and Databricks Runtime version (Databricks: table properties (read 2026-11-01)).
  • Redshift. VACUUM DELETE reclaims the disk space of rows marked for deletion, and Redshift runs a DELETE ONLY vacuum automatically in the background (Amazon Redshift: VACUUM (read 2026-11-01)). Snapshots are separate: By default, manual snapshots are retained indefinitely, even after you delete your cluster, and automated snapshots are deleted at the end of a retention period whose default is one day (Amazon Redshift: snapshots and backups (read 2026-11-01)). A snapshot contains a copy of the cluster data as of this exact time (AWS CLI: describe-cluster-snapshots (read 2026-11-01)), so one taken before a delete still holds the rows that were deleted, unless the table was created with BACKUP NO on a cluster that supports it: RG and RA3 clusters and Serverless back up every table (Amazon Redshift: snapshots and backups (read 2026-11-01)).

What you will have at the end

A Python script, deletion_masking_evidence.py, that reads each platform's own records, exported as CSV with the platform's published column names, plus two lists you keep: a retention register and a masking policy. It runs up to 26 input checks first (fewer if you leave a platform out), then prints three lists, each with its period, its filters, the run time, its row count and a SHA-256 of its rows, and writes each as a CSV. A platform you do not export is printed as NOT CHECKED. The details are also in a PDF: Download the full guide (PDF).

  1. Rows past retention. Any table in your register with rows older than its retention is PAST RETENTION, with the count and the oldest date. A table with no row-age line is NOT MEASURED.
  2. Deleted but still recoverable. For each register table, how long deleted data stays recoverable on its platform, against the days your register allows. Longer is RECOVERABLE BEYOND POLICY. A Redshift manual snapshot kept indefinitely that was taken before a delete is INDEFINITE SNAPSHOT, whatever the register says.
  3. Masking against your policy. Every column tagged with a category your masking policy covers needs a mask in force, and any role that reads it in the clear must be on the policy's list for that category. Otherwise it is NOT MASKED, NOT MASKED FOR PUBLIC, MASK NOT ACTIVE or CLEAR READER NOT ON POLICY LIST.

The records we ran it on are generated: a seeded stand-in, not a company's data and not a measurement of anything. We have not run the script on a real Snowflake, Databricks or Redshift account. The script produces evidence; it does not say a control is met or that anyone will accept it.

What you will need

  • Your own two lists: a retention register (table, data category, retention days, the date column that ages a row, and the days your policy lets deleted data stay recoverable) and a masking policy (data category, and the roles that may see it in the clear). Table names are written database.schema.table on Snowflake and Databricks, and schema.table on Redshift.
  • Snowflake: ACCOUNT_USAGE.TABLES and ACCOUNT_USAGE.POLICY_REFERENCES can lag by up to 90 and 120 minutes, so export some hours after your last change (Snowflake: ACCOUNT_USAGE.TABLES (read 2026-11-01); Snowflake: ACCOUNT_USAGE.POLICY_REFERENCES (read 2026-11-01)). POLICY_STATUS is ACTIVE for a mask in force; other values the page lists, such as MULTIPLE_MASKING_POLICY_ASSIGNED_TO_THE_COLUMN, mean it is not.
  • Databricks: run DESCRIBE HISTORY for each register table and add the table name as a column (Databricks: Table history schema (read 2026-11-01)). History is kept for logRetentionDuration, 30 days by default, so export more often than that (Databricks: Work with table history (read 2026-11-01)). information_schema.column_masks shows only columns the exporting user can interact with, and the page marks the feature as Public Preview on Unity Catalog (Databricks: COLUMN_MASKS (read 2026-11-01)).
  • Redshift: SVV_ATTACHED_MASKING_POLICY is visible only to superusers and users with the sys:secadmin role, and Regular users will see 0 rows (Amazon Redshift: SVV_ATTACHED_MASKING_POLICY (read 2026-11-01)); an empty export fails an input check. SYS_QUERY_HISTORY and SYS_VACUUM_HISTORY show every row only to a superuser (Amazon Redshift: SYS_QUERY_HISTORY (read 2026-11-01); Amazon Redshift: SYS_VACUUM_HISTORY (read 2026-11-01)), and query text might be truncated. The pages do not say how far back the system views reach, so check your own: an input check fails if any day of the period has no query history. Snapshots come from aws redshift describe-cluster-snapshots (AWS CLI: describe-cluster-snapshots (read 2026-11-01)).
  • Python 3 and the duckdb module (we ran it with duckdb 1.5.6 and 2.0.0).

Step 1. Export each platform's records into one folder

The script reads these fourteen files from EXPORT_DIR. Column names are the platforms' own; the script lower-cases the header, so RETENTION_TIME and retention_time both work, and it stops and names the column if one is missing. Names are compared whole and without case or double quotes. Timestamps are read as YYYY-MM-DD HH:MM:SS in UTC (a fractional second, a T and a Z, +00:00 or +0000 suffix are accepted; any other offset is not): export from a session set to UTC. A value that cannot be read is counted by an input check, never skipped.

The fourteen files
FileColumns the script readsDefined in
retention_register.csvplatform, table_name, data_category, retention_days, age_column, recoverable_days_allowedYou write it: your register
row_ages.csvplatform, table_name, retention_days_used, rows_total, rows_past_retention, rows_no_age, oldest_date, measured_onThe query that --queries prints
masking_policy.csvdata_category, roles_in_clear (names separated by semicolons)You write it: your masking policy
column_tags.csvplatform, table_name, column_name, data_categoryYour tags (Snowflake TAG_REFERENCES, Databricks column tags, or a list you keep)
clear_readers.csvplatform, table_name, column_name, role_nameA list you keep: roles that see the column unmasked
snowflake_tables.csvTABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, IS_TRANSIENT, RETENTION_TIME, DELETEDSnowflake: ACCOUNT_USAGE.TABLES (read 2026-11-01)
snowflake_policy_references.csvPOLICY_NAME, POLICY_KIND, REF_DATABASE_NAME, REF_SCHEMA_NAME, REF_ENTITY_NAME, REF_ENTITY_DOMAIN, REF_COLUMN_NAME, POLICY_STATUSSnowflake: ACCOUNT_USAGE.POLICY_REFERENCES (read 2026-11-01)
databricks_history.csvtable_name, version, timestamp, operation (DESCRIBE HISTORY, one table after another)Databricks: Table history schema (read 2026-11-01)
databricks_table_properties.csvtable_name, deleted_file_retention_days (blank if the table does not set it), deletion_vectors (true or false: the table property delta.enableDeletionVectors)Databricks: VACUUM (SQL) (read 2026-11-01); Databricks: table properties (read 2026-11-01)
databricks_column_masks.csvcatalog_name, schema_name, table_name, column_name, mask_catalog, mask_schema, mask_nameDatabricks: COLUMN_MASKS (read 2026-11-01)
redshift_sys_query_history.csvuser_id, query_id, query_type, status, start_time, query_textAmazon Redshift: SYS_QUERY_HISTORY (read 2026-11-01)
redshift_sys_vacuum_history.csvschema_name, table_name, vacuum_type, is_automatic, status, start_time, end_timeAmazon Redshift: SYS_VACUUM_HISTORY (read 2026-11-01)
redshift_snapshots.csvSnapshotIdentifier, SnapshotCreateTime, SnapshotType, ManualSnapshotRetentionPeriodAWS CLI: describe-cluster-snapshots (read 2026-11-01)
redshift_attached_masking_policy.csvpolicy_name, schema_name, table_name, grantee, grantee_type, output_columnsAmazon Redshift: SVV_ATTACHED_MASKING_POLICY (read 2026-11-01)

Step 2. Write your policy, then measure row ages

No platform view holds the age of a row, so the script prints the query for you. Put your retention register in retention_register.csv, then run python3 deletion_masking_evidence.py --queries. It prints one query per register table, in the dialect of the table's platform: Snowflake and Redshift use DATEADD, Databricks uses date_sub. Each query reads the date column of every row in its table, so run it when the load is light. Here is the first one for the demo register:

SELECT 'snowflake' AS platform, 'sf_prod.sales.customer_contacts' AS table_name, 730 AS retention_days_used,
       COUNT(*) AS rows_total,
       COALESCE(SUM(CASE WHEN created_at < DATEADD(day, -730, CURRENT_DATE) THEN 1 ELSE 0 END), 0) AS rows_past_retention,
       COALESCE(SUM(CASE WHEN created_at IS NULL THEN 1 ELSE 0 END), 0) AS rows_no_age,
       CAST(MIN(created_at) AS DATE) AS oldest_date, CURRENT_DATE AS measured_on
FROM sf_prod.sales.customer_contacts;

Run each query on the platform it names and save every answer as one line of row_ages.csv. Each line carries the retention days it used, and the script checks that against your register. We printed these queries but did not run them on a platform: they are plain SQL, and you should read one before you run it. The two lists you keep look like this in the demo (the full register and the full masking policy):

retention_register.csv
platform,table_name,data_category,retention_days,age_column,recoverable_days_allowed
snowflake,sf_prod.sales.customer_contacts,contact,730,created_at,14
snowflake,sf_prod.sales.web_events,event,90,event_date,14
snowflake,sf_prod.hr.employee_notes,hr,365,noted_on,14
databricks,main.sales.customer_contacts,contact,730,created_at,14
databricks,main.sales.web_events,event,90,event_date,14
redshift,sales.customer_contacts,contact,730,created_at,14
redshift,sales.web_events,event,90,event_date,14

masking_policy.csv
data_category,roles_in_clear
contact,SUPPORT_LEAD;DATA_STEWARD
hr,HR_ADMIN
network,SECURITY_ANALYST

Step 3. How each platform is measured

The second list's "recoverable days" come from the table's settings on Snowflake, and from each delete in the period on Databricks and Redshift. On Redshift they count the time to a completed vacuum or to a snapshot's expiry; they are not a promise that a restore would work.

Snowflake. Recoverable days are the table's retention time, plus the 7-day Fail-safe unless the table is transient. A dropped table with the same name is ignored. Set SNOWFLAKE_COUNT_FAILSAFE to False if your policy counts only the days you can query the data yourself, because Fail-safe is recoverable by Snowflake only (Snowflake: Fail-safe (read 2026-11-01)). A temporary table, like a transient one, has no Fail-safe. If your account sets MIN_DATA_RETENTION_TIME_IN_DAYS, the effective retention is the larger of it and the table's own (Snowflake: Time Travel (read 2026-11-01)); the TABLES page does not say whether RETENTION_TIME reflects it, so check SHOW PARAMETERS LIKE 'MIN_DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT.

hit = con.execute("SELECT rt, transient, ttype FROM sf_t WHERE t = ? AND deleted IS NULL", [t]).fetchall()
if not hit:
    status, basis = "NOT MEASURED", "table not in the ACCOUNT_USAGE.TABLES export"
elif hit[0][0] is None:
    status, basis = "NOT MEASURED", "retention_time is not a whole number"
else:
    rt, transient, ttype = hit[0]
    fs = FAILSAFE_DAYS if (transient == "NO" and "TEMPORARY" not in ttype and SNOWFLAKE_COUNT_FAILSAFE) else 0
    days = rt + fs
    basis = (f"Time Travel {rt} d + Fail-safe {FAILSAFE_DAYS} d" if fs else
             f"Time Travel {rt} d, transient: no Fail-safe" if transient == "YES" else
             f"Time Travel {rt} d, temporary: no Fail-safe" if "TEMPORARY" in ttype else f"Time Travel {rt} d only")
    status = "RECOVERABLE BEYOND POLICY" if days > allowed else "WITHIN POLICY"

Databricks. For each DELETE in the table's history during the period, the data can be removed by the first finished VACUUM that runs at least the table's deleted-file retention after the delete; a VACUUM that ran earlier removes none of that delete's files. On a table with deletion vectors the clock starts at the first REORG after the delete (the setting DATABRICKS_PURGE_OPS), because until it rewrites the files the deleted rows are still in the table's current files; a table that does not say whether deletion vectors are on is NOT MEASURED. If no qualifying VACUUM has run, the delete is open: recoverable at least until the export. Only DELETE operations are read; MERGE, UPDATE, overwrites and TRUNCATE are not. The worst delete is the one reported.

for (d,) in dels:
    # with deletion vectors a DELETE only marks rows: a REORG ... APPLY (PURGE) must rewrite the files
    # first, and the VACUUM that removes the old files must come at least the retention after that
    start = d
    if dv == "true":
        hit = con.execute(f"SELECT ts, op FROM db_h WHERE t = ? AND op IN ({', '.join('?' for _ in purge)}) AND ts >= ? ORDER BY ts LIMIT 1",
                          [t, *purge, d]).fetchone()
        start, purge_op = (hit[0], hit[1]) if hit else (None, None)
    v = None if start is None else con.execute("SELECT min(ts) FROM db_h WHERE t = ? AND op = ? AND ts >= ?",
                                               [t, done, start + dt.timedelta(days=keep)]).fetchone()[0]
    if dv not in ("true", "false"):
        val, st, b = None, "unknown", f"delete {d:%Y-%m-%d}; deletion_vectors not stated, so removal cannot be judged"
    elif v:
        val, st = (v - d).total_seconds() / 86400, None
        b = f"delete {d:%Y-%m-%d}, " + (f"{purge_op} {start:%Y-%m-%d}, " if dv == "true" else "") + f"VACUUM finished {v:%Y-%m-%d}, files kept {keep} d"
    elif dv == "true" and start is None:
        val, st, b = (as_of_end - d).total_seconds() / 86400, "open", f"delete {d:%Y-%m-%d}, deletion vectors on and no " + "/".join(purge) + " since: rows still in live files"
    else:
        val, st, b = (as_of_end - d).total_seconds() / 86400, "open", f"delete {d:%Y-%m-%d}, no VACUUM yet old enough to remove its files (kept {keep} d)"
    if st == "unknown":
        cand = (0, 0.0, "NOT MEASURED", b)
        best = cand if best is None else best

Redshift. Deletes come from the query history: a successful statement that starts DELETE (FROM is optional in Redshift) once comments and double quotes are removed, and names its table as schema.table. One that names only the table, or sits behind a WITH clause, fails an input check when that name matches a table in your register, because the script cannot tell which schema it meant; one on any other table (a staging table) is left out. Removal is the end of the first completed Delete or Full vacuum that started after the delete. A manual snapshot taken before the delete and kept indefinitely is flagged on its own; a snapshot that expires later pushes the recoverable days out to its expiry. By default a vacuum reclaims space so that at least 95 percent of the remaining rows are not marked for deletion (Amazon Redshift: VACUUM (read 2026-11-01)), so a small share of deleted rows can survive a completed vacuum, and a WITHIN POLICY line on Redshift may be early.

indef = [c for c, ty, r in snaps if ty == "manual" and r == -1 and c < d]
exp = [c + dt.timedelta(days=(r if ty == "manual" else auto)) for c, ty, r in snaps
       if c < d and not (ty == "manual" and r == -1) and c + dt.timedelta(days=(r if ty == "manual" else auto)) > d]
if indef:
    cand = (rank["INDEFINITE SNAPSHOT"], 0.0, "INDEFINITE SNAPSHOT",
            f"delete {d:%Y-%m-%d}; a manual snapshot from {min(indef):%Y-%m-%d} is kept indefinitely")

Masking. A column counts as masked if the platform lists a mask on it: a Snowflake MASKING_POLICY row with POLICY_STATUS ACTIVE (only Snowflake reports a status), a Databricks column_masks row, or a Redshift attached policy whose output columns name the column (compared as a whole word) and whose grantee is PUBLIC. A Redshift policy attached only to a role or user is NOT MASKED FOR PUBLIC, because PUBLIC Attaches the masking policy to all users accessing the table (Amazon Redshift: ATTACH MASKING POLICY (read 2026-11-01)). Databricks lists the attachment, and the script cannot tell from it who the mask applies to. The roles that read the column in the clear come from clear_readers.csv, a list you keep, because no platform view names them, and are compared with your policy's list.

allowed = {r.strip().lower() for r in roles.split(";") if r.strip()}
clear = sorted({r for (r,) in con.execute("SELECT role FROM clr WHERE platform = ? AND t = ? AND col = ? AND role IS NOT NULL", [plat, t, col]).fetchall()})
extra = [r for r in clear if r not in allowed]
flags = []
if not active:
    flags.append("MASK NOT ACTIVE (" + ", ".join(sorted(set(inactive))) + ")" if inactive else
                 "NOT MASKED FOR PUBLIC (attached only to " + ", ".join(sorted(set(partial))) + ")" if partial else "NOT MASKED")
if extra:
    flags.append("CLEAR READER NOT ON POLICY LIST: " + ", ".join(extra))
rows.append((plat, t, col, cat, ", ".join(clear), "; ".join(flags) or "OK"))

Step 4. Run it on the clean generated records

python3 deletion_masking_evidence.py --demo writes generated exports (seed 231) in each platform's column names and reads them back. In this run every table is inside the retention in its register and every tagged column has a mask, so nothing needs follow-up. That says only that these generated exports agree with this register and policy. The status of the open Databricks deletes is OPEN, WITHIN SO FAR: no VACUUM old enough to remove their files has run, and the days so far are inside the allowance. The 26 input checks all passed (they are shown in step 6).

== Rows past retention: iso_1_rows_past_retention
   period 2026-09-16 to 2026-10-15 | filters: register tables on exported platforms | run 2026-11-01 13:48:31Z | rows 7 | sha256 c90b08ff10d0b5bfdb512aab47e8236f675186c6323878ae8342100ab3dd84f9
   platform    table                            category  retention_days  rows    rows_past_retention  oldest_date  status
   ----------  -------------------------------  --------  --------------  ------  -------------------  -----------  ------
   databricks  main.sales.customer_contacts     contact   730             207851  0                    2024-10-21   OK
   databricks  main.sales.web_events            event     90              338638  0                    2026-07-22   OK
   redshift    sales.customer_contacts          contact   730             161316  0                    2024-10-22   OK
   redshift    sales.web_events                 event     90              147811  0                    2026-07-19   OK
   snowflake   sf_prod.hr.employee_notes        hr        365             239349  0                    2025-10-18   OK
   snowflake   sf_prod.sales.customer_contacts  contact   730             51426   0                    2024-10-19   OK
   snowflake   sf_prod.sales.web_events         event     90              105751  0                    2026-07-20   OK

== Deleted but still recoverable: iso_2_deleted_but_recoverable
   period 2026-09-16 to 2026-10-15 | filters: register tables; Snowflake from table settings, the others from deletes in the period | run 2026-11-01 13:48:31Z | rows 7 | sha256 ea257ee2b272b846bd833c976dabb85aec5cd8fd3adf4ab864ab4b356fea82fb
   platform    table                            category  allowed_days  recoverable_days  basis                                                                              status
   ----------  -------------------------------  --------  ------------  ----------------  ---------------------------------------------------------------------------------  -------------------
   databricks  main.sales.customer_contacts     contact   14            7.6               delete 2026-10-08, no VACUUM yet old enough to remove its files (kept 7 d)         OPEN, WITHIN SO FAR
   databricks  main.sales.web_events            event     14            7.6               delete 2026-10-08, no VACUUM yet old enough to remove its files (kept 7 d)         OPEN, WITHIN SO FAR
   redshift    sales.customer_contacts          contact   14            7.9               delete 2026-09-20, VACUUM DELETE finished 2026-09-21, snapshot kept to 2026-09-28  WITHIN POLICY
   redshift    sales.web_events                 event     14            7.9               delete 2026-09-20, VACUUM DELETE finished 2026-09-21, snapshot kept to 2026-09-28  WITHIN POLICY
   snowflake   sf_prod.hr.employee_notes        hr        14            8                 Time Travel 1 d + Fail-safe 7 d                                                    WITHIN POLICY
   snowflake   sf_prod.sales.customer_contacts  contact   14            14                Time Travel 7 d + Fail-safe 7 d                                                    WITHIN POLICY
   snowflake   sf_prod.sales.web_events         event     14            1                 Time Travel 1 d, transient: no Fail-safe                                           WITHIN POLICY

== Masking against your policy: iso_3_masking_against_policy
   period 2026-09-16 to 2026-10-15 | filters: columns tagged with a category | run 2026-11-01 13:48:31Z | rows 10 | sha256 603359d40c940b2c5226048bae8db58eb17234cd6803c638c3302ec44e5d361a
   platform    table                            column      category  roles_reading_in_clear  status
   ----------  -------------------------------  ----------  --------  ----------------------  ------
   databricks  main.sales.customer_contacts     email       contact   data_steward            OK
   databricks  main.sales.customer_contacts     phone       contact                           OK
   databricks  main.sales.web_events            ip_address  network                           OK
   redshift    sales.customer_contacts          email       contact                           OK
   redshift    sales.customer_contacts          phone       contact                           OK
   redshift    sales.web_events                 ip_address  network   security_analyst        OK
   snowflake   sf_prod.hr.employee_notes        note_text   hr        hr_admin                OK
   snowflake   sf_prod.sales.customer_contacts  email       contact   support_lead            OK
   snowflake   sf_prod.sales.customer_contacts  phone       contact                           OK
   snowflake   sf_prod.sales.web_events         ip_address  network                           OK

== Result
   follow-up: none
   input checks: 26 of 26 passed
   RESULT: 0 item(s) need follow-up; 0 input check(s) failed; 0 platform(s) not checked.

Step 5. Run it again with findings planted in the records

python3 deletion_masking_evidence.py --demo --plant changes the generated records and nothing else: a Snowflake table with a 90-day Time Travel setting, a Databricks table whose deleted-file retention is 30 days, a Redshift manual snapshot kept indefinitely, rows older than their retention in two tables, a table never measured, a tagged column with no mask on Snowflake and on Databricks, and a role that is not on the policy's list reading an email column in the clear.

== Rows past retention: iso_1_rows_past_retention
   period 2026-09-16 to 2026-10-15 | filters: register tables on exported platforms | run 2026-11-01 13:48:31Z | rows 7 | sha256 8fc9ade2cbe47539df667646a1ce764749a7dc29dd3a23835461532d1c215ae2
   platform    table                            category  retention_days  rows    rows_past_retention  oldest_date  status
   ----------  -------------------------------  --------  --------------  ------  -------------------  -----------  --------------
   databricks  main.sales.customer_contacts     contact   730             207851  0                    2024-10-21   OK
   databricks  main.sales.web_events            event     90              338638  37                   2026-07-02   PAST RETENTION
   redshift    sales.customer_contacts          contact   730                                                       NOT MEASURED
   redshift    sales.web_events                 event     90              147811  0                    2026-07-19   OK
   snowflake   sf_prod.hr.employee_notes        hr        365             239349  0                    2025-10-18   OK
   snowflake   sf_prod.sales.customer_contacts  contact   730             51426   0                    2024-10-19   OK
   snowflake   sf_prod.sales.web_events         event     90              105751  1204                 2026-06-30   PAST RETENTION

== Deleted but still recoverable: iso_2_deleted_but_recoverable
   period 2026-09-16 to 2026-10-15 | filters: register tables; Snowflake from table settings, the others from deletes in the period | run 2026-11-01 13:48:31Z | rows 7 | sha256 f0ef5eed6dcfa0c11130b0a43bb7f0374c50d0d924f128dd1d6429bcd40530b0
   platform    table                            category  allowed_days  recoverable_days  basis                                                                        status
   ----------  -------------------------------  --------  ------------  ----------------  ---------------------------------------------------------------------------  -------------------------
   databricks  main.sales.customer_contacts     contact   14            25.6              delete 2026-09-20, no VACUUM yet old enough to remove its files (kept 30 d)  RECOVERABLE BEYOND POLICY
   databricks  main.sales.web_events            event     14            7.6               delete 2026-10-08, no VACUUM yet old enough to remove its files (kept 7 d)   OPEN, WITHIN SO FAR
   redshift    sales.customer_contacts          contact   14                              delete 2026-09-20; a manual snapshot from 2026-09-18 is kept indefinitely    INDEFINITE SNAPSHOT
   redshift    sales.web_events                 event     14                              delete 2026-09-20; a manual snapshot from 2026-09-18 is kept indefinitely    INDEFINITE SNAPSHOT
   snowflake   sf_prod.hr.employee_notes        hr        14            97                Time Travel 90 d + Fail-safe 7 d                                             RECOVERABLE BEYOND POLICY
   snowflake   sf_prod.sales.customer_contacts  contact   14            14                Time Travel 7 d + Fail-safe 7 d                                              WITHIN POLICY
   snowflake   sf_prod.sales.web_events         event     14            1                 Time Travel 1 d, transient: no Fail-safe                                     WITHIN POLICY

== Masking against your policy: iso_3_masking_against_policy
   period 2026-09-16 to 2026-10-15 | filters: columns tagged with a category | run 2026-11-01 13:48:31Z | rows 10 | sha256 b9385ecd6afa326e48d1c2eacea21c5542ab3406a2b36d6564fbf5ae464a6de6
   platform    table                            column      category  roles_reading_in_clear  status
   ----------  -------------------------------  ----------  --------  ----------------------  --------------------------------------------------
   databricks  main.sales.customer_contacts     email       contact   data_steward            OK
   databricks  main.sales.customer_contacts     phone       contact                           OK
   databricks  main.sales.web_events            ip_address  network                           NOT MASKED
   redshift    sales.customer_contacts          email       contact   marketing_analyst       CLEAR READER NOT ON POLICY LIST: marketing_analyst
   redshift    sales.customer_contacts          phone       contact                           OK
   redshift    sales.web_events                 ip_address  network   security_analyst        OK
   snowflake   sf_prod.hr.employee_notes        note_text   hr        hr_admin                OK
   snowflake   sf_prod.sales.customer_contacts  email       contact   support_lead            OK
   snowflake   sf_prod.sales.customer_contacts  phone       contact                           NOT MASKED
   snowflake   sf_prod.sales.web_events         ip_address  network                           OK

== Result
   follow-up: 2 past retention, 1 not measured, 2 recoverable beyond policy, 2 indefinite snapshot, 2 not masked or mask not active, 1 clear reader not on policy list
   input checks: 26 of 26 passed
   RESULT: 10 item(s) need follow-up; 0 input check(s) failed; 0 platform(s) not checked.

Ten items need follow-up. The Snowflake table is set to be recoverable for 97 days, 90 of Time Travel and 7 of Fail-safe, which only Snowflake can recover, against 14 allowed. The Databricks table's delete of 2026-09-20 has been recoverable for 25.6 days: a VACUUM finished on 2026-09-28, but the table keeps deleted files for 30 days, so the script counts that VACUUM as removing none of them. The Redshift snapshot is flagged without a number because it has no end date.

Step 6. Run it with faults planted in the inputs

A check is only worth showing if it can fail. python3 deletion_masking_evidence.py --demo --plant-input plants one fault for each of the 26 input checks. Every one reports FAIL, and the three lists are marked INCOMPLETE so nobody takes them for a clean result.

== Input checks
   [FAIL] every platform name in your lists is snowflake, databricks or redshift: 1 row(s)
   [FAIL] no table appears twice in the retention register: 1 row(s)
   [FAIL] every register line has a category, whole-number days, and a date column that is a plain name: 1 row(s)
   [FAIL] every column tag has a table, a column and a category: 1 row(s)
   [FAIL] every category in the masking policy is on at least one tagged column: 1 row(s)
   [FAIL] every clear reader names a table, a column and a role: 1 row(s)
   [FAIL] every clear reader is on a column that is tagged: 1 row(s)
   [FAIL] every row-age line is for a table in the register: 1 row(s)
   [FAIL] no table has two row-age lines: 1 row(s)
   [FAIL] row-age counts are whole numbers and the oldest date is YYYY-MM-DD (or blank for an empty table): 1 row(s)
   [FAIL] row ages were made with the same retention days as the register: 1 row(s)
   [FAIL] row ages were measured within 3 days before AS_OF, not after it: 1 row(s)
   [FAIL] no register table has rows with no date to age them by: 1 row(s)
   [FAIL] Snowflake: retention_time is a whole number and is_transient is YES or NO: 1 row(s)
   [FAIL] Snowflake: no register table has two live (not dropped) rows: 1 row(s)
   [FAIL] Databricks: every history timestamp is YYYY-MM-DD HH:MM:SS: 1 row(s)
   [FAIL] Databricks: every VACUUM operation in the history is one the settings name: 1 row(s)
   [FAIL] Databricks: each register table's history reaches back to the period's start (or to its first version): 1 row(s)
   [FAIL] Databricks: a table's deleted-file retention is a whole number of days or blank: 1 row(s)
   [FAIL] Databricks: every register table says whether deletion vectors are on (true or false): 1 row(s)
   [FAIL] Redshift: every query-history, vacuum and snapshot timestamp is YYYY-MM-DD HH:MM:SS: 1 row(s)
   [FAIL] Redshift: every day from the period's start to AS_OF has query history, except KNOWN_GAPS: 1 row(s)
   [FAIL] Redshift: snapshot type is manual or automated, and a manual retention is -1 or whole days from 1: 1 row(s)
   [FAIL] Redshift: REDSHIFT_AUTOMATED_RETENTION_DAYS is set when automated snapshots exist: 1 row(s)
   [FAIL] Redshift: the masking-policy export has rows (a user who is not a superuser or sys:secadmin sees 0): 1 row(s)
   [FAIL] Redshift: every DELETE that could touch a register table starts the statement and names schema.table: 1 row(s)
== Result
   follow-up: 2 not measured, 3 not masked or mask not active
   input checks: 0 of 26 passed; the lists above are INCOMPLETE
   RESULT: 5 item(s) need follow-up; 26 input check(s) failed; 0 platform(s) not checked.
The fault that trips each input check
#CheckPlanted fault
1Every platform name in your lists is snowflake, databricks or redshifta column tag for platform oracle
2No table appears twice in the retention registersales.customer_contacts listed twice on Redshift
3Every register line has a category, whole-number days and a plain date columnrecoverable_days_allowed written as 14.5
4Every column tag has a table, a column and a categorya tag with a blank category
5Every category in the masking policy is on a tagged columnthe category financial, which no column carries
6Every clear reader names a table, a column and a rolea clear-reader row with a blank role
7Every clear reader is on a tagged columna clear reader on DOB, which is not tagged
8Every row-age line is for a register tablea row-age line for sales.old_staging
9No table has two row-age linessf_prod.sales.web_events measured twice
10Row-age counts are whole numbers, the oldest date is YYYY-MM-DDrows_total written as many
11Row ages used the register's retention dayscustomer_contacts measured with 365 days against 730
12Row ages were measured shortly before AS_OFemployee_notes measured on 2026-09-01
13No register table has rows with no daterows_no_age of 4 on employee_notes
14Snowflake: retention_time is whole, is_transient is YES or NOa table with is_transient MAYBE and retention_time seven
15Snowflake: no register table has two live rowsWEB_EVENTS listed live twice
16Databricks: every history timestamp is readablea history timestamp written last tuesday
17Databricks: every VACUUM operation is one the settings namean operation called VACUUM FULL
18Databricks: history reaches back to the period's startweb_events history that starts on 2026-09-20 with no version 0
19Databricks: deleted-file retention is whole days or blankdeleted_file_retention_days written 7 days
20Databricks: every register table says whether deletion vectors are ondeletion_vectors written maybe
21Redshift: every timestamp is readablea snapshot created yesterday
22Redshift: every day has query historyno query history on 2026-10-03
23Redshift: snapshot type and retention are readablea manual snapshot with retention ten
24Redshift: the automated retention setting is setREDSHIFT_AUTOMATED_RETENTION_DAYS left unset
25Redshift: the masking-policy export has rowsan empty masking-policy export
26Redshift: every DELETE that could touch a register table starts the statement and names schema.tablea DELETE behind a WITH clause

How to read the results

  • INCOMPLETE on a list means an input check failed, so the list may be missing rows. Fix the export, or explain a quiet Redshift day in KNOWN_GAPS, before you show it to anyone.
  • PAST RETENTION is a table with rows older than your register allows. The count is as of the day the row-age query ran. NOT MEASURED means no row-age line or no platform record for that table: the list cannot say it is clean.
  • RECOVERABLE BEYOND POLICY is a delete whose data stayed recoverable longer than the days your register allows. OPEN, WITHIN SO FAR is a delete with no VACUUM yet that is old enough to remove its files (on Redshift, no completed vacuum yet): it is inside the allowance today, and not yet a finding, but it will become one if nothing runs. NO DELETE IN PERIOD means the period holds no delete to measure.
  • INDEFINITE SNAPSHOT is a Redshift manual snapshot with no end date, taken before a delete. It is flagged whatever the register allows, because a snapshot with no end date means the deleted rows it holds have no end date either. Whether to keep it is your decision.
  • NOT MASKED is a tagged column with no mask in the platform's record. NOT MASKED FOR PUBLIC is a Redshift column whose mask is attached only to named roles or users, so everyone else reads it in the clear. MASK NOT ACTIVE is a Snowflake mask whose status is not ACTIVE. CLEAR READER NOT ON POLICY LIST is a role in your clear_readers.csv that your masking policy does not name for that category. NO POLICY FOR CATEGORY is a tagged column whose category your masking policy does not mention.
  • A clean run on your own exports means only that those exports agree with your register and your policy. OK, WITHIN POLICY and WITHIN SO FAR are the script's labels, not a finding about your controls, and they say nothing about copies outside the three platforms.
  • Keep each run's settings, its SHA-256 lines and the CSVs it wrote. Your auditor decides what they show.

Run it on your own data

To run it on your own data, change only the settings block at the top of the download. Set EXPORT_DIR to the folder holding your exports. Set PERIOD_START and AS_OF. Set PLATFORMS_EXPORTED to the platforms you exported. Set REDSHIFT_AUTOMATED_RETENTION_DAYS to your cluster's automated snapshot retention in days if you export Redshift. Write your retention register and your masking policy as the two CSV files named in FILES. Run the script with --queries, run each query it prints on the platform it names, and save the answers as row_ages.csv. Export each platform's own records with the platform's own column names, tag your columns in column_tags.csv, and list the roles that read a tagged column in the clear in clear_readers.csv. If a file has another name, change its entry in FILES. Change nothing else, and run it without --demo.

We tested those steps on a second, separate set of generated exports: other file names, other schema, table and column names, names in mixed case, quoted names in a Redshift delete, a blank role list in the masking policy, a blank policy column and a blank dropped-on date, and Databricks left out of PLATFORMS_EXPORTED. The script reported Databricks as NOT CHECKED, caught the table with rows past its retention, the table never measured, the Snowflake table recoverable for 37 days against 30 allowed, the Redshift snapshot with no end date, the mask that was not active, the column with no mask and the role that was not on the list, and it ran to the end. That test is also generated data.

Download deletion_masking_evidence.py · Download the full guide (PDF)

Method

  • Input: generated records written by the script as CSV in each platform's published column names (the links in step 1), with a fixed seed (231). The register, the masking policy, the tags and the clear-reader list are generated too. The tables, roles and counts are invented.
  • Period: 2026-09-16 to 2026-10-15, UTC, inclusive. Deletes are read from that period. Everything is read as of the end of AS_OF (2026-10-15). Row ages must have been measured within three days before it.
  • Recoverable days: Snowflake from the table's settings (retention time, plus 7 days unless transient); Databricks and Redshift from the time between a delete and the event that removes the data, or the time so far if none has happened. The script reads records; it never measures bytes held in storage.
  • Redshift snapshots: a manual snapshot with retention -1 is retained indefinitely (AWS CLI: describe-cluster-snapshots (read 2026-11-01)); one with a number of days expires that many days after it was created; an automated snapshot expires after the days in REDSHIFT_AUTOMATED_RETENTION_DAYS. Only snapshots created before a delete are counted for it.
  • Fingerprints: each list is sorted on every column before it is hashed, so the same input gives the same SHA-256 on every run. We ran the clean records on duckdb 1.5.6 and on 2.0.0 and the three lists hashed the same.
  • Our assumptions, to check against one of your own rows: Databricks history calls a delete DELETE (the history schema page says so) and a finished VACUUM VACUUM END (the history schema page lists VACUUM's metrics but not its operation names, Databricks: Table history schema (read 2026-11-01), so it is a setting, and an unknown operation starting VACUUM fails an input check), and REORG TABLE ... APPLY (PURGE) is the operation REORG (a setting, DATABRICKS_PURGE_OPS; the history schema page does not list it); Snowflake POLICY_KIND is MASKING_POLICY for a masking policy; in Redshift's SYS_VACUUM_HISTORY a finished vacuum has the status Complete and a vacuum that removes deleted rows has the vacuum_type Delete or Full, which the page lists (Amazon Redshift: SYS_VACUUM_HISTORY (read 2026-11-01)), with case ignored (check one of your own rows); the platforms' column names are as the linked pages list them on 2026-11-01.

Limits

  • This is not a client result. Every record here is generated. We have not run the script on a real Snowflake, Databricks or Redshift account, and we did not run the row-age queries on a platform.
  • The standard was not read. We name Annex A 8.10 and 8.11 by id and title and say nothing about what they require. Your policy is yours; the script cannot tell you whether it is the right one. The auditor decides what evidence they accept.
  • Row ages are only as good as the query and the day it ran; a table the query was not run on is NOT MEASURED. Rows with no date to age them by fail an input check.
  • Snowflake: the days come from table settings, not from bytes actually held. Fail-safe is recoverable by Snowflake only (Snowflake: Fail-safe (read 2026-11-01)); the script counts it unless you set SNOWFLAKE_COUNT_FAILSAFE to False.
  • Databricks: a VACUUM run with a shorter retention than the table's property could remove files earlier than the script assumes, and the history schema page does not say that the history records the retention a VACUUM used. The VACUUM page also warns that with disk caching a cluster may still hold data from deleted files until it restarts (Databricks: Remove unused data files with VACUUM (read 2026-11-01)). Deletes older than the table's history are not seen, which is why an input check requires the history to reach the period's start.
  • Redshift: the script reads deletes from query text, so a DELETE it cannot place (one behind a WITH clause, or one that names only the table) fails an input check when its table name matches your register, rather than being skipped; one on any other table, such as a staging table, is left out. A successful status is not proof the transaction committed (Amazon Redshift: SYS_QUERY_HISTORY (read 2026-11-01)). VACUUM DELETE reclaims space so that at least 95 percent of the remaining rows are not marked for deletion by default (Amazon Redshift: VACUUM (read 2026-11-01)), and the script treats a completed Delete or Full vacuum as removal. Snapshots can leave out tables created with BACKUP NO on clusters that support it; RG and RA3 clusters and Serverless back up every table (Amazon Redshift: snapshots and backups (read 2026-11-01)). The script cannot see BACKUP NO, so it flags a snapshot against every table. TRUNCATE and DROP are not read. Snapshots come from describe-cluster-snapshots, which covers provisioned clusters; Redshift Serverless snapshots are not read. A masking policy attached to one role or user masks the column only for that grantee, so a column counts as masked only when a policy that lists it is attached to PUBLIC (Amazon Redshift: ATTACH MASKING POLICY (read 2026-11-01)).
  • Masking: "in force" is what the platform's own view says. The roles that read a column in the clear are a list you keep, because no platform view names them; the script trusts it. Databricks column_masks lists only columns the exporting user can interact with (Databricks: COLUMN_MASKS (read 2026-11-01)).
  • Evidence, not a verdict. The script produces evidence. It never says a control is met, and it does not know what any auditor will accept.

Tested on

Platform
DuckDB (the script reads exported CSV files; it never connects to Snowflake, Databricks or Redshift)
Edition
duckdb 1.5.6 and 2.0.0; Python 3
Date
2026-11-01
Dataset
Generated records (seed 231) in each platform's published column names; not a company's data

When this is not enough

  • Copies outside the platform: exports, extracts in BI tools, files in cloud storage, logs and backups made by other systems. The three lists see only what the three platforms record.
  • Data a delete never touched. A table can be inside its retention and still hold data your policy says should not be kept at all; the register is only as good as its list of tables and categories.
  • A policy that is silent: a category your masking policy does not cover is listed as NO POLICY FOR CATEGORY, but deciding what the policy should say is for you and your auditor.
  • Exports taken once. A single run shows one day; a delete or a mask can change the day after. Run it on a schedule and keep every run's lists and hashes.
  • Platforms other than the three, and the other Annex A controls.

See Data Control Tower on a fictional company's data

The code in this guide is free and needs nothing from us to run. Separately, Data Control Tower deploys into your own Azure subscription from a template your IT team can read before it runs. Every day it reads the records your data platforms already keep, and it never writes to them. It covers Amazon Redshift, Databricks and Snowflake, and no platform passwords are stored.

We'll walk you through it on a fictional company's data, then show what you would share to see your own.

Read about Data Control Tower