The Problem: Why Does the Same Product Cost 30% More on Different Platforms?

Have you ever searched for something on Taobao and found the exact same product listed at prices varying by up to 30% across three different shops?

You might think it’s coincidence. It’s not. Merchants use dynamic pricing algorithms—prices drop for high-volume items, promotions for overstocked goods, and competitors’ price changes trigger automatic adjustments.

You can manually compare a few items, but you can’t do it every day. What if you could build an automated price comparison system that runs daily, alerts you to changes, and surfaces profit opportunities?

In this article, I’ll show you how to build a cross-platform price comparison engine using DuckDB + Python: collecting price data from 3 platforms across 100 SKUs, generating comparison reports, and querying 100,000 daily rows in under 2 seconds.

The best part? No database server needed, no DevOps required, and it runs on a cloud instance.

Who Is This For?

After reading the code, you might think: “Isn’t this just a few SQL queries?”

Here’s the reality: 99% of e-commerce operators cannot build this system themselves.

Their daily routine looks like this:

1. Open 3 merchant dashboards, screenshot prices
2. Compare them manually in Excel
3. Rush to adjust when a competitor lowers prices

If you provide them with an automated tool that fetches, compares, and alerts on price changes—I’ve seen similar SaaS products with 800+ paying customers at .

Your costs? DuckDB is open source and free. A server runs . Your time? One weekend.

Why DuckDB Instead of SQLite?

You might ask: “Can’t I just use SQLite?”

Yes, but it has two critical limitations:

DuckDB is a columnar, in-memory database designed specifically for analytical queries. When reading Parquet files, it only loads the columns you need—if you query 3 fields, it skips the 4th entirely, which is why it’s so fast.

The architecture is straightforward:

Data Collection (Python requests) → Write to Parquet partitions → DuckDB analytical queries → Reports & Alerts

No intermediate database, no table creation, no migrations.

Step 1: Data Collection

Start with a simple price collector. No web scraping framework needed—just a few lines:

import json
from datetime import datetime, date
from pathlib import Path

def fetch_prices(sku_ids):
 """Simulate fetching prices from 3 platforms"""
 results = []
 
 for sku in sku_ids:
 for platform, price in [
 ("Taobao", 129.0),
 ("Pinduoduo", 115.5),
 ("JD.com", 135.0),
 ]:
 results.append({
 "sku_id": sku,
 "platform": platform,
 "price": price,
 "timestamp": datetime.now().isoformat(),
 "date": date.today().isoformat(),
 "url": f"https://{platform}.com/{sku}",
 "in_stock": True,
 "sales_count": 1000 + hash(sku) % 5000,
 })
 
 return results

# Example SKU list
sku_ids = [f"SKU{str(i).zfill(5)}" for i in range(1, 101)]
data = fetch_prices(sku_ids)
print(f"Collected {len(data)} price records")

This generates 300 price records daily (100 SKUs × 3 platforms). Scale to 1,000 SKUs and you have 90,000 records/month—DuckDB handles this volume effortlessly.

In production, replace fetch_prices with real API calls from Taobao Open Platform, Pinduoduo API, or JD.com API.

Step 2: Parquet Partitioned Storage

Now store the collected data as Parquet, partitioned by date:

import duckdb
import pandas as pd

def save_parquet(data, out_dir="price_data"):
 """Save as Parquet with automatic date partitioning"""
 Path(out_dir).mkdir(exist_ok=True)
 
 df = pd.DataFrame(data)
 df["date"] = pd.to_datetime(df["date"])
 
 for d, group in df.groupby("date"):
 date_str = d.strftime("%Y-%m-%d")
 file_path = Path(out_dir) / f"date={date_str}" / "prices.parquet"
 file_path.parent.mkdir(exist_ok=True)
 group.to_parquet(file_path, index=False, engine="pyarrow")

save_parquet(data)

The key insight: files are organized as date=YYYY-MM-DD. DuckDB automatically recognizes this Hive partition format and only scans the relevant date directory—this is partition pruning, and it makes querying massive datasets feel instant.

Step 3: Core Queries—The Price Comparison Engine’s Brain

Now for the critical part—how do you find “which platform is cheapest” and “which SKU has the widest price spread”?

Query 1: Daily Lowest Price Leaderboard

import duckdb

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

# Read all partitioned Parquet files
con.execute("""
 CREATE VIEW daily_prices AS
 SELECT * FROM read_parquet('price_data/date=*/prices.parquet');
""")

# Daily cheapest price per SKU
result = con.execute("""
 SELECT 
 sku_id,
 platform AS cheapest_platform,
 price AS lowest_price,
 url
 FROM (
 SELECT 
 sku_id,
 platform,
 price,
 url,
 ROW_NUMBER() OVER (
 PARTITION BY date, sku_id 
 ORDER BY price ASC
 ) AS rn
 FROM daily_prices
 WHERE date = CURRENT_DATE - INTERVAL 1 DAY
 )
 WHERE rn = 1
 ORDER BY sku_id
""").fetchdf()

