How to Query the Bambu Lab Stock History Dataset Using DuckDB and Parquet Files

You can query the Bambu Lab stock history dataset directly from Cloudflare R2 using DuckDB's read_parquet() function on HTTP URLs, eliminating the need to download files locally.

The Bambu Lab stock history dataset is publicly hosted as hourly-partitioned Parquet files in the nelsonjchen/bbl-tracker-public-db repository. This guide explains how to efficiently query the Bambu Lab stock history dataset using DuckDB and Parquet files directly from remote storage, leveraging zero-copy streaming and parallel HTTP fetching.

Understanding the Dataset Structure

File Naming Convention and Partitioning

The dataset resides in a public Cloudflare R2 bucket at https://db-public.bbltracker.com. Each file represents a 6-hour UTC window and follows the deterministic naming pattern YYYY-MM-DD-HHMM.parquet. For example, 2026-02-16-0000.parquet contains data from midnight to 06:00 UTC on February 16, 2026.

The Manifest File

Rather than guessing which files exist, query the manifest.json at the bucket root. This JSON object lists every Parquet file with its row count, enabling you to calculate time ranges without issuing HTTP HEAD requests to individual shards. In script.py, lines 13-21 demonstrate fetching and parsing this manifest.

Setting Up Your DuckDB Environment

You can execute these queries using either the DuckDB Python API or the DuckDB CLI. Both support the read_parquet() function with HTTP(S) URLs. Install DuckDB via pip:

pip install duckdb

Or download the CLI binary from the DuckDB documentation for shell-based workflows.

Querying Remote Parquet Files with DuckDB

Step 1: Fetch the Manifest

Use Python's urllib or requests to retrieve the manifest and identify relevant files:

import json
import urllib.request

manifest_url = "https://db-public.bbltracker.com/manifest.json"
with urllib.request.urlopen(manifest_url) as response:
    manifest = json.loads(response.read())

Step 2: Select Your Time Range

Filter the manifest keys (ISO-formatted dates) to your desired window. In script.py lines 24-30, the code selects the most recent four files. For a 30-day reconstruction, reconstruct_db.py lines 34-40 filter the manifest accordingly:

from datetime import datetime, timedelta, timezone

cutoff = (datetime.now(timezone.utc) - timedelta(days=30)).strftime("%Y-%m-%d")
recent_files = [f for f in sorted(manifest["files"]) if f >= cutoff]
urls = [f"https://db-public.bbltracker.com/{f}" for f in recent_files]

Step 3: Execute SQL Queries

Pass the URL list to read_parquet() and run analytical SQL. DuckDB transparently merges the files:

import duckdb

df = duckdb.query("""
    SELECT 
        product_name,
        variant_name,
        ROUND(100.0 * SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS availability_pct
    FROM read_parquet(?)
    GROUP BY product_name, variant_name
    ORDER BY availability_pct ASC
""", urls).df()

In script.py lines 47-49, the query string is constructed dynamically; reconstruct_db.py lines 62-65 use CREATE TABLE AS SELECT to persist the data locally.

Complete Code Examples

Python: Query a Custom Date Range

This example queries the entire month of February 2026 without downloading files locally:

import duckdb

base = "https://db-public.bbltracker.com"
urls = [
    f"{base}/{day:04d}-{hour:02d}00.parquet"
    for day in range(20260201, 20260301)
    for hour in (0, 6, 12, 18)
]

df = duckdb.query("""
    WITH src AS (
        SELECT * FROM read_parquet(?)
        WHERE region = 'us'
    ),
    agg AS (
        SELECT
            product_name,
            variant_name,
            ROUND(100.0 * SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) /
                  COUNT(*), 1) AS availability_pct,
            MAX(stock) AS max_stock_seen
        FROM src
        GROUP BY product_name, variant_name
    )
    SELECT * FROM agg
    ORDER BY availability_pct ASC
    LIMIT 20
""", urls).df()

print(df)

Python: Reconstruct a Local DuckDB Database

To create a persistent local database containing the last 30 days (mirroring reconstruct_db.py):

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

