Debugging and Exploring Parquet Files with Local SQL Queries

You open a 12 GB Parquet file in a hex editor to confirm a pipeline wrote the right timestamps. Beyond the initial metadata page, the file looks like random noise. Timestamps stored as INT96 render as twelve unintelligible bytes per value, with eight bytes encoding the elapsed nanoseconds and four bytes holding the Julian day offset; without a type declaration those bytes render as unintelligible gibberish.¹

What follows is a loop: rerun the export process, inspect the pipeline log for the declared schema, open the file in a different viewer, repeat. The cycle eats an afternoon. Loading the same file into a local SQL workbench triggers a different response entirely: column names, types, and row counts appear in seconds without touching a CLI, a schema decoder, or a staging pipeline. This post covers four specific Parquet inspection patterns, schema verification, row-group offset reading, null-cluster detection, and partition-gap checking, and moves you from uncertainty to verified, detailed inspection before any query reaches a target database.

Why Parquet files resist casual inspection

Binary structure and row group offsets

Columnar formats do not serialize rows into a contiguous memory block. Each column writes its own independent chunk, and those chunks are compressed and then written to disk in a defined structure of row groups followed by column chunks within each row group. The result is that byte one of a Parquet file maps to neither the first row nor the first column in any straightforward way. Finding how many rows a file holds requires reading the file footer first. The file footer stores row group and column-chunk metadata as a unified block at the end of the file, and that metadata tells the reader where to jump within the file to access any column chunk. Reading a Parquet file linearly from the start will land you in the middle of a compressed column stripe long before you encounter any structured row boundaries.²

When you open a Parquet file in a plain text viewer, the data reads like static. Columns that should contain timestamps show as ASCII noise, and numeric columns look no different from serialized text. Columnar formats deliberately separate physical storage from logical structure, so the first byte of a Parquet file bears no relation to the first column of the first row in any intuitive way. Every row group within the file contains its own column-chunk statistics and encoding decisions, meaning the data representation can shift between groups without changing the schema declaration.

A timestamp column written as INT96 by one pipeline, for instance, occupies twelve bytes per value, with eight bytes encoding the elapsed nanoseconds and four bytes holding the Julian day offset; without a type declaration those bytes render as unintelligible gibberish.¹ Because the file footer at the end of the file stores row group and column-chunk offsets that a reader must decode before accessing any data, a hex editor leaves you reading backward through the file trying to locate columns before you know where they start. A column-oriented store does not serialize a row into a contiguous block. Instead, each column writes its own independent chunk, and those chunks are then compressed and written into their column-chunk locations. The file footer stores row group and column-chunk metadata, but extracting even rough statistics requires parsing both the footer and the column-chunk metadata sections before you can count how many rows the file holds.² A pipeline that wrote a daily export and you want to verify it used the expected partitioning logic needs to confirm row counts per partition key, and while a hex editor can show you raw bytes, it completely fails to interpret column encodings, identify type mismatches, or translate internal offsets back to logical rows, meaning that exact confirmation becomes impossible without manually walking the file footer.

The practical consequence is that quick data verification requires either a purpose-built decoder or a tool reading columnar metadata internally. Checking that an export succeeded, that a partition key populated correctly, or that no nulls snuck in where integers were expected is a core verification task. Writing a script to dump the footer and decode the row group offsets works, but it is overkill when you just need five seconds inside a data file. Running the Parquet file query tool that lets you inspect schemas and run SQL locally in the browser exposes column names, types, and statistics immediately without CLIs, schema decoders, or export pipelines.

Schema evolution and encoding drift

Different pipeline generations do not necessarily write the same column types. An INT96 timestamp from an older Parquet pipeline occupies twelve bytes per value, with eight bytes encoding the elapsed nanoseconds and four bytes holding the Julian day offset reading little-endian; the INT96 physical type is a legacy three-part format in the Parquet encoding specification, and many readers that do not expose it explicitly will silently produce wrong numbers rather than throw a type error.¹ See the Apache Parquet encoding specification that defines how INT96 timestamps and other physical types are stored on disk for the full layout. DuckDB-Wasm can query Parquet files directly from a browser file handle, which makes it a practical local reader for Parquet files in the browser.³ Consequently, because visual inspection cannot distinguish whether a legible-looking number in a hex editor represents a correctly-decoded INT32 or a misparsed INT96 whose bytes happened to align with readable ASCII, relying on a robust engine like DuckDB-WASM becomes essential. Closed-source pipelines that have evolved across three teams over five years accumulate these encoding decisions invisibly. DuckDB-WASM handles that hardest part of the job. It lets you ask the engine to expose declared types and row counts from the file instead of manually walking the footer. Reading Parquet with the wrong encoding produces no error. It just gives you the wrong numbers.

Inspecting schema and column types before writing a single query

