Constructing a Information Lakehouse with DuckDB and DuckLake

A few years in the past, should you wished to retailer massive quantities of information that might be sensibly queried, a database like Oracle or Postgres and such was your most important selection. Positive, there have been different choices just like the mainframe techniques from firms akin to ICL and IBM, however they had been very pricey and locked you in to a selected producer. 

The subsequent large advance in knowledge storage was the information warehouse. This introduced info from separate operational techniques right into a central repository designed particularly for reporting and historic evaluation. Its most important benefits had been quicker analytical queries and constant enterprise definitions, whereas its disadvantages included costly infrastructure, advanced ETL pipelines and the necessity to mannequin knowledge earlier than loading it.

The latest advance in knowledge storage is the emergence of the information lake. Information lakes allowed organisations to retailer a lot bigger volumes of uncooked structured, semi-structured, and unstructured knowledge cheaply. I say low cost, however don’t get me mistaken; firms like Databricks, Snowflake, and the large cloud suppliers like AWS are vying to extract as a lot money as attainable from their clients to make the administration, operating, and growth of information lakes as clean as attainable.

However fact be advised, you may go a good distance towards growing an efficient knowledge lake for nearly zero price with DuckDB and the DuckLake extension (additionally free) for DuckDB.

In the remainder of this text, I’ll present you ways.

Each DuckDB and DuckLake are MIT-licensed, open-source, and free to make use of. To be clear, I’ve no affiliation or business affiliation with any of the techniques or their creators talked about on this article.

A fast recap on Parquet format recordsdata, DuckDB, and DuckLake

Information lakes of just about all sorts depend on Parquet recordsdata to retailer their underlying knowledge. Parquet is a columnar file format designed for analytical knowledge. It shops values from the identical column collectively, which permits question engines to learn solely the columns wanted by a question. Parquet recordsdata are usually immutable and sometimes want further metadata recordsdata to be helpful in knowledge lakes. The metadata information which Parquet recordsdata belong to an information desk, their places, partitions and statistics, in addition to which recordsdata had been added or eliminated throughout every desk model. Moreover, all adjustments to the information made by way of SQL, like inserts, updates, deletes and schema adjustments, are tracked. This metadata permits a lakehouse system to assist environment friendly queries, transactions, schema evolution and time journey with out modifying the underlying Parquet recordsdata immediately.

I’ve written many instances earlier than about DuckDB. One in all my favorite third-party Python libraries, it’s a super-fast, in-memory analytical database appropriate for small to medium databases (say as much as a few hundred GBs of information).

DuckLake is an extension for DuckDB, developed by the staff behind DuckDB and launched simply over a 12 months in the past. It turned the normal concept of how an information lake file system needs to be structured on its head, managing the metadata in a relational database as an alternative of in recordsdata co-located with the underlying Parquet knowledge recordsdata. By the way, the database used to retailer the DuckLake metadata would not ned to be DuckDB. Postgres, SQLite and MySQL are additionally supported.

A number of competing desk codecs handle knowledge in fashionable knowledge lakes, together with Delta Lake, Apache Iceberg, and Apache Hudi. As talked about, all of them retailer the underlying knowledge in Parquet format recordsdata and document desk state and alter historical past in metadata recordsdata saved with, or near, the Parquet knowledge.

DuckLake can retailer petabytes of information, however processing it’s the bottleneck. Single-node DuckDB is properly suited to selective queries that scan solely a manageable portion of the lake, however multi-user workloads that repeatedly course of tens or tons of of terabytes would require a beefier database like Postgres and sure a distributed question engine akin to Spark.

A number of months in the past, DuckDB launched V1.0 of DuckLake, signalling it was prepared for manufacturing use.

What we’ll construct

On this article, we’ll construct an instance knowledge lake in two phases, starting with a single buyer Parquet file on our native laptop and utilizing it to discover the principle options of DuckDB and DuckLake. As soon as the native lakehouse is working, we are going to add an orders Parquet file saved in Amazon S3 and be part of it to the native buyer knowledge. 

Notice, though the aim of information lakes is the storage and processing of enormous knowledge volumes, the concept behind this explicit article is to point out the “the way to” of constructing an information lake, so I’m not involved with the information volumes and the information recordsdata I’ll be utilizing might be very small.

By the tip, we could have demonstrated the way to:

• Use DuckDB to question native and distant Parquet recordsdata

