1. The Pain: That Every Friday Afternoon Feels Like Monday

Every data analyst, operations manager, or small business owner knows this moment: it’s Friday afternoon, and you still need to produce the weekly report. You export CSVs from the database, copy them into Excel, build pivot tables by hand, create a few charts, write up conclusions, and send the email to the team. The whole process takes 2–3 hours. If you manage multiple business lines, that time doubles.

Even worse, manual operations are error-prone. Mixing up last week’s data with this week’s, misaligning columns, forgetting chart titles—these mistakes erode trust in your reports and, ultimately, in your team’s decision-making.

We’ll solve this permanently with DuckDB + Python. The goal: every Monday at 8 AM, your weekly report auto-generates and lands on your phone. You just glance at it.

2. Step One: Prepare Test Data

First, we need a simulated sales dataset. In production, this data might come from e-commerce platform exports, ERP system backups, or direct database dumps.

import os
os.makedirs('/tmp/duckdb_weekly', exist_ok=True)

with open('/tmp/duckdb_weekly/sales.csv', 'w') as f:
    import random
    products = ['iPhone', 'iPad', 'MacBook', 'AirPods']
    regions = ['East China', 'North China', 'South China', 'Southwest']
    f.write('date,product,region,revenue\n')
    for day in range(1, 32):
        for _ in range(50):
            f.write(f'2024-01-{day:02d},{random.choice(products)},{random.choice(regions)},{random.randint(5000, 50000)}\n')
print("Data generated: 1600 records ready")

This produces a ~1600-row CSV with four columns: date, product, region, and revenue. This is our foundational dataset.

3. Step Two: Core Query — Generate the Weekly Report with SQL

DuckDB’s greatest strength is that it requires zero database server installation—you call it directly from Python. Its SQL syntax is fully ANSI-compatible, so anyone familiar with relational databases can get started immediately.

import duckdb
from datetime import datetime, timedelta

conn = duckdb.connect()
conn.execute("CREATE TABLE sales AS SELECT * FROM read_csv('/tmp/duckdb_weekly/sales.csv')")

end_date = datetime(2024, 1, 31)
start_date = end_date - timedelta(days=6)

weekly_report = conn.execute("""
    SELECT 
        DATE(date) AS report_date,
        product,
        region,
        SUM(revenue) AS total_revenue,
        COUNT(*) AS order_count,
        AVG(revenue) AS avg_order_value
    FROM sales
    WHERE date BETWEEN ? AND ?
    GROUP BY 1, 2, 3
    ORDER BY total_revenue DESC
""", str(start_date.date()), str(end_date.date())).fetchdf()

print(weekly_report.head(10))

The output is a complete weekly report table containing total revenue, order count, and average order value for each product in each region.

Key points:

• read_csv() is DuckDB’s built-in table function—it auto-detects column types and delimiters, no pre-schema needed
• fetchdf() converts results directly to a Pandas DataFrame for downstream processing
• Parameterized queries (?) prevent SQL injection while supporting dynamic date ranges

4. Step Three: Visualization — Let the Data Speak

A weekly report full of raw numbers isn’t useful to stakeholders. You need charts that make trends and comparisons immediately obvious.

import matplotlib.pyplot as plt

product_summary = weekly_report.groupby('product')['total_revenue'].sum().sort_values(ascending=False)

plt.figure(figsize=(10, 6))
bars = plt.bar(product_summary.index, product_summary.values, color='steelblue')
plt.title('Weekly Revenue by Product', fontsize=16)
plt.ylabel('Revenue (CNY)', fontsize=12)
plt.xlabel('Product', fontsize=12)
plt.tight_layout()
plt.savefig('/tmp/duckdb_weekly/chart.png', dpi=150)
print("Chart saved")

For more sophisticated analysis, you can generate multi-dimensional visualizations:

# Heatmap: Region × Product
pivot_table = weekly_report.pivot_table(
    values='total_revenue', 
    index='region', 
    columns='product', 
    aggfunc='sum'
)
plt.figure(figsize=(12, 6))
plt.imshow(pivot_table, cmap='YlOrRd', aspect='auto')
plt.colorbar(label='Revenue')
plt.xticks(range(len(pivot_table.columns)), pivot_table.columns)
plt.yticks(range(len(pivot_table.index)), pivot_table.index)
plt.title('Revenue Heatmap: Region × Product')
plt.tight_layout()
plt.savefig('/tmp/duckdb_weekly/heatmap.png', dpi=150)

