How to Join product_name and variant_name for Unique Product Identification in BBL Tracker

To create a unique product identifier in the Bambu Lab Store Filament Tracker, concatenate product_name and variant_name using the SQL || operator with a separator, as demonstrated in script.py lines 42-45.

The BBL Tracker public database stores filament inventory data with product families and their specific variants in separate columns. When analyzing stock levels or tracking availability over time, joining these fields creates a stable, human-readable primary key that distinguishes between different colors and finishes within the same product line.

Why Combine product_name and variant_name?

The database schema, documented in the repository's README, defines two distinct string columns:

• product_name: The filament family or product line (e.g., "PLA Matte", "PETG Translucent")
• variant_name: The specific color or finish within that family (e.g., "White", "Black")

Neither column alone provides unique identification. A single product family contains multiple variants, while variant names like "White" appear across many different materials. Joining them resolves this ambiguity and produces values like "PLA Matte - White" that are unique within the dataset.

SQL Concatenation Pattern in script.py

The reference implementation appears in [script.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py) at lines 42-45. The query uses DuckDB's string concatenation operator to build a derived column called full_name:

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

This inline expression requires no temporary tables or post-processing. The || operator concatenates the two source columns with a dash separator, creating a column suitable for GROUP BY clauses, joins, or export to external systems.

Practical Implementation Methods

Depending on your workflow, you can apply this concatenation pattern using SQL directly, Python with DuckDB, or Pandas DataFrames.

DuckDB SQL Inline Query

For ad-hoc analysis, execute the concatenation directly in your SQL query:

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

Python View Creation

For reusable analysis, create a persistent view using the DuckDB Python API. This mirrors the approach in script.py but wraps it in a view for cleaner subsequent queries:

import duckdb

urls = [
    "https://db-public.bbltracker.com/2026-02-16-0000.parquet",
    "https://db-public.bbltracker.com/2026-02-16-0600.parquet"
]

con = duckdb.connect()

con.execute(f"""
    CREATE VIEW stock_with_id AS
    SELECT
        product_name || ' - ' || variant_name AS full_name,
        *
    FROM read_parquet({urls})
""")

df = con.execute("""
    SELECT
        full_name,
        ROUND(100.0 * SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) /
              COUNT(*), 1) AS availability_pct
    FROM stock_with_id
    GROUP BY full_name
    ORDER BY availability_pct ASC
    LIMIT 5
""").df()

print(df)

Pandas DataFrame Processing

If you prefer working with Pandas, load the data and concatenate the columns using vectorized string operations:

import duckdb
import pandas as pd

df = duckdb.read_parquet(
    "https://db-public.bbltracker.com/2026-02-16-0000.parquet"
).df()

df["full_name"] = df["product_name"] + " - " + df["variant_name"]

bottlenecks = (
    df.groupby("full_name")
      .apply(lambda g: (g["stock"] > 0).mean())
      .reset_index(name="availability_pct")
      .sort_values("availability_pct")
)

print(bottlenecks.head(10))

Extending the Pattern in reconstruct_db.py

For users building permanent local databases, [reconstruct_db.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py) demonstrates how to ingest raw Parquet shards into a DuckDB file. You can extend this script to create a generated column or materialized view that includes the concatenated identifier:

CREATE TABLE filament_stock AS
SELECT
    product_name || ' - ' || variant_name AS full_name,
    timestamp,
    stock,
    region,
    product_name,
    variant_name
FROM read_parquet('parquet_files/*.parquet');

This approach ensures that every query against your local database automatically includes the unique identifier without repetitive concatenation logic.

Summary

• The BBL Tracker database separates product families (product_name) from specific variants (variant_name), requiring concatenation for unique identification.
• Use the SQL expression product_name || ' - ' || variant_name to create a human-readable full_name column, as shown in script.py lines 42-45.
• Implement this pattern via inline SQL queries, DuckDB Python views, or Pandas DataFrame operations depending on your workflow.
• For persistent databases, modify reconstruct_db.py to materialize the concatenated column during data ingestion.

Frequently Asked Questions

How do I handle NULL values when concatenating product_name and variant_name?

DuckDB follows standard SQL behavior where concatenating a NULL value with a string returns NULL. To prevent losing rows, use the COALESCE function to substitute empty strings: COALESCE(product_name, '') || ' - ' || COALESCE(variant_name, ''). This ensures every record receives a valid identifier even if one field is missing.

Can I use a different separator instead of ' - ' when joining the columns?

Yes, you can substitute any string literal within the concatenation expression. Common alternatives include pipes (' | '), slashes (' / '), or colons (': '). Ensure the separator does not appear within the source data to avoid parsing ambiguity. The script.py example uses ' - ' for readability in bottleneck analysis outputs.

Is the concatenated full_name column suitable for use as a primary key?

While full_name provides human-readable uniqueness for analysis, it is not guaranteed stable for primary key purposes if the underlying product or variant names change. For database design, consider using a surrogate key or combining the original columns in a composite key. For the BBL Tracker dataset, full_name serves as an effective logical identifier for time-series aggregation and reporting.

Where can I find the complete schema definition for the BBL Tracker database?

The complete schema is documented in the Schema section of the repository's [README.md](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/README.md). It lists all columns including product_name (STRING) and variant_name (STRING), along with timestamp, stock, region, and other metadata fields. The README also provides example queries showing how these fields relate to the Parquet files hosted at db-public.bbltracker.com.

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