Constructing a Information Lakehouse with DuckDB and DuckLake

A few years in the past, if you happen to needed to retailer massive quantities of information that could possibly be sensibly queried, a database like Oracle or Postgres and such was your essential selection. Certain, there have been different choices just like the mainframe methods from corporations equivalent to ICL and IBM, however they had been very expensive and locked you in to a selected producer. 

The following large advance in knowledge storage was the info warehouse. This introduced info from separate operational methods right into a central repository designed particularly for reporting and historic evaluation. Its essential benefits had been sooner 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 newest advance in knowledge storage is the emergence of the info 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 flawed; corporations like Databricks, Snowflake, and the massive cloud suppliers like AWS are vying to extract as a lot money as attainable from their prospects to make the administration, operating, and growth of information lakes as clean as attainable.

However fact be advised, you’ll be able to go a good distance towards growing an efficient knowledge lake for nearly zero value 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 industrial affiliation with any of the methods or their creators talked about on this article.

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

Information lakes of just about every type 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 data 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 info made by way of SQL, like inserts, updates, deletes and schema adjustments, are tracked. This metadata permits a lakehouse system to help environment friendly queries, transactions, schema evolution and time journey with out modifying the underlying Parquet recordsdata straight.

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 group behind DuckDB and launched simply over a yr in the past. It turned the standard concept of how an information lake file system needs to be structured on its head, managing the metadata in a relational database as a substitute 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 file 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 effectively suited to selective queries that scan solely a manageable portion of the lake, however multi-user workloads that repeatedly course of tens or a whole bunch of terabytes would require a beefier database like Postgres and sure a distributed question engine equivalent to Spark.

A couple 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 pc 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. 

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

By the tip, we may have demonstrated find out how 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 current 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 costs should still apply. Please delete it whenever you’re completed to keep away from any unwelcome payments. Be aware: if you happen to don’t wish to use the cloud for the second a part of the info instance, it’s nice to make use of native storage once more.

Creating our challenge construction

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

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

The recordsdata have completely different functions:

• prospects.parquet is our authentic native supply file.

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

• metadata.ducklake accommodates the DuckLake catalogue.

• lake/ accommodates Parquet recordsdata managed by DuckLake.

• duckdb.dev holds the DuckDB session database.

On Home windows PowerShell, run:

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

Putting in DuckDB

DuckDB is out there as a command-line program for Home windows. All strategies to put in DuckDB are documented on the official DuckDB set up web 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 is just not chargeable 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 atmosphere 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 test 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

You need to now see the DuckDB immediate:

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

From the DuckDB immediate, run:

INSTALL ducklake;LOAD ducklake;

Create the native “prospects” Parquet file

We are going to 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 prospects(               customer_id,               customer_name,               area           )       )       TO 'knowledge/prospects.parquet'       (FORMAT PARQUET);duck D

This creates the file knowledge/prospects.parquet. At this level, the info exists as an unusual file, not a DuckDB or DuckLake desk. So, we will question the Parquet file straight like this.

