TPC-H Data Vault Q3 — Shipping Priority

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

Top 10 unshipped orders with highest value (Data Vault)

Representative SQL

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

SELECT
  hl.l_orderkey,
  SUM(sl.l_extendedprice * (
    1 - sl.l_discount
  )) AS revenue,
  so.o_orderdate,
  so.o_shippriority
FROM hub_customer AS hc
JOIN sat_customer AS sc
  ON hc.hk_customer = sc.hk_customer AND sc.load_end_dts IS NULL
JOIN link_order_customer AS loc
  ON hc.hk_customer = loc.hk_customer
JOIN hub_order AS ho
  ON loc.hk_order = ho.hk_order
JOIN sat_order AS so
  ON ho.hk_order = so.hk_order AND so.load_end_dts IS NULL
JOIN link_lineitem AS ll
  ON ho.hk_order = ll.hk_order
JOIN hub_lineitem AS hl
  ON ll.hk_lineitem = hl.hk_lineitem
JOIN sat_lineitem AS sl
  ON ll.hk_lineitem_link = sl.hk_lineitem_link AND sl.load_end_dts IS NULL
WHERE
  sc.c_mktsegment = 'BUILDING'
  AND so.o_orderdate < CAST('1995-03-15' AS DATE)
  AND sl.l_shipdate > CAST('1995-03-15' AS DATE)
GROUP BY
  hl.l_orderkey,
  so.o_orderdate,
  so.o_shippriority
ORDER BY
  revenue DESC,
  so.o_orderdate
LIMIT 10

Representative DataFrame

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

def q3_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q3")
    col, lit = ctx.col, ctx.lit
    segment = params.get("segment", "BUILDING")
    order_date = params.get("order_date", date(1995, 3, 15))

    hc = _strip_audit_columns(ctx.get_table("hub_customer"))
    sc = _strip_audit_columns(ctx.get_table("sat_customer").filter(col("load_end_dts").is_null()))
    loc = _strip_audit_columns(ctx.get_table("link_order_customer"))
    ho = _strip_audit_columns(ctx.get_table("hub_order"))
    so = _strip_audit_columns(ctx.get_table("sat_order").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()))

    df = (
        hc.join(sc, left_on="hk_customer", right_on="hk_customer")
        .filter(col("c_mktsegment") == lit(segment))
        .join(loc, left_on="hk_customer", right_on="hk_customer")
        .join(ho, left_on="hk_order", right_on="hk_order")
        .join(so, left_on="hk_order", right_on="hk_order")
        .filter(col("o_orderdate") < lit(order_date))
        .join(ll, left_on="hk_order", right_on="hk_order")
        .join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
        .filter(col("l_shipdate") > lit(order_date))
    )

    df = df.with_columns((col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("revenue"))
    result = df.group_by("o_orderkey", "o_orderdate", "o_shippriority").agg(
        col("revenue").sum().alias("revenue"),
    )
    result = result.select("o_orderkey", "revenue", "o_orderdate", "o_shippriority")
    return result.sort([("revenue", "desc"), ("o_orderdate", "asc")]).limit(10)

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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