Query Data Files with SQL in Your Browser

Query Data Files with SQL in Your Browser

Opening a data file and asking it a question should not require a database server. The SQL Workbench loads Parquet, GeoParquet, Arrow, Feather, DBF, CSV, TSV, Excel, JSON, NDJSON and Avro files into DuckDB running as WebAssembly in your browser, so every format below answers to the same SQL once it is loaded. What changes from one format to the next is the part before the first query: how the file is read, which column types come through intact and which quirks need a cast or a workaround.

Each section covers one format in full. Parquet comes first because it is the format most analytical exports use, and the [Parquet debugging post](/blog/developer-tools/debugging-and-exploring-parquet-files-with-local-sql-queries/) walks through a full investigation on one file. Arrow, Feather and DBF follow, then GeoParquet, the text formats (CSV, TSV, JSON and NDJSON), Excel workbooks and Avro exports from streaming pipelines.

Whatever the format, start the same way. Run DESCRIBE to see the column names and inferred types, look at a few rows with LIMIT, and fix any type that came through as text before you aggregate. Because the workbench loads one file per session, a join across two separate files needs them combined into one file first; the CSV section shows two ways to do that.

Query Parquet Files with SQL in Your Browser

Apache Parquet is the dominant columnar format for analytical data. Warehouse exports, Spark jobs, dbt output, and cloud storage buckets all produce it. Drop a .parquet file into the SQL Workbench and DuckDB registers it as a queryable view, with no install, upload, or conversion step required.¹

Why data engineers reach for Parquet

Parquet emerged from the Hadoop ecosystem to solve a concrete read-performance problem. Row-oriented formats like CSV store every field in a record together, which is efficient for writing one record at a time but wasteful for analytical reads that touch only a handful of columns across millions of rows. Columnar storage inverts that layout. Each column occupies its own contiguous byte range in the file, so a SELECT that reads three columns out of thirty touches roughly 10% of the bytes. Consequently, file sizes shrink, query latency drops, and bandwidth costs fall.²

That combination explains why Parquet became the standard output of tools like Apache Spark, dbt, BigQuery export, Snowflake unload, and Redshift UNLOAD. Your .parquet file most likely came from one of those systems, from a Python pandas or Polars job, or from another DuckDB instance. GeoParquet (a Parquet-based format for geospatial vector data) follows the same physical layout and loads through the same reader.³

How the workbench loads your Parquet file

DuckDB reads Parquet natively, meaning no JavaScript translation layer sits between your file and the query engine. Drop orders.parquet and the workbench registers a view named orders. You can immediately run SELECT * FROM orders. DuckDB reads the Parquet footer first (that metadata block records column names, types, and row group statistics), so DESCRIBE orders returns the full schema in milliseconds even on a 2 GB file.

Inspecting schema and nested fields

Type inference is unnecessary because Parquet encodes schema information directly: INT64, FLOAT, UTF8, TIMESTAMP, and BOOLEAN columns arrive with their types already declared. Nested structures stored as repeated groups become accessible via dot notation without any preprocessing.⁴ Your file name sets the view name: pipeline_output_2026_04.parquet becomes the view pipeline_output_2026_04. For files with deeply nested schemas, DESCRIBE reveals the full struct hierarchy so you can plan your dot-notation paths before writing the first query, which saves time when the nesting goes three or four levels deep.

For a struct column, you reach a nested field with dot notation directly in the SELECT list or a WHERE clause, and you can chain further levels without first flattening the column. Because the types are declared in the file, DuckDB returns these nested reads as properly typed values rather than opaque blobs, so joins and aggregations on nested fields behave exactly like their top-level equivalents.

Column pruning, filter pushdown, and practical query patterns

Because Parquet stores columns separately, DuckDB reads only the columns your query references. SELECT order_id, total FROM orders WHERE status = 'shipped' reads three columns from the file regardless of how many columns orders contains. Across a 200-column warehouse export, that selectivity makes exploration fast. Filter pushdown extends the savings further: DuckDB inspects row group statistics embedded in the Parquet footer and skips entire row groups when your WHERE clause eliminates them based on recorded minimum and maximum values.⁵ Start every unfamiliar Parquet file with DESCRIBE to inspect the schema, then SELECT * FROM table LIMIT 10 to see sample rows. Both complete instantly. For aggregations, GROUP BY on low-cardinality columns like region or status first, then drill into individual values with additional WHERE clauses. The workbench runs each query in a Web Worker, so a slow aggregation across millions of rows does not freeze the editor.

Exporting query results back to Parquet

After exploring or filtering a Parquet file you can export results back to Parquet. The Export button runs a COPY ... TO statement through DuckDB, producing a correctly typed output file rather than a serialized snapshot of on-screen rows. Column types carry through the export: an INT64 source column becomes INT64 in the output, not a string. This typed fidelity is what distinguishes Parquet export from CSV export, which serializes every value as plain text and forces downstream readers to re-infer types.⁴

Choosing Parquet export over CSV

You can filter, aggregate, or join before exporting, and the exported file reflects exactly what your SQL produced. Parquet export depends on DuckDB directly and is available only for DuckDB-loaded file types.⁶ Opening a SQLite database disables Parquet export because those sessions run through sql.js, which does not have access to DuckDB's native Parquet writer. The Export button still offers CSV, JSON, and Excel for SQLite results. When you know the output will feed another analytical tool, Parquet is the better export choice because it preserves column types and compresses the data, whereas CSV serializes every value as plain text and forces downstream readers to guess types all over again. That downstream guessing step is where the most time gets lost in a CSV-based workflow, because every reader has to scan values and decide whether a string of digits is a number or text.

Understanding sort order and row group skipping

When a Parquet file comes from a pipeline that sorts data by a date or ID column before writing, DuckDB can skip entire row groups on future queries that filter on that column. A file written with ORDER BY event_date before export produces row groups where the minimum and maximum date within each group are sequential and non-overlapping. A query WHERE event_date = '2026-05-01' lets DuckDB read the Parquet footer, identify which row groups contain that date based on min/max statistics, and read only those groups.⁵

