You’re paying warehouse prices to render 12 tiles on a customer dashboard. Each click fans out to a cloud service that charges by the query, the second, and the byte scanned. Latency is unpredictable. The bill is not. With DuckDB 2.0 on the horizon (the preview is making the rounds), it’s time to flip the default: put OLAP in your app, not in a distant warehouse.
DuckDB’s embedded, columnar engine has been the worst-kept secret in data for years. What’s changing now is maturity. The 2.0 cycle signals a stable foundation, faster I/O, and a clearer story for extensions and large result handling. You don’t need to wait for the GA tag to start piloting: the architecture and trade-offs are well understood. And for a large class of SaaS dashboards, in-product analytics, and ad hoc slice-and-dice, embedded OLAP beats serverless query services on cost, latency, privacy, and simplicity.
What goes wrong when you “ship a warehouse”
Warehouses and serverless query engines are brilliant at multi-tenant, cross-domain analytics. They’re bad at powering product interactions in real time. You feel this in three places:
- Unbounded unit economics. If you push 100k dashboard loads/day at $5 per 1,000 queries, that’s ~$15k/month in query fees before storage, egress, or “accelerators.” It creeps up with growth because optimized caching is someone else’s problem.
- UI latency you can’t own. Even fast warehouses fluctuate. A “simple” group-by over a few GB might be 200ms on Monday, 1.5s on Friday, with the cost of shaving that spread falling on you.
- Data gravity fights you. Exporting feature tables, event logs, or per-tenant snapshots into a warehouse adds pipelines, backfills, and failure modes. Audits and data residency multiply the overhead.
When the question is “show this customer their last 90 days of usage with 5 filters,” you don’t need an internet-scale leaf network. You need fast columnar scans in-process with tight control over CPU, cache, and memory.
Where DuckDB 2.0 changes the default
DuckDB is a vectorized, columnar SQL engine you can embed into your app (C/C++, Python, Node, Rust, Go, Java) and point at Parquet, CSV, or Arrow buffers. It’s OLAP without the cluster: analytics queries run inside your process, on the same machine as your app or worker.
What the 2.0 track means for CTOs isn’t a single magic feature—it’s a stability and performance milestone. Expect faster I/O paths, more headroom on large results, and improved extension and file handling ergonomics. If you’ve been waiting for a major version line to standardize on, this is your signal.
Concrete benefits you can bank on
- Predictable latency: Columnar, vectorized execution on local storage keeps 95th percentiles tight. For 1–5 GB per-tenant datasets, sub-second aggregates on commodity 8–16 core instances are normal when you design for cache locality.
- Zero per-query tax: Queries don’t bill you. Instances do. A single c6i.2xlarge-class server (8 vCPU, 16–32 GB RAM) can handle thousands of daily dashboard renders with headroom.
- Privacy and residency by default: Keep analytics in the tenant’s region, or even on-device. No cross-border sync to a third-party data plane just to compute daily active users.
- Sane developer ergonomics: Ship SQL, not a fleet. No drivers to babysit, no external connections to tune, and no opaque retries. Your app owns the lifecycle.
Decision framework: When to embed vs. warehouse
Don’t rip out your warehouse. Draw a line. If most of your product analytics fit the left column, start embedding DuckDB. If you land in the right, keep the warehouse and optimize.
Use-case fit
- Great fit for DuckDB: Per-tenant dashboards, cohort analysis on 10–100M rows, time-windowed aggregates, interactive filtering with 3–8 dimensions, feature store exploration, CSV/Parquet ingestion and QA, offline analytics in desktop/mobile.
- Keep the warehouse: Cross-tenant joins over TBs of data, 100s of concurrent users querying the same dataset, access patterns with long-running joins, governance policies centralized in the warehouse, BI teams building federated models.
Rough sizing thresholds
- Per-tenant data under ~10 GB compressed: Strong candidate for embedded. Larger is still workable with partitioning and streaming, but test memory headroom.
- Concurrent queries per instance under ~20: DuckDB scales vertically; you’ll multiplex with app threads or run per-tenant workers. If you need 200+ concurrent heavy queries over the same data, a warehouse will be simpler operationally.
- Freshness = minutes to hourly: Embedded shines when you can update snapshots on a schedule or append to partitioned Parquet. If you need second-by-second updates across tenants, keep the warehouse path for those flows.
Risk controls
- No user-supplied arbitrary SQL: Don’t let the app or an LLM improvise SQL. Compile a small DSL to parameterized SQL. A recent dev post bluntly said it: don’t give the model SQL. You’ll ship safer and faster.
- File isolation: One file per tenant or per workspace. No cross-tenant sharing on disk. This makes snapshots, backup, and deletion tractable.
- Extension hygiene: Only whitelist audited extensions. Embedded means your process is the blast radius.
Reference architectures you can ship this quarter
1) Server-embedded OLAP per tenant
Pattern: For each tenant, maintain a partitioned Parquet lake in object storage. On query, your app spins up a DuckDB connection, attaches the Parquet directory, runs parameterized SQL, and returns JSON to the UI. Cache hot aggregates in Redis with a TTL.
- Pros: Minimal infra, predictable cost, easy audit. Snapshots are just object storage versioning. Great for 1–10k tenants with light-to-moderate traffic.
- Cons: You will manage CPU contention on shared nodes. Cold starts pay an I/O tax unless you pin hot partitions to local NVMe.
2) Worker-driven materialization
Pattern: Event streams land in columnar files (Parquet). A periodic worker (15–60 minutes) compacts and refreshes derived tables (rollups, tiles) with DuckDB. The app serves pre-aggregated results with filter pushdown.
- Pros: Sub-200ms P95 for repetitive dashboards. Very cheap to scale. Easy to enforce SLAs.
- Cons: Less interactive for arbitrary drill-downs beyond the precomputed shapes.
3) On-device analytics with WebAssembly
Pattern: For client-heavy products, preload duckdb-wasm and ship encrypted slices of data to the browser or desktop. Run queries locally; send back only aggregates for audit or sync.
- Pros: Zero server cost per query, instant interactivity, strong privacy posture. Ideal for enterprise customers who won’t let their data leave the VPC without a fight.
- Cons: Package size, memory ceilings, and update complexity. You need a robust content strategy for data packages.
4) Hybrid with a hosted DuckDB service
Pattern: Use a hosted DuckDB-compatible service for collaboration, multi-writer semantics, or SQL sharing (e.g., analysts iterating on metrics) while your app path stays embedded for runtime queries.
- Pros: Best of both worlds for teams with analysts who demand SQL ergonomics without imposing a warehouse on the product path.
- Cons: You’ll manage two paths—keep the product critical path embedded to protect latency and cost.
Performance and capacity planning that won’t surprise you
Shape your data for CPU caches
- Columnar all the way: Store in Parquet by default. Pick sensible column types. Encode categoricals; avoid free-text in hot paths.
- Partition where it matters: Time (by month/week) and tenant/workspace IDs. This reduces scan volume drastically.
- Tile your heavy visuals: Precompute 256×256 heatmap tiles, histogram buckets, or percentile tables. Then filter and assemble on demand.
Know your memory envelope
- Target 1–2× compressed data size as a safe working set for complex group-bys. If a tenant’s compressed data is 6 GB, plan for 8–12 GB RAM headroom when running their heaviest interactive queries.
- Pin hot partitions to NVMe if you rely on frequent cold scans; DuckDB loves fast local read bandwidth. Use object storage for the long tail.
- Throttle concurrency with a simple token bucket per node. Don’t let 30 simultaneous 8-core queries fight on a 16-core box.
Security and governance in an embedded world
- Row-level security is in your app tier: With one file per tenant, your authorization logic stays simple: either you can mount that dataset or you can’t. Avoid cross-tenant files that force complex RLS policies.
- SQL surface area: Build a safe API that compiles user intent to SQL templates. No arbitrary filesystem access, UDF creation, or shell escapes. Audit every executed statement with parameters.
- Encryption and deletion: Encrypt at rest via object storage keys, plus application keys if you ship to client devices. Bake deletion into partition lifecycle; prove erasure with object version tombstones and key revocation.
- Backups are file copies: Snapshot Parquet directories. Verify restore by booting a staging instance hourly with the latest snapshot and running a smoke suite.
Costs that actually get better with scale
Here’s a conservative budget comparison for a mid-stage SaaS serving 50k dashboard views/day:
- Warehouse path: 50k queries × $5/1,000 = $250/day in query fees (~$7.5k/mo), plus storage, compute reservations, and egress. Realistically $10–20k/month for the product analytics slice alone.
- Embedded path: 3× c7i.2xlarge-class instances (~$350/mo each) + storage/NVMe + nearshore engineering to ship and maintain. Infra: ~$1.5–3k/month. Engineering amortized: depends on your team, but most see payback in 2–4 months versus warehouse bills.
None of this counts the user-visible latency delta (sub-second versus seconds). Faster dashboards convert trials, reduce drop-offs, and cut support tickets—benefits your finance sheet doesn’t capture cleanly but your growth metrics will.
30/60/90: A pragmatic rollout plan
Days 0–30: Inventory and pilot
- Log every dashboard query shape in your app today: dimensions, filters, time windows, and result sizes. You’ll find that 80% fall into a dozen patterns.
- Stand up a pilot repo embedding DuckDB in your app’s primary language. Connect to a Parquet snapshot per tenant. Rebuild three heaviest dashboards with parameterized SQL.
- Golden tests: Validate equality with your warehouse results on fixed snapshots. Make this repeatable in CI.
Days 31–60: Operational hardening
- Partitioning and compaction: Build the compactor job to create partitioned Parquet for each tenant nightly or hourly, depending on freshness needs.
- Caching strategy: Add a 5–15 minute cache per expensive aggregate. Show “data as of HH:MM” in the UI.
- Concurrency controls: Introduce a per-node token bucket. Fail fast to cache, or degrade to precomputed tiles under load.
- Security posture: Lock extensions. Remove filesystem primitives from the SQL surface. Store every executed statement for audit.
Days 61–90: Flip the default safely
- Dark launch: Route 10–20% of tenants to embedded, measure P50/P95 latency and correctness. Roll forward to 50–70% if error budget holds.
- Cost controls: Drop warehouse compute for this slice. Keep a “break glass” path for two weeks while you monitor.
- Team enablement: Publish your SQL templates and DSL docs. Train PMs/analysts on what’s possible in-UI versus what stays in the warehouse.
Trade-offs you must accept
- Vertical not horizontal scaling: DuckDB loves big cores and fast local disks. If your mental model is “add a node, go faster,” you’ll be disappointed. Instead, shard by tenant or workspace.
- Single-writer realities: Concurrency is fine for reads; compaction and materialization jobs should be scheduled to avoid head-on conflicts. If you truly need multi-writer at high concurrency, a hosted DuckDB-compatible service or your warehouse may make sense for authoring paths.
- Limited long-running query tolerance: Embedded is the wrong place for 5–30 minute joins. Timebox queries and design the UX to encourage precomputed paths for big work.
- You own correctness: There’s no “mysterious service” to blame. The good news: your test snapshots, golden queries, and CI can prove correctness every release.
Why this is a nearshore-friendly move
Embedded analytics pays off fastest when you can iterate in the app codebase with tight feedback loops. That’s a nearshore sweet spot: 6–8 hours of overlap with US time zones, low integration overhead, and clear deliverables (dashboards X/Y/Z moved to embedded; P95 under 800 ms; $N saved/month). We routinely see a small pod—1 senior backend, 1 data engineer, 1 full-stack—ship a pilot in 3–4 weeks and flip 60% of product analytics in a quarter.
The bottom line
Warehouses aren’t going away. But if you’re using them to render per-tenant dashboards, you’re buying a Formula 1 car to drive inside a mall. With DuckDB 2.0 maturing, embedded OLAP is the sane default for product-facing analytics: cheaper, faster, and simpler to operate. Draw your line and move the left side—now.
Key Takeaways
- Don’t ship a warehouse for per-tenant dashboards; embed DuckDB and keep analytics in your app.
- Target per-tenant datasets under ~10 GB compressed and sub-20 concurrent heavy queries per node.
- Store data in partitioned Parquet, compile user intent to parameterized SQL, and throttle concurrency.
- Expect sub-second P95 for common aggregates on 8–16 core instances and a 3–10× cost reduction versus per-query services.
- Roll out in 90 days: pilot 3 dashboards, harden compaction/caching, dark launch to 20–70% of tenants, then cut warehouse spend for that slice.