--- title: "DuckDB@1" description: "DuckDB@1 — DuckDB database files, opened read-only, with tables or views over Parquet, JSON and other files; no driver install" url: "https://docs.justcat.it/reference/data-sources/providers/duckdb-1/" --- # DuckDB@1 ## 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](https://docs.justcat.it/how-to-guides/data-platforms/test-parquet-json-and-other-files-with-duckdb/ "Test Parquet, JSON and other files with DuckDB"). [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") and [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "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** Name : Warehouse Provider : DuckDB@1 Technology : DuckDB Connection string : DataSource=%DATA_DIR%\warehouse.duckdb;access_mode=READ_ONLY **YAML** ```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: | Key | Meaning | |---|---| | `DataSource` (also `Data Source`) | Path to the database file. A relative path resolves against the **working directory of the process** that runs CAT, not against the project file — use an absolute path or an environment variable. | | `access_mode` | `READ_ONLY` is what to use. Several readers can open the file at once, a mistyped path fails with `database does not exist` instead of creating an empty file, and a test cannot write to your data — except while a run writes results into that same file, see the [DuckDB output](https://docs.justcat.it/reference/outputs/duckdb/ "DuckDB output"). The data source a DuckDB output writes through must not carry it; a second data source on the same file may. The default, read-write, **creates the file when it does not exist**. CAT does not add the key for you. | | `threads`, `memory_limit` | Engine settings, as DuckDB documents them. | | `file_search_path` | The folder DuckDB searches for a file named by a relative path. Set it when the views in the database name their Parquet or JSON files relative to the database. | 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](https://docs.justcat.it/reference/data-sources/providers/introduction/#how-every-provider-is-used "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 (PID n) by user ``` 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](https://docs.justcat.it/reference/outputs/duckdb/#where-the-results-live "Where the results live"): 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](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "Csv@2") and [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "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](https://docs.justcat.it/how-to-guides/data-platforms/test-parquet-json-and-other-files-with-duckdb/#credentials-for-remote-files "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 | DuckDB type | What the test sees | |---|---| | `TINYINT` … `BIGINT`, `UTINYINT` … `UBIGINT`, `FLOAT`, `DOUBLE`, `BOOLEAN`, `VARCHAR`, `DECIMAL` | the same, as a number, boolean or text | | `DATE`, `TIME`, `TIMESTAMP`, `UUID` | a date, time, date and time, or GUID | | `TIMESTAMPTZ` | date and time **in UTC** — `10:00+02` arrives as `08:00` | | `INTERVAL` | a time span, compared as text (`1.00:00:00`) | | `HUGEINT`, `UHUGEINT` | a number. Every `SUM` over an integer column is a `HUGEINT` in DuckDB. A value beyond ±7.9e28 fails the test naming the column — cast it to `VARCHAR` or `DOUBLE` in the query. | | `LIST`, `ARRAY`, `STRUCT`, `MAP` | JSON text — `[1,2,3]`, `{"x":1,"y":"b"}`; nested values likewise | | `BLOB` | upper-case hex text, `6162` for `'ab'`, as DuckDB's `hex()` writes it | `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](https://docs.justcat.it/reference/project-file/lists/ "Lists") of definitions: any statement that returns the columns of a [test](https://docs.justcat.it/reference/tests/properties/ "Test properties"), [data source](https://docs.justcat.it/reference/data-sources/properties/ "Data source properties") or [query](https://docs.justcat.it/reference/queries/properties/ "Query properties") 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. ```yaml 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](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs") — 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](https://docs.justcat.it/reference/outputs/duckdb/ "DuckDB output"). ## Related * [Technologies](https://docs.justcat.it/reference/data-sources/technologies/ "Technologies") — DuckDB and the provider behind it. * [DuckDB output](https://docs.justcat.it/reference/outputs/duckdb/ "DuckDB output") — test results into a DuckDB file. * [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "Csv@2") · [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") — the same engine over CSV files and workbooks. * [Test Parquet, JSON and other files with DuckDB](https://docs.justcat.it/how-to-guides/data-platforms/test-parquet-json-and-other-files-with-duckdb/ "Test Parquet, JSON and other files with DuckDB") — the how-to. * [Query CSV and Excel data with DuckDB SQL](https://docs.justcat.it/how-to-guides/data-platforms/query-csv-and-excel-with-duckdb-sql/ "Query CSV and Excel data with DuckDB SQL") — the SQL dialect, by example.