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

> Query Bambu Lab stock history fast with DuckDB and Parquet. Access data directly from Cloudflare R2 via HTTP URLs no local downloads needed.

- 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 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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py), lines [13-21](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py#L13-L21) 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:

```bash
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:

```python
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) lines [24-30](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py#L24-L30), the code selects the most recent four files. For a 30-day reconstruction, [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) lines [34-40](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py#L34-L40) filter the manifest accordingly:

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

```python
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) lines [47-49](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py#L47-L49), the query string is constructed dynamically; [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) lines [62-65](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py#L62-L65) 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:

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

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

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

## Key Repository Files

| File | Purpose | Link |
|------|---------|------|
| [`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md) | High-level documentation, data layout, manifest description, quick-start instructions | [README.md](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/README.md) |
| [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) | Example agentic query that fetches the manifest, selects recent shards, and runs an availability-bottleneck SQL query | [script.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py) |
| [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) provides a complete index of available files, enabling efficient time-range filtering before querying.
- For persistent local analysis, [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) script demonstrates this pattern on lines [34-40](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py#L34-L40) 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.