duck D SELECT *       FROM read_parquet('knowledge/prospects.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, however it doesn’t flip the file right into a transactional desk. Parquet is an immutable file format from the standpoint of regular SQL operations. We will’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 info path can be recorded within the catalogue, so it doesn’t need to be provided once more when reconnecting later.

We will see the databases connected to the present DuckDB session with this command:

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

Now we will import our prospects file into DuckLake to create a managed desk.

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

There are actually two copies of the info.

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

The unique file hasn’t been modified, and we will question the brand new desk identical to we’d every other database desk.

duck D SELECT *       FROM customer_lake.prospects;┌─────────────┬───────────────┬────────────┐│ 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 vital variations are behind the scenes. For instance, we will listing the bodily recordsdata utilized by the desk:

duck D CALL ducklake_flush_inlined_data(           'customer_lake',           schema_name => 'essential',           table_name => 'prospects'       );┌─────────────┬────────────┬──────────────┐│ schema_name │ table_name │ rows_flushed ││   varchar   │  varchar   │    int128    │├─────────────┼────────────┼──────────────┤│ essential        │ prospects  │            4 │└─────────────┴────────────┴──────────────┘duck D .mode lineduck D FROM ducklake_list_files(           'customer_lake',           'prospects'       );                 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 prospects desk and the Parquet recordsdata used to retailer it.

Analyzing 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                │├──────────────┼───────────────────────────────────────┤│ essential         │ ducklake_column                       ││ essential         │ ducklake_column_mapping               ││ essential         │ ducklake_column_tag                   ││ essential         │ ducklake_data_file                    ││ essential         │ ducklake_delete_file                  ││ essential         │ ducklake_file_column_stats            ││ essential         │ ducklake_file_partition_value         ││ essential         │ ducklake_file_variant_stats           ││ essential         │ ducklake_files_scheduled_for_deletion ││ essential         │ ducklake_inlined_data_1_1             ││ essential         │ ducklake_inlined_data_1_2             ││ essential         │ ducklake_inlined_data_2_3             ││ essential         │ ducklake_inlined_data_3_5             ││ essential         │ ducklake_inlined_data_tables          ││ essential         │ ducklake_inlined_delete_1             ││ essential         │ ducklake_macro                        ││ essential         │ ducklake_macro_impl                   ││ essential         │ ducklake_macro_parameters             ││ essential         │ ducklake_metadata                     ││ essential         │ ducklake_name_mapping                 ││ essential         │ ducklake_partition_column             ││ essential         │ ducklake_partition_info               ││ essential         │ ducklake_schema                       ││ essential         │ ducklake_schema_versions              ││ essential         │ ducklake_snapshot                     ││ essential         │ ducklake_snapshot_changes             ││ essential         │ ducklake_sort_expression              ││ essential         │ ducklake_sort_info                    ││ essential         │ ducklake_table                        ││ essential         │ ducklake_table_column_stats           ││ essential         │ ducklake_table_stats                  ││ essential         │ ducklake_tag                          ││ essential         │ 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, be sure you swap again your attachment:

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

Updating a DuckLake desk

Suppose we wish to change London with Larger London in our prospects desk for buyer 1001. It’s simply common SQL.

duck D UPDATE customer_lake.prospectsSET area = 'Larger London'WHERE customer_id = 1001;duck D SELECT *       FROM customer_lake.prospects       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 will reveal that by querying it once more:

duck D SELECT *       FROM read_parquet('knowledge/prospects.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 info lake, together with its schemas, tables and underlying knowledge recordsdata. Snapshots retailer metadata about every model quite than creating a whole copy of the info. We will listing 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

You need to see separate snapshots for operations equivalent to creating tables, knowledge inserts, deletes and updates. Be aware that an replace is handled as a delete adopted by an insert. Using snapshots has one very helpful aspect impact. It means we will return in time and question desk contents as they had been sooner or later up 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. Trying on the above snapshot question we will glean that snapshot_id = 1 ought to give us that info, so we will use that identifier within the following question.

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

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

duck D SELECT *       FROM customer_lake.prospects       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 dependent upon whenever you ran the replace. 

Snapshots could be a life-saver. Let’s say we inadvertantly delete our buyer data with ids 1002 and 1004.

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

We will 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.prospects       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 prospects desk and our knowledge is recovered.

duck D insert into customer_lake.prospects       SELECT *              FROM customer_lake.prospects              AT (VERSION => 1)              WHERE customer_id in (1002,1004);duck D choose * from customer_lake.prospects;┌─────────────┬───────────────┬────────────────┐│ 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, however it 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.prospects       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 most recent snapshot ought to now embody the writer and message.

Be aware that DuckLake supplies 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 will additionally alter the desk with out rewriting our authentic supply file. DuckLake makes use of area identifiers to trace columns and helps suitable schema adjustments with out requiring each current Parquet file to be rewritten. Let’s say we wish to add a brand new column known as customer_status containing a default worth.

duck D ALTER TABLE customer_lake.prospects       ADD COLUMN customer_status VARCHAR DEFAULT 'energetic';

Examine the brand new schema:

duck D DESCRIBE customer_lake.prospects;column_name      column_type  null  key   default   further---------------  -----------  ----  ----  --------  -----customer_id      INTEGER      YES   NULL  NULL      NULLcustomer_name    VARCHAR      YES   NULL  NULL      NULLarea           VARCHAR      YES   NULL  NULL      NULLcustomer_status  VARCHAR      YES   NULL  'energetic'  NULL

Question the desk.

duck D SELECT *       FROM customer_lake.prospects;customer_id  customer_name  area          customer_status-----------  -------------  --------------  ---------------1003         Globex         Glasgow         energetic1004         Initech        Manchester      energetic1001         Acme Ltd       Larger London  energetic1002         Northwind      Yorkshire       energetic

Each current row ought to have a customer_status of energetic.

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 based mostly 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 type or one other of cloud storage. In order that’s what we’ll take a look at subsequent.

Our buyer reference knowledge is managed by DuckLake on our native pc. We are going to 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 are going to create the file regionally, add it after which question the S3 model. You’ll want the AWS CLI device for this so be sure you’ve put in that if you happen to’re following alongside.

Create the orders Parquet file

Return to the DuckDB session. Should you closed it, reopen the database from the challenge 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);

Verify 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 have to add it to an appropriate bucket on S3. Open one other terminal within the challenge 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

Be aware, I’ve modified my bucket title 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 accommodates 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 accommodates the credentials resolved by the AWS SDK quite than exposing them within the SQL assertion.

Now we must always be capable 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 essential choices for becoming a member of our distant knowledge to our current DuckLake. 

1/ We will 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.essential.prospects 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         energetic5004      1004         2026-07-04  3         44.50       1004         Initech        Manchester      energetic5003      1001         2026-07-03  5         19.99       1001         Acme Ltd       Larger London  energetic5002      1002         2026-07-02  2         74.00       1002         Northwind      Yorkshire       energetic5001      1001         2026-07-01  4         29.50       1001         Acme Ltd       Larger London  energetic

2/ We will add the S3 knowledge file to our current DuckLake and subsequent adjustments to the orders DuckLake desk can be tracked regionally, identical to what occurs with the native prospects 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.prospects AS c           ON o.customer_id = c.customer_id;order_id  customer_id--------  -----------5003      10015002      10025001      10015005      10035004      1004

Abstract

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

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

We then imported the info into DuckLake. The managed desk could possibly 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 end result as one other managed DuckLake desk.

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

One vital query you might need is: Why use DuckLake at everywhere in 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 supplies transactions, snapshots and schema evolution over Parquet by means of a well-known SQL catalogue. For groups that worth a light-weight, SQL-native lakehouse, utilizing DuckLake could possibly be a no brainer. Like-wise, if in case you have prices constraints, that is most likely the 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 on…) offered by corporations like Snowflake, DataBricks and others like them are apparent selections. These are costly choices although. 

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