Writing sorted Parquet from the workbench

To produce a sorted Parquet file from the workbench, add ORDER BY to your export query before clicking Export: SELECT * FROM orders ORDER BY order_date. The exported Parquet file stores rows in date order, which enables faster filter pushdown on future WHERE order_date queries. For very large files where you repeatedly query a specific date range, this pre-sort step is the single highest-impact optimization available without schema changes.

Common Parquet type issues and how to resolve them

For the most common type mismatch in Parquet files from Python pipelines, the culprit is a numeric column that pandas wrote as FLOAT64 when the original values were integers. DESCRIBE reveals the column type; if a column you expect to hold order IDs or counts shows as DOUBLE, cast it in your query: order_id::BIGINT. The cast works for any column where the double values have no fractional part.

Timestamp columns from pandas pipelines often arrive as TIMESTAMP WITH TIME ZONE when the source DataFrame had timezone-aware datetime values. DuckDB handles these correctly, but filtering requires including the timezone in comparisons: WHERE event_ts AT TIME ZONE 'UTC' >= TIMESTAMP '2026-01-01 00:00:00'. For pipelines that mix timezone-aware and timezone-naive values across partitions, converting all timestamps to UTC before exporting from the upstream tool is the cleanest long-term fix, and you can fix a mismatched Parquet column type with a single cast rather than re-exporting the file.

When to use this

Use this when you receive a .parquet export from a data warehouse, pipeline, or Python job and want to inspect its schema, run ad hoc SQL, or filter it before passing results to another tool. Drop your file into the workbench and run DESCRIBE first; DuckDB reads the schema straight from the footer, so you see every column and type before writing a single query.

Examples

DESCRIBE orders;

SELECT customer_id, total
FROM orders
WHERE status = 'shipped'
  AND total > 500
ORDER BY total DESC;

SELECT region, COUNT(*) AS n
FROM sales
GROUP BY region
ORDER BY n DESC;

SELECT payload.user_id, payload.event_type
FROM events
LIMIT 20;

Query Apache Arrow and Feather Files with SQL

Pandas and Polars often need a fast handoff format that preserves types. Apache Arrow is a language-agnostic columnar format for in-memory data, serialization, and data transport.¹ Feather V2 is the Arrow IPC file format on disk, while Feather V1 is a legacy format distinct from Arrow IPC.² DuckDB-WASM can ingest Arrow IPC streams and query Parquet files directly in the browser, so a DataFrame saved from pandas or Polars can become a SQL view without a separate conversion step.³

Arrow and Feather as inter-process data formats

Apache Arrow was designed around a columnar memory layout that supports fast sequential scans, random access, and zero-copy sharing across processes.¹ Feather V2 stores Arrow tables using Arrow IPC, so .feather and .arrow files share the same serialized columnar layout; Feather V1 is the older, non-Arrow format that DuckDB cannot read directly.²

Schema preservation across tools

When a pipeline writes either file, the schema travels with the data, which is why DESCRIBE can report column names, types, and nullability without guessing. That is especially useful after a pandas or Polars step, because the file carries the difference between INTEGER, DOUBLE, TIMESTAMP, LIST, and STRUCT columns instead of asking DuckDB to infer everything from text. For teams that move data between Python, R, and SQL, this schema fidelity means a single file can serve as the authoritative exchange format without a separate schema registry or documentation step, which reduces the risk of type mismatches when a file crosses a language boundary.

Because Arrow stores both the logical type and the physical layout, an integer column arrives as a true integer rather than a text column DuckDB must re-parse, which keeps arithmetic and joins correct on the first query. The same fidelity applies to nested LIST and STRUCT columns, so a complex record written by Polars reads back with the same structure in DuckDB without a flattening step.

How the workbench loads Arrow and Feather files

DuckDB-WASM can insert Arrow IPC stream bytes through insertArrowFromIPCStream and can query Parquet files directly after registering a file.³ DuckDB's Arrow extension consumes and produces Arrow IPC files through read_arrow; filenames ending in .arrow or .arrows can be scanned directly.⁴ Because Feather V2 is Arrow IPC, .feather follows the same path after the browser exposes the file bytes to DuckDB-WASM. The Arrow schema embedded in the file defines columns, types, and nullability, so DESCRIBE orders reports the file schema rather than inferred SQL types. This direct schema mapping means you spend less time casting columns and more time analyzing data, which is especially helpful when the file originated from a Polars or pandas pipeline that already enforced strict types.

When to reach for Arrow instead of Parquet

Arrow IPC is a good fit when the next step needs fast serialization, while Parquet is a better fit for repeated selective queries because it compresses pages and carries row-group statistics.⁵⁶ Arrow files appear most often as temporary exchange files between pipeline steps, whereas Parquet appears in data lakes and warehouse exports. If you received a .arrow or .feather file, it likely came from a tool that wanted fast read speed, such as a Polars pipeline step, a pandas to_feather() call, or DuckDB itself. The workbench handles both use cases: load the file you have, and the query experience is identical regardless of format.

Python and Polars workflows that produce Arrow files

When a pandas pipeline saves a checkpoint with df.to_feather('/tmp/checkpoint.feather'), it writes a Feather V2 Arrow IPC file that DuckDB-WASM can ingest as Arrow bytes. A Polars pipeline that writes df.write_ipc('/tmp/result.arrow') produces the same Arrow IPC format with a different extension, and DuckDB reads both identically without requiring you to rename files or convert between formats manually.

Verifying Python and Polars round trips

After loading, run DESCRIBE to confirm that column types survived the round-trip from your pipeline. If a timestamp became VARCHAR, the upstream export likely wrote text rather than an Arrow timestamp. If a nullable integer became DOUBLE, inspect the source DataFrame for nulls and consider converting to a nullable integer type before exporting. CapyToolkit keeps this inspection local in the browser, so you can validate pipeline outputs without installing DuckDB on the machine that produced the file.

