DuckDB is changing backend analytics by querying files directly, eliminating unnecessary ETL pipelines, and simplifying production data workflows.

Most engineers first discover DuckDB because someone posts an absurd benchmark on social media.

"Query a 20GB CSV in seconds."

"Read Parquet files faster than PostgreSQL."

"Process billions of rows on a laptop."

The speed is impressive.

But it's also the least interesting thing about DuckDB.

The real shift isn't performance.

It's the realization that you've probably been moving data around for years simply because your database required it.

Traditional Analytics Workflow

                Raw CSV
                   │
                   ▼
           Upload to S3
                   │
                   ▼
          ETL Transformation
                   │
                   ▼
        PostgreSQL Warehouse
                   │
                   ▼
          Create Indexes
                   │
                   ▼
        Run Analytics Queries

Lots of movement.

Lots of waiting.

Lots of infrastructure.

DuckDB Workflow

            Raw CSV
               │
               ▼
      SELECT * FROM 'sales.csv';

Or…

          Parquet Files
                │
                ▼
 SELECT * FROM 'orders/*.parquet';

Or…

       S3 Bucket
          │
          ▼
SELECT *
FROM read_parquet(
's3://analytics/events/*.parquet'
);

No warehouse.

No import.

No migration.

No staging tables.

Traditional PostgreSQL Analytics

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:password@localhost:5432/analytics"
)

df = pd.read_csv("orders.csv")

df.to_sql(
    "orders",
    engine,
    if_exists="append",
    index=False,
)

query = """
SELECT
    customer_id,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC
LIMIT 10;
"""

result = pd.read_sql(query, engine)

print(result)

DuckDB Equivalent

import duckdb

result = duckdb.sql("""

SELECT
    customer_id,
    SUM(total_amount) AS revenue
FROM 'orders.csv'
GROUP BY customer_id
ORDER BY revenue DESC
LIMIT 10

""").df()

print(result)

Reading Multiple CSV Files

import duckdb

df = duckdb.sql("""

SELECT
    *
FROM 'logs/*.csv'

""").df()

Reading Thousands of Parquet Files

import duckdb

orders = duckdb.sql("""

SELECT
    customer_id,
    product_id,
    quantity,
    total_amount
FROM 'warehouse/orders/**/*.parquet'

""").df()

Querying Partitioned Data

query = """

SELECT
    DATE(order_date) AS day,
    SUM(total_amount) AS revenue
FROM 'warehouse/orders/year=*/month=*/*.parquet'
GROUP BY day
ORDER BY day

"""

duckdb.sql(query).show()

Joining Multiple Files

query = """

SELECT
    o.order_id,
    o.total_amount,
    c.customer_name,
    c.country
FROM 'orders.parquet' o
JOIN 'customers.parquet' c
ON o.customer_id = c.customer_id

"""

duckdb.sql(query).df()

Reading JSON Without Importing

query = """

SELECT
    id,
    payload.user.name,
    payload.device.os
FROM read_json_auto(
    'events.json'
)

"""

duckdb.sql(query).show()

Mixing CSV + JSON + Parquet

query = """

SELECT
    o.customer_id,
    c.country,
    e.event_name
FROM 'orders.parquet' o
JOIN 'customers.csv' c
ON o.customer_id = c.customer_id
JOIN read_json_auto('events.json') e
ON o.customer_id = e.customer_id

"""

duckdb.sql(query).show()

Working Directly With Pandas

import pandas as pd
import duckdb

orders = pd.read_csv("orders.csv")

result = duckdb.sql("""

SELECT
    customer_id,
    AVG(total_amount)
FROM orders
GROUP BY customer_id

""").df()

Using Polars

import polars as pl
import duckdb

orders = pl.read_parquet("orders.parquet")

result = duckdb.sql("""

SELECT
    customer_id,
    COUNT(*),
    SUM(total_amount)
FROM orders
GROUP BY customer_id

""").pl()

Querying a DataFrame Without Loading Anything Else

import duckdb
import pandas as pd

users = pd.read_csv("users.csv")
payments = pd.read_csv("payments.csv")

result = duckdb.sql("""

SELECT
    u.name,
    SUM(p.amount)
FROM users u
JOIN payments p
ON u.id=p.user_id
GROUP BY u.name

""").df()

Building a FastAPI Analytics Endpoint

from fastapi import FastAPI
import duckdb

app = FastAPI()

@app.get("/analytics/top-customers")
async def top_customers():

    result = duckdb.sql("""

    SELECT
        customer_id,
        SUM(total_amount) revenue
    FROM 'orders.parquet'
    GROUP BY customer_id
    ORDER BY revenue DESC
    LIMIT 20

    """).df()

    return result.to_dict("records")

