DEV Community

Cover image for Why We Replaced SaaS Analytics with In-Process DuckDB (Zero-Raw-Data Architecture)
Veil Analytics
Veil Analytics

Posted on

Why We Replaced SaaS Analytics with In-Process DuckDB (Zero-Raw-Data Architecture)

#javascript #webdev #database #privacy

Why We Replaced SaaS Analytics with In-Process DuckDB (Zero-Raw-Data Architecture)

Every time a data analyst or developer wants to build interactive dashboards for sensitive client data, traditional SaaS BI tools force them into a dangerous trade-off:

You must upload your raw customer files (CSV, Parquet, SQL dumps) to a third-party cloud server.

For healthcare, fintech, legal, and security-conscious engineering teams, this is a massive compliance headache. GDPR, HIPAA, and strict data residency rules make sending raw customer data to third-party SaaS an immediate non-starter.

So we asked a simple question: What if we ran the analytical database engine 100% locally inside the client environment?

Here is how we built VeilAnalytics β€” an in-process analytics engine powered by DuckDB, AST query validation, and local-first architecture.

πŸ› οΈ The Local-First Architecture

Instead of shipping raw data over the network to a heavy cloud data warehouse, we leverage DuckDB running in-process alongside the application.

[ Raw Data Files (CSV / Parquet / SQL) ]
                 β”‚
                 β–Ό
[ Local DuckDB Engine (In-Memory Compute) ]
                 β”‚
                 β–Ό
[ AST Security Sanitizer (SELECT-Only Filter) ]
                 β”‚
                 β–Ό
[ React 18 Chart & Tabular Visualization ]
Enter fullscreen mode Exit fullscreen mode

Key Technical Pillars:

  1. Zero Raw-Data Retention:

    Computation happens in-memory on the client hardware. Raw files never leave your environment.

  2. In-Process Performance:

    DuckDB operates as a high-speed columnar database. Querying 1,000,000+ rows takes milliseconds right on your local machine.

  3. AST SQL Security Guardrails:

    To prevent arbitrary execution risks, all queries are parsed into an Abstract Syntax Tree (AST) before hitting the engine. Non-SELECT statements or file system commands are blocked instantly.

πŸ”’ Code Snippet: AST SQL Query Sanitization

Here is how we ensure queries executed against local DuckDB sessions remain strictly read-only and isolated:

import { Parser } from 'node-sql-parser';

const parser = new Parser();

function validateSqlSafety(sqlQuery) {
  try {
    const ast = parser.astify(sqlQuery);

    // Ensure only SELECT statements are allowed
    const statements = Array.isArray(ast) ? ast : [ast];
    for (const stmt of statements) {
      if (stmt.type !== 'select') {
        throw new Error(`Unsafe operation: Only SELECT queries are permitted.`);
      }
    }

    return true;
  } catch (err) {
    console.error('SQL Validation Error:', err.message);
    return false;
  }
}
Enter fullscreen mode Exit fullscreen mode

πŸ’‘ What We Learned

Building local-first developer tools taught us three major lessons:

  1. Developers love speed: Eliminating network latency to cloud warehouses makes dashboard interactions feel instantaneous.
  2. Compliance is a major bottleneck: Giving enterprises 100% data residency removes months of legal approvals.
  3. DuckDB is a game-changer: The ability to run vectorized columnar SQL in-process opens up a new generation of local web applications.

πŸš€ Try It Live

We just launched the live web demo of VeilAnalytics!

I'd love to hear your thoughts, feedback, and questions in the comments below! Have you experimented with DuckDB or local-first architectures for data tools?

Top comments (0)