Choosing between Parquet export and keeping Arrow format

For results you plan to load directly into a Python script, the Parquet file you download from the workbench is immediately readable with pd.read_parquet() or pl.read_parquet(). A round-trip from Arrow IPC in the workbench to Parquet export to Polars read is type-safe because all three steps share the Apache Arrow type system.

When to convert Arrow to Parquet for future queries

For files you will query repeatedly in future workbench sessions, converting Arrow to Parquet using the Export button produces a file that benefits from compression and row-group statistics on future selective WHERE queries.⁵⁶ Parquet embeds per-row-group min/max statistics that Arrow IPC files lack. Load the Arrow file, run SELECT * FROM tablename, export as Parquet, and use the Parquet file as your new analytical baseline. Selective queries on sorted columns can run faster because Parquet readers can discard row groups that cannot match the filter. The Export button produces a type-safe Arrow-to-Parquet round trip you can confirm locally before wiring it into a pipeline that depends on it.

When to use this

Use this when you have a .arrow or .feather file produced by pandas, Polars, R, or another Arrow-native tool and want to run SQL aggregations or filters on it without installing DuckDB locally.

Examples

DESCRIBE orders;

SELECT product_category,
       AVG(unit_price) AS avg_price,
       COUNT(*) AS n
FROM products
GROUP BY product_category
ORDER BY avg_price DESC;

SELECT *
FROM events
WHERE event_date >= '2026-03-01'
  AND user_segment = 'paid';

SELECT metadata.source,
       metadata.version,
       COUNT(*) AS n
FROM records
GROUP BY 1, 2;

Query DBF dBASE Files with SQL in Your Browser

If you have ever downloaded a shapefile bundle, you have already met DBF. It is the dBASE attribute table used by ESRI shapefiles and a common output of legacy business systems.¹ The dBASE format dates to the early 1980s, and its compact table structure still appears in GIS downloads, government data portals, and older enterprise exports.² The SQL Workbench decodes dBASE files in the browser and loads them into DuckDB, so you can run SQL against GIS attribute tables, legacy database exports, and mainframe data extracts without any desktop GIS software.

Where DBF files still appear

The dBASE file format dates to the 1980s but remains actively used in geospatial workflows. Every ESRI shapefile consists of at least three component files: a .shp geometry file, a .shx index file, and a .dbf attribute table.² The .dbf holds all the non-geometry columns, such as census identifiers, land use codes, and population counts, that you join to geometry when making a map. Beyond GIS, DBF files appear in legacy enterprise systems: older ERP outputs, mainframe extracts converted to dBASE format, and government open data portals that still publish in shapefile bundles. Because these systems are not always easy to migrate, DBF files persist in data workflows long after the tooling around them has changed.

How the workbench loads DBF files

DBF files pass through a JavaScript parser that reads the dBASE header, extracts column names and types, and decodes the record bytes. This parsing step runs entirely in the browser, so the raw file bytes never leave your machine.³ Column names in dBASE are limited to 10 characters, so they may be truncated compared to the full attribute names you see in GIS software.

DBF type codes and encoding

The parser infers types from the dBASE type codes: C (character) becomes VARCHAR, N (numeric) becomes DOUBLE, D (date) becomes VARCHAR with an ISO date string, and L (logical) becomes BOOLEAN.⁴ Character fields in dBASE are stored as code page characters, and the dBASE header records a language-driver identifier used to interpret them.⁴ The parser attempts to detect encoding from the file header and convert to UTF-8. If accented characters appear garbled in the workbench, the file uses an encoding not identified in its header.

Numeric N fields keep any decimal precision declared in the header, so a field typed as N with two decimal places arrives as DOUBLE with its fractional part intact rather than as a rounded integer. When a column mixes numeric and blank values, dBASE stores the blanks as spaces, and the parser maps those empty cells to NULL so your aggregations do not count them as zeroes.

Practical patterns for GIS attribute tables

When working with the attribute table of a shapefile, the most common tasks are inspecting available fields, filtering by attribute values, and computing summary statistics. Run DESCRIBE to see all column names; they may be truncated to 10 characters in dBASE format.³ To find which land use categories appear in a parcel dataset: SELECT luse_code, COUNT(*) FROM parcels GROUP BY luse_code ORDER BY COUNT(*) DESC. To compute average population density by county: SELECT county_fips, AVG(pop_per_km2) FROM census_blocks GROUP BY county_fips. Export the result to CSV for use in another GIS tool or to Excel for reporting. Note that the geometry (spatial coordinates) lives in the companion .shp file, not in the .dbf; the workbench queries the attribute table only.⁵

Handling character encoding in older DBF files

Handling character encoding problems in older DBF files requires understanding the language-driver information stored with the file. The dBASE header records a language driver ID (LDID), which tells software such as ArcGIS which code page the text in the table uses.⁶ A file written in a DOS-era OEM code page such as CP437 (United States) or CP850 (Western European) shows garbled accented characters when a reader assumes the Windows ANSI code page instead.⁶ If the declared code page does not match what the file actually uses, the JavaScript parser converts characters using the wrong mapping and produces garbled output for any character outside the ASCII range.

Converting to UTF-8 before loading

For files with garbled accented characters, convert the file to UTF-8 before loading it in the workbench. On Linux and macOS, iconv -f cp1252 -t utf-8 input.dbf > output.dbf converts from Windows-1252 to UTF-8. Some DBF files from Western European GIS publishers use CP1252 but declare no code page in the header; try Windows-1252 as the source encoding first, then CP437 or CP850 if that produces garbled output.

Querying attributes before GIS cleanup