Querying Multiple Years

query = """

SELECT
    YEAR(order_date) AS year,
    COUNT(*) orders,
    SUM(total_amount) revenue
FROM 'lake/orders/**/*.parquet'
GROUP BY year
ORDER BY year

"""

duckdb.sql(query).show()

Export Query Results

duckdb.sql("""

COPY (

SELECT
    customer_id,
    SUM(total_amount) revenue
FROM 'orders.parquet'
GROUP BY customer_id

)

TO 'customer_report.parquet'

(FORMAT PARQUET); """)

Export to CSV

duckdb.sql("""

COPY (

SELECT *
FROM 'orders.parquet'

)

TO 'orders.csv'

(FORMAT CSV, HEADER);""")

Running Analytical Views

con = duckdb.connect()

con.execute("""

CREATE VIEW daily_sales AS

SELECT
    DATE(order_date) day,
    SUM(total_amount) revenue
FROM 'orders.parquet'
GROUP BY day """)

result = con.sql(""" SELECT * FROM daily_sales ORDER BY day""").df()

Temporary In-Memory Database

import duckdb

con = duckdb.connect(":memory:")

con.execute(""" CREATE TABLE sales AS SELECT * FROM 'sales.parquet' """)

con.sql(""" SELECT COUNT(*) FROM sales """).show()

Persistent Local Database

import duckdb
con = duckdb.connect("analytics.db")

con.execute(""" CREATE TABLE orders AS SELECT * FROM 'orders.parquet' """)

Project Structure

analytics/

│
├── api/
│   ├── routes.py
│   ├── dependencies.py
│
├── data/
│   ├── orders.parquet
│   ├── customers.parquet
│
├── queries/
│   ├── revenue.sql
│   ├── customers.sql
│
├── services/
│   ├── analytics.py
│
├── main.py
│
└── requirements.txt

The biggest mistake isn't choosing PostgreSQL over DuckDB.

It's assuming every analytical question deserves another ETL pipeline.

Instead of adding another warehouse, another scheduled job, or another transformation layer, smart teams increasingly treat DuckDB as a query engine that sits directly beside their application.

The architecture becomes smaller.

The code becomes easier to reason about.

And most importantly, developers spend their time answering business questions instead of maintaining pipelines.

Building an Analytics Service Layer

# services/analytics.py

import duckdb
from pathlib import Path

DB = duckdb.connect("analytics.db")


class AnalyticsService:

    @staticmethod
    def revenue_by_country():

        return DB.sql("""

        SELECT
            country,
            SUM(total_amount) revenue
        FROM 'warehouse/orders/*.parquet'
        GROUP BY country
        ORDER BY revenue DESC

        """).df()

    @staticmethod

    def top_products(limit=20):

        return DB.sql(f"""

        SELECT
            product_name,
            SUM(quantity) sold
        FROM 'warehouse/orders/*.parquet'
        GROUP BY product_name
        ORDER BY sold DESC
        LIMIT {limit}

        """).df()

FastAPI Dependency

from fastapi import Depends
from services.analytics import AnalyticsService

def get_service():
    return AnalyticsService()

Production API

from fastapi import APIRouter, Depends

router = APIRouter()


@router.get("/dashboard")

async def dashboard(

    service=Depends(get_service)

):

    return {

        "countries": service.revenue_by_country().to_dict("records"),

        "products": service.top_products().to_dict("records")

    }

SQL Stored Separately

queries/

├── revenue.sql
├── dashboard.sql
├── retention.sql
├── churn.sql
├── inventory.sql

revenue.sql

SELECT

    customer_id,

    SUM(total_amount) revenue,

    COUNT(*) orders

FROM orders

GROUP BY customer_id

ORDER BY revenue DESC;

Loading SQL Files

from pathlib import Path

query = Path("queries/revenue.sql").read_text()

duckdb.sql(query).df()

Background Refresh Job

from apscheduler.schedulers.blocking import BlockingScheduler

import duckdb

scheduler = BlockingScheduler()


@scheduler.scheduled_job("cron", hour=1)

def refresh():

    con = duckdb.connect("analytics.db")

    con.execute("""

    CREATE OR REPLACE TABLE orders AS

    SELECT *

    FROM 'warehouse/orders/**/*.parquet'

    """)

scheduler.start()

Redis Cache

import redis
import json

cache = redis.Redis(
    host="localhost",
    port=6379,
    decode_responses=True
)

key = "top-products"

cached = cache.get(key)

if cached:
    return json.loads(cached)

data = duckdb.sql("""

SELECT
    product_name,
    SUM(quantity) sold
FROM orders
GROUP BY product_name
ORDER BY sold DESC
LIMIT 50

""").df().to_dict("records")

