DuckLake

https://ducklake.select/docs
Admin
DuckLake is an open-source lakehouse format for DuckDB that combines data lake and warehouse...
Tokens:34,678
Snippets:454
Trust Score:7.7
Update:2 months ago
Show doc for...
Tokens:
Raw
### Create and Populate Table

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

Initial setup for the example table used in subsequent operations.

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

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

### Initialize DuckLake Table

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

Setup a DuckLake instance and create a sample table for sorting 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);
```

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

### Initialize Rclone Configuration

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

Start the interactive Rclone configuration process.

```bash
rclone config
```

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

### Select Rclone Storage Type

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

Example input for selecting S3-compatible storage during Rclone setup.

```text
Option Storage.
Type of storage to configure.
Choose a number from below, or type in your own value.
...
Storage> s3
```

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

### Initialize DuckLake Instance

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

Setup the database connection and define initial snapshots for tracking changes.

```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);
```

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

### Configure Rewrite Options

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

Examples of setting rewrite thresholds or disabling auto-compaction at different granularities.

```sql
CALL my_ducklake.set_option('rewrite_delete_threshold', 0.5);
```

```sql
CALL my_ducklake.set_option('rewrite_delete_threshold', 0.5, schema => 'my_schema');
```

```sql
CALL my_ducklake.set_option('rewrite_delete_threshold', 0.5, table_name => 'my_table');
```

```sql
CALL my_ducklake.set_option('auto_compact', false, table_name => 'my_table');
```

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

### Registering Parquet Files in DuckLake

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

Examples demonstrating how to add Parquet files to tables with various configuration options.

```sql
CALL ducklake_add_data_files('my_ducklake', 'people', 'people.parquet');
```

```sql
CALL ducklake_add_data_files('my_ducklake', 'people', 'people.parquet', schema => 'some_schema');
```

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

```sql
CALL ducklake_add_data_files('my_ducklake', 'people', 'people.parquet', ignore_extra_columns => true);
```

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

### Install DuckLake Extension

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

Install the DuckLake extension to enable its functionality within DuckDB.

```sql
INSTALL ducklake;
```

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

### Default Path Structure Example

Source: https://ducklake.select/docs/stable/duckdb/usage/paths

Visual representation of the directory structure for unpartitioned and partitioned tables in DuckLake.

```text
main
├── unpartitioned_table
│   └── ducklake-uuid.parquet
└── partitioned_table
    └── year=2025
        └── ducklake-uuid.parquet
```

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

### Enabling and Using Deletion Inlining

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

Example of configuring the deletion threshold and verifying that small deletes do not generate new Parquet delete files.

```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';
```

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

### PostgreSQL Permission Error

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

Example of a permission error during table creation.

```sql
CREATE TABLE yet_another_table (a INT);
```

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

### Configuring DuckLake Connections with Secrets

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

Examples of creating unnamed and named secrets to manage connection parameters for DuckLake, followed by the ATTACH command.

```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;
```

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

### Rewrite Data Files Usage

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

Examples of calling the rewrite function on catalogs or specific tables, including threshold configuration.

```sql
CALL ducklake_rewrite_data_files('my_ducklake');
```

```sql
CALL ducklake_rewrite_data_files('my_ducklake', 't');
```

```sql
CALL ducklake_rewrite_data_files('my_ducklake', 't', delete_threshold => 0.5);
```

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

### Unsupported Multiple Actions

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

Example of an invalid query attempting multiple update/delete conditions, followed by the resulting error.

```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 AND people.salary < 100_000.0 THEN UPDATE
    -- Second update or delete condition
    WHEN MATCHED AND people.salary > 100_000.0 THEN DELETE
    WHEN NOT MATCHED THEN INSERT;
```

```text
Not implemented Error:
MERGE INTO with DuckLake only supports a single UPDATE/DELETE action currently
```

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

### Configure Migration CLI and Connection

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

Sets up command-line arguments, establishes the DuckDB connection, and configures the DuckLake secret for migration.

```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,
    )