print(result.to_string(index=False))

What does this do? It uses the ROW_NUMBER() window function to rank each SKU’s prices and picks the cheapest. DuckDB processes this query faster than writing a 10-line pandas loop.

Query 2: Price Spread Analysis—Finding Profit Margins

# Price spread across platforms for the same SKU
result = con.execute("""
 WITH price_stats AS (
 SELECT 
 sku_id,
 COUNT(DISTINCT platform) AS platform_count,
 ROUND(MIN(price), 2) AS min_price,
 ROUND(MAX(price), 2) AS max_price,
 ROUND(AVG(price), 2) AS avg_price,
 ROUND(MAX(price) - MIN(price), 2) AS price_spread,
 ROUND(
 (MAX(price) - MIN(price)) * 100.0 / NULLIF(MIN(price), 0), 2
 ) AS spread_pct
 FROM daily_prices
 WHERE date = CURRENT_DATE - INTERVAL 1 DAY
 GROUP BY sku_id
 HAVING COUNT(DISTINCT platform) >= 2
 )
 SELECT * FROM price_stats
 ORDER BY price_spread DESC
 LIMIT 20
""").fetchdf()

print(result.to_string(index=False))

What can you do with this? Find the SKUs with the widest price spreads. If a product sells for 129 on Taobao and 115 on Pinduoduo—that 14 yuan spread is pure profit opportunity. You can tell operators: “This SKU has room for competitive pricing on other platforms.”

Query 3: Price Trends—Should You Match?

# 7-day price trend for a specific SKU
result = con.execute("""
 WITH daily_min AS (
 SELECT 
 date,
 sku_id,
 MIN(price) AS min_price
 FROM daily_prices
 WHERE date >= CURRENT_DATE - INTERVAL 7 DAY
 GROUP BY date, sku_id
 )
 SELECT 
 sku_id,
 date,
 min_price,
 ROUND(
 (min_price - LAG(min_price) OVER (
 PARTITION BY sku_id ORDER BY date
 )) * 100.0 / NULLIF(LAG(min_price) OVER (
 PARTITION BY sku_id ORDER BY date
 ), 0) * 100, 2
 ) AS day_over_day_change_pct
 FROM daily_min
 WHERE sku_id = 'SKU00001'
 ORDER BY date
""").fetchdf()

print(result.to_string(index=False))

What does this tell you? If a SKU has dropped in price for 3 consecutive days, a competitor is running a price war. Without a response, sales could plummet. This query tells you when to match prices and when to hold steady.

Step 4: Automated Alerts—Proactive Price Change Notifications

Reports alone aren’t enough. You need proactive alerts:

# Check which SKUs changed price by more than 5%
result = con.execute("""
 WITH latest AS (
 SELECT * FROM daily_prices
 WHERE date = CURRENT_DATE
 ),
 previous AS (
 SELECT * FROM daily_prices
 WHERE date = CURRENT_DATE - INTERVAL 1 DAY
 )
 SELECT 
 l.sku_id,
 l.platform,
 l.price AS new_price,
 p.price AS old_price,
 ROUND((l.price - p.price) / NULLIF(p.price, 0) * 100, 2) AS change_pct
 FROM latest l
 JOIN previous p ON l.sku_id = p.sku_id AND l.platform = p.platform
 WHERE ABS((l.price - p.price) / NULLIF(p.price, 0) * 100) > 5
 ORDER BY change_pct DESC
""").fetchdf()

if len(result) > 0:
 # Send push notification (via DingTalk/Feishu/WeCom webhook)
 for _, row in result.iterrows():
 alert = f"⚠️ SKU {row['sku_id']} price changed {row['change_pct']}% on {row['platform']}!"
 alert += f"\n New: {row['new_price']} | Old: {row['old_price']}"
 print(alert)

Run this daily. If prices change, get notified. Operators don’t need to check data all day—the data comes to them.

Performance Comparison: DuckDB vs Traditional Approaches

Deployment: A 1-CPU, 1GB RAM Server Is Enough

This system has minimal hardware requirements:

You don’t even need a 2C2G server. A 1C1G instance at is more than enough.

# 1. Install dependencies
pip install duckdb pandas pyarrow requests

# 2. Run daily at 9 AM via cron
0 9 * * * cd /path/to/project && python fetch_and_analyze.py

# 3. Use systemd or supervisor to keep it running

Next Steps

1. Replace the mock fetch_prices with real API calls from Taobao, Pinduoduo, or JD.com
2. Run it with your own SKU data
3. Add push notifications—DingTalk group webhooks take 5 lines of code

The core of this system isn’t the technology—it’s the mindset: replacing manual comparison with automation, and replacing gut-feel decisions with data-driven insights.

The technology is straightforward. The hard part is taking that first step.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。