How to Reconstruct a Local DuckDB Database from the Streaming Parquet Dataset

You can reconstruct a local DuckDB database by downloading the manifest index from Cloudflare R2, filtering the desired Parquet shards by date, and using DuckDB's read_parquet() function to stream the remote files directly into a local .duckdb file.

The nelsonjchen/bbl-tracker-public-db repository provides a public, hourly-updated dataset of Bambu Lab filament stock history stored as Parquet files. Reconstructing a local DuckDB database from this streaming Parquet dataset allows you to run complex SQL queries offline without maintaining a live connection to the remote storage.

Understanding the Dataset Architecture

The Manifest File Structure

The dataset is indexed by a lightweight manifest.json file hosted at https://db-public.bbltracker.com/manifest.json. This JSON file contains a files array listing every available Parquet shard in the bucket. According to the source code in reconstruct_db.py (lines 18-24), the script fetches this manifest using urllib.request and parses the JSON to discover available data shards.

Parquet Shard Organization

Each shard represents a 6-hour window of stock history data, stored as an individual Parquet file. The filenames follow an ISO-date prefix pattern (e.g., 2026-02-10T00-00-00Z.parquet), which allows for chronological filtering without parsing file metadata. The repository stores these files on Cloudflare R2, making them accessible via HTTPS for direct streaming.

Step-by-Step Reconstruction Process

Fetching the Manifest Index

The reconstruction process begins by retrieving the manifest to understand what data is available. The reconstruct_db.py script (lines 18-24) implements this by opening a connection to the manifest URL and loading the JSON response into a Python dictionary. This step is essential because the dataset grows continuously, and the manifest provides the authoritative list of current shards.

Filtering Shards by Date Range

Once the manifest is loaded, you typically want to limit the reconstruction to a specific time window to manage local storage size. The reference implementation in reconstruct_db.py (lines 34-40) filters for the last 30 days by comparing the ISO-date prefix of each filename against a calculated cutoff date. This approach avoids downloading metadata for files outside your target range, significantly reducing ingestion time.

Ingesting Remote Parquet into DuckDB

The core reconstruction step leverages DuckDB's ability to read remote Parquet files directly without intermediate downloads. The reconstruct_db.py script (lines 60-66) constructs a SQL query using read_parquet() with a Python list of URLs, then executes CREATE TABLE stock_history AS SELECT * FROM read_parquet([urls]). This streams each Parquet file from Cloudflare R2 directly into the local bambu_stock.duckdb file, creating a persistent stock_history table with the full schema (timestamp, product_name, variant_name, stock, region, eta, max_quantity, is_flash_sale).

Complete Reconstruction Script

The repository provides a ready-made reconstruction script that automates the entire pipeline. Before running, ensure you have DuckDB installed (pip install duckdb or uv add duckdb).

# reconstruct_db.py

# Full implementation from nelsonjchen/bbl-tracker-public-db

# Lines 18-24: Fetch manifest

# Lines 34-40: Filter last 30 days

# Lines 48-55: Create/overwrite local DB

# Lines 60-66: Merge remote Parquet

# Lines 70-73: Verify & exit

import duckdb
import json
import urllib.request
from datetime import datetime, timedelta, timezone
import os

BASE_URL = "https://db-public.bbltracker.com"
MANIFEST_URL = f"{BASE_URL}/manifest.json"
DB_FILE = "bambu_stock.duckdb"

def main():
    # 1. Fetch manifest (lines 18-24)

    print("Fetching manifest...")
    with urllib.request.urlopen(MANIFEST_URL) as response:
        manifest = json.loads(response.read())
    
    # 2. Filter last 30 days (lines 34-40)

    cutoff = datetime.now(timezone.utc) - timedelta(days=30)
    files = manifest["files"]
    selected = [
        f for f in files 
        if datetime.fromisoformat(f[:10]).replace(tzinfo=timezone.utc) > cutoff
    ]
    urls = [f"{BASE_URL}/{fn}" for fn in selected]
    print(f"Selected {len(urls)} shards to ingest")
    
    # 3. Create/overwrite local DB (lines 48-55)

    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)
        print(f"Removed existing {DB_FILE}")
    
    con = duckdb.connect(DB_FILE)
    
    # 4. Merge remote Parquet (lines 60-66)

    print("Ingesting remote Parquet files...")
    con.execute(f"""
        CREATE TABLE stock_history AS
        SELECT * FROM read_parquet({urls});
    """)
    
    # 5. Verify & exit (lines 70-73)

    count = con.execute("SELECT count(*) FROM stock_history").fetchone()[0]
    print(f"Reconstructed database with {count:,} rows")
    con.close()

