SQL Data Workbench Reference

What Is Parquet Format?

If a wide analytical table feels wasteful to scan, Parquet changes the unit of work. Instead of storing each row's values together, Apache Parquet stores each column's values contiguously, so DuckDB can read less data and apply row-group statistics before touching values. That layout enables selective reads, compression, and compatibility across data tools.¹

What is Parquet format?

Origins and design goals

Parquet emerged as a Hadoop-era columnar storage format for analytical workloads. Row-oriented text formats such as CSV are convenient for manual inspection, but analytical queries often need only a few columns from many rows. Parquet inverts that layout. Each column is stored separately, so a query over three columns can read only the three column chunks it references instead of scanning every column.

Why selective reads matter

Column-level compression improves further because values in a column share the same type, allowing dictionary encoding and run-length encoding to exploit repetition. Parquet is therefore a better fit for analytical storage than CSV when file size, selective reads, and query speed matter. On a 200-column warehouse export where most queries touch fewer than ten columns, the I/O savings from selective reads alone can reduce query time by an order of magnitude.

Selective reads also reduce memory pressure during query execution, because DuckDB materialises only the columns a query needs rather than buffering a wide row. For files shared with downstream tools, this efficiency means a single Parquet file serves both heavy aggregation and light lookups without forcing readers to load columns they ignore. The footer metadata that enables all of this is small and cached after the first read, so repeated queries against the same file stay fast.

How the SQL Workbench uses Parquet

DuckDB reads Parquet natively, which means the workbench registers a .parquet file as a view and makes it immediately queryable without a conversion step.⁴ DuckDB reads the Parquet footer to extract the schema, including column names, types, and row group statistics, before touching row data. That makes DESCRIBE useful even on large files.

Native Parquet reads in the SQL Workbench

Column pruning restricts disk reads to only the columns a query references, which means a SELECT that touches three columns out of forty reads roughly 75% fewer bytes from disk. Filter pushdown uses row group statistics to skip chunks when a WHERE clause eliminates them. The Export button writes query results back to Parquet via DuckDB's native COPY ... TO writer, preserving column types in the output.⁴

Parquet compared to CSV and Arrow

CSV is human-readable and universally supported but lacks type information and compresses poorly; every value is text. Parquet is binary and not directly human-readable, but it carries a typed schema, compresses well, and reads efficiently for analytics. Apache Arrow IPC files share Parquet's columnar layout and exact schema but are optimised for in-memory speed and zero-copy exchange rather than long-term storage efficiency.⁵ Feather V2 is the Arrow IPC file format on disk and supports all Arrow data types with LZ4 or ZSTD compression. Parquet is the right choice for long-term storage and data lake files; Arrow is the right choice for temporary exchange between pipeline steps that want fast reads without a conversion layer.

Row groups, statistics, and how DuckDB skips data

In a Parquet file, data is stored in horizontal slices called row groups. Each row group contains one column chunk per column, and the Parquet file format stores metadata that points readers to those column chunks.² DuckDB exposes that metadata through parquet_metadata, including row group IDs, column paths, compression, encodings, min/max values, and null counts.⁶ DuckDB uses these statistics to skip row groups entirely when a WHERE clause provably excludes all rows in that group.⁶ Consequently, a query WHERE order_date >= '2026-06-01' on a sorted Parquet file skips every row group whose maximum order_date predates that threshold without reading any column data.

This filter pushdown behavior is why column sort order matters when you write Parquet files from upstream tools. Parquet files sorted by a filter column allow DuckDB to skip the most row groups. When you export query results from the workbench to Parquet and sort the result set by the column you will filter on in future sessions, you produce a file that DuckDB can prune more aggressively.

Nested types and dictionary encoding in Parquet

Parquet supports nested data through its definition and repetition level system, which encodes optional and repeated fields without storing NULL bytes explicitly.³ DuckDB reads Parquet nested types through its schema mapping, so nested fields remain accessible through SQL. A Parquet file produced by Spark with a nested struct column loads with its field structure intact and queryable via dot notation.

Nested fields and dictionary encoding

For columns with a small number of distinct values (like country_code, status, or category), Parquet can apply dictionary encoding: it builds a lookup table of distinct values and stores each row value as an integer index into that table. A column with 50 distinct country codes across 10 million rows stores only 50 string values in the dictionary, with all remaining rows stored as small integers. This reduces file size and can make GROUP BY on such columns faster because the engine compares integer indexes rather than repeated strings.

Try in the tool

What Is Apache Arrow Format?

When data moves between Python, R, JavaScript, and SQL, Arrow avoids repeated reshaping. Apache Arrow is a language-independent columnar format for analytical data. It stores each column in contiguous buffers, supports nested data, and can be shared without converting between runtime-specific layouts.¹ The Arrow IPC file format serializes that layout to disk or streams; Feather v2 is Arrow IPC on disk for Python and R data frames.²

What is Apache Arrow format?

Zero-copy sharing and the Arrow ecosystem

Arrow's primary design goal is reducing the serialization cost that appears when analytical data moves between tools. Because each column uses a predictable buffer layout, an Arrow-aware library can point at memory that another Arrow-aware library understands. That shared understanding eliminates the need to copy and convert data at every handoff boundary, which is why Arrow has become the backbone of modern data exchange between Python, R, JavaScript, and SQL engines.

Zero-copy access in shared memory

The columnar format is relocatable without pointer swizzling, which enables true zero-copy access in shared memory. DuckDB's Arrow IPC support follows the same principle: DuckDB can consume and produce Arrow IPC data through the arrow community extension, and DuckDB and Arrow work well together because both are built around columnar data. This compatibility means you can query Arrow files in the workbench without any type coercion or data reshaping.

Because the buffers are self-describing and aligned, a JavaScript runtime and a SQL engine can operate on the same bytes without marshalling data across a serialization boundary. That property is what makes in-browser analytics on Arrow fast: the workbench hands DuckDB the Arrow record batches directly rather than re-parsing a text format. The same mechanism lets Python and SQL share data within a single process without an intermediate copy.

How the SQL Workbench reads Arrow files

