DuckDB@1

DuckDB@1 tests a DuckDB database file. Prepare what you need to test outside CAT — tables, or views over Parquet, JSON and other files — and CAT tests it.

Connects to #

A DuckDB database file (.duckdb, .db, whatever its writer named it), opened read-only. Any DuckDB client can write such a file — Python, R, the DuckDB command line, dbt-duckdb. The file holds tables, or views over Parquet, JSON, CSV and other files DuckDB reads, local or remote, so it is also the way to test formats CAT has no provider for: see Test Parquet, JSON and other files with DuckDB.

Excel@2 and Csv@2 run on the same DuckDB engine and remain the way to test workbooks and CSV files when the file is the data. DuckDB@1 is for when the database is the data, or the format has no provider.

Prepare what you need to test outside CAT; CAT tests it.

Example #

• Properties
• YAML

Data sources:
- Name: Warehouse
  Provider: DuckDB@1
  Technology: DuckDB
  Connection string: DataSource=%DATA_DIR%\warehouse.duckdb;access_mode=READ_ONLY

Connection string #

The keys are the driver’s — Key=Value pairs separated by semicolons, handed to the DuckDB.NET driver’s connection object exactly as written. CAT reads no key from the string, adds none and validates nothing; a key the driver does not know fails with the driver’s own message (Unrecognized connection string property 'bogus'). The keys you will use most:

Every DuckDB configuration option can be given as a key; the driver rejects a key that is not one. The connection string format is described in the DuckDB.NET documentation, the options themselves in DuckDB’s configuration reference.

The whole string, or any part of it, can come from an environment variable — %NAME% is replaced with the value of NAME; see How every provider is used.

In-memory databases — not supported at this stage #

DataSource=:memory: opens an empty database that exists for the one query and is discarded with it: nothing a test creates is seen by any other test, every test re-reads its files, and every test runs an engine of its own.

DataSource=:memory:?cache=shared is one database for every connection in the process that uses exactly that string, and it is kept only while one of them is open — so two tests share it only when they happen to run at the same time, and never with the tables Excel@2 and Csv@2 load.

Both work and CAT does not stop them, but neither is supported at this stage: prepare a database file instead. A later version may keep an in-memory database for the whole session.

Who else has the file open #

DuckDB locks the database file. One process that opens it read-write excludes every other process, read-only or not; several processes that open it read-only coexist. A lock conflict fails with a message that names the holder:

Could not set lock on file "…": Conflicting lock is held in <program> (PID n) by user <name>

So the pipeline step or notebook that writes the file has to close it before CAT runs. Any number of CAT processes (Studio, catcli, the PowerShell module) read it at the same time — unless one of them is writing results into it (the DuckDB output), which excludes the others for the length of that run. If the writer must stay open, close the notebook, have the writer open the file read-only too, or point CAT at a copy. DuckDB’s server mode is out of CAT’s scope.

Within one process, while any connection to a file is open, a connection opened meanwhile joins it in the mode of the first one: a read-write open after a read-only one cannot write, and a read-only open after a read-write one can. Once every connection is closed, the next open starts afresh. CAT relies on this for the DuckDB output: it opens its read-write connections before the first test and keeps them until the run ends.

Settings #

None beyond Connection string, Query and Timeout. DuckDB options go in the connection string; session options go in the query as SET …;.

Query #

One or more DuckDB SQL statements separated by ;. The result of the last one is the data — unlike on other providers, where the first result set is read — so SET …;, ATTACH … (READ_ONLY);, CREATE SECRET …; and INSTALL …; LOAD …; may precede the SELECT, and all of them work on a read-only database (a temporary secret is not a write). Table names are as the file has them: schema.table, or main.table. The dialect is the one in DuckDB’s SQL reference, and the same as on Csv@2 and Excel@2.

The query can carry a secret expanded from an environment variable, and the expanded text can surface: the message of a test that does not pass shows the query, a sets match or contains test logs both queries at Debug level, and a DuckDB error message may echo the statement it failed on. Keep secrets in a DuckDB persistent secret instead — see Credentials for remote files.

Timeout #

The test’s Timeout cancels the statement: a query that has not produced its result set after that many seconds ends the test in Error with The query did not finish within N seconds (Timeout)., and the query stops. 0 means no limit. A query that opens its own transaction (BEGIN; …) and is cancelled leaves that transaction open on its connection — don’t.

Prerequisites #

DuckDB ships with CAT; Windows, Linux and macOS alike. Two things must be in place on the machine:

Microsoft Visual C++ Redistributable (Windows) #

A very common prerequisite used by much other software, so it is probably already installed. To verify or install it, go to Latest Microsoft Visual C++ Redistributable Version and download and install the “X64” version.

If the installer shows a dialog with “Repair”, “Uninstall” and “Close” buttons, just close it — the prerequisite is already there. Otherwise install it (next, next, next…).

Internet connection #

DuckDB loads its extensions (httpfs, json, parquet, excel) from http://extensions.duckdb.org the first time a query needs them. The URL has to be reachable — connect to the Internet and white-list it if needed.

Files from a newer DuckDB #

The DuckDB engine inside CAT is version 1.5.5. DuckDB 2.0 (released 2026-10-21) writes a storage format this engine cannot read; opening such a file fails with:

Trying to read a database file with version number 999, but we can only read versions between 64 and 68. The database file was created with an newer version of DuckDB.

The writer fixes it: set the compatibility version before writing — SET storage_compatibility_version = 'v1.5.6';, in Python duckdb.connect(path, config={'storage_compatibility_version': 'v1.5.6'}), on the command line -storage-version v1.5.6. A file written that way opens. Upgrading the engine inside CAT is on the roadmap.

Types #

NaN and the infinities of DOUBLE and FLOAT arrive as themselves, and two such values match each other. In an expected-data list the word NaN matches a NaN; to compare an infinity as text, cast it in the query (CAST(x AS VARCHAR)). Inside a LIST, JSON shows them as bare NaN and Infinity. DuckDB sorts NaN above everything, .NET below it, so do not use a column that holds NaN as a Key.

Infinite dates and timestamps (infinity, -infinity) cannot be delivered by the driver: the test ends in Error with the driver’s message (Cannot convert infinite timestamp value to DateTime or DateTimeOffset …). Cast the column to VARCHAR in the query and the value is the word infinity.

Serving test and data source definitions #

DuckDB@1 can also serve lists of definitions: any statement that returns the columns of a test, data source or query definition works — a table or a view in the database file, read when the project opens. The list takes the same Connection string as a data source (read-only, as above), and the file must not be held open for writing at that moment.

Get list of tests from:
- Name: tests kept in the warehouse file
  Provider: DuckDB@1
  Connection string: DataSource=%DATA_DIR%\warehouse.duckdb;access_mode=READ_ONLY
  Query: SELECT * FROM main.Tests

Outputs #

A DuckDB@1 data source can be the target of a database output — the results history in the same file as the data under test or in a file of its own. The data source the output writes through is opened without access_mode=READ_ONLY. See the DuckDB output.

Related #

• Technologies — DuckDB and the provider behind it.
• DuckDB output — test results into a DuckDB file.
• Csv@2 · Excel@2 — the same engine over CSV files and workbooks.
• Test Parquet, JSON and other files with DuckDB — the how-to.
• Query CSV and Excel data with DuckDB SQL — the SQL dialect, by example.