Build a Multi-Tenant Revenue Analytics SaaS with DuckDB — One Codebase, 10 Customers
A recurring request many freelance data analysts get: automatically generate daily revenue reports for e-commerce store owners. The common approach — Python scripts plus manual SQL — is slow and error-prone. Today, I’ll walk through building a lightweight, reusable multi-tenant automated reporting system with DuckDB that you can productize: one codebase, ten customers, near-zero marginal cost.
Why DuckDB for a SaaS Backend
Traditional approaches using Pandas + CSV hit walls with e-commerce order data: memory explosions, I/O bottlenecks, and sluggish execution at scale. DuckDB’s core advantages:
- Single file = full database: One
.duckdbfile is a complete database, no services to start - Columnar storage + vectorized execution: 10-50x faster aggregations than Pandas on the same data
- Natural multi-tenant isolation: One table per shop, data isolated within the same file
- Zero operations: No PostgreSQL, MySQL, or any server database required
Project Structure
duck_revenue_saaS/
├── config.py
├── engine.py
├── report_generator.py
├── queries/
│ ├── daily_summary.sql
│ ├── category_breakdown.sql
│ └── refund_analysis.sql
├── templates/
│ └── report.html
└── main.py
Step 1: Engine Layer — Multi-Tenant Data Isolation
DuckDB’s CREATE INDEX uses compressed indexes that are more space-efficient than regular B-Trees and are maintained automatically.
# engine.py
import duckdb
from contextlib import contextmanager
class DuckRevenueEngine:
"""Multi-tenant DuckDB engine — one file manages all shops"""
def __init__(self, db_path: str = "revenue.duckdb"):
self.db_path = db_path
@contextmanager
def connection(self):
conn = duckdb.connect(self.db_path, read_only=False)
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
def init_schema(self, shop_id: str):
"""Initialize table schema for each shop"""
with self.connection() as conn:
conn.execute(f"""
CREATE TABLE IF NOT EXISTS {shop_id}_orders (
order_id VARCHAR PRIMARY KEY,
shop_id VARCHAR,
order_time TIMESTAMP,
customer_id VARCHAR,
amount DECIMAL(12, 2),
refund_amount DECIMAL(12, 2) DEFAULT 0,
status VARCHAR,
category VARCHAR,
channel VARCHAR
)
""")
conn.execute(f"""
CREATE INDEX IF NOT EXISTS idx_{shop_id}_time
ON {shop_id}_orders(order_time)
""")
conn.execute(f"""
CREATE INDEX IF NOT EXISTS idx_{shop_id}_cust
ON {shop_id}_orders(customer_id)
""")
print(f"✅ Shop {shop_id} schema initialized")
Key design: All shops share the same DuckDB file, isolated by table name prefix. Benefits:
- Deployment is trivially simple — one file
- Cross-shop analysis works via direct JOIN, no distributed architecture needed
- Backup is a single file copy
Step 2: SQL Template Queries
Split SQL queries into independent .sql files using {{placeholder}} syntax, replaced in Python at runtime. This separates SQL from code, making maintenance and auditing straightforward.
-- queries/daily_summary.sql
SELECT
shop_id,
DATE(order_time) AS order_date,
COUNT(*) AS total_orders,
SUM(amount - refund_amount) AS net_gmv,
AVG(amount - refund_amount) AS avg_order_value,
ROUND(
SUM(refund_amount) * 100.0 / NULLIF(SUM(amount), 0),
2
) AS refund_rate_pct
FROM {{shop_table}}
WHERE DATE(order_time) = DATE('{{date}}')
GROUP BY shop_id, DATE(order_time)
ORDER BY order_date DESC
-- queries/category_breakdown.sql
SELECT
category,
COUNT(*) AS order_count,
SUM(amount - refund_amount) AS category_gmv,
ROUND(
SUM(amount - refund_amount) * 100.0 /
NULLIF(SUM(SUM(amount - refund_amount)) OVER(), 0),
2
) AS gmv_share_pct
FROM {{shop_table}}
WHERE DATE(order_time) = DATE('{{date}}')
GROUP BY category
ORDER BY category_gmv DESC
LIMIT 10
-- queries/refund_analysis.sql
SELECT
DATE(order_time) AS refund_date,
order_id,
amount,
refund_amount,
customer_id,
category
FROM {{shop_table}}
WHERE DATE(order_time) = DATE('{{date}}')
AND refund_amount > 0
ORDER BY refund_amount DESC
LIMIT 20
Note that {{shop_table}} and {{date}} are runtime placeholders, not SQL parameter bindings. This templating approach works well for batch report generation where each shop gets its own replacement.
Step 3: Report Generator
# report_generator.py
import duckdb
from pathlib import Path
from datetime import date, datetime, timedelta
import jinja2
class ReportGenerator:
def __init__(self, engine, template_dir: str = "templates"):
self.engine = engine
self.env = jinja2.Environment(
loader=jinja2.FileSystemLoader(template_dir),
autoescape=True
)
def generate_report(self, shop_id: str, report_date: date) -> str:
table_name = f"{shop_id}_orders"
date_str = report_date.strftime("%Y-%m-%d")
with self.engine.connection() as conn:
summary = self._run_query(
conn, "queries/daily_summary.sql",
shop_table=table_name, date=date_str
)
categories = self._run_query(
conn, "queries/category_breakdown.sql",
shop_table=table_name, date=date_str
)
refunds = self._run_query(
conn, "queries/refund_analysis.sql",
shop_table=table_name, date=date_str
)
yesterday = self._run_query(
conn, "queries/daily_summary.sql",
shop_table=table_name,
date=(report_date - timedelta(days=1)).strftime("%Y-%m-%d")
)
template = self.env.get_template("report.html")
return template.render(
shop_id=shop_id,
report_date=report_date,
summary=summar.fetchone() if summary else None,
categories=categories.fetchall() if categories else [],
refunds=refunds.fetchall() if refunds else [],
yesterday=yesterday.fetchone() if yesterday else None,
now=datetime.now().strftime("%Y-%m-%d %H:%M")
)
def _run_query(self, conn, query_file: str, **params):
sql_text = Path(__file__).parent / query_file
sql_text = sql_text.read_text()
for key, value in params.items():
sql_text = sql_text.replace(f"{{{{{key}}}}}", str(value))
return conn.execute(sql_text)
Step 4: Main Entry Point & Scheduling
# main.py
import argparse
from datetime import date
from pathlib import Path
from engine import DuckRevenueEngine
from report_generator import ReportGenerator
def main():
parser = argparse.ArgumentParser()
parser.add_argument("--date", type=str, default=date.today().isoformat())
parser.add_argument("--shops", nargs="+", required=True)
parser.add_argument("--output", default="reports/")
args = parser.parse_args()
engine = DuckRevenueEngine("revenue.duckdb")
generator = ReportGenerator(engine)
for shop_id in args.shops:
report_date = date.fromisoformat(args.date)
html = generator.generate_report(shop_id, report_date)
out_path = f"{args.output}/{shop_id}_{args.date}.html"
Path(out_path).parent.mkdir(parents=True, exist_ok=True)
Path(out_path).write_text(html)
print(f"📊 {shop_id} report generated: {out_path}")
if __name__ == "__main__":
main()
Schedule with cron:
# Generate all shop reports daily at 22:00
0 22 * * * cd /home/user/duck_revenue_saaS && python main.py --shops shop001 shop002 shop003
Performance Comparison: Traditional vs DuckDB
| Dimension | Pandas + CSV | DuckDB |
|---|---|---|
| Data scale limit | Memory-bound, slows at ~5M rows | Columnar compression, handles 1B+ rows |
| Query speed | Row-by-row iteration, slow aggregation | Vectorized execution, 10-50x faster |
| Deployment complexity | File I/O + memory management | Single file, zero configuration |
| Multi-tenant isolation | Manual file splitting | Table-level isolation, native support |
| Cross-shop analysis | Merge multiple CSVs first | Direct JOIN across same-db tables |
| Memory footprint | High (full load) | Low (columnar + compressed) |
Deployment Options
- Lightweight: Single Docker + cron, suitable for ≤10 clients
- Standard: FastAPI + DuckDB + Redis cache, supports web access
- Advanced: DuckDB Cloud or cloud-native deployment, multi-region support
# Quick start
pip install duckdb jinja2
python main.py --shops shop001 shop002 --date 2026-08-18
Learn more DuckDB hands-on experience → duckdblab.org
本文信息
| 项目 | 内容 |
|---|---|
| DuckDB 版本 | v1.5.x（部分功能基于 v2.0 Preview） |
| 最后验证 | 2026-09-12 |
| 测试环境 | Linux / x86_64 / 16GB RAM |
| 官方文档 | DuckDB Documentation |
| GitHub | pengzz9527/duckdb-blog |
如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。