Developer Tools

Local-First Data Analysis in the Browser: SQL Workbench and Big Log Explorer for Zero-Upload Analytics

Updated:  ·  Published:  ·  20 min read
Local-first analytics with SQL Workbench and Big Log Explorer

You have a 400 MB Parquet export from production. It contains customer IDs, revenue events, and session timestamps. Your options: spin up a local Postgres instance, upload to a cloud notebook, or push to a managed warehouse. Each one triggers a compliance review, a network hop, or a cost center approval. What if you could just drop the file into a browser tab, write SELECT customer_id, SUM(revenue) FROM data GROUP BY 1, and get the answer in seconds without the file ever leaving your machine?

That scenario is not hypothetical. CapyToolkit’s SQL Data Workbench loads Parquet, CSV, JSON, SQLite, Excel, Arrow, Avro, DBF, and GeoParquet directly in the browser using DuckDB-WASM. Big Log Explorer indexes 500 MB-plus log files with Web Workers and IndexedDB. Together they form a complete local-first analytics stack: raw logs become structured tables, structured tables become SQL queries, and SQL results export as typed Parquet for downstream pipelines. No server, no upload, no account.

This post walks through the full workflow. You will learn which formats each tool handles natively, how to chain them for end-to-end analysis (logs → patterns → structured export → SQL → Parquet), and the specific DuckDB SQL patterns that replace the pandas code you have been copy-pasting from Stack Overflow. By the end, you will have a zero-upload, zero-install, zero-cost analytics stack that works offline and keeps sensitive data on your disk.

Why Local-First Matters for Data Work

When your exports contain personal identifiers, revenue figures, or authentication tokens, safeguarding your data sovereignty becomes a critical engineering constraint rather than a corporate buzzword. Under strict regulations like GDPR, HIPAA, and SOC2, uploading production files to third-party cloud environments is frequently forbidden. By adopting a local-first browser tool, you sidestep these compliance bottlenecks entirely, processing raw data locally while avoiding security questionnaires and legal reviews altogether.

In addition to bypassing these hurdles, processing your data within a browser tab yields a massive performance advantage. Uploading a 500 MB file over a typical office connection takes minutes. DuckDB-WASM starts querying the same file in milliseconds after the initial engine load, and that engine caches in the browser for subsequent sessions. The engine is a full WebAssembly port of DuckDB’s columnar analytical database that runs entirely in the browser with no server component, as documented in the official DuckDB-WASM launch post. No queue time, no cold-start warehouse provisioning, no credit-card billing for ad-hoc exploration. The initial download is roughly 2 MB of WebAssembly, and every file after that opens against the cached engine.1

Cost scales with usage in the cloud. Locally it stays at zero. Your laptop can comfortably handle files into the hundreds of megabytes because DuckDB streams columnar data and the Web Worker architecture keeps the main thread responsive. IndexedDB persistence for log indexes means you can close the tab and reopen it without re-indexing. For privacy, the browser deletes the in-memory database on reload, a deliberate design choice that prevents accidental data retention. With modern desktop browsers allocating several gigabytes of local space to IndexedDB, as detailed in the MDN guide to storage quotas and eviction, you can comfortably process large analytical datasets on your machine without paying for cloud warehouse credits.2

Verifiability is the final piece. Open DevTools Network tab, drop a file, run a query, and watch zero requests fire. That transparency builds trust that no privacy policy can match. CapyToolkit’s SQL Data Workbench and Big Log Explorer both operate this way. These browser-based tools from CapyToolkit never upload your data, and you can verify this yourself in any browser with the Network panel open. When a stakeholder asks where the data went, you can show them an empty request log instead of a terms-of-service document.

Ingesting Columnar Data with SQL Workbench

Parquet and GeoParquet

DuckDB reads Parquet natively with column pruning and filter pushdown, so wide files with hundreds of columns stay fast.3 Only the columns referenced in your query make it to the decoder; everything else stays on disk. Geometry columns in GeoParquet arrive as WKB binary. You can still filter rows by spatial predicates and query attribute columns without loading a spatial extension. Export back to Parquet via COPY ... TO for lossless round-trips, preserving the exact column types and statistics. The query Parquet files in your browser variant demonstrates this path end to end with a real dataset.

Arrow and Feather

Apache Arrow and Feather formats stream through a highly optimized JavaScript bridge that maps the parsed file buffers directly to DuckDB’s in-memory columnar layout with minimal CPU deserialization overhead.4 Drop orders.arrow and the tool immediately creates a view named after the file stem. Run SELECT * FROM orders. The on-disk columnar format is identical to the in-memory representation, so Python pyarrow and Rust arrow2 pipelines pick up the data without any conversion step.