• Create a DuckLake to retailer native knowledge

• Use DuckDB to question our DuckLake

• Use SQL to replace desk knowledge and evolve a desk’s schema.

• Use DuckLake snapshots to look at earlier variations.

• Carry out a be part of between a DuckLake desk and an exterior S3 file.

• Create a DuckLake desk with our exterior S3 file knowledge

Conditions

You’ll need:

• Home windows, macOS or a latest Linux distribution. I’m utilizing Home windows.

• A terminal or PowerShell.

• An web connection to put in the DuckDB CLI and its DuckLake extension.

• An AWS account for the S3 a part of the article.

• Permission to create or use an S3 bucket.

• The AWS CLI if you wish to observe the command-line add steps.

The instance creates a really small S3 object, however AWS storage and request fees should apply. Please delete it if you’re accomplished to keep away from any unwelcome payments. Notice: should you don’t need to use the cloud for the second a part of the information instance, it’s nice to make use of native storage once more.

Creating our venture construction

Our folder construction for our venture goes to appear to be this.

ducklake-demo/├── knowledge/│   ├── clients.parquet│   ├── orders.parquet│   ├── metadata.ducklake│   └── lake/└── duckdb.dev

The recordsdata have completely different functions:

• clients.parquet is our authentic native supply file.

• orders.parquet is a staging file that we are going to (optionally) add to S3.

• metadata.ducklake comprises the DuckLake catalogue.

• lake/ comprises Parquet recordsdata managed by DuckLake.

• duckdb.dev holds the DuckDB session database.

On Home windows PowerShell, run:

PS C:Usersthoma> New-Merchandise -ItemType Listing -Pressure ducklake-demodatalakePS C:Usersthoma> Set-Location ducklake-demo

Putting in DuckDB

DuckDB is accessible as a command-line program for Home windows. All strategies to put in DuckDB are documented on the official DuckDB installation page. Select your choice and observe the directions.

For me, the only Home windows set up makes use of winget.

PS C:Usersthomaducklake-demo> winget set up DuckDB.cliDiscovered DuckDB CLI [DuckDB.cli] Model 1.5.5This software is licensed to you by its proprietor.Microsoft just isn't answerable for, nor does it grant any licenses to, third-party packages.Downloading https://github.com/duckdb/duckdb/releases/obtain/v1.5.5/duckdb_cli-windows-amd64.zip  ██████████████████████████████  12.3 MB / 12.3 MBEfficiently verified installer hashExtracting archive...Efficiently extracted archiveBeginning bundle set up...Path surroundings variable modified; restart your shell to make use of the brand new worth.Command line alias added: "duckdb"Efficiently put in

Shut and reopen PowerShell, then examine the set up:

PS C:Usersthomaducklake-demo> duckdb --versionv1.5.5 (Variegata) d8cdaa33fdPS C:Usersthomaducklake-demo>

Creating our DuckDB database and putting in DuckLake

Begin DuckDB and create a persistent working database:

PS C:Usersthomaducklake-demo> duckdb duckdb.dev

It’s best to now see the DuckDB immediate:

DuckLake is distributed as a DuckDB extension. There isn’t any separate desktop software or server to put in.

From the DuckDB immediate, run:

INSTALL ducklake;LOAD ducklake;

Create the native “clients” Parquet file

We’ll start with a small buyer dataset. Enter the next statements:

PS C:Usersthomaducklake-demo> duckdb duck.devDuckDB v1.5.5 (Variegata)Enter ".assist" for utilization hints.duck D COPY (           SELECT *           FROM (               VALUES                   (1001, 'Acme Ltd',  'London'),                   (1002, 'Northwind', 'Leeds'),                   (1003, 'Globex',    'Glasgow'),                   (1004, 'Initech',   'Manchester')           ) AS clients(               customer_id,               customer_name,               area           )       )       TO 'knowledge/clients.parquet'       (FORMAT PARQUET);duck D

This creates the file knowledge/clients.parquet. At this level, the information exists as an odd file, not a DuckDB or DuckLake desk. So, we are able to question the Parquet file immediately like this.

duck D SELECT *       FROM read_parquet('knowledge/clients.parquet');┌─────────────┬───────────────┬────────────┐│ customer_id │ customer_name │   area   ││    int32    │    varchar    │  varchar   │├─────────────┼───────────────┼────────────┤│        1001 │ Acme Ltd      │ London     ││        1002 │ Northwind     │ Leeds      ││        1003 │ Globex        │ Glasgow    ││        1004 │ Initech       │ Manchester │└─────────────┴───────────────┴────────────┘