```

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

### DuckLake Error Message

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

Example of an S3 access error message.

```text
HTTP Error:
Unable to connect to URL "https://ducklake-access-control.s3.amazonaws.com/main/other_table_in_main/ducklake-01992ec2-d9f7-745e-88e8-708e659a70be.parquet": 403 (Forbidden).

Authentication Failure - this is usually caused by invalid or missing credentials.
* No credentials are provided.
* See https://duckdb.org/docs/stable/extensions/httpfs/s3api.html
```

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

### DuckLake Reader Error Message

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

Example of an HTTP 403 error when reading data.

```text
HTTP Error:
HTTP GET error on 'https://ducklake-access-control.s3.amazonaws.com/some_schema/another_table/ducklake-019929c8-c9c9-77d7-91e6-bc3c6dc87605.parquet' (HTTP 403)
```

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

### Configuring and Testing Data Inlining

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

Demonstrates attaching a database with a specific inlining limit and verifying that small inserts do not create Parquet files.

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

CREATE TABLE tbl (col INTEGER);
-- Inserting 3 rows – below the threshold, data is inlined
INSERT INTO tbl VALUES (1), (2), (3);
-- No Parquet files exist
SELECT count(*) FROM glob('inlining.duckdb.files/**');
```

```text
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      0       │
└──────────────┘
```

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

### Attach with external catalog and storage

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

Connect using a PostgreSQL database as the catalog and an S3 bucket as the data path.

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

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

### Initialize DuckLake with DuckDB Catalog

Source: https://ducklake.select

Configures a DuckLake instance using DuckDB as the catalog database.

```SQL
INSTALL ducklake;

ATTACH 'ducklake:metadata.ducklake'
    AS my_ducklake
    (DATA_PATH 'data/');

USE my_ducklake;
```

```SQL
INSTALL ducklake;

ATTACH 'ducklake:metadata.ducklake'
    AS my_ducklake
    (DATA_PATH 'data/');

USE my_ducklake;
```

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

### Initialize DuckLake with PostgreSQL Catalog

Source: https://ducklake.select

Configures a DuckLake instance using a PostgreSQL database for the catalog.

```SQL
INSTALL ducklake;
INSTALL postgres;

ATTACH 'ducklake:postgres:dbname=ducklake_catalog host=your_postgres_host'
    AS my_ducklake (DATA_PATH 'data/');
USE my_ducklake;
```

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

### Retrieving Last Committed Snapshot

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

Get the latest committed snapshot for an existing open connection, useful for multi-connection environments.

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

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

### Initialize DuckLake with Superuser Credentials

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

Configure S3 and PostgreSQL secrets in DuckDB to initialize the DuckLake extension as a superuser.

```sql
-- Using the credentials for the AWS DuckLake Superuser (other providers such as STS or SSO can also be used)
CREATE OR REPLACE SECRET s3_ducklake_superuser (
  TYPE s3,
  PROVIDER config,
  KEY_ID 'key',
  SECRET 'secret',
  REGION 'eu-north-1'
);

-- Using the DuckLake Superuser credentials for Postgres
CREATE OR REPLACE SECRET postgres_secret_superuser (
  TYPE postgres,
  HOST 'localhost',
  DATABASE 'access_control',
  USER 'ducklake_superuser',
  PASSWORD 'simple'
);

-- DuckLake config secret
CREATE OR REPLACE SECRET ducklake_superuser_secret (
  TYPE ducklake,
  METADATA_PATH '',
  DATA_PATH 's3://ducklake-access-control/',
  METADATA_PARAMETERS MAP {'TYPE': 'postgres','SECRET': 'postgres_secret_superuser'}
);

-- This initializes DuckLake
ATTACH 'ducklake:ducklake_superuser_secret' AS ducklake_superuser;
USE ducklake_superuser;

-- Perform operations in DuckLake
CREATE SCHEMA IF NOT EXISTS some_schema;
CREATE TABLE IF NOT EXISTS some_schema.some_table (id INTEGER, name VARCHAR);
INSERT INTO some_schema.some_table VALUES (1, 'test');
```

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

