Data Generation

BenchBox provides systematic data generation capabilities for all supported benchmarks. This guide covers both basic data generation concepts and the advanced smart data generation features that optimize performance and developer productivity.

Overview

Data generation in BenchBox is designed to be:

Scalable: Generate data from small test datasets to production-scale benchmarks
Intelligent: Automatically validate and skip regeneration when valid data exists
Fast: Optimized generation algorithms and caching
Reliable: Comprehensive error handling and validation
Flexible: Support for different output formats and directories

Basic Data Generation

Simple Generation

All benchmarks support a consistent generate_data() interface:

from benchbox import TPCH, TPCDS

tpch = TPCH(scale_factor=0.1)
tables = tpch.generate_data()
print(f"Generated {len(tables)} TPC-H tables")

tpcds = TPCDS(scale_factor=0.1)
tables = tpcds.generate_data()
print(f"Generated {len(tables)} TPC-DS tables")

Scale Factor Configuration

Scale factors control the amount of data generated:

benchmark = TPCH(scale_factor=0.001)

benchmark = TPCH(scale_factor=0.01)

benchmark = TPCH(scale_factor=0.1)

benchmark = TPCH(scale_factor=1.0)

The four scale factors give about 1MB (development and testing), 10MB (CI/CD pipelines, for fast execution), 100MB (performance testing, moderate size) and 1GB (production validation, full scale).

Output Directory Management

Control where data files are generated:

from pathlib import Path

benchmark = TPCH(scale_factor=0.1)

benchmark = TPCH(
    scale_factor=0.1,
    output_dir="./benchmark-data/tpch"
)

import tempfile
temp_dir = tempfile.mkdtemp()
benchmark = TPCH(scale_factor=0.01, output_dir=temp_dir)

The first call uses the default location, the second a custom directory, and the third a temporary directory for testing.

Parallel Generation

Speed up data generation with parallel processing:

benchmark = TPCH(
    scale_factor=1.0,
    parallel=4
)
tables = benchmark.generate_data()

The parallel=4 setting uses 4 parallel processes.

Smart Data Generation

BenchBox includes intelligent data generation that validates existing data and automatically skips regeneration when valid data is present.

How Smart Generation Works

Smart generation follows this process:

Check for existing data files in the output directory
Validate row counts against expected values for the scale factor
Verify file integrity and minimum size requirements
Handle platform-specific differences (e.g., TPC-H minimum scale factors)
Skip generation if valid data exists, or regenerate if needed

Default Behavior (Smart Generation)

Smart validation is enabled by default and works transparently:

from benchbox import TPCH

benchmark = TPCH(scale_factor=1.0, output_dir="./data")
tables = benchmark.generate_data()

benchmark2 = TPCH(scale_factor=1.0, output_dir="./data")
tables2 = benchmark2.generate_data()

The first run finds no data, so it generates it and prints Data validation failed, generating TPC-H data.... The second run finds valid data and skips generation, printing Valid TPC-H data found, skipping generation.

Force Regeneration

When you need to regenerate data regardless of existing files:

benchmark = TPCH(
    scale_factor=1.0,
    output_dir="./data",
    force_regenerate=True
)
tables = benchmark.generate_data()

This regenerates the data even if valid data exists, and prints Force regeneration requested, generating data....

Verbose Validation Reports

Enable detailed validation information:

benchmark = TPCH(
    scale_factor=1.0,
    output_dir="./data",
    verbose=True
)
tables = benchmark.generate_data()

Successful validation output:

✅ Valid TPC-H data found for scale factor 1.0
✅ Data validation PASSED
   Validated 8 tables
   Total data size: 1.2 GB
   Skipping data generation

Failed validation output:

⚠️️  Data validation failed for scale factor 1.0
❌ Data validation FAILED
   Row count mismatches:
     customer: expected 150,000, found 75,000
     lineitem: expected 6,001,215, found 3,000,000
   Issues:
     - Table customer: expected ~150000 rows, found 75000 rows
     - Table lineitem: expected ~6001215 rows, found 3000000 rows
   Generating TPC-H data...

Benchmark-Specific Generation

TPC-H Data Generation

TPC-H uses the official dbgen tool for standards-compliant data:

from benchbox import TPCH

tpch = TPCH(scale_factor=1.0)
tables = tpch.generate_data()