That is all nice, but it surely doesn’t flip the file right into a transactional desk. Parquet is an immutable file format from the standpoint of regular SQL operations. We are able to’t deal with our authentic file precisely like a database desk and replace one row in place, for instance. The file would should be changed or rewritten. That’s the place DuckLake comes into its personal.

Create the native DuckLake

Connect a brand new DuckLake catalogue:

duck D ATTACH 'ducklake:knowledge/metadata.ducklake' AS customer_lake (    DATA_PATH 'knowledge/lake/');

This assertion identifies two storage places:

knowledge/metadata.ducklake    - The Metadata catalogueknowledge/lake/                - The Managed Parquet recordsdata

If {the catalogue} doesn’t exist already, DuckLake creates it. The information path can also be recorded within the catalogue, so it doesn’t should be equipped once more when reconnecting later.

We are able to see the databases hooked up to the present DuckDB session with this command:

duck D present databases;┌───────────────┐│ database_name ││    varchar    │├───────────────┤│ customer_lake ││ duck          │└───────────────┘

Now we are able to import our clients file into DuckLake to create a managed desk.

duck D CREATE TABLE customer_lake.clients ASSELECT *FROM read_parquet('knowledge/clients.parquet');

There are actually two copies of the information.

1) The unique knowledge/clients.parquet supply file.
2) The managed DuckLake desk saved underneath knowledge/lake/.

The unique file hasn’t been modified, and we are able to question the brand new desk similar to we’d another database desk.

duck D SELECT *       FROM customer_lake.clients;┌─────────────┬───────────────┬────────────┐│ customer_id │ customer_name │   area   ││    int32    │    varchar    │  varchar   │├─────────────┼───────────────┼────────────┤│        1001 │ Acme Ltd      │ London     ││        1002 │ Northwind     │ Leeds      ││        1003 │ Globex        │ Glasgow    ││        1004 │ Initech       │ Manchester │└─────────────┴───────────────┴────────────┘

It seems to be the identical as an everyday desk, and it behaves the identical. The necessary variations are behind the scenes. For instance, we are able to checklist the bodily recordsdata utilized by the desk:

duck D CALL ducklake_flush_inlined_data(           'customer_lake',           schema_name => 'most important',           table_name => 'clients'       );┌─────────────┬────────────┬──────────────┐│ schema_name │ table_name │ rows_flushed ││   varchar   │  varchar   │    int128    │├─────────────┼────────────┼──────────────┤│ most important        │ clients  │            4 │└─────────────┴────────────┴──────────────┘duck D .mode lineduck D FROM ducklake_list_files(           'customer_lake',           'clients'       );                 data_file = datalakemaincustomersducklake-019fdb4f-df34-7498-93c2-1f9a521b7c8b.parquet      data_file_size_bytes = 943     data_file_footer_size = 674  data_file_encryption_key = NULL               delete_file = NULL    delete_file_size_bytes = NULL   delete_file_footer_size = NULLdelete_file_encryption_key = NULL

DuckLake maintains the connection between the logical clients desk and the Parquet recordsdata used to retailer it.

Inspecting the DuckDB DuckLake metadata

Behind the scenes, DuckDB is squirrelling away metadata that tracks the standing of our knowledge lake. Right here’s how one can entry that knowledge.