### Define macro with default parameters

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

Demonstrates setting a default value for a macro parameter.

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

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

### Variant Statistics JSON Format

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

Example of the JSON array structure used for variant statistics, recording shredded sub-field information.

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

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

### Define Nested Types Table

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

Example schema definition and data insertion for nested types including lists, structs, and maps.

```sql
CREATE TABLE nested_types (
    col_list INT[],
    col_struct STRUCT(a INT, b VARCHAR),
    col_map MAP(VARCHAR, INT)
);
INSERT INTO nested_types VALUES
    ([1, 2, 3], {'a': 10, 'b': 'hello'}, MAP {'x': 1}),
    ([4, 5, 6], {'a': 20, 'b': 'world'}, MAP {'y': 2});
```

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

### Attach with specific catalog and data paths

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

Connect using a DuckDB database as the catalog, with options to specify or default the data path.

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

```sql
ATTACH 'ducklake:duckdb_database.ducklake' (DATA_PATH 'my_files/');
```

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

### Geometry Statistics JSON Format

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

Example of the JSON structure used for geometry statistics, containing a bounding box and a list of geometry types.

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

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

### Initialize DuckLake with SQLite Catalog

Source: https://ducklake.select

Configures a DuckLake instance using a SQLite database for the catalog.

```SQL
INSTALL ducklake;
INSTALL sqlite;

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

USE my_ducklake;
```

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

### Connect via command line

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

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

```bash
duckdb ducklake:my_ducklake.ducklake
```

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

### Query DuckDB Query Logs

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

Demonstrates enabling the general QueryLog and retrieving the logs from the duckdb_logs view.

```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';
```

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

### Create and Attach a DuckLake Database

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

Initialize a new DuckLake database using the ATTACH statement and set the active database context.

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

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

### List files by point in time

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

Retrieve files as they existed at a specific timestamp.

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

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

### Wrap Table Initialization in Transaction

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

Ensures table creation fails if the user lacks permission to insert data.

```sql
BEGIN TRANSACTION;
CREATE TABLE other_table_in_main (id INTEGER, name VARCHAR);
INSERT INTO other_table_in_main VALUES (1, 'test');
COMMIT;
```

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

### Configure PostgreSQL Roles

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

Create superuser, writer, and reader roles in PostgreSQL with appropriate schema and table permissions.

```sql
-- Setup initialization user, migrations, and writing, assuming the database is already created
CREATE USER ducklake_superuser WITH PASSWORD 'simple';
GRANT CREATE ON DATABASE access_control TO ducklake_superuser;
GRANT CREATE, USAGE ON SCHEMA public TO ducklake_superuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ducklake_superuser;

-- Writer/reader
CREATE USER ducklake_writer WITH PASSWORD 'simple';
GRANT USAGE ON SCHEMA public TO ducklake_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ducklake_writer;

-- Reader only
CREATE USER ducklake_reader WITH PASSWORD 'simple';
GRANT USAGE ON SCHEMA public TO ducklake_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ducklake_reader;
```

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

### Inspect Directory Structure

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

Displays the file structure of the generated DuckLake directory.

```bash
tree tpch-sf1
```

```text
tpch-sf1
└── main
    ├── customer
    │   └── ducklake-019a2726-0362-7eae-8a4d-1404ead2c506.parquet
    ├── lineitem
    │   └── ducklake-019a2726-03ad-7d79-b28a-6ae3f114fbd3.parquet
    ├── nation
    │   └── ducklake-019a2726-055d-7f55-914d-02787bda2eae.parquet
    ├── orders
    │   └── ducklake-019a2726-0579-72e2-be9e-0190b3f8f8af.parquet
    ├── part
    │   └── ducklake-019a2726-05fd-7d30-8051-a9172d75f815.parquet
    ├── partsupp
    │   └── ducklake-019a2726-0645-765b-9456-6ba632d8288b.parquet
    ├── region
    │   └── ducklake-019a2726-06a4-7b3a-9788-8f32006be0ad.parquet
    └── supplier
        └── ducklake-019a2726-06bb-72f4-97b3-131d7186787e.parquet
```

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

