DuckLake

https://ducklake.select/docs/stable
Admin
DuckLake is a lakehouse format specification and a DuckDB extension. It defines a catalog database...
Tokens:71,760
Snippets:642
Trust Score:7.7
Update:5 days ago
Show doc for...
Tokens:
Raw
### Example Setup

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/sorted_tables.html

Initializes the DuckLake instance and creates the events table used in subsequent examples.

```sql
ATTACH 'ducklake:sorted.duckdb' AS my_ducklake (DATA_PATH 'data/');
USE my_ducklake;
CREATE TABLE events (event_time TIMESTAMP, event_type VARCHAR, value DOUBLE);
```

--------------------------------

### Install DuckLake extension

Source: https://ducklake.select/docs/stable/duckdb/introduction.html

Installs the DuckLake extension in DuckDB. Run this before using DuckLake features.

```sql
INSTALL ducklake;
```

--------------------------------

### Get DuckLake instance settings via convenience macro

Source: https://ducklake.select/docs/stable/duckdb/usage/configuration.html

Uses the convenience macro on an attached DuckLake to retrieve instance settings.

```sql
FROM my_ducklake.settings();

```

--------------------------------

### Initialize Rclone configuration

Source: https://ducklake.select/docs/stable/duckdb/guides/public_ducklake_on_object_storage.html

Run this command to start the interactive Rclone configuration process, where you can create a new remote named 'your_rclone_remote'.

```bash
rclone config

```

--------------------------------

### Example: QueryLog logging workflow

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/logging

Shows a complete workflow: attach a DuckLake catalog, create a table, enable QueryLog logging, run an INSERT, and view all queries (including internal DuckLake metadata queries) via duckdb_logs.

```sql
ATTACH 'ducklake:catalog.db' AS my_ducklake (DATA_PATH 'data/');
USE my_ducklake;
CREATE TABLE t1 (a INTEGER, b VARCHAR);

-- Enable query logging
CALL enable_logging('QueryLog');

-- Run an operation
INSERT INTO t1 VALUES (1, 'hello');

-- View all queries including internal DuckLake metadata queries
SELECT type, message
FROM duckdb_logs
WHERE type = 'QueryLog';
```

--------------------------------

### Tiered Compaction Strategy

Source: https://ducklake.select/docs/stable/duckdb/maintenance/merge_adjacent_files.html

Example workflow for a tiered compaction strategy using ducklake_merge_adjacent_files with size filtering and target_file_size settings.

```APIDOC
## Tiered Compaction Strategy

### Description
Demonstrates a tiered compaction strategy for streaming workloads, merging files in stages based on size thresholds.

### Method
CALL (DuckDB procedures)

### Endpoint
`ducklake_set_option` and `ducklake_merge_adjacent_files`

### Parameters
#### ducklake_set_option
- **ducklake_name** (string) - Required - The name of the DuckLake instance.
- **option** (string) - Required - The option to set, e.g., `target_file_size`.
- **value** (string) - Required - The value to set, e.g., `'5MB'`.

#### ducklake_merge_adjacent_files
- **ducklake_name** (string) - Required - The name of the DuckLake instance.
- **min_file_size** (integer, bytes) - Optional - Minimum file size to consider for compaction.
- **max_file_size** (integer, bytes) - Optional - Maximum file size to consider for compaction.

### Request Example
```sql
-- Tier 0 → Tier 1: merge small files
CALL ducklake_set_option('my_ducklake', 'target_file_size', '5MB');
CALL ducklake_merge_adjacent_files('my_ducklake', max_file_size => 1048576);

-- Tier 1 → Tier 2: merge medium files
CALL ducklake_set_option('my_ducklake', 'target_file_size', '32MB');
CALL ducklake_merge_adjacent_files('my_ducklake', min_file_size => 1048576, max_file_size => 10485760);

-- Tier 2 → Tier 3: merge large files
CALL ducklake_set_option('my_ducklake', 'target_file_size', '128MB');
CALL ducklake_merge_adjacent_files('my_ducklake', min_file_size => 10485760, max_file_size => 67108864);
```

### Response
No explicit response is documented. The functions perform the operations.
```

--------------------------------

### Get DuckLake instance settings

Source: https://ducklake.select/docs/stable/duckdb/usage/configuration.html

Returns metadata about a DuckLake instance, including catalog type, extension version, and data path.

```sql
FROM ducklake_settings('my_ducklake');

```

--------------------------------

### Example: DuckLakeMetadata logging workflow

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/logging