Before you write any data-verification query, confirm the actual column types are what you expect them to be. The schema accordion shows every table name, column name, and declared SQL type immediately without requiring you to type anything. A column that appears to hold integers cannot be validated by appearance alone. If it is declared as VARCHAR, any range or comparison against numeric bounds will produce lexicographical results instead of the expected numeric values, which is why you confirm the declared type before writing a query.

Confirming column nullability with the schema metadata before writing a sanity-check query prevents a whole class of silent bugs. A COUNT that returns a value for every row even on a column that carries sporadically missing values is the most common symptom of that confusion. The schema metadata tells you upfront whether a column is nullable, eliminating guesswork.⁴ Notice also the ordinal_position column in this same query. It confirms the display order DuckDB assigned within each table, which matters when columns were added in later schema migrations and you need to know whether the latest schema version matches the one a downstream report expects.

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
ORDER BY table_name, ordinal_position;

Typing SELECT * FROM <table_name> LIMIT 5 is still the fastest move when a table name is ambiguous and you need to confirm the data slice. A preview row catches type mismatches a schema list cannot. A column declared as DOUBLE holding text-stored fractions silently breaks downstream comparisons. The schema accordion gives you the map, but a limit query gives you the territory. CapyToolkit’s browser-based SQL workbench that runs DuckDB queries against Parquet files locally runs this inspection without any file upload — open Parquet, expand the schema accordion, confirm column types, then write your first aggregation query with confidence.

Filtering, grouping, and aggregation for quick sanity checks

Once you know what the columns are, a small set of query patterns covers most routine validation work. Each query type targets a specific question about the data.

Query type	What it reveals
GROUP BY on suspected partition column	Total row count per group, reveals gaps in expected partition coverage
SUM / COUNT on numeric column	Min, max, and mean without a stats notebook
COUNT(*) before and after WHERE	Row-completeness signal per filter
HAVING on aggregated result	Subset quality after group collapse

The GROUP BY on a suspected partition key is the most underrated diagnostic move in the Parquet toolkit. A Parquet file written by a partitioned pipeline frequently shows the partition values as a column, city, date tier, or product category. Running a GROUP BY on that column produces a distinct-key row count. If the pipeline wrote the day-partition for seven dates but the query returns six, you know a partition is missing before you export anything.

SUM and COUNT on a numeric column serve a similar function. They surface basic distribution stats without opening a statistics notebook or writing to a temp table. A numeric range check, verifying that the minimum and maximum of a price or temperature column fall within expected bounds, runs in one line and tells you whether the export silently truncated or inverted values. CapyToolkit’s developer-tool suite offers local file hashing and checksum tools alongside the SQL workflow for a complete verification pipeline available at CapyToolkit’s free suite of browser-based developer tools that run entirely locally.

The distinction between HAVING and WHERE trips up more engineers than any syntax question. WHERE filters rows at scan time. HAVING filters groups after aggregation.⁵ Put a condition in the wrong clause and you get different row counts, and those differences are particularly visible in queries that compute SEM scores or t-tests across partitioned data. If your statistical calculations are returning unexpected values, check that HAVING is only present on grouped aggregates, not raw numeric columns.

Data quality and null analysis

Counting null rates precisely

Because generic row counts easily miss them, specific null patterns carry significant diagnostic meaning in Parquet files. A column reporting all-valid values in the first row group may still carry sparse nulls deeper in the file, discarded silently until an explicit check surfaces them.

A simple count difference surfaces the null rate per column without writing to a temp table. Subtracting COUNT(your_target_column) from COUNT(*) gives the exact number of absent values in that specific field, and dividing by COUNT(*) produces the absence rate as a percentage.⁶ That single comparison replaces a temp-table write for every null column you need to audit.

Finding adjacency and sentinel values

Structured null sentinels, empty strings, -1, NULL_PLACEHOLDER, require a different check because DuckDB does not classify them as SQL NULL. These values pass a null filter even though they carry no meaningful content. A CASE WHEN pattern detecting sentinel characters catches the cases a basic count comparison cannot see, and running LAG() or LEAD() over a column surfaces adjacent null clusters that count calls alone miss. Running LAG() over a column and filtering where current_value IS NULL differs from prior_value tells you whether nulls form runs, consecutive nulls at the start of a group, in the middle of a sequential field, or at the tail of a dataset.⁷ Distinct null rates tell you how many are absent; the window function tells you exactly where. The DuckDB window function explorer for running LAG and LEAD patterns against local files in the browser makes these null-cluster checks interactive without installing DuckDB locally.

Cross-file comparison using file hashes

