Test Parquet, JSON and other files with DuckDB

CAT has no provider for Parquet or JSON — but it has a DuckDB engine, and DuckDB reads them. Prepare a DuckDB database file outside CAT, with views over your files, then test it with CAT.

What you can test #

Whatever DuckDB reads, a view in your database file turns into a table CAT tests.

Prepare the database #

Do this once with the DuckDB command line or Python, or as a step of your pipeline that runs before CAT. A view reads the files when it is queried, so the database file stays small and the tests see today’s data:

CREATE VIEW sales AS
SELECT * FROM read_parquet('s3://acme-exports/sales/date=*/part-*.parquet', hive_partitioning = true);

In Python:

import duckdb

con = duckdb.connect("catalog.duckdb")
con.execute("""
    CREATE VIEW sales AS
    SELECT * FROM read_parquet('s3://acme-exports/sales/date=*/part-*.parquet', hive_partitioning = true)
""")
con.close()

Use CREATE TABLE … AS SELECT … instead when you want a frozen copy. Unions and joins across files belong in these views, written once, not in every test.

Then close the file. DuckDB lets one writer or many readers hold a file, never both — see Who else has the file open.

Credentials for remote files #

Two ways, both with a secret that DuckDB itself keeps.

A persistent secret (recommended). Create it once with the DuckDB client that prepared the file:

CREATE PERSISTENT SECRET acme (TYPE s3, KEY_ID '…', SECRET '…', REGION '…');

DuckDB stores it under your home folder (~/.duckdb/stored_secrets, in DuckDB’s own format, not encrypted), and every DuckDB instance started afterwards on that machine and account loads it by itself — CAT’s included. It must exist before CAT opens the file: an instance that is already running does not pick up a secret created later, so in CAT Studio create it before opening the project. See DuckDB’s documentation of secrets.

A temporary secret in the query. Make CREATE SECRET the first statement of every test’s query and the SELECT the last:

CREATE SECRET acme (TYPE s3, KEY_ID '%AWS_KEY_ID%', SECRET '%AWS_SECRET%', REGION 'eu-west-1');
SELECT 1 FROM sales LIMIT 1

%NAME% is replaced in the query as well as in the connection string, so the values stay in the environment (see Work with secrets), and the statement works on a read-only database. The expanded query can surface, though: the message of a test that does not pass shows the query, a sets match test logs it at Debug level, and a DuckDB error message may echo the statement it failed on — which is why the persistent secret is the recommended route.

Add the data source #

• Properties
• YAML

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

Open the file read-only: nothing a test does can change it, a mistyped path fails instead of creating an empty database, and several readers coexist. In CAT Studio pick DuckDB under Analytics; the guided form asks for the file and has the read-only switch on.

Write the tests #

Plain SELECTs over the views. A smoke test — there is data for yesterday:

• Properties
• YAML

Tests:
- Name: Yesterday's sales arrived
  Suite: Sales export
  Data source: Catalog
  Query: SELECT 1 FROM sales WHERE date = '2026-10-22' LIMIT 1
  Expectation: set is not empty

A rule — no nulls in the key column, no duplicates across the whole export:

• Properties
• YAML

Tests:
- Name: Sale ids are unique and filled
  Suite: Sales export
  Data source: Catalog
  Query: |
    SELECT sale_id, count(*) AS copies
    FROM sales
    GROUP BY sale_id
    HAVING sale_id IS NULL OR count(*) > 1
  Expectation: set is empty

And the comparison — the export against the warehouse table it came from, ordered on both sides:

• Properties
• YAML

Tests:
- Name: Export matches the warehouse
  Suite: Sales export
  First data source: Catalog
  First query: SELECT sale_id, amount FROM sales WHERE date = '2026-10-22' ORDER BY sale_id
  Second data source: DWH
  Second query: SELECT SaleId, Amount FROM fact.Sales WHERE SaleDate = '2026-10-22' ORDER BY SaleId
  Expectation: sets match

How to climb from row counts to a full comparison, and what to do with the differences: Compare data across systems.

Csv@2 and Excel@2, or DuckDB@1? #

Csv@2 and Excel@2 load the file once per session into tables named after the data source, give you a guided form and normalised names, and let one query join several files and sheets. DuckDB@1 opens a database you prepared, in which the views read the files on every test, and reaches every format and every location DuckDB does.

Use the first when the file is the data. Use the second when the format or the place has no provider, or when you want DuckDB’s reader options as written.

In-memory? #

DataSource=:memory: works, and the query can read the files directly, but the database is empty at every test and gone after it, so every test carries the whole preamble and runs an engine of its own. It is not supported at this stage; prepare a database file. See In-memory databases.

Prerequisites #

DuckDB ships with CAT. 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. If not, get the “X64” version from Latest Microsoft Visual C++ Redistributable Version.
• Internet connection. DuckDB loads its extensions (httpfs, parquet, json, …) from http://extensions.duckdb.org the first time a query needs them. The URL has to be reachable — white-list it if needed.

Related #

• DuckDB@1 — the provider: connection string, locks, types.
• Test CSV and Excel files — when the file is the data.
• Query CSV and Excel data with DuckDB SQL — the SQL dialect, by example.
• Compare data across systems
• Work with secrets