DuckLake The Definitive Guide (for Raymond Rhine) (Matt Martin, Alex Monahan) (z-library.sk, 1lib.sk, z-lib.sk)

Page 1

(This page has no text content)

Page 2

MotherDuck

Page 3

(This page has no text content)

Page 4

DuckLake: The Definitive Guide Building Next-Generation Lakehouses with SQL- Native Table Formats With Early Release ebooks, you get books in their earliest form—the author’s raw and unedited content as they write—so you can take advantage of these technologies long before the official release of these titles. Matt Martin and Alex Monahan

Page 5

DuckLake: The Definitive Guide by Matt Martin and Alex Monahan Copyright © 2027 O’Reilly Media, Inc. All rights reserved. Published by O’Reilly Media, Inc., 141 Stony Circle, Suite 195, Santa Rosa, CA 95401. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (https://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Aaron Black Development Editor: Gary O’Brien Production Editor: Aleeya Rahman Interior Designer: David Futato Interior Illustrator: Kate Dullea January 2027: First Edition Revision History for the Early Release 2026-04-01: First Release See https://oreilly.com/catalog/errata.csp?isbn=9798341673571 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. DuckLake: The Definitive Guide, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the authors and do not represent the publisher’s views. While the publisher and the authors have

Page 6

used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the authors disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights. This work is part of a collaboration between O’Reilly and MotherDuck. See our statement of editorial independence. 979-8-341-67354-0

Page 7

Brief Table of Contents (Not Yet Final) Chapter 1: Rethinking the Lakehouse (available) Chapter 2: Getting Started with DuckLake (unavailable) Chapter 3: DuckLake Architecture Deep Dive (unavailable) Chapter 4: Advanced Features and Capabilities (unavailable) Chapter 5: Performance Optimization (unavailable) Chapter 6: Integration and Migration (unavailable) Chapter 7: Operations and Production Readiness (unavailable) Chapter 8: Real-World Use Cases and Patterns (unavailable) Chapter 9: Building Applications with DuckLake (unavailable)

Page 8

Chapter 1. Rethinking the Lakehouse A NOTE FOR EARLY RELEASE READERS With Early Release ebooks, you get books in their earliest form—the author’s raw and unedited content as they write—so you can take advantage of these technologies long before the official release of these titles. This will be the 1st chapter of the final book. Please note that the GitHub repo will be made active later on. If you’d like to be actively involved in reviewing and commenting on this draft, please reach out to the editor at gobrien@oreilly.com. The Pain Points of Today’s Lakehouses Imagine setting up a lakehouse attached to a cloud object store in less than a minute. No Apache Spark, no Java, no catalog wiring, no distributed architectural management. Just a couple lines of SQL and you are ready. It might sound absurdly optimistic, but it is not. Ducklake makes this a reality and here’s the proof. With just two lines of SQL, you will have a Ducklake lakehouse wired up to GCS ready to make and manage tables: CREATE OR REPLACE SECRET gcs_creds (TYPE GCS, KEY_ID getenv('GCS_KEY'), SECRET getenv('GCS_SECRET')); ATTACH OR REPLACE 'ducklake:gcs_wh.ducklake' AS gcs_wh (DATA_PATH getenv('GCS_WHS_PATH'));

Page 9

If you have built lakehouses with other formats, then the simplicity illustrated here might be hard to believe. Why does DuckLake take a few lines to code and setup, while Apache Iceberg and Delta Lake require pages of configuration? The answer boils down to a core architectural decision - where the metadata lives. In DuckLake, the metadata is stored in a relational database optimized for indexed, low-latency lookups. The object store holds only the data files. This approach isn’t really a wild one; it’s a methodical and intentional decision. It uses tried-and-true technologies like object storage, PostgreSQL, and DuckDB. DuckLake is as simple as possible, but no simpler - it can still scale to petabyte-sized workloads. In the case of the other lakehouses, metadata lives in the object store along with the data. You might say “Well so what? Object stores are highly durable and scalable”. And you are right. Amazon S3 is famous for its 11 9’s of durability.1 If you were ever to prove to AWS that they “lost” a file in S3, they might give you a trophy. But these object stores are not designed for low latency access to thousands of small files; they excel at large file reads, not high fan-out of small metadata file scans. To understand why this is an issue, we need to look at an actual example and see what transpires. The following is a simple snippet of Spark code that builds an Iceberg table and inserts a single row: sql = f""" CREATE TABLE IF NOT EXISTS {catalog_name}.{namespace}.orders ( order_id BIGINT, order_date DATE, customer_id BIGINT, total_amount DOUBLE ) USING ICEBERG spark.sql(sql) spark.sql(f"insert into {catalog_name}.{namespace}.orders values (1, current_date(), 1001, 250.75)")

Page 10

After this is executed, let’s run a tree command in our terminal to see what files Iceberg created for the table: (ducklake-definitive-guide) orders % tree . ├── data │ └── 00000-0-069cd534-d44f-4b2b-a9f6-3fdd16dcbed9-0-00001.parquet └── metadata ├── 02611d41-d398-48f4-8a24-9ecb9e7524d6-m0.avro ├── snap-6774179103726272682-1-02611d41-d398-48f4-8a24- 9ecb9e7524d6.avro ├── v1.metadata.json ├── v2.metadata.json └── version-hint.text Two simple operations to create a table and insert a row create five metadata files. To be fair, the last metadata file version-hint.text is a fast lookup file that points to the most recent snapshot, which in our case is v2.metadata.json. But why did Iceberg create those other four metadata files? In its simplest form, every Iceberg transaction that commits a snapshot will produce, at a minimum, three metadata artifacts: A vN.metadata.json file that captures the table’s logical definition and current state, including schema, partition spec, table properties, and the list of snapshots. Each commit produces a new metadata file; the latest one represents the table’s current view. A snap-*.avro file (the manifest list) that belongs to a specific snapshot and enumerates the manifest files included in that snapshot, along with high-level summary statistics such as files added/removed and records added/removed. One or more *-m*.avro files (the manifest files) that describe the actual data files referenced by the snapshot. Each manifest entry includes the data file path, record count, partition values (if applicable), and column-level statistics such as min/max values used for query planning and file pruning.

Page 11

Delta Lake follows a different implementation but a similar pattern: a growing log of metadata entries stored as files in the object store. At a small scale, this metadata design works well. At a large scale though, it creates a choke point on the metadata and query planning. To help better understand the growth of Iceberg and Delta Lake metadata files in an object store for a table, we can apply this simple scaling equation where N represents a transaction: Iceberg: 1 + 3N Delta Lake: 1 + N Now let’s consider a real world scenario. You have recently built out a new application logging analytics lakehouse using Iceberg. You implemented both streaming for near-real time update information and batch to handle overnight corrections. This is known as the modern lambda architecture. Your team marvels at your expertise. The lakehouse is humming along just fine. Fast forward six months and 50,000,000 logs streamed in, and you are called into a war room on the weekend. Simple read and write operations that used to take a few seconds are now taking more than 30. But how can this be? You followed best practices. What is causing such a degradation? Well, over those six months of running, your Iceberg warehouse has produced roughly 150,000,000 metadata files. Nothing “broke”. The warehouse acted as designed. Query planning just involves way more remote file reads now. The key point to understand here is that object stores can have very high throughput, but at the cost of high latency. They are not optimized for this type of access pattern (reading a lot of metadata files). DuckLake, however, is optimized for this type of access pattern, because it uses a database to lookup and manage the metadata. Object stores are built to go incredibly fast on columnar data files such as Parquet, not high fan-out metadata traversing. In contrast, transactional databases have spent more than 40

Page 12

years optimizing specifically for small, frequent, low latency reads and writes - they are excellent at them! Now, if you consider our original example of setting up DuckLake and wiring it to GCS, here’s what the equivalent setup would be for Iceberg and Spark: # Iceberg Runtime stuff spark_version = os.getenv("SPARK_VERSION", "3.5") scala_version = os.getenv("SCALA_VERSION", "2.12") iceberg_version = os.getenv("ICEBERG_VERSION", "1.7.0") iceberg_package = f"org.apache.iceberg:iceberg-spark-runtime- {spark_version}_{scala_version}:{iceberg_version}" # Define the Iceberg warehouse path warehouse_path = f"gs://{gcs_bucket}/icehouse" local_jar_path = "./jars/gcs-connector-3.0.4-shaded.jar" return SparkSession.builder \ .appName("local_spark_gcs") \ .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions") \ .config(f"spark.sql.catalog.{catalog_name}", "org.apache.iceberg.spark.SparkCatalog") \ .config(f"spark.sql.catalog.{catalog_name}.type", "hadoop") \ .config(f"spark.sql.catalog.{catalog_name}.warehouse", warehouse_path) \ .config("spark.jars.packages", iceberg_package) \ .config("spark.jars", local_jar_path) \ .config("spark.hadoop.google.cloud.auth.service.account.enable", "false") \ .config("spark.hadoop.fs.gs.impl", "com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystem") \ .config("spark.hadoop.fs.AbstractFileSystem.gs.impl", "com.google.cloud.hadoop.fs.gcs.GoogleHadoopFS") \ .config("spark.driver.host", "localhost") \ .config("spark.driver.bindAddress", "127.0.0.1") \ .config("spark.hadoop.google.cloud.auth.type", "APPLICATION_DEFAULT") \ .getOrCreate()

Page 13

Again, your eyes are not playing tricks on you. Iceberg and Spark require many more knobs to turn and configure. And this, in turn, adds a significant cognitive load onto the data engineer. What you are seeing is the nuts and bolts for getting a distributed architecture such as Spark up and running, and bootstrapped to a cloud object store. Ducklake flips this script nearly 180 degrees, by making the setup dramatically simpler, and with way fewer knobs for the user to worry about. NOTE Matt here. I can personally say that the first time I got a Ducklake lakehouse running on GCS, I was floored, nearly jaw dropped. I could not believe just how simple it was. I realized that Ducklake put the focus back on solving the real business problems and got us out of the business of managing and troubleshooting complex distributed query engines. Enough on that for now! Let’s dig deeper. Metadata Performance and Concurrency Challenges So far, we have barely scratched the surface of what DuckLake is; usually, a good bridge is a side-by-side comparison of an existing tech stack vs. the new one. Figure 1-1 is a side-by-side comparison of Apache Iceberg’s metadata and data architecture vs. DuckLake.

Page 14

Figure 1-1. A side-by-side comparison of Apache Iceberg and Ducklake catalog and file architecture

Page 15

As you can see, Iceberg generates a lot of metadata files to maintain its ability to be flexible and time travel. DuckLake, on the other hand, manages metadata in a database rather than a cloud object store, eliminating the metadata overhead that Iceberg carries with it. But what does this overhead mean in terms of actual DML operations, such as read/write? Let’s break it down. Read Operations For Iceberg, a read operation involves 4 separate round trip calls from the query engine, just for metadata alone: 1. Query the catalog to get the latest snapshot ~ 1ms 2. Query all metadata files (1 to n queries to object store) ~ 100ms 3. Query all manifest list files (1 to n queries to object store) ~ 100ms 4. Query all manifest files (1 to n queries to object store) ~ 100ms 5. Query the parquet data files themselves (1 to n queries to object store) ~ variable As a result, the fastest query on Iceberg is about half a second at a minimum. Write Operations Write operations suffer from a similar overhead problem. For Iceberg, writing a single record or batch of records will require several round trips: 1. Write the new data files 2. Write the new manifest file set 3. Write the new manifest list file set 4. Write the new metadata file 5. Update the Iceberg catalog to point to the latest metadata file

Page 16

This means even a single write can take roughly half a second. Now consider what happens when applications attempt to write hundreds of records concurrently. You might assume the solution is to elastically scale more workers and process writes in parallel, but Iceberg’s optimistic concurrency model makes that ineffective. To understand why, it helps to understand optimistic concurrency control (OCC). OCC was designed to avoid a classic bottleneck in traditional relational database management systems (RDBMSs), where increasing read or write scope leads to escalating locks - first rows, then pages, and sometimes entire tables - forcing other transactions to block until the lock is released. OCC takes a different approach. Queries read a consistent snapshot of the data as it existed when the query began. In-flight, uncommitted changes are invisible, and no locks are held during reads. This improves read concurrency and scalability, which is why OCC has become the default model in many modern databases and cloud data warehouses, including platforms like Google BigQuery. But for write operations in lakehouses, OCC is actually painful because it needs to guarantee ACID. If a current query is writing its metadata files and another concurrent transaction completes, then the current query will have to cancel, rollback, and retry its transaction to maintain Iceberg’s OCC posture. Thus, more worker nodes won’t solve the problem - this is a fundamental limitation of the architecture. DuckLake also uses the OCC model. So you might think “Well, I guess it suffers from the same problem?” Actually, it doesn’t. Remember, DuckLake has the benefit of managing its metadata inside a database; therefore, its transactional throughput will be orders of magnitude faster than Iceberg’s. How much faster do you ask? Ducklake metadata transactions complete in about 30ms, vs. Iceberg’s minimum of 300ms, a dramatic 90% improvement in latency. Now that we have addressed the read and write complexity of the modern lakehouse, we need to look at one more fundamental issue: the small file problem.

Page 17

The Small File Problem The small file problem is like compounding interest, but in a very bad way; over time as a lakehouse table handles many row level changes, each change generates one or more new metadata files (three + in the case of Iceberg!) as well as more data files, as previously discussed. Eventually, this problem manifests as poor query performance due to the sheer time it takes for Iceberg to list all the metadata files it needs to consider for a query. In an object store, reading many small files is much slower vs. reading a few larger files; you will hit a latency floor with these read operations, but once the file is cracked open, you get a good read speed of roughly 8MB per second; considering most metadata files are tiny (a few KB in size), reading a smaller number of metadata files will in many cases drastically improve the query planning performance. There are a few methods to address the small file problem, but each has issues of its own: Write less often Queue up and batch your writes. This will lower the number of files your Iceberg table needs to manage over time; however, you will need infrastructure in place to support the buffering and batching of your writes such as Apache Kafka or Flink. TIP Unless it’s a hard requirement, don’t ever try to use your application logic to write a single record to Iceberg via a SQL insert statement; this will result in a single new set of metadata files generated for that one row. Kafka and Flink are much better at buffering up chunks of rows before flushing the buffer and committing to the table. Use Compaction

Page 18

Compacting small files effectively combines numerous smaller metadata files into larger consolidated ones. This means that you are writing the metadata multiple times. Plus, when these compaction jobs run, they will be competing with other writes that are going on concurrently in your workload. Lastly, each time data is compacted, you lose flexibility on query time travel due to the metadata files getting consolidated. Before we get going any further, does the compaction of small files into larger ones sound somewhat familiar? If you grew up in the database world, this sounds a lot like rebuilding/reorganizing/defragmenting an index. We have not really solved the problem on this one yet; we’ve just shifted where the problem takes place. Innovations and Remaining Gaps of the Lakehouse The lakehouse has a very logical historical progression. In a nutshell, we can think of the lakehouse evolving from these 3 major milestones in the data world: Teradata introduces the formal data warehouse (1984). The introduction of the data lake and cloud object store with Hadoop and schema-on-read becomes the compute strategy (2010). The data lakehouse is born (2021). The data warehouse held up for a very long time; however, its tightly coupled storage and compute created a glaring bottleneck. Once data growth started to outpace the physical hardware storage capacity of these data warehouses, we ran into a problem of scaling quickly and elastically. The data lake aimed to solve the data scaling problem by decoupling storage and compute, placing the storage on object stores. Object stores can

Page 19

scale pretty much limitlessly and deliver another value proposition that traditional data warehouses did not account for: dealing with semi- structured and unstructured data. Data lakes also held up for a reasonable amount of time, but had some major drawbacks and eventually devolved into what people would call data swamps (i.e., endless copies of copies of the same data, with poor governance controls). Then, in 2021, the data lakehouse arrived to provide the best of both data warehouses and data lakes: deliver warehouse speed and governance, but provide the flexibility and limitless scale of the cloud object store. But how would they do that? What would the compute and storage look like? The lakehouse paradigm has an ambitious task: to handle the planetary scale of data, while maintaining strong governance controls and the performance you would expect from a data warehouse. In order to facilitate these requirements, two new table specifications were created: Delta Lake The first popular lakehouse table spec released. Developed by Databricks, Delta Lake became a part of the Linux Foundation in October 2019. Iceberg Developed in-house by Netflix in 2017 as a means to combat all the data they had to wrangle across the organization for their analytics and the limitations they were hitting with Apache Hive for large data lakes. It was adopted as a top- level Apache project in 2020. Once these table specifications gained broad adoption and proved that reliable, ACID-compliant tables could exist directly on object storage, the architectural pattern that we now call the lakehouse emerged over the following years. Both Delta Lake and Iceberg paved the way for the next half-decade; however, as history has shown with new tech, we eventually hit some

Page 20

interesting edge cases that manifest themselves into larger problems, such as the ones we discussed earlier with Iceberg. Ducklake: A Database-First Architecture Ducklake builds on the core promise of the lakehouse, but with two major added benefits: Ease of use Low latency If you recall our earlier code snippet, implementing a Ducklake connected to a cloud object store required only two lines of SQL. It didn’t require a distributed architecture, special software, or complex configurations. As Steve Jobs used to say, “it just works.” DuckLake is an open source specification, with its primary implementation in DuckDB and a secondary implementation in Apache Spark. It is both a lakehouse format and a lakehouse catalog in one. The DuckLake architecture has three main components: storage, the metadata catalog, and compute (Figure 1-2). Each layer has multiple easy options to choose from.