TPC-H Data Vault Q6 — Forecasting Revenue Change

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

Revenue increase from eliminating discounts (Data Vault)

Representative SQL

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

SELECT
  SUM(sl.l_extendedprice * sl.l_discount) AS revenue
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
  sl.l_shipdate >= CAST('1994-01-01' AS DATE)
  AND sl.l_shipdate < CAST('1994-01-01' AS DATE) + INTERVAL '1' YEAR
  AND sl.l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01
  AND sl.l_quantity < 24

Representative DataFrame

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

def q6_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q6")
    col, lit = ctx.col, ctx.lit
    start_date = params.get("start_date", date(1994, 1, 1))
    end_date = params.get("end_date", date(1995, 1, 1))
    discount_low = params.get("discount_low", 0.05)
    discount_high = params.get("discount_high", 0.07)
    quantity = params.get("quantity", 24)

    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 = ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link").filter(
        (col("l_shipdate") >= lit(start_date))
        & (col("l_shipdate") < lit(end_date))
        & (col("l_discount") >= lit(discount_low))
        & (col("l_discount") <= lit(discount_high))
        & (col("l_quantity") < lit(quantity))
    )
    return (
        df.with_columns((col("l_extendedprice") * col("l_discount")).alias("charge"))
        .agg(
            col("charge").sum().alias("revenue"),
            col("charge").count().alias("n"),
        )
        .select(ctx.when(col("n") > lit(0)).then(col("revenue")).otherwise(lit(None)).alias("revenue"))
    )

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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