TPC-H Data Vault Q16 — Parts/Supplier Relationship

TPC-H Data Vault benchmark · all TPC-H Data Vault queries

Count suppliers per part, excluding complaints (Data Vault)

Representative SQL

Rendered in the benchmark’s native SQL with default parameters (no dialect translation available).

SELECT
  sp.p_brand,
  sp.p_type,
  sp.p_size,
  COUNT(DISTINCT lps.hk_supplier) AS supplier_cnt
FROM hub_part AS hp
JOIN sat_part AS sp
  ON hp.hk_part = sp.hk_part AND sp.load_end_dts IS NULL
JOIN link_part_supplier AS lps
  ON hp.hk_part = lps.hk_part
JOIN hub_supplier AS hs
  ON lps.hk_supplier = hs.hk_supplier
JOIN sat_supplier AS ss
  ON hs.hk_supplier = ss.hk_supplier AND ss.load_end_dts IS NULL
WHERE
  sp.p_brand <> 'Brand#45'
  AND sp.p_type NOT LIKE 'MEDIUM POLISHED%'
  AND sp.p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
  AND ss.s_comment NOT LIKE '%Customer%Complaints%'
GROUP BY
  sp.p_brand,
  sp.p_type,
  sp.p_size
ORDER BY
  supplier_cnt DESC,
  sp.p_brand,
  sp.p_type,
  sp.p_size

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def q16_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q16")
    col, lit = ctx.col, ctx.lit
    brand = params.get("brand", "Brand#45")
    type_prefix = params.get("type_prefix", "MEDIUM POLISHED")
    sizes = params.get("sizes", [49, 14, 23, 45, 19, 3, 36, 9])

    sp = _strip_audit_columns(ctx.get_table("sat_part").filter(col("load_end_dts").is_null()))
    lps = _strip_audit_columns(ctx.get_table("link_part_supplier"))
    ss = _strip_audit_columns(ctx.get_table("sat_supplier").filter(col("load_end_dts").is_null()))

    bad_suppliers = ss.filter(col("s_comment").str.contains("Customer.*Complaints"))

    parts = sp.filter(
        (col("p_brand") != lit(brand)) & ~col("p_type").str.starts_with(type_prefix) & col("p_size").is_in(sizes)
    )

    df = parts.join(lps, left_on="hk_part", right_on="hk_part")

    df = df.join(bad_suppliers.select("hk_supplier"), left_on="hk_supplier", right_on="hk_supplier", how="anti")

    result = df.group_by("p_brand", "p_type", "p_size").agg(
        col("hk_supplier").n_unique().alias("supplier_cnt"),
    )
    return result.sort([("supplier_cnt", "desc"), ("p_brand", "asc"), ("p_type", "asc"), ("p_size", "asc")])

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import DataVault

bench = DataVault(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries[16])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('datavault', '16', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/datavault/queries.py