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

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

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:

# 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 (lines 34-68), this query identifies variants with the lowest availability across recent snapshots:

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 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 demonstrates real-time bottleneck detection, while 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 index maintains references to all available shards, but the 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 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.

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