Shows a complete workflow: attach a DuckLake catalog, create a table, enable DuckLakeMetadata logging, run an INSERT that triggers metadata queries, and view the structured log fields (catalog, elapsed_ms, query) via duckdb_logs_parsed.

```sql
ATTACH 'ducklake:catalog.db' AS my_ducklake (DATA_PATH 'data/');
USE my_ducklake;
CREATE TABLE t1 (a INTEGER, b VARCHAR);

-- Enable DuckLake metadata logging
CALL enable_logging('DuckLakeMetadata');

-- Run an operation that triggers metadata queries
INSERT INTO t1 VALUES (1, 'hello'), (2, 'world');

-- View the metadata queries with structured fields
SELECT catalog, elapsed_ms, query
FROM duckdb_logs_parsed('DuckLakeMetadata');
```

--------------------------------

### Attach MySQL as catalog database (not recommended)

Source: https://ducklake.select/docs/stable/duckdb/usage/choosing_a_catalog_database.html

Install the ducklake and mysql extensions, then attach to a MySQL-backed DuckLake catalog. Requires MySQL 8 or newer. There are known issues with MySQL as a catalog for DuckLake, so it is not recommended.

```sql
INSTALL ducklake;
INSTALL mysql;

-- Make sure that the database `ducklake_catalog` exists in MySQL
ATTACH 'ducklake:mysql:db=ducklake_catalog host=localhost' AS my_ducklake
    (DATA_PATH 'data_files/');
USE my_ducklake;

```

--------------------------------

### Attach PostgreSQL as catalog database

Source: https://ducklake.select/docs/stable/duckdb/usage/choosing_a_catalog_database.html

Install the ducklake and postgres extensions, then attach to a PostgreSQL-backed DuckLake catalog. Requires PostgreSQL 12 or newer and that the database ducklake_catalog exists.

```sql
INSTALL ducklake;
INSTALL postgres;

-- Make sure that the database `ducklake_catalog` exists in PostgreSQL
ATTACH 'ducklake:postgres:dbname=ducklake_catalog host=localhost' AS my_ducklake
    (DATA_PATH 'data_files/');
USE my_ducklake;

```

--------------------------------

### Initialize DuckLake with remote data path

Source: https://ducklake.select/docs/stable/duckdb/guides/using_a_remote_data_path.html

Attaches to a DuckLake using a remote URL as the data path and immediately detaches. This is the initial setup step for a read-only DuckLake.

```sql
ATTACH 'tpch-sf1.ducklake' AS tpch_sf1_ducklake (
    TYPE ducklake,
    DATA_PATH 'https://blobs.duckdb.org/datalake/tpch-sf1'
);
DETACH tpch_sf1_ducklake;

```

--------------------------------

### Enable both DuckLakeMetadata and QueryLog

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/logging

Enables both log types simultaneously to get DuckLake-specific timing information alongside the full query trace.

```sql
CALL enable_logging(['DuckLakeMetadata', 'QueryLog']);
```

--------------------------------

### Example: small delete with deletion inlining

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/data_inlining.html

Demonstrates enabling deletion inlining with DATA_INLINING_ROW_LIMIT 10, inserting 100 rows to exceed the threshold, then deleting 2 rows. The final query confirms no new Parquet delete file is created.

```sql
ATTACH 'ducklake:inlining.duckdb' AS my_ducklake (DATA_INLINING_ROW_LIMIT 10);
USE my_ducklake;

CREATE TABLE tbl (col INTEGER);
-- Insert enough rows to exceed the threshold, so they go to Parquet
INSERT INTO tbl FROM range(100);

-- Delete 2 rows – below the threshold, stored inline
DELETE FROM tbl WHERE col < 2;
-- No new delete file is created
SELECT count(*) FROM glob('inlining.duckdb.files/**/*.parquet') WHERE file LIKE '%-delete.parquet';

```

--------------------------------

### Attach DuckDB as catalog database

Source: https://ducklake.select/docs/stable/duckdb/usage/choosing_a_catalog_database.html

Install the ducklake extension and attach to a DuckDB-based DuckLake catalog. Note that using DuckDB as the catalog limits you to a single client.

```sql
INSTALL ducklake;

ATTACH 'ducklake:metadata.ducklake' AS my_ducklake;
USE my_ducklake;

```

--------------------------------

### Attach SQLite as catalog database

Source: https://ducklake.select/docs/stable/duckdb/usage/choosing_a_catalog_database.html

Install the ducklake and sqlite extensions, then attach to a SQLite-backed DuckLake catalog. SQLite's default mode attaches and detaches for every query and provides a retry time-out for write-lock conflicts, allowing reasonable multi-processing support.

