Delta Lake

MotherDuck supports querying data in the Delta Lake format. The Delta DuckDB extension is loaded automatically when any of the supported Delta Lake functions are called.

Delta function

Function Name	Description	Supported parameters
delta_scan	Query Delta Lake data	All the parquet_scan parameters plus delta_file_number.

note

The available functions are only for reading Delta Lake data. Creating or updating data in Delta format is not yet supported.

Examples

-- query data
SELECT COUNT(*) FROM delta_scan('path-to-delta-folder');

-- query data with parameters
FROM delta_scan('path-to-delta-folder', delta_file_number=1, file_row_number=1);

Query Delta data stored in Amazon S3

warning

At the moment, querying Delta tables stored in Amazon S3 from public buckets is not supported.

Create a S3 secret in MotherDuck using the secret manager:

CREATE SECRET IN MOTHERDUCK (
    TYPE S3,
    KEY_ID 's3_access_key',
    SECRET 's3_secret_key',
    REGION 's3-region'
);

Query Delta data stored in S3:

SELECT count(*) 
FROM delta_scan('s3://<your-bucket>/<your-delta-folder>');

note

To query data in an Amazon S3 bucket, you will need to configure your Amazon S3 credentials.

Example using MotherDuck Delta sample dataset.

SELECT COUNT(*)
FROM delta_scan('s3://us-prd-motherduck-open-datasets/file_format_demo/delta_lake/dat/out/reader_tests/generated/basic_append/delta');