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

> Learn to reconstruct a local DuckDB database from streaming Parquet data. Efficiently query remote files directly into your .duckdb file for powerful local analytics.

- Repository: [Nelson Chen/bbl-tracker-public-db](https://github.com/nelsonjchen/bbl-tracker-public-db)
- Tags: how-to-guide
- Published: 2026-03-08

---

**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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`).

```python

# 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:

```bash
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py), which analyzes the latest 24 hours to identify stock bottlenecks:

```python
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`.

```python
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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.