DuckDB's Arrow extension scans Arrow IPC buffers and files, so the workbench can register a dropped .arrow file as a queryable view through DuckDB's Arrow reader.³ Feather v2 files are also Arrow IPC files, which is why .feather files follow the same read path once the Arrow IPC layout is available to DuckDB. The schema is embedded in the Arrow file before the record batches, so DuckDB can read declared column names and types rather than infer everything from raw text.

Arrow IPC versus Parquet metadata

Arrow IPC files do not carry Parquet-style row group statistics; DuckDB's Parquet metadata tools expose row group IDs, min/max values, null counts, compression, and page offsets for Parquet files instead.⁴ For moderate files, that difference rarely matters. For repeated selective filters on very large files, Parquet gives DuckDB more metadata to skip work before reading row data. Converting an Arrow file to Parquet through the workbench Export button is a practical way to gain those statistics for files you query frequently.

Arrow versus Parquet: when to use each

Arrow and Parquet are complementary formats rather than direct substitutes, because each one optimizes for a different point in the data lifecycle. DuckDB describes Arrow IPC as a fast interchange format and Parquet as a more sophisticated archival storage format, because Arrow minimizes encoding and decoding overhead while Parquet emphasizes compact files and rich metadata.

Choosing by workload and lifecycle

Feather v2 stores Arrow tables or data frames and supports LZ4 or ZSTD compression, but it still does not provide Parquet's row group statistics or Parquet-style column chunk metadata.² The Parquet file layout separates column chunks, row groups, and file metadata, which is why engines can read only selected columns and inspect metadata before touching all data pages.⁵ Use Arrow when a file is a short-lived handoff between pipeline steps. Use Parquet when the file will be stored, queried repeatedly, or moved across a network where size and pruning matter.

Arrow files from Python, R, and data pipelines

Arrow files appear most often as checkpoints in Python and R pipelines where one step writes a result and the next step reads it without converting through CSV or JSON. The pyarrow.feather module reads and writes Feather files, and Feather v2 is the default current version for storing Arrow tables or pandas and R data frames.⁶ A pandas pipeline can save a checkpoint with df.to_feather('/tmp/checkpoint.feather'); a Polars pipeline can write Arrow IPC with df.write_ipc('/tmp/result.arrow'). Drop either file into the workbench and the query experience is the same once DuckDB reads the Arrow IPC data.

When you load an Arrow file from any of these pipelines, run DESCRIBE immediately to verify that column types survived the round-trip. If a column you expected as BIGINT arrives as INTEGER, the upstream code used a narrower dtype before writing the Arrow file. If a timestamp arrives as TIMESTAMP[us] instead of TIMESTAMP[ns], the upstream library preserved microsecond precision rather than nanosecond precision.

Arrow memory layout and the columnar execution model

Beyond the file format, Apache Arrow defines an in-memory layout standard for analytical data. Each column occupies contiguous buffers, and Arrow recommends alignment and padding that support SIMD instructions over column values. Arrow IPC files do not expose the same per-chunk min/max/null-count statistics that Parquet metadata exposes, which is a meaningful difference for repeated selective queries, because Arrow readers must scan every row during query execution while Parquet readers can skip entire row groups based on embedded statistics without reading any column data, and this difference grows more significant as file size increases.

DuckDB's parquet_metadata function reports those Parquet statistics and page offsets, helping identify what can be skipped for selective queries.⁵ Consequently, DuckDB applies Arrow filters during query execution after reading the Arrow data, whereas Parquet benefits from filter pushdown at the file level. For ad-hoc analysis on files under a few hundred megabytes, the scan-every-row behavior of Arrow is rarely a bottleneck on modern hardware.

For files where you repeatedly run selective WHERE queries on a date or numeric column, export the Arrow file to Parquet using the workbench Export button and reload the Parquet file. Future queries on the Parquet version can use Parquet metadata for pruning, which makes a noticeable difference on large datasets with selective access patterns. For one-off exploratory queries on smaller files, the convenience of loading Arrow directly often outweighs the pruning benefit that Parquet provides.

Try in the tool

What Is DuckDB-WASM?

DuckDB-WASM is DuckDB compiled to WebAssembly. It brings the core DuckDB analytical SQL engine into a browser tab, where you can run SQL against local files without a server, plugin, or local database install.¹

What is DuckDB-WASM?

How WebAssembly enables server-free SQL

WebAssembly lets compiled native code run in a browser as a compact binary module inside the browser's sandboxed runtime.² That model is a good fit for DuckDB because the engine can run in-process rather than sending SQL to a remote server. DuckDB-WASM exposes this as a browser client that can be embedded in JavaScript applications or used through the DuckDB web shell.⁶

Browser execution without a server

The browser may cache the WASM assets after first download, but the first load still depends on network access and the available cache state. Query execution is isolated from the main UI thread through Web Workers, so a long aggregation can run while the page remains interactive. This architecture is what makes the workbench fundamentally different from a cloud SQL editor: your data never leaves the machine, and the query engine runs entirely within the browser sandbox.

Because the engine lives in the sandbox, a malformed query cannot touch files on your disk outside the virtual file system the workbench set up. The Web Worker boundary also means a runaway aggregation degrades only that background thread rather than locking the whole tab. For analysts who handle confidential data, this local-first model removes the upload step that most cloud SQL editors require.

How the SQL Workbench uses DuckDB-WASM

The SQL Workbench initialises a DuckDB-WASM instance when you first open the page. File access works through the browser's File object API: when you drop a file, the workbench reads its bytes and passes them to DuckDB-WASM's virtual file system.⁷ DuckDB-WASM uses a dedicated web filesystem abstraction for browser data sources, including local files, remote files, and registered buffers. From DuckDB's perspective inside the WASM sandbox, the registered file is a path that DuckDB can scan with its normal file readers. Query results flow back from the Web Worker to the main thread as serialized result sets, which the workbench renders in the results table. Each query runs asynchronously, so the UI remains responsive during a long aggregation. Parquet export uses DuckDB-WASM's COPY ... TO mechanism, which writes the output file to the virtual file system and then triggers a browser download.

Differences from native DuckDB

