Register tables in a catalog and query them from DuckDB

By the end of this chapter you can register an Iceberg table in a SQL catalog, attach a DuckLake catalog, open a Delta Lake table by its path, and run one DuckDB statement that joins a table from Iceberg with a table from DuckLake. You can also say what each catalog holds, and what happens to a table when its catalog and its data files disagree.

The earlier chapters opened each table by its own route: a catalog object for Iceberg, a file for DuckLake, a folder for Delta Lake. Every chapter uses the same table, wiki.pageviews_hourly, the hourly view counts of Wikimedia pages. The first table registered it in an Iceberg SQL catalog under the name local. This chapter explains what that name does, and what each format uses in its place. It also adds a second table, wiki.domains: a four-row list of domain codes and the language each one is in. It lives in the DuckLake catalog, and the join section joins it to the Iceberg pageviews.

What a catalog holds

A table format stores a table as data files plus metadata files. The metadata names the table's current state: its schema, its partition spec and, directly or through manifest files, the data files of its current snapshot or version. Each commit writes new metadata and moves the table to it. The catalog is the registry that maps a table's name to the place where its current metadata is. A reader that asks the catalog for wiki.pageviews_hourly gets a pointer to the metadata, and then reads the data files that the metadata lists.

The catalog holds pointers, not the rows. The Parquet files stay in the warehouse folder, and the catalog says where to start reading. Names have a second level as well: wiki is a namespace, and every Iceberg table name in this book is a namespace and a table name joined by a dot.

Choosing a catalog comes down to two questions: who writes the table, and what reads it. A table that one job writes and one engine reads can live in a file-based catalog and be found by its path. A table that several engines read needs a name each of them can resolve, which is what a SQL catalog or a service provides. We recommend one catalog for each table, and a record next to the table that names its catalog and its owner. A join across catalogs, as the join section shows, works in one session. It does not make the two catalogs one.

The three formats in this book use three different catalogs:

The Iceberg REST catalog is the other kind of catalog, a service instead of a file. Its REST catalog page describes it as a common API, specified with OpenAPI, for interacting with any Iceberg catalog, so that clients need one implementation for all of them. The same page says the protocol uses change-based commits, so the service can resolve conflicts and retry on its side, and that the catalog service writes the root metadata. This chapter runs no REST catalog. Each one needs a running service, and the examples here run offline with no service. The book does not compare the products that implement the protocol.

A catalog can hold more than names. Classifications and retention rules are table metadata too. Data Governance as Code covers them, and this book does not teach them.

Working rule: Before you write to a table, find out which catalog or path names it, and back up that catalog along with the data folder. Parquet files do not name a table. The catalog does, as the failure section shows.

Register a table in a SQL catalog

The pyiceberg SqlCatalog keeps its tables in a database. You give it a uri for that database and a warehouse for the folder where it writes data files and metadata. This book uses a SQLite file for the database and a local folder for the warehouse. The pyiceberg configuration page says the SQL catalog "requires a database for its backend", and that "SQLite is not built for concurrency, you should use this catalog for exploratory or development purposes". The examples here are development examples, so that limit does not change anything in them. A production catalog needs a database that several writers can use.

The next example opens the same catalog file a second time, with the same name, and reads the table through that second object. It then registers a second name for the table's metadata, and shows what a catalog object with a different name can see.

1"""Register an Iceberg table in a SQL catalog, then reach it from a second catalog object."""23from lib.lakehouse import (ICEBERG_ID, NAMESPACE, create_iceberg_table, new_warehouse,4                           open_catalog, read_cut_hour, safe_error, scan_arrow)56with new_warehouse() as wh:7    table = create_iceberg_table(wh)8    for hour in range(3):9        table.append(read_cut_hour(hour))1011    # A second object with the same catalog name sees the same tables.12    same = open_catalog(wh)13    loaded = same.load_table(ICEBERG_ID)14    print("rows read through the second object:", scan_arrow(loaded).num_rows)1516    # A loaded table is a snapshot of the catalog as it was when loaded.17    stale = same.load_table(ICEBERG_ID)18    table.append(read_cut_hour(3))19    print("rows in the loaded object after another append:", scan_arrow(stale).num_rows)20    print("rows after a reload:", scan_arrow(same.load_table(ICEBERG_ID)).num_rows)2122    # Register the same metadata file under a new name: a pointer, not a copy.23    same.create_namespace("archive")24    same.register_table("archive.pageviews_copy", table.metadata_location)25    print("rows in archive copy:", scan_arrow(same.load_table("archive.pageviews_copy")).num_rows)2627    # An object with another name does not see the namespace.28    try:29        open_catalog(wh, name="other").list_tables(NAMESPACE)30    except Exception as exc:31        print("other name:", safe_error(exc, wh))

