Killing Our SaaS BI Stack: DuckDB + Postgres for 200 People

We recently completed a migration that I’ve wanted to write about for months: moving our entire business intelligence stack from a well-known SaaS BI platform (think Looker, Tableau, or Mode) to a self-hosted pipeline built on DuckDB and Postgres. The org is about 200 people — product, engineering, sales, marketing, finance, and operations. Our old BI bill was $50K/year, and queries on our 2 TB data warehouse took 30-60 seconds on average. Today, the same queries run in 1-3 seconds, we own every byte of our data, and our infrastructure cost dropped to ~$500/month for the compute and storage. Here’s exactly how we did it — the architecture, the trade-offs, and the scars we earned along the way.

Why We Left SaaS BI

The decision wasn’t about cost alone. Yes, $50K/year is significant for a 200-person company, but the real pain points were:

• Query latency: Even simple aggregations on our 2 TB Postgres replica took 30+ seconds because the BI tool’s query engine would either push down poorly or pull entire tables into its in-memory engine.
• Data egress fees: Every dashboard refresh pulled data from our cloud Postgres instance through the BI tool’s API, racking up network costs and unnecessary load on production-adjacent databases.
• Access control complexity: We needed row-level security based on tenancy and department. The SaaS tool supported it, but configuration was a nightmare — and any change required a support ticket.
• Data sovereignty: Our legal team flagged that some dashboards contained personally identifiable information (PII) that shouldn’t leave our VPC. The SaaS vendor’s SOC 2 report was fine, but the risk wasn’t worth it.

We evaluated alternatives: Apache Superset, Metabase, Redash, and even rolling our own with Streamlit. Each had trade-offs. Superset was too heavy for 200 users. Metabase was great for self-service but couldn’t handle our row-level security needs without hacks. Redash was sunsetting. Streamlit would require too much custom code.

Then we stumbled on DuckDB. The idea was radical: instead of a centralized BI server, give each analyst a local DuckDB database that they could query with SQL, and use Postgres as the source of truth for raw data. The dashboards would be built with a lightweight web layer (we chose Evidence.dev) that runs DuckDB in the browser. No server to scale, no query queue, no data egress.

Architecture Overview

Here’s the final architecture:

┌─────────────────┐       ┌─────────────────┐
│   Source Apps   │──────▶│   Postgres (RDS)│
│ (Salesforce,    │       │   (2 TB, 50+    │
│  HubSpot, etc.) │       │   schemas)      │
└─────────────────┘       └────────┬────────┘
                                    │
                                    ▼
┌──────────────────────────────────────────┐
│  Data Transformation (dbt + DuckDB)     │
│  - Materialize views as Parquet files   │
│  - Incremental refreshes every 30 min   │
│  - Store in object storage (S3/MinIO)   │
└──────────────────────────────────────────┘
                                    │
                                    ▼
┌──────────────────────────────────────────┐
│  Evidence.dev (static site)             │
│  - SQL queries against local DuckDB     │
│  - Row-level security via .env secrets  │
│  - Deployed to Cloudflare Pages         │
└──────────────────────────────────────────┘
                                    │
                                    ▼
┌──────────────────────────────────────────┐
│  User Browser (DuckDB WASM)             │
│  - Queries Parquet files directly       │
│  - Zero server-side compute             │
└──────────────────────────────────────────┘

Key components:

• Postgres (RDS): Source of truth. All application data lives here. We maintain a read replica for the pipeline.
• dbt + DuckDB: We use dbt to transform data from Postgres into analytics-ready tables, but instead of loading into a warehouse, we write the results as Parquet files to S3. DuckDB excels at this because it can read directly from Postgres via its postgres extension and write Parquet in parallel.
• Evidence.dev: A static-site BI tool that lets you write SQL in Markdown files. It compiles to HTML/JS that runs DuckDB in the browser via WebAssembly. No server-side queries — the user’s browser fetches the Parquet files from S3 and runs DuckDB locally.
• Row-level security: Each department gets a separate Parquet file per table, filtered by tenant_id. The Evidence site uses environment variables to decide which files to load. No SQL injection possible because the queries are pre-compiled.

Migration Steps

Step 1: Audit existing dashboards

We had 47 dashboards covering sales pipeline, marketing attribution, financial reporting, and engineering metrics. Each dashboard was a collection of SQL queries, often with complex joins and window functions. We extracted all SQL from the old BI tool’s API and categorized them:

• 30% were simple aggregations (e.g., “revenue by month”).
• 50% were multi-table joins with filters.
• 20% were complex (nested subqueries, recursive CTEs, or window functions over large time ranges).

Step 2: Build the dbt models

We created a dbt project with models that mirror the old queries. The key difference: we materialized everything as views or incremental tables, and wrote the output as Parquet files partitioned by date and tenant.

Here’s an example model for a sales pipeline dashboard:

-- models/sales/pipeline.sql
{{ config(materialized='incremental', partition_by='period', unique_key='deal_id') }}

SELECT
    deal_id,
    tenant_id,
    amount,
    stage,
    created_at,
    DATE_TRUNC('month', created_at) AS period
FROM postgres_database.sales.deals
WHERE created_at >= '2020-01-01'
{% if is_incremental() %}
  AND created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

