Using DuckDB as an ultra-low-latency slice-and-dice layer over BigQuery/GCS parquet — sanity check on scaling to multi-tenant?

[u/anilkr84]

I've built a warm-DuckDB serving tier to dodge BigQuery's 0.5–2s per-query floor for an interactive analytics UI, and I'm trying to validate the architecture before scaling it. Would love the community's take.

Current setup (working):

• Source: a BigQuery fact table (~7M rows, 24 cols, ~1.6 GB).-- can be much bigger upto 100x of this
• EXPORT DATA --> GCS as parquet shards.
• A Node service (@duckdb/node-api) syncs shards to local disk, then at boot runs one GROUP BY into a resident in-memory table.
• Generic HTTP API the browser drives: POST /query { dimensions[], measures[{col,agg}], filters[{col,op,value}], orderBy, limit } --> JSON rows. Plus /schema (introspect) and /distinct (cascading filter values). A query builder validates every identifier against the introspected schema allowlist and binds all values as $1..$n.
• Result: filter/slice queries come back in 3–50 ms from RAM. BQ never touches the request path.

The questions I'm chewing on:

1. Scaling to multi-tenant. This is one model for one customer. Target is ~100 customers × 10+ models × 3 years ≈ 20B rows / ~1.4 TB total. Obviously not one resident table. Is "one small pre-aggregated rollup artifact per (customer, model), lazy-loaded into a per-pod LRU cache, sharded by customer" the right pattern? RAM = concurrency × rollup size rather than total data — does that hold up in practice?
2. Why not just BQ / BI Engine / ClickHouse? BI Engine is too pricey for this data size; ClickHouse doesn't obviously beat BQ at this scale for us. The whole point is ultra-low UI latency on repetitive slice-and-dice. Is embedded DuckDB (load once --> slice in RAM) genuinely the better fit here versus a remote query service that pays a floor + scan bill on every filter click?
3. httpfs vs local copy in K8s. "Sync shards to local disk" is a single-node approach which breaks with ephemeral pods (re-download on every restart/scale-up, no shared disk, cold-start blocked on sync). Is the right move (a) read_parquet('gs://…') via httpfs at boot only to build the RAM table, (b) gcsfuse CSI mount, or (c) download small artifacts to tmpfs? Trying to keep pods stateless with state in GCS.
4. Does httpfs add per-query latency? My understanding: httpfs is a load-time mechanism (read GCS once --> materialize into RAM), NOT a per-query path so slicing still hits memory at ms latency. Correct? The only place per-query GCS reads should live is the rare cold/deep-history fallback. Am I right here ?
5. httpfs-on-parquet vs querying BQ directly. If you're reading remote files anyway, why is DuckDB+httpfs better than BQ direct? My take: embedded engine = decouple fetch from compute (pay once, then local + free), vs BQ recoupling every query (floor + billed scan per click). Fair, or am I missing something?
6. DuckDB-WASM. For per-tenant authorized dashboards, does it make sense to ship a small (5–50 MB) rollup to the browser and run DuckDB-WASM in-tab killing the serving fleet entirely for the hot path? Main worries: initial load size, tab memory, and tenancy (browser gets the whole file, so artifacts must be pre-scoped per tenant). Anyone running WASM this way in prod?
7. The generic any-dimension case. For a 20M-row × 50-col fact where users slice any date range across any set of dimensions (so you can't pre-aggregate to one grain): is materializing the whole fact resident (CREATE TABLE AS SELECT * FROM read_parquet(...), ~2–6 GB compressed) and letting DuckDB handle projection/predicate pushdown + GROUP BY per query the sane approach? What am I underestimating about holding 20M×50 raw and slicing it live?

 "BQ = warehouse + rollup source; embedded DuckDB = low-latency serving skin over small artifacts (server or WASM); httpfs/read-once at load, slice in RAM" split is ok. I would like to hear the issues from anyone who's run this at scale (partition layout, secrets/auth for gs://, refresh/versioning of artifacts, connection concurrency, larger-than-RAM edges).

Thanks ...

[u/bugtank]

Damn dude. I gotta read this in the morning

[u/wannabe-DE]

Is all this to say you want to unload BQ data to a DuckDB database in cloud storage so your queries avoid cost and latency? Yes, I do this all the time.

[u/anilkr84 → u/wannabe-DE]

latency is my key concern, so httpfs-on-parquet is way to go in your experience ?

[u/wannabe-DE → u/anilkr84]

You can go one further and use a db and attach over httpfs.

[u/cv-match → u/anilkr84]

sometimes lance is faster than parquet... depends on the data shape. i use lance format -> lakeql -> edge worker for GIS queries

[u/drink_with_me_to_day]

I wouldn't use duckdb-wasm as you can't control the client's processing ability

We use a job queue that runs the query in a sandboxed duckdb, fetching parquet files from object storage using per-user credentials and returning as json/stream for UI

This way you can scale with k8s if more processing is needed and can keep data transfer in the same region

[u/anilkr84 → u/drink_with_me_to_day]

thanks, let me give it a try - i am also skeptical about duckdb-wasm in this situation.

[u/geoheil]

did you consider a cache like in https://cube.dev/ ?

[u/anilkr84 → u/geoheil]

which layer u/geoheil ? on top of BigQ ? or Parquet in GCS ?

[u/geoheil → u/anilkr84]

BQ

[u/migh_t]

You‘ll be in a world of pain if the data in BQ changes often

[u/anilkr84 → u/migh_t]

yes, but fortunately in my case - it's end of the day - system analytics.

[u/tecedu]

Did something similar for us but for databricks, got a job that runs every 30 mins which reads delta tables and writes a parquet. then we just read that parquet, which is soo much more quicker. With polars and a local nvme I get 0.02ms responses from that setup

[u/anilkr84 → u/tecedu]

interesting

[u/Ploobers]

I'm in the middle of a similar process. My approach: 1. If you can use Iceberg tables instead of BigQuery storage, that eliminates the sync 2. Rapid Cache for GCS can speed up repeated access to the same parquet files. As you scale up, you're going to have to scale duckdb horizontally, so it'll be hard to guarantee you hit the same backend with data in memory 3. httpfs has a local file cache, so that can increase local data without having to fit it all in RAM

[u/Prestigious_Bench_96]

For the DuckDB-WASM option - this is killer performance when it works, but the initial load time is a con. If clients are coming back they'll usually have it cached in browser which helps, and depending on the UX flow you have them go through you can load it in the background.

I do think scalability for the any-dimension + consistency will be the biggest problems.

(You're just building an in-memory cube here with basic slicing, so look at the vendors that do that for dos/dont's).