Janitor cannot delete snapshots on DuckLake: DROP ... CASCADE is unsupported, and fails silently until a snapshot expires

[aydingeeringh]

Summary

sqlmesh janitor can never reclaim space on a DuckLake catalog. SnapshotEvaluator forces
cascade=True when deleting a snapshot's tables, and DuckLake does not implement cascade drops:

Not implemented Error: Cascade Drop not supported in DuckLake

The cause is a granularity mismatch: cascade support is declared per engine adapter, but on
DuckDB it varies per catalog. A plain DuckDB catalog supports DROP TABLE ... CASCADE; a
DuckLake catalog attached in the same connection does not.

This fails quietly. sqlmesh janitor returns Cleanup complete. having deleted nothing while
no snapshot has yet reached its TTL, so a DuckLake project looks healthy and then silently stops
reclaiming space once snapshots start expiring. Only --ignore-ttl surfaces it immediately.

Reproduction

import duckdb
con = duckdb.connect(); con.execute("INSTALL ducklake; LOAD ducklake")
con.execute("ATTACH 'ducklake:t.ducklake' AS lk (DATA_PATH 'storage/')")
con.execute("CREATE SCHEMA lk.phys; CREATE SCHEMA lk.virt")
con.execute("CREATE TABLE lk.phys.t (i INTEGER); INSERT INTO lk.phys.t VALUES (1)")
con.execute("CREATE VIEW lk.virt.v AS SELECT * FROM lk.phys.t")   # as the virtual layer builds

con.execute("DROP TABLE lk.phys.t CASCADE")   # FAIL — Cascade Drop not supported in DuckLake
con.execute("DROP TABLE lk.phys.t")           # OK   — the dependent view does NOT block it
con.execute("DROP SCHEMA lk.virt CASCADE")    # OK   — schema cascade is supported

In a real project, sqlmesh janitor --ignore-ttl gives:

Cleanup failed!
Error: Execution failed for node SnapshotId<"unlisted"."du"."prefix_map": 259425956>
  caused by: _duckdb.NotImplementedException:
  Not implemented Error: Cascade Drop not supported in DuckLake

Root cause

sqlmesh/core/snapshot/evaluator.py:1366 (0.236.1):

evaluation_strategy.delete(
    table_name,
    ...
    # we need to set cascade=true or we will get a 'cant drop because other objects depend on it'-style
    # error on engines that enforce referential integrity, such as Postgres
    cascade=True,
)

sqlmesh/core/engine_adapter/duckdb.py:39:

SUPPORTED_DROP_CASCADE_OBJECT_KINDS = ["SCHEMA", "TABLE", "VIEW"]

This looks like collateral from #4767 ("Janitor fails to delete object with dependencies in
Postgres"), which is a genuine Postgres problem — but DuckDB does not enforce referential
integrity, so TABLE/VIEW cascade was never needed on this engine, and declaring it breaks
DuckLake catalogs. The reproduction above shows the dependent view does not block a plain
DROP TABLE.

Suggested fix

Make the capability catalog-aware rather than adapter-wide. The adapter already has this exact
pattern at duckdb.py:176 in _create_table:

catalog_type_tuple = self.fetchone(
    exp.select("type").from_("duckdb_databases()")
    .where(exp.column("database_name").eq(catalog))
)
catalog_type = catalog_type_tuple[0] if catalog_type_tuple else None
if catalog_type == "ducklake":
    ...

So an _drop_object override on DuckDBEngineAdapter that drops cascade for TABLE/VIEW
when the target's catalog type is ducklake would fix it, keeping SCHEMA (which works, and
which environment cleanup relies on).

One caveat: the existing pattern resolves get_current_catalog(), which is not right for
_drop_object — it receives a fully-qualified name whose catalog may differ from the session's,
so the catalog should be resolved from the table name.

Workaround

Setting DuckDBEngineAdapter.SUPPORTED_DROP_CASCADE_OBJECT_KINDS = ["SCHEMA"] before building a
Context makes the built-in janitor work correctly. On a real project this dropped 78 physical
tables and reclaimed 3.64 GB, after which plan --skip-backfill reported no changes and all
blocking audits still passed. It is blunter than the fix above — it disables cascade for plain
DuckDB catalogs too — but harmless for the reason given.

Environment

• sqlmesh 0.233.0 and 0.236.1 (verified the declaration and cascade=True are unchanged in both)
• duckdb 1.5.5, ducklake extension d8a1881e (DuckLake format 1.0)
• macOS 15, Python 3.11 / 3.12

[kingjaiteh]

Reproduced on DuckDB 1.5.5 with your script: DROP TABLE lk.phys.t CASCADE and DROP VIEW ... CASCADE both raise, plain drops and DROP SCHEMA ... CASCADE work.

Are you planning a PR? If not, I'd like to take it. I have a branch that keeps the cascade list as is and instead has the DuckDB adapter check the target catalog's type in duckdb_databases() before a TABLE/VIEW drop, skipping CASCADE when it is ducklake. That leaves native catalogs (and anyone running with enable_view_dependencies) unchanged. I can also try the other way and just remove TABLE/VIEW from the DuckDB list if a maintainer prefers that.

[aydingeeringh]

Thanks for reproducing it — please take it, I have no PR in flight.

Your approach is the one I'd argue for. The distinction that matters here is adapter vs catalog: the same DuckDB adapter is correct to cascade against a native catalog and incorrect against an attached DuckLake one, so a per-drop check on the target catalog's type is the only thing that can be right in both cases. Dropping TABLE/VIEW from the DuckDB list wholesale would fix DuckLake but silently regress anyone running enable_view_dependencies, which is the case #4767 added it for.

One detail in case it's useful: the failure is invisible rather than loud. SnapshotEvaluator.delete() raises, the janitor catches it, and sqlmesh janitor reports "Cleanup complete." having dropped nothing — so the symptom people will actually hit is unbounded growth in sqlmesh__*, not an error. On this project that reached 15 models holding 2–6 materialised versions each, 3.56 GB of 6.46 GB, before anyone looked.

I'm running with SUPPORTED_DROP_CASCADE_OBJECT_KINDS = ["SCHEMA"] locally in the meantime and it's been fine, so if a maintainer does prefer the simpler list change, that path is at least exercised. Happy to test your branch against a real DuckLake catalog when it's up.

[kingjaiteh]

PR is up: #6034. It does the per-drop catalog type check as described, leaves SCHEMA cascade alone, and adds a test that attaches a real DuckLake catalog and drops a table with a dependent view through the adapter with cascade=True.

I'd take you up on the offer to run it against your project. My test catalog is tiny, so a janitor run on something with 15 models and a few GB of expired snapshots would be a much better check.

The PR description calls out the silent "Cleanup complete." path so a reviewer sees why this went unnoticed.