### List files by schema

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

Retrieve files for a table located within a specific schema.

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

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

### Attach a DuckLake instance

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

Connect to a new or existing DuckLake by specifying the catalog database and data storage location.

```sql
ATTACH 'ducklake:metadata_storage_location' (DATA_PATH 'data_storage_location');
```

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

### Configure SQLite as Catalog Database

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

Use SQLite for local data warehousing with multiple local clients.

```sql
INSTALL ducklake;
INSTALL sqlite;

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

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

### Query DuckLake Options

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

Retrieve all currently configured options for an attached DuckLake instance.

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

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

### Create Timestamped Backup in Python

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

Generate a backup file with a current timestamp using Python's datetime module, as ATTACH does not support functions directly.

```python
import duckdb
import datetime
con = duckdb.connection(f"backup_{datetime.datetime.now().strftime('%Y-%m-%d__%I_%M_%S')}.duckdb")
```

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

### Initialize a Remote DuckLake

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

Initializes the DuckLake with a remote URL as the data path and detaches immediately.

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

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

### Configure DuckDB as Catalog Database

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

Use DuckDB for local data warehousing with a single client.

```sql
INSTALL ducklake;

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

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

### Configure Logging Storage

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

Directs logs to stdout or a specific file path instead of the default in-memory buffer.

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

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

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

### Upload DuckLake Data with Rclone

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

Commands to copy the catalog file and synchronize the data directory to the remote bucket.

```bash
rclone cp -v your_ducklake_catalog.ducklake your_rclone_remote:your_bucket_name/path/
rclone sync -v your_ducklake_directory your_rclone_remote:your_bucket_name/path/
```

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

### Full Schema Creation Script

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

Executes the complete set of CREATE TABLE statements to initialize the DuckLake database structure.

```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);
```

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

### Migration Script CLI Usage

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

Command-line arguments required to execute the migration script.

```bash
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
```

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

### Manage Transactions with SQL

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

Use BEGIN, COMMIT, and ROLLBACK to define transactional boundaries for database operations.

```sql
BEGIN TRANSACTION;
-- Some operation
-- Some other operation
COMMIT;
-- Or
ROLLBACK; -- ABORT will have the same behavior
```

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

### List files by snapshot version

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

Retrieve files associated with a specific snapshot version of the table.

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

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

### Partition by Hash Buckets

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

Distribute rows into a fixed number of buckets using an Iceberg-compatible hash.

```sql
ALTER TABLE tbl SET PARTITIONED BY (bucket(8, user_id));
```

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

### Create a new snapshot

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

Register a new snapshot and log its associated changes in the DuckLake metadata tables.

```sql
INSERT INTO ducklake_snapshot (
    snapshot_id,
    snapshot_timestamp,
    schema_version,
    next_catalog_id,
    next_file_id
)
VALUES (
    snapshot_id,
    now(),
    schema_version,
    next_catalog_id,
    next_file_id
);

INSERT INTO ducklake_snapshot_changes (
    snapshot_id,
    changes_made,
    author,
    commit_message,
    commit_extra_info
)
VALUES (
    snapshot_id,
    changes,
    author,
    commit_message,
    commit_extra_info
);
```

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

### Configure DuckLake Writer

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

Sets up secrets for Postgres and S3 to enable the DuckLake writer role.

