TPC-H Data Vault Q21 — Suppliers Who Kept Orders Waiting

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

Suppliers who delayed orders they could fill (Data Vault)

Representative SQL

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

SELECT
  ss.s_name,
  COUNT(*) AS numwait
FROM sat_supplier AS ss
JOIN link_supplier_nation AS lsn
  ON ss.hk_supplier = lsn.hk_supplier
JOIN sat_nation AS sn
  ON lsn.hk_nation = sn.hk_nation AND sn.load_end_dts IS NULL
JOIN link_lineitem AS ll1
  ON ss.hk_supplier = ll1.hk_supplier
JOIN sat_lineitem AS sl1
  ON ll1.hk_lineitem_link = sl1.hk_lineitem_link AND sl1.load_end_dts IS NULL
JOIN sat_order AS so
  ON ll1.hk_order = so.hk_order AND so.load_end_dts IS NULL
WHERE
  ss.load_end_dts IS NULL
  AND so.o_orderstatus = 'F'
  AND sl1.l_receiptdate > sl1.l_commitdate
  AND sn.n_name = 'SAUDI ARABIA'
  AND EXISTS(
    SELECT
      1
    FROM link_lineitem AS ll2
    WHERE
      ll2.hk_order = ll1.hk_order AND ll2.hk_supplier <> ll1.hk_supplier
  )
  AND NOT EXISTS(
    SELECT
      1
    FROM link_lineitem AS ll3
    JOIN sat_lineitem AS sl3
      ON ll3.hk_lineitem_link = sl3.hk_lineitem_link AND sl3.load_end_dts IS NULL
    WHERE
      ll3.hk_order = ll1.hk_order
      AND ll3.hk_supplier <> ll1.hk_supplier
      AND sl3.l_receiptdate > sl3.l_commitdate
  )
GROUP BY
  ss.s_name
ORDER BY
  numwait DESC,
  ss.s_name
LIMIT 100

Representative DataFrame

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

def q21_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q21")
    col, lit = ctx.col, ctx.lit
    nation = params.get("nation", "SAUDI ARABIA")

    ss = _strip_audit_columns(ctx.get_table("sat_supplier").filter(col("load_end_dts").is_null()))
    ll = _strip_audit_columns(ctx.get_table("link_lineitem"))
    sl = _strip_audit_columns(ctx.get_table("sat_lineitem").filter(col("load_end_dts").is_null()))
    so = _strip_audit_columns(ctx.get_table("sat_order").filter(col("load_end_dts").is_null()))
    lsn = _strip_audit_columns(ctx.get_table("link_supplier_nation"))
    sn = _strip_audit_columns(ctx.get_table("sat_nation").filter(col("load_end_dts").is_null()))

    li = ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
    late_li = li.filter(col("l_receiptdate") > col("l_commitdate"))

    failed_orders = so.filter(col("o_orderstatus") == lit("F")).select("hk_order")

    supp_nation = (
        ss.join(lsn, left_on="hk_supplier", right_on="hk_supplier")
        .join(sn, left_on="hk_nation", right_on="hk_nation")
        .filter(col("n_name") == lit(nation))
    )

    l1 = late_li.join(failed_orders, left_on="hk_order", right_on="hk_order")

    order_suppliers = li.group_by("hk_order").agg(col("hk_supplier").n_unique().alias("n_suppliers"))
    order_late_suppliers = late_li.group_by("hk_order").agg(col("hk_supplier").n_unique().alias("n_late"))

    df = (
        l1.join(supp_nation, left_on="hk_supplier", right_on="hk_supplier")
        .join(order_suppliers, left_on="hk_order", right_on="hk_order")
        .filter(col("n_suppliers") > lit(1))
        .join(order_late_suppliers, left_on="hk_order", right_on="hk_order")
        .filter(col("n_late") == lit(1))
    )

    result = df.group_by("s_name").agg(col("hk_order").count().alias("numwait"))
    return result.sort([("numwait", "desc"), ("s_name", "asc")]).limit(100)

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[21])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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