# Comparing Stock Levels Across Different Time Periods with the Bambu Lab Public Database

> Compare stock levels across time periods using the Bambu Lab Public Database. Query Parquet shards with DuckDB or reconstruct your own database to analyze historical inventory trends.

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

---

**To compare stock levels across different time periods in the Bambu Lab Store Filament Tracker, query the time-partitioned Parquet shards directly using DuckDB or build a local database with [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) to analyze historical inventory trends by aggregating the `stock` column across your target date ranges.**

The `nelsonjchen/bbl-tracker-public-db` repository maintains a public dataset of Bambu Lab store filament availability as time-series snapshots stored in Cloudflare R2. Comparing stock levels across different time periods is straightforward because each observation is timestamped and stored in predictable 6-hour partitions, enabling precise temporal analysis without requiring a persistent database server.

## Understanding the Data Architecture

### Time-Partitioned Parquet Shards

The dataset stores raw stock snapshots as **Parquet files** partitioned into 6-hour UTC intervals. Each file follows the deterministic naming pattern `YYYY-MM-DD-HHMM.parquet` (for example, `2026-02-16-0000.parquet` covers midnight to 6:00 AM UTC). These files are publicly accessible via HTTPS URLs at `https://db-public.bbltracker.com/`.

Because the filenames encode their time range, selecting data for specific periods reduces to generating the correct list of URLs based on your target date range.

### The Manifest Index

Before querying, clients typically read the [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) file located at the root of the bucket. According to the source code, this manifest serves as an index of all available Parquet shards, including row counts for validation. The [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) file demonstrates how to fetch this manifest first to discover which files exist before constructing queries.

### Core Data Schema

Each Parquet shard contains rows with the following schema:

- `timestamp`: ISO 8601 datetime of the observation
- `product_name`: Base product identifier (e.g., "PLA Basic")
- `variant_name`: Specific variant (e.g., "Black")
- `stock`: Integer quantity available at snapshot time
- `region`: Geographic market code (e.g., "us", "eu")
- `eta`: Estimated restock date if out of stock
- `max_quantity`: Purchase limit per order
- `is_flash_sale`: Boolean flag for promotional periods

## Query Strategies for Time-Period Comparison

### Direct Remote Querying with DuckDB

For comparisons spanning days or weeks, DuckDB can query remote Parquet URLs directly without downloading files first. This approach works best when comparing stock levels across different time periods for short durations, as it eliminates local storage requirements.

The [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) file demonstrates this pattern by selecting recent shards and running an availability bottleneck query. DuckDB's `read_parquet()` function accepts an array of URLs, enabling you to aggregate metrics across multiple time windows in a single SQL statement.

### Local Database Reconstruction for Deep Analysis

When comparing stock levels across months of historical data, the [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) script provides a more efficient workflow. This utility downloads all shards from the last 30 days and merges them into a single local DuckDB file named `bambu_stock.duckdb`.

As implemented in `nelsonjchen/bbl-tracker-public-db`, the script filters the manifest for recent entries, downloads the corresponding Parquet files, and loads them into a local `stock_history` table. Once reconstructed, you can run complex multi-period comparisons using standard SQL joins and window functions without network latency.

## Practical Code Examples

### Comparing Two Weeks Using Remote Shards

This Python example generates shard URLs for two separate weeks and calculates average stock levels and availability percentages:

```python
import duckdb
from datetime import datetime, timedelta

def shard_urls(start: str, end: str):
    """Generate Parquet URLs for a date range (YYYY-MM-DD format)"""
    start_dt = datetime.fromisoformat(start)
    end_dt = datetime.fromisoformat(end)
    urls = []
    while start_dt <= end_dt:
        for hour in (0, 6, 12, 18):
            fname = f"{start_dt:%Y-%m-%d}-{hour:02d}00.parquet"
            urls.append(f"https://db-public.bbltracker.com/{fname}")
        start_dt += timedelta(days=1)
    return urls

# Define comparison periods

week1 = shard_urls("2026-01-31", "2026-02-06")
week2 = shard_urls("2026-02-07", "2026-02-13")

def analyze_period(urls):
    query = f"""
        SELECT
            product_name,
            variant_name,
            AVG(stock) AS avg_stock,
            SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END)::FLOAT / COUNT(*) * 100 AS in_stock_pct
        FROM read_parquet({urls})
        GROUP BY product_name, variant_name
        ORDER BY avg_stock DESC
    """
    return duckdb.query(query).df()

print("Week 1 Statistics:")
print(analyze_period(week1).head())

print("\nWeek 2 Statistics:")
print(analyze_period(week2).head())

```

