Expired isn't gone: Auto-TTL's real timing contract and the 17-day math

The audit finding is not a bug — it is the documented contract. DELETE ROWS 30 DAYS AFTER created_at makes rows eligible for deletion at 30 days. Eligibility is where your control ends: predictive optimization picks them up asynchronously, and the docs are explicit that exact deletion timing is not guaranteed. If you promised the audit team day-30 erasure, you promised something the feature never offered.

Reproducible example — illustrative. A demo table with five rows aged 1 to 40 days, and the docs' own 30-day-target example (30 − 6 − 7 = 17):

-- Illustrative demo table
CREATE TABLE demo.events (
  id BIGINT,
  event_ts TIMESTAMP
);

-- 5 illustrative rows, event_ts from 1 to 40 days ago
INSERT INTO demo.events VALUES
  (1, current_timestamp() - INTERVAL 40 DAYS),
  (2, current_timestamp() - INTERVAL 25 DAYS),
  (3, current_timestamp() - INTERVAL 17 DAYS),
  (4, current_timestamp() - INTERVAL 5 DAYS),
  (5, current_timestamp() - INTERVAL 1 DAY);

-- The docs' formula: target_days − 6 − deletedFileRetentionDuration.
-- A 30-day removal target with default 7-day retention → set 17 days.
ALTER TABLE demo.events DELETE ROWS 17 DAYS AFTER event_ts;

-- Confirm the policy registered
SHOW TBLPROPERTIES demo.events;
-- Expected: autottl.expireInDays = 17, autottl.timestampColumn = event_ts

-- Immediately after the ALTER: old rows are STILL returned.
-- Expected: rows 1–3 present — eligibility, not erasure.
SELECT min(event_ts), max(event_ts) FROM demo.events;

What to expect, honestly. Immediately after the ALTER, the old rows are still returned — expected: nothing has run yet; the pipeline is asynchronous. SHOW TBLPROPERTIES should show autottl.expireInDays = 17 and autottl.timestampColumn = event_ts — expected: the policy is registered. Over the following days the background DELETE runs (up to ~3 days of buffer) and VACUUM clears the old versions (another ~3 days) — then the 7-day deleted-file retention window runs before the bytes are truly unrecoverable. Illustrative end state: rows gone from storage around day 43 from a 30-day promise. One honest caveat: the end-to-end wait is not reproducible in a single notebook session — what you can verify today is eligibility (TBLPROPERTIES) and, over the coming days, the operations in the system table.

A second docs example worth pocketing: a 90-day removal target with a 30-day configured retention needs 54 days (90 − 6 − 30). The 6-day buffer is constant; your retention window is the variable.

Recommended learning: Databricks docs: Auto time-to-live for automatic row deletion

The failure

Illustrative scenario: a data platform team puts DELETE ROWS 30 DAYS AFTER event_ts on a Unity Catalog managed clickstream table and tells the compliance team that user-level PII is erased 30 days after capture. At day 35, the audit samples the table: the rows are still queryable — and the bytes are still on storage. Nobody misconfigured anything. Expiration fired on schedule; the rows were eligible. But the background pipeline was still working through its up-to-6-day buffer, and the 7-day deleted-file retention window kept the rows time-travel accessible regardless. The team had sized the policy with the deadline, not the math.

1. Check the pre-reqs — the feature has a silent kill switch

Unity Catalog managed Delta or Iceberg table. Predictive optimization must be enabled — turning it off silently stops auto-TTL; no error, no warning. DBR 17.3 or above (17.2 and below can read and write the table but cannot manage the policy). A DATE, TIMESTAMP, or TIMESTAMP_NTZ column — nested fields work via dot notation (payload.ts) — and MODIFY permission on the table. One limitation to plan around now: the TTL time column cannot be renamed later, even with column mapping enabled.

2. Size the policy with the formula, then set it

Two docs examples to memorize. 30-day removal target with the default 7-day retention: 30 − 6 − 7 = 17 days. 90-day target with a 30-day configured retention: 90 − 6 − 30 = 54 days. The 6-day buffer is constant (up to 3 days per command: DELETE, then VACUUM); your retention window is the variable.

-- Sized with the formula: 30-day target, default 7-day retention → 17 days
ALTER TABLE catalog.schema.events DELETE ROWS 17 DAYS AFTER event_ts;

3. Know the pipeline: DELETE, then VACUUM

Predictive optimization deletes the expired rows, then VACUUM clears the old versions. Each command has up to ~3 days of buffer. And as Issue 005 covered, VACUUM uses the table's configured deleted-file retention (7-day default, 7-day minimum) — the floor that keeps expired rows recoverable until it passes.

4. Monitor it like a pipeline, not a setting

The saveable takeaway of this issue — the query that replaces deadline-watching with evidence:

-- SAVE THIS: the Auto-TTL operations monitor.
-- Shows the actual DELETE/VACUUM timestamps so you stop
-- trusting the deadline and start trusting the pipeline.
-- (Column names verified against the official predictive-optimization
-- system table reference.)
SELECT
  start_time,
  operation_type,
  operation_status,
  operation_metrics
FROM system.storage.predictive_optimization_operations_history
WHERE catalog_name = 'catalog'
  AND schema_name = 'schema'
  AND table_name = 'events'
  AND operation_type IN ('DELETE', 'VACUUM')
ORDER BY start_time DESC;

Add a DBSQL alert on failed operations so a stalled DELETE or a failed VACUUM pages someone instead of silently missing your erasure target. Note the status vocabulary: operation_status is SUCCESSFUL or FAILED: … — including FAILED: AUTO_TTL_COLUMN_DOES_NOT_EXIST_ERROR and FAILED: PRIVATE_LINK_SETUP_ERROR — and the table name columns are separate (catalog_name, schema_name, table_name), so filter on all three, not on a three-part path. DESCRIBE HISTORY shows the per-table operation trail if you need it at table scope.

5. Fix the silent production breaker: streaming readers

Every Structured Streaming job reading the table breaks on the first background deletion — auto-TTL deletions appear as data changes, and the read fails without skipChangeCommits. It is the companion incident waiting to happen, and it is one option away from never happening:

# A structured streaming job reading a TTL table breaks on the
# first background deletion unless you skip the change commits.
safe_read = (
    spark.readStream.format("delta")
    .option("skipChangeCommits", "true")
    .table("catalog.schema.events")
)

6. GDPR hard erasure: never claim 'deleted' before PURGE

On deletion-vector tables, soft-deleted rows remain physically present in the current files until PURGE rewrites them — and every deleted row stays time-travel recoverable until VACUUM plus the retention window clears the old versions. If an audit needs proof of byte-level erasure on a schedule, don't rely on the async pipeline: run the manual chain — DELETE, then REORG TABLE ... APPLY (PURGE), then VACUUM — per the Azure Databricks GDPR guidance.

Recommended learning: Data engineering workflows — pipelines, orchestration, and streaming practice