ETL Studio — Technical Documentation

Architecture Overview

ETL Studio is a zero-backend, browser-based ETL designer powered by DuckDB-WASM. Everything runs in the browser — data is processed locally using DuckDB's WebAssembly build, and no data ever leaves your device. The application follows a pipeline architecture where each step transforms data and passes it to the next step.

Key Design Decisions

Decision	Rationale
DuckDB-WASM as engine	Full SQL OLAP capabilities in-browser with near-native performance via WASM SIMD. No server needed.
CTE-based pipeline	Each step compiles to a Common Table Expression. The entire pipeline is a single SQL query — efficient and debuggable.
Zustand for state	Lightweight, TypeScript-friendly state management with minimal boilerplate and built-in persistence support.
File System Access API	Read and write project files directly to the user's local file system. No uploads, no server round-trips.
Vite + React + TypeScript	Fast development experience with HMR, type safety, and a mature ecosystem.
PWA with Workbox	App shell precached for offline use. WASM binaries cached with expiration for performance.

Pipeline Engine

Steps are compiled into a DuckDB SQL query using Common Table Expressions (CTEs):

WITH
  step_01 AS (SELECT * FROM source),
  step_02 AS (SELECT * FROM step_01 WHERE condition),
  step_03 AS (SELECT col, COUNT(*) FROM step_02 GROUP BY col)
SELECT * FROM step_03

Key Components

Component	Description
Step Definitions	Pure functions that convert a step's config into DuckDB SQL
CTE Builder	Chains enabled steps into a single CTE-based SQL query
SQL Executor	Runs queries against DuckDB-WASM and returns results
Preview System	Auto-executes the pipeline on changes with debounced updates
State Management	Zustand stores for queries, steps, connections, UI state
File Persistence	File System Access API + IndexedDB for project files

Step Categories

Category	Steps	Description
Get Data	Source File, Source Table	Load data from files or database tables
Columns	Set Names, Pick Columns, Change Type, Duplicate	Manipulate column structure and types
Rows	Filter, Sort, Remove Duplicates, Fill Null	Filter and organize rows
Values	Replace Values, Remove Errors, Fix Errors	Clean and transform cell values
Transform	Add Index, Formula, Pivot, Unpivot, Clean Text	Advanced data transformations
Combine	Append Tables, Join Tables	Merge data from multiple sources
Aggregate	Group & Aggregate	Summarize and group data
Advanced	Custom SQL	Write hand-crafted SQL with AI assistance
Output	Export to File	Export results to CSV, Parquet, Excel, JSON, JSONL

AI Integration

ETL Studio supports multiple AI providers for natural language to SQL generation:

Ollama — Local inference with models like Gemma, Llama, Mistral
Claude — Anthropic's Claude API
OpenAI — GPT-4o, GPT-4o-mini, and more
Gemini — Google's Gemini models
Groq — Fast inference with Llama, Mixtral, Gemma
OpenRouter — Unified API for 200+ models
Local (browser) — wllama (WASM), WebLLM (WebGPU), Transformers.js (WebGPU/WASM). Models run 100% in the browser; weights are downloaded once and cached in IndexedDB. Configured in the AI Setup modal's "Local (browser)" card.

AI settings are configured in the AI panel (click the "AI" button in the header). The AI Setup modal offers the remote providers above plus a "Local (browser)" card for in-browser models. API keys are stored in your browser's localStorage and never sent to any server except the API endpoint you choose; local models need no key and never leave your device.

Data Flow

Create a query — Click "+" in the left panel
Add data source — Use "Get Data" step to load a file or table
Add transform steps — Filter, clean, aggregate, pivot, etc.
Preview results — Each step shows its output in the center panel
Export — Add an "Export to File" step or use the Run All button

Export Formats

Format	Extension	Use Case
CSV	.csv	Universal spreadsheet format
Parquet	.parquet	Columnar storage, efficient for large data
Excel	.xlsx	Microsoft Excel workbooks
JSON	.json	Full result as a JSON array
JSONL	.jsonl	One JSON object per line, streaming-friendly

Technical Details

DuckDB-WASM

The app uses DuckDB's WebAssembly build to run SQL queries entirely in the browser. DuckDB is an in-process SQL OLAP database that excels at analytical queries on large datasets. The WASM build runs at near-native speed using WebAssembly SIMD instructions.

CTE Pipeline

Each enabled step becomes a CTE (Common Table Expression) in a single SQL query. The CTE builder chains them together, resolving prev references to the prior step's CTE name. Cross-query references (e.g., FROM other_query) are resolved by inlining the referenced query's compiled SQL as a subquery.

State Management

Zustand stores manage all application state. The project file (etlstudio.json) is serialized/deserialized via the File System Access API and persisted to the user's local file system. Auto-save triggers 800ms after the last change.

File System Access API

The app uses the File System Access API (Chromium browsers) to read and write project files directly to the user's file system. Folder connections use the same API to register directory handles, enabling DuckDB to query files in linked folders.

PWA & Caching

The app uses vite-plugin-pwa with Workbox for service worker management:

App shell (HTML, CSS, JS, icons) — precached on install for offline use
DuckDB WASM binaries — cache-first with expiration (30-day TTL, max 5 entries)
DuckDB worker scripts — cache-first with expiration (30-day TTL, max 5 entries)
Navigation requests — network-first, fall back to cached index.html

Build & Deploy

Development

npm install
npm run dev

Runs at http://localhost:5173 with HMR.

Production Build

npm run build

Output goes to dist/. Serve with any static file server.

Deployment

Push to any static host (GitHub Pages, Netlify, Vercel, Cloudflare Pages, S3). The included GitHub Actions workflow handles GitHub Pages deployment automatically.

← Back to ETL Studio · README