```sql
INSTALL ducklake;
INSTALL sqlite;

ATTACH 'ducklake:sqlite:metadata.sqlite' AS my_ducklake
    (DATA_PATH 'data_files/');
USE my_ducklake;

```

--------------------------------

### Get Current Snapshot ID

Source: https://ducklake.select/docs/stable/specification/queries.html

Retrieves the most recent snapshot ID from the ducklake_snapshot table.

```sql
SELECT max(snapshot_id)
FROM ducklake_snapshot;
```

--------------------------------

### Set up DuckLake instance with snapshots

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/data_change_feed.html

Creates a DuckLake instance and performs operations to create snapshots 1 through 4: create table, insert rows, delete a row, and update a row.

```sql
ATTACH 'ducklake:changes.duckdb' AS db (DATA_PATH 'change_files/');
-- Snapshot 1
CREATE TABLE db.tbl (id INTEGER, val VARCHAR);
-- Snapshot 2
INSERT INTO db.tbl VALUES (1, 'Hello'), (2, 'DuckLake');
-- Snapshot 3
DELETE FROM db.tbl WHERE id = 1;
-- Snapshot 4
UPDATE db.tbl SET val = concat(val, val, val);

```

--------------------------------

### Secrets

Source: https://ducklake.select/docs/stable/duckdb/usage/connecting.html

Describes how to create secrets for DuckLake connections, including the additional parameters supported and example usage.

```APIDOC
## Secrets

### Description
Secrets can be created to contain all required information for setting up a DuckLake connection. They support the same parameters as `ATTACH`, plus `METADATA_PATH` and `METADATA_PARAMETERS`.

### Parameters

| Name | Description | Default |
|------|-------------|---------|
| `METADATA_PATH` | The connection string for connecting to the metadata | |
| `METADATA_PARAMETERS` | Map of parameters to pass to the catalog server | `{}` |

### Usage Examples

```sql
-- Default (unnamed) secret
CREATE SECRET (
    TYPE ducklake,
    METADATA_PATH 'metadata.duckdb',
    DATA_PATH 'metadata_files/'
);

ATTACH 'ducklake:' AS my_ducklake;

-- Named secrets
CREATE SECRET my_secret (
    TYPE ducklake,
    METADATA_PATH '',
    DATA_PATH 's3://my-s3-bucket/',
    METADATA_PARAMETERS MAP {'TYPE': 'postgres', 'SECRET': 'postgres_secret'}
);
ATTACH 'ducklake:my_secret' AS my_ducklake;
```

### Persistence
To persist secrets, use the `CREATE PERSISTENT SECRET` statement.
```

--------------------------------

### Retrieving Last Committed Snapshot

Source: https://ducklake.select/docs/stable/duckdb/usage/snapshots.html

Use the last_committed_snapshot function to get the latest committed snapshot for an existing open connection.

```sql
FROM my_ducklake.last_committed_snapshot();
```

--------------------------------

### Query changes made in the last week

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/data_change_feed.html

Uses timestamps as bounds to get changes from the last week. The bounds are inclusive.

```sql
FROM changes.table_changes('tbl', now() - INTERVAL '1 week', now());

```

--------------------------------

### Migration Script: Main Entry Point

Source: https://ducklake.select/docs/stable/duckdb/migrations/duckdb_to_ducklake

This is the main script that parses command-line arguments, connects to DuckDB, creates a DuckLake secret, attaches both catalogs, and invokes the migration functions.

```python
if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Migrate DuckDB catalog to DuckLake.")
    parser.add_argument("--duckdb-catalog", required=True, help="DuckDB catalog name")
    parser.add_argument("--duckdb-file", required=True, help="Path to DuckDB file")
    parser.add_argument(
        "--ducklake-catalog", required=True, help="DuckLake catalog name"
    )
    parser.add_argument(
        "--catalog-type",
        choices=["duckdb", "postgresql", "sqlite"],
        required=True,
        help="Choose one of: duckdb, postgresql, sqlite",
    )
    parser.add_argument("--ducklake-file", required=False, help="Path to DuckLake file")
    parser.add_argument(
        "--ducklake-data-path", required=True, help="Data path for DuckLake"
    )

    args = parser.parse_args()

    con = duckdb.connect(database=args.duckdb_file)

    if args.catalog_type == "postgresql":
        con.execute(get_postgres_secret())

    secret = (
        "CREATE SECRET ducklake_secret (TYPE ducklake"
        + (
            f"\n,METADATA_PATH '{args.ducklake_file if args.catalog_type == 'duckdb' else f'sqlite:{args.ducklake_file}'}'"
            if args.catalog_type in ("duckdb", "sqlite")
            else "\n,METADATA_PATH ''"
        )
        + f"\n,DATA_PATH '{args.ducklake_data_path}'"
        + (
            "\n,METADATA_PARAMETERS MAP {'TYPE': 'postgres', 'SECRET': 'postgres_secret'});"
            if args.catalog_type == "postgresql"
            else ");"
        )
    )
    con.execute(secret)

    con.execute(
        f"ATTACH '{args.duckdb_file}' AS {args.duckdb_catalog};"
        f"ATTACH 'ducklake:ducklake_secret' AS {args.ducklake_catalog}; USE {args.ducklake_catalog};"
    )

    migrate_tables_and_views(
        duckdb_catalog=args.duckdb_catalog,
        con=con,
    )
```