Once the text reads correctly, use SQL to reduce the attribute table before opening it in GIS software. Filtering by state, category, or population in the workbench gives you a smaller CSV or Parquet export to join back to geometry later. You can also compute summary statistics, identify outlier values, and flag records with missing attributes, all of which are tedious to do manually in a GIS attribute table but straightforward with a few SQL queries.

Working with shapefile bundles and the DBF row order relationship

In a shapefile bundle, the .dbf attribute table relates to the .shp geometry by row order: row 1 in the .dbf matches feature 1 in the .shp.¹ There is no explicit foreign key column; GIS software maintains that relationship internally. When you query the .dbf in the workbench, you get every attribute column but no geometry. The FID or OBJECTID column in most files tells you which geometry in the companion .shp each row corresponds to, but the workbench cannot display or query that geometry.

For workflows that combine SQL attribute filtering with GIS spatial analysis, query and filter the .dbf in the workbench first, note the FID values of the rows you want, then use QGIS or GeoPandas to select those features from the companion .shp using the FID list. Export the filtered attribute data to CSV and use the Join Attributes by Field Value tool in QGIS to attach it to the shapefile features. Filtering the attribute side reveals what a shapefile's attributes actually hold and narrows a large shapefile bundle down to the rows you need before you touch a desktop GIS tool at all.

When to use this

Use this when you have the .dbf component of a shapefile, a GIS data download, or a legacy business system export and want to run SQL aggregations or filters on the attribute data.

Examples

DESCRIBE parcels;

SELECT land_use, COUNT(*) AS n
FROM parcels
GROUP BY land_use
ORDER BY n DESC;

SELECT fips_code, county_name, pop_2020
FROM counties
WHERE pop_2020 > 1000000
ORDER BY pop_2020 DESC;

SELECT state_fips,
       COUNT(*) AS n_counties,
       SUM(area_km2) AS total_area
FROM counties
GROUP BY state_fips
ORDER BY total_area DESC
LIMIT 10;

Query GeoParquet Files with SQL in Your Browser

GeoParquet turns a map dataset into a Parquet file you can filter before opening GIS software. The SQL Workbench reads GeoParquet files through DuckDB's native Parquet reader. Geometry arrives as Well-Known Binary in a standard column. Every non-geometry attribute is fully queryable with SQL.

What makes GeoParquet different from regular Parquet

GeoParquet is a Parquet file with an additional metadata convention in the file-level key-value metadata. That convention records which column contains geometry, what geometry type it holds (Point, Polygon, LineString, and so on), and what coordinate reference system the coordinates use.¹ GeoParquet stores geometry as a standard Parquet BYTE_ARRAY column, encoded as Well-Known Binary.¹ Because geometry is just another column type, GeoParquet files load through the same DuckDB Parquet reader as any other .parquet file.² The additional metadata is readable but requires a spatial extension to act on geometry values. DuckDB-Wasm loads extensions only when asked, and the spatial extension is not listed among the officially available DuckDB-Wasm extensions in the current browser extension list.³ Without that extension, the geometry column contains raw binary bytes.⁴

How the workbench loads GeoParquet files

Drop a .parquet GeoParquet file and the workbench registers it as a DuckDB view. DESCRIBE returns the full schema: you see all attribute columns (VARCHAR, INTEGER, DOUBLE, DATE) alongside the geometry column, which appears as BLOB or BYTEA.⁵ The file name sets the view name, so a file named places.parquet becomes the view places, ready for immediate querying without any additional configuration.

Attribute queries without spatial SQL

You cannot run spatial predicates like ST_Intersects or ST_Within without a spatial extension, and DuckDB-WASM in the browser does not load the spatial extension automatically.³ You can, however, query all non-geometry attributes freely: SELECT fid, name, population FROM places WHERE country = 'US' works exactly as it does on any Parquet file. You can select and export the geometry column, but it appears as raw binary in CSV or JSON exports. For most attribute-only analysis, you can simply omit the geometry column from your SELECT and work with clean tabular data.

Sorting and ranking also work on attribute columns, so you can produce leaderboards like the highest-population places per country with a window function over PARTITION BY country_code ORDER BY population DESC. Because the geometry column is just bytes, including it in a wide SELECT only adds bulk to the result; leaving it out keeps the result small and readable when all you need is the tabular answer.

Useful patterns for GeoParquet attribute queries

GeoParquet files from Overture Maps, Natural Earth, or GeoPandas exports typically contain rich attribute tables alongside geometry. Inspect what attributes are available with DESCRIBE, then aggregate, filter, or join on those attributes. To find the ten most populous cities in a GeoParquet dataset: SELECT name, population FROM places ORDER BY population DESC LIMIT 10. To count features by country code: SELECT country_code, COUNT(*) FROM places GROUP BY country_code ORDER BY COUNT(*) DESC.

Preparing a filtered export for GIS

Export filtered subsets to Parquet to load into a GIS tool or Python GeoPandas for spatial analysis. The WKB geometry column carries through the Parquet export, so a downstream tool with spatial support can reconstruct the geometry from your filtered export.⁶ Before exporting, run a quick attribute summary with GROUP BY or COUNT DISTINCT to verify that your WHERE clause captured the expected feature count, and exclude the geometry column if you only need the tabular attributes.

Filtering GeoParquet attributes for downstream GIS workflows

When your GIS workflow requires a filtered subset of a large GeoParquet dataset, the workbench lets you produce that subset without installing QGIS or running a local DuckDB instance. Apply a WHERE clause on attribute columns, include the geometry column in your SELECT, then export as Parquet. The geometry column carries through the Parquet export as intact WKB bytes; a GIS tool or GeoPandas session can read the filtered export as a valid GeoParquet file.⁶

Filtering Overture Maps places to a single country

For an Overture Maps places file, SELECT * FROM places WHERE country_code = 'DE' produces a GeoParquet file containing only German features with geometry intact. The workbench handles the attribute filtering; the spatial predicate logic happens downstream in the tool that understands WKB geometry. For the full workflow: load the file, filter GeoParquet by country code, click Export, choose Parquet, then load the downloaded file in QGIS or GeoPandas for rendering and spatial analysis.