tables_info = {
    'customer': 150_000,
    'lineitem': 6_001_215,
    'nation': 25,
    'orders': 1_500_000,
    'part': 200_000,
    'partsupp': 800_000,
    'region': 5,
    'supplier': 10_000
}

The tables_info mapping shows the expected tables and approximate row counts at SF=1.0. The nation and region tables have a fixed size.

TPC-H specific features:

Minimum scale factor of 0.1 (dbgen limitation)
Fixed-size dimension tables (nation, region)
Standards-compliant data distribution

CoffeeShop Data Generation

The CoffeeShop benchmark has been rewritten to align with the public reference generator. It now produces three tables (dim_locations, dim_products, and order_lines) with an exploded fact table that averages 1.5 lines per order.

from benchbox import CoffeeShop

coffeeshop = CoffeeShop(scale_factor=0.001)
data_files = coffeeshop.generate_data()
print(data_files["order_lines"])

This uses the reference-aligned generator at a small scale for development. data_files["order_lines"] is the path to the generated CSV file.

Scale factors map directly to order counts using the approved formula order_count = 50_000_000 * scale_factor. The table below outlines the recommended operating ranges:

Scale Factor	Orders Generated	Approx. Order Lines	Approx. Size
0.001	50,000	~75,000	~5 MB
0.01	500,000	~750,000	~50 MB
0.1	5,000,000	~7,500,000	~500 MB
1.0	50,000,000	~75,000,000	~5 GB

CoffeeShop specific highlights:

Seasonal (monthly) weighting, regional bias, and linear growth trend.
Deterministic order-line explosion following the 60/30/5/4/1 distribution.
Vendored seed files for locations and products ensure deterministic IDs and pricing windows.
Default output directories align with the BenchBox data layout (benchmark_runs/datagen/coffeeshop_{format_scale_factor(scale)} regardless of whether you use the high-level CoffeeShop wrapper or instantiate the generator directly) and honour optional compression settings.
Query suite updated (SA*, PR*, TR*, TM*, QC*) to operate exclusively on the new schema.
Repeated runs with the same inputs overwrite the existing CSV files deterministically, ensuring identical table contents across regenerations.
Seed CSVs are vendored verbatim from JosueBogran/coffeeshopdatageneratorv2 (MIT licensed, commit 2a99993b6bca94c0bc04fae7c695e86cd152add1) and verified via SHA256 checksums in the unit test suite.

TPC-DS Data Generation

TPC-DS uses the official dsdgen tool for complex analytical data:

from benchbox import TPCDS

tpcds = TPCDS(scale_factor=1.0)
tables = tpcds.generate_data()

print(f"Generated {len(tables)} TPC-DS tables")

TPC-DS generates 24 tables with complex relationships.

TPC-DS specific features:

24 interconnected tables
Complex dimension and fact table relationships
Time-series data with seasonal patterns
Advanced-level query generation integration
Streaming output via -FILTER Y flag (see below)

TPC-DS Streaming Generation

BenchBox includes patched dsdgen binaries that support streaming data to stdout, enabling efficient compression pipelines:

dsdgen -TABLE ship_mode -SCALE 1 -FILTER Y | zstd > ship_mode.dat.zst

dsdgen -TABLE date_dim -SCALE 1 -FILTER Y -RNGSEED 12345 | gzip > date_dim.dat.gz

The first command generates a table directly to a compressed file. The second does the same with a fixed seed (-RNGSEED) for reproducibility.

This streaming capability reduces disk I/O and memory usage for large-scale data generation. See TPC-DS Streaming Data Generation for detailed examples.

Primitives Benchmark - Shared TPC-H Data

The Read Primitives benchmark automatically shares data with TPC-H to avoid duplication:

from benchbox import ReadPrimitives

read_primitives = ReadPrimitives(scale_factor=0.1)
tables = read_primitives.generate_data()

Read Primitives automatically uses TPC-H data.

How Primitives Data Sharing Works:

Automatic Path Selection: Primitives defaults to the same data directory as TPC-H (tpch_sf*), not a separate primitives_sf* directory
Shared Data Files: Primitives uses TPC-H tables (customer, orders, lineitem, etc.) directly
No Duplication: One set of TPC-H data serves both benchmarks, saving disk space and generation time
Manifest Compatibility: Primitives accepts TPC-H manifests without modification

Benefits of Data Sharing:

Disk Space Savings: Eliminates duplicate TPC-H data (saves GBs for larger scale factors)
Faster Setup: If TPC-H data exists, Primitives uses it immediately without regeneration
Consistency: Both benchmarks use identical data, ensuring comparable results

Example - Shared Data Usage:

from benchbox import TPCH, ReadPrimitives

tpch = TPCH(scale_factor=1.0)
tpch_tables = tpch.generate_data()

read_primitives = ReadPrimitives(scale_factor=1.0)
prim_tables = read_primitives.generate_data()

assert tpch_tables['customer'] == prim_tables['customer']

The TPC-H data is generated once, into data/tpch_sf1.0/. Read Primitives automatically uses the same directory, so it needs no regeneration. Both benchmarks share the same table files, so the customer entries are the same file path.

Custom Output Paths:

You can still use custom paths for isolated Primitives data:

read_primitives = ReadPrimitives(
    scale_factor=0.1,
    output_dir="./custom-primitives-data"
)
tables = read_primitives.generate_data()

This gives isolated Primitives data that does not share with TPC-H. The call generates fresh TPC-H data in that directory.

Other Benchmarks

from benchbox import SSB
ssb = SSB(scale_factor=1.0)
tables = ssb.generate_data()

SSB is the Star Schema Benchmark.

Data Validation

Automatic Validation

Smart generation automatically validates:

File existence: All required table files present
Row counts: Match expected values for scale factor
File sizes: Meet minimum size requirements
Data integrity: Files are readable and non-empty

Manual Validation

Access validation directly for custom workflows:

from benchbox.utils.data_validation import BenchmarkDataValidator

validator = BenchmarkDataValidator("tpch", scale_factor=1.0)

result = validator.validate_data_directory("./data")

if result.valid:
    print("✅ Data is valid")
    print(f"Validated {len(result.tables_validated)} tables")
else:
    print(f"❌ Validation failed: {len(result.issues)} issues")
    for issue in result.issues:
        print(f"  - {issue}")

validator.print_validation_report(result, verbose=True)

The example creates a validator for a specific benchmark, validates a data directory, checks the results, and prints a detailed report.

Custom Validation Rules

For advanced validation scenarios:

should_regenerate, result = validator.should_regenerate_data("./data")

if should_regenerate:
    print("Data needs regeneration:")
    for issue in result.issues:
        print(f"  - {issue}")
else:
    print("Existing data is valid")

force_regen = len(result.row_count_mismatches) > 0
benchmark = TPCH(scale_factor=1.0, force_regenerate=force_regen)

The example first checks whether regeneration is needed, then forces regeneration programmatically when row counts do not match.

Performance Optimization

Smart Generation Benefits

Smart data generation avoids regenerating valid data:

Initial run: Full data generation (time depends on scale factor, hardware, and CPU cores)
Subsequent runs: Validation only - checks existing files and skips generation if valid

This provides significant time savings for iterative development workflows, CI/CD pipelines, and repeated benchmark runs regardless of scale factor.

Parallel Processing

Use parallel generation for large scale factors:

import multiprocessing

cores = multiprocessing.cpu_count()
benchmark = TPCH(scale_factor=10.0, parallel=cores)
tables = benchmark.generate_data()

This uses all available CPU cores.

Caching Strategies

Organize data for maximum reuse:

./benchmark-data/
  ├── tpch/
  │   ├── sf-0.01/
  │   ├── sf-0.1/
  │   ├── sf-1.0/
  │   └── sf-10.0/
  └── tpcds/
      ├── sf-0.01/
      └── sf-1.0/

This organizes data by benchmark and scale factor. Small datasets (sf-0.01) suit quick testing, medium datasets (sf-0.1) suit CI/CD, large datasets (sf-1.0) suit performance testing, and sf-10.0 is production scale.

Configuration Options

Constructor Parameters

All benchmarks support these common parameters:

benchmark = TPCH(
    scale_factor=1.0,
    output_dir="./data",
    verbose=True,
    force_regenerate=False,
    parallel=1
)

The parameters are the data scale factor, the output directory, verbose output, force_regenerate (the default False leaves smart validation on) and the number of parallel generation processes.

Environment Variables

Control behavior globally via environment variables:

export BENCHBOX_FORCE_REGENERATE=true

export BENCHBOX_VERBOSE=true