--------------------------------

### Create people table and insert initial data

Source: https://ducklake.select/docs/stable/duckdb/usage/upserting.html

Creates a simple table named people with columns id, name, and salary, and inserts two initial rows.

```sql
CREATE TABLE people (id INTEGER, name VARCHAR, salary FLOAT);
INSERT INTO people VALUES (1, 'John', 92_000.0), (2, 'Anna', 100_000.0);
```

--------------------------------

### Define macro with default parameters

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/macros.html

Demonstrates how to assign a default value to a macro parameter.

```sql
CREATE MACRO add_with_default(a, b := 10) AS a + b;
SELECT add_with_default(5);
```

--------------------------------

### Create a view with CREATE VIEW

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/views

Use standard CREATE VIEW syntax to create a view. The view is stored in the metadata table ducklake_view.

```sql
CREATE VIEW v1 AS SELECT * FROM tbl;
```

--------------------------------

### Create a local DuckLake database

Source: https://ducklake.select/docs/stable/duckdb/introduction.html

Creates a new DuckLake database using a local DuckDB file and switches to it with USE. This creates a file 'my_ducklake.ducklake' and a folder 'my_ducklake.ducklake.files' for Parquet files.

```sql
ATTACH 'ducklake:my_ducklake.ducklake' AS my_ducklake;
USE my_ducklake;
```

--------------------------------

### Alternative Cloudflare CORS network error

Source: https://ducklake.select/docs/stable/duckdb/guides/public_ducklake_on_object_storage.html

Another example error message when the browser fails to load the DuckLake metadata due to CORS restrictions.

```text
Invalid Error: Failed to attach DuckLake MetaData "__ducklake_metadata..." at path + "..."
Opening file '...' failed with error:
NetworkError: Failed to execute 'send' on 'XMLHttpRequest': Failed to load '...'.

```

--------------------------------

### Cloudflare CORS error when attaching DuckLake

Source: https://ducklake.select/docs/stable/duckdb/guides/public_ducklake_on_object_storage.html

Example error message when querying a Cloudflare-hosted DuckLake from another website due to missing CORS policy.

```text
IO Error: Failed to attach DuckLake MetaData "__ducklake_metadata_..." at path + "..."
Cannot open database "..." in read-only mode: database does not exist

```

--------------------------------

### List files of a table in a specific schema

Source: https://ducklake.select/docs/stable/duckdb/metadata/list_files

Uses the schema parameter to specify the schema for the table, defaulting to 'main'.

```SQL
FROM ducklake_list_files('catalog', 'table_name', schema => 'main');
```

--------------------------------

### Create and use a table macro

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/macros.html

Defines a table macro that returns a table based on a threshold and demonstrates its usage.

```sql
CREATE MACRO filtered_table(threshold) AS TABLE
    SELECT *
    FROM my_table
    WHERE value > threshold;
SELECT * FROM filtered_table(100);
```

--------------------------------

### Variant extra_stats JSON example

Source: https://ducklake.select/docs/stable/specification/data_types.html

Shows the JSON array for variant statistics, with one entry per shredded sub-field. If the variant cannot be shredded, extra_stats is NULL.

```json
[
    {
        "field_name": "root",
        "shredded_type": "int32",
        "null_count": 0,
        "min": "42",
        "max": "99",
        "num_values": 3,
        "column_size_bytes": 37,
        "any_valid": true
    }
]
```

--------------------------------

### Geometry extra_stats JSON example

Source: https://ducklake.select/docs/stable/specification/data_types.html

Shows the JSON structure for geometry statistics, containing a bounding box and a list of primitive types. Stored in the extra_stats column.

```json
{
    "bbox": {
        "xmin": 0.000000,
        "xmax": 5.000000,
        "ymin": 0.000000,
        "ymax": 5.000000,
        "zmin": null,
        "zmax": null,
        "mmin": null,
        "mmax": null
    },
    "types": ["point"]
}
```