```sql
-- Drop this to avoid the extension defaulting to this secret
DROP SECRET s3_ducklake_superuser;

-- Using the DuckLake Writer credentials for Postgres
CREATE OR REPLACE SECRET postgres_secret_writer (
  TYPE postgres,
  HOST 'localhost',
  DATABASE 'access_control',
  USER 'ducklake_writer',
  PASSWORD 'simple'
);

-- Using the credentials for the AWS DuckLake Writer
CREATE OR REPLACE SECRET s3_ducklake_schema_reader_writer (
  TYPE s3,
  PROVIDER config,
  KEY_ID 'key',
  SECRET 'secret',
  REGION 'eu-north-1'
);

-- DuckLake config secret
CREATE OR REPLACE SECRET ducklake_writer_secret (
  TYPE ducklake,
  METADATA_PATH '',
  DATA_PATH 's3://ducklake-access-control/',
  METADATA_PARAMETERS MAP {'TYPE': 'postgres','SECRET': 'postgres_secret_writer'}
);

ATTACH 'ducklake:ducklake_writer_secret' AS ducklake_writer;
USE ducklake_writer;

-- Perform operations
CREATE TABLE IF NOT EXISTS some_schema.another_table (id INTEGER, name VARCHAR);
INSERT INTO some_schema.another_table VALUES (1, 'test'); -- Works
INSERT INTO some_schema.some_table VALUES (2, 'test2'); -- Also works

-- Try to perform an unauthorized operation
CREATE TABLE other_table_in_main (id INTEGER, name VARCHAR); -- This unfortunately works
INSERT INTO other_table_in_main VALUES (1, 'test'); -- This doesn't work
```

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

### Inspect underlying files

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

Query the file system to view the Parquet files generated by DuckLake.

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

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

### List underlying files after update

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

Observe the file system changes after performing an update operation.

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

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

### Default Value Constraints

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

Demonstrates the difference between supported literal default values and unsupported non-literal default values in DuckLake.

```sql
-- This is allowed
CREATE TABLE t1 (id INTEGER, d DATE DEFAULT '2025-08-08');

-- This is not allowed
CREATE TABLE t1 (id INTEGER, d DATE DEFAULT now());
```

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

### Set Table Partitioning Keys

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

Define partitioning keys for a table to organize new data writes.

```sql
ALTER TABLE tbl SET PARTITIONED BY (part_key);
```

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

### Tiered Compaction Workflow

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

Implements a tiered compaction strategy by adjusting target file sizes and size filters in stages.

```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);
```

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

### Enable Multiple Log Types

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

Enables both DuckLakeMetadata and QueryLog simultaneously using an array.

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

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

### Configure PostgreSQL as Catalog Database

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

Use PostgreSQL for multi-user lakehouse operations with remote clients. Requires PostgreSQL 12 or newer.

```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;
```

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

### List table files

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

Retrieve all data and delete files associated with a specified table in a catalog.

```sql
FROM ducklake_list_files('catalog', 'table_name');
```

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

### Configure DuckLake Reader

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

Sets up secrets for the DuckLake reader role.

```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
```

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

### Query DuckLake Metadata Logs

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

Demonstrates enabling metadata logging and querying the resulting structured log data.

```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');
```

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

### Migrate Database Objects

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

Processes a queue of database objects to create schemas, views, and tables in the target DuckLake environment.