We used the postgres extension in DuckDB to run this directly against our read replica:

# Run dbt with DuckDB adapter
dbt run --target prod

This produced Parquet files in S3 like:

s3://analytics/sales/pipeline/tenant_id=abc123/period=2024-01/data_0.parquet
s3://analytics/sales/pipeline/tenant_id=def456/period=2024-01/data_0.parquet

Step 3: Build Evidence pages

Each dashboard became a Markdown page in Evidence. For example, the sales pipeline page:

---
title: Sales Pipeline
---

```sql pipeline
SELECT
    stage,
    SUM(amount) AS total_amount,
    COUNT(*) AS deal_count
FROM 's3://analytics/sales/pipeline/tenant_id=$TENANT_ID/*.parquet'
WHERE period = '2024-01'
GROUP BY stage
ORDER BY total_amount DESC

The `$TENANT_ID` is set via an environment variable during build. Each department’s Evidence site is built separately with its own tenant ID, so no cross-tenant data leakage.

### Step 4: Set up refresh pipeline

We use a cron job (Airflow) every 30 minutes to:
1. Run dbt to pull new data from Postgres.
2. Write new Parquet files to S3.
3. Trigger a rebuild of the Evidence site (via webhook to Cloudflare Pages).

The rebuild takes about 2 minutes for 200 pages. The total pipeline latency from Postgres commit to dashboard update is under 5 minutes.

## Performance Results

We benchmarked the 10 most complex queries from the old BI tool against the new setup. Results:

| Query | Old BI (seconds) | DuckDB (seconds) | Speedup |
|-------|------------------|------------------|---------|
| Monthly revenue by region (1 year) | 42 | 1.2 | 35x |
| Sales funnel conversion (90 days) | 28 | 0.9 | 31x |
| Customer cohort retention (2 years) | 67 | 3.1 | 22x |
| Marketing attribution (multi-touch) | 55 | 2.4 | 23x |
| Finance P&L (all time) | 120 | 5.8 | 21x |

Average speedup: ~25x. The main reason is that DuckDB processes data locally with no network round-trips, and Parquet’s columnar format allows it to skip irrelevant columns and rows.

## Cost Breakdown

| Item | Old (annual) | New (annual) |
|------|--------------|--------------|
| SaaS BI license | $50,000 | $0 |
| RDS read replica | $12,000 | $12,000 (same) |
| S3 storage (Parquet) | $0 | $600 (approx 200 GB) |
| Compute (dbt + cron) | $0 | $2,400 (small EC2) |
| Cloudflare Pages | $0 | $0 (free tier) |
| **Total** | **$62,000** | **$15,000** |

We saved $47,000/year, and the performance improvement is a bonus.

## Lessons Learned

### What Worked

- **Local-first architecture**: Users don’t compete for server resources. Each browser runs its own DuckDB instance. Even with 200 concurrent users, there’s no queuing.
- **Parquet + partitioning**: The combination of columnar storage and Hive-style partitioning (by tenant and date) made queries ultrafast. We could also easily grant access by simply controlling S3 prefixes.
- **dbt + DuckDB**: dbt’s incremental models worked flawlessly with DuckDB. The `postgres` extension made pulling data trivial.
- **Evidence.dev**: It’s not a full BI tool — no drag-and-drop, no ad-hoc query builder. But for our use case (pre-defined dashboards with SQL), it was perfect. The developer experience is excellent.

### What Hurt

- **Row-level security is manual**: We have to build separate sites for each department. This is fine for 10 departments, but wouldn’t scale to 1000 tenants. We’re exploring dynamic loading via DuckDB’s `read_parquet` with a secret manager, but it’s not production-ready yet.
- **No ad-hoc queries**: Users who used to explore data with the old BI’s drag-and-drop now have to write SQL. We mitigated this by offering a “query sandbox” using DuckDB CLI via a web terminal, but adoption is low.
- **Historical data migration**: The old BI tool had its own cached data that didn’t match Postgres exactly. We had to reconcile differences manually for some financial reports.
- **Alerting**: The old BI had built-in alerting (e.g., “revenue dropped 20%”). We now use a separate Python script that runs DuckDB queries and sends Slack messages. It works, but it’s another thing to maintain.

## Is This Right for You?

This architecture is ideal for mid-sized organizations (50-500 employees) that:
- Have a SQL-literate team (analysts, engineers).
- Need row-level security but don’t have thousands of tenants.
- Want full data sovereignty and low latency.
- Are willing to trade a polished UI for speed and cost savings.

It’s probably not right if you need a drag-and-drop interface for non-technical users, or if you have hundreds of ad-hoc queries per day that change frequently.

## Final Thoughts

We’ve been running this stack for 6 months now. No outages, no complaints about speed (only compliments), and the legal team sleeps better at night. The total engineering effort was about 4 weeks for one data engineer and one backend engineer. If I had to do it again, I’d start with DuckDB + Evidence from day one. The SaaS BI industry is ripe for disruption — and local-first, columnar, WebAssembly-powered tools are the way.

If you’re considering a similar migration, start small: pick one dashboard, rebuild it in DuckDB, and compare performance. You’ll likely be hooked.

*All code examples are simplified for readability. The actual dbt models include error handling, retries, and monitoring.*