DuckDB for Ecommerce Analytics: Skip the Warehouse for Datasets Under 100GB

Your Shopify store did £4m last year. Your entire order history is a few hundred megabytes of Parquet. And someone quoted you a Snowflake bill to analyse it.

That is the mismatch DuckDB fixes. Most ecommerce data is small, and an in-process columnar engine will out-run a cloud warehouse for that scale, at zero infrastructure cost, on the laptop you already own.

Who This Is For

• Ecommerce operators and analysts whose full dataset (orders, line items, customers, events) fits under ~100GB.
• Teams paying for Snowflake, BigQuery or Redshift to query data that would fit in memory.
• Data folk who live in Python and want SQL-speed aggregations next to pandas or Polars.
• Anyone who has waited 40 seconds for a warehouse to wake up so it could scan 2 million rows.

If you are running a multi-brand marketplace with dozens of concurrent analysts hammering the same tables, skip to Where DuckDB Is the Wrong Tool. This may not be for you.

Where DuckDB Fits

DuckDB is an in-process OLAP database. Think “SQLite for analytics”: one dependency, no server, no daemon, no port. It runs inside your Python process, your R session, your CLI, or a Node script, and it is built for the read-heavy aggregate queries that ecommerce reporting is made of.

• Columnar storage and vectorised execution: scans and GROUP BYs that stall row-stores fly here.
• Reads files in place: query Parquet, CSV and JSON directly, no load step.
• Talks to Postgres: scan your live operational DB via the postgres extension.
• Embeds anywhere: one pip install duckdb and you have a warehouse-grade engine in-process.
• Zero infra: nothing to provision, patch, autoscale or leave running overnight burning credits.

flowchart LR
  A[Shopify export] --> B[Parquet files]
  C[Postgres orders] --> D[DuckDB in-process]
  B --> D
  D --> E[Dashboard / report]
  D --> F[pandas / Polars]

The Pattern: Files In, Answers Out

The core move is simple: stop loading data into a database, and start pointing a database at your data. DuckDB reads a folder of Parquet files as if it were a table.

import duckdb

# No load step. Query the files where they sit.
rev = duckdb.sql("""
    SELECT
        date_trunc('month', created_at) AS month,
        count(*)                        AS orders,
        round(sum(total_price), 2)      AS revenue,
        round(avg(total_price), 2)      AS aov
    FROM 'data/orders/*.parquet'
    WHERE financial_status = 'paid'
    GROUP BY 1
    ORDER BY 1
""")

rev.show()          # print it
df = rev.df()       # hand it to pandas
pl = rev.pl()       # or Polars, zero-copy

That query scans a glob of monthly Parquet exports, aggregates a few million rows, and returns in well under a second on a laptop. There is no cluster, no warehouse to resume, no credits ticking. The .df() and .pl() methods hand the result straight to pandas or Polars with no serialisation tax, so DuckDB does the heavy scan and your dataframe library does the last-mile shaping.

Joining Live Postgres Without ETL

The awkward reality of ecommerce analytics is that some data lives in flat exports and some lives in your operational Postgres. DuckDB collapses that gap: attach Postgres and join across both in one query.

import duckdb

con = duckdb.connect()
con.sql("INSTALL postgres; LOAD postgres;")
con.sql("""
    ATTACH 'dbname=shop host=10.0.0.5 user=analytics'
    AS pg (TYPE postgres, READ_ONLY);
""")

# Join cold Parquet history against the live customers table
con.sql("""
    SELECT
        c.country,
        count(DISTINCT o.customer_id) AS buyers,
        round(sum(o.total_price), 2)  AS revenue
    FROM 'data/orders/*.parquet' o
    JOIN pg.public.customers        c ON c.id = o.customer_id
    WHERE o.created_at >= '2026-01-01'
    GROUP BY 1
    ORDER BY revenue DESC
""").show()

READ_ONLY keeps you honest: analytics should never write to the production store. One caveat worth internalising: the Postgres scanner reads over the wire, so a repeated join against a hot table is slower than reading local Parquet. If you query the same Postgres table again and again, pull it into DuckDB once (CREATE TABLE customers AS SELECT * FROM pg.public.customers) or export it to Parquet and read that.

Embedding DuckDB in a Pipeline

DuckDB shines as the transform step in a small, boring, reliable pipeline. Export from Shopify, land as Parquet, transform with SQL, publish a report, and every stage is a file on disk you can inspect.