cache.setex(

    key,

    600,

    json.dumps(data)

)

return data

Reading Directly From S3

import duckdb

con = duckdb.connect()

con.execute("""

INSTALL httpfs;

LOAD httpfs;

SET s3_region='us-east-1';

SET s3_access_key_id='ACCESS_KEY';

SET s3_secret_access_key='SECRET';

""")

result = con.sql("""

SELECT *

FROM read_parquet(

's3://company-data/orders/*.parquet'

)

""").df()

Reading Millions of Records

query = """

SELECT

    customer_id,

    COUNT(*) orders,

    SUM(total_amount) revenue,

    AVG(total_amount) average_order,

    MAX(order_date) last_purchase

FROM 'warehouse/**/*.parquet'

GROUP BY customer_id

HAVING revenue > 1000

ORDER BY revenue DESC

LIMIT 100

"""
duckdb.sql(query).show()

Window Functions

SELECT

    customer_id,

    order_date,

    total_amount,

    SUM(total_amount)

    OVER(

        PARTITION BY customer_id

        ORDER BY order_date

    ) running_total

FROM orders;

Ranking Customers

SELECT

    customer_id,

    revenue,

    DENSE_RANK()

    OVER(

        ORDER BY revenue DESC

    ) customer_rank

FROM revenue_view;

Creating Materialized Analytics Tables

duckdb.sql("""

CREATE TABLE monthly_summary AS

SELECT

    YEAR(order_date) year,

    MONTH(order_date) month,

    SUM(total_amount) revenue,

    COUNT(*) orders

FROM orders

GROUP BY

    year,

    month""")

Export Dashboard Data

duckdb.sql(""" COPY ( SELECT * FROM monthly_summary)
TO 'dashboard.parquet'(FORMAT PARQUET);""")

Dockerfile

FROM python:3.12-slim

WORKDIR /app

COPY requirements.txt .

RUN pip install -r requirements.txt

COPY . .

CMD [

"uvicorn",

"main:app",

"--host",

"0.0.0.0",

"--port",

"8000"

]

docker-compose.yml

version: "3.9"

services:

  api:

    build: .

    ports:

      - "8000:8000"

    volumes:

      - ./warehouse:/app/warehouse

      - ./analytics.db:/app/analytics.db

  redis:

    image: redis:7

Logging Queries

import logging
import time

logger = logging.getLogger(__name__)

start = time.perf_counter()

result = duckdb.sql(query).df()

elapsed = time.perf_counter() - start

logger.info(

    "query_completed",

    extra={

        "duration": elapsed,

        "rows": len(result)

    }

)

Streaming Results

result = duckdb.sql("""

SELECT *

FROM huge_dataset

""")

while True:

    chunk = result.fetch_df_chunk(100000)

    if chunk.empty:
        break

    process(chunk)

Batch Processing

from pathlib import Path

import duckdb

for file in Path("warehouse").glob("*.parquet"):

    duckdb.sql(f""" INSERT INTO consolidated SELECT * FROM '{file}' """)

Register Existing DataFrames

import pandas as pd
import duckdb

orders = pd.read_csv("orders.csv")

customers = pd.read_csv("customers.csv")

duckdb.register("orders", orders)

duckdb.register("customers", customers)

duckdb.sql("""

SELECT c.name, 

SUM(o.total_amount)

FROM customers c

JOIN orders o

ON c.id=o.customer_id

GROUP BY c.name

""").show()

Using MotherDuck

import duckdb

con = duckdb.connect(

"md:production"

)

con.sql("""

SELECT *

FROM sales

LIMIT 100

""")

Backend Architecture

                     FastAPI
                        │
                        ▼
               Analytics Service
                        │
         ┌──────────────┼──────────────┐
         ▼              ▼              ▼
     DuckDB         Redis Cache     SQL Files
         │
         ▼
 Parquet │ CSV │ JSON │ S3 │ Pandas │ Polars

Typical Production Layout

warehouse/
├── customers/
├── orders/
├── inventory/
├── payments/
├── events/
├── exports/
└── reports/

DuckDB isn't replacing PostgreSQL.

It isn't replacing OLTP databases.

It isn't replacing Redis.

It isn't replacing Kafka.

It replaces something much more expensive:

Unnecessary complexity.

For years, backend teams accepted that answering a simple analytical question required moving data through half a dozen systems before SQL could even begin.

DuckDB challenges that assumption.

Instead of asking, "Where should we load this data?", teams increasingly ask, "Can we query it where it already lives?"

That small change in thinking often eliminates thousands of lines of ETL code, scheduled jobs, temporary tables, warehouse maintenance, and infrastructure that quietly accumulated over time.

Fast databases are useful.

Simple architectures are transformative.

DuckDB happens to give you both.