rows read through the second object: 1499
rows in the loaded object after another append: 1499
rows after a reload: 1999
rows in archive copy: 1999
other name: NoSuchNamespaceError: Namespace does not exist: wiki

The second object reads 1499 rows of wiki.pageviews_hourly, the three hours that chapter 1 landed. pyiceberg stores each table under the catalog name you pass to SqlCatalog. An object with the same name finds the same tables.

The catalog and the warehouse folder are a pair. The catalog holds the locations of the metadata files, and those locations sit under the folder that the warehouse property names. A copy of the catalog file without the warehouse folder, or the folder without the file, gives you pointers to files that are not there. Back up both, and move both together.

Each process opens its own catalog object, and none shares the state of another. The example uses two objects in one process to show the effect that a second process would have.

A loaded table is a snapshot of the catalog as it was when it was loaded. The example keeps a table object from before a fourth hour is appended through the first object. That object still reads 1499 rows. A reload reads 1999. A process that loads a table at its start and reads it at its end reads the state from the start. It has to reload to see the commits made in between.

Working rule: Open a SQL catalog file with one name everywhere in your code, and reload a table before you read it when another writer may have committed since you loaded it. Keep SQLite for one writer at a time, and use a database built for concurrent writers before you run several.

Register a table from its metadata file

register_table(identifier, metadata_location) adds a name for an existing metadata file. The pyiceberg API page gives it as the way to register a table from its metadata, and says that passing overwrite=True replaces an entry that already exists. The example registers the table's metadata under archive.pageviews_copy, in a namespace it creates first. The archive name reads the same rows as the original table: 1999. Both names point at the same metadata, and the registration writes no data files.

A registered name is a second route to the same table, not a second table. Commits through one name are the commits of both, because both read the same metadata. Treat a second name as a read route, and not as a backup.

Working rule: Keep one writer name for each table. Register a metadata file under a second name only for a read you will remove afterwards, and pass overwrite=True only when you mean to replace that entry.

Attach a DuckLake catalog

DuckLake's catalog is a database file, and DuckDB attaches it with an ATTACH statement that starts with ducklake: and gives the file's path. The data path, the folder where DuckLake writes Parquet files, is stored in the catalog. The DuckLake connecting page says the data storage location only has to be given when a new DuckLake is created, and that the location is loaded from the catalog database after that. The helper connect_ducklake loads DuckLake into the DuckDB session with downloads off, and attach_ducklake runs the ATTACH for a catalog file under a folder.

The next example creates wiki.domains in a fresh DuckLake catalog, detaches it, and attaches the file again with no data path. It then lists the tables in the wiki schema of the file.

1"""Attach a DuckLake catalog file, then attach it again from the file alone."""23from lib.lakehouse import (DUCKLAKE_ALIAS, NAMESPACE, attach_ducklake, connect_ducklake,4                           create_ducklake_domains, new_warehouse, print_rows, sql_rows)56with new_warehouse() as wh:7    con = connect_ducklake(wh.con)8    create_ducklake_domains(con, wh.root)9    print("rows written:", sql_rows(con, f"SELECT count(*) FROM {DUCKLAKE_ALIAS}.{NAMESPACE}.domains")[0][0])1011    # Detach, then attach with no DATA_PATH: the catalog file stores the data path.12    con.execute(f"DETACH {DUCKLAKE_ALIAS}")13    attach_ducklake(con, DUCKLAKE_ALIAS, wh.root)14    print("tables in the catalog:")15    print_rows(sql_rows(con, f"""SELECT schema_name, table_name FROM duckdb_tables()16                                 WHERE database_name = '{DUCKLAKE_ALIAS}' AND schema_name = '{NAMESPACE}'"""))17    print("rows after re-attach:", sql_rows(con, f"SELECT count(*) FROM {DUCKLAKE_ALIAS}.{NAMESPACE}.domains")[0][0])