flowchart TD
  A[Nightly Shopify export] --> B[Raw CSV/JSON]
  B --> C[DuckDB: clean + type]
  C --> D[Curated Parquet]
  D --> E[DuckDB: aggregate]
  E --> F[Metrics table]
  F --> G[Dashboard]

Because the engine is just a library, the “pipeline” is one Python script on a cron. No orchestration platform, no warehouse connection pool, no secrets sprawl. This is the same engine that can back a lightweight reporting layer if you build dashboards straight in Python rather than paying for a BI seat per viewer.

DuckDB also speaks proper analytical SQL, with full window functions and CTEs, so cohort retention, running LTV and rank-within-category all work exactly as they would in a warehouse:

-- Customer cohorts by first-order month, with cumulative revenue
WITH first_order AS (
    SELECT customer_id,
           min(date_trunc('month', created_at)) AS cohort
    FROM 'data/orders/*.parquet'
    GROUP BY customer_id
)
SELECT f.cohort,
       date_trunc('month', o.created_at)                     AS active_month,
       round(sum(o.total_price), 2)                          AS revenue,
       round(sum(sum(o.total_price)) OVER (
             PARTITION BY f.cohort
             ORDER BY date_trunc('month', o.created_at)), 2) AS cumulative
FROM 'data/orders/*.parquet' o
JOIN first_order f USING (customer_id)
GROUP BY 1, 2
ORDER BY 1, 2;

Cloud Warehouse vs DuckDB

The honest comparison, for a store whose data fits under ~100GB:

DuckDB does not win every row, and it is not meant to. It wins the ones that matter for a single store’s analytics: cost, latency and operational weight.

When to Graduate to a Warehouse

Dataset size is the first fork, but it is not the only one. Use this to decide honestly.

flowchart TD
  A[Analytics workload] --> B{Data under ~100GB?}
  B -- No --> W[Warehouse]
  B -- Yes --> C{Many concurrent users?}
  C -- Yes --> W
  C -- No --> D{Multiple writers?}
  D -- Yes --> W
  D -- No --> K[DuckDB]

If your data is small, your consumers are few, and one process owns the writes, use DuckDB. Flip any of those and a warehouse starts earning its bill.

Where DuckDB Is the Wrong Tool

Be clear-eyed. DuckDB is single-node and read-optimised, and these are the failure modes to avoid:

• High-concurrency serving layer: do not put DuckDB behind a public API taking hundreds of simultaneous analytical requests. It is an embedded engine, not a multi-tenant service.
• Transactional / OLTP workloads: this is not a replacement for Postgres. Order capture, inventory decrements and checkout writes belong in your operational database.
• Multi-writer setups: a single DuckDB file allows one writer. Concurrent processes writing the same database will contend; use it as a reader or serialise writes.
• Genuinely huge data: once you are consistently past what a single node’s memory and disk handle comfortably, spreading the scan across a cluster is the right answer.
• Fine-grained RBAC and audit: if you need row-level security and per-user access logs, that is a warehouse feature, not a file on disk.

What to Build First

1. Export your orders to Parquet. One nightly dump from Shopify (or your platform’s API) into a data/orders/ folder, partitioned by month.
2. Write three canonical queries. Monthly revenue and AOV, new-vs-returning revenue split, and top products by margin. Save them as .sql files.
3. Wrap them in one Python script. Read the Parquet glob, run the queries, write results to a metrics/ Parquet or CSV. Put it on a cron.
4. Point a dashboard at the output. A simple Python or static dashboard over the metrics files, no BI seat required.
5. Add the Postgres scanner only when you need live data. Start with cold exports; attach Postgres read-only once a report genuinely needs today’s numbers.

Final Take

The instinct to reach for a cloud warehouse is mostly cargo-culting from companies with genuinely large data. Your store almost certainly does not have that problem. DuckDB gives you warehouse-grade columnar SQL, in-process, over the files you already have, for nothing. It is fast precisely because it is not a distributed system pretending your small data is big.

Start with Parquet exports and three queries. Add Postgres when you need it. Move to a warehouse the day concurrency, multi-writer or true scale forces your hand, and not a day sooner. For most ecommerce stores, that day never comes.

If you want a pragmatic analytics stack built on this (Parquet exports, DuckDB transforms, dashboards without the BI tax), that is what I do.