```python
    while queue:
        catalog, schema, table, table_type = queue.popleft()
        con.execute(f"CREATE SCHEMA IF NOT EXISTS {schema}")
        try:
            if table_type == "VIEW":
                view_definition = con.execute(
                    f"SELECT view_definition FROM information_schema.views "
                    f"WHERE table_name = '{table}' AND table_schema = '{schema}' AND table_catalog = '{catalog}'"
                ).fetchone()[0]
                con.execute(
                    f"CREATE VIEW IF NOT EXISTS {view_definition.removeprefix('CREATE VIEW ')}"
                )
                print(f"Migrating Catalog: {catalog}, Schema: {schema}, View: {table}")
            else:
                excepts, casts = _resolve_data_types(table, schema, catalog, con)
                if casts:
                    select_clause = (
                        "* EXCLUDE(" + ", ".join(excepts) + "),\n" + ",\n".join(casts)
                    )
                    con.execute(
                        f"CREATE TABLE IF NOT EXISTS {schema}.{table} AS "
                        f"SELECT {select_clause} FROM {catalog}.{schema}.{table}"
                    )
                else:
                    con.execute(
                        f"CREATE TABLE IF NOT EXISTS {schema}.{table} AS "
                        f"SELECT * FROM {catalog}.{schema}.{table}"
                    )
                print(f"Migrating Catalog: {catalog}, Schema: {schema}, Table: {table}")
        except Exception as e:
            print(f"WARNING: Requeuing {table_type} {table}")
            # Prevent infinite loop if no progress is possible
            if (catalog, schema, table, table_type) in failed_last_round:
                print(
                    f"Skipping {table_type} {table} permanently due to repeated failure. {e}"
                )
                continue
            else:
                queue.append((catalog, schema, table, table_type))
                failed_last_round.add((catalog, schema, table, table_type))
        else:
            # Success — ensure we clear from failure set
            failed_last_round.discard((catalog, schema, table, table_type))
```

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

### Enable DuckDB Query Logging

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

Activates the built-in QueryLog to trace all SQL queries.

```sql
CALL enable_logging('QueryLog');
```

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

### Querying Snapshots

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

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();
```

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

### Configure MySQL as Catalog Database

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

Use MySQL for catalog storage, though it is not recommended due to known connector limitations. Requires MySQL 8 or newer.

```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;
```

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

### Promote Column Types

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

Change column or field types using lossless type promotion.

```sql
ALTER TABLE tbl ALTER col1 SET TYPE BIGINT;
```

```sql
ALTER TABLE tbl ALTER nested_column.new_field SET TYPE BIGINT;
```

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

### Create and use a table macro

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

Defines a macro that returns a filtered table based on a threshold parameter.

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

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

### Configure DuckLake Extension Settings

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

Use the SET statement to modify global configuration parameters for the DuckLake extension.

```sql
SET ducklake_default_data_inlining_row_limit = 50;
SET ducklake_max_retry_count = 100;
SET ducklake_retry_backoff = 2;
SET ducklake_retry_wait_ms = 100;
```

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

### Attach Database at Snapshot Time

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

Attaches a DuckLake database as it existed at a specific timestamp.

```sql
ATTACH 'ducklake:metadata.duckdb' (SNAPSHOT_TIME '2025-05-26 00:00:00');
```

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

### CALL set_option

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

Configures global catalog options for snapshot expiration.

```APIDOC
## CALL [catalog].set_option

### Description
Sets a global configuration option for the catalog, such as the automatic expiration threshold.

### Parameters
- **option_name** (string) - Required - The name of the option to set (e.g., 'expire_older_than').
- **value** (string) - Required - The value for the option (e.g., '1 month').

### Example
CALL my_ducklake.set_option('expire_older_than', '1 month');
```

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

### Set catalog-level cleanup option

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

Configures the retention period for file deletion at the catalog level.

```sql
CALL my_ducklake.set_option('delete_older_than', '1 week');
```

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

### Attach in read-only mode

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

Connect to a DuckLake instance without write permissions.

```sql
ATTACH 'ducklake:postgres:dbname=postgres' (READ_ONLY);
```

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

### Execute CHECKPOINT

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

Runs the full suite of maintenance functions across the entire DuckLake instance using default configuration options.

```sql
CHECKPOINT;
```

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

### Migrate DuckDB to DuckLake via SQL

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

Use these commands to perform a direct migration when all features are supported by DuckLake.

```sql
ATTACH 'ducklake:my_ducklake.ducklake' AS my_ducklake;
ATTACH 'db.duckdb' AS my_duckdb;

