--- title: "Test Parquet, JSON and other files with DuckDB" description: "Test Parquet, JSON, files in S3 or Azure storage and other formats CAT has no provider for — prepare a DuckDB database file with views over them, outside CAT, and test it with DuckDB@1" url: "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 ## What you can test Whatever DuckDB reads, a view in your database file turns into a table CAT tests. | Data | How DuckDB reads it | |---|---| | Parquet — a file, a glob, a Hive-partitioned folder | `read_parquet('…')` | | JSON and newline-delimited JSON | `read_json_auto('…')` | | CSV, when you want DuckDB's reader options rather than [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "Csv@2") | `read_csv('…')` | | Excel, when you have a reason — [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") is the usual way | `read_xlsx('…')`, through DuckDB's `excel` extension | | files over `https://`, `s3://`, `az://`, `gs://` | the same functions; `httpfs` and the cloud extensions are loaded on demand | | another DuckDB file | `ATTACH '…' (READ_ONLY)` | | Delta, Iceberg and DuckLake tables | through their DuckDB extensions | ## 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: ```sql CREATE VIEW sales AS SELECT * FROM read_parquet('s3://acme-exports/sales/date=*/part-*.parquet', hive_partitioning = true); ``` In Python: ```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](https://docs.justcat.it/reference/data-sources/providers/duckdb-1/#who-else-has-the-file-open "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: ```sql 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: ```sql 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](https://docs.justcat.it/how-to-guides/organize-and-run-tests/work-with-secrets/ "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** Name : Catalog Provider : DuckDB@1 Technology : DuckDB Connection string : DataSource=%DATA_DIR%\catalog.duckdb;access_mode=READ_ONLY **YAML** ```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 `SELECT`s over the views. A smoke test — there is data for yesterday: **Properties** 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 **YAML** ```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** Name : Sale ids are unique and filled Suite : Sales export Data source : Catalog Query : ```sql SELECT sale_id, count(*) AS copies FROM sales GROUP BY sale_id HAVING sale_id IS NULL OR count(*) > 1 ``` Expectation : set is empty **YAML** ```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** 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 **YAML** ```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](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "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](https://docs.justcat.it/reference/data-sources/providers/duckdb-1/#in-memory-databases--not-supported-at-this-stage "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](https://docs.justcat.it/reference/data-sources/providers/duckdb-1/ "DuckDB@1") — the provider: connection string, locks, types. * [Test CSV and Excel files](https://docs.justcat.it/how-to-guides/data-platforms/test-csv-and-excel-files/ "Test CSV and Excel files") — when the file is the data. * [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. * [Compare data across systems](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems") * [Work with secrets](https://docs.justcat.it/how-to-guides/organize-and-run-tests/work-with-secrets/ "Work with secrets")