5. Step Four: Telegram Delivery — Report to Your Phone

Data is ready, charts are generated. Now let’s automate delivery. We use a Telegram Bot—it’s free, reliable, and supports image messages natively.

import telebot

BOT_TOKEN = 'your_bot_token_here'
CHAT_ID = 'your_chat_id_here'

bot = telebot.TeleBot(BOT_TOKEN)

message = f"""📊 Weekly Report Generated (2024-01-25 to 2024-01-31)

🏆 Top 3 Products by Revenue:
{weekly_report.head(3).to_string(index=False)}

💰 Total Revenue: {weekly_report['total_revenue'].sum():,.0f} CNY
📦 Total Orders: {weekly_report['order_count'].sum():,}
📈 Avg Order Value: {weekly_report['avg_order_value'].mean():,.0f} CNY
"""

bot.send_photo(
    CHAT_ID, 
    open('/tmp/duckdb_weekly/chart.png', 'rb'), 
    caption=message
)
print("Report sent to Telegram")

How to get your Telegram Bot Token and Chat ID:

1. Search @BotFather in Telegram, send /newbot, follow prompts to create a bot
2. You’ll receive an API Token
3. Message your bot, then visit https://api.telegram.org/bot<YOUR_TOKEN>/getUpdates to find your chat_id

6. Step Five: Automation Scheduling — Let It Run Unattended

Running the script manually is just the beginning. The real value is fully hands-off automation. Two approaches:

Approach 1: Cron Job (Recommended for Linux)

crontab -e

# Run every Monday at 8:00 AM
0 8 * * 1 cd /root && python3 /path/to/weekly_report.py >> /tmp/duckdb_weekly/cron.log 2>&1

Approach 2: APScheduler (Cross-platform, more flexible)

from apscheduler.schedulers.background import BackgroundScheduler
import time

def run_weekly():
    print(f"[{datetime.now()}] Generating weekly report...")
    # Call the generation logic above
    print("Weekly report generated")

scheduler = BackgroundScheduler()
scheduler.add_job(
    run_weekly, 
    'cron', 
    day_of_week='mon', 
    hour=8, 
    minute=0
)
scheduler.start()

while True:
    time.sleep(60)

APScheduler lets you add or modify tasks programmatically—no need to edit system crontabs.

7. Advanced Optimizations: Production-Grade Deployment

The above setup is perfect for personal use or small teams. For a commercial SaaS product, consider these upgrades:

7.1 Parquet Cache Layer

Repeatedly reading CSV files is inefficient. DuckDB natively supports Parquet columnar storage—one conversion, infinite reuse:

# First conversion: CSV → Parquet
conn.execute("""
    CREATE TABLE sales AS 
    SELECT * FROM read_csv('/data/raw/sales_2024/*.csv')
""")
conn.execute("COPY sales TO '/data/parquet/sales.parquet' (FORMAT PARQUET)")

# Subsequent queries read Parquet directly—5-10x faster
conn.execute("""
    CREATE TABLE sales_cache AS 
    SELECT * FROM read_parquet('/data/parquet/sales.parquet')
""")

7.2 Multi-Source Data Fusion

Real business data is scattered across multiple systems. DuckDB’s ATTACH feature lets you connect different sources with zero ETL:

# Mount remote data sources
conn.execute("ATTACH 'sales.db' AS sales_db (TYPE SQLite)")
conn.execute("ATTACH 'finance.duckdb' AS finance_db")

# Cross-source JOIN (zero data movement)
conn.execute("""
    SELECT 
        s.date,
        s.product,
        s.revenue,
        f.cost
    FROM sales_db.sales s
    JOIN finance_db.costs f ON s.order_id = f.order_id
""")

7.3 Web Dashboard with Streamlit

import streamlit as st
import duckdb

