DWHPro Insights

Building a Teradata Data Warehouse: Considerations for ETL Process, SQL Queries, and Physical Data Model

By Roland Wenzlofsky ·

Building a Teradata Data Warehouse: Considerations for ETL Process, SQL Queries, and Physical Data Model

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

Checklist PDM
  • 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?
  • Tables can be colocated when join/hash semantics and maps are compatible. Identical PI labels alone do not guarantee AMP-local joins.
  • Is the primary index of a table often used in a WHERE condition?
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • 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)?
  • Choose FALLBACK and recovery protection from resilience requirements and supported deployment behavior; do not remove protection as a universal performance recommendation.
  • 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

stathist
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • Do you use DELETE instead of DROP/CREATE to preserve statistics histories?
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • Choose SET/MULTISET, compression and statistics from integrity requirements and measured workload. Target useful statistics and validate sampling/collection cost rather than collecting everything indiscriminately.
  • 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 used for marketing purposes or stored.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam