Will Framework help me avoid cache based errors for Windows users when attached files are queried repeatedly?

[eamcvey]

I have recently built a dashboard using Quarto with Observable JS and DuckDB. All the data is stored in parquet files that are queried by DuckDB-Wasm in response to Observable Inputs, and these update Observable plots and tables. It's working beautifully ... for Mac users on several types of browsers.

I've just discovered during internal testing that Windows users encounter errors on both Chrome and Edge. The errors go away in private browsing mode. After some digging, I believe that they are due to an issue with DuckDB-Wasm discussed here. The gist of it is that on Chrome and Edge on Windows, there's a caching issue that doesn't have robust solution yet. The result is that after one or a few times successfully querying a parquet file, an error is thrown that prevents further successful queries. Private browsing disables caching, hence no problems. The dashboard I'm working on will be publicly released to provide access to the data produced by the non-profit I work at, so I need to be confident it will function for people on a wide variety of machines and browsers.

In response to a question I asked about a more minor problem I'm having, @mbostock suggested that switching from Quarto to Observable Framework might be a better experience. I'm willing to try porting my dashboard over (though it will further stretch my limited JS skills), but want to determine if it's likely to solve my Windows users caching problems -- and if so, why? If I understand data loaders correctly, they provide a way to generate files of data to support a dashboard, but the data is still accessed via file attachment, which is what I'm doing now. I'm using DuckDB because the data is rather large to load all of at once, so querying only what I need in response to user inputs from the parquet files seemed like a good option. Is there anything about using Observable Framework that would fundamentally change my problem and allow me to provide access to the same amount of data interactively without running into the errors we're currently experiencing?

[mbostock]

This unfortunately appears to be a DuckDB-Wasm issue (as you linked, and as described in #1470) so I’m not sure there’s anything we can do in Framework to resolve this issue automatically. But there are a variety of ways you could workaround it.

Perhaps the most direct workaround is to avoid caching on Windows. To ensure that the browser has not cached the Parquet file, you could add a query string with the current time to the file name. The SQL front matter only supports statically-registered sources, but you can use DuckDBClient.sql to register SQL sources dynamically in JavaScript.

```js
const sql = DuckDBClient.sql({
  gaia: FileAttachment("./lib/gaia-sample.parquet").href + (navigator.userAgent.includes("Windows") ? `?t=${Date.now()}` : "")
});
```

Another way to avoid caching would be to register a service worker that intercepts requests to load Parquet files to add the Cache-Control: no-cache request header. This is a little tricky because you probably want to guarantee that the service worker is registered before DuckDB-Wasm attemps to load the Parquet file; but on the other hand, the first time you visit the page you don’t actually need the service worker registered before you make the request because the files are initially uncached. It’s also tricky because service workers can only intercept requests to paths in the same directory, and Framework puts any local JavaScript modules in _import while file attachments such as Parquet files live in _file; but thanks to the new exported modules feature that will be available in the next release 1.12, we can have the service worker live in the app root and intercept file requests.

To register the service worker:

navigator.serviceWorker.register("./service-worker.js", {type: "module"});

Then in service-worker.js:

addEventListener("fetch", async (event) => {
  const {pathname} = new URL(event.request.url);
  if (!pathname.endsWith(".parquet")) return;
  if (!event.request.headers.get("User-Agent")?.includes("Windows")) return;
  const headers = new Headers(event.request.headers);
  headers.set("Cache-Control", "no-cache");
  const request = new Request(event.request.url, {headers});
  event.respondWith(fetch(request));
});

You’ll also need to register the /service-worker.js in dynamicPaths so that it’s exported and available at that path. (And it might be possible to do this using FileAttachment instead, but I couldn’t get it to work. 🤷 I don’t know enough about service workers…)

Another possibility would be to avoid the Parquet format. DuckDB has a native file format you could consider instead of Parquet, as well as support for many other file formats include CSV. You could have your data loader output a different format, or use a data loader to convert a Parquet file to another format.

Finally you could consider avoiding DuckDB-Wasm entirely and instead use DuckDB at build time within a data loader. Maybe you could elaborate a bit more on your use case, how big your data is, how you’re currently using DuckDB-Wasm, what sort of user interaction you want to support, etc.

[eamcvey]

Thanks @brichards920 this is the best option for me of those that have been suggested so far! I've been working through the cache-busting options, but with mixed results so far. I feel more comfortable with the arrow option knowing that I'm sidestepping the caching problem with parquet files.

That said, the data files for my app are now 85Mb instead of 2Mb, so it will definitely be nice to have the parquet option working.

[eamcvey]

I've never had to think deeply before about the tradeoffs between parquet and arrow, and I found this SO answer from Wes McKinney helpful. I was vaguely assuming that parquet is definitely the better choice for backing data apps that use DuckDB-Wasm, but after reading this I wonder if that's the case. The "parquet is a lot more expensive to read" part. My updated thinking is that if file size isn't a deal breaker, arrow may actually be preferable. Unlike when I substituted parquet with csv (which has multiple downsides), the query speed seems fine with arrow.

[mbostock]

My basic understanding of data loaders is that they provide a convenient way to recreate files, similar to what I'm doing, at the time of dashboard build. In cases where data changes regularly, this would allow for something like a nightly build that would provide users with fresh data powering the app each day. Since the data loaders are integrated with Framework, as opposed to separate scripts like the R file I'm using for my data prep, it ensures that everything runs together without additional orchestration needed. Is this right?

That’s right! An additional advantage with data loaders is that when you edit them, you’ll get reactive updates in preview without having to reload the page or re-run them manually. This tends to make development much more pleasant/faster because you don’t have to remember to re-run your scripts.

If you want to reduce the size of the data, would it be possible to break up the data into multiple files so the client doesn’t need to load all 85Mb initially — maybe some data could be lazily loaded when the user interacts? Or maybe a summary that covers the initial view, then allowing the full dataset to be downloaded in the background? OTOH 2Mb in Parquet doesn’t sound like much data so it’s a shame that there’s this bug in DuckDB-Wasm…

[brichards920]

@eamcvey I've found that converting character columns to factors before exporting the arrow file from R can make a big difference in the arrow file's size.

I just took a quick look at one common dataset I use with 70 columns and 350,000 rows, and just lazily running:

mydfWithFactors <- mydf  %>%
  mutate(across(where(is.character), as.factor))

dropped the .arrow size from just over 200mb to 89mb. It will depend a lot on your data, but it's worth a try.

I've also had some success having observable load in zip files and then to unpack the contents, so the download is cheaper but the process is a little more expensive.

[eamcvey]

@brichards920 Brilliant! This took my two biggest files, each ~40Mb down to 13 and 8 (in my case, about half the columns are naturally factors, with a relatively small number of values with respect to the number of rows). I can see a corresponding performance improvement.

One note of caution for others -- take care with the factor conversion if you have the same columns in multiple files. In my case, some key join columns have different subsets of values in different files, and I had to make sure that I converted them to factors using the same set of levels in every case.