--------------------------------

### Cloudflare bucket CORS policy JSON

Source: https://ducklake.select/docs/stable/duckdb/guides/public_ducklake_on_object_storage.html

CORS policy to add to the bucket settings when serving directly from the bucket, allowing GET requests from duckdb.org and shell.duckdb.org.

```json
[
  {
    "AllowedOrigins": [
      "https://duckdb.org",
      "https://shell.duckdb.org"
    ],
    "AllowedMethods": [
      "GET"
    ]
  }
]

```

--------------------------------

### Setting Multiple Sort Keys

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/sorted_tables.html

Demonstrates defining multiple columns for the sort order.

```sql
ALTER TABLE events SET SORTED BY (event_time ASC, event_type DESC);
```

--------------------------------

### Launch DuckDB CLI with a DuckLake URL

Source: https://ducklake.select/docs/stable/duckdb/introduction.html

Pass the DuckLake filename or URL as an argument to the DuckDB command line client to open it directly.

```bash
duckdb ducklake:my_ducklake.ducklake

```

--------------------------------

### List files after update

Source: https://ducklake.select/docs/stable/duckdb/introduction.html

Shows the files in the DuckLake after the update, revealing the original data, deleted rows, and inserted rows.

```sql
FROM glob('my_ducklake.ducklake.files/**/*');

```

--------------------------------

### Basic upsert with MERGE INTO

Source: https://ducklake.select/docs/stable/duckdb/usage/upserting.html

Performs a simple upsert that updates or inserts whole rows based on matching id. The example uses a subquery with unnest to provide new data, then queries the table to show the result.

```sql
MERGE INTO people
    USING (
        SELECT
            unnest([3, 1]) AS id,
            unnest(['Sarah', 'John']) AS name,
            unnest([95_000.0, 105_000.0]) AS salary
    ) AS upserts
    ON (upserts.id = people.id)
    WHEN MATCHED THEN UPDATE
    WHEN NOT MATCHED THEN INSERT;

FROM people;
```

--------------------------------

### List files at a specific point in time

Source: https://ducklake.select/docs/stable/duckdb/metadata/list_files

Uses the snapshot_time parameter to fetch files for a given timestamp.

```SQL
FROM ducklake_list_files('catalog', 'table_name', snapshot_time => '2025-06-16 15:24:30');
```

--------------------------------

### Implement tiered compaction strategy

Source: https://ducklake.select/docs/stable/duckdb/maintenance/merge_adjacent_files.html

A multi-stage workflow using ducklake_set_option and merge_adjacent_files to perform tiered compaction for streaming workloads.

```sql
-- Tier 0 → Tier 1: merge small files
CALL ducklake_set_option('my_ducklake', 'target_file_size', '5MB');
CALL ducklake_merge_adjacent_files('my_ducklake', max_file_size => 1048576);

-- Tier 1 → Tier 2: merge medium files
CALL ducklake_set_option('my_ducklake', 'target_file_size', '32MB');
CALL ducklake_merge_adjacent_files('my_ducklake', min_file_size => 1048576, max_file_size => 10485760);

-- Tier 2 → Tier 3: merge large files
CALL ducklake_set_option('my_ducklake', 'target_file_size', '128MB');
CALL ducklake_merge_adjacent_files('my_ducklake', min_file_size => 10485760, max_file_size => 67108864);
```

--------------------------------

### Count output files per table with GROUP BY

Source: https://ducklake.select/docs/stable/duckdb/maintenance/merge_adjacent_files.html

Query to get the total number of output files produced per table by summing files_created. Each row from ducklake_merge_adjacent_files represents one output file, so files_created is always 1.

```sql
SELECT schema_name, table_name, sum(files_created) AS total_output_files
FROM ducklake_merge_adjacent_files('my_ducklake')
GROUP BY schema_name, table_name;
```

--------------------------------

### ducklake_merge_adjacent_files

Source: https://ducklake.select/docs/stable/duckdb/maintenance/merge_adjacent_files.html

Merges adjacent files in a DuckLake table. Returns one row per output file created, with schema name, table name, and file counts. Supports grouping to get total output files per table.

