ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 0; turns off Time Travel for orders: from now on, a bad UPDATE, DELETE or DROP on it cannot be undone with AT/BEFORE or UNDROP, and history already kept falls out of reach. CREATE OR REPLACE TABLE silently drops the existing table, and INSERT OVERWRITE replaces every row. Set the retention back now; past changes can be recovered only while they are still inside the retention period.
How to recover the data
- Restore the retention immediately:
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 1;(orUNSETit to inherit from the schema or database). - If
CREATE OR REPLACE TABLEreplaced a table, rename the new one and bring the old one back within its retention period:ALTER TABLE orders RENAME TO orders_new; UNDROP TABLE orders; - If
INSERT OVERWRITEor another statement replaced rows, clone the table as it was before that statement:CREATE TABLE orders_restored CLONE orders BEFORE (STATEMENT => '<query_id>'); - For a permanent table whose history is already outside Time Travel, only Snowflake Support can attempt a Fail-safe recovery, within its 7-day period.
Why it happens
Retention is lowered to cut storage cost, often on a whole schema or database at once, without noticing that it also removes the undo. CREATE OR REPLACE is common in dbt-style and generated scripts because it makes them rerunnable, and it replaces whatever table had that name.
How to fix it
Keep Time Travel retention on tables you would need to restore, and lower costs with transient tables for data you can rebuild instead. Use CREATE TABLE IF NOT EXISTS or an explicit, versioned migration rather than CREATE OR REPLACE on tables that hold data, and change retention only in reviewed infrastructure code.
How to stop it from happening again
VERICTO-094 · Recovery reduced (Snowflake) · Severity: High · AST node: Classified > ALTER TABLE > DATA_RETENTION_TIME_IN_DAYS = 0
The statement the rule reports
-- retention.sql
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 0;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline --dialect snowflake retention.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
✖ retention.sql BLOCKED VERICTO-094 high
Classified > ALTER TABLE > DATA_RETENTION_TIME_IN_DAYS = 0
fix: Keep Time Travel retention; use CREATE … IF NOT EXISTS or a versioned migration
1 blocked · 0 allowed · 0 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
✖ Vericto blocked 1 statement (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
✖ retention.sql BLOCKED VERICTO-094 high
Classified > ALTER TABLE > DATA_RETENTION_TIME_IN_DAYS = 0
fix: Keep Time Travel retention; use CREATE … IF NOT EXISTS or a versioned migration
This is Vericto enforcing a rule — the exit code is intentional, not a crash.
To allow a finding on purpose, add an inline comment on the statement:
-- vericto:ignore[VERICTO-094] <reason> (a reason is required)
note: VERICTO-094 is schema DDL. It is reported as FLAGGED only in a migration file (under a migrations/, migration/ or migrate/ directory, a Flyway V1__name.sql or a golang-migrate 1_name.up.sql file, or a path in .vericto.toml's migration_paths); --sql, stdin and other files are checked like SQL run against the database.
The CLI blocks it and exits with code 1, so the CI job fails before the merge.
Try it free, no account
The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).
npx @vericto/vericto-cli@2.3.0 check --offline --dialect snowflake migrations/
On every pull request
The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:
on: pull_request
jobs:
vericto:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v7
with: { fetch-depth: 0 }
- uses: vericto/check-sql@v1
with: { offline: true, upload-sarif: false, dialect: snowflake }
At runtime, for AI agents
The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.
The AST condition in the engine
The rule is evaluated by the dialect's classifier; its full definition is in the open source engine's README (AGPL-3.0-only), at this page's commit.
Frequently asked questions
Related rules
- VERICTO-010: DROP TABLE or DROP DATABASE
- VERICTO-011: TRUNCATE TABLE
- VERICTO-096: Code that runs later (Snowflake)