TPC-H Data Vault Q4 — Order Priority Checking

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

Orders by priority with late lineitems (Data Vault)

Representative SQL

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

SELECT
  so.o_orderpriority,
  COUNT(*) AS order_count
FROM hub_order AS ho
JOIN sat_order AS so
  ON ho.hk_order = so.hk_order AND so.load_end_dts IS NULL
WHERE
  so.o_orderdate >= CAST('1993-07-01' AS DATE)
  AND so.o_orderdate < CAST('1993-07-01' AS DATE) + INTERVAL '3' MONTH
  AND EXISTS(
    SELECT
      1
    FROM link_lineitem AS ll
    JOIN sat_lineitem AS sl
      ON ll.hk_lineitem_link = sl.hk_lineitem_link AND sl.load_end_dts IS NULL
    WHERE
      ll.hk_order = ho.hk_order AND sl.l_commitdate < sl.l_receiptdate
  )
GROUP BY
  so.o_orderpriority
ORDER BY
  so.o_orderpriority

Representative DataFrame

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

def q4_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q4")
    col, lit = ctx.col, ctx.lit
    start_date = params.get("start_date", date(1993, 7, 1))
    end_date = params.get("end_date", date(1993, 10, 1))

    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()))

    late_orders = (
        ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
        .filter(col("l_commitdate") < col("l_receiptdate"))
        .select("hk_order")
        .unique()
    )

    orders = ho.join(so, left_on="hk_order", right_on="hk_order").filter(
        (col("o_orderdate") >= lit(start_date)) & (col("o_orderdate") < lit(end_date))
    )

    result = (
        orders.join(late_orders, left_on="hk_order", right_on="hk_order")
        .group_by("o_orderpriority")
        .agg(col("hk_order").count().alias("order_count"))
    )
    return result.sort("o_orderpriority")

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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