# Optimizing Query Performance by Selecting Specific Time Ranges in BBL Tracker

> Speed up BBL Tracker DuckDB queries by selecting specific time ranges. Generate deterministic URLs for 6-hour Parquet shards to avoid downloading the full manifest and boost performance.

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

---

**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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py), demonstrates how to generate deterministic URLs for a specific 6-hour window and query it with DuckDB:

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

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

```bash

# 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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py), avoids the overhead of fetching and parsing [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) at lines 41-48.

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

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