Acquire / database · All operations

Attach a database as a catalog

A variant of Read from a database

Register a database, file or catalog so its tables can be queried in place by name.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the sqlite_scanner and ducklake extensions.

The DuckDB step

Attach a SQLite database, a DuckDB file and a DuckLake catalog, then query their tables by name

It reads the orders and customers tables the proofs share. Create them first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
INSTALL sqlite; LOAD sqlite;
INSTALL ducklake; LOAD ducklake;
ATTACH '<your_scratch_dir>/crm.sqlite' AS crm (TYPE sqlite);
CREATE TABLE crm.customers AS SELECT customer, segment FROM customers;
DETACH crm;
ATTACH '<your_scratch_dir>/warehouse.duckdb' AS warehouse;
CREATE TABLE warehouse.orders AS SELECT order_id, customer, amount FROM orders;
DETACH warehouse;
-- a DuckLake catalog: a metadata file and a folder of data files, attached by one statement
ATTACH 'ducklake:<your_scratch_dir>/lake_meta.ducklake' AS lake (DATA_PATH '<your_scratch_dir>/lake_data/');
CREATE TABLE lake.events AS SELECT order_id, 'shipped' AS status FROM orders WHERE order_id <= 4;
DETACH lake;
-- registered once, the tables are named directly, with no scan function
ATTACH '<your_scratch_dir>/crm.sqlite' AS crm (TYPE sqlite, READ_ONLY);
ATTACH '<your_scratch_dir>/warehouse.duckdb' AS warehouse (READ_ONLY);
ATTACH 'ducklake:<your_scratch_dir>/lake_meta.ducklake' AS lake (DATA_PATH '<your_scratch_dir>/lake_data/', READ_ONLY);
CREATE TEMP TABLE result AS
SELECT o.order_id, o.customer, c.segment, e.status FROM warehouse.orders o JOIN crm.customers c USING (customer) LEFT JOIN lake.events e USING (order_id);
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(database_name ORDER BY database_name) FROM duckdb_databases() WHERE database_name IN ('crm', 'warehouse', 'lake')) = ['crm', 'lake', 'warehouse'] AND (SELECT count(*) = 4 AND count(*) FILTER (WHERE segment = 'retail') = 3 AND count(status) = 1 AND max(order_id) FILTER (WHERE status = 'shipped') = 1 FROM result)) THEN 'as expected' ELSE error('the three attached catalogs were not all registered or their tables did not join into 4 orders with a segment and one shipped') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes crm.sqlite, lake_data, lake_meta.ducklake and warehouse.duckdb itself, in <your_scratch_dir>.
  • It needs the sqlite_scanner and ducklake extensions.
  • The statement checks its own result and raises an error when it is wrong: “the three attached catalogs were not all registered or their tables did not join into 4 orders with a segment and one shipped”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
  • CREATE EXTERNAL CATALOGread 2026-09-27

    Register a catalog from a catalog provider factory, such as an Iceberg REST catalog

DuckDB (1)
  • ATTACH and DETACHread 2026-09-27

    Attach another database file or remote database as a catalog

Duckle (2)
  • DuckDBread 2026-09-25

    read a table from an attached DuckDB file

  • DuckLakeread 2026-09-25

    read a table from an attached DuckLake catalog, optionally as of a snapshot

Ibis (2)
  • DatabaseTableread 2026-09-27

    A named table that lives in a connected backend

  • UnboundTableread 2026-09-27

    Declare a table by name and schema alone, bound to a backend only when executed

Background

DuckDB's ATTACH statement adds a database file to the catalog, where it can be read from and written to.[1] In DuckDB, the fully qualified name of a catalog object contains the catalog, the schema and the name of the object.[1] In Apache DataFusion, CREATE EXTERNAL CATALOG registers a catalog, such as one backed by a remote catalog service, to be queried alongside built-in catalogs.[2]

When to use it

In DuckDB, attachment definitions are not persisted between sessions, so every database has to be attached again in a new session.[1] Keep the ATTACH statements at the top of the script that relies on them, so a fresh session rebuilds the same catalogs. (Meridian's view)

DuckDB's READ_ONLY option attaches a database in read-only mode, and read-write mode is the default.[1] DuckDB's ATTACH creates a read-only connection by default for a database reached over HTTP.[1] Attach a source you only mean to read with READ_ONLY, so no later step can change it by mistake. (Meridian's view)

In DuckDB, a single transaction can write to only one attached database, because atomicity is not maintained across attached databases.[1] DuckDB's DETACH statement closes an attached database and releases any locks held on its file.[1] In DuckDB, closing a connection, for example with close() in Python, does not release the locks held on attached database files.[1] When a schema and an attached catalog in DuckDB share a name, a one-part qualification is ambiguous and DuckDB asks for a fully qualified path.[1] Give attached databases aliases that no schema uses, and detach them explicitly when another program needs the file. (Meridian's view)

The method

In DuckDB, the TYPE option of ATTACH names the storage engine behind the attached database, such as SQLITE, POSTGRES or MYSQL, each provided by an extension.[1] When TYPE is not given, DuckDB deduces it from a scheme prefix in the path, such as sqlite:, or else from the file's contents, where a SQLite header selects the SQLITE type.[1] Once a SQLite file is attached, DuckDB reads its tables as if they were DuckDB tables, while the data is read from the SQLite file at query time.[3] DuckDB's postgres extension attaches every schema of a PostgreSQL database by default, and its SCHEMA option attaches a single schema.[4] DuckDB can attach a data file such as a Parquet file directly, exposing it through two read-only views that read the file each time they are queried.[1]

DuckDB's search_path setting lists the databases searched for a name that is not fully qualified, so tables in several attached databases can be referenced by name alone.[1] DuckDB's USE statement changes the default database and schema.[1]

Apache DataFusion's DROP CATALOG removes a catalog that CREATE EXTERNAL CATALOG registered.[2]

Compared with the alternatives

DuckDB's postgres_query table function runs an arbitrary read query within an attached PostgreSQL database and returns the result.[4] As of DuckDB 2.0, the CONNECT statement routes a whole session to an attached PostgreSQL database, shipping queries to the PostgreSQL server instead of pulling tables over the wire.[4] Attach when a script reads several tables from one source or joins across sources, and hand the query to the source when the source database should do the filtering and aggregating. (Meridian's view)

Sources

  1. [1]ATTACH and DETACH Statements – DuckDBdocumentation
  2. [2]DDL — Apache DataFusion main documentationdocumentation
  3. [3]SQLite Extension – DuckDBdocumentation
  4. [4]PostgreSQL Extension – DuckDBdocumentation