This post aims to compile all crucial aspects to be considered while constructing a Teradata Data Warehouse, including the ETL process and SQL queries.

This list is just the beginning, and I anticipate receiving valuable feedback from my readers to expand it in the future. Initially, I have provided a few concepts, but I intend to include more categories and topics in the coming days and weeks. Furthermore, you can contribute your ideas at the end of this blog post, which I will continuously incorporate into the article, along with your name if desired.

The Teradata Physical Data Model

  • Are all Primary Key columns defined as NOT NULL?
  • Does the Primary Index distribute the rows equally across all AMPs?
  • Are the Primary Index column values stable, or do they change frequently?
  • Do columns with the same content have the same data type across all tables?
  • Are SET tables only used for exceptional cases, otherwise always MULTISET tables?
  • Are tables from which a range of values is often queried row-level partitioned?
  • Do tables that are often joined together have the same primary index?
  • Is the primary index of a table often used in a WHERE condition?
  • Was Multi-Value compression performed on all tables?
  • Are all row partitions maintained (to cover new values)?
  • Is the correct CHARACTER SET used (LATIN vs. UNICODE)?
  • Is the DATABLOCKSIZE appropriate for the workload (Tactical vs. Strategic)?
  • Are tables created without the FALLBACK option? (Raluca Topor)
  • Was a 1-AMP design chosen for tiny tables (1-AMP Hashmap)? (Peter Nagy)
  • Was columnar considered for wide datamarts with narrow queries? (Peter Nagy)
  • Was NOT NULL unique key applied on parent tables to enable join elimination? (alternative to soft RI) (Peter Nagy)
  • Was primary AMP design considered for tables that are frequently reloaded and yet still joined? (Peter Nagy)
  • Is adequate sample percentage used during collect stats for big tables? (Peter Nagy)
  • Are statistics copied from table to table whenever it’s a replica instead of recollected? (Peter Nagy)
  • Is a value-ordered NUSI considered for range query columns on tables where row partitioning is not an option? (Peter Nagy)

The Teradata Statistics

  • Are full statistics collected on all small tables (fewer table rows than AMPs)?
  • Are statistics collected on all non-indexed columns that are used in WHERE conditions?
  • Are full statistics collected on all skewed columns?
  • Do you use DELETE instead of DROP/CREATE to preserve statistics histories?
  • Have you collected statistics on all join columns?
  • Have you collected statistics on all Primary Indexes?
  • Have you collected statistics on all NUSIs?
  • Have you collected statistics on all partition columns (individually and in combination)?
  • Are you using Create Table Table1 as Table2 with data and stats to keep the stats? (Atharva Joshi)

Teradata SQL

teradata union
  • Do you use UNION only if you need to remove duplicates, otherwise, UNION ALL?
  • Do you only select the columns you really need?
  • Do you use outer joins only when it is really necessary?
  • If possible, do you use WHERE conditions to enable partition elimination?
  • Do you use multi-statement requests where possible?
  • Do you use CLOB instead of large VARCHAR for rarely requested columns?
  • Are you careful not to use functions on join columns?
  • Are you using a Fast Path INSERT/SELECT?

Please Send Me Your Own Tuning Ideas

Please add this tuning idea!

Your email address will not be utilized for marketing or stored.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like