DuckDB Automated Financial Report Generator: A Money-Making System

Tonight I’m sharing a DuckDB project you can directly sell to small and medium enterprises: Automated Financial Report Generator.

Many small businesses spend thousands of yuan monthly on accountants to produce reports, but report templates are highly standardized. With just a few lines of DuckDB SQL, you can compress what used to take 2 hours of manual work down to 30 seconds—that efficiency gap is your business opportunity.

Project Background: A Chain Restaurant’s Pain Point

Imagine you’ve been hired to solve this problem: a chain restaurant brand with 5 locations needs monthly operational reports containing:

• Revenue comparison across stores
• Month-over-month growth rates
• Top-selling menu items
• Anomaly detection for underperforming stores

The raw data is scattered across different Excel files with inconsistent formats. The traditional approach uses Python + Pandas to read, clean, and calculate row by row. But with DuckDB, the entire process becomes remarkably concise.

This project has been validated commercially: a chain restaurant brand was paying 3,000 yuan monthly to an accountant for reports. Our automated solution charged 5,000 yuan one-time plus 1,000 yuan annual maintenance. The client was extremely satisfied since report generation time dropped from 2 hours to 30 seconds.

Core Technology: read_csv_auto Batch Reading

The key acceleration point of this project is read_csv_auto—it automatically infers schema, supports wildcard reading of multiple files, and can directly handle mixed CSV, JSON, and Parquet formats.

-- One line of code to read all store files, replacing traditional Pandas loops
SELECT * FROM read_csv_auto('data/store_*.csv')

The traditional Pandas approach requires:

import glob
all_data = []
for f in glob.glob("data/store_*.csv"):
    all_data.append(pd.read_csv(f))
df = pd.concat(all_data)

DuckDB’s advantages lie in: lazy execution + columnar storage + vectorized computation. Even if data grows from 100K rows to 100M rows, your code doesn’t need to change—performance remains strong.

Complete Code Implementation

1. Generate Sample Data

# Generate simulated order data for 5 stores
import pandas as pd
from datetime import datetime, timedelta
import random

stores = ["A", "B", "C", "D", "E"]
menus = ["Burger", "Pizza", "Salad", "Fries", "Cola", "Coffee", "Cake", "Fried Chicken"]

for store in stores:
    rows = []
    base_date = datetime(2026, 7, 1)
    for day in range(31):
        n_orders = random.randint(50, 200)
        for _ in range(n_orders):
            rows.append({
                "date": (base_date + timedelta(days=day)).strftime("%Y-%m-%d"),
                "store": f"store_{store}",
                "menu": random.choice(menus),
                "quantity": random.randint(1, 5),
                "unit_price": round(random.uniform(15, 68), 2),
            })
    df = pd.DataFrame(rows)
    df.to_csv(f"data/store_{store}.csv", index=False)

# Menu cost table
pd.DataFrame([
    {"menu": m, "cost": round(random.uniform(3, 25), 2)} 
    for m in menus
]).to_csv("data/menu.csv", index=False)

2. Core Analysis SQL

Monthly revenue ranking by store:

SELECT
    store,
    SUM(quantity * unit_price) AS total_revenue,
    COUNT(*) AS total_orders,
    ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
FROM read_csv_auto('data/store_*.csv')
GROUP BY store
ORDER BY total_revenue DESC;

Top 10 menu items + gross margin calculation:

WITH menu_sales AS (
    SELECT
        m.menu AS menu_name,
        SUM(o.quantity) AS total_qty,
        SUM(o.quantity * o.unit_price) AS total_revenue,
        AVG(o.unit_price) AS avg_price
    FROM read_csv_auto('data/store_*.csv') o
    JOIN read_csv_auto('data/menu.csv') m ON o.menu = m.menu
    GROUP BY m.menu
    ORDER BY total_qty DESC
    LIMIT 10
)
SELECT
    menu_name,
    total_qty,
    ROUND(total_revenue, 2) AS total_revenue,
    ROUND(avg_price, 2) AS avg_price,
    ROUND(
        (total_revenue - total_qty * (
            SELECT cost FROM read_csv_auto('data/menu.csv') 
            WHERE menu = menu_name LIMIT 1
        )) 
        / total_revenue * 100, 1
    ) AS gross_margin_pct
FROM menu_sales;

3. Python Glue Layer

import duckdb
import pandas as pd
from datetime import datetime

con = duckdb.connect()

# Read all store data
df = con.sql("SELECT * FROM read_csv_auto('data/store_*.csv')").fetchdf()

# Monthly summary
summary = con.sql("""
    SELECT
        store,
        SUM(quantity * unit_price) AS revenue,
        COUNT(*) AS orders,
        ROUND(AVG(quantity * unit_price), 2) AS avg_order
    FROM df
    GROUP BY store
    ORDER BY revenue DESC
""").fetchdf()

# Rank calculation
summary["revenue_rank"] = summary["revenue"].rank(ascending=False).astype(int)

# Anomaly detection: stores with revenue > 2 standard deviations from mean
mean_rev = summary["revenue"].mean()
std_rev = summary["revenue"].std()
summary["alert"] = summary["revenue"].apply(
    lambda x: "⚠️ Attention Needed" if abs(x - mean_rev) > 2 * std_rev else "✅ Normal"
)

# Output as JSON for web/API integration
result = {
    "generated_at": datetime.now().strftime("%Y-%m-%d %H:%M"),
    "summary": summary.to_dict(orient="records"),
}

print(f"Report generated successfully, {len(summary)} stores")
print(result["summary"])

DuckDB vs Traditional Solutions

Three Monetization Strategies

This project can become three different products:

Option A: Monthly Subscription Service

• Clients upload Excel monthly, you run scripts to generate PDF reports
• Pricing: 299 yuan/month per store, 5 stores = 1,495 yuan/month
• DuckDB processes each analysis in < 1 second, marginal cost near zero

Option B: SaaS Backend

• Build a lightweight web app with FastAPI + DuckDB
• Clients upload data themselves, view reports in real-time
• Pricing: 99 yuan/month per store, multi-tenant support

Option C: One-time Delivery

• Build a complete data analytics pipeline for the enterprise
• Pricing: 3,000-8,000 yuan per project
• Ongoing maintenance billed separately

Key Technique: DuckDB’s Lazy Execution

DuckDB uses a lazy execution strategy—when you execute a query, it doesn’t immediately read all data. Instead, it builds an execution plan first, then the optimizer automatically determines the optimal reading order and computation method.

This means the SQL you write gets automatically optimized by DuckDB. Even if your writing isn’t the most efficient approach, DuckDB will find the optimal execution path.

-- This query will be automatically optimized by DuckDB
SELECT 
    store,
    SUM(quantity * unit_price) AS revenue
FROM read_csv_auto('data/store_*.csv')
GROUP BY store
ORDER BY revenue DESC
LIMIT 5;

Next Steps: Deploy to Server

Deploy the script to a server, configure a cron job, and automatically run and email reports on the 1st of each month. Once this system is running, you’ve built a small passive income engine.

Want to dive deeper into enterprise DuckDB applications? duckdblab.org has a complete tutorial series from beginner to advanced, covering data pipeline construction, performance optimization, and Airflow integration.

Summary

The core value of this automated financial report generator:

1. Efficiency improvement: From 2 hours to 30 seconds
2. Zero maintenance: DuckDB is zero-configuration, copy and use
3. Scalable: Code doesn’t change whether 10 or 100 stores
4. Monetizable: Three business models, near-zero marginal cost

Learning DuckDB gives you not just a tool, but a monetizable business model.

Code has been verified locally. Data is simulated—replace with your actual business data when implementing.