Maintenance

Keep your data fast and your storage under control

Keep your data fast and your storage under control. Built-in maintenance — no separate tooling needed.

Mental Model

Data is immutable (for time travel and CDC)
Old data accumulates over time
Maintenance removes what’s no longer needed

What Maintenance Does

Command	What it does
`merge`	Combine small files → faster queries
`expire`	Remove old snapshot metadata
`cleanup`	Delete unused files → free storage
`orphaned`	Remove stray files from failed writes

Typical Workflow

ondatrasql merge
ondatrasql expire
ondatrasql cleanup
ondatrasql orphaned

Run weekly or as needed. Each step builds on the previous one.

Safe by Default

All commands support sandbox — preview before deleting anything:

ondatrasql merge sandbox
ondatrasql cleanup sandbox

When to Run What

Command	When
`merge`	Queries slowing down (many small files)
`expire`	Regularly (e.g. 30-day retention)
`cleanup`	After expire
`orphaned`	After failures or crashes

One-Command Option

DuckLake’s CHECKPOINT runs all steps at once:

CHECKPOINT;

Configure defaults:

CALL lake.set_option('expire_older_than', '30 days');
CALL lake.set_option('delete_older_than', '7 days');

Scheduling

Use cron or any workflow scheduler:

# Weekly maintenance (Sunday 3 AM)
0 3 * * 0 cd /path/to/project && ondatrasql merge && ondatrasql expire && ondatrasql cleanup && ondatrasql orphaned

Customizing

The SQL files in sql/ are fully editable:

ondatrasql edit merge
ondatrasql edit expire
ondatrasql edit cleanup
ondatrasql edit orphaned

Adjust retention periods, add table-specific operations, or switch to CHECKPOINT.

Why This Matters

Without maintenance, queries slow down and storage grows indefinitely. OndatraSQL keeps your data efficient with a few commands — no vacuum jobs, no external tools.