duck D DETACH customer_lake;duck Dduck D ATTACH 'knowledge/metadata.ducklake'       AS customer_metadata (READ_ONLY);duck D SELECT table_schema, table_name       FROM information_schema.tables       WHERE table_catalog = 'customer_metadata'       ORDER BY table_schema, table_name;┌──────────────┬───────────────────────────────────────┐│ table_schema │              table_name               ││   varchar    │                varchar                │├──────────────┼───────────────────────────────────────┤│ most important         │ ducklake_column                       ││ most important         │ ducklake_column_mapping               ││ most important         │ ducklake_column_tag                   ││ most important         │ ducklake_data_file                    ││ most important         │ ducklake_delete_file                  ││ most important         │ ducklake_file_column_stats            ││ most important         │ ducklake_file_partition_value         ││ most important         │ ducklake_file_variant_stats           ││ most important         │ ducklake_files_scheduled_for_deletion ││ most important         │ ducklake_inlined_data_1_1             ││ most important         │ ducklake_inlined_data_1_2             ││ most important         │ ducklake_inlined_data_2_3             ││ most important         │ ducklake_inlined_data_3_5             ││ most important         │ ducklake_inlined_data_tables          ││ most important         │ ducklake_inlined_delete_1             ││ most important         │ ducklake_macro                        ││ most important         │ ducklake_macro_impl                   ││ most important         │ ducklake_macro_parameters             ││ most important         │ ducklake_metadata                     ││ most important         │ ducklake_name_mapping                 ││ most important         │ ducklake_partition_column             ││ most important         │ ducklake_partition_info               ││ most important         │ ducklake_schema                       ││ most important         │ ducklake_schema_versions              ││ most important         │ ducklake_snapshot                     ││ most important         │ ducklake_snapshot_changes             ││ most important         │ ducklake_sort_expression              ││ most important         │ ducklake_sort_info                    ││ most important         │ ducklake_table                        ││ most important         │ ducklake_table_column_stats           ││ most important         │ ducklake_table_stats                  ││ most important         │ ducklake_tag                          ││ most important         │ ducklake_view                         │└──────────────┴───────────────────────────────────────┘  33 rows                                    2 columns

Question any of the tables in column 2 above as you’ll an everyday database desk. e.g.

duck D choose * from customer_metadata.ducklake_table_column_stats;┌──────────┬───────────┬───────────────┬──────────────┬────────────┬────────────┬─────────────┐│ table_id │ column_id │ contains_null │ contains_nan │ min_value  │ max_value  │ extra_stats ││  int64   │   int64   │    boolean    │   boolean    │  varchar   │  varchar   │   varchar   │├──────────┼───────────┼───────────────┼──────────────┼────────────┼────────────┼─────────────┤│        1 │         1 │ false         │ NULL         │ 1001       │ 1004       │ NULL        ││        1 │         2 │ false         │ NULL         │ Acme Ltd   │ Northwind  │ NULL        ││        1 │         3 │ false         │ NULL         │ Glasgow    │ Yorkshire  │ NULL        │└──────────┴───────────┴───────────────┴──────────────┴────────────┴────────────┴─────────────┘

When completed inspecting them, ensure you change again your attachment:

duck D DETACH customer_metadata;duck D ATTACH 'ducklake:knowledge/metadata.ducklake'AS customer_lake

Updating a DuckLake desk

Suppose we need to substitute London with Larger London in our clients desk for buyer 1001. It’s simply common SQL.

duck D UPDATE customer_lake.clientsSET area = 'Larger London'WHERE customer_id = 1001;duck D SELECT *       FROM customer_lake.clients       WHERE customer_id = 1001;customer_id  customer_name  area-----------  -------------  --------------1001         Acme Ltd       Larger London

The unique supply file hasn’t been up to date. We are able to show that by querying it once more:

duck D SELECT *       FROM read_parquet('knowledge/clients.parquet')       WHERE customer_id = 1001;customer_id  customer_name  area-----------  -------------  ------1001         Acme Ltd       London

That question nonetheless returns London.

DuckLake did not make the unique file mutable. It created and now manages a separate illustration of the desk.

Inspecting the snapshot historical past

Each dedicated change to a DuckLake database is related to a snapshot. A snapshot is a point-in-time illustration of the information lake, together with its schemas, tables and underlying knowledge recordsdata. Snapshots retailer metadata about every model relatively than creating an entire copy of the information. We are able to checklist all of the snapshots with this question:

duck D SELECT           snapshot_id,           snapshot_time,           schema_version,           adjustments,           writer,           commit_message       FROM customer_lake.snapshots()       ORDER BY snapshot_id;snapshot_id  snapshot_time                  schema_version  adjustments                                                writer  commit_message-----------  -----------------------------  --------------  -----------------------------------------------------  ------  --------------0            2026-08-07 09:10:35.1368+01    0               {schemas_created=[main]}                               NULL    NULL1            2026-08-07 09:13:56.142558+01  1               {tables_created=[main.customers], inlined_insert=[1]}  NULL    NULL2            2026-08-07 09:21:12.625364+01  1               {flushed_inlined=[1]}                                  NULL    NULL3            2026-08-07 09:24:15.991166+01  1               {inlined_insert=[1], inlined_delete=[1]}               NULL    NULL

