TPC Benchmark™ DS - Standard Specification, Version 2.11.0 Page 1 of 141
TPC BENCHMARK ™ DS
Standard Specification
Version 2.11.0
April, 2019
Transaction Processing Performance Council (TPC)
www.tpc.org
info@tpc.org
© 2019 Transaction Processing Performance Council
All Rights Reserved
TPC Benchmark™ DS - Standard Specification, Version 2.11.0 Page 2 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 2.11.0 Page 3 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 2.11.0 Page 4 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)
• Modified answer set for Query 58 (FogBugz 1472)
TPC Benchmark™ DS - Standard Specification, Version 2.11.0 Page 5 of 141
• Corrected Version number in tool files (FogBugz 1393)
• Modified lines 35-36 in w_store_sales.h contain: #define W_STORE_SLAES_H to #define
W_STORE_SALES_H (FogBugz 1322)
• Removed comments in Clause 4.1.1.8 (FogBugz1263)
08-05-2016
2.3.0
• Changed 1 digit numbering to 2 digit numbering in B48, change YEAR.02 = 2002 to YEAR.01 = 2002
in B75 and "NULLCOLCS01 = cs_ship_addr_sk" to "NULLCOLCS.01 = cs_ship_addr_sk" in B76
(FogBugz 1676)
• Changed:
• B.1 change AGGFIELD.01 to AGG_FIELD.01
• B.4: SELECTCONE typo; should be SELECTONE
• B.11: SELECTONE value has a ":q" interposed; should be removed
• B.35: qualification parameters are not bulleted; should be bulleted
• B.49: "Query49.tpl" is capitalized; should be lowercase (FogBugz 1627)
• Changed answers sets: q34.ans_nulls_first and q34.ans_nulls_last. (FogBugz 1531)
• Changes to Query Templates 4, 29, 48, 73, 74, 78, 92, 93, (FogBugz 1501)
• Changes to print.c (FogBugz 616)
02-24-2017
2.4.0
• Merge clauses 6.1.1.2 and 6.1.1.3 into one clause 6.1.1.2 (FogBugz 1728)
• Change define to Define in the following templates: query12.tpl, query39.tpl, query91.tpl, query92.tpl,
query96.tpl, query98.tpl and change query tempates 66 and 85 to use upper case in substitution
parameter names (FogBugz 1697)
• Modify Query Template 34 (FogBugz 1696)
• Corrected column name in join clause for Query 78 (FogBugz 1654)
06-08-2017
2.5.0
• Added new minor query modification (MQM) rule to allow changes to scalar functions which only
affect output formatting or result precision. See new item 10 "Existing scalar functions" in clause
4.2.3.4 section (f). (Fogbugz 1756)
• Added new comment to clause B72 in Appendix B which clarifies that the scalar number 5 in the
expression "d1.d_date + 5" means to add 5 days. (Fogbugz 1894)
• Modified query24.tpl to add a missing join predicate, modified predicate to "c_birth_country <>
upper(ca_country)", and added ORDER BY clause to make output deterministic. Modified answer set
24.ans accordingly. Modified qualification parameters in clause B24 in Appendix B to be: COLOR.1 =
"peach", COLOR.2 = "saddle". Modified the business question in clause B24 in Appendix B to reflect
the predicate change. (FogBugz 1909)
• Modified the business question wording in clause B23 in Appendix B to more accurately describe
query 23 (query23.tpl). (FogBugz 1912)
• Added two new query variants, query10a.tpl and query35a.tpl. The new variants replace "exists {sub-
query A} or exists {sub-query B)" syntax to use UNION ALL. (FogBugz 1980)
• Added new minor query modification (MQM) rule to allow ordinals to be replaced by referenced
column names. See new item 3 "Ordinals" in clause 4.2.3.4 section (g). (FogBugz 1981)
09-26-2017
2.6.0
• Added new comment to clause 3.4.5 stating that dsdgen generates international characters and that data
must be encoded such that international characters are preserved (fogbugz 1556).
• Removed extraneous comments related to qualification substitution values from query templates
query23.tpl and query54.tpl (fogbugz 1984).
• Updated EULA.txt to version 2.2 of the TPC's End User Licence Agreement (fogbugz 1985).
• Corrected variant query10a.tpl by replacing WHERE with AND in two places (fogbugz 2030).
• Added a second column (a.ca_state) to the ORDER BY clause (to make the qualification answer set
deterministic) in query template query6.tpl (no change to answer set 6.ans was required) (fogbugz
2031).
• For consistency with other query template files, replaced DEC with DECIMAL in query template
query49.tpl (fogbugz 2036).
• Modified ORDER BY clause in query template query78.tpl to have column reference "ratio" instead of
previous expression "round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)" (fogbugz 2037).
• Added a second column (i_item_id) to the outer ORDER BY clause (to make the qualification answer
set deterministic) in query template query56.tpl (no change to answer sets was required) (fogbugz
2039).
• Added a third column (cs2.s1) to the outer ORDER BY clause (to make the qualification answer set
deterministic) in query template query64.tpl, and modified answer set 64.ans accordingly (fogbugz
2040).
• Added a second column (sales_amt_diff) to the ORDER BY clause (to make the qualification answer
set deterministic) in query template query75.tpl (fogbugz 2041).
• Changed ORDER BY clause (to make the qualification answer set deterministic) in query template
query49.tpl from " order by 1,4,5" to " order by 1,4,5,2" (no change to answer set 49.ans was required)
(fogbugz 2043).
• In Table 2-9 (Call_center Column Definitions) in section 2.4.2 in the specification, modified the
Datatype for columns "cc_closed_date_sk" and "cc_open_date_sk" from "integer" to "identifier". In
Table 2-10 (Catalog Page Column Definitions) in section 2.4.3, modified the Datatype of columns
"cp_start_date_sk" and "cp_end_date_sk") from "integer" to "identifier" (fogbugz 2045).
