TPC Benchmark™ DS - Standard Specification, Version 3.2.0 Page 1 of 141
TPC BENCHMARK ™ DS
Standard Specification
Version 3.2.0
June, 2021
Transaction Processing Performance Council (TPC)
www.tpc.org
info@tpc.org
© 2021 Transaction Processing Performance Council
All Rights Reserved
TPC Benchmark™ DS - Standard Specification, Version 3.2.0 Page 2 of 141
TPC Benchmark™ DS - Standard Specification, Version 3.2.0 Page 3 of 141
Legal Notice
The TPC reserves all right, title, and interest to this document and associated source code as provided under U.S.
and international laws, including without limitation all patent and trademark rights therein.
Permission to copy without fee all or part of this document is granted provided that the TPC copyright notice, the
title of the publication, and its date appear, and notice is given that copying is by permission of the Transaction
Processing Performance Council. To copy otherwise requires specific permission.
No Warranty
TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, THE INFORMATION CONTAINED
HEREIN IS PROVIDED “AS IS” AND WITH ALL FAULTS, AND THE AUTHORS AND DEVELOPERS
OF THE WORK HEREBY DISCLAIM ALL OTHER WARRANTIES AND CONDITIONS, EITHER
EXPRESS, IMPLIED OR STATUTORY, INCLUDING, BUT NOT LIMITED TO, ANY (IF ANY) IMPLIED
WARRANTIES, DUTIES OR CONDITIONS OF MERCHANTABILITY, OF FITNESS FOR A
PARTICULAR PURPOSE, OF ACCURACY OR COMPLETENESS OF RESPONSES, OF RESULTS, OF
WORKMANLIKE EFFORT, OF LACK OF VIRUSES, AND OF LACK OF NEGLIGENCE. ALSO, THERE
IS NO WARRANTY OR CONDITION OF TITLE, QUIET ENJOYMENT, QUIET POSSESSION,
CORRESPONDENCE TO DESCRIPTION OR NON-INFRINGEMENT WITH REGARD TO THE WORK.
IN NO EVENT WILL ANY AUTHOR OR DEVELOPER OF THE WORK BE LIABLE TO ANY OTHER
PARTY FOR ANY DAMAGES, INCLUDING BUT NOT LIMITED TO THE COST OF PROCURING
SUBSTITUTE GOODS OR SERVICES, LOST PROFITS, LOSS OF USE, LOSS OF DATA, OR ANY
INCIDENTAL, CONSEQUENTIAL, DIRECT, INDIRECT, OR SPECIAL DAMAGES WHETHER UNDER
CONTRACT, TORT, WARRANTY, OR OTHERWISE, ARISING IN ANY WAY OUT OF THIS OR ANY
OTHER AGREEMENT RELATING TO THE WORK, WHETHER OR NOT SUCH AUTHOR OR
DEVELOPER HAD ADVANCE NOTICE OF THE POSSIBILITY OF SUCH DAMAGES.
Trademarks
TPC Benchmark, TPC-DS and QphDS are trademarks of the Transaction Processing Performance Council.
TPC Benchmark™ DS - Standard Specification, Version 3.2.0 Page 4 of 141
Acknowledgments
Developing a TPC benchmark for a new environment requires a huge effort to conceptualize research, specify,
review, prototype, and verify the benchmark. The TPC acknowledges the work and contributions of the TPC-DS
subcommittee member companies in developing the TPC-DS specification.
The TPC-DS subcommittee would like to acknowledge the contributions made by the many members during the
development of the benchmark specification. It has taken the dedicated efforts of people across many companies,
often in addition to their regular duties. The list of significant contributors to this version includes Susanne
Englert, Mary Meredith, Sreenivas Gukal, Doug Johnson 1+2, Lubor Kollar, Murali Krishna, Bob Lane, Larry
Lutz, Juergen Mueller, Bob Murphy, Doug Nelson, Ernie Ostic, Raghunath Othayoth Nambiar, Meikel Poess,
Haider Rizvi, Bryan Smith, Eric Speed, Cadambi Sriram, Jack Stephens, John Susag, Tricia Thomas, Dave
Walrath, Shirley Wang, Guogen Zhang, Torsten Grabs, Charles Levine, Mike Nikolaiev, Alain Crolotte,
Francois Raab, Yeye He, Margaret McCarthy, Indira Patel, Daniel Pol, John Galloway, Jerry Lohr, Jerry
Buggert, Michael Brey, Nicholas Wakou, Vince Carbone, Wayne Smith, Dave Steinhoff, Dave Rorke, Dileep
Kumar, Yanpei Chen, John Poelman, and Seetha Lakshmi.
Document Revision History
Date
Version
Description
08-28-2015
2.0.0
Mail ballot version
11-12-2015
2.1.0
• The reference to "c_dep_count" in the SELECT clause (4th column selected, right under
"cd_marital_status") has a typo, should be "cd_dep_count" (FogBugz 937)
• added Dave Rorke, Dileep Kumar, Yanpei Chen, John Poelman, and Seetha Lakshmi to
Acknowledgment section and added following bullet to bullet list in Clause 0.1: Run on “Big Data”
solutions, such as RDBMS as well as Hadoop/Spark based systems (FogBugz 991)
• increased limit for db_version column to 100 (dbgen_version.h), modified release information to
2.0.0 and added warnings for 100 and 300 scale factors and changed warning from "Warning:
Selected volume is NOT valid for result publication" to "Warning: Selected scale factor is NOT
valid for result publication" (FogBugz 1002)
• Fixed query variant templates error in templates 18a, 22a and 27a (FogBugz 1033)
• Added companion document Version2CompanionDocument_final.docx (FogBugz 1053)
• Fix broken link in 5.1.2 to refer to 7.3.8.5 (FogBugz 1060) (FogBugz 1060)
• Change references in 5.3.4 and 5.3.5 to refer to Table 5-5 (FogBugz 1060)
• Delete Clause 7.2.5 (FogBugz 1060)
• Fix reference in 7.6.2 to refer to clauses 7.4.7.3 and 7.4.7.6 (FogBugz 1060)
• Change reference in 10.3.2.4 to refer to 2.5.3 (FogBugz 1060)
• Delete "isolation requirements" in 0.2 bullet d. (FogBugz 1060)
• Delete 10.3.4.6 (FogBugz 1060)
• Change 10.3.6 to read "10.3.6 Clause 7- Performance Metrics and Execution Rules Related Items"
(FogBugz 1060) (FogBugz 1060)
• refer to 7.6.3 in comment of 10.6 (FogBugz 1060)
• 11.2.4.1. Delete broken link wording. (FogBugz 1060)
• Delete from Query Template 2 (FogBugz 1121)
• define COUNTY=random(1, rowcount("active_counties", "store"), uniform); (FogBugz 1121)
• define GMT=distmember(fips_county,[COUNTY], 6) (FogBugz 1121)
• Delete from Query Template 17 (FogBugz 1121)
• define QRT = random(1,4,uniform); (FogBugz 1121)
• Delete from Query Template 22 (FogBugz 1121)
• define YEAR=random(1998,2002,uniform); (FogBugz 1121)
• No changes necessary for Query Template 38 (FogBugz 1121)
• Delete from Query Template 39 (FogBugz 1121)
• define CATEGORY = text({"Books",1},{"Home",1},{"Electronics",1},{"Jewelry",1},{"Sports",1});
(FogBugz 1121)
• define STATENUMBER=ulist(random(1, rowcount("active_states", "warehouse"), uniform),3);
(FogBugz 1121)
• define STATEA=distmember(fips_county,[STATENUMBER.1], 3); (FogBugz 1121)
TPC Benchmark™ DS - Standard Specification, Version 3.2.0 Page 5 of 141
• define STATEB=distmember(fips_county,[STATENUMBER.2], 3); (FogBugz 1121)
• define STATEC=distmember(fips_county,[STATENUMBER.3], 3); (FogBugz 1121)
• Delete from template 51 (FogBugz 1121)
• define YEAR = random(1998, 2002, uniform); (FogBugz 1121)
• Delete from template 59 (FogBugz 1121)
• define YEAR=random(1998,2001,uniform); (FogBugz 1121)
• Delete from template 62 (FogBugz 1121)
• define YEAR = random(1998,2002,uniform); (FogBugz 1121)
• Delete from template 63 (FogBugz 1121)
• define year = random(1998,2002,uniform); (FogBugz 1121)
• Delete from template 67 (FogBugz 1121)
• define YEAR = random(1998, 2002, uniform); (FogBugz 1121)
• Delete from template 71 (FogBugz 1121)
• define MANAGER=random(1,100,uniform); (FogBugz 1121)
• Delete from template 87 (FogBugz 1121)
• define YEAR= random(1998, 2002, uniform); (FogBugz 1121)
• Delete from template 97 (FogBugz 1121)
• define YEAR=random(1998,2002, uniform); (FogBugz 1121)
• Delete from template 99 (FogBugz 1121)
• define YEAR=random(1998,2002,uniform); (FogBugz 1121)
• Revised How To Document for TPC-DS tools (FogBugz 1128)
• Modified query variant 22a.tpl (FogBugz 1135)
• Modified query variant 70a.tpl (1136)
06-09-2016
2.2.0
• Query 14_NULLS_FIRST.ans (FogBugz 1571)
• Modified query template 84 (FogBugz 1559)
• Modified answer set for Query 11 (FogBugz 1539)
• Add Clause 3.4.5 The output of dsdgen is text. The content of each field is terminated by '|'. A '|' in
the first position of a row indicates that the first column of the row is empty. Two consecutive '|'
indicate that the given column value is empty. Empty column values are only generated for columns
that are NULL-able as specified in the logical database design. Empty column values, as generated
by dsdgen, must be treated as NULL values in the data processing system, i.e. the data processing
system must be able to retrieve NULL-able columns using 'is null' predicates (FogBugz 1538)
• Added bullet in 3.4.5 i) NULLs must always be printed by the same string pattern of zero or more
characters (FogBugz 1538)
• Added the following comment to Clause 7.5.2 Comment: Since the reference answer set provided
in the specification originated from different data processing systems, the reference answer set does
not consistently express NULL values with the same string pattern. (FogBugz 1538)
• Modified answer set for Query 27 (FogBugz 1537)
• changed "• BPTWO.01 = unknown to • BPTWO.01 = Unknown" in the specification and "define
BPTWO= text({"0-500",1},{"unknown",1},{"5001-10000",1}); to define BPTWO= text({"0-
500",1},{"Unknown",1},{"5001-10000",1});" in query template 34 (FogBugz 1531)
• Modified answer sets q34.ans_nulls_first and q34.ans_nulls_last (FogBugz 1531 and 1470)
• Modified changes to queries 18 and 49 as suggested(FogBugz 1502)
• Made changes to B.4, B.29, B.48, B.73, B.74, B.92, B93 and B.97 (FogBugz 1501)
• Made changes to Clause 4.2.3.4 (FogBugz 1480)
• Changed qualification parameter substitution in Query Template 4 as follows: YEAR.01=2001 and
SELECTCONE.01= t_s_secyear.customer_preferred_cust_flag (FogBugz 1479)
• Modified answers sets for q98_nulls_first and q98_nulls_last (FogBugz 1474)
• Change substitution parameter for Query 73 in specification as follows:
• COUNTY_D.01 = Orange County
• COUNTY_C.01 = Bronx County
• COUNTY_B.01 = Franklin Parish
• COUNTY_A.01 = Williamson County
• YEAR.01 = 1999
• BPTWO.01 = Unknown
• BPONE.01 = >10000
• And changed template parameter definitions as follows:
• define BPONE= text({"1001-5000",1},{">10000",1},{"501-1000",1});
• define BPTWO= text({"0-500",1},{"Unknown",1},{"5001-10000",1});
• define YEAR= random(1998, 2000, uniform);
• define COUNTYNUMBER=ulist(random(1, rowcount("active_counties", "store"),
uniform),8);
• define COUNTY_A=distmember(fips_county, [COUNTYNUMBER.1], 2);
• define COUNTY_B=distmember(fips_county, [COUNTYNUMBER.2], 2);
• define COUNTY_C=distmember(fips_county, [COUNTYNUMBER.3], 2);
• define COUNTY_D=distmember(fips_county, [COUNTYNUMBER.4], 2); (FogBugz 1473)