DuckDB Zero-ETL Daily Report: From CSV to Telegram in 5 Minutes

Waking up to a beautifully formatted daily report on your phone isn’t a fantasy—it’s what you build with DuckDB + Python + Telegram Bot in under an hour.

The pain points of traditional reporting systems are clear: you need ETL pipelines, database servers, and scheduling tools (Airflow or cron) just to produce a daily summary. DuckDB’s disruption lies in its radical simplicity: a single CSV file and ten lines of code are enough to go from raw data to push notification.

This guide walks through a practical side hustle: building a zero-ETL automated daily report system with DuckDB, automatically generating core metrics reports for SMEs, and pushing them to Telegram groups or DMs.

1. The Business Logic: What Are You Selling?

Many people think they’re selling “data reports,” but the real value is saving time.

A small e-commerce team spends 30–60 minutes every morning compiling sales data and creating reports. If you can automate this workflow, charging 299 RMB/month, clients will happily pay—because they’re buying back an extra hour of their day.

Pricing strategy reference:

• Basic (core daily metrics): 299 RMB/month
• Advanced (with MoM analysis + Top-N product ranking): 499 RMB/month
• Enterprise (multi-business lines + custom metrics): 999 RMB/month

2. System Architecture: Three-Layer Minimalist Design

The entire system has three layers—no database server, no Airflow:

┌─────────────────────────────────────────────────────┐
│ Data Layer (data stays put) │
│ CSV / Excel / JSON → DuckDB read_csv_auto() │
│ Zero ETL, direct in-place reading │
└────────────────────────┬────────────────────────────┘
 │
 ▼
┌─────────────────────────────────────────────────────┐
│ Compute Layer (DuckDB + SQL) │
│ Python + DuckDB SQL: aggregation, MoM, Top-N │
│ 1–2 seconds for millions of rows │
└────────────────────────┬────────────────────────────┘
 │
 ▼
┌─────────────────────────────────────────────────────┐
│ Delivery Layer (Telegram Bot API) │
│ Format message → Send via Bot API → Schedule push │
│ Supports: DM / Group / Channel │
└─────────────────────────────────────────────────────┘

Core advantage: Runs on a cloud server (or even a local machine). No third-party data services required.

3. Step 1: Prepare Sample Data

Start by generating mock e-commerce daily data to validate the system logic:

import duckdb
import pandas as pd
from datetime import datetime, timedelta
import random

# Generate 30 days of mock e-commerce data
dates = [datetime(2026, 8, 1) + timedelta(days=i) for i in range(30)]
products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headphones"]
categories = ["Digital Accessories", "Peripherals", "Input Devices", "Displays", "Audio"]

data = []
for d in dates:
 for p, c in zip(products, categories):
 orders = random.randint(10, 200)
 revenue = orders * random.uniform(50, 500)
 users = random.randint(50, 500)
 data.append({
 "date": d.strftime("%Y-%m-%d"),
 "product": p,
 "category": c,
 "orders": orders,
 "revenue": round(revenue, 2),
 "users": users,
 })

df = pd.DataFrame(data)
df.to_csv("/tmp/ecommerce_daily.csv", index=False)
print(f"✅ Generated {len(df)} records")

This creates 150 records (30 days × 5 products) with date, product, category, orders, revenue, and active users.

4. Step 2: DuckDB Core Queries (Zero ETL)

DuckDB’s killer feature is read_csv_auto()—reading CSV files directly with automatic type inference, no import needed:

import duckdb

con = duckdb.connect()

# 1. Core metrics (yesterday vs day-before comparison)
yesterday = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")
day_before = (datetime.now() - timedelta(days=2)).strftime("%Y-%m-%d")

daily_report = con.execute("""
 SELECT 
 'Yesterday' as period,
 SUM(revenue) as total_revenue,
 SUM(orders) as total_orders,
 SUM(users) as total_users,
 ROUND(SUM(revenue)/NULLIF(SUM(orders),0), 2) as avg_order_value
 FROM read_csv_auto('/tmp/ecommerce_daily.csv')
 WHERE date = ?
""", [yesterday]).fetchdf()

print("📊 Core Metrics:")
print(daily_report.to_string(index=False))

Key points:

• read_csv_auto() automatically detects date formats and numeric types—no manual schema definition
• Parameterized queries (? placeholders) prevent SQL injection
• NULLIF() prevents division-by-zero errors

5. Step 3: MoM Calculation and Top-N Ranking

A daily report’s value lies in trends, not just absolute values. Use DuckDB’s CASE WHEN for month-over-month calculations:

# 2. MoM growth rate
growth = con.execute("""
 SELECT 
 SUM(CASE WHEN date = ? THEN revenue END) as rev_yesterday,
 SUM(CASE WHEN date = ? THEN revenue END) as rev_day_before,
 ROUND(
 (SUM(CASE WHEN date = ? THEN revenue END) - 
 SUM(CASE WHEN date = ? THEN revenue END)) * 100.0 / 
 NULLIF(SUM(CASE WHEN date = ? THEN revenue END), 0), 
 2) as revenue_growth_pct
 FROM read_csv_auto('/tmp/ecommerce_daily.csv')
""", [yesterday, day_before, yesterday, day_before, day_before]).fetchdf()

