# Ducklake

> How do I use Ducklake to store large datasets in S3 and query them with SQL in Windmill?

This page is part of our section on [Persistent storage & databases](./index.mdx) which covers where to effectively store and manage the data manipulated by Windmill. Check that page for more options on data storage.

Ducklake allows you to store massive amounts of data in S3, but still query it efficiently in natural SQL language.

[Learn more about Ducklake](https://ducklake.select/)

## Getting started

Prerequisites:

- A workspace storage configured
- A Postgres or MySQL resource if you are not superuser. Superusers can use a [Custom instance database](/docs/core_concepts/custom_instance_database).

Go to `workspace settings` -> `Object storage (S3)` and configure a Ducklake :

![Ducklake settings](./ducklake_images/ducklake_settings.png 'Ducklake settings')

## Using Ducklake in scripts

Ducklakes are referenced by their name. 'main' is the special default ducklake name, which can be omitted when referencing it.

```ts

	let sql = wmill.ducklake();

	// This string interpolation syntax is safe
	// and is transformed into a parameterized query
	let friend = await sql`SELECT * FROM friend WHERE id = ${user_id}`.fetchOne();
	// let allFriends = await sql`INSERT INTO friend VALUES ('John', 21)`.fetch();

	return friend;
}
```

```python
import wmill

def main(user_id: str):
    # dl = wmill.ducklake('named_ducklake')
    dl = wmill.ducklake()

    # DuckDB scripts use named arguments
    friend = dl.query('SELECT * FROM friend WHERE id = $id', id=user_id).fetch_one()
    # all_friends = dl.query('SELECT * FROM friend').fetch()

    return friend
```

```sql
-- $user_id (bigint)

-- ATTACH 'ducklake://named_ducklake' AS dl;
ATTACH 'ducklake' AS dl;
USE dl;

SELECT * FROM friend WHERE id = $user_id;

-- Note: the original DuckDB syntax `ATTACH 'ducklake:postgres:connection_string'` does not benefit from Windmill's integration.
```

You can use the Ducklake button in the editor bar for convenience, which will insert the necessary statements for you.
![S3 content](./ducklake_images/ducklake_button.png 'S3 content')

## DuckDB example

DuckDB is the native query engine for Ducklake. Other integrations (TypeScript, Python...) run DuckDB scripts under the hood.
Note that these integrations do not start a new job when running the queries. The DuckDB script is run inline within the same worker.

In the example below, we pass a list of messages with positive, neutral or negative sentiment.
This list might come from a Python script which queries new reviews from the Google My Business API,
and sends them to an LLM to determine their sentiment.
The messages are then inserted into a Ducklake table, which effectively creates a new parquet file and stores metadata in the catalog.

```sql
-- $messages (json[])

ATTACH 'ducklake://main' AS dl;
USE dl;

CREATE TABLE IF NOT EXISTS messages (
  content STRING NOT NULL,
  author STRING NOT NULL,
  date STRING NOT NULL,
  sentiment STRING
);

CREATE TEMP TABLE new_messages AS
  SELECT
    value->>'content' AS content,
    value->>'author' AS author,
    value->>'date' AS date,
    value->>'sentiment' AS sentiment
  FROM json_each($messages);

INSERT INTO messages
  SELECT * FROM new_messages;
```

## Using the database manager

In your Ducklake settings, clicking the "Explore" button will open the database manager. You can perform all CRUD operations through the UI or with the SQL Repl.

![Explore ducklake](./ducklake_images/ducklake_db_manager.png 'Explore ducklake')

## Scheduled maintenance

Scheduled lake maintenance is an [Enterprise Edition](/pricing) feature.

A Ducklake grows unboundedly by default: every write advances a snapshot, and frequent incremental jobs (for example [scheduled](../1_scheduling/index.mdx) ingestions or [materializations](../63_pipelines/materialization.mdx)) accumulate many small parquet files. Windmill can run scheduled maintenance on each lake to keep it compact:

- Snapshot expiry: snapshots older than the retention window are expired, so the files they reference can be reclaimed.
- Compaction: adjacent small parquet files are merged into larger ones, which keeps queries fast.
- Orphaned file cleanup: files on storage that are no longer referenced by the catalog (for example left behind by expired snapshots or failed writes) are deleted.

### Configuration

Maintenance is configured per lake from the Maintenance column of the Ducklake settings, in `workspace settings` -> `Object storage (S3)`:

![Ducklake maintenance settings](./ducklake_images/ducklake_maintenance.png 'Ducklake maintenance settings')

- Scheduled maintenance: enables maintenance for this lake. Snapshot expiry always runs when maintenance is enabled.
- Snapshot retention (days): how long snapshots are kept before being expired. Defaults to 7 days.
- Cadence (cron, UTC): when maintenance runs. Defaults to daily at 03h UTC, with a deterministic per-lake minute offset so all lakes do not run at the same time.
- Compaction and Orphaned file cleanup: each operation can be toggled individually.

Lakes with scheduled maintenance must have names made of letters, digits, underscores and hyphens only (`[A-Za-z0-9_-]+`); this is validated when saving the settings.

### Maintenance jobs

Saving the settings creates a managed [schedule](../1_scheduling/index.mdx) at the reserved path `f/ducklake_maintenance/<lake>`. Maintenance runs as normal jobs on the `duckdb` [worker tag](../9_worker_groups/index.mdx): each run is visible on the [Runs](../5_monitor_past_and_future_runs/index.mdx) page, and the run history serves as the audit trail of what maintenance did and when. Each run returns a one-row summary as its result: `expired_snapshots`, `compacted_file_groups`, `cleaned_files`, `orphaned_files_deleted` and `remaining_snapshots`.

The schedule is fully managed by the Ducklake settings: it is hidden from the Schedules page, cannot be created, edited or deleted directly, and is removed when maintenance is disabled or the lake is deleted. It is also excluded from workspace exports and [workspace forks](../../advanced/20_workspace_forks/index.mdx).

### Retention and time-travel

Expired snapshots are gone for good: [time-travel](../63_pipelines/materialization.mdx#versioning-and-time-travel) reads (`AT (VERSION => n)`) and recorded snapshot references older than the retention window become non-queryable. Set the retention window at least as long as the history you need to query.

File deletion is conservative: files released by expired snapshots are physically deleted with an extra one-day margin, so a long-running query reading a just-expired snapshot is not interrupted, and orphaned file cleanup never deletes files younger than one day, so files being written by a concurrent job are safe. Storage space is therefore reclaimed about a day after snapshots expire.

## Ducklake in workspace forks

In a [workspace fork](../../advanced/20_workspace_forks/index.mdx), each Ducklake resolves by default to an isolated, fork-scoped data environment: writes land in a separate metadata schema and bucket prefix instead of the parent workspace's tables, and tables the fork has not written yet are read from the parent through read-only defer views. The fork's Ducklake settings show a banner and a per-lake isolated or shared-with-parent chip. See [Fork data environments for DuckLake](../../advanced/20_workspace_forks/index.mdx#fork-data-environments-for-ducklake).

{/* TODO: screenshot of workspace settings -> Ducklake in a fork, with the fork data environment banner and per-lake isolated / shared-with-parent chips. */}

## Performance: Ducklake vs Snowflake

Most workloads under 1 TB do not need a managed data warehouse. Ducklake on a single-node DuckDB engine runs analytical queries at a fraction of the cost of Snowflake while keeping your data in your own S3 bucket.

## What Ducklake does behind the scenes

If you explore your catalog database, you will see that Ducklake created some tables for you. These metadata tables store information about your data and where it is located in S3 :

![Catalog database](./ducklake_images/ducklake_catalog_db.png 'Catalog database')

If you explore your selected workspace storage you will see your tables and their contents as columnar, parquet files :

![S3 content](./ducklake_images/ducklake_s3_content.png 'S3 content')