rows written: 4
tables in the catalog:
wiki	domains
rows after re-attach: 4

The four rows of wiki.domains are written, and 4 rows read back after the re-attach. The table list shows one table in the wiki schema, domains. The second attach needed no data path, because the file already held it. The listing comes from the catalog, not from the data folder. The catalog file holds the list of tables, and the data folder holds the bytes. Losing the first loses the names even when the second is intact, as the failure section shows.

The same connecting page says that DuckLake inlines small inserts into the catalog database, with a default limit of 10 rows. The helper this chapter uses sets the limit to 0, so every insert in these examples becomes a Parquet file in the data folder. Without that setting, a small table lives in the catalog file, and a copy of the data folder would not hold its rows. That is one reason the catalog file needs a backup of its own.

Working rule: Back up a DuckLake catalog file with the data folder, and copy the file while no session has it attached. Give the data path when you create a catalog, and attach later with the catalog file alone.

Find a table by path, with no catalog

A Delta Lake table in this book is a folder. It has no catalog, and nothing in this book's setup names it apart from its path. The deltalake package opens the folder with DeltaTable(path), and every read goes through that path. Delta counts versions from 0, so the three appends in the next example are versions 0, 1 and 2.

1"""Open a Delta Lake table by its path, with no catalog, and read it through QueryBuilder."""23from deltalake import DeltaTable, write_deltalake45from lib.lakehouse import new_warehouse, read_cut_hour, read_delta67with new_warehouse() as wh:8    path = str(wh.root / "delta" / "pageviews_hourly")9    for hour in range(3):10        write_deltalake(path, read_cut_hour(hour), mode="append")1112    table = DeltaTable(path)13    print("delta version:", table.version())14    print("rows at that version:", read_delta(path).num_rows)15    print("rows at version 0:", read_delta(path, version=0).num_rows)1617    # The path is the only name a Delta table has here: a wrong path is a missing table.18    try:19        DeltaTable(str(wh.root / "delta" / "pageviews_typo"))20        print("missing path: opened")21    except Exception as exc:22        print("missing path:", type(exc).__name__)

delta version: 2
rows at that version: 1499
rows at version 0: 499
missing path: TableNotFoundError

The table's current version is 2, and it reads 1499 rows. Version 0 holds 499 rows, the first hour alone. The rows here come through read_delta, which reads through deltalake's QueryBuilder and returns an Arrow table. The decisions at the end of this chapter say why the book reads Delta tables that way.

The path is the whole name, so a typo in it is a missing table. The example opens a folder that does not exist, and DeltaTable raises TableNotFoundError. The error names the path it tried, but only once the wrong path has been used, so the check belongs in the code that builds the path.

A path is a good name for a table that one job writes and a few jobs read. It needs no service and no setup. It has costs, though. A path does not say which namespace a table is in, so two tables with the same folder name in different places look identical to a reader. An engine that wants to find the table by name has nothing to look up. The Delta tables in this book are small and local, and the path is enough for them. A shared lake read by several engines needs a catalog for its Delta Lake tables, and this book does not teach one.

Working rule: Keep each Delta table's path in one place your code reads, such as a configuration value, and open the table once at the start of a job to confirm the path. A path that does not exist is an error, not an empty table.

Join tables from two catalogs

DuckDB can query an Arrow table that a library hands it, and a DuckLake table that is attached to the same session, in one statement. The Iceberg table leaves pyiceberg as an Arrow table through scan_arrow. DuckDB registers that table under a name, and the join reads wiki.domains through the attached catalog. The book does not load DuckDB's iceberg extension for this, so the Iceberg side is the Arrow table and nothing else.

