New In DuckDB 2.0, the dsdgen data generator was updated to match the TPC-DS version 4 tools. As a result, the generated data differs slightly from the data generated by DuckDB v1.x. See the related PR for more details.

The tpcds extension implements the data generator and queries for the TPC-DS benchmark.

Installing and Loading

The tpcds extension will be transparently autoloaded on first use from the official extension repository. If you would like to install and load it manually, run:

INSTALL AND LOAD tpcds;

Usage

To generate data for scale factor 1, use:

CALL dsdgen(sf = 1);

To run a query, e.g., query 8, use:

PRAGMA tpcds(8);

Listing Queries

To list all 99 queries, run:

FROM tpcds_queries();

This function returns a table with columns query_nr and query.

Listing Expected Answers

To produce the expected results for all queries on scale factors 1 and 10, run the following (note that in DuckDB 2.0.0 this fails with a Not implemented Error for scale factor 10):

FROM tpcds_answers();

This function returns a table with columns query_nr, scale_factor and answer.

Generating the Schema

It's possible to generate the schema of TPC-DS without any data by setting the scale factor to 0:

CALL dsdgen(sf = 0);

Data Generator Parameters

The data generator function dsdgen has the following parameters:

Pre-Generated Datasets

Pre-generated DuckDB databases for TPC-DS are available for download. Note that these were generated with DuckDB v1.x. DuckDB v2.x generates slightly different TPC-DS files, compatible with TPC-DS v4.

• tpcds-sf10.db (2.9 GB)
• tpcds-sf30.db (7.7 GB)
• tpcds-sf100.db (26.6 GB)
• tpcds-sf300.db (79.3 GB)

Limitations

The tpcds(⟨query_id⟩) function runs a fixed TPC-DS query with pre-defined bind parameters (a.k.a. substitution parameters). It is not possible to change the query parameters using the tpcds extension.