Query Parquet without importing tables

Use DuckDB to query a collection of Parquet exports and Drizzle to compose filters and grouped measures, including joins to database lookup tables. The sales stay in Parquet, with no sales import or migration needed.

Run the self-contained local example:

bun install --frozen-lockfile
bun example/parquet-analytics.ts

The example writes temporary Parquet fixtures, queries them, then closes its connection and removes the files. It requires no MotherDuck account or network access after dependencies are installed.

Source: example/parquet-analytics.ts

Compose a file scan

motherDuckReadParquet() also works with local DuckDB when mdRun is omitted. It binds paths and named option values as parameters. Pass a path, glob, or a list of file paths. Do not interpolate paths into sql.raw().

import { motherDuckReadParquet } from '@duckdbfan/drizzle-duckdb';

const source = motherDuckReadParquet(
  ['exports/first.parquet', 'exports/second.parquet'],
  {
    named: { union_by_name: true, hive_partitioning: true },
  }
);

Use explicit selections with db.select({...}).from(source). Alias that query with .as('sales') to reuse its fields in filters, grouping, and ordering. The runnable example demonstrates the complete composition.

Join files to a dimension table

The runnable example creates a small regions lookup table and joins its labels to the file-backed sales selection before grouping:

import { eq, isNotNull } from 'drizzle-orm';
import { pgTable, text } from 'drizzle-orm/pg-core';
import { countN, sumN } from '@duckdbfan/drizzle-duckdb';

const regions = pgTable('regions', {
  code: text('code').primaryKey(),
  label: text('label').notNull(),
});

const report = await db
  .select({
    region: sales.region,
    label: regions.label,
    orders: countN(),
    revenue: sumN(sales.amount),
  })
  .from(sales)
  .leftJoin(regions, eq(sales.region, regions.code))
  .where(isNotNull(sales.amount))
  .groupBy(sales.region, regions.label)
  .orderBy(sales.region);

The pgTable declaration describes an existing table. Create it separately, as the runnable demo does, or use your application’s migrated lookup table. Its primary key prevents multiple dimension rows from multiplying each sale.

The report keeps the original region code and uses a left join so an unknown code retains its sales with a null label, inferred as string | null. Grouping by both code and label keeps unknown regions separate. Lookup rows without sales do not appear. Filtering the right-hand label in where would remove unmatched sales, so choose that policy deliberately.

Expected demo output:

region	label	orders	revenue
east	null	1	7
west	Western region	2	15

East has no lookup entry, while the unused north lookup entry is absent. The west export without an amount is still excluded before aggregation.

Diagnose changing export schemas

If a report fails after an export changes, inspect the same file set and scan options before changing its TypeScript selection:

import { sql } from 'drizzle-orm';

const columns = await db.execute(sql`DESCRIBE SELECT * FROM ${source}`);
console.table(columns);

DESCRIBE reports column names and DuckDB types. It does not validate every value. If amount is absent from every file, union_by_name cannot create it and the report fails during binding. Restore the column in the export or choose the correct file set. Do not replace an unknown amount with zero by default.

An export containing text amounts such as '10.50' has a VARCHAR column. The report’s SUM rejects it even though the selection uses sql<number> and .mapWith(Number): those declarations do not cast values before aggregation. When numeric text is the intended input contract, cast explicitly in SQL:

import { sumN } from '@duckdbfan/drizzle-duckdb';

const totals = await db
  .select({ revenue: sumN(sql`CAST(amount AS DECIMAL(18, 2))`) })
  .from(source);

Choose precision and scale for your data. This example returns 15 for text amounts '10.50' and '4.50', and rejects 'invalid'. TRY_CAST would turn invalid values into NULL, which SUM ignores, so use it only with an explicit rejection-count or quarantine policy. sumN still converts the final total to a JavaScript number. Use an exact result representation when required.

Reconcile mixed-quality exports

Copy the example’s parquetReconciliation(db, paths) query when an export contains numeric text, malformed text, and files without amount. It uses TRY_CAST(amount AS DECIMAL(18, 2)) and reports each row in exactly one category before summing accepted amounts:

const rows = await parquetReconciliation(db, paths);
// [{ region, scanned, accepted, invalid, missing, revenue }, ...]

Region	Scanned	Accepted	Invalid	Missing	Revenue
east	1	0	1	0	0
west	4	2	1	1	15

For each region, accepted + invalid + missing = scanned. A SQL NULL is missing. A non-NULL value that cannot fit DECIMAL(18, 2) is invalid. Revenue is zero when a group has no accepted amounts. The example keeps rejected rows visible in counts, but does not store their original values or quarantine them. If every file lacks amount, DuckDB cannot bind the query: inspect the schema and restore the expected column. Adjust the decimal precision and scale to match your input contract: casting can round extra fractional digits. The returned revenue is a JavaScript number, so use an exact decimal result representation for high-precision accounting.