The bridge works by mapping Arrow’s C data interface to DuckDB’s internal array format without further copying. Releases happen automatically when the view drops, so memory pressure stays low even for large files.

CSV and TSV

DuckDB sniffs delimiters and infers types automatically, though the sniffer examines only a sample of rows before committing to a schema.5 DuckDB processes tab-separated files using an explicit \t delimiter so they parse correctly on the first pass. Large files stream chunk by chunk with no full-file memory allocation. The query CSV and TSV files with SQL variant walks through a complete example with a messy real-world export.

One subtle behavior: if a column first appears as integers but later contains a string, DuckDB widens that column to VARCHAR rather than failing the entire load. You can override the inferred type with explicit CAST in your query.

To help you select the optimal format for your browser session, the table below details how each standard format interacts with the WebAssembly runtime:

FormatLoader PathFilter PushdownPartition PruningParquet Export
Parquet / GeoParquetNative DuckDBYesYesYes
Arrow / FeatherJS Bridge → DuckDB ViewYesNoYes
CSV / TSVNative DuckDBYesNoYes
JSON / NDJSONNative DuckDBPartialNoYes
SQLitesql.js (WASM)NoNoNo
Excel / DBF / AvroJS Parser → DuckDBNoNoYes

Ingesting Semi-Structured and Legacy Data

JSON and NDJSON

A single reader covers both JSON arrays and newline-delimited JSON. Automatic schema inference samples the first N records, and nested objects remain queryable through json_extract and the ->> operator. The query JSON and NDJSON files with SQL page shows the extraction patterns with nested payloads from API responses.

For NDJSON, each line parses independently, so a malformed line in a 100 MB log export becomes a null row you can filter out without breaking the entire load. Schema inference merges types across samples: if the first 100 rows have an integer user_id but row 101 has a string, the column becomes VARCHAR. You can force a type with CAST(json_extract(payload, '$.user_id') AS BIGINT) in your query.

The ->> operator returns nested scalars as text. json_extract(payload, '$.user.profile.name') retrieves the nested field. For arrays, json_extract(payload, '$.tags[0]') accesses the first element, while UNNEST(json_extract(payload, '$.tags')) explodes the entire array into rows. These operators work on JSON columns inside Parquet files too, not just standalone JSON.

SQLite Databases

SQLite files load through sql.js (WebAssembly), which reads the entire database into memory.6 Every table in the .sqlite or .db file becomes available immediately, but they speak standard SQLite SQL rather than DuckDB dialect. This distinction matters when you reach for QUALIFY or PIVOT.

Because sql.js materializes the full database in the browser’s heap, the practical limit sits around 200-300 MB depending on available memory. For larger SQLite files, consider exporting to Parquet first using a local script, then loading the Parquet in SQL Workbench. Within the tool, you can run SELECT sql FROM sqlite_master WHERE type = 'table' to inspect schemas, then query any table directly. Cross-table joins work as long as all tables live in the same file.

Excel, DBF, Avro, and GeoParquet

Excel workbooks expose each sheet as a separate table with headers taken from row one. The parser ignores merged cells and formatting entirely, reading only the raw cell values. The parser handles .xlsx only. Legacy .xls is not supported. DBF files, still common alongside ESRI shapefiles and in legacy business systems, parse in JavaScript before handing off to DuckDB as a single queryable table. Avro containers carry their schema in the header, so column types set automatically without inference. GeoParquet stores geometry as WKB while attribute columns remain fully queryable, same as standalone Parquet.

The Excel path is worth understanding for stakeholder handoffs. A finance team drops a multi-sheet workbook; you load it, query across sheets with UNION ALL, and export a clean Parquet for the data lake. DBF support exists because geospatial workflows still produce dBASE files alongside shapefiles. Avro appears in Kafka and Hadoop ecosystems where the schema travels with the data. Each of these “legacy” formats represents a real integration point that would otherwise require a custom script.

SQL Techniques That Replace Python Pandas

Window Functions Without Subqueries

ROW_NUMBER(), RANK(), and DENSE_RANK() work over partitions with full ORDER BY and frame clause support. LAG() and LEAD() enable sessionization and funnel analysis without self-joins. The QUALIFY clause filters window results directly without wrapping the query in a subquery. Sessionization is the classic pandas pain point. In DuckDB you compute a session boundary with LAG() to measure the gap from the previous row, then accumulate those boundaries with a second window function. DuckDB requires this split because window functions cannot nest directly inside another window function’s argument.7 The two-step approach still runs in a single pass over the data, but the lag check happens in one projection and the running sum in the next, avoiding the binder error that a fully nested expression would trigger.