st.set_page_config(page_title="Weekly Dashboard", page_icon="📊")
st.title("📊 Real-time Weekly Dashboard")

@st.cache_data(ttl=3600)
def load_data():
    return duckdb.query("SELECT * FROM read_csv('sales.csv')").df()

df = load_data()
st.dataframe(df)

col1, col2, col3 = st.columns(3)
with col1:
    st.metric("Total Revenue", f"{df['revenue'].sum():,.0f} CNY")
with col2:
    st.metric("Total Orders", f"{df.shape[0]:,}")
with col3:
    st.metric("Avg Order Value", f"{df['revenue'].mean():,.0f} CNY")

st.line_chart(df.set_index('date')['revenue'])

Run with streamlit run dashboard.py and share the URL with your team.

8. Traditional Approach vs DuckDB Approach

9. Monetization Paths: From Personal Tool to Business Product

The greatest value of this system isn’t just saving time—it’s that it’s a replicable, sellable, scalable data product prototype. Here are four proven monetization paths:

Path 1: Internal Tool — Every Hour Saved Is Profit

If you work on a data team, building this system saves 2–3 hours per week. At ¥100/hour:

52 weeks × 2.5 hours × ¥100 = ¥13,000/year saved

For a team of 5, that’s ¥65,000/year in efficiency gains. Add in the hidden value of reduced errors, and the ROI is even clearer.

Path 2: SaaS Product — Subscription Revenue

Transform the system into a multi-tenant SaaS offering weekly report generation for SMBs:

• Pricing: ¥99/month per company (3 data sources, 5 charts, Telegram/email delivery)
• Target customers: E-commerce, retail, and restaurant businesses under ¥5M annual revenue
• Acquisition channels: WeChat tech articles, V2EX,即刻, Xiaohongshu
• Projected revenue: 100 paying customers × ¥99 × 12 months = ¥118,800/year

DuckDB’s ATTACH mode is naturally suited for multi-tenancy—each tenant’s data stays isolated in separate files or databases.

Path 3: Outsourcing Service — Per-Project Fees

Many SMBs lack technical staff but need weekly data reports. Offer a “Weekly Report System Customization” service:

• Per-project fee: ¥2,000–5,000
• Delivery timeline: 2–3 days (data collection → analysis → visualization → deployment)
• Ongoing maintenance: Optional ¥1,000/year support contract
• Acquisition: Xianyu, local business WeChat groups

At 2 projects per month conservatively: ¥48,000–120,000/year.

Path 4: Open Source → Consulting & Training

Open-source the core code on GitHub to build technical credibility:

1. Open-source a weekly report template project, accumulate Stars and followers
2. Add a “Paid Consulting” link in README (¥199/hour)
3. Record a DuckDB实战 series course (¥299/set)
4. Offer enterprise training sessions (¥3,000/day)

This path has higher upfront investment and longer payoff cycles, but once established, marginal costs approach zero.

10. Complete Code Repository

To help you get started quickly, here’s a complete runnable project structure:

# Clone the project
git clone https://github.com/duckdblab/weekly-report-system.git
cd weekly-report-system

# Install dependencies
pip install duckdb pandas matplotlib pyTelegramBotAPI apscheduler

# Generate test data
python generate_test_data.py

# Run the report manually
python weekly_report.py

# Or start the scheduler
python scheduler.py

Project structure:

weekly-report-system/
├── config.yaml          # Configuration (DB paths, Bot Token, etc.)
├── weekly_report.py     # Core report generation script
├── scheduler.py         # APScheduler entry point
├── generate_test_data.py # Test data generator
├── templates/           # Email/message templates
└── data/
    ├── raw/             # Raw CSV data
    └── parquet/         # Parquet cache layer

Conclusion

Building an automated weekly report system with DuckDB is, at its core, replacing an entire workflow with a single SQL query. From data ingestion to report delivery, the whole process runs without human intervention. You can finish in the time it takes to drink a coffee what used to take two hours.

More importantly, this system isn’t just an efficiency tool—it’s a replicable, sellable, scalable data product prototype. Whether you want to save your own time or turn it into a business, DuckDB provides the lightest possible technical foundation.

Learn more DuckDB实战经验 → duckdblab.org