Find the export containing rejected rows

When a region has invalid or missing amounts, use the example’s parquetReconciliationByFile(db, paths) to identify the source export. Run its self-contained demo with:

bun example/parquet-analytics.ts --reconcile

The demo adds an invalid-only file to the numeric and missing-column fixtures. It returns one row per region and source file, including zero-revenue groups:

Region	File	Scanned	Accepted	Invalid	Missing	Revenue
east	third.parquet	1	1	0	0	7
west	first’s.parquet	2	2	0	0	15
west	rejected.parquet	1	0	1	0	0
west	second.parquet	1	0	0	1	0

sourceFile contains DuckDB’s full source path. The table shortens it for readability. For each file, accepted + invalid + missing = scanned. Sum the file counts by region to recover parquetReconciliation() totals over the same input. Revenue uses the same decimal policy, with ordinary JavaScript floating-point limits when adding returned numbers.

The query uses existing scan options and typed subquery fields:

const source = motherDuckReadParquet(paths, {
  named: {
    union_by_name: true,
    hive_partitioning: true,
    filename: '_export_file',
  },
});
// Select sql<string>`_export_file`.as('source_file') in the sales subquery,
// then group by both sales.region and sales.sourceFile.

DuckDB’s filename option names the metadata column _export_file. This avoids confusing a payload column named filename with the source path. Reserve _export_file in the input schema: DuckDB rejects this scan if that name already exists. Choose another metadata name and update the selection if needed. Paths and option values remain bound parameters, including globs and file lists.

The report counts rows rather than inventorying files, so empty files have no row. It does not report row positions or persist rejected values. Missing or unreadable files, incompatible schemas, and an amount column absent from every file still fail the scan. TypeScript selections do not validate file contents. The DECIMAL(18, 2) policy accepts zero and negative values, rounds extra fractional digits (for example 1.235 becomes 1.24), and classifies malformed or out-of-range values as invalid. SQL NULL is missing. Number output is not exact accounting. This workflow is verified with local Parquet files only.

Keep dimension labels without losing rejected groups

Run the mixed-region reconciliation example:

bun example/parquet-reconciliation-regions.ts

It scans a non-partitioned file containing several region codes, an unknown code and SQL NULL keys. A second file lacks amount. The report keeps all seven scanned rows in five region/source groups: two accepted, two invalid and three missing amounts. Accepted revenue is 13. The missing-only and invalid-only groups remain visible even when they have no matching label.

Use the existing reconciliation query as a subquery, then attach dimension labels without regrouping or filtering its rows:

const reconciled = parquetReconciliationByFile(db, paths).as('reconciled');
const report = await db
  .select({
    region: reconciled.region,
    sourceFile: reconciled.sourceFile,
    label: regions.label,
    scanned: reconciled.scanned,
    accepted: reconciled.accepted,
    invalid: reconciled.invalid,
    missing: reconciled.missing,
    revenue: reconciled.revenue,
  })
  .from(reconciled)
  .leftJoin(regions, eq(reconciled.region, regions.code))
  .orderBy(reconciled.region, reconciled.sourceFile);

The example gives each computed measure an explicit SQL alias, such as countN().as('scanned'). This lets Drizzle preserve its numeric result type when the field is selected through the reconciled subquery.

Keep the original region code and source path. Different codes can share a label, and both a NULL key and an unknown code have a NULL label. Grouping only by label would combine those distinct cases. Filtering on the right-hand label would discard unmatched reconciliation groups.

The actual lookup table must enforce unique region codes, as the demo’s PRIMARY KEY does. Declaring primaryKey() in TypeScript does not add that constraint to an existing database. Duplicate lookup keys would multiply report rows even though the amounts were already aggregated. Unused lookup rows stay absent, and an empty lookup leaves all counts and amounts intact.

Both reconciliation examples declare region as string | null because a file key can be NULL. The joined label is also string | null. These types describe the expected values and do not validate file contents. The decimal policy, reserved metadata name and local-file limitations above still apply.

Schema and value boundaries

• union_by_name matches columns by name across files and fills missing columns with SQL NULL. parquetRevenue() excludes rows with a missing amount, while parquetReconciliation() counts them. Choose a missing-data policy appropriate for your own reports.
• hive_partitioning reads partition values from directories such as region=west/. Keep partition names and types consistent across files.
• TypeScript selections describe the schema you expect. They do not inspect Parquet files or validate their runtime contents. Missing columns across all files or incompatible types can still produce DuckDB errors.
• sumN() and countN() convert results to JavaScript numbers. Use an exact decimal or bigint representation instead when precision matters.
• Local paths are resolved on the machine running DuckDB. Remote storage needs the relevant DuckDB extension and credentials. This example tests local files only and does not configure or verify remote access.