Show HN: Open-source, browser-local data exploration using DuckDB-WASM and PRQL (github.com)
You can play with the demo CSV that’s pre-loaded (GitHub data of text-editor adjacent projects) or upload your own CSV/XLSX file. The tool runs fully in-browser—you can disconnect from the internet once the website loads—so feel free to use sensitive data if you like.
Here’s how it works: You upload a CSV file and then, explore your data as a series of successive data transforms and plots. For example, you might: (1) Remove some columns; (2) Apply some filters (remove nulls, remove outliers, restrict time range etc); (3) Do a pivot (i.e, a group-by but fancier); (4) Plot a chart; (5) Download the chart and the the transformed data. See screenshot: https://imgur.com/a/qO4yURI
In the UI, each transform step appears as a “Block”. You can always see the result of the full transform in a table on the right. The transform blocks are editable - for instance in the example above, you can go to step 2, change some filters and the reactivity will take care of re-computing all the cells that follow, including the charts.
We wanted Pretzel to run locally in the browser and be extremely performant on large files. So, we parse CSVs with the fastest CSV parser (uDSV: https://github.com/leeoniya/uDSV) and use DuckDB-Wasm (https://github.com/duckdb/duckdb-wasm) to do all the heavy lifting of processing the data. We also wanted to allow for chained data transformations where each new block operates on the result of the previous block. For this, we’re using PRQL (https://prql-lang.org/) since it maps 1-1 with chained data transform blocks - each block maps to a chunk of PRQL which when combined, describes the full data transform chain. (PRQL doesn’t support DuckDB’s Pivot statement though so we had to make some CTE based hacks).
There’s also an AI block: This is the only (optional) feature that requires an internet connection but we’re working on adding local model support via Ollama. For now, you can use your own OpenAI API key or use an AI server we provide (GPT4 proxy; it’s loaded with a few credits), specify a transform in plain english and get back the SQL for the transform which you can edit.
Our roadmap includes allowing API calls to create new columns; support for an SQL block with nice autocomplete features, and a Python block (using Pyodide to run Python in the browser) on the results of the data transforms, much like a jupyter notebook.
There’s two of us and we’ve only spent about a week coding this and fixing major bugs so there are still some bugs to iron out. We’d love for you to try this and to get your feedback!
83 comments
[ 3.4 ms ] story [ 166 ms ] threadAre you planning on supporting parquet files any time soon? I'd love an easy way to just drop a parquet file on there and easily visualize time-series data with it (e.g. sensor readings).
Small issue I noticed: When you chart time-series data, the timestamp axis is just represented as an integer rather than a meaningfully formatted timestamp.
> Are you planning on supporting parquet files any time soon?
Yes. This is an oversight on our part - duckdb natively supports Parquet and Arrow files but we just haven't gotten around to adding upload support for those yet. It's a small change - landing in main tomorrow!
On the bug, yes, we've been trying to parse timestamps but it's just been very finicky. I'll try to fix this! Thanks for the report.
However, there's a couple of major differences:
- Datasette - to me - is a tool for devs - it takes a fair bit of tech know-how to understand how to load new data in datasette, install plugins and deploy an instance. I wanted Pretzel to have a "low-ceiling, high-floor" - meaning the tool should work well for non-technical folks but also shouldn't hinder technical folks and power users
- Datasette has a unique way to explore data based on "facets" where you can dig through data. It's a powerful way to explore but I personally don't think Datasette is the best tool for complex data transforms - its strengths lie in exploring datasets instead.
- For accessibility and speed, we wanted something browser local and fast - hence the focus on DuckDB and canvas rendered tables. Datasette requires setting up an instance and that's additional friction for non-tech folks and isn't particularly designed for large datasets (is my impression - I might be wrong)
- Lastly, we have ton of future plans - including in-browser python support via WASM (Pyodide), local LLM support, ability to connect databases etc.
I think there's space for both tools!
In terms of it being a product, things I can think of that would be useful to me.
- Importing/exporting data from Google Sheets/Google Drive.
- Scheduling queries to run and export etc.
Not sure if that really aligns to this specific project being that it runs locally though.
Edit: Having a few standard dimension tables available to join would be great too. I often want to join time from a date dimension table when I'm making time series data for example.
This tool definitely comes from a place of personal need - beyond just handling large files, I've also never really gelled well with the Excel/Google Sheet model of changing data in place as if you were editing text. I'm a Data Scientist and always preferred the chained data transforms you see in things like dplyr (https://dplyr.tidyverse.org/) or Polars (https://pola.rs/) and I feel this tool maps very closely to the chained model.
Also, thank you for the feature requests! Those would all be very useful - we'll put them on the roadmap.
1. Allow to download/upload workflows as files (maybe JSON, or PRQL)
2. Allow to share private URLs by attaching the workflow as a URL parameter
We are building a roadmap upvoting site, we'll add it to the README as soon as it's ready
We'll definitely add a PRQL/SQL block to the UI soon (you will be able to toggle to select which one you'd like).
On that point - PRQL doesn't natively support PIVOT statements. As a hack, we made our own flavor of PRQL with a PIVOT statement and we parse that to SQL CTEs to make it work for now. I will be submitting a pull request to PRQL to add support to get around this.
We're working on saving queries to local storage as well as sharing via downloadable config files (we're thinking a PRQL file with some sugar that when uploaded, recreates the entire transform)
I'll take a look and if it's simply a matter of changing from the PRQL compiler to EdgeQL compiler, then, we should be able to have land in main in short order. It's not the highest priority thing right now though.
Not ideal because it’s just wasting credits, but it worked for me.
Love what you've done and thanks for building on PRQL!
We're very happy to add something like a PIVOT statement. In the long term, we'd have to think about how to make it work deeply in PRQL, since the column names are suddenly runtime dependent. In the short term, we should definitely make PRQL work for your case — it's important that there's an escape hatch for things that aren't natively supported by PRQL yet. (We have s-strings but I'm guessing they don't cover this specific case?)
That makes sense. I ran into some old issues about Pivot but it seems it's not been implemented yet. And yes, s-strings wouldn't work - the DuckDB pivot statement looks like this:
So, we'd have to pass the `dataset` so far into the pivot statement. This is where CTEs come in handy but happy to hear if there's a better solution :)I'm currently using a self hosted instance of lightdash connected to a dbt project and I can see this being really efficient for data exploration for business users
quite interesting!
Happy to chat at prasoon [at] withpretzel [dot] com if you need any integration help!
As a result, I'll be adding support for PIVOTs very soon, either in the main repo or just in a temporary fork.
We'll definitely need to figure out (a) what's the right thing to measure, and (b) an automated testing/CI to check for regressions and do perf testing
We're a sales tech startup and I've had to look through large CSVs with prospecting information in the past - anything over 10 MB crashes my browser. I use a Mac so I don't have Excel. This looks great for simple data manipulations. I tried out a large CSV I had, and it loaded without a problem.
Quick bug report: The filter interface seems to be slow for me though with the large file. Also, one feature that would be really helpful would be connecting this to a database and also some way to share my workflow/analysis.
I wasn't sure whether you could query DBs directly from the browser but looks like you can! (https://github.com/alexanderguy/pgress) - will add it to roadmap!
Alright, did a bit of digging and the flamegraph points to a problem with the Table component. I'd hoped that BlueprintJS tables would be performant enough for our usecase but apparently not!
We'll try to move to canvas based rendering ASAP - that should fix any lags in the filter UI
I'll definitely look into it. For the moment, I know a team trying to solve this exact problem (they're not open-source/free but I think they're pretty inexpensive) - happy to connect you with them if you like :)
You mentioned that you went to some length to implement pivots. How far do you think you will take the pivot feature and UI?
For financial type usecases it's pretty much a requirement to be able to map many fields on X/Y, be able to collapse them in the the browser, and also show/control aggregations.
Asking because many apps stop at the simplest level of pivot features and go to great lengths in other areas like more advanced visualization. For mapping many fields though, I think a good pivot table UI is like a secret weapon.
I get that this isn't what you would focus on so much in week 1.
Nice work + love the local-first and direct UI.
I really liked that interface and haven't really found anything near as useable yet. Perspective JS (a free library by JP Morgan, you can try it here: https://perspective.finos.org/block/) has some really cool functionality in this direction but it has its one data processing engine, query language, rendering engine etc so we couldn't have used that for this project.
But, this is an interesting thought - I definitely would want to see this functionality in Pretzel - the only question is how to prioritize this. If you know of any performant table libraries that support collapsible pivots out-of-the-box, I'd love to integrate that. Alternatively, we'll write our own!
There is a license thing about logos to note if you do use it in Pretzel [2].
On GW [1], Create Dataset -> Public Datasets -> Student Performance then change the Mark Type to Table you can play around. It hits the things that I mentioned pretty good! You might have similar issues as you have with Perspective though.
I make calculang [3] and I'm getting ready to plug into a lot of things. A good pivot table generalizes well for my needs (mainly quick feedback during DX).
Perspective is on the list but so is GW and Pretzel.
Perspective might suit my needs perfectly.
But I like DuckDB-WASM approach anyway so I hope you continue and I hope you nail it one way or another! :)
[1] https://graphic-walker.kanaries.net
[2] https://github.com/Kanaries/graphic-walker/issues/330
[3] https://calculang.dev
I am also ex-GS and worked on what I am fairly sure is the table display tool you're describing. I tried to carry the essential aspects of that work (multi-level pivots, with drill-down to the leaf level, and all interactive events and analytics supported by db queries) to Tad (https://www.tadviewer.com/, https://github.com/antonycourtney/tad), another open source project powered by DuckDb.
An embeddable version of Tad, powered by DuckDb WASM, is used as the results viewer in the MotherDuck Web UI (https://app.motherduck.com/).
If you're interested in embedding Tad in Pretzel, or leveraging pieces of it in your work, or collaborating on other aspects of DuckDb WASM powered UIs, please get in touch!
Tad viewer looks perfect for embedding in Pretzel! Is it easy to embed it in web apps? I’m on mobile right now and did a quick search and didn’t find anything. I’ll definitely be in touch!
You can just do the data manipulations in DuckDB WASM and pipe the Arrow data to Perspective...
There’s also a accompanying tutorial blog post at https://tobilg.com/using-duckdb-wasm-for-in-browser-data-eng...
Interesting to watch the similarities (duckdb wasm) and differences (python vs js) between your SQL IDEs in the browser. Exciting stuff.
I think a snappy interface for <1gb datasets is really neat and super useful for certain kinds of data
https://developer.chrome.com/docs/capabilities/web-apis/file...
Browser is pretty powerful nowadays.
We are thinking of making data connectors to major DBs though so you should be able to do a similar style visual analysis while keeping the compute on your DB.
Why should I use this tool over Excel or Google Sheets?
I don't mean this question to be a challenge. Just trying to understand which sorts of tasks would I be better off using Pretzel as opposed to a traditional spreadsheet tool.
- I don't have excel on a Mac so I need to use Google Sheets. Google Sheets crashes on any file over 30-40 MB (at least on my macbook air)
- I think this type of chained data transform is a much better (more code-like?) way of doing transforms. You can see exactly how you got to your end-result. Vs, for example, in Google Sheets, you're modifying data in-place and your transform history is only accessible via Undo and Redo. This is far more reproducible short of using a python script.
(I've worked as a data scientist for a while and I suppose that influences how I think about chained data transforms vs the in-place Excel way too!)
But this is just for now - the long term vision is: Being able to easily switch between visual no-code blocks, SQL and Python in the same browser-local "notebook"/"workbook" that's easily shareable. I think it doesn't take much convincing that such a tool would be more powerful than Excel for certain workflows!
Memory per tab can be an issue for really big files (1gb+) but we're exploring a transform to CBOR which allows us to free up JS memory, i.e. when parsing CBOR we can leave row level data as Uint8Array and it doesn't increase the JS memory overhead.
This is quite interesting - we've not explored really large files so far and being honest, we haven't thought that far either. Didn't know about CBOR! I will have to look deeper into how this can save on memory. I was wondering though, since WASM memory is limited to 4GB, if I have sufficiently large memory on my compute device, at least one tab should be able to handle 1gb+ files too, correct?
I've not done much profiling on DuckDB and what the overhead is - i.e. after the data is parsed how much memory is used. Would be really interesting to push it to the limit - or to explore not loading the entire file in, but only reading the relevant parts, but again that probably requires a conversion first, e.g. to parquet or some other column based storage format.
However, using Sqlite requires a duckdb extension and i'm not sure if works for wasm, and if so enabled this project in particular.
https://duckdb.org/docs/guides/import/query_sqlite
But I believe that most of these duckdb web-IDEs will revolve around a couple of generalized used cases:
1. OLAP/analytical type workloads/queries. Sqlite is really more for OLTP/transactional workloads.
2. Querying datasets that are available on, targeted for blob/object storage like S3. Parquet, CSV, line-delimited JSON, etc
So, it should be pretty straightforward to let folks drop a SQLite file instead of a CSV file! I haven't been able to get extensions to work on WASM so far though but I will definitely take a look to see if we can make this work!
It's almost a meta thing but I think dev-tools have been getting better and better over the last 10-20 years while data tools seem to be stuck in the past. Devs, when then see friction, go build something to solve it while this tendency is much less common in data scientists, for eg.
https://datasette.io/
Or even OpenRefine?
https://openrefine.org/
https://news.ycombinator.com/item?id=39732135
Didn't know about OpenRefine - but one major difference is Pretzel is web-based meaning no installation necessary (important for work laptops, for eg). We're also going to be adding a ton of features to this - think Jupyter notebooks crossed with Metabase crossed with Datasette/Pretzel.
How is it different from CSVFiddle (which is also based on DuckDB-wasm, I think)? Ability to have multiple tables loaded and running join queries against them would be crucial.
We're different in a couple of ways:
- You don't need SQL to transform and manipulate data in Pretzel - we have visual blocks where you can construct a transform chain (though we do support manual SQL blocks now!)
- We have better CSV parsing (I spent a fair bit of time on this!). See one of our test case CSVs here that breaks (https://imgur.com/a/O8XMfET)
- We allow plotting!
- We support PRQL - which IMO is a better way to build data pipelines (but we also support SQL)
- We have AI Blocks! You can use your API keys (or our server) to get SQL from text which you can edit
- We're currently adding support for Python via Pyodide (pyodide.org) so you can do complex data transforms, even train simple ML models right inside the browser!
- Lastly, we'll be adding a ton more features to Pretzel over the coming months (many of them have been asked for in this thread actually!)
Support for multiple files is crucial - we'll be adding that soon. Right now, we're rethinking our whole task execution architecture so any major features will have to wait until then.