1"""Join an Iceberg table, read through Arrow, with a DuckLake table in one DuckDB statement."""23from lib.lakehouse import (DUCKLAKE_ALIAS, NAMESPACE, connect_ducklake, create_iceberg_table,4                           create_ducklake_domains, new_warehouse, print_rows, read_cut_hour,5                           scan_arrow, sql_rows)67with new_warehouse() as wh:8    table = create_iceberg_table(wh)9    for hour in range(3):10        table.append(read_cut_hour(hour))11    con = connect_ducklake(wh.con)12    create_ducklake_domains(con, wh.root)1314    pageviews = scan_arrow(table)15    joined = f"FROM pageviews p JOIN {DUCKLAKE_ALIAS}.{NAMESPACE}.domains d ON p.domain_code = d.domain_code"16    print("joined rows:", sql_rows(con, f"SELECT count(*) {joined}", pageviews=pageviews)[0][0])17    print("rows with no language:", sql_rows(con, f"""SELECT count(*) FROM pageviews p18        LEFT JOIN {DUCKLAKE_ALIAS}.{NAMESPACE}.domains d ON p.domain_code = d.domain_code19        WHERE d.domain_code IS NULL""", pageviews=pageviews)[0][0])20    print("top three by views:")21    print_rows(sql_rows(con, f"""SELECT p.domain_code, d.language, p.page_title, p.views {joined}22        ORDER BY p.views DESC, p.domain_code, p.page_title, p.hour_utc LIMIT 3""", pageviews=pageviews, keep_order=True),23        keep_order=True)

joined rows: 1499
rows with no language: 0
top three by views:
en	English	Main_Page	64859
en	English	Main_Page	64564
en	English	Main_Page	61107

The join matches 1499 pageview rows to a language. The count of rows with no language is 0, so every domain code in the pageviews is in the four-row dimension. The top three rows by views are the same page, Main_Page on the English domain.

The join key is domain_code, a string on both sides. A join on a key that differs in case or in trailing spaces matches nothing: the joined-row count would drop to 0, and the count of rows with no language would equal the table's row count. Check the key on both sides, in the same session, before you trust a join.

The query works because both sides meet in one DuckDB session. It does not make the two tables one. Each keeps its own commits, and the Arrow table is a snapshot of the Iceberg table at the moment scan_arrow ran. A join over a table that another writer is changing reads whatever state was current at that moment.

We recommend a cross-catalog join for lookups like this one: a small dimension table that stays in its own catalog, read by a query over a large table. When one table appears in almost every query that reads another, copy it into the catalog those queries use. Chapter 9 copies a table between formats and checks each copy, and that check is the step to take before you move a table anywhere.

Working rule: Join across catalogs in one session only when the side you read as Arrow fits in memory. Choose its state before you read it, and count the rows that find no match, which should be zero.

What goes wrong in production

The symptoms are usually small: a count that stops moving, a namespace that is missing, or a read error on a table that was read before. Each case below starts from one of them.

Three failures in this chapter are shown by an example, so you can recognize them when they happen. A fourth is named and not shown.

The wrong catalog name. The last line of register-iceberg-table opens the same file under the name other. Listing the namespace then fails with NoSuchNamespaceError: Namespace does not exist: wiki. The table is still in the file. The SQL catalog scopes its tables by catalog name, so the second name sees an empty catalog. The message names the namespace, not the name, so the error looks like a missing schema. A job that builds its catalog name from configuration can fail this way after a small edit. You notice it at the first read, not at the write, so log the catalog name at the start of the job.

The stale pointer. A process that loads a table and reads it much later reads the state from when it loaded. register-iceberg-table showed 1499 rows where a reload shows 1999. Nothing fails. The job runs and reports fewer rows than the table holds. You notice it when two jobs disagree about a count, so keep the load next to the read.

The catalog file replaced by an empty one. The next example attaches a fresh, empty catalog file to the data folder of a live catalog, then runs ducklake_delete_orphaned_files with cleanup_all => true. The DuckLake cleanup page shows cleanup_all deleting all orphaned files, and dry_run listing them instead. It defines an orphan as a file that the catalog you run it against does not track. A fresh catalog tracks nothing, so every Parquet file in the folder is an orphan to it.

