Handling ETA Field Availability and NULL Values in Bambu Lab Tracker Queries

To handle ETA field availability and NULL values in Bambu Lab Tracker queries, use standard SQL NULL semantics with DuckDB by filtering with IS NOT NULL, replacing NULLs with COALESCE, or counting non-NULL values with COUNT(column).

The nelsonjchen/bbl-tracker-public-db repository provides hourly stock snapshots for Bambu Lab filaments stored as Parquet files on Cloudflare R2. When querying this dataset using DuckDB, the optional eta column—which indicates estimated arrival times for out-of-stock items—frequently contains NULL values that require specific handling to avoid filtering out valid rows or miscalculating availability statistics.

Understanding the ETA Field Schema

Column Definition and Data Types

According to the repository's README, the Parquet files follow a strict schema where the eta column is defined as a STRING type but is explicitly documented as optional. The column represents the estimated arrival time in ISO 8601 format when stock is expected to return, but many snapshots omit this data when no restock is scheduled.

The schema includes:

• timestamp (STRING): ISO 8601 snapshot time (UTC)
• product_name (STRING): Product family identifier
• variant_name (STRING): Specific SKU variant
• stock (INTEGER): Current quantity available
• region (STRING): Region code (e.g., us, eu)
• eta (STRING): Estimated arrival time—may be missing
• max_quantity (INTEGER): Purchase limit per transaction
• is_flash_sale (BOOLEAN): Flash sale flag

NULL Value Semantics in DuckDB

When DuckDB reads the Parquet files via read_parquet(), it adheres to standard SQL NULL semantics for the eta column:

• NULL ≠ any value: Comparisons such as eta = '2026-03-10' return FALSE for NULL rows, not NULL in boolean contexts.
• Aggregations ignore NULL: Functions like COUNT(eta) count only non-NULL values, while COUNT(*) counts all rows regardless of ETA availability.
• COALESCE substitution: The COALESCE(eta, 'unknown') function returns the first non-NULL argument, allowing you to replace missing ETAs with default placeholders.

SQL Patterns for Handling ETA NULL Values

Filtering Rows with Known ETA

To retrieve only items that have a confirmed restock date, explicitly filter for non-NULL values:

SELECT
    timestamp,
    product_name,
    variant_name,
    stock,
    eta
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE eta IS NOT NULL
  AND region = 'us'
ORDER BY timestamp ASC;

This pattern ensures that rows without estimated arrival times are excluded from results, which is useful when calculating average restock lead times or identifying imminent inventory arrivals.

Replacing NULL with Default Values

When passing data to downstream systems that cannot handle NULLs, use COALESCE to substitute a placeholder string:

SELECT
    timestamp,
    product_name,
    variant_name,
    stock,
    COALESCE(eta, 'unknown') AS eta_status
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE region = 'us';

This approach guarantees that every row returns a string value for the ETA field, preventing null pointer exceptions in Python pandas operations or JSON serialization errors.

Aggregating ETA Availability Statistics

To analyze data coverage quality, calculate what percentage of snapshots include ETA information:

SELECT
    product_name || ' - ' || variant_name AS sku,
    COUNT(*) AS total_snapshots,
    COUNT(eta) AS snapshots_with_eta,
    ROUND(100.0 * COUNT(eta) / COUNT(*), 1) AS eta_coverage_pct
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE region = 'us'
GROUP BY sku
ORDER BY eta_coverage_pct ASC
LIMIT 20;

Note that COUNT(eta) automatically excludes NULL values, while COUNT(*) includes all rows, providing an accurate coverage percentage without additional filtering.

Grouping and Sorting with NULLs

When grouping by ETA values, NULLs may be excluded from results depending on your SQL dialect. Use COALESCE in the GROUP BY clause to ensure NULL rows appear as a distinct category:

SELECT
    COALESCE(eta, 'no_eta') AS eta_group,
    COUNT(*) AS product_count,
    AVG(stock) AS avg_stock
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE region = 'us'
GROUP BY COALESCE(eta, 'no_eta')
ORDER BY 
    CASE WHEN eta_group = 'no_eta' THEN 1 ELSE 0 END,
    eta_group;

For sorting operations, explicitly control NULL placement using NULLS LAST or NULLS FIRST:

SELECT product_name, variant_name, eta, stock
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE region = 'us'
ORDER BY eta NULLS LAST;

Practical Code Examples

CLI Query for Non-NULL ETA

Execute this command in the DuckDB CLI to retrieve only products with confirmed restock dates from a specific snapshot:

