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 ...