```APIDOC
## ducklake_merge_adjacent_files

### Description
Merges adjacent files in a DuckLake table to reduce file count and improve query performance. Returns one row per output file created.

### Function Signature
`ducklake_merge_adjacent_files(database_name)`

### Parameters
- **database_name** (VARCHAR) - Required - The name of the DuckLake database containing the table to compact.

### Return Values
Returns one row per output file created, with the following columns:

| Column | Type | Description |
|--------|------|-------------|
| `schema_name` | `VARCHAR` | Name of the schema containing the table |
| `table_name` | `VARCHAR` | Name of the table |
| `files_processed` | `BIGINT` | Number of input files merged into this output file |
| `files_created` | `BIGINT` | Always `1` — each row represents one output file created |

### Example
```sql
SELECT schema_name, table_name, sum(files_created) AS total_output_files
FROM ducklake_merge_adjacent_files('my_ducklake')
GROUP BY schema_name, table_name;
```

### Notes
- If a table has a sort order defined, the merged output is sorted by those keys before writing the resulting Parquet file. The sort order applied is the one currently active on the table at the time compaction runs.
- Only files that share the same schema version are merged. Schema-altering DDL statements (ADD COLUMN, DROP COLUMN, RENAME COLUMN, ALTER COLUMN) create a new schema version, so files written before and after the change are never merged together. Cosmetic alterations (COMMENT ON, sort key changes) do not bump the schema version.
- Calling this function does not immediately delete the old files. See the cleanup old files section on how to trigger a cleanup of these files.
```

--------------------------------

### Create DuckLake metadata tables

Source: https://ducklake.select/docs/stable/specification/tables/overview.html

Run this SQL script to create all DuckLake metadata tables. It defines tables for columns, data files, delete files, snapshots, schemas, and other metadata. Each table includes columns for snapshot tracking and metadata fields.

