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

> Master ETA field availability and NULL values in Bambu Lab Tracker queries. Learn to filter, replace, or count non-NULL values using standard SQL and DuckDB for accurate data.

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

---

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

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

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

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

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

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

```sql
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) demonstrates loading multiple Parquet shards. Extend this pattern to handle NULL ETAs using Python and DuckDB:

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

```python
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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/main/README.md)](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/README.md) |
| [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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/main/script.py)](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py) |
| [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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/main/reconstruct_db.py)](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py) |
| [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) | Remote JSON file listing all available Parquet shards; used to programmatically discover files before querying ETA data. | [[`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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.