SELECT
    timestamp,
    product_name,
    variant_name,
    stock,
    eta
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE eta IS NOT NULL
  AND region = 'us'
ORDER BY timestamp ASC;

This query filters out approximately 30-40% of rows that typically lack ETA data, focusing analysis only on items with scheduled replenishment.

Python Script with COALESCE

The repository's script.py demonstrates loading multiple Parquet shards. Extend this pattern to handle NULL ETAs using Python and DuckDB:

import duckdb

# Load the most recent 4 shards (as implemented in script.py)

urls = [
    f"https://db-public.bbltracker.com/{f}"
    for f in ["2026-02-15-1800.parquet",
              "2026-02-16-0000.parquet",
              "2026-02-16-0600.parquet",
              "2026-02-16-1200.parquet"]
]

# Query that normalizes the ETA column to prevent NULL errors

query = f"""
SELECT
    timestamp,
    product_name,
    variant_name,
    stock,
    COALESCE(eta, 'unknown') AS eta
FROM read_parquet({urls})
WHERE region = 'us'
"""

df = duckdb.query(query).df()
print(df.head())

This approach ensures that pandas DataFrame operations downstream never encounter unexpected None values in the ETA column.

Analyzing ETA Coverage

Use this aggregation query to identify which product variants lack ETA data most frequently:

import duckdb

query = """
WITH snapshots AS (
    SELECT
        product_name,
        variant_name,
        stock,
        eta
    FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
    WHERE region = 'us'
)
SELECT
    product_name || ' - ' || variant_name AS sku,
    COUNT(*) AS total_snapshots,
    COUNT(eta) AS snapshots_with_eta,
    ROUND(100.0 * COUNT(eta) / COUNT(*), 1) AS eta_coverage_pct
FROM snapshots
GROUP BY sku
ORDER BY eta_coverage_pct ASC
LIMIT 20;
"""
print(duckdb.query(query).df())

This analysis helps identify data quality issues or products that rarely have restock estimates available.

Key Repository Files

File	Role	Location
README.md	Documents the full Parquet schema, including the optional eta column definition and dataset overview.	[README.md](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/README.md)
script.py	Example Python script demonstrating DuckDB queries on recent shards; serves as a template for adding ETA-aware logic.	[script.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py)
reconstruct_db.py	Utility to merge multiple Parquet shards into a single local DuckDB database file for persistent ETA analysis.	[reconstruct_db.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py)
manifest.json	Remote JSON file listing all available Parquet shards; used to programmatically discover files before querying ETA data.	[manifest.json](https://db-public.bbltracker.com/manifest.json)

Summary

• The ETA field in the Bambu Lab Tracker dataset is an optional STRING column that frequently contains NULL values when no restock date is scheduled.
• DuckDB follows standard SQL NULL semantics: comparisons with NULL return FALSE, COUNT(column) excludes NULLs, and COALESCE provides safe defaults.
• Filter explicitly using WHERE eta IS NOT NULL when you need only rows with confirmed arrival times.
• Normalize NULLs using COALESCE(eta, 'unknown') to prevent downstream errors in Python pandas or JSON serialization.
• Analyze coverage by comparing COUNT(*) versus COUNT(eta) to identify data quality gaps in specific product variants.

Frequently Asked Questions

How do I exclude rows with missing ETA values from my query results?

Use the IS NOT NULL predicate in your WHERE clause. For example: WHERE eta IS NOT NULL. This filters out rows where the estimated arrival time is absent, ensuring your analysis only includes products with scheduled restock dates.

What is the difference between COUNT(*) and COUNT(eta) in DuckDB?

COUNT(*) returns the total number of rows in your result set, including those with NULL ETA values. COUNT(eta) returns only the number of rows where ETA is not NULL. This distinction is crucial for calculating what percentage of your inventory snapshots include restock estimates.

Can I replace NULL ETA values with a custom string in my query results?

Yes, use the COALESCE function to substitute NULL values with a placeholder. The syntax COALESCE(eta, 'unknown') returns the ETA value if present, or the string 'unknown' if the field is NULL. This prevents errors when exporting data to systems that cannot handle NULL values.

Why do some products never have an ETA value in the dataset?

The ETA field is only populated when Bambu Lab provides an estimated restock date for out-of-stock items. Products that are either currently in stock or permanently discontinued typically have NULL ETA values. Use the aggregation query pattern comparing COUNT(eta) to COUNT(*) to identify which SKUs lack arrival-time data most frequently.

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