manifest_url = "https://db-public.bbltracker.com/manifest.json"
manifest = json.loads(urllib.request.urlopen(manifest_url).read())

cutoff = (datetime.now(timezone.utc) - timedelta(days=30)).strftime("%Y-%m-%d")
recent = [f for f in sorted(manifest["files"]) if f >= cutoff]
urls = [f"https://db-public.bbltracker.com/{f}" for f in recent]

db_path = "bambu_stock.duckdb"
if os.path.exists(db_path):
    os.remove(db_path)

con = duckdb.connect(db_path)
con.execute(f"""
    CREATE TABLE stock_history AS
    SELECT * FROM read_parquet({urls});
""")
print("Rows imported:", con.execute("SELECT count(*) FROM stock_history").fetchone()[0])
con.close()

DuckDB CLI: Quick One-Liner

For ad-hoc analysis without Python:

duckdb -c "SELECT product_name, variant_name, AVG(stock > 0)::DOUBLE * 100 AS availability_pct
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
GROUP BY product_name, variant_name
ORDER BY availability_pct ASC
LIMIT 10;"

Why This Approach Is Efficient

Zero-copy streaming: DuckDB reads the compressed columnar Parquet format directly from the remote bucket, transferring only the columns referenced by your query.

Parallel HTTP fetching: DuckDB automatically distributes the list of URLs across worker threads, maximizing bandwidth utilization when querying multiple shards.

On-the-fly schema inference: The Parquet schema is self-describing; DuckDB discovers column types automatically without requiring an external schema definition.

Minimal local storage: You never need to persist gigabytes of raw Parquet files locally unless you explicitly materialize a combined database using reconstruct_db.py.

Key Repository Files

File	Purpose	Link
README.md	High-level documentation, data layout, manifest description, quick-start instructions	README.md
script.py	Example agentic query that fetches the manifest, selects recent shards, and runs an availability-bottleneck SQL query	script.py
reconstruct_db.py	Utility that builds a consolidated DuckDB file from the last 30 days of Parquet shards (useful for AI code-interpreter uploads)	reconstruct_db.py

These files together illustrate the recommended pattern: fetch the manifest → compute the list of URLs for the desired time window → hand that list to DuckDB’s read_parquet() → write SQL for the analysis you need.

Summary

• The Bambu Lab stock history dataset is stored as 6-hour Parquet shards at https://db-public.bbltracker.com with a deterministic naming convention.
• DuckDB can query these remote Parquet files directly via HTTP without local downloads, using read_parquet() with a list of URLs.
• The manifest.json provides a complete index of available files, enabling efficient time-range filtering before querying.
• For persistent local analysis, reconstruct_db.py demonstrates how to materialize a 30-day window into a single DuckDB database file.

Frequently Asked Questions

Do I need to download the Parquet files locally before querying?

No. DuckDB supports reading Parquet files directly from HTTP(S) URLs via the read_parquet() function. Only the specific columns and rows required by your query are streamed over the network, eliminating the need for local storage unless you explicitly choose to materialize the data using a pattern like reconstruct_db.py.

What is the granularity of the stock history data?

Each Parquet file covers a 6-hour UTC window, with files named according to the pattern YYYY-MM-DD-HHMM.parquet. For example, 2026-02-16-0000.parquet contains data from 00:00 to 06:00 UTC on February 16, 2026. The manifest file lists all available shards with their exact row counts.

How do I query only the most recent 30 days of data?

Fetch the manifest.json from https://db-public.bbltracker.com/manifest.json, filter the file list to include only entries with dates greater than or equal to 30 days ago, and pass the resulting URL list to read_parquet(). The reconstruct_db.py script demonstrates this pattern on lines 34-40 by calculating a cutoff date and filtering the manifest keys accordingly.

Can I use the DuckDB CLI instead of Python?

Yes. The DuckDB CLI supports the same read_parquet() function with HTTP URLs. You can execute one-off queries directly from the shell without writing Python code, making it ideal for quick inspections or shell-scripted data pipelines. The CLI automatically handles the HTTP transport and Parquet decoding just as the Python API does.

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