It’s best to see separate snapshots for operations akin to creating tables, knowledge inserts, deletes and updates. Notice that an replace is handled as a delete adopted by an insert. The usage of snapshots has one very helpful aspect impact. It means we are able to return in time and question desk contents as they had been in some unspecified time in the future prior to now versus what they’re proper now.

Time-travel queries

Let’s say we’ve forgotten what area was assigned to customer_id 1001 when it was first created. Wanting on the above snapshot question we are able to glean that snapshot_id = 1 ought to give us that info, so we are able to use that identifier within the following question.

duck D SELECT *       FROM customer_lake.clients       AT (VERSION => 1)       WHERE customer_id = 1001;customer_id  customer_name  area-----------  -------------  ------1001         Acme Ltd       London

In addition to utilizing model numbers, DuckLake may choose a model by timestamp. For instance,

duck D SELECT *       FROM customer_lake.clients       AT (           TIMESTAMP => now() - INTERVAL '25 minutes'       );customer_id  customer_name  area-----------  -------------  ----------1001         Acme Ltd       London1002         Northwind      Leeds1003         Globex         Glasgow1004         Initech        Manchester

Whether or not this returns the sooner or present values for knowledge is determined by if you ran the replace. 

Snapshots generally is a life-saver. Let’s say we inadvertantly delete our buyer information with ids 1002 and 1004.

duck D delete from customer_lake.clients the place customer_id in (1002,1004);duck D choose * from  customer_lake.clients;customer_id  customer_name  area          -----------  -------------  -------------- 1003         Globex         Glasgow        1001         Acme Ltd       Larger London

We are able to get again the unique deleted knowledge if we go to a snaphot from earlier than the unique delete ws transacted.

duck D SELECT *       FROM customer_lake.clients       AT (VERSION => 1)       WHERE customer_id in (1002,1004);┌─────────────┬───────────────┬────────────┐│ customer_id │ customer_name │   area   ││    int32    │    varchar    │  varchar   │├─────────────┼───────────────┼────────────┤│        1002 │ Northwind     │ Leeds      ││        1004 │ Initech       │ Manchester │└─────────────┴───────────────┴────────────┘

Now, simply re-insert this knowledge into the unique clients desk and our knowledge is recovered.

duck D insert into customer_lake.clients       SELECT *              FROM customer_lake.clients              AT (VERSION => 1)              WHERE customer_id in (1002,1004);duck D choose * from customer_lake.clients;┌─────────────┬───────────────┬────────────────┐│ customer_id │ customer_name │     area     ││    int32    │    varchar    │    varchar     │├─────────────┼───────────────┼────────────────┤│        1003 │ Globex        │ Glasgow        ││        1001 │ Acme Ltd      │ Larger London ││        1002 │ Northwind     │ Leeds          ││        1004 │ Initech       │ Manchester     │└─────────────┴───────────────┴────────────────┘

Including commit messages

The replace we did beforehand, created a snapshot, but it surely didn’t clarify why the change was made. DuckLake permits an writer and commit message to be related to a transaction.

Run one other replace inside an express transaction:

duck D start;duck D UPDATE customer_lake.clients       SET area = 'Yorkshire'       WHERE customer_id = 1002;duck D CALL customer_lake.set_commit_message(           'Article demonstration',           'Up to date area to Yorkshire for buyer 1002'       );Success-------duck D commit;

Examine the snapshots once more:

duck D SELECT           snapshot_id,           snapshot_time,           writer,           commit_message       FROM customer_lake.snapshots()       ORDER BY snapshot_id;snapshot_id  snapshot_time                  writer                 commit_message-----------  -----------------------------  ---------------------  ---------------------------------------------0            2026-08-07 09:10:35.1368+01    NULL                   NULL1            2026-08-07 09:13:56.142558+01  NULL                   NULL2            2026-08-07 09:21:12.625364+01  NULL                   NULL3            2026-08-07 09:24:15.991166+01  NULL                   NULL4            2026-08-07 09:42:10.67807+01   Article demonstration  Up to date area to Yorkshire for buyer 1002duck D

The newest snapshot ought to now embody the writer and message.

Notice that DuckLake offers ACID transactions with snapshot isolation. A profitable BEGIN–COMMIT block produces one snapshot containing all of the adjustments within the transaction. If the transaction is rolled again, none of these adjustments turns into seen.

Evolving a desk schema