```sql
CREATE TABLE ducklake_column (column_id BIGINT, begin_snapshot BIGINT, end_snapshot BIGINT, table_id BIGINT, column_order BIGINT, column_name VARCHAR, column_type VARCHAR, initial_default VARCHAR, default_value VARCHAR, nulls_allowed BOOLEAN, parent_column BIGINT, default_value_type VARCHAR, default_value_dialect VARCHAR);
CREATE TABLE ducklake_column_mapping (mapping_id BIGINT, table_id BIGINT, "type" VARCHAR);
CREATE TABLE ducklake_column_tag (table_id BIGINT, column_id BIGINT, begin_snapshot BIGINT, end_snapshot BIGINT, "key" VARCHAR, "value" VARCHAR);
CREATE TABLE ducklake_data_file (data_file_id BIGINT PRIMARY KEY, table_id BIGINT, begin_snapshot BIGINT, end_snapshot BIGINT, file_order BIGINT, path VARCHAR, path_is_relative BOOLEAN, file_format VARCHAR, record_count BIGINT, file_size_bytes BIGINT, footer_size BIGINT, row_id_start BIGINT, partition_id BIGINT, encryption_key VARCHAR, mapping_id BIGINT, partial_max BIGINT);
CREATE TABLE ducklake_delete_file (delete_file_id BIGINT PRIMARY KEY, table_id BIGINT, begin_snapshot BIGINT, end_snapshot BIGINT, data_file_id BIGINT, path VARCHAR, path_is_relative BOOLEAN, format VARCHAR, delete_count BIGINT, file_size_bytes BIGINT, footer_size BIGINT, encryption_key VARCHAR, partial_max BIGINT);
CREATE TABLE ducklake_file_column_stats (data_file_id BIGINT, table_id BIGINT, column_id BIGINT, column_size_bytes BIGINT, value_count BIGINT, null_count BIGINT, min_value VARCHAR, max_value VARCHAR, contains_nan BOOLEAN, extra_stats VARCHAR);
CREATE TABLE ducklake_file_partition_value (data_file_id BIGINT, table_id BIGINT, partition_key_index BIGINT, partition_value VARCHAR);
CREATE TABLE ducklake_file_variant_stats (data_file_id BIGINT, table_id BIGINT, column_id BIGINT, variant_path VARCHAR, shredded_type VARCHAR, column_size_bytes BIGINT, value_count BIGINT, null_count BIGINT, min_value VARCHAR, max_value VARCHAR, contains_nan BOOLEAN, extra_stats VARCHAR);
CREATE TABLE ducklake_files_scheduled_for_deletion (data_file_id BIGINT, path VARCHAR, path_is_relative BOOLEAN, schedule_start TIMESTAMP WITH TIME ZONE);
CREATE TABLE ducklake_inlined_data_tables (table_id BIGINT, table_name VARCHAR, schema_version BIGINT);
CREATE TABLE ducklake_macro (schema_id BIGINT, macro_id BIGINT, macro_name VARCHAR, begin_snapshot BIGINT, end_snapshot BIGINT);
CREATE TABLE ducklake_macro_impl (macro_id BIGINT, impl_id BIGINT, dialect VARCHAR, "sql" VARCHAR, "type" VARCHAR);
CREATE TABLE ducklake_macro_parameters (macro_id BIGINT, impl_id BIGINT, column_id BIGINT, parameter_name VARCHAR, parameter_type VARCHAR, default_value VARCHAR, default_value_type VARCHAR);
CREATE TABLE ducklake_metadata ("key" VARCHAR NOT NULL, "value" VARCHAR NOT NULL, "scope" VARCHAR, scope_id BIGINT);
CREATE TABLE ducklake_name_mapping (mapping_id BIGINT, column_id BIGINT, source_name VARCHAR, target_field_id BIGINT, parent_column BIGINT, is_partition BOOLEAN);
CREATE TABLE ducklake_partition_column (partition_id BIGINT, table_id BIGINT, partition_key_index BIGINT, column_id BIGINT, "transform" VARCHAR);
CREATE TABLE ducklake_partition_info (partition_id BIGINT, table_id BIGINT, begin_snapshot BIGINT, end_snapshot BIGINT);
CREATE TABLE ducklake_schema (schema_id BIGINT PRIMARY KEY, schema_uuid UUID, begin_snapshot BIGINT, end_snapshot BIGINT, schema_name VARCHAR, path VARCHAR, path_is_relative BOOLEAN);
CREATE TABLE ducklake_schema_versions (begin_snapshot BIGINT, schema_version BIGINT, table_id BIGINT);
CREATE TABLE ducklake_snapshot (snapshot_id BIGINT PRIMARY KEY, snapshot_time TIMESTAMP WITH TIME ZONE, schema_version BIGINT, next_catalog_id BIGINT, next_file_id BIGINT);
CREATE TABLE ducklake_snapshot_changes (snapshot_id BIGINT PRIMARY KEY, changes_made VARCHAR, author VARCHAR, commit_message VARCHAR, commit_extra_info VARCHAR);
CREATE TABLE ducklake_sort_expression (sort_id BIGINT, table_id BIGINT, sort_key_index BIGINT, expression VARCHAR, dialect VARCHAR, sort_direction VARCHAR, null_order VARCHAR);
CREATE TABLE ducklake_sort_info (sort_id BIGINT, table_id BIGINT, begin_snapshot BIGINT, end_snapshot BIGINT);
CREATE TABLE ducklake_table (table_id BIGINT, table_uuid UUID, begin_snapshot BIGINT, end_snapshot BIGINT, schema_id BIGINT, table_name VARCHAR, path VARCHAR, path_is_relative BOOLEAN);
CREATE TABLE ducklake_table_column_stats (table_id BIGINT, column_id BIGINT, contains_null BOOLEAN, contains_nan BOOLEAN, min_value VARCHAR, max_value VARCHAR, extra_stats VARCHAR);
CREATE TABLE ducklake_table_stats (table_id BIGINT, record_count BIGINT, next_row_id BIGINT, file_size_bytes BIGINT);
CREATE TABLE ducklake_tag (object_id BIGINT, begin_snapshot BIGINT, end_snapshot BIGINT, "key" VARCHAR, "value" VARCHAR);
CREATE TABLE ducklake_view (view_id BIGINT, view_uuid UUID, begin_snapshot BIGINT, end_snapshot BIGINT, schema_id BIGINT, view_name VARCHAR, dialect VARCHAR, "sql" VARCHAR, column_aliases VARCHAR);
```

--------------------------------

### Define macro with typed parameters

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/macros.html

Shows how to specify data types for macro parameters.

```sql
CREATE MACRO typed_add(a INTEGER, b INTEGER) AS a + b;
```

--------------------------------

### ATTACH with DuckDB catalog and default data path

Source: https://ducklake.select/docs/stable/duckdb/usage/connecting.html

Uses a DuckDB database `duckdb_database.ducklake` as the catalog database with the data path defaulting to `duckdb_database.ducklake.files`.

```sql
ATTACH 'ducklake:duckdb_database.ducklake';
```

--------------------------------

### List files at a specific snapshot version

Source: https://ducklake.select/docs/stable/duckdb/metadata/list_files

Uses the snapshot_version parameter to fetch files for a given snapshot id.

```SQL
FROM ducklake_list_files('catalog', 'table_name', snapshot_version => 2);
```

--------------------------------

### Flush data with deletions and time travel

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/data_inlining.html

Demonstrates creating a table, performing deletions, and flushing the data to Parquet while preserving snapshot information.