COPY FROM DATABASE my_duckdb TO my_ducklake;
```

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

### Use Expression-Based Sort Keys

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

Apply sort orders using SQL expressions, functions, or custom macros.

```sql
ALTER TABLE events SET SORTED BY (date_trunc('hour', event_time) ASC);
```

```sql
CREATE MACRO event_bucket(t) AS date_trunc('day', t);
ALTER TABLE events SET SORTED BY (event_bucket(event_time) ASC);
```

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

### Combine Partitioning Transforms

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

Apply multiple partitioning transforms simultaneously to a table.

```sql
ALTER TABLE tbl SET PARTITIONED BY (bucket(8, user_id), month(ts));
```

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

### Attach Database at Snapshot Version

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

Attaches a DuckLake database to a specific snapshot version.

```sql
ATTACH 'ducklake:metadata.duckdb' (SNAPSHOT_VERSION 3);
```

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

### List database snapshots

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

Retrieve a list of all historical snapshots for the database.

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

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

### Create a view in DuckDB

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

Use the standard CREATE VIEW statement to define a view based on a SELECT query.

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

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

### snapshots()

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

Returns a list of all snapshots and their associated changesets for the attached DuckLake database.

```APIDOC
## snapshots()

### Description
Queries the set of snapshots to retrieve a list of all snapshots and their changesets.

### Usage
SELECT * FROM [database_alias].snapshots();
```

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

### Filter Files by Size

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

Restricts compaction to files within a specific size range in bytes.

```sql
-- Only merge files between 10KB and 100KB
CALL ducklake_merge_adjacent_files('my_ducklake', min_file_size => 10240, max_file_size => 102400);
```

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

### Initialize Encrypted DuckLake Catalog

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

Use the ENCRYPTED flag during the ATTACH command to enable encryption for the data directory.

```sql
ATTACH 'ducklake:encrypted.ducklake'
    (DATA_PATH 'untrusted_location/', ENCRYPTED);
```

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

### Import data into a DuckLake table

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

Create a new table by importing data from a remote CSV source.

```sql
CREATE TABLE nl_train_stations AS
    FROM 'https://blobs.duckdb.org/nl_stations.csv';
```

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

### Backup and Recover SQLite Catalog

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

Perform catalog backups and recovery for SQLite using the same COPY FROM DATABASE pattern as DuckDB.

```sql
-- Backup
ATTACH 'sqlite:db.duckdb' AS db (READ_ONLY);
ATTACH 'sqlite:backup.duckdb' AS backup;
COPY FROM DATABASE db TO backup;

-- Recover
ATTACH 'sqlite:db.duckdb' AS db;
ATTACH 'sqlite:backup.duckdb' AS backup (READ_ONLY);
COPY FROM DATABASE backup TO db;
ATTACH 'ducklake:sqlite:db.duckdb' AS my_ducklake;
```

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

### Populate the DuckLake

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

Attaches to the DuckLake locally to load data and flush inlined content.

```sql
ATTACH 'tpch-sf1.ducklake' AS tpch_sf1_ducklake (
    TYPE ducklake,
    DATA_PATH 'tpch-sf1',
    OVERRIDE_DATA_PATH true
);
USE tpch_sf1_ducklake;
```

```sql
CREATE TABLE customer AS FROM 'customer.parquet';
CREATE TABLE lineitem AS FROM 'lineitem.parquet';
CREATE TABLE nation AS FROM 'nation.parquet';
CREATE TABLE orders AS FROM 'orders.parquet';
CREATE TABLE part AS FROM 'part.parquet';
CREATE TABLE partsupp AS FROM 'partsupp.parquet';
CREATE TABLE region AS FROM 'region.parquet';
CREATE TABLE supplier AS FROM 'supplier.parquet';
```

```sql
CALL ducklake_flush_inlined_data('tpch_sf1_ducklake');
```

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

### Calculate Total Output Files per Table

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

Use a GROUP BY clause on the result of the function to aggregate the total number of output files produced for each table.

```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;
```

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

### Query Table at Snapshot Version

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

Retrieves data from a table as it existed at a specific snapshot version.

```sql
SELECT * FROM tbl AT (VERSION => 3);
```
Tokens:
Raw