We are able to additionally alter the desk with out rewriting our authentic supply file. DuckLake makes use of area identifiers to trace columns and helps appropriate schema adjustments with out requiring each present Parquet file to be rewritten. Let’s say we need to add a brand new column known as customer_status containing a default worth.

duck D ALTER TABLE customer_lake.clients       ADD COLUMN customer_status VARCHAR DEFAULT 'lively';

Examine the brand new schema:

duck D DESCRIBE customer_lake.clients;column_name      column_type  null  key   default   additional---------------  -----------  ----  ----  --------  -----customer_id      INTEGER      YES   NULL  NULL      NULLcustomer_name    VARCHAR      YES   NULL  NULL      NULLarea           VARCHAR      YES   NULL  NULL      NULLcustomer_status  VARCHAR      YES   NULL  'lively'  NULL

Question the desk.

duck D SELECT *       FROM customer_lake.clients;customer_id  customer_name  area          customer_status-----------  -------------  --------------  ---------------1003         Globex         Glasgow         lively1004         Initech        Manchester      lively1001         Acme Ltd       Larger London  lively1002         Northwind      Yorkshire       lively

Each present row ought to have a customer_status of lively.

At this level, now we have demonstrated the principal DuckLake options regionally:

• Managed Parquet storage.

• SQL queries.

• Updates.

• Transactions.

• Commit info.

• Snapshots.

• Time journey.

• Schema evolution.

Coping with cloud primarily based knowledge recordsdata

Not all knowledge you’re employed with might be native, actually for knowledge lakes the alternative is often true. Many of the knowledge in enterprise knowledge lakes might be held in a single kind or one other of cloud storage. In order that’s what we’ll have a look at subsequent.

Our buyer reference knowledge is managed by DuckLake on our native laptop. We’ll assume that order knowledge is produced by one other system and delivered to Amazon S3.

The S3 file will comprise:

order_id | customer_id | order_date | amount | unit_price---------+-------------+------------+----------+-----------5001     | 1001        | 2026-07-01 | 4        | 29.505002     | 1002        | 2026-07-02 | 2        | 74.005003     | 1001        | 2026-07-03 | 5        | 19.995004     | 1004        | 2026-07-04 | 3        | 44.505005     | 1003        | 2026-07-05 | 1        | 125.00

We’ll create the file regionally, add it after which question the S3 model. You’ll want the AWS CLI device for this so ensure you’ve put in that should you’re following alongside.

Create the orders Parquet file

Return to the DuckDB session. Should you closed it, reopen the database from the venture listing, re-attach the DuckLake and run this command from the DuckDB CLI.

duck D COPY (    SELECT *    FROM (        VALUES            (5001, 1001, DATE '2026-07-01', 4,  29.50),            (5002, 1002, DATE '2026-07-02', 2,  74.00),            (5003, 1001, DATE '2026-07-03', 5,  19.99),            (5004, 1004, DATE '2026-07-04', 3,  44.50),            (5005, 1003, DATE '2026-07-05', 1, 125.00)    ) AS orders(        order_id,        customer_id,        order_date,        amount,        unit_price    ))TO 'knowledge/orders.parquet'(FORMAT PARQUET);

Examine the file knowledge:

duck D SELECT *       FROM read_parquet('knowledge/orders.parquet');order_id  customer_id  order_date  amount  unit_price--------  -----------  ----------  --------  ----------5001      1001         2026-07-01  4         29.505002      1002         2026-07-02  2         74.005003      1001         2026-07-03  5         19.995004      1004         2026-07-04  3         44.505005      1003         2026-07-05  1         125.00

The file has been created regionally, we simply must add it to an acceptable bucket on S3. Open one other terminal within the venture listing and run:

C:Usersthomaducklake-demodata>cd C:Usersthomaducklake-demoC:Usersthomaducklake-demo>aws s3 cp dataorders.parquet s3://my-bucket/supply/orders.parquetadd: dataorders.parquet to s3://my-bucket/supply/orders.parquet

Notice, I’ve modified my bucket identify within the above command for safety and privateness causes.

For DuckDB to learn knowledge on S3 we have to set up one other couple of extensions. Return to the DuckDB immediate and run:

duck D INSTALL httpfs;duck D LOAD httpfs;

Subsequent, create a brief DuckDB secret. I’m utilizing my default AWS profile that comprises my credentials to connect with AWS. Select whichever area you need. I’m utilizing eu-west-2.