SUM(CASE WHEN ts - LAG(ts) OVER (...) > INTERVAL '30 MINUTES'
         THEN 1 ELSE 0 END) OVER (...)

The inner window function computes each row’s time gap. The outer window function then accumulates a running sum of session boundaries. No intermediate DataFrame, no memory spike, no index reset.

Funnel analysis follows the same pattern. LAG(event_type) over a user-ordered partition lets you detect step transitions. QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1 keeps only the first event per user. These patterns compose: sessionize first, then funnel within sessions, all in one query.

PIVOT and UNPIVOT for Wide/Long Transformations

PIVOT aggregates and rotates in one statement. UNPIVOT melts wide tables back to tidy format. Together they replace pivot_table() and melt() with declarative SQL that the optimizer can reason about.

A concrete example: you have daily event counts per user and want a weekly summary matrix. In pandas this is groupby(['user_id', 'week']).event_type.value_counts().unstack().fillna(0). In DuckDB:

PIVOT events ON date_trunc('week', ts) USING COUNT(*)
GROUP BY user_id;

The ON clause defines the columns to become headers, USING specifies the aggregation. UNPIVOT melts them back to tidy format in one statement.

JSON Extraction and Nested Data

json_extract(col, '$.path') and the ->> shorthand pull scalar values from JSON columns. UNNEST flattens arrays inline with parent columns preserved. This works on JSON columns in Parquet or standalone JSON files.