# 3. Top 3 products by revenue
top_products = con.execute("""
 SELECT product, 
 SUM(revenue) as revenue,
 SUM(orders) as orders,
 COUNT(DISTINCT date) as active_days
 FROM read_csv_auto('/tmp/ecommerce_daily.csv')
 GROUP BY product
 ORDER BY revenue DESC
 LIMIT 3
""").fetchdf()

print("\n📈 MoM Change:")
print(growth.to_string(index=False))
print("\n🏆 Top 3 Products:")
print(top_products.to_string(index=False))

Performance comparison: For 1M rows, DuckDB SQL aggregation is 10–50× faster than pandas loops, with lower memory usage.

6. Step 4: Telegram Bot Push

Format the report into a clean Telegram message and push it via the Bot API:

import http.client
import json

def send_telegram_message(bot_token, chat_id, message):
 """Send message via Telegram Bot API"""
 conn = http.client.HTTPSConnection("api.telegram.org")
 payload = json.dumps({
 "chat_id": chat_id,
 "text": message,
 "parse_mode": "HTML"
 })
 headers = {'Content-Type': 'application/json'}
 conn.request("POST", f"/bot{bot_token}/sendMessage", payload, headers)
 res = conn.getresponse()
 print(f"Telegram status: {res.status} {res.reason}")
 conn.close()
 return res.status == 200

# Format daily report message
msg = f"""📊 <b>E-commerce Daily Report · {yesterday}</b>

💰 Total Revenue: <b>{daily_report['total_revenue'].values[0]:,.2f} CNY</b>
📦 Orders: <b>{daily_report['total_orders'].values[0]}</b>
👥 Active Users: <b>{daily_report['total_users'].values[0]}</b>
🛒 Avg Order Value: <b>{daily_report['avg_order_value'].values[0]:.2f} CNY</b>

📈 vs Yesterday: <b>{growth['revenue_growth_pct'].values[0]:+.2f}%</b>

🏆 Top 3 Products:
{chr(10).join([f" {i+1}. {r['product']} — {r['revenue']:,.0f} CNY" 
 for i, r in top_products.iterrows()])}

🤖 Powered by DuckDB Automated Daily Report System
"""

# Call send (replace with real token and chat_id)
# send_telegram_message("YOUR_BOT_TOKEN", "YOUR_CHAT_ID", msg)

Telegram Bot setup steps:

1. Search for @BotFather in Telegram, send /newbot to create a bot
2. Follow prompts to set the bot name, get your bot_token (format: 123456789:ABCdefGHIjklMNOpqrsTUVwxyz)
3. Search for your bot and send any message
4. Visit https://api.telegram.org/bot<YOUR_TOKEN>/getUpdates to get your chat_id

7. Step 5: Scheduled Execution

Two deployment options, pick what fits:

Option A: System crontab (recommended for production)

# Run daily at 8:00 AM
0 8 * * * cd ~/duckdb-daily-report && python3 run_report.py >> /var/log/daily_report.log 2>&1

Option B: Python schedule library (good for development/testing)

import schedule
import time

def job():
 print(f"[{datetime.now()}] Starting daily report...")
 # Run queries and push logic
 send_telegram_message("YOUR_BOT_TOKEN", "YOUR_CHAT_ID", msg)
 print("✅ Daily report sent")

schedule.every().day.at("08:00").do(job)

while True:
 schedule.run_pending()
 time.sleep(60)

8. Comparison with Traditional Approaches

Core difference: Traditional approaches move data around; DuckDB computes where the data lives.

10. Advanced Tips

1. Multi-File Glob Reading

When data is spread across multiple CSV files, DuckDB supports glob patterns for direct reading:

-- Read all CSV files from August 2026, auto-merged
SELECT date, SUM(revenue) as daily_revenue
FROM read_csv_auto('/data/sales/2026-08/*.csv')
GROUP BY date
ORDER BY date;

2. Performance: Parallel Reading

DuckDB automatically leverages multi-core CPUs for parallel reading. You can force parallelism:

con = duckdb.connect(config={
 'max_threads': 4,
 'threads_per_query': 4
})

3. Error Handling and Alerting

In production, add exception handling and alerting:

try:
 daily_report = con.execute(...).fetchdf()
 send_telegram_message(token, chat_id, msg)
except Exception as e:
 error_msg = f"❌ Daily report generation failed: {str(e)}"
 send_telegram_message(token, chat_id, error_msg)
 raise

Summary

DuckDB brings reporting back to basics: compute where the data lives. No ETL pipelines, no database servers—a single CSV file plus ten lines of SQL completes the entire workflow from raw data to push notification.

The true value isn’t the technology itself—it’s replicability. Once built for the first client, you can deploy it to more clients rapidly, with marginal cost approaching zero.

💡 更多 DuckDB 实战技巧 → duckdblab.org

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。