duck D CREATE OR REPLACE SECRET s3_credentials (           TYPE s3,           PROVIDER credential_chain,           CHAIN 'config',           REGION 'eu-west-2'       );Success-------true

This secret exists for the present DuckDB session. It comprises the credentials resolved by the AWS SDK relatively than exposing them within the SQL assertion.

Now we must always have the ability to question the distant Parquet file:

duck D SELECT *       FROM read_parquet(           's3://my-bucket/supply/orders.parquet'       );order_id  customer_id  order_date  amount  unit_price--------  -----------  ----------  --------  ----------5001      1001         2026-07-01  4         29.505002      1002         2026-07-02  2         74.005003      1001         2026-07-03  5         19.995004      1004         2026-07-04  3         44.505005      1003         2026-07-05  1         125.00

Utilizing the S3 knowledge with our DuckLake

At this stage now we have two most important choices for becoming a member of our distant knowledge to our present DuckLake. 

1/ We are able to maintain the DuckLake knowledge and S3 knowledge separate and simply be part of them utilizing SQL like this.

duck D SELECT           o.*,           c.*       FROM read_parquet(           's3://my-bucket/supply/orders.parquet'       ) AS o       LEFT JOIN customer_lake.most important.clients AS c           ON o.customer_id = c.customer_id;order_id  customer_id  order_date  amount  unit_price  customer_id  customer_name  area          customer_status--------  -----------  ----------  --------  ----------  -----------  -------------  --------------  ---------------5005      1003         2026-07-05  1         125.00      1003         Globex         Glasgow         lively5004      1004         2026-07-04  3         44.50       1004         Initech        Manchester      lively5003      1001         2026-07-03  5         19.99       1001         Acme Ltd       Larger London  lively5002      1002         2026-07-02  2         74.00       1002         Northwind      Yorkshire       lively5001      1001         2026-07-01  4         29.50       1001         Acme Ltd       Larger London  lively

2/ We are able to add the S3 knowledge file to our present DuckLake and subsequent adjustments to the orders DuckLake desk can be tracked regionally, similar to what occurs with the native clients knowledge.

duck D CREATE TABLE customer_lake.orders AS       SELECT *       FROM read_parquet('s3://my-bucket/supply/orders.parquet');duck D SELECT           o.order_id,           c.customer_id       FROM customer_lake.orders AS o       LEFT JOIN customer_lake.clients AS c           ON o.customer_id = c.customer_id;order_id  customer_id--------  -----------5003      10015002      10025001      10015005      10035004      1004

Abstract

We lined so much on this article however it is best to now have a deeper understanding of information lakes usually and the way DuckLake is completely different from applied sciences you will have heard about earlier than, like Iceberg, Delta and Hudi. 

We started with one native Parquet file and queried it immediately utilizing DuckDB. That required no database server and no ingestion course of.

We then imported the information into DuckLake. The managed desk might be up to date with SQL, modified inside transactions and queried at earlier snapshots. We additionally modified its schema with out altering the unique supply file.

Solely after establishing these native options did we add distant knowledge. DuckDB learn an orders file on AWS S3, joined it to the native DuckLake desk and materialised the outcome as one other managed DuckLake desk.

The instance exhibits the boundary between the 2 instruments. DuckDB is the engine that reads recordsdata and executes SQL. DuckLake offers {the catalogue} and transaction mannequin that turns Parquet recordsdata into maintained lakehouse tables.

One necessary query you might need is: Why use DuckLake at all around the established gamers in knowledge lake applied sciences? 

The reply comes down to suit and prices. In case your knowledge processing necessities aren’t too onerous and DuckDB is already on the centre of your analytics stack, DuckLake offers transactions, snapshots and schema evolution over Parquet via a well-recognized SQL catalogue. For groups that worth a light-weight, SQL-native lakehouse, utilizing DuckLake might be a no brainer. Like-wise, if in case you have prices constraints, that is in all probability your best choice too because it’s virtually free.

Should you’re operating an enterprise grade knowledge lake then, positive, proprietary and open-source desk codecs (Iceberg, Delta and so forth…) supplied by firms like Snowflake, DataBricks and others like them are apparent decisions. These are costly choices although. 

A system arrange round DuckDB and DuckLake might be accomplished for nearly zero price. If it doesn’t scale, throw it away. All you misplaced was a little bit of of your time.