Optimizing Query Performance by Selecting Specific Time Ranges in BBL Tracker

You can dramatically speed up DuckDB queries against the BBL Tracker public database by generating deterministic URLs for only the 6-hour Parquet shards that intersect your target time window, eliminating the need to download the full manifest.

The nelsonjchen/bbl-tracker-public-db repository stores stock snapshot data in time-based Parquet shards with predictable filenames. By leveraging this deterministic naming convention (YYYY-MM-DD-HHMM.parquet), you can construct precise URL lists that minimize data transfer and reduce query latency compared to scanning the entire dataset.

Understanding the 6-Hour Parquet Shard Architecture

The database organizes stock snapshots into discrete time blocks to balance granularity with file size efficiency.

Filename Convention and Predictable URLs

Each shard follows a strict naming pattern based on UTC timestamps:

• Format: YYYY-MM-DD-HHMM.parquet
• Interval: 6-hour blocks (00:00, 06:00, 12:00, 18:00 UTC)
• Base URL: https://db-public.bbltracker.com/

Because the filenames are deterministic, you can calculate exactly which files contain data for any given time range without consulting an index. For example, the window from February 14, 2026 12:00 UTC to 18:00 UTC maps directly to 2026-02-14-1200.parquet and 2026-02-14-1800.parquet.

Manifest vs. Direct URL Generation

The repository provides a manifest.json file that indexes every shard in the bucket. While useful for bulk reconstruction across large date ranges, the manifest is unnecessary for targeted queries. As implemented in script.py, generating URLs directly avoids the overhead of downloading and parsing the full manifest when you only need a specific time window.

Implementing Time-Range Optimized Queries

You can execute targeted queries using either Python with DuckDB or pure SQL, depending on your workflow requirements.

Python Implementation for Custom Time Windows

The following pattern, derived from the repository's script.py, demonstrates how to generate deterministic URLs for a specific 6-hour window and query it with DuckDB:

import duckdb
from datetime import datetime, timedelta

# Base URL of the public bucket

BASE = "https://db-public.bbltracker.com"

# Define start/end timestamps (UTC)

start = datetime(2026, 2, 14, 12, 0)   # 12:00 UTC

end   = datetime(2026, 2, 14, 18, 0)   # 18:00 UTC

# Helper to round datetime down to nearest 6-hour boundary

def round_to_6h(dt):
    hour = (dt.hour // 6) * 6
    return dt.replace(hour=hour, minute=0, second=0, microsecond=0)

# Build list of shard URLs intersecting the window

urls = []
cur = round_to_6h(start)
while cur <= end:
    fname = cur.strftime("%Y-%m-%d-%H%M.parquet")
    urls.append(f"{BASE}/{fname}")
    cur += timedelta(hours=6)

# DuckDB reads remote Parquet files directly

df = duckdb.read_parquet(urls).filter("region = 'us'").filter("stock > 0").df()
print(df.head())

This approach fetches only the three shards covering the 12:00–18:00 UTC window, minimizing network traffic to approximately three times the individual shard size.

SQL-Only Approach with DuckDB

If you prefer working directly in SQL, you can pass a list of generated URLs to DuckDB's read_parquet function. This mirrors the query pattern found in script.py at lines 41-48:

-- Replace with your generated URL list
SELECT *
FROM read_parquet([
    'https://db-public.bbltracker.com/2026-02-14-1200.parquet',
    'https://db-public.bbltracker.com/2026-02-14-1800.parquet'
])
WHERE region = 'us' 
  AND stock > 0;

DuckDB streams these remote files directly without requiring local storage, applying predicate pushdown to filter data at the Parquet level before transferring it to memory.

Bulk Reconstruction for Large Time Spans

For analyses requiring extensive historical data (e.g., the last 30 days), use reconstruct_db.py instead of manual URL generation. As implemented in lines 58-66 of that file, the script builds a comprehensive URL list using the manifest, then creates a local DuckDB table from read_parquet([...]):

# Builds bambu_stock.duckdb with the last 30 days of data

uv run reconstruct_db.py

This approach is optimal when you need to run multiple queries against the same large dataset, as it eliminates repeated remote file access.

Performance Comparison: Targeted vs. Full Manifest Scans

Selecting specific time ranges provides measurable performance benefits over manifest-based approaches:

Approach	Data Downloaded	CPU Cost	Best For
Deterministic URL Generation	Only shards intersecting the time window (typically 1-4 files)	Low – minimal file opens and metadata parsing	Focused analyses like "last 24 hours" or specific promotional periods
Full Manifest Scan	All shards for the selected period (potentially thousands)	High – extensive file enumeration and metadata processing	Long-term trend analysis spanning months or years

By generating URLs directly from the timestamp pattern, you avoid the overhead of downloading manifest.json and filtering through entries that fall outside your query window. This is particularly effective for the 6-hour shard size used in nelsonjchen/bbl-tracker-public-db, where each file represents a manageable, queryable unit.

Summary

• The BBL Tracker database uses 6-hour Parquet shards with deterministic filenames (YYYY-MM-DD-HHMM.parquet) that map directly to UTC time blocks.
• You can optimize query performance by calculating which shards intersect your target time range and generating their URLs directly, bypassing the need to download the full manifest.json.
• DuckDB's read_parquet() function accepts a Python list of URLs, enabling targeted remote queries with predicate pushdown that minimizes data transfer.
• For large historical analyses, use reconstruct_db.py to build a local DuckDB file from the manifest, while focused time windows benefit from the deterministic URL approach shown in script.py.

Frequently Asked Questions

What is the shard size in the BBL Tracker database?

The database stores stock snapshots in 6-hour intervals, with each shard covering a UTC time block ending at 00:00, 06:00, 12:00, or 18:00. Each shard is a separate Parquet file named according to its timestamp (e.g., 2026-02-14-1200.parquet), typically a few megabytes in size.

How do I query data for a specific date without downloading the entire manifest?

Calculate the deterministic URLs for the 6-hour shards that cover your target date using Python's datetime and strftime with the format "%Y-%m-%d-%H%M.parquet". Pass the resulting URL list directly to DuckDB's read_parquet() function. This approach, demonstrated in script.py, avoids the overhead of fetching and parsing manifest.json.

Can I use SQL-only queries with remote Parquet files in DuckDB?

Yes. DuckDB supports passing a list of HTTP URLs directly to the read_parquet() table function in SQL. You can write SELECT * FROM read_parquet(['url1', 'url2']) WHERE ... and DuckDB will stream the remote files while applying predicate pushdown to filter data before transfer. This mirrors the implementation in script.py at lines 41-48.

When should I use reconstruct_db.py instead of direct URL generation?

Use reconstruct_db.py when you need to analyze a large time span (such as the last 30 days) or run multiple queries against the same historical dataset. This script downloads the manifest, builds a comprehensive URL list for the specified range, and creates a local bambu_stock.duckdb file. For focused queries on narrow time windows (hours or single days), direct URL generation is faster and more efficient.

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