DuckDB as storage: replacing ETL Postgres

DuckDB as a Compact Analytics Warehouse: Ditching ETL and Postgres

DuckDB lets you build an analytics warehouse in a single .duckdb file, skipping ETL pipelines and Postgres for OLAP workloads. The engine directly processes Parquet, CSV, and JSON from local files or S3/GCS, delivering fast data access for dashboards and reports without distributed infrastructure. It's perfect for resource-constrained teams prioritizing data freshness and reproducibility.

Pain Points of the Traditional ETL + Postgres Stack

Postgres handles OLTP workloads well, but OLAP queries create cascading issues: indexes slow down vacuuming, aggregates compete with transactions, and ETL jobs tangle with schema changes. Teams end up spending more time on admin tasks than analysis. DuckDB eliminates these failure points as an embedded OLAP engine without persistent storage.

The architecture simplifies to:

• Sources (DBs, S3, CSV) → Parquet snapshots → DuckDB (bronze/silver/gold layers) → BI exports.

The pipeline boils down to a scheduled script with views and exports, minimizing ops overhead.

SLAs for a DuckDB Micro-Warehouse

You can deliver clear SLAs without cluster complexity:

• Data Freshness: Updates every 15–60 minutes with timestamp logging.
• Accuracy: Metrics in version-controlled SQL views with code review.
• Performance: p95 < 2s for dashboards on 90 days of data.
• Recovery: Full rebuild from Parquet in <30 minutes.
• Ops/Cost: One file + one job, no running storage costs.

The .duckdb file is easy to back up and move around.

Use Case in Product Analytics

For B2B SaaS with events in a relational DB and logs in S3:

• Daily snapshots of tables to partitioned Parquet.
• Events as date-partitioned Parquet files.
• Gold-layer metric views (WAUs, retention cohorts).
• Export curated Parquet for BI tools.

The httpfs extension enables direct S3 reads without local copies. DuckDB aggregates multiple Parquet files as a single table.

Practical Implementation Scripts

Update Script (Python)

import duckdb
from datetime import datetime

DB = "micro_warehouse.duckdb"

con = duckdb.connect(DB)
con.execute("INSTALL httpfs;")
con.execute("LOAD httpfs;")

con.execute("""
CREATE OR REPLACE VIEW bronze_events AS
SELECT *
FROM read_parquet('data/events/date=*/events-*.parquet');
""")

con.execute("""
CREATE OR REPLACE VIEW silver_events AS
SELECT
  CAST(event_time AS TIMESTAMP) AS event_time,
  user_id,
  team_id,
  event_name,
  NULLIF(properties, '') AS properties
FROM bronze_events
WHERE user_id IS NOT NULL;
""")

con.execute("""
CREATE OR REPLACE VIEW gold_weekly_active_teams AS
SELECT
  date_trunc('week', event_time) AS week,
  COUNT(DISTINCT team_id) AS weekly_active_teams
FROM silver_events
GROUP BY 1
ORDER BY 1;
""")

con.execute("""
COPY (SELECT * FROM gold_weekly_active_teams)
TO 'exports/gold_weekly_active_teams.parquet' (FORMAT PARQUET);
""")

con.execute("CREATE OR REPLACE TABLE ops_refresh_log AS SELECT 1 AS dummy;")
con.execute("""
INSERT INTO ops_refresh_log
SELECT 1
""")

print("Update OK:", datetime.utcnow().isoformat(), "DB:", DB)

This script builds bronze/silver/gold layers and exports metrics.

Incremental Updates

For upserts, use MERGE on keys (week, team_id) into a materialized table, recalculating sliding windows.

Zero-Downtime Migration

Keep OLTP in Postgres and migrate OLAP to DuckDB via the postgres_scanner extension for read/write access to a live instance. Gradually shift queries without refactoring.

Limitations of DuckDB as a Warehouse

Great for:

• A few analysts (dashboards, exports).
• Append-only or snapshot data.
• Portable Parquet workflows.

Not for:

• High concurrency with interactive users.
• OLTP writes with ACID guarantees.
• Multi-tenant row-level security.
• Enterprise data contracts with many producers.

Key Takeaways

• DuckDB is an embedded OLAP engine for a single file, reading Parquet/S3 directly.
• Simplifies your stack: script + views instead of ETL + Postgres.
• Achievable SLAs: 15-min freshness, p95<2s, <30-min recovery.
• Gradual migration via postgres_scanner.
• Limitations: low concurrency, not for OLTP.

— Editorial Team