How to Filter Bambu Lab Stock Data by Region (US, EU, UK, AU, CA) in SQL Queries

You can filter Bambu Lab store inventory by region using standard SQL WHERE clauses on the region column in DuckDB queries against the public Parquet dataset hosted at db-public.bbltracker.com.

The nelsonjchen/bbl-tracker-public-db repository provides a streaming dataset of hourly Parquet files tracking Bambu Lab store filament availability across global markets. Each record includes a region column containing lowercase string codes that identify specific markets, allowing you to isolate inventory data for the United States, European Union, United Kingdom, Australia, or Canada using straightforward SQL predicates.

Understanding the Dataset Schema

According to the repository's README.md, the dataset schema defines the region column as a STRING type storing region codes. This column is present in every hourly Parquet file available in the public database.

The repository organizes data through three core components:

• script.py – Handles manifest discovery by downloading manifest.json to identify available Parquet files, then demonstrates region filtering using read_parquet.
• reconstruct_db.py – Utility script for merging multiple Parquet files into a single DuckDB database file, useful for analyzing regional trends across extended time periods.
• README.md – Documents the complete schema including the region column specification.

Single-Region Filtering

To isolate data for a specific market, apply an equality predicate against the region column. This approach works for any of the five supported regions: us, eu, uk, au, or ca.

SELECT
    timestamp,
    product_name,
    variant_name,
    stock,
    max_quantity
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE region = 'us'
ORDER BY timestamp DESC
LIMIT 20;

The WHERE region = 'us' clause restricts results to United States inventory snapshots.

Multi-Region Filtering with IN Clauses

When analyzing inventory across multiple territories simultaneously, use the IN operator to specify a list of region codes. This eliminates the need for multiple OR conditions and produces cleaner, more maintainable queries.

SELECT
    region,
    product_name,
    COUNT(*) AS snapshots,
    SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock,
    ROUND(100.0 * SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS availability_pct
FROM read_parquet([
    'https://db-public.bbltracker.com/2026-02-15-1800.parquet',
    'https://db-public.bbltracker.com/2026-02-16-0000.parquet'
])
WHERE region IN ('us', 'eu')
GROUP BY region, product_name
ORDER BY availability_pct ASC
LIMIT 15;

This example calculates stock availability percentages across both US and EU markets.

Querying All Five Regions

For comprehensive regional analysis covering all available markets (US, EU, UK, AU, and CA), expand the IN clause to include all five region codes:

SELECT
    region,
    COUNT(DISTINCT product_name) AS distinct_products,
    AVG(stock) AS avg_stock
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0600.parquet')
WHERE region IN ('us', 'eu', 'uk', 'au', 'ca')
GROUP BY region;

This aggregation provides a per-region inventory health check, showing distinct product counts and average stock levels.

Dynamic Region Filtering in Python

The script.py file demonstrates how to programmatically construct region filters. Below is an adapted version that accepts dynamic region lists:

import duckdb

def query_region(parquet_urls, regions):
    # Build the IN-list for SQL

    region_list = ",".join(f"'{r}'" for r in regions)
    sql = f"""
        SELECT *
        FROM read_parquet({parquet_urls})
        WHERE region IN ({region_list})
    """
    return duckdb.query(sql).df()

# Example usage:

urls = [
    "https://db-public.bbltracker.com/2026-02-16-0000.parquet",
    "https://db-public.bbltracker.com/2026-02-16-0600.parquet"
]
df = query_region(urls, ["us", "eu", "uk"])
print(df.head())

This function safely parameterizes region selection by constructing the SQL IN clause from a Python list.

Summary

• The region column in nelsonjchen/bbl-tracker-public-db contains lowercase string codes (us, eu, uk, au, ca) enabling direct SQL filtering.
• Use WHERE region = 'us' for single-market analysis or WHERE region IN ('us', 'eu', ...) for multi-region queries.
• DuckDB's read_parquet function can query remote URLs directly without downloading files locally.
• Reference script.py for implementation patterns including manifest discovery and dynamic SQL generation.
• For large-scale historical analysis, use reconstruct_db.py to merge multiple Parquet files into a single DuckDB database before applying regional filters.

Frequently Asked Questions

What region codes does the Bambu Lab tracker dataset support?

The dataset supports five region codes as strings: us (United States), eu (European Union), uk (United Kingdom), au (Australia), and ca (Canada). These values are stored in the region column of every Parquet file, as documented in the repository's README.md schema table.

Can I filter by multiple regions in a single SQL query?

Yes. Use the IN operator with a list of region codes: WHERE region IN ('us', 'eu', 'uk'). This approach is more efficient than chaining multiple OR conditions and works seamlessly with DuckDB's read_parquet function when querying both local and remote Parquet files.

How do I query regional data without downloading the entire dataset?

DuckDB can query Parquet files directly over HTTP using read_parquet('https://db-public.bbltracker.com/...'). As implemented in script.py, you can apply WHERE region = 'us' predicates to these remote reads, ensuring only filtered results are transferred to your local environment rather than the complete dataset.

Is the region filter case-sensitive?

Yes, the region filter is case-sensitive because the region column contains lowercase strings (us, eu, etc.). Queries using uppercase values like WHERE region = 'US' will return zero results. Always use lowercase region codes when writing filters against this dataset.

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