Even when a Parquet export re-runs cleanly on the exact same source dataset, it rarely guarantees byte-identical output because non-deterministic encoding decisions, metadata timestamps, and row group ordering inherently differ between executions. Comparing file SHA-256 hashes that produce a fixed 256-bit fingerprint for detecting byte-level differences between files between the original export and a freshly fetched replacement rules out silent corruption such as a truncated file or a misrouted response, which row-count alone cannot catch.⁸ A checksum mismatch immediately tells you the pipeline did not reproduce the file exactly as it stood before.

Hash verification works as a validation step before you open a new export in a workbench. Load the new file, hash it, and compare the hash against a published baseline value, or against a self-signed hash from the prior run, rather than trusting the pipeline output metadata. CapyToolkit’s browser-based file hash verifier computes SHA-256, SHA-512, SHA-1, and MD5 checksums for any file and compares it against a reference checksum you supply. The file never leaves your machine during the computation. Use it as the gate: pass the hash check first, then open the export.

One practical workflow is to hash the original file immediately after you save it, run the query verification pass, then export the cleaned data and hash the replacement. If both hashes match the expected values, the ETL step preserved data integrity end-to-end without any changes you cannot account for.

Building a repeatable validation query

Wrapping each check as a CTE block turns a scattered set of queries into a single schema audit you can run repeatedly. The schema, null-rate, and range checks each produce discrete result rows. Composition as CTEs means the entire audit runs from one RUN button and produces one ordered result set instead of five separate executions.

WITH unpivoted AS (
  SELECT *
  FROM source_data
  UNPIVOT (
    val
    FOR col IN (rating, budget, gross)
  )
),
null_rates AS (
  SELECT
    col,
    SUM(CASE WHEN val IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS absent_pct
  FROM unpivoted
  GROUP BY col
),
range_checks AS (
  SELECT 'rating_min'  AS check_name, MIN(rating)        AS value FROM source_data UNION ALL
  SELECT 'rating_max',                                MAX(rating)                      UNION ALL
  SELECT 'gross_min',                                MIN(gross)                       UNION ALL
  SELECT 'gross_max',                                MAX(gross)
)
SELECT col AS check_name, ROUND(absent_pct, 1) AS value FROM null_rates
UNION ALL SELECT check_name, value FROM range_checks;

Exporting the audit result as a local JSON file and diffing it against the previous run produces a lightweight regression signal that version control can track. A JSON diff tool compares every key and value in serialized form, so a change in any column’s null rate comes through as a clean diff line rather than a visual scan across a spreadsheet. Any change in null rates or column ranges flags a schema or pipeline shift without requiring you to re-read every row manually. PIVOT summarises partition-level coverage across any comparison key, removing the need to export intermediate aggregates to a separate notebook and reconnect them after the export. A table with a few hundred MB of partition data is manageable in SQL, but folding those aggregations into a notebook column after export adds a manual step that defeats the purpose of an in-browser local environment.

Building on this fully local workflow, the end result is a schema-audit SLI, a schema-level indicator, that runs entirely in the browser, requires zero uploads, and replays identically next week or next month. By running CapyToolkit’s SQL Data Workbench as your interface, you execute the audit, perform the hash check with the File Hash Verifier, and manage the download entirely as local operations. No round-trips to a server, no accounts, no accounts or email. Just drop the file, run the CTEs, and compare the JSON output against a stored baseline.

Sources

1. 1.

   Apache Parquet Format, “PARQUET-861: Document INT96 timestamps by xhochy · Pull Request #49,” github.com, accessed June 2026. https://github.com/apache/parquet-format/pull/49

2. 2.

   Apache Parquet, “File Format,” parquet.apache.org, July 2024. https://parquet.apache.org/docs/file-format/

3. 3.

   DuckDB Foundation, “Data Ingestion,” duckdb.org, accessed June 2026. https://duckdb.org/docs/lts/clients/wasm/data_ingestion

4. 4.

   Microsoft, “COLUMNS (Transact-SQL),” learn.microsoft.com, November 2025. https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql?view=sql-server-ver16

5. 5.

   Microsoft, “Use HAVING and WHERE Clauses in the Same Query,” learn.microsoft.com, March 2025. https://learn.microsoft.com/en-us/sql/ssms/visual-db-tools/use-having-and-where-clauses-in-the-same-query-visual-database-tools?view=sql-server-ver16

6. 6.

   DuckDB Foundation, “Aggregate Functions,” duckdb.org, accessed June 2026. https://duckdb.org/docs/current/sql/functions/aggregates

7. 7.

   DuckDB Foundation, “Window Functions,” github.com, accessed June 2026. https://github.com/duckdb/duckdb-web/blob/main/docs/1.2/sql/functions/window_functions.md

8. 8.

   National Institute of Standards and Technology, “FIPS 180-4, Secure Hash Standard (SHS),” csrc.nist.gov, August 2015. https://csrc.nist.gov/pubs/fips/180-4/upd1/final