GeoParquet data sources and how to access them

Among the most useful public GeoParquet datasets is Overture Maps, which publishes open map data as GeoParquet partitioned by theme and region.⁷ The places theme contains points of interest with attributes like name, country_code, website_url, and categories alongside geometry.⁸ The buildings theme contains building footprints with height and class attributes.⁷ Downloading a single theme file from the Overture Maps release gives you a GeoParquet file you can drop directly into the workbench.

GeoPandas exports from Python produce GeoParquet via gdf.to_parquet('output.parquet') since GeoPandas 0.10 with pyarrow installed.⁹ QGIS 3.38 and later can export any vector layer to GeoParquet from the Save Vector Layer dialog when the installation includes GDAL 3.8 or higher.¹⁰ PostGIS tables are not directly exportable to GeoParquet without additional tooling; the standard approach is to export to GeoJSON first and convert with ogr2ogr, or to use DuckDB with the spatial extension on a desktop installation.¹¹

When to use this

Use this when you have a GeoParquet file from Overture Maps, GeoPandas, QGIS, or PostGIS and want to filter, aggregate, or inspect attribute data before doing spatial analysis in a GIS tool. Drop the file into the workbench and leave the geometry column out of your SELECT list when you only need the tabular attributes; DuckDB still reads the schema instantly either way.

Examples

DESCRIBE places;

SELECT country_code, COUNT(*) AS n
FROM places
WHERE feature_type = 'city'
GROUP BY country_code
ORDER BY n DESC
LIMIT 20;

SELECT *
FROM places
WHERE country_code = 'DE'
  AND population > 100000;

SELECT subtype,
       COUNT(*) AS n,
       AVG(population) AS avg_pop
FROM places
GROUP BY subtype
ORDER BY n DESC;

Query CSV and TSV Files with SQL in Your Browser

When CSV exports land on your desk, they often look too simple to query. Every spreadsheet tool, database, and API can produce them, yet those plain text files still carry enough structure for serious analysis.¹ The SQL Workbench reads both CSV and TSV with DuckDB, automatically detecting delimiters and inferring column types, so you can run GROUP BY, JOIN, and window function queries without writing any Python.

Why SQL on CSV beats a spreadsheet for data work

Spreadsheets handle a few thousand rows comfortably. Beyond that, scrolling and manual filtering become impractical, pivot tables slow down, and formula errors multiply. SQL does not share those constraints. DuckDB reads CSV files row by row without loading the entire file into memory at once, making it practical to query files with millions of rows in a browser tab. Furthermore, SQL is reproducible: a query you save and re-run tomorrow produces the same result, whereas a series of spreadsheet filter clicks does not. For tasks like aggregating log exports, summarising survey data, or cross-tabulating report outputs, SQL on CSV is faster to write and easier to verify than equivalent spreadsheet work.²

How the workbench loads CSV and TSV files

Drop a .csv file and DuckDB sniffs the delimiter, quoting character, and column types automatically. Tab-separated files use an explicit tab delimiter, so .tsv files load correctly without any configuration. The view name comes from the file stem: sales_2026_q1.csv becomes the view sales_2026_q1. For files with non-standard headers or leading whitespace, the sniffer still identifies the column boundary positions correctly. This automatic detection process is what makes the workbench practical for CSV exploration, because you can run meaningful queries on an unfamiliar file without first inspecting its contents in a text editor.³

Type inference and schema preview

DuckDB inspects the first rows to infer types: integers become BIGINT, decimals become DOUBLE, ISO date strings become DATE or TIMESTAMP, and everything else becomes VARCHAR. The workbench shows a schema preview and runs SELECT * FROM table LIMIT 10 on its own before you write anything. If DuckDB infers a type incorrectly (a ZIP code column read as INTEGER, for example), you can cast it in your query: SELECT zip_code::VARCHAR FROM addresses. The preview also reveals whether the header row was parsed correctly, so you can spot offset columns before writing aggregation queries.

Because the sniffer reads only a sample of leading rows, a column that looks numeric at the top can turn out to contain text further down, and DuckDB still declares it VARCHAR to stay safe. When you know a column is numeric throughout, an explicit cast keeps your aggregations from silently returning zero rows. Reviewing the preview before writing GROUP BY or JOIN logic is the fastest way to avoid a query that runs but reports the wrong totals.

Practical query patterns for CSV data

Start with DESCRIBE to confirm column names and inferred types, then run a LIMIT query to scan a few rows. From there, GROUP BY aggregations identify the shape of the data: SELECT category, COUNT(*) FROM products GROUP BY category ORDER BY COUNT(*) DESC. Filtering with WHERE, sorting with ORDER BY, and computing derived columns with expressions all work exactly as in any SQL database. Window functions such as ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date DESC) rank rows while preserving each row's identity.⁴

Because the workbench loads one file at a time, JOINs across two separate CSV files are not directly possible. Within a single file, self-joins and subqueries work normally. For a quick cross-check on data quality, SELECT COUNT(*) AS total, COUNT(DISTINCT id) AS unique_ids FROM table reveals duplicate key problems instantly.

Handling encoding and delimiter detection problems

Handling a CSV that fails to load correctly usually comes down to two issues: wrong encoding or wrong delimiter. DuckDB's CSV sniffer handles comma, semicolon, pipe, and tab delimiters automatically, but it may guess incorrectly for unusual files.⁵ When the sniffer picks the wrong delimiter, your data loads as one large VARCHAR column per row rather than individual typed columns. The symptom is DESCRIBE returning a single column named something like column0 with all values being long strings.

Overriding the delimiter and encoding

If automatic detection fails, override it with a custom read_csv query: SELECT * FROM read_csv('myfile.csv', delim=';', header=true). Replace ; with the correct delimiter for your file. For TSV files with a .csv extension, use delim='\t'. Encoding issues appear as replacement characters or garbled accented text in query results, and they are especially common when a file uses Windows-1252 or Latin-1 rather than UTF-8. Convert such files to UTF-8 first using most text editors or the iconv command-line tool, which handle this conversion reliably.

Multi-file analysis when the workbench loads one CSV at a time

When you need to compare or join data from two separate CSV files, you have two practical options within the one-file-per-session constraint. The first option is to concatenate the rows before loading: combine both CSVs into a single file using a command-line tool, then load the combined file and use a source discriminator column to distinguish which file each row came from.

Comparing files without direct multi-file joins

The second option applies when the files have different schemas and you need to join them on a key column. Export one file to SQLite using a tool like csvkit's csvsql insert command, load the SQLite database in the workbench, then query across both tables from the single database file.⁶ For repeated multi-file workflows, loading the data into a local SQLite database once and querying the .db file is more efficient than preprocessing on each workbench session. Pasting either the concatenated file or the SQLite export into the workbench gives you two CSV files joined without a server.

When to use this

Use this when you have a CSV export from a CRM, a database dump, a spreadsheet export, or an API response saved to disk and want to run SQL against it without installing anything.

Examples

SELECT COUNT(*) AS total,
       COUNT(DISTINCT id) AS unique_ids
FROM customers;

SELECT category, SUM(revenue) AS total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

SELECT zip_code::VARCHAR AS zip,
       city,
       state
FROM addresses
WHERE state = 'CA';

SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
  AND status = 'fulfilled';

Query JSON and NDJSON Files with SQL in Your Browser

JSON is flexible, which is why messy exports often use it. API responses, log exports, and event streams can arrive as plain arrays or as newline-delimited records, and the SQL Workbench reads both through DuckDB without a conversion step.¹²

Two JSON shapes, one reader

JSON data comes in two common file layouts. A JSON array file wraps all records in a top-level array: the file starts with [ and ends with ].¹ Unlike the array format, an NDJSON (Newline-Delimited JSON) file places one complete JSON object on each line with no surrounding array, making it easy to stream and append without rewriting the file.² Both layouts are common: REST API exports usually produce arrays, while log aggregators and event buses (Kafka consumers, AWS Kinesis exports, Logstash outputs) typically produce NDJSON. From the .json, .jsonl, or .ndjson file extension and the file's first bytes, the workbench detects the format automatically. The workbench loads both layouts so you query them identically.

Type inference and nested field access

DuckDB infers column types from the JSON values it reads.³ String values become VARCHAR, numbers become BIGINT or DOUBLE, booleans become BOOLEAN, and null values are handled gracefully. The sampling window covers enough rows to produce a stable schema, yet very large files still load quickly because DuckDB streams the JSON rather than buffering every byte before parsing.

Nested objects, arrays, and JSONPath

Nested objects within each record (a user object inside an event record, for example) are loaded as DuckDB STRUCT columns.⁴ Access struct fields with dot notation: SELECT event.user.id, event.user.email FROM events. Arrays within records become LIST columns that you can flatten with UNNEST: SELECT UNNEST(tags) AS tag FROM articles.⁵ For deeply nested JSON where you want to extract a specific path without unnesting entire structures, json_extract_string(payload, '$.user.address.city') navigates arbitrary nesting levels in a single expression without restructuring the file.⁴ This path-based extraction is especially useful when only a handful of fields matter inside a deeply nested payload.

Mixing the two approaches in one query is common: flatten a list with UNNEST for rows you want to group, then reach into a sibling struct with dot notation for the value you want to aggregate. Because the struct and list types are real DuckDB types, you can pass them into GROUP BY or join them on a key exactly as you would with flat columns, which keeps deeply nested data as queryable as a plain table.

Practical queries for JSON data

API response files often contain pagination wrappers that put the actual records inside a nested key. If your JSON file looks like { "data": [...], "meta": {...} }, use SELECT UNNEST(data) AS record FROM api_response to flatten the records field. For log files, aggregating by event type quickly reveals the distribution: SELECT event_type, COUNT(*) FROM logs GROUP BY event_type ORDER BY COUNT(*) DESC. Filtering by timestamp works once you parse the date string: WHERE strptime(timestamp, '%Y-%m-%dT%H:%M:%SZ') > '2026-04-01'. Because DuckDB-Wasm uses a browser Worker component, the query runs without a server round trip.⁶ You can also combine multiple WHERE conditions to narrow the result set before exporting, which keeps the output file small and focused on the records that actually matter for your analysis.

Working with paginated and wrapped API responses

Working with a paginated API export where each page is a separate JSON file requires a preparation step before loading. Save each page as an NDJSON file with one record per line (using jq: jq -c '.data[]' page1.json > combined.ndjson && jq -c '.data[]' page2.json >> combined.ndjson), then load the combined NDJSON file. The workbench registers it as a single DuckDB view covering all pages. This approach avoids the one-file-per-session constraint without merging raw JSON arrays into a single enormous document that would be slow to parse and memory-intensive to load.

Verifying the flattened record schema after UNNEST

After flattening records from a wrapper with UNNEST, run DESCRIBE on the result to confirm all expected fields became top-level columns. If your JSON wrapper has deeply nested records, the unnested result may produce a struct column rather than flat columns. Access struct fields via dot notation: SELECT record.user_id, record.event_type FROM (SELECT UNNEST(data) AS record FROM api_export). Adding a LIMIT 5 before inspecting the types keeps the schema preview fast on large files.

Schema heterogeneity and missing fields in NDJSON files

In NDJSON log files from production systems, not every record shares the same set of fields. An error event might include a stack_trace field that success events omit entirely. DuckDB samples the first rows to infer the schema, then reads all records against that inferred schema.³ Fields present in the sampled rows become columns; fields that only appear in later records are not discovered and are silently excluded from the result.

Discovering late-arriving fields

