TPC-H Data Vault Q20 — Potential Part Promotion

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

Suppliers with excess inventory of parts (Data Vault)

Representative SQL

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

SELECT
  ss.s_name,
  ss.s_address
FROM hub_supplier AS hs
JOIN sat_supplier AS ss
  ON hs.hk_supplier = ss.hk_supplier AND ss.load_end_dts IS NULL
JOIN link_supplier_nation AS lsn
  ON hs.hk_supplier = lsn.hk_supplier
JOIN hub_nation AS hn
  ON lsn.hk_nation = hn.hk_nation
JOIN sat_nation AS sn
  ON hn.hk_nation = sn.hk_nation AND sn.load_end_dts IS NULL
WHERE
  sn.n_name = 'CANADA'
  AND hs.hk_supplier IN (
    SELECT
      lps.hk_supplier
    FROM link_part_supplier AS lps
    JOIN sat_partsupp AS sps
      ON lps.hk_part_supplier = sps.hk_part_supplier AND sps.load_end_dts IS NULL
    JOIN hub_part AS hp
      ON lps.hk_part = hp.hk_part
    JOIN sat_part AS sp
      ON hp.hk_part = sp.hk_part AND sp.load_end_dts IS NULL
    WHERE
      sp.p_name LIKE 'forest%'
      AND sps.ps_availqty > (
        SELECT
          0.5 * SUM(sl.l_quantity)
        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_part = hp.hk_part
          AND ll.hk_supplier = lps.hk_supplier
          AND sl.l_shipdate >= CAST('1994-01-01' AS DATE)
          AND sl.l_shipdate < CAST('1994-01-01' AS DATE) + INTERVAL '1' YEAR
      )
  )
ORDER BY
  ss.s_name

Representative DataFrame

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

def q20_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q20")
    col, lit = ctx.col, ctx.lit
    color = params.get("color", "forest")
    start_date = params.get("start_date", date(1994, 1, 1))
    end_date = params.get("end_date", date(1995, 1, 1))
    nation = params.get("nation", "CANADA")

    ss = _strip_audit_columns(ctx.get_table("sat_supplier").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()))
    lps = _strip_audit_columns(ctx.get_table("link_part_supplier"))
    sps = _strip_audit_columns(ctx.get_table("sat_partsupp").filter(col("load_end_dts").is_null()))
    sp = _strip_audit_columns(ctx.get_table("sat_part").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()))

    color_parts = sp.filter(col("p_name").str.starts_with(color))

    li_qty = (
        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)))
        .group_by("hk_part", "hk_supplier")
        .agg(col("l_quantity").sum().alias("total_qty"))
    )

    excess = (
        lps.join(sps, left_on="hk_part_supplier", right_on="hk_part_supplier")
        .join(color_parts, left_on="hk_part", right_on="hk_part")
        .join(li_qty, left_on=["hk_part", "hk_supplier"], right_on=["hk_part", "hk_supplier"])
        .filter(col("ps_availqty") * lit(2) > col("total_qty"))
        .select("hk_supplier")
        .unique()
    )

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

    result = suppliers_in_nation.join(excess, left_on="hk_supplier", right_on="hk_supplier")
    return result.select("s_name", "s_address").sort("s_name")

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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