TPC-DS-OBT Benchmark

CLI name: tpcds_obt - use benchbox run --benchmark tpcds_obt

Query templates: every TPC-DS-OBT query

Overview

The TPC-DS-OBT (One Big Table) benchmark adapts the standard TPC-DS benchmark to run against a single denormalized table instead of the traditional 25-table normalized schema. This experimental benchmark tests how databases handle wide tables with hundreds of columns, a pattern increasingly common in modern data warehouses and lakehouse architectures.

The benchmark is ideal for evaluating column pruning efficiency, wide table scan performance, and storage format effectiveness (Parquet, Delta Lake, Iceberg) on denormalized schemas.

Key Features

• Single wide table - All TPC-DS data flattened into one denormalized table
• 89 of 99 queries - TPC-DS queries rewritten for flat schema (10 queries are not OBT-convertible; see below)
• No joins required - Tests pure scan and aggregation performance
• Column pruning focus - Evaluates optimizer column projection efficiency
• Modern lakehouse pattern - Simulates real-world denormalized data models
• Storage format comparison - Ideal for Parquet vs Delta vs Iceberg testing

Use Cases

When to Use TPC-DS-OBT

• Lakehouse performance testing - Evaluate denormalized table performance
• Column pruning benchmarks - Test how efficiently engines skip unused columns
• Wide table handling - Stress test databases with 200+ column tables
• Storage format comparison - Compare Parquet, Delta Lake, Iceberg on wide tables
• Scan-heavy workloads - Benchmark pure analytical scan performance without join overhead

When to Use Standard TPC-DS

• Join performance testing - Evaluating multi-table join strategies
• Normalized schema workloads - Traditional data warehouse patterns
• TPC compliance - Official TPC-DS compliance requires normalized schema

Data Model

One Big Table Schema

The OBT schema denormalizes all 25 TPC-DS tables into a single wide table:

Aspect	Value
Tables	1 (denormalized)
Columns	~200+
Source Tables	All 25 TPC-DS tables flattened
Primary Grain	store_sales fact table

Column Groups

The denormalized table contains columns from all TPC-DS dimensions:

Source Table	Columns Added	Prefix
store_sales	~23	ss_
customer	~18	c_
customer_address	~13	ca_
customer_demographics	~9	cd_
date_dim	~28	d_
item	~22	i_
store	~29	s_
promotion	~19	p_
household_demographics	~5	hd_
time_dim	~10	t_
…	…	…

Scale Factors

Scale Factor	Approximate Rows	Approximate Size
1	~2.8 million	~2 GB
10	~28 million	~20 GB
100	~280 million	~200 GB
1000	~2.8 billion	~2 TB

Quick Start

benchbox run --platform duckdb --benchmark tpcds_obt --scale 1.0

benchbox run --platform duckdb --benchmark tpcds_obt --scale 1.0 --queries Q1,Q3,Q7

benchbox run --platform duckdb --benchmark tpcds --scale 1.0
benchbox run --platform duckdb --benchmark tpcds_obt --scale 1.0

The first command runs TPC-DS-OBT on DuckDB and the second runs only queries Q1, Q3 and Q7. The last two commands run standard TPC-DS and then TPC-DS-OBT at the same scale for comparison.

CLI Options (--benchmark-option)

Configure TPC-DS-OBT via --benchmark-option KEY=VALUE:

Option	Default	Description
dimension_mode	full	OBT dimension mode: full, minimal
channels	all	Sales channels: store, web, catalog (comma-separated)
output_format	parquet	Output format: dat, parquet
tpcds_source_dir	-	Directory containing TPC-DS source data
force_regenerate	-	Force data regeneration (true/false)

Accepts hyphenated aliases (e.g. dimension-mode, output-format, tpcds-source-dir).

benchbox run --platform duckdb --benchmark tpcds_obt --scale 1 \
  --benchmark-option dimension_mode=minimal \
  --benchmark-option channels=store

benchbox run --platform duckdb --benchmark tpcds_obt --scale 1 \
  --benchmark-option output_format=dat

The first command uses minimal dimensions and the store channel only. Parquet is the default output format, so it needs no flag. The second forces the legacy pipe-delimited .dat output, which is rarely needed.

Storage format

Parquet is the default because the OBT is a very wide table (518 columns at dimension_mode=full) with high null density - store rows carry NULL for all web_site_*, web_page_*, and call_center_* columns, and so on per channel. Columnar storage with null suppression reduces the SF1 artifact by an order of magnitude vs the 11 GB pipe-delimited .dat, and makes the DataFrame platforms (DataFusion, Polars, Pandas) viable for SF1+.

Use --benchmark-option output_format=dat when you need to reproduce results from runs that pre-date this default change, or when feeding a tool that can only read pipe-delimited text. Existing .dat files on disk are not deleted automatically; BenchBox will regenerate as parquet and log the path of the stale .dat so you can reclaim disk space manually.

Query Adaptations

TPC-DS-OBT provides 89 of the standard 99 TPC-DS queries rewritten to work with the flat schema. 10 queries are excluded because they cannot be expressed against a single denormalized fact:

Category	Queries	Reason for exclusion
Inventory fact	Q21, Q22, Q37, Q39, Q72, Q82	Use the inventory fact table, a separate fact domain not included in the OBT denormalization
External customer dimensions	Q46, Q64, Q68, Q84	Require the customer’s current address/demographics (independent of the sales fact), which the OBT cannot recover from the single-row grain. Q64 additionally uses a cross-channel self-join that defeats OBT’s single-scan design

Queries 14 and 49 are included but hand-written rather than produced by the automatic converter because their semantics require manual rewrites.

Example: Query 1

Standard TPC-DS Q1 (with joins):

SELECT c_customer_id, c_first_name, c_last_name, ...
FROM customer, store_sales, date_dim, store
WHERE c_customer_sk = ss_customer_sk
  AND ss_sold_date_sk = d_date_sk
  AND ss_store_sk = s_store_sk
  ...

TPC-DS-OBT Q1 (flat table):

SELECT c_customer_id, c_first_name, c_last_name, ...
FROM tpcds_obt
WHERE d_year = 2000
  AND s_state = 'TN'
  ...

Performance Considerations

Advantages of OBT

• No join overhead - Eliminates multi-table join costs
• Simplified query plans - Single table scan with filters
• Columnar format efficiency - Modern formats excel at column pruning
• Predictable performance - Less optimizer variability

Challenges of OBT

• Storage overhead - Denormalization increases data redundancy
• Column count - Wide tables stress metadata handling
• Update complexity - Changes require full table rewrites
• Memory pressure - Wide rows can stress memory buffers

Platform Support

Platform	Status	Notes
DuckDB	✅ Full	Excellent wide table handling
ClickHouse	✅ Full	Strong columnar performance
Databricks	✅ Full	Native Delta Lake support
Snowflake	✅ Full	Automatic micro-partitioning
BigQuery	✅ Full	Columnar storage optimized
Polars	✅ Full	Efficient Arrow-based scans
PostgreSQL	⚠️ Limited	Row-store less efficient for wide tables

Comparison with Related Benchmarks

Benchmark	Schema	Tables	Focus
TPC-DS	Normalized	25	Join performance, complex queries
TPC-DS-OBT	Denormalized	1	Wide table scans, column pruning
TPC-H	Normalized	8	Simpler decision support
ClickBench	Single table	1	Real-world analytics patterns

See Also

• TPC-DS Benchmark - Standard normalized TPC-DS
• ClickBench - Another single-table analytics benchmark
• Data Vault - Alternative schema modeling approach
• Platform Comparison - Platform capabilities