For files where the first rows do not represent the full schema, discover all keys with SELECT DISTINCT UNNEST(json_object_keys(payload)) AS key FROM logs on any VARCHAR column holding raw JSON payloads. If your inferred schema is missing a field you know exists, check whether that field first appears well beyond the sample window. Increasing the sample with read_ndjson('file.ndjson', sample_size=10000) gives DuckDB more rows to infer from, and running both the discovery query and the wider sample in the workbench shows how to surface fields a JSON sample hid before you trust an aggregation built on the inferred schema.

When to use this

Use this when you have an API export, a log file, or an event stream saved as JSON or NDJSON and want to aggregate, filter, or inspect the data with SQL.

Examples

SELECT event_type, COUNT(*) AS n
FROM logs
GROUP BY event_type
ORDER BY n DESC;

SELECT payload.user_id,
       payload.action,
       payload.timestamp
FROM events
LIMIT 20;

SELECT id, UNNEST(tags) AS tag
FROM articles;

SELECT json_extract_string(metadata, '$.location.city') AS city,
       COUNT(*) AS n
FROM sessions
GROUP BY city
ORDER BY n DESC;

Query Excel Files with SQL in Your Browser

An .xlsx workbook often arrives as the final stop before a messy export. The SQL Workbench converts every sheet in that workbook into a separate DuckDB table, named after the sheet tab, so you can query Sheet1 and Summary in the same session and JOIN them without copy-paste formulas.¹

Excel as a data source: where it fits and where it struggles

Excel workbooks carry data in a format that every business user understands, which is exactly why they appear in so many pipelines: stakeholders paste updates into a shared sheet, analysts download reports from SaaS platforms as .xlsx, and finance teams maintain ledgers that never migrate to a database. Yet Excel has limits that SQL does not. Formula cells can hide calculation errors, merged cells break tabular structure, and workbooks with hundreds of thousands of rows become slow to navigate. Because the workbench loads the raw cell values into DuckDB tables, you bypass all of those issues. Each sheet becomes a plain table. Formula results appear as their computed values.² You run SQL against the data, not against the spreadsheet machinery.

How the workbench loads Excel files

Excel files pass through a JavaScript parser that reads the modern XML-based .xlsx format.¹ Each sheet in the workbook becomes a DuckDB table named after the sheet tab.³ Column headers come from the first row, and a workbook with sheets named Orders, Products, and Returns produces three tables: Orders, Products, and Returns.

Excel types after parsing

The parser infers column types from the cell values: numeric cells become DOUBLE, date cells become VARCHAR (Excel date serial numbers are converted to ISO strings), and text cells become VARCHAR.³ After parsing, the workbench hands every table to DuckDB, so you query them with the same SQL as any other file format. Parquet export is available for Excel-derived tables because they are loaded into DuckDB rather than sql.js, which means your exported file preserves column types exactly.

Because every column resolves to a single DuckDB type, a column that mixes numbers and text lands as VARCHAR to avoid dropping values, which is why a price column with a stray note becomes text instead of DOUBLE. Cast such columns in the query rather than trusting the preview type, and review the SHOW TABLES output before writing JOINs so a type mismatch does not filter your rows out silently.

Multi-sheet queries and practical patterns

Having multiple sheets as separate tables unlocks queries that spreadsheets handle awkwardly. JOIN the Orders sheet to the Products sheet on a product ID column to compute revenue per product. Use GROUP BY on the Orders sheet alone to find the top customers by spend. Apply window functions like ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date DESC) to rank rows within each region across an entire sheet.⁴ For data quality checks, COUNT(DISTINCT id) versus COUNT(*) identifies duplicate rows in a sheet header that should have unique identifiers. When a workbook contains summary sheets that aggregate from detail sheets, you can verify the summary against the raw data with a single query.

Data quality checks across multiple sheets

Data quality checks across sheets are where the workbench outperforms in-spreadsheet formulas. A single SQL query cross-validates two sheets simultaneously: SELECT o.order_id FROM Orders o LEFT JOIN Products p ON o.product_id = p.id WHERE p.id IS NULL returns all order rows whose product_id has no matching record in Products. A VLOOKUP would handle this only awkwardly, because a spreadsheet formula cannot scan two entire sheets at once and flag every mismatched row in a single pass.

Detecting duplicates and referential integrity failures

For duplicate detection within a sheet, SELECT id, COUNT(*) AS n FROM Orders GROUP BY id HAVING n > 1 returns only IDs that appear more than once. Combine both checks into a single quality session: run the duplicate query on each sheet first, then run the LEFT JOIN referential integrity check across sheets. Export the problem rows to CSV to share findings with the file owner without resending the entire workbook.

Excel date and number formatting in SQL queries

In Excel, date cells are stored internally as numeric serial numbers (days since January 1, 1900) and displayed with a format mask.⁵ When the workbench loads an Excel file, the JavaScript parser converts date cells to ISO date strings (YYYY-MM-DD) before handing them to DuckDB. The resulting column type is VARCHAR, not DATE.

Working with Excel dates and percentages

To use date arithmetic or date_trunc, cast the column first: sale_date::DATE converts the VARCHAR ISO string to a DuckDB DATE type, which is exactly where Excel dates need a real DATE cast before any date math. Number cells formatted as currencies in Excel (formatted as $1,234.56) arrive as DOUBLE in DuckDB; the currency symbol and comma formatting are presentation concerns the parser strips. Percentage cells arrive as DOUBLE in their decimal form: 25% becomes 0.25. Multiply by 100 when you need the display percentage: SELECT margin_pct * 100 AS margin_pct_display FROM orders.

When to use this

Use this when you receive an .xlsx export from a SaaS platform, a finance system, or a colleague and want to run SQL aggregations, cross-sheet JOINs, or data quality checks without opening the file in Excel. Drop the workbook into the workbench and run SHOW TABLES first to confirm every sheet loaded as its own table before you write a cross-sheet JOIN.

Examples

SHOW TABLES;