1"""Run cleanup_all against a fresh, empty DuckLake catalog that shares a data path with a live one."""23from lib.lakehouse import (DUCKLAKE_ALIAS, NAMESPACE, connect_ducklake, create_ducklake_domains,4                           new_warehouse, sql_rows)56CLEANUP = "CALL ducklake_delete_orphaned_files('fresh', cleanup_all => true{dry})"78def parquet_count(root):9    return len(list((root / f"{DUCKLAKE_ALIAS}_data").rglob("*.parquet")))1011with new_warehouse() as wh:12    con = connect_ducklake(wh.con)13    create_ducklake_domains(con, wh.root)14    print("parquet files, live catalog:", parquet_count(wh.root))1516    # A replacement catalog: a new, empty file that points at the same data folder.17    con.execute(f"DETACH {DUCKLAKE_ALIAS}")18    # Attached by hand: attach_ducklake would give fresh its own data folder, not the live one.19    con.execute(f"ATTACH 'ducklake:{wh.root}/fresh.ducklake' AS fresh (DATA_PATH '{wh.root}/{DUCKLAKE_ALIAS}_data/')")20    print("files the dry run lists:", len(sql_rows(con, CLEANUP.format(dry=", dry_run => true"))))21    con.execute(CLEANUP.format(dry=""))22    print("parquet files after cleanup_all:", parquet_count(wh.root))2324    # The live catalog still names the table, and its data files are gone.25    con.execute(f"ATTACH 'ducklake:{wh.root}/{DUCKLAKE_ALIAS}.ducklake' AS live")26    print("count through the live catalog:", sql_rows(con, f"SELECT count(*) FROM live.{NAMESPACE}.domains")[0][0])27    try:28        print("reading the live catalog:", len(sql_rows(con, f"SELECT domain_code, language FROM live.{NAMESPACE}.domains")))29    except Exception as exc:30        print("reading the live catalog:", type(exc).__name__)

parquet files, live catalog: 1
files the dry run lists: 1
parquet files after cleanup_all: 0
count through the live catalog: 4
reading the live catalog: IOException

The example runs in a temporary folder, and prints counts only. The live catalog's data is in 1 Parquet file. The dry run with cleanup_all lists 1 file, which is the file the real run deletes. After the real run, the folder holds 0 Parquet files. The live catalog still names the table. It answers count(*) with 4, from the catalog's statistics, and reading its columns fails with IOException. A count check would pass here, so it does not show missing files.

The safe order starts where the example did not. Back up the catalog file first. Run the cleanup with dry_run => true against the catalog that owns the data folder, and read the list. Run it for real only when every listed file is one you know is orphaned. Never run cleanup_all => true against a catalog you have not confirmed.

Two writers on one SQLite catalog. The pyiceberg documentation says SQLite is not built for concurrency. This chapter does not show what two writers do to the SQL catalog, so the book does not claim that they are safe. The stale-pointer example is the nearest case it does show: an object loaded before a commit reads the older state, and no error is raised. A writer that holds an old object does not write against that old state. At the pinned version of pyiceberg, an append through such an object is retried against the new metadata and commits on top of the newer state. The book makes no claim for other operations, or for two processes on one SQLite catalog. The reload rule in the SQL catalog section applies to reads.

Working rule: Run an orphan cleanup with dry_run => true first, against the catalog that owns the data folder, and read the list before any real run. If the catalog file is missing or empty, restore it before you run anything that deletes files.

Decisions this chapter made

• Iceberg is registered in a pyiceberg SQL catalog on SQLite, with a local warehouse. The book has one catalog name, local, and every example uses it. The pyiceberg documentation describes SQLite for development, and the examples are development examples.
• Iceberg tables reach DuckDB as Arrow tables. The book does not use DuckDB's iceberg extension, so the examples read Iceberg data through scan_arrow.
• Delta Lake tables are found by path, and the book teaches no Delta catalog. The examples open a folder, and a missing folder is an error.
• Delta tables are read through QueryBuilder only. This is a measurement from the book's build, not a primary source. At the pinned versions, the other Arrow conversion methods aborted the process at exit in about 17 of 20 runs. The QueryBuilder path aborted in none of 24 runs, including reads of earlier versions.
• DuckLake is loaded by connect_ducklake, with downloads off. Every example runs offline on the pinned DuckDB.
• The cleanup hazard runs in a temporary folder and prints counts only. The book shows the failure so the order of the safe steps is clear. It does not print file names, which change on every run.
• Each example prints counts and labelled values only. The examples name no file, path or ID in their output, so the output reads the same on every run and on every machine.
• REST catalogs have no runnable example. They need a network, so the chapter describes the protocol from its page and makes no claim about any product.