DuckDB-WASM runs much of the same analytical SQL surface as native DuckDB, but the browser environment changes the boundary conditions. The DuckDB-WASM package describes it as an in-process analytical SQL database for the browser with Arrow, Parquet, CSV, JSON, filesystem, and HTTP request support.⁶ The DuckDB-WASM repository also calls out differences in HTTP handling, extension loading, out-of-core behavior, filesystem access, and default threading compared with native DuckDB.⁵ The spatial extension is not active in the workbench. Network file access through HTTP or HTTPS requires a WASM build with the httpfs extension, which is not active in the workbench. The workbench also does not support DuckDB's multi-file glob reads (SELECT * FROM 'data/*.parquet'). Within those limits, common DuckDB SQL patterns such as window functions, PIVOT, UNNEST, JSON functions, date functions, regex, and list/struct operations remain available for local browser-side analysis.

The browser virtual file system and memory model

For DuckDB-WASM, file access works through a virtual file system that bridges browser file security and DuckDB's native file I/O. DuckDB-WASM's launch article describes the integrated web filesystem and explains how local, remote, and buffer-backed files are normalized for DuckDB scans.³ When you drop a file into the workbench, the browser reads its bytes through the File API and writes them to DuckDB-WASM's in-memory virtual file system.⁷ From DuckDB's perspective inside the WASM sandbox, the file is a normal path on a local disk: it reads the Parquet footer, accesses column chunks, and applies DuckDB's normal query planning.

Local-only browser execution

This design means that every file you load exists only in your browser tab's memory. No bytes reach a server. The browser's memory ceiling is the practical constraint on file size; for large files, the virtual file system and the query engine compete for the same memory pool, which is why very large files can cause browser memory pressure. For sensitive datasets that cannot leave your machine, this local-only model is not just a convenience but a compliance requirement that the workbench satisfies by design.

Query execution in Web Workers and cancellation

Query execution in DuckDB-WASM runs in a Web Worker, a background thread that operates independently of the browser's main UI thread.⁴ This separation means that a query scanning millions of rows does not freeze the page, block scroll interaction, or prevent you from editing the SQL in the editor while the query runs. The workbench communicates with the Web Worker asynchronously: when you click Run, the main thread posts a message to the worker; when the query completes, the worker posts the result set back for rendering.

Cancelling a long-running query

Because the Web Worker runs independently, the workbench implements query cancellation by terminating and recreating the Web Worker, which causes a brief DuckDB reinitialisation. A cancelled query leaves no partial results; the next query starts on a clean DuckDB instance. For interactive exploration, add a LIMIT clause to large table scans: SELECT * FROM large_table LIMIT 1000 returns results immediately, while an unlimited scan may run for several minutes on a file with millions of rows.

Try in the tool

What Is NDJSON?

For log pipelines, every record needs to stand on its own. NDJSON gives each line its own complete JSON document, which makes appending new events safe without rewriting earlier bytes. That layout also lets DuckDB infer a tabular schema from a text file you can inspect in an editor.¹

What is NDJSON?

Why NDJSON is the standard for log and event data

Log aggregation systems and event buses face a problem that JSON arrays do not handle cleanly: records arrive continuously and must be appended to a file without rewriting it, which is a constraint that affects every production logging pipeline. Appending to a JSON array requires replacing the closing ] bracket and adding a comma before each new record, an operation that is not safe under concurrent writes and can corrupt the file if two producers write at the same time. ### Append-only records without rewriting the file

NDJSON sidesteps that entirely. Appending a new record means writing a newline and the JSON object: no existing bytes change. Consequently, event pipelines can treat each record as an independent JSON document. Amazon Data Firehose documents this pattern for JSON input: multiple JSON documents may appear in the same record as {"a": 1}{"b": 1}, while an array of JSON documents is not valid input.³

NDJSON streams well: a receiver reading line by line can begin processing the first record while the sender is still writing later records, which is impossible with a JSON array that must be fully received before parsing can complete. This streaming property is what makes NDJSON the default format for log aggregation frameworks like Fluentd and for event buses that append records continuously.

Because each line is a complete JSON document, a downstream consumer can re-read from any point in the file by scanning for newlines rather than re-parsing the whole thing. That property also makes NDJSON resilient to partial writes: a crash mid-record leaves a single malformed line, not a corrupted array bracket that invalidates the entire file. The result is a format that tolerates the messy, never-ending write pattern of real event pipelines.

How DuckDB reads NDJSON

DuckDB reads NDJSON files natively through its JSON reader, which means the workbench can scan a multi-gigabyte log file without buffering the entire document in memory first. The reader can use the newline_delimited format, and read_ndjson/read_ndjson_auto are aliases for read_json with that format, so you can use whichever function name reads most clearly in your queries.²

Inferring schema from line-delimited records

By parsing each line independently, DuckDB infers column types from a sample of the first rows: string fields become VARCHAR, numbers become BIGINT or DOUBLE, booleans become BOOLEAN, and null fields are nullable. Nested objects within each record become STRUCT columns accessible via dot notation. For arrays within records, DuckDB creates LIST columns that you can flatten with UNNEST. After loading, the file is available as a view named after the file stem, so a log file named app_events.ndjson becomes the view app_events. Because DuckDB streams NDJSON line by line, files with millions of lines remain tractable in browser memory.

Adjusting the sample size helps when the first rows are not representative of the whole file. DuckDB infers types from a leading sample, so a column that looks numeric early but contains text later may be declared VARCHAR, while a column with text early and numbers later may be read as VARCHAR too. Raising the sample size with read_ndjson_auto sample options gives the reader more rows before it commits to a type, which reduces surprises on heterogeneous event logs.

NDJSON compared to JSON arrays and Avro

NDJSON and JSON arrays convey the same data with different physical layouts. NDJSON is better for streaming and append-only workloads; JSON arrays are more convenient for single-file API responses where the entire payload is received at once. Avro is a binary serialization format with a JSON schema representation and an object container file layout that stores file metadata, including the schema, in the header.⁴ That makes Avro a compact schema-aware counterpart to NDJSON in high-throughput streaming, while NDJSON remains easier to inspect in a text editor. For analytical queries, both arrive in DuckDB as equivalent queryable tables. The key difference at query time is that Avro files load through a JavaScript bridge that decodes the binary records, whereas NDJSON streams line by line through DuckDB's native JSON reader.

Converting NDJSON to Parquet for faster analytical queries

Converting a large NDJSON log export to Parquet before running repeated queries gives you two advantages that plain NDJSON cannot: columnar storage and file metadata that tells readers where column chunks live. Parquet splits columns into row groups, then writes file metadata at the end so readers can locate the column chunks they need.⁵ Columnar storage means DuckDB reads only the fields your query references rather than scanning every field of every record. Row group statistics allow DuckDB to skip entire chunks when your WHERE clause targets a date or numeric range that the per-chunk min/max values prove cannot contain matching rows.

Running the conversion in two steps

Load the NDJSON file in the workbench, run SELECT * FROM logs (or any filtering query to subset first), then click Export and choose Parquet. The exported file carries the column types DuckDB inferred from the NDJSON schema. Reload the Parquet file for your analytical sessions; subsequent aggregations on large filtered ranges run faster because filter pushdown now operates at the file level rather than during query execution.

NDJSON in production log and event pipelines

In production log pipelines, NDJSON is useful because every system in the chain can append records without reading or rewriting existing content. Amazon Data Firehose uses the same JSON-document sequence pattern for input conversion, accepting adjacent JSON documents such as {"a": 1}{"b": 1} while rejecting arrays as invalid input.³ A single downloaded partition from any of these systems is immediately queryable in the workbench without conversion.

Start with SELECT * FROM logs LIMIT 10 to inspect the first records and see what the inferred schema looks like. Then run DESCRIBE logs to confirm column types before writing aggregation queries. For files where some events have optional fields others lack, check the nullable flag in DESCRIBE output: nullable columns in DuckDB map to NDJSON fields that were absent in some of the sampled records.

Checking schema drift in NDJSON exports

Schema drift is easier to spot before aggregation queries run. If a new event field appears after the first rows, DuckDB may treat it as absent from the sampled schema; if an existing field changes type, downstream filters can return surprising NULL values that are difficult to trace back to their root cause.

Inspect nullable fields before grouping

Run DESCRIBE logs after loading the file, then compare the nullable flags with the event contract you expected. For fields that should always exist, add WHERE field IS NOT NULL or promote the file to Parquet only after the schema has been confirmed. Spotting a nullable flag on a field that your pipeline guarantees will always be present is often the first sign of upstream data quality issues that would silently corrupt downstream aggregations. Running this check before you write GROUP BY or window function queries saves time by catching schema mismatches early.

Try in the tool

What Is Apache Avro Format?

In an event stream, the schema is the contract between producers and consumers. Avro carries that contract as a JSON block inside each container file header, so every .avro file remains self-describing as producers and consumers evolve.¹ The format is also the native serialization format for Apache Kafka and Confluent Schema Registry.²

What is Apache Avro format?

Why Kafka and streaming pipelines use Avro

Avro was designed for systems where producers and consumers evolve independently over time.² A Kafka producer writing events may add a new field in a schema update; consumers running an older schema version must still be able to read events produced with the new schema without crashing. Avro's schema evolution rules define which changes are backward-compatible (adding an optional field) and which are not (renaming or removing a required field). ### Schema negotiation across producer and consumer versions

Confluent Schema Registry extends this by storing Avro schemas centrally and assigning version numbers, allowing producers and consumers to negotiate the correct schema version for each message. Avro's row-oriented layout is efficient for streaming workloads where records arrive individually rather than in batches, because each record can be encoded and decoded independently without reading the entire file.

How the workbench loads Avro files

Avro files pass through a JavaScript bridge that reads the container header, extracts the schema, and decodes the binary records into plain JavaScript objects.⁴ The schema determines column names and types: Avro string fields become VARCHAR, long fields become BIGINT, double fields become DOUBLE, and union types like ["null","string"] become nullable VARCHAR. After decoding, the JavaScript bridge hands the rows to DuckDB, which registers a view named after the file stem. From that point, you query the Avro data with the same SQL syntax as any other file format: GROUP BY, window functions, PIVOT, and JSON functions all work. Because Avro is row-oriented, the entire file is decoded before querying begins, which makes load time proportional to file size.

Avro compared to Parquet and NDJSON

Avro, Parquet, and NDJSON all store tabular data but optimise for different workloads.⁵ Avro is row-oriented and binary with an embedded schema: it is ideal for streaming and schema evolution. NDJSON is row-oriented and text-based; it is human-readable and append-friendly but slower to parse and larger on disk. Parquet is columnar and binary: compact, fast for analytics, and capable of filter pushdown, but not suitable for streaming appends. In a typical event pipeline, Avro appears in the Kafka transport layer, NDJSON appears in log aggregators that write human-readable files, and Parquet appears in the data lake after a batch conversion step. The workbench handles all three, so you can query whichever format you received from your pipeline.

Inspecting Avro schema and nested record fields

Before running aggregation queries on an Avro export, run DESCRIBE on the loaded table to see how the JavaScript bridge mapped Avro types to DuckDB types. Avro's string type becomes VARCHAR, long becomes BIGINT, double becomes DOUBLE, boolean becomes BOOLEAN, and ["null","string"] union types become nullable VARCHAR. Nested Avro record fields become STRUCT columns in DuckDB, accessible via dot notation in the SELECT clause. DESCRIBE reveals which columns are structs and which are primitives before you write field access expressions.

Reading nested Avro records

For Avro files exported from Confluent connectors that use the "magic byte" format (a 5-byte schema ID prefix before the Avro payload), the JavaScript bridge may fail to decode records because the magic bytes are not part of the standalone Avro container format.⁶ Confluent-format Avro files require stripping the schema ID prefix before the workbench can read them. Standalone Avro container files, which store the full schema in the header, load without modification.

For topic exports that arrive in Confluent format, a quick preprocessing step removes the 5-byte prefix so the workbench can decode the records directly. Tools that write standalone container files, such as most Kafka connect S3 sinks, avoid the prefix entirely and load without any fixup. Knowing which producer wrote the file tells you whether that extra decode step is needed before the schema becomes queryable.

Choosing between Avro, Parquet, and NDJSON for pipeline outputs

For teams deciding which format to export from a Kafka pipeline for downstream analysis in the workbench, the three most common choices are Avro, Parquet, and NDJSON. Each has a distinct trade-off.⁵ Avro preserves schema fidelity through schema evolution and produces compact binary output for row-by-row streaming; however, its row-oriented layout means the entire file loads into DuckDB memory before querying begins. NDJSON is human-readable and easy to inspect with a text editor, but it is the largest of the three formats and the slowest to parse. Parquet is the fastest for analytical queries and the most compact, but it requires a batch step to produce from a streaming source.

Choosing by pipeline stage

The format that fits best depends on where in the pipeline you are working. At the Kafka consumer output, Avro is appropriate because it maps directly from the Confluent serialization format. At the batch export layer where event data is aggregated into a data lake, Parquet is the right target. NDJSON fits best for ad hoc exports where human readability matters more than query performance. The workbench handles all three without changing your SQL query patterns, so you can standardize on one tool regardless of which format your pipeline produces.

Validating Avro schema evolution before querying

Avro remains reliable only when the embedded schema still matches the contract your queries expect. Adding an optional field is usually safe, but renaming a required field or changing a primitive type can change the table shape that DuckDB sees. Even adding a new required field without a default value can break older consumers, so understanding the evolution rules before querying prevents unexpected NULL columns.

Confirm nullable unions and renamed fields

Run DESCRIBE events immediately after loading the file, then check union fields such as ["null","string"] and any field names that changed in the producer contract. If a query depends on a renamed field, alias it in SQL rather than assuming the old column name exists. Catching these mismatches before you write aggregation queries prevents silent NULL results that are difficult to trace back to a schema change. For files from long-running Kafka topics, comparing the DESCRIBE output against the latest schema version from your data catalog is a quick sanity check that takes seconds but saves hours of debugging.

Try in the tool

What Is Feather Format?

When a Python or R pipeline needs a typed checkpoint, Feather writes the Arrow record batch to disk. Feather v2, the current version used by pandas and Polars, is byte-identical to the Arrow IPC file format.¹ .feather and .arrow files load through the same DuckDB reader and produce identical query results.

What is Feather format?

Feather v1 versus Feather v2

Feather v1 predates the Arrow IPC file format and used a custom binary layout. It offered fast read and write speeds for Python-R data exchange but had no formal specification, limited compression support, and no handling of nested types like lists and structs. ### Feather v2 as the Arrow IPC file format

Feather v2 replaced v1 entirely: it is the Arrow IPC file format with a .feather extension, fully specified and supported across the Arrow ecosystem. Pandas writes Feather v2 by default via df.to_feather() since pandas 1.0. Polars writes Feather v2 via df.write_ipc(). R writes it via arrow::write_feather(). If you have an older .feather file produced before 2020, it may be v1 format. DuckDB reads Feather v2 natively; Feather v1 files may fail to load or produce incorrect results.

How the workbench loads Feather files

DuckDB reads Feather v2 files through its Arrow IPC reader, the same code path it uses for .arrow files.⁴ Drop a .feather file and the workbench registers a view named after the file stem. The schema comes from the Arrow schema message in the file header: DuckDB maps Arrow INT32 to INTEGER, FLOAT64 to DOUBLE, UTF8 to VARCHAR, TIMESTAMP[us] to TIMESTAMP, and so on, without any type inference. DESCRIBE returns exact types immediately.

Why Feather lacks Parquet row-group stats

Because Feather v2 is an Arrow IPC format, it does not embed row group statistics like Parquet does. Filter pushdown occurs at the query level rather than at the file level, meaning DuckDB reads all rows and applies WHERE conditions during query execution rather than skipping file chunks. For exploratory work on moderate-sized files this rarely matters, but for repeated analytical queries on large datasets, converting to Parquet first gives DuckDB the metadata it needs to skip irrelevant data.

For workloads that repeatedly filter the same large Feather file, the missing statistics add up across sessions because every query re-reads the full row set. A one-time conversion to Parquet through the Export button pays for itself quickly when the file is queried many times. The trade-off is clearest on sorted columns, where Parquet can discard whole row groups that a Feather reader must still scan.

When to use Feather versus Parquet

Feather (Arrow IPC) and Parquet serve complementary roles. Feather optimises for read and write speed: serialising an Arrow DataFrame to Feather and reading it back approaches memory bandwidth limits, making it ideal for caching intermediate pipeline results.⁵ Parquet optimises for compact storage and analytical query performance: column-level compression codecs and row group statistics make Parquet files 3 to 10 times smaller than equivalent Feather files and faster to query with selective WHERE clauses.⁶ Use Feather when a pipeline step saves a result that the next step reads immediately; the overhead of Parquet compression is unnecessary in that case. Use Parquet when the file is stored long-term, transmitted across a network, or queried selectively across many sessions.

Writing Feather files from Python, R, and Polars

From Python with pandas, df.to_feather('output.feather') writes the file with LZ4 compression by default. Pass compression='uncompressed' or compression='zstd' to change the codec. With Polars, df.write_ipc('output.arrow') produces an Arrow IPC file; rename it to .feather if your downstream tool requires that extension. Both files are byte-identical at the data level, which means a Feather file written by pandas on a Linux machine produces the same DuckDB schema as a file written by Polars on Windows or by R on macOS. In R with the arrow package, arrow::write_feather(df, 'output.feather') produces a Feather v2 file from arrow package version 4.0 onward.

Round-tripping between languages without type loss

For round-tripping data between languages, Arrow IPC ensures that column types translate exactly. An INT32 column in a pandas DataFrame becomes INTEGER in DuckDB, an INT32 column in R, and a 32-bit integer column in Polars. No type coercion or rounding occurs because all three tools share the same Arrow memory layout specification. This fidelity makes Feather the preferred exchange format when type integrity across languages matters more than compact storage.

Comparing query performance between Feather and Parquet in the workbench

Compared to Parquet, Feather loads faster on initial file open because LZ4 decompression is faster than Snappy and the Arrow record batch format avoids the footer-parsing step that Parquet requires. For most queries on files under a few hundred megabytes, the overall performance difference between Feather and Parquet in the workbench is small because both use columnar layouts that allow DuckDB to read only the columns your query references.

The main difference appears in selective filtering on large files. Parquet's row group statistics enable filter pushdown: a query like WHERE sale_date = '2026-05-01' may skip 95% of a sorted Parquet file without reading it. Feather has no equivalent statistics, so DuckDB scans all rows and applies the filter during query execution. For a file with millions of rows and a highly selective WHERE clause, converting Feather to Parquet using the workbench Export button produces a file that DuckDB reads substantially faster in future sessions.

Inspecting Feather schema before export

Feather files are fast to query, but they still deserve a quick schema check before you export a result. A DataFrame can arrive with TIMESTAMP[us], TIMESTAMP[ns], dictionary-encoded strings, or nullable integer columns, and each choice affects how DuckDB displays the table. Paying attention to these details upfront prevents subtle bugs that are hard to trace after the file has been shared with a downstream team or tool.

Confirm Arrow types before sharing

Run DESCRIBE table_name after loading the file, then compare the DuckDB types with the upstream Python or R code that produced it. If a timestamp precision or nullable flag looks wrong, fix the upstream write step before exporting the Feather file to Parquet or another tool. This quick check prevents subtle type mismatches that can cause silent data loss, such as a TIMESTAMP[ns] column being truncated to microsecond precision when written from a source that only supports microseconds.

Try in the tool

What Is DBF dBASE Format?

A DBF file is often the table hidden inside a shapefile bundle. DBF is the file extension for dBASE tabular data files, and it stores rows and columns in a simple binary format with a fixed-width record layout. The SQL Workbench reads .dbf files via a JavaScript bridge and loads them into DuckDB for SQL querying.

What is DBF dBASE format?

DBF in the GIS and legacy data landscape

The ESRI Shapefile format, introduced in 1998 and still the most widely distributed geospatial vector format, mandates a .dbf file as the attribute table for every shapefile, and most GIS tools cannot render a shapefile bundle without its accompanying .dbf.⁵ Census bureaus, land agencies, environmental authorities, and open data portals worldwide distribute shapefile bundles with .dbf attribute tables. Beyond GIS, dBASE files persist in legacy enterprise systems: older ERP platforms, mainframe data extracts converted for PC use in the 1980s and 1990s, and government databases that have not migrated away from their original format even though the underlying technology is decades old. ### Legacy outputs that still reach modern workflows

Because these systems are expensive and risky to replace, DBF files continue to appear in modern data workflows as outputs from unchanged legacy processes, and you may receive them from a colleague who exports data from an older GIS tool without considering whether the format is still widely supported. CapyToolkit lets you open these files directly in the browser without installing a desktop GIS or legacy dBASE runtime.

Structure, encoding, and common quirks

DBF's fixed-width record layout makes it fast to seek to any record by offset, but it also imposes limitations that cause problems in modern workflows, especially when files were created decades ago with assumptions that no longer hold, such as the assumption that all consumers share the same code page.

Encodings and truncated field names

The dBASE format limits column names to 10 characters, so descriptive names like land_use_category become truncated codes like luse_cat or land_use_c in the .dbf file. Numeric fields store values as fixed-width decimal strings rather than binary, which can cause precision issues for large numbers.⁶ Date fields store YYYYMMDD without a century indicator in some older versions, causing ambiguity for dates before 1970. Character encoding is a persistent source of problems: files from Western European countries often use Latin-1 or CP437, files from Central and Eastern Europe use CP852, and files from newer systems may use UTF-8. If the code page byte in the header does not match the actual encoding, accented characters appear garbled, and recovering the original text requires converting the file with the correct code page before loading it into the workbench.

Truncated names also complicate joins with other tables, because the short DBF column rarely matches the full attribute name used downstream. Plan to alias columns in your query or rename them in the export step so the resulting CSV or Parquet lines up with the rest of your pipeline. A DESCRIBE before any join reveals exactly which abbreviations you are working with, which avoids mismatched keys that silently drop rows.

Querying DBF files in the workbench

The workbench decodes the DBF header, reads column names and types, and hands the records to DuckDB as a table. Character columns become VARCHAR, numeric columns become DOUBLE, date columns become VARCHAR in ISO format (YYYY-MM-DD), and logical columns become BOOLEAN. The table name comes from the file stem. GIS attribute tables typically contain a geometry identifier column (like FID or OBJECTID) that links each record to its companion geometry in the .shp file; the workbench can query that column, but the geometry itself is in the .shp file, which the workbench does not parse. Aggregations, filters, and GROUP BY queries on the attribute columns work normally. Export the query result to CSV or Excel to bring the filtered attribute data into another tool.

Reading DBF files from specific GIS data sources

Reading a DBF file from a well-known GIS source comes with predictable schema conventions. US Census Bureau TIGER/Line shapefiles use column names like GEOID, NAMELSAD, ALAND, and AWATER across their county and tract files.⁷ Natural Earth shapefiles use ADMIN, ISO_A2, NAME, and POP_EST. For OpenStreetMap data converted to shapefile by Osmosis or ogr2ogr, the attribute fields depend on which OSM tags you selected during export.

Querying census FIPS data from TIGER shapefiles

For US census data, the GEOID column links the .dbf attribute table to the companion .shp geometry. Running SELECT GEOID, NAMELSAD, ALAND FROM cb_2022_us_county_500k WHERE STATEFP = '06' filters California counties by the state FIPS code without needing the geometry. Population figures, area measurements, and demographic variables all appear in the DBF attribute table and query normally. Export the filtered subset to CSV to use it in a BI tool or to join against another dataset that shares the GEOID format.

Converting DBF files to modern formats for archival and analysis

DBF files from legacy systems often contain data worth migrating to a more portable format. Once you load the .dbf file and confirm the schema via DESCRIBE, export the entire table or a filtered subset to CSV, JSON, or Excel using the Export button. The exported file carries the DuckDB column types inferred from the DBF type codes: C fields become VARCHAR, N fields become DOUBLE, D fields become a YYYY-MM-DD string, and L fields become BOOLEAN.

For long-term archival, Parquet is a better target than CSV because it preserves column types explicitly. Load the .dbf file, run a column-selective query to exclude truncated or garbled column names, then export as Parquet. The resulting file is smaller than the equivalent CSV, reads faster in DuckDB in future sessions, and carries typed column metadata that documents what the original DBF columns contained.

Checking DBF encodings before loading

DBF decoding starts before the first SQL query. The header code page tells the parser how to interpret fixed-width character bytes, and a mismatch can turn ordinary place names into unreadable text. Because the encoding decision happens at parse time, no amount of SQL manipulation after loading can recover characters that were decoded incorrectly from the source bytes.

Treat garbled text as a metadata issue

If accented characters look wrong, compare the .dbf header code page with the source documentation or a companion .cpg file. When the parser cannot infer the correct code page, convert the file to UTF-8 in a local GIS or database tool before loading it into the workbench. Trying to fix garbled characters with SQL string functions after loading is almost always futile because the wrong bytes were decoded at parse time, so the correct approach is to get the encoding right before the file enters the workbench.

Try in the tool

What Is Columnar Storage?

Analytical queries usually read columns, not complete records. Columnar storage organises data on disk or in memory by column rather than by row, writing all values of the same column together. For analytical queries that read a few columns across many rows, that layout reduces the data read by orders of magnitude.

What is Columnar storage?

Row orientation versus columnar orientation

Row-oriented storage, used by transactional databases like PostgreSQL, MySQL, and SQLite, writes each complete record to disk as a unit. Reading a single record requires only one sequential read, which is efficient for OLTP workloads where you retrieve one customer record, update one order row, or insert one payment. ### Query patterns that reward column-first reads

Analytical queries have different access patterns. SELECT revenue, region FROM orders WHERE order_date > '2026-01-01' touches only two columns out of perhaps fifty in the orders table. In a row-oriented store, the database reads all fifty columns to reach the two you want, wasting 96% of the I/O.¹

Columnar storage eliminates that waste. Only the revenue and order_date columns are read from disk, and the region column, if not in the WHERE clause, is skipped entirely. Furthermore, all values in a column share the same type, which allows compression algorithms like dictionary encoding, run-length encoding, and bit packing to achieve ratios that are impossible on mixed-type row data.²

Formats that use columnar storage

Apache Parquet and Apache Arrow IPC (Feather v2) are the dominant columnar file formats in the data engineering ecosystem.³ In Parquet, data sits in row groups (horizontal partitions of the data), where each column within a row group is stored as a column chunk with compression and per-chunk statistics (minimum, maximum, null count). Arrow organises data as record batches with the same columnar layout but without the row group statistics, optimising for in-memory speed over storage compactness.

Columnar formats in DuckDB and the workbench

As a columnar analytical engine, DuckDB's internal file format (the .db file used by the DuckDB CLI) follows the same layout principle. Outside of the Parquet and Arrow ecosystem, ORC (Optimized Row Columnar) serves Hive and Spark deployments, though it is less common outside those environments.⁴ The workbench loads both Parquet and Arrow files into DuckDB-WASM, so you get column pruning and filter pushdown on Parquet and fast streaming reads on Arrow without changing your SQL.

ORC and Parquet share the columnar principle but differ in ecosystem fit, so a Hive or Spark pipeline tends to standardise on ORC while a Python or DuckDB pipeline tends to standardise on Parquet. The underlying win is the same in both: queries read narrow column slices instead of whole rows. Because the workbench focuses on Parquet and Arrow, the columnar benefits you get are pruning for Parquet and fast column scans for Arrow, which cover the typical browser analytics workload.

Why columnar storage matters in the SQL Workbench

The SQL Workbench runs DuckDB-WASM, and DuckDB is a columnar analytical engine. When you load a Parquet or Arrow file, DuckDB reads only the columns your query references, a behaviour called column pruning. A query touching three columns out of forty reads roughly 7.5% of the bytes in a Parquet file.³ Parquet's row group statistics allow filter pushdown: if your WHERE clause filters on a date column, DuckDB reads the per-row-group minimum and maximum date values from the Parquet footer and skips entire row groups that cannot contain matching rows. Both optimisations make exploratory analytics on large files fast enough to run in a browser tab. For CSV files (which are row-oriented text), DuckDB still applies its columnar execution engine, but the disk read is always a full file scan because CSV has no column-level structure.

Compression advantages of columnar formats

Columnar formats compress far more efficiently than row-oriented formats because all values in a column share the same type. A column containing 10 million sequential order IDs compresses to near zero with delta encoding: store the first value and then the difference between each adjacent pair.⁵ A column with only four distinct string values (North, South, East, West) across millions of rows compresses to a small dictionary plus a column of integer indexes; the compressed column may occupy a fraction of the original bytes.

Dictionary encoding and GROUP BY performance

Parquet automatically applies dictionary encoding to low-cardinality columns, and DuckDB exploits this during query execution. When you run GROUP BY country_code on a Parquet column with 50 distinct country codes, DuckDB operates on integer dictionary indexes rather than on the raw string values, making the comparison as fast as an integer sort. For high-cardinality columns like UUIDs or free-text fields, dictionary encoding provides no benefit and Parquet falls back to general-purpose compression. Understanding this trade-off helps you design Parquet schemas where the columns you GROUP BY most frequently are low-cardinality.

When to convert CSV or JSON to a columnar format

When your data analysis involves a file you query repeatedly with different filters or aggregations, converting from CSV or JSON to Parquet pays off quickly. CSV files require a full scan for every query because their row-oriented layout gives DuckDB no way to skip non-matching rows. After converting to Parquet, queries with selective WHERE clauses on sorted columns can skip entire row groups, and queries that touch only a few columns read only those columns from disk.

The conversion is one query in the workbench: load the CSV or JSON file, run SELECT * FROM tablename (or any transformation you want applied permanently), then export as Parquet. The downloaded Parquet file is your new analytical baseline. For a CSV with 50 columns where most queries touch 3 to 5, the Parquet version may be substantially smaller and faster to query.⁶ Keep the original CSV as a backup; use the Parquet file as the working copy.

Designing columnar files for repeated filters

Columnar storage works best when the file layout matches the queries you run most often. A file that is repeatedly filtered by region, date, or status benefits from writing those columns in a stable order and sorting by the filter column when the upstream pipeline can afford it, because the sort order determines how effectively DuckDB can skip irrelevant row groups on future queries.

Sort by the column your WHERE clause uses

If most future queries filter on order_date, sort the exported Parquet by order_date before saving it. DuckDB can then compare the query predicate against row group min and max values and skip chunks that cannot match, instead of reading every row group. This pre-sort step is the single most effective optimization you can apply to a Parquet file that will be queried repeatedly with range filters, and it costs nothing extra during the initial export.

Try in the tool

What Is GeoParquet Format?

A GeoParquet file lets a map travel through the same storage layer as analytical tables. It stores vector geometry as Well-Known Binary (WKB) in a standard Parquet BYTE_ARRAY column, and records coordinate reference system and geometry type metadata in the Parquet file-level key-value metadata.

What is GeoParquet format?

Structure and the WKB geometry encoding

Well-Known Binary (WKB) is an ISO standard binary encoding for geometry types defined by the OGC Simple Features specification. A WKB geometry begins with a byte order marker, followed by a type code (1 for Point, 3 for Polygon, 6 for MultiPolygon, and so on), and then the coordinate values as IEEE 754 double-precision floats. ### Geometry as a WKB column

GeoParquet stores one WKB byte sequence per feature row, in a Parquet BYTE_ARRAY column whose name is typically geometry. The Parquet file-level metadata records which column contains geometry, what geometry types appear (useful for tools that need to choose a renderer), and the coordinate reference system. Coordinate reference systems are encoded as PROJJSON strings or EPSG authority codes, both of which are sufficient for GIS tools to project coordinates correctly.

Geospatial ecosystems that produce GeoParquet

GeoParquet emerged as a replacement for ESRI Shapefile in modern geospatial pipelines. GeoPandas (Python) writes GeoParquet via gdf.to_parquet() since GeoPandas 0.10.² QGIS supports GeoParquet import and export through its GDAL driver, which has included native GeoParquet support since GDAL 3.8.³ PostGIS does not write GeoParquet directly but can produce it via DuckDB with the spatial extension using COPY query TO 'output.parquet' (FORMAT PARQUET). The Overture Maps Foundation publishes its open map dataset (places, roads, buildings, and administrative boundaries) in GeoParquet format, making it one of the largest publicly available GeoParquet datasets.⁴

Cloud and desktop support for GeoParquet

Wherobots and cloud data warehouses with spatial extensions increasingly support GeoParquet as a standard exchange format. Major platforms including BigQuery, Snowflake, and Databricks have added native GeoParquet ingestion, which means a file you prepare in the workbench can flow directly into a production analytics pipeline without an intermediate conversion step. On the desktop side, QGIS and GDAL have supported GeoParquet for several releases now, so the format bridges the gap between local exploration in the workbench and cloud-scale spatial analysis without requiring you to maintain parallel Shapefile and Parquet copies of the same dataset.

Keeping a single GeoParquet file as the exchange format also simplifies version control and sharing, because one file replaces the multi-file Shapefile bundle that older pipelines still require. When a team standardises on GeoParquet, the workbench becomes a quick inspection step before the file moves into a warehouse or a GIS desktop tool. The metadata that records geometry type and CRS travels with the file, so downstream tools project coordinates correctly without extra configuration.

How the workbench handles GeoParquet

DuckDB reads GeoParquet files through its standard Parquet reader.⁵ The geometry column appears as a BLOB type in the schema because DuckDB-WASM does not load the spatial extension by default. You can query all non-geometry attribute columns freely: SELECT name, population, country_code FROM places ORDER BY population DESC returns correct results with the geometry column simply excluded. The geometry column can be selected and included in a Parquet export; the WKB bytes carry through intact, so a downstream GIS tool with GeoParquet support can reconstruct the geometry from the exported file. Spatial predicates like ST_Intersects, ST_Within, and ST_Distance are not available without the spatial extension.

Querying attribute data without the spatial extension

For GeoParquet files where you only need to filter or aggregate on non-spatial attributes, the spatial extension is not required. You can SELECT, GROUP BY, filter, and sort on any attribute column alongside the geometry column as you would with any other Parquet file. A GeoParquet file from the Overture Maps places dataset contains columns like name, country_code, population, and categories alongside the geometry column. A query like SELECT name, country_code, population FROM places WHERE country_code = 'DE' ORDER BY population DESC LIMIT 20 works without any spatial function because it only touches attribute columns.

The geometry column appears as BLOB in the DuckDB schema. You can include the geometry column in your query results and in Parquet exports; the WKB bytes carry through intact. A downstream GIS tool that reads GeoParquet can reconstruct the geometry from the exported file even when the workbench treated the column as an opaque binary blob during the query session.

Exporting filtered GeoParquet subsets

Filtering a GeoParquet file to a subset of features and exporting the result to Parquet produces a file that retains the WKB geometry column. The workbench Parquet export passes the BLOB column through without modification. GeoParquet file-level metadata (CRS, geometry type annotation stored in the Parquet key-value metadata) is not propagated by the workbench export, but most GIS tools can still read the WKB geometry directly when told which column holds the data.

Exporting attribute columns to CSV for tabular analysis

For tools that accept CSV rather than Parquet, exclude the geometry column from your SELECT and export only the attribute columns: SELECT name, country_code, population FROM places WHERE country_code = 'DE'. The resulting CSV contains no binary data and opens cleanly in any spreadsheet or data tool. Use the workbench for attribute analysis and a GIS tool like QGIS or GeoPandas for spatial operations; the combination covers the majority of GeoParquet use cases without requiring the spatial extension.

Reading GeoParquet metadata before spatial work

GeoParquet files can look like ordinary Parquet files until you inspect the geometry metadata. The key named geo tells downstream tools which column stores WKB, which CRS to apply, and which geometry types the file contains. Without checking this metadata first, you risk misinterpreting coordinates or exporting a file that downstream GIS tools cannot render correctly.

Check CRS before projecting coordinates

Run a schema query or inspect the source metadata before projecting coordinates. If a GeoParquet file uses WGS 84, longitude and latitude are degrees; if it uses a projected CRS, the same coordinates may represent metres or feet. Mixing up CRS interpretations is one of the most common sources of spatial analysis errors, so verifying the coordinate reference system before you project or measure distances prevents subtle mistakes that can invalidate downstream results. The bounding box metadata also helps you confirm the geographic extent before committing to a full spatial analysis workflow.

Try in the tool