Repository navigation
Expired snapshot discovery qualifies model queries before cleanup #6210
Description
Summary
Discovering expired snapshots for janitor cleanup prepares their optimized queries. This makes cleanup depend on optimizer work for models that are no longer referenced by an environment.
The example below creates one synthetic snapshot in a temporary DuckDB state database. Without running any model SQL, it shows get_expired_snapshots() calling SQLGlot's qualify() after that snapshot expires. The unexpired control does not call it.
Can cleanup identify the required physical tables and versions without qualifying the stored model's SELECT query? An expensive or incompatible historical query can otherwise make metadata cleanup expensive too.
Environment
- SQLMesh:
0.236.4 - SQLGlot:
30.8.0 - DuckDB:
1.4.2, used only for temporary local state - Python: CPython
3.14.7 - One preparation worker, so the instrumentation observes optimizer calls in the same process
Reproduce
Save this as reproduce.py:
import os
os.environ["MAX_FORK_WORKERS"] = "1"
os.environ["SQLMESH__DISABLE_ANONYMIZED_ANALYTICS"] = "true"
import json
from pathlib import Path
from tempfile import TemporaryDirectory
from unittest.mock import patch
from sqlglot import exp
from sqlmesh.core import renderer
from sqlmesh.core.config.connection import DuckDBConnectionConfig
from sqlmesh.core.model import SqlModel
from sqlmesh.core.snapshot import Snapshot, SnapshotChangeCategory
from sqlmesh.core.state_sync.common import ExpiredBatchRange
from sqlmesh.core.state_sync.db.facade import EngineAdapterStateSync
from sqlmesh.utils.date import now_timestamp
with TemporaryDirectory() as directory:
root = Path(directory)
adapter = DuckDBConnectionConfig(database=str(root / "metadata.duckdb")).create_engine_adapter()
state = EngineAdapterStateSync(adapter, schema="snapshot_state", cache_dir=root / "setup-cache")
try:
state.migrate(skip_backup=True)
model = SqlModel(
name="demo.retired",
dialect="duckdb",
query=exp.select(exp.Literal.number(1).as_("id")),
)
snapshot = Snapshot.from_node(model, nodes={model.fqn: model}, ttl="in 1 day")
snapshot.categorize_as(SnapshotChangeCategory.BREAKING)
state.push_snapshots([snapshot])
discovery = EngineAdapterStateSync(
adapter, schema="snapshot_state", cache_dir=root / "cold-cache"
)
calls = []
original = renderer.qualify
def tracked(*args, **kwargs):
calls.append("qualify")
return original(*args, **kwargs)
with patch.object(renderer, "qualify", tracked):
unexpired = discovery.get_expired_snapshots(
batch_range=ExpiredBatchRange.all_batch_range(),
current_ts=now_timestamp(),
)
before_expiry_calls = len(calls)
expired = discovery.get_expired_snapshots(
batch_range=ExpiredBatchRange.all_batch_range(),
current_ts=now_timestamp() + 2 * 24 * 60 * 60 * 1000,
)
result = {
"unexpired_batch_is_none": unexpired is None,
"qualify_calls_before_expiry": before_expiry_calls,
"expired_snapshot_count": len(expired.expired_snapshot_ids),
"qualify_calls_during_expired_discovery": len(calls) - before_expiry_calls,
}
print(json.dumps(result, indent=2))
assert result["unexpired_batch_is_none"]
assert result["qualify_calls_before_expiry"] == 0
assert result["expired_snapshot_count"] == 1
assert result["qualify_calls_during_expired_discovery"] == 0, (
"Expired-snapshot discovery qualified a stored model query"
)
finally:
state.close()Run it in an isolated environment:
uv run --isolated --no-project --python 3.14.7 \
--with 'sqlmesh==0.236.4' --with 'sqlglot==30.8.0' \
--with 'duckdb==1.4.2' python reproduce.pyThe script uses temporary DuckDB state and does not execute the model's SELECT query.
Actual result
After the state-migration output:
{
"unexpired_batch_is_none": true,
"qualify_calls_before_expiry": 0,
"expired_snapshot_count": 1,
"qualify_calls_during_expired_discovery": 1
}The final assertion fails. The wrapper records real calls and delegates to the original qualify(); it does not substitute an error or a slow optimizer. Separate setup and discovery cache directories make this a cold-load example.
Expected result
For an ordinary SQL model, identifying an expired, unreferenced snapshot and constructing its cleanup task should not require query qualification. The example should return the same expired snapshot with zero qualify() calls during discovery.
If some cleanup strategies need a rendered query, could optimization be deferred until such a strategy actually needs it? Any change must preserve TTL checks, environment references, shared physical versions, and cleanup semantics.
Relevant implementation
The path in v0.236.4 is:
janitor.delete_expired_snapshots()
-> EngineAdapterStateSync.get_expired_snapshots()
-> SnapshotState.get_expired_snapshots()
-> SnapshotState._get_snapshots()
-> SnapshotCache.get_or_load()
-> optimized-query cache preparation
-> query qualification
See snapshot discovery and snapshot-cache loading. The example stops at the discovery API used by janitor, before physical table drops or snapshot-state deletion.
Relationship to the known SQLGlot performance issue
SQLGlot #7821 describes exponential type resolution for certain UNNEST/CTE shapes, fixed by #7823. That demonstrates why query qualification can be costly for a historical snapshot. The bounded example here uses SELECT 1 to isolate cleanup's optimizer dependency; it does not reproduce a long-running query.
SQLMesh 0.236.4 requires sqlglot~=30.8.0. Making the known fix available in the supported dependency set would help with that particular slow path. Avoiding unnecessary query optimization during cleanup would also reduce dependence on historical queries remaining cheap under the currently installed optimizer.
Activity