```sql
ATTACH 'ducklake:inlining.duckdb' AS my_ducklake (DATA_PATH 'data/', DATA_INLINING_ROW_LIMIT 10);
USE my_ducklake;

CREATE TABLE t1 (a INTEGER);
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8);

DELETE FROM t1 WHERE a = 2;
DELETE FROM t1 WHERE a = 5;

-- Flush materializes data to Parquet and creates a deletion file with snapshot information
CALL ducklake_flush_inlined_data('ducklake');
```

--------------------------------

### Log to stdout or file

Source: https://ducklake.select/docs/stable/duckdb/advanced_features/logging

Shows how to configure log storage: use storage='stdout' to log to stdout, or storage_path to log to a file. By default, logs are stored in an in-memory buffer and queried via the duckdb_logs view.

```sql
-- Log to stdout
CALL enable_logging('DuckLakeMetadata', storage = 'stdout');

-- Log to a file
CALL enable_logging('DuckLakeMetadata', storage_path = '/tmp/ducklake_logs');
```

--------------------------------

### ATTACH with PostgreSQL catalog and S3 data path

Source: https://ducklake.select/docs/stable/duckdb/usage/connecting.html

Uses a PostgreSQL database as the catalog database and an S3 path as the data path.

```sql
ATTACH 'ducklake:postgres:dbname=postgres' (DATA_PATH 's3://my-bucket/my-data/');
```

--------------------------------

### Migration Script CLI Usage

Source: https://ducklake.select/docs/stable/duckdb/migrations/duckdb_to_ducklake

The command-line interface usage for the migration.py script, detailing required arguments for catalog and file paths.

```text
usage: migration.py [-h]
    --duckdb-catalog DUCKDB_CATALOG
    --duckdb-file DUCKDB_FILE
    --ducklake-catalog DUCKLAKE_CATALOG
    --catalog-type {duckdb,postgresql,sqlite}
    [--ducklake-file DUCKLAKE_FILE]
    --ducklake-data-path DUCKLAKE_DATA_PATH
```

--------------------------------

### Time travel to snapshot version 1

Source: https://ducklake.select/docs/stable/duckdb/introduction.html

Queries the table as of snapshot version 1 (table creation) using the AT (VERSION => 1) syntax.

```sql
SELECT name_long
FROM nl_train_stations AT (VERSION => 1)
WHERE code = 'ASB';

```

--------------------------------

### Listing Snapshots with snapshots()

Source: https://ducklake.select/docs/stable/duckdb/usage/snapshots.html

Query the snapshots function to retrieve a list of all snapshots and their associated changesets.

```sql
ATTACH 'ducklake:my_ducklake.duckdb' AS my_ducklake;
CREATE TABLE my_ducklake.people (a INTEGER, b VARCHAR);
INSERT INTO my_ducklake.people VALUES (1, 'pedro');
SELECT * FROM my_ducklake.snapshots();
```

--------------------------------

### Set up DuckLake Reader secrets and perform read operations

Source: https://ducklake.select/docs/stable/duckdb/guides/access_control

Creates secrets for S3 and Postgres with DuckLake reader credentials, attaches to DuckLake, and attempts to read from tables. Reading from the authorized table works, but reading from an unauthorized table fails.

```sql
DROP SECRET s3_ducklake_schema_reader_writer;
CREATE OR REPLACE SECRET s3_ducklake_table_reader (
  TYPE s3,
  PROVIDER config,
  KEY_ID 'key_id',
  SECRET 'secret_key',
  REGION 'eu-north-1'
);
CREATE OR REPLACE SECRET postgres_secret_reader (
  TYPE postgres,
  HOST 'localhost',
  DATABASE 'access_control',
  USER 'ducklake_reader',
  PASSWORD 'simple'
);
CREATE OR REPLACE SECRET ducklake_reader_secret (
  TYPE ducklake,
  METADATA_PATH '',
  DATA_PATH 's3://ducklake-access-control/',
  METADATA_PARAMETERS MAP {'TYPE': 'postgres','SECRET': 'postgres_secret_reader'}
);
ATTACH 'ducklake:ducklake_reader_secret' AS ducklake_reader;
USE ducklake_reader;

SELECT * FROM some_schema.some_table; -- Works
SELECT * FROM some_schema.another_table; -- Fails
```

--------------------------------

### Add a file with allow_missing

Source: https://ducklake.select/docs/stable/duckdb/metadata/adding_files

Registers people.parquet while allowing columns present in the table but missing in the file. Missing columns will use their default values when reading.

```sql
CALL ducklake_add_data_files('my_ducklake', 'people', 'people.parquet', allow_missing => true);
```
Tokens:
Raw