Consider a Parquet file where one column contains {"items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]}. To get line-item rows:

SELECT order_id,
       item.sku,
       item.qty
FROM orders,
     UNNEST(json_extract(items, '$.items')) AS item;

The UNNEST with a JSON path produces a struct column; dot notation (item.sku) accesses struct fields to give you flat rows from embedded arrays.

Array and List Operations

list_aggregate, list_transform, and list_filter process list columns without leaving SQL. UNNEST with WITH ORDINALITY preserves element position for ordering-dependent logic. These replace explode() plus groupby().apply() patterns that fragment pandas pipelines.

list_aggregate(arr, 'sum') computes the sum of a list column. list_transform(arr, x -> x * 2) doubles every element. list_filter(arr, x -> x > 0) keeps only positive values. These operate vectorized on the column, not row-by-row in Python. Combined with UNNEST ... WITH ORDINALITY, you can rank items within each list while preserving the parent row context.

Code Snippet: Pandas vs DuckDB for Sessionization and Pivot

df['session'] = (df['ts'].diff() > pd.Timedelta('30m')).cumsum()
pivot = df.pivot_table(index='user_id', columns='event_type', values='session', aggfunc='count')
-- DuckDB: isolate the lag boundary check first, then accumulate
WITH lagged AS (
  SELECT *,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 MINUTES'
              THEN 1 ELSE 0 END AS is_new_session
  FROM events
)
SELECT user_id,
       COUNT(*) FILTER (WHERE event_type = 'view') AS views,
       COUNT(*) FILTER (WHERE event_type = 'click') AS clicks,
       COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchases
FROM (
  SELECT *,
         SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
  FROM lagged
) GROUP BY user_id;

The SQL for Pandas users guide maps every common pandas operation to its DuckDB equivalent, including groupby().agg(), merge(), query(), apply(), and rolling window functions.

From Log Lines to Queryable Tables with Big Log Explorer

Pattern Clustering: The Hidden Schema in Your Logs

A Web Worker reads the file in 1 MB chunks via the browser’s File API, parses each line into structured records, and writes batches to IndexedDB, keeping the main thread responsive even for files exceeding 500 MB. This chunked streaming pattern, which avoids loading the entire file into memory at once, is documented in the web.dev guide on processing large local files. Every line is normalized by replacing URLs with <url>, IPs with <ip>, UUIDs with <uuid>, and numbers with <n>. The top 50 templates by count appear in the Patterns panel. Clicking a row filters the viewer to that template in place of awk '{print $1}' | sort | uniq -c | sort -rn. A log line like 2024-01-15 10:23:45 INFO User 12345 logged in from 192.168.1.1 becomes INFO User <n> logged in from <ip>. Every occurrence of that template increments the same counter regardless of the specific user ID or IP address, and the filter stacks cleanly with text search and level pills to drill into failures within that template.

Time-Series Filtering: Zoom Into Incidents

Drag on the log-volume-over-time chart to select a window. The selection combines with level pills (ERROR, WARN, INFO, DEBUG) and text search. Virtual scrolling keeps the DOM light across millions of lines. Only visible rows plus a small overscan buffer exist in the document at any time, and a spacer div sized to the total line count gives the scrollbar correct proportions. The IndexedDB session database deletes on tab close, leaving no residue.

The chart aggregates log lines into time buckets sized automatically based on the time range. Hover a bucket to see the exact count and level breakdown. Drag-select a narrow window around a spike and the viewer jumps to that time range with all filters preserved. This is how you move from “something broke at 3 PM” to “here are the 47 ERROR lines between 14:58 and 15:03” in three clicks.

Supported Formats and Auto-Detection

Format detection runs per line, not per file, so a single log can mix formats. JSONL extracts timestamp, time, ts, @timestamp, datetime plus level, severity, lvl. ISO 8601 lines start with a datetime optionally followed by a bracketed level like [ERROR]. Common Log Format covers Apache and Nginx access logs with status code to severity mapping (5xx → error, 4xx → warn, else info). Lines matching none of these store as raw text and still appear in the viewer and patterns panel. The tool tries each detector in order, and lines that fail all three store with a zero timestamp and “raw” level so they remain searchable and visible in the pattern clusterer.

Exporting Structured Data for SQL Workbench

Filtered view → Export → JSONL or CSV. Drop the exported file into SQL Workbench for joins, window functions, and PIVOT. The round-trip flows: logs → patterns → export → SQL → Parquet → BI tool. Every step stays in your browser.

A typical export from a filtered error window is 5 MB pulled from a 500 MB source: small enough for instant SQL Workbench load, rich enough for deep analysis. You can export multiple windows, such as errors from three different incidents, and union them in SQL Workbench with UNION ALL.

Exporting Results for Downstream Pipelines

CSV downloads instantly from the rendered grid. JSON produces an array of objects, one per row. Excel generates a single-sheet .xlsx for stakeholder handoff. Parquet runs COPY ... TO through DuckDB. The download matches exactly what the SQL engine produced, typed and compressed with ZSTD by default. Parquet export is disabled for SQLite sessions because sql.js does not expose the DuckDB execution path. The export results as Parquet variant explains the engine path.

The format choice depends on the consumer. CSV is universal but loses type information. JSON preserves types but bloats size. Excel satisfies non-technical stakeholders who need a spreadsheet. Parquet is the only format that preserves the full DuckDB type system: timestamps stay timestamps, decimals keep precision, nested structs and lists survive the round-trip. If you are feeding a downstream DuckDB instance, a PyArrow pipeline, or a Parquet-aware BI tool, choose Parquet.

Large result sets stream to the browser’s download API without materializing the full file in memory. A 50 million row aggregation exports as Parquet in chunks, each chunk compressed and written to the download stream as it completes. The progress indicator reflects actual bytes written. For CSV and JSON, the grid data already exists in memory, so the download is nearly instant up to the browser’s blob size limit (typically 2 GB). Excel hits that limit sooner because the workbook XML structure adds overhead.

One practical note: if your query returns a massive result set, consider adding LIMIT or writing the Parquet to a named file in DuckDB’s local filesystem first, then downloading. The workbench’s export button is optimized for interactive result sets, specifically the grid you see on screen. For batch exports of entire tables, the COPY statement in a raw query gives you more control over partitioning and compression settings.

Putting It Together: A Combined Workflow

  1. Drop your 500 MB application log (JSONL) directly into Big Log Explorer. A background Web Worker immediately begins indexing the file in 1 MB chunks while you watch the Patterns panel populate in real time.
  2. Filter to level=ERROR in the last two hours using the time chart and level pill. The viewer shows only error lines, and the Patterns panel re-ranks templates within that window.
  3. Export the filtered view as JSONL. With the target error subset isolated, your download finishes in seconds, producing a file that typically lands between 2 MB and 10 MB depending on error volume, with all parsed fields intact: timestamp, level, message, and any JSON keys.
  4. Drop the exported JSONL into SQL Workbench. The schema panel immediately shows the columns: ts (TIMESTAMP), level (VARCHAR), message (VARCHAR), plus any extracted fields like error_code, service, and trace_id.
  5. Run SELECT error_code, COUNT(*) AS freq, MIN(ts) AS first_seen, MAX(ts) AS last_seen FROM data GROUP BY error_code ORDER BY freq DESC. The result ranks error codes by frequency with time bounds, surfacing the most frequent failures at the top.
  6. Pivot by hour for a time-series view: PIVOT data ON date_trunc('hour', ts) USING COUNT(*). The output has one row per error code with hourly columns, ready for a heatmap in your dashboard.
  7. Export as Parquet for archival or dashboard ingestion. The typed Parquet preserves the TIMESTAMP column and exact counts, handed directly to DuckDB’s COPY ... TO execution path without re-serializing the grid.
  8. Every step stays offline, requires no upload, and is fully auditable via the DevTools Network panel.

This pipeline turns an opaque log mountain into a structured, queryable, exportable dataset in minutes. No Docker, no credentials, no network dependency. A colleague can open the resulting Parquet in their own browser, run queries, and verify results without a server ever seeing the original 500 MB of raw logs.

The reverse path works the same way. Pull a Parquet export from your data lake into SQL Workbench, find anomalous user IDs, export them as CSV, then open Big Log Explorer with your raw application logs and search for those IDs in the text filter to see full request context. Both tools share the same local-first architecture and compatible export formats, so you can flow between them in either direction.

When to Use Each Tool (And When to Combine)

SQL Workbench excels with structured or columnar files, SQL-native workflows, joins, aggregations, and typed exports. Big Log Explorer shines on raw log files over 100 MB, unknown schemas, pattern discovery, and time-window drilling. Keep the combination scenarios specific:

  • Combine when the log needs structured analysis, such as error aggregation after big-log pattern discovery, or when SQL results need log-context enrichment for root-cause drill-down
  • Skip the combination when the file is already clean Parquet or CSV, because SQL Workbench handles it directly without needing a log exploration step
  • Skip it also when the log is under 50 MB with a known schema; SQL Workbench handles JSONL natively and avoids the overhead of loading a second tool

The decision tree is practical. If you have a Parquet, CSV, Excel, or SQLite file and you know the schema, open SQL Workbench. You get full SQL, instant schema preview, and Parquet export. If you have a .log, .txt, or .jsonl file and you do not know the structure, or if the structure varies line by line, open Big Log Explorer. The pattern clusterer will show you the schema implicitly, and the time chart lets you zoom to the relevant window.

The combination unlocks workflows neither tool handles alone. A security analyst receives a 2 GB nginx access log. Big Log Explorer clusters patterns, reveals the top 404-generating URLs, and exports the suspicious IP addresses as JSONL. SQL Workbench loads that export, joins it against a Parquet file of known malicious IPs from a threat feed, and outputs a Parquet of confirmed threats. A data engineer debugging a pipeline failure opens the pipeline logs in Big Log Explorer, filters to the failure window, exports the error context, then uses SQL Workbench to correlate error codes with deployment metadata stored in a separate Parquet file.

Do not force the combination when it adds steps without value. A 10 MB JSONL file with a consistent schema loads directly in SQL Workbench. The native JSON reader handles it without needing separate log exploration. A clean 500 MB Parquet file loads straight into SQL Workbench and needs no log exploration at all. The tools are designed to overlap at the edges so you can choose the entry point that matches your starting artifact.

Browser-based tools that process everything locally change what is practical for everyday data work. Open the SQL Data Workbench that runs DuckDB queries on Parquet, CSV, and JSON files locally or Big Log Explorer that indexes and filters 500 MB log files in the browser, drop a file, and start querying. Your data never leaves the tab, and that is the only architecture that scales to zero trust.

Sources
  1. 1.

    duckdb, “duckdb-wasm: WebAssembly version of DuckDB,” github.com, accessed July 2026. https://github.com/duckdb/duckdb-wasm

  2. 2.

    Pete LePage, “Storage for the web,” web.dev, accessed July 2026. https://web.dev/articles/storage-for-the-web

  3. 3.

    DuckDB, “Querying Parquet Files,” duckdb.org, 2026. https://duckdb.org/docs/current/guides/file_formats/query_parquet

  4. 4.

    Apache Arrow, “The Arrow C data interface,” arrow.apache.org, accessed July 2026. https://arrow.apache.org/docs/dev/format/CDataInterface.html

  5. 5.

    DuckDB, “CSV Auto Detection,” duckdb.org, 2026. https://duckdb.org/docs/lts/data/csv/auto_detection

  6. 6.

    sql.js, “Database API,” sql.js.org, accessed July 2026. https://sql.js.org/documentation/Database.html

  7. 7.

    duckdb, “InternalException when nesting window functions via an alias reference (Issue #8462),” github.com, accessed July 2026. https://github.com/duckdb/duckdb/issues/8462

More in Developer Tools