if __name__ == "__main__":
    main()

Execute the script to generate your local database:

uv run reconstruct_db.py

# or

python reconstruct_db.py

Custom Query Patterns

Ad-Hoc Analysis Without Local Storage

For quick investigations that do not require a persistent local database, you can query the remote Parquet files directly using DuckDB's in-memory mode. This approach is demonstrated in the repository's script.py, which analyzes the latest 24 hours to identify stock bottlenecks:

import duckdb

con = duckdb.connect()

# Query the four most recent shards (last 24h) directly from R2

urls = [
    "https://db-public.bbltracker.com/2026-02-16T00-00-00Z.parquet",
    "https://db-public.bbltracker.com/2026-02-16T06-00-00Z.parquet",
    "https://db-public.bbltracker.com/2026-02-16T12-00-00Z.parquet",
    "https://db-public.bbltracker.com/2026-02-16T18-00-00Z.parquet",
]

df = con.execute(f"""
    WITH availability AS (
        SELECT 
            product_name,
            variant_name,
            COUNT(*) FILTER (WHERE stock > 0)::FLOAT / COUNT(*) * 100 AS availability_pct
        FROM read_parquet({urls})
        WHERE region = 'us'
        GROUP BY product_name, variant_name
    )
    SELECT * FROM availability
    ORDER BY availability_pct ASC
    LIMIT 10;
""").df()

print(df)

This pattern streams data directly from Cloudflare R2 without writing intermediate files, making it ideal for ephemeral analysis tasks.

Querying the Reconstructed Database

Once you have created bambu_stock.duckdb, any DuckDB client can connect to it for persistent querying. The database contains a single stock_history table with columns: timestamp, product_name, variant_name, stock, region, eta, max_quantity, and is_flash_sale.

import duckdb

con = duckdb.connect("bambu_stock.duckdb")

# Calculate average stock and availability percentage by product variant

df = con.execute("""
    SELECT 
        product_name,
        variant_name,
        ROUND(AVG(stock)::FLOAT, 1) AS avg_stock,
        COUNT(*) FILTER (WHERE stock > 0)::FLOAT / COUNT(*) * 100 AS availability_pct
    FROM stock_history
    WHERE region = 'us'
    GROUP BY product_name, variant_name
    ORDER BY availability_pct ASC
    LIMIT 10;
""").df()

print(df)
con.close()

Summary

• The Bambu Lab Store Filament Tracker publishes hourly stock data as Parquet shards on Cloudflare R2, indexed by a manifest.json file.
• Reconstructing a local DuckDB database involves fetching the manifest, filtering shards by date (typically the last 30 days), and using read_parquet() to stream remote files directly into a local .duckdb file.
• The reconstruct_db.py script in nelsonjchen/bbl-tracker-public-db automates this entire pipeline, handling manifest parsing (lines 18-24), date filtering (lines 34-40), database creation (lines 48-55), and Parquet ingestion (lines 60-66).
• For ad-hoc analysis, you can query remote Parquet URLs directly without creating a local file, as demonstrated in script.py.
• The resulting local database contains a single stock_history table with complete schema information for timestamp, product details, stock levels, and regional availability.

Frequently Asked Questions

How much storage space does a 30-day local DuckDB database require?

A 30-day reconstruction typically consumes between 500 MB and 1.5 GB of disk space, depending on data density and compression ratios in the source Parquet files. DuckDB's native storage format is efficient, but you should ensure at least 2 GB of free space before running reconstruct_db.py to accommodate temporary processing overhead.

Can I reconstruct the database for a custom date range instead of the default 30 days?

Yes, you can modify the date filtering logic in reconstruct_db.py (lines 34-40) to adjust the timedelta(days=30) parameter or replace the cutoff calculation with specific start and end dates. The manifest contains ISO-date prefixed filenames that allow precise chronological selection without downloading file metadata.

Does the reconstruction process download raw Parquet files to my local disk?

No, the reconstruction streams Parquet data directly from Cloudflare R2 into DuckDB without saving intermediate .parquet files. The read_parquet() function accepts a list of HTTPS URLs and processes them remotely, writing only the final DuckDB database file (bambu_stock.duckdb) to your local storage.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}