This approach leverages DuckDB's ability to read remote Parquet files transparently, computing **average stock levels** and **in-stock percentage** (the ratio of snapshots where inventory was available) for each product variant.

### Analyzing Multi-Month Trends with Local Data

For broader historical comparisons, first build the local database using the reconstruction script:

```bash

# Build local database (downloads last 30 days)

uv run reconstruct_db.py

# Query the local DuckDB file

duckdb bambu_stock.duckdb <<'SQL'
WITH early_period AS (
    SELECT 
        product_name, 
        variant_name, 
        AVG(stock) AS avg_stock
    FROM stock_history
    WHERE timestamp >= '2026-01-01' AND timestamp < '2026-01-15'
    GROUP BY product_name, variant_name
),
late_period AS (
    SELECT 
        product_name, 
        variant_name, 
        AVG(stock) AS avg_stock
    FROM stock_history
    WHERE timestamp >= '2026-01-15' AND timestamp < '2026-02-01'
    GROUP BY product_name, variant_name
)
SELECT
    e.product_name,
    e.variant_name,
    e.avg_stock AS early_avg,
    l.avg_stock AS late_avg,
    ROUND((l.avg_stock - e.avg_stock) / NULLIF(e.avg_stock, 0) * 100, 2) AS pct_change
FROM early_period e
JOIN late_period l USING (product_name, variant_name)
ORDER BY pct_change DESC
LIMIT 20;
SQL

```

The local `stock_history` table preserves the same schema as the remote shards, allowing identical queries to run against either data source.

### Detecting Availability Bottlenecks

Adapted from [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) (lines 34-68), this query identifies variants with the lowest availability across recent snapshots:

```python
import duckdb

# Select recent 24-hour window (4 shards)

urls = [
    "https://db-public.bbltracker.com/2026-02-16-0000.parquet",
    "https://db-public.bbltracker.com/2026-02-16-0600.parquet",
    "https://db-public.bbltracker.com/2026-02-16-1200.parquet",
    "https://db-public.bbltracker.com/2026-02-16-1800.parquet",
]

query = f"""
    WITH snapshot_stats AS (
        SELECT
            product_name || ' - ' || variant_name AS variant,
            COUNT(*) AS total_snapshots,
            SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock_count,
            MAX(stock) AS peak_stock
        FROM read_parquet({urls})
        WHERE region = 'us'
        GROUP BY product_name, variant_name
    )
    SELECT
        variant,
        ROUND(in_stock_count::FLOAT / total_snapshots * 100, 1) AS availability_pct,
        peak_stock
    FROM snapshot_stats
    ORDER BY availability_pct ASC
    LIMIT 10;
"""

print(duckdb.query(query).df())

```

This pattern surfaces inventory constraints by calculating the percentage of time each variant was actually available for purchase, helping identify consistent stock shortages versus temporary sellouts.

## Summary

- **Data is partitioned** into 6-hour Parquet shards with deterministic filenames, making date-range selection straightforward.
- **DuckDB queries** can execute directly against remote URLs for quick comparisons or against a local database built with [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) for intensive analysis.
- **The `stock` column** represents discrete inventory counts at snapshot time, suitable for averaging, trend detection, and availability percentage calculations.
- **File paths matter**: [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) demonstrates real-time bottleneck detection, while [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) enables offline historical studies of stock levels across different time periods.

## Frequently Asked Questions

### How far back does the historical data extend?

The [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) index maintains references to all available shards, but the [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) script specifically targets the last 30 days for the local database build. For longer historical comparisons, you must manually specify older shard URLs in your DuckDB queries or modify the reconstruction script's date filter.

### What is the performance difference between remote and local queries?

Remote queries through `read_parquet()` incur network latency for each shard but require zero local storage. For comparisons spanning more than 2-3 weeks, the local DuckDB approach via [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) is significantly faster because it eliminates HTTP overhead and enables DuckDB to use full table statistics for query optimization.

### Can I compare stock levels across different geographic regions?

Yes. Each row includes a `region` column (e.g., "us", "eu", "cn") indicating the specific store region. When comparing stock levels across different time periods, add `WHERE region = 'us'` (or your target region) to your queries to ensure you're analyzing comparable market data, as inventory patterns vary significantly by geography.

### How accurate are the timestamps for cross-period analysis?

Each shard filename represents a 6-hour UTC window (0000, 0600, 1200, 1800), and the internal `timestamp` column records the exact observation time. For precise period comparisons, aggregate by the `timestamp` column rather than the filename, as this accounts for the exact moment each stock level was recorded.