SELECT o.order_id,
       o.quantity,
       p.product_name,
       p.unit_price
FROM Orders o
JOIN Products p ON o.product_id = p.id;

SELECT customer_name,
       SUM(amount) AS total_spend
FROM Orders
GROUP BY customer_name
ORDER BY total_spend DESC
LIMIT 10;

SELECT id, COUNT(*) AS n
FROM Products
GROUP BY id
HAVING COUNT(*) > 1;

Query Avro Files with SQL in Your Browser

When a streaming system needs schema and bytes to travel together, Avro keeps them in one self-describing file.¹² The SQL Workbench decodes the Avro schema and row data in the browser, then hands the result to DuckDB so you can query Kafka exports and pipeline dumps with full SQL.

Why Avro is common in streaming data pipelines

The Apache project designed Avro for schema evolution in streaming systems where producers and consumers may operate at different schema versions.² The schema is stored as a JSON block inside the Avro container header, which makes the file self-describing: a reader can inspect column names and types without a separate schema registry. That property explains Avro's dominance in Apache Kafka ecosystems. AWS Glue Schema Registry supports Avro as one of its streaming schema data formats.³ Avro uses a row-oriented binary layout, which is efficient for write-heavy streaming workloads where records arrive one at a time. Because the schema is embedded, a .avro file you export from a Kafka topic or a Confluent export contains everything the workbench needs to load and query it correctly.

How the workbench loads Avro files

Avro files go through a JavaScript bridge that reads the container header, extracts the embedded schema, and decodes the binary row data. The schema sets the column names and types for the DuckDB table: Avro string fields become VARCHAR, int fields become INTEGER, long fields become BIGINT, and union types that allow null become nullable columns. After decoding, the workbench hands the rows to DuckDB, so you query Avro data with the same SQL as Parquet or CSV. The table name comes from the file stem.

Row-oriented loading trade-off

Because Avro is row-oriented, the JavaScript bridge reads the entire file during loading; column pruning is not available the way it is with Parquet. Practically, this means query performance scales with file size more directly than columnar formats, but moderate-sized Avro exports (up to a few hundred megabytes) load in a few seconds. Once loaded into DuckDB, however, query execution itself is fast because the data is in a structured tabular format that the engine can scan efficiently.

For workloads that only need a few columns from a very large Avro export, the full-file read means you pay the load cost regardless of how little you query, which is the main practical limit relative to columnar formats. The trade-off is usually acceptable because the files arrive from streaming exports that are already bounded in size, and the structured DuckDB table you get at the end supports fast filtering and aggregation on every column.

Querying Kafka exports and pipeline dumps

Kafka topic exports saved as Avro typically contain a key field, a value struct, and metadata like partition and offset.⁴ The value struct becomes a nested DuckDB STRUCT column. Access its fields with dot notation: SELECT value.user_id, value.event_name FROM topic_export.⁵ For schema evolution scenarios where newer records have fields older records lack, those missing fields become NULL in the query result. Filtering by timestamp works once you identify the timestamp field name: WHERE strptime(created_at, '%Y-%m-%dT%H:%M:%SZ') > '2026-01-01'. Export the query result to CSV, JSON, Excel, or Parquet to move it into another tool. You can also filter by partition or offset to isolate a specific slice of the topic, which is useful when debugging a particular producer batch without reprocessing the entire file.

Inspecting Avro schema before writing queries

Inspecting the Avro schema is the first step after loading, because column types can differ from what you might expect from the original Kafka message schema. Run DESCRIBE on the loaded table immediately to see what types DuckDB assigned. The JavaScript bridge maps Avro string to VARCHAR, long to BIGINT, double to DOUBLE, boolean to BOOLEAN, and union types like ["null","string"] to nullable VARCHAR.

Avro logical types and how they appear in DuckDB

Avro logical types are annotations on top of primitive types. A timestamp-millis logical type on a long field stores a Unix epoch timestamp in milliseconds. Depending on how the JavaScript Avro decoder handles this logical type, the field may arrive as BIGINT (the raw millisecond value) or as a formatted string. Check DESCRIBE output and, if the field arrives as BIGINT, convert it: to_timestamp(created_at_ms / 1000.0) produces a readable DuckDB TIMESTAMP.

Reading nested Avro records

Nested Avro records become DuckDB STRUCT columns, so field access follows the same dot-notation pattern you use for Parquet nested types. If a Kafka value contains user.id and user.email, DESCRIBE shows the nested structure before you write SELECT value.user.id, value.user.email FROM topic_export. For deeply nested records with multiple levels, you can chain dot notation to reach any depth, and DESCRIBE output helps you verify the exact field names and types before constructing those paths.

Exporting Avro data to Parquet for analytical use

Exporting Avro query results to Parquet converts a row-oriented binary format into a columnar one that DuckDB queries more efficiently on future sessions. After the Avro file loads through the JavaScript bridge, the data is a standard DuckDB view, and any query result is exportable to Parquet via the Export button.⁶

For Avro files from Kafka topic exports, the common pattern is to flatten nested struct fields into top-level columns before exporting: SELECT value.user_id, value.event_type, value.timestamp FROM kafka_export. Then click Export and choose Parquet. The exported Parquet file has a simpler, flat schema that DuckDB queries faster in future sessions because it avoids the struct field access step, which is why flattening Avro structs before export pays off on later queries.

When to use this

Use this when you have a .avro file from a Kafka export, a Confluent topic dump, or an AWS Glue job and want to inspect its schema and run SQL against the event records. Drop the file into the workbench and run DESCRIBE right after loading, since that is the fastest way to confirm what types the JavaScript bridge actually assigned before you write a query against them.

Examples

DESCRIBE events;

SELECT event_type, COUNT(*) AS n
FROM events
GROUP BY event_type
ORDER BY n DESC;

SELECT value.user_id,
       value.action,
       value.session_id
FROM kafka_export
LIMIT 50;

SELECT *
FROM events
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31';