export BENCHBOX_DATA_DIR=./benchmark_runs/datagen

The variables force regeneration for all benchmarks, enable verbose output, and set the default output directory.

Platform-Specific Settings

Some platforms have special considerations:

benchmark = TPCH(scale_factor=100.0, platform="clickhouse-local")

benchmark = TPCH(scale_factor=10.0, platform="duckdb")

ClickHouse handles large scale factors efficiently. DuckDB is configured for analytical workloads.

Advanced-level Usage

Data Format Conversion

Generate data in different formats:

tpch = TPCH(scale_factor=0.1)
tables = tpch.generate_data()

for table_name, file_path in tables.items():
    parquet_path = file_path.with_suffix('.parquet')

The generate_data() call produces standard delimited files. The loop is a skeleton for converting them to Parquet, which requires pyarrow; add your own conversion code inside the loop.

Custom Data Directories

Implement custom directory structures:

from pathlib import Path
from datetime import datetime

timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
data_dir = Path(f"./runs/{timestamp}/tpch_sf1")

benchmark = TPCH(scale_factor=1.0, output_dir=data_dir)
tables = benchmark.generate_data()

The directory name is timestamp-based, so each run gets its own data directory.

Integration with CI/CD

Cache data between runs:

- name: Cache benchmark data
  uses: actions/cache@v3
  with:
    path: ./benchmark-data
    key: benchbox-data-${{ runner.os }}-${{ hashFiles('**/benchmark-config.yaml') }}

- name: Generate benchmark data
  run: |
    python -c "
    from benchbox import TPCH
    tpch = TPCH(scale_factor=0.01, output_dir='./benchmark-data/tpch')
    tpch.generate_data()
    "

This is a GitHub Actions example.

Error Handling

Common Issues and Solutions

Scale Factor Mismatches

Problem: Existing data was generated with different scale factor

benchmark = TPCH(scale_factor=0.5)

benchmark = TPCH(scale_factor=1.0, force_regenerate=True)

Solution 1 is to use a matching scale factor (here 0.5, to match the existing data). Solution 2 is to force regeneration with the new scale factor.

Missing or Corrupted Files

Problem: Some data files are missing or corrupted

benchmark = TPCH(scale_factor=1.0, verbose=True)
tables = benchmark.generate_data()

Smart generation automatically detects the problem and regenerates the data. It prints Missing files detected, regenerating all data.

Permission Errors

Problem: Cannot read/write data files

chmod 755 ./data
chown $USER:$USER ./data

These commands fix the directory permissions.

Disk Space Issues

Problem: Not enough space for large scale factors

def estimate_data_size(benchmark, scale_factor):
    base_size_mb = {
        'tpch': 1000,
        'tpcds': 2000,
    }
    return base_size_mb.get(benchmark, 1000) * scale_factor

required_mb = estimate_data_size('tpch', 10.0)
print(f"Required space: ~{required_mb}MB")

This checks the required space before generation. The base sizes are about 1GB for TPC-H and 2GB for TPC-DS at SF=1.0.

Debug Mode

Enable detailed debugging:

import logging
logging.basicConfig(level=logging.DEBUG)

benchmark = TPCH(scale_factor=1.0, verbose=True)
tables = benchmark.generate_data()

This shows detailed generation and validation steps.

Best Practices

Development Workflow

Start Small: Use scale factors 0.001-0.01 during development
Cache Aggressively: Reuse generated data when possible
Validate Early: Test with small datasets before scaling up
Monitor Resources: Track memory and disk usage

def setup_benchmark(scale_factor=0.01):
    data_dir = Path("./dev-cache") / f"tpch-sf{scale_factor}"
    return TPCH(scale_factor=scale_factor, output_dir=data_dir, verbose=True)

tpch = setup_benchmark()
tables = tpch.generate_data()

This is the development pattern: set up the benchmark with smart caching, and the call uses the cache if it is available.

Production Deployment

Pre-generate Data: Generate data during deployment, not runtime
Validate Integrity: Always validate data before benchmarks
Monitor Performance: Track generation time and data quality
Plan Storage: Estimate storage requirements for scale factors

def setup_production_benchmark(scale_factor=1.0):
    data_dir = Path("/opt/benchmark-data") / f"tpch-sf{scale_factor}"

    benchmark = TPCH(
        scale_factor=scale_factor,
        output_dir=data_dir,
        verbose=True,
        parallel=multiprocessing.cpu_count()
    )

    tables = benchmark.generate_data()

    validator = BenchmarkDataValidator("tpch", scale_factor)
    result = validator.validate_data_directory(data_dir)

    if not result.valid:
        raise RuntimeError(f"Data validation failed: {result.issues}")

    return benchmark, tables

This is the production pattern: it generates or pre-validates the data, then runs an additional validation and raises an error if the data is not valid.

Testing Strategy

Unit Tests: Test generation with small scale factors
Integration Tests: Validate full benchmark workflows
Performance Tests: Measure generation performance
Regression Tests: Detect data quality changes

import pytest
import tempfile

def test_data_generation():
    with tempfile.TemporaryDirectory() as temp_dir:
        benchmark = TPCH(scale_factor=0.001, output_dir=temp_dir)
        tables = benchmark.generate_data()

        assert len(tables) == 8
        for table_path in tables.values():
            assert table_path.exists()
            assert table_path.stat().st_size > 0

def test_smart_generation():
    with tempfile.TemporaryDirectory() as temp_dir:
        benchmark1 = TPCH(scale_factor=0.001, output_dir=temp_dir)
        tables1 = benchmark1.generate_data()

        benchmark2 = TPCH(scale_factor=0.001, output_dir=temp_dir, verbose=True)
        tables2 = benchmark2.generate_data()

        assert tables1.keys() == tables2.keys()

The first test checks basic data generation and validates the results. The second tests the smart generation skip logic: the first generation creates the data, the second should skip generation and return the same tables.

Supported Benchmarks

Data generation support by benchmark:

Benchmark	Smart Generation	Row Validation	Parallel Support	Notes
TPC-H	✅ Full	✅ Yes	✅ Yes	Official dbgen tool
TPC-DS	✅ Full	✅ Yes	✅ Yes	Official dsdgen tool
Read Primitives	✅ Full	✅ Yes	✅ Yes	Shares TPC-H data
SSB	⚠️️ Basic	❌ No	✅ Yes	Will be improved
ClickBench	⚠️️ Basic	❌ No	❌ No	External data
Join Order	✅ Downloaded	✅ Hashes/rows	❌ No	Canonical IMDb JOB data, `SF=1` only
Others	⚠️️ Basic	❌ No	❌ No	Being improved

Future Enhancements

Planned data generation improvements:

Checksum Validation: Verify data integrity using file checksums
Schema Validation: Check table structure and column types
Compression Support: Handle compressed data files (.gz, .bz2)
Remote Data: Support cloud-stored benchmark datasets
Incremental Updates: Update only changed tables
Cross-Platform Sync: Share data between database platforms
Format Conversion: Auto-convert between CSV, Parquet, etc.
Resource Estimation: Predict generation time and space requirements

Troubleshooting

Performance Issues

import time

start_time = time.time()
benchmark = TPCH(scale_factor=1.0, verbose=True, parallel=4)
tables = benchmark.generate_data()
generation_time = time.time() - start_time

print(f"Generated {len(tables)} tables in {generation_time:.2f} seconds")
print(f"Average: {generation_time/len(tables):.2f}s per table")

This monitors generation performance.

Memory Issues

import psutil

def monitor_generation():
    process = psutil.Process()

    print(f"Memory before: {process.memory_info().rss / 1024**2:.1f} MB")

    benchmark = TPCH(scale_factor=10.0, parallel=2)
    tables = benchmark.generate_data()

    print(f"Memory after: {process.memory_info().rss / 1024**2:.1f} MB")
    return tables

For large scale factors, monitor memory usage.

Validation Issues

from benchbox.utils.data_validation import BenchmarkDataValidator

validator = BenchmarkDataValidator("tpch", scale_factor=1.0)
result = validator.validate_data_directory("./data")

if not result.valid:
    print("Validation Issues:")
    print(f"Missing tables: {result.missing_tables}")
    print(f"Row count mismatches: {result.row_count_mismatches}")
    print(f"File size issues: {[f for f, size in result.file_size_info.items() if size == 0]}")

This debugs validation problems with a detailed analysis.

Data generation in BenchBox is designed to be powerful, intelligent, and developer-friendly. The smart generation features ensure appropriate performance while maintaining data integrity and standards compliance.

FilesExpand file tree

data-generation.md

Latest commit

History