Introduction to Teradata Data Types
Further Data Type considerations require occasional attention to additional issues.
Part of the Teradata Physical Design and Data Modelling guide.
Happenstance Nullability
Strive for nullability settings that adhere to the data model and are consistent throughout all tables. Allowing mandatory columns to be left open for null entries incurs costs for both storage and optimization. An additional presence bit per column must be allocated, and a null option must be considered when creating the SQL execution plan.
Content Cleanliness
Ensure to thoroughly verify when transferring data from a source table attribute to a target table attribute to avoid discrepancies and inconsistencies in values. Consider that an attribute like ORDER_NUMBER can have varying structures based on its origin.
AAA.123.456O or BBB.123.456. Assume further that the positions of 456 represent a customer-type code. The “O” stands for order. The application creates it that way. If you merely extract the number using a substring starting from position 5, your result will not be entirely numeric – which you might not even realize as you load into a character field! – And there are invalid customer type codes. Surprisingly wrong reporting results for customer-type aggregations despite no-load fails and quantitative checks passed will keep you busy later.
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 bookContent Sobriety
Achieving this goal is a commendable task for any warehouse, despite the difficulty posed by manual data entry. Nonetheless, it is a goal worth striving for, much like how we regularly organize our living spaces, rather than aiming for a one-time, ultimate state of cleanliness.
In contrast to cleanliness, which refers to the adequate processing of data, I like to call sobriety a state of redundancy-free and simplified attribute content down to the point where it cannot be pruned further without loss of information.
First, values should be all written out or all coded and abbreviated. A threefold categorization of “A,” “B” and “DEFAULT” could be converted to “A,” “B” and “D” without loss of information, but size needs down to one character byte instead of several. Taking this further, could dozens of full-text entries be converted to official codes so your table does not take in extra calories?
An anonymized real-world example:
An ENTITY_NAME, let’s say a branch name, is stored like these thousands of times each:
“ABCD – ABCD Division of ABCD Industries Fantasia”
Why not cut it down to “ABCD” and store the meaning of the ENTITY_NAME in a tiny lookup table?
In this historical 177-million-row example, converting a name to a four-character code was considered. The approximate (average length – 4 + 2) byte calculation assumes uncompressed single-byte strings and a particular row layout; actual savings depend on character set and storage representation.
Standardization can improve consistency and compression only when it preserves business meaning. Agree mappings with data owners and retain original source values or an auditable mapping; apparently similar addresses or names may refer to different entities.
Derived from what I just saw on a production table, imagine a street field mapped 1 to 1 from the source where we have stored “Main Street,” “Main Str.” as well as “Mainstreet” thousands of times each. Once a standard form of street name representation is defined, the distinct values can probably be cut in half.
Do not collapse unknown, refused, invalid and missing values unless the business rules explicitly permit losing those distinctions. Preserve source values and audit information for reversibility.
Propose and plan this, and the chances are that your reporting and analytics department colleagues will add you as a new friend.
A Success Story to Inspire You
A historical project reported 5–25% space reductions, with some larger outcomes, after resizing types. Those percentages were not reproduced in this audit and are not forecasts for another workload.
See also:
First, the PDM, then Compression – Measures to reduce Resource Usage
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.
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
Hello,
These two-part of data types considerations are really nice.
It is not only the waste space that we have on our tables, (so more time for backup and so on) but as you said, it looks like that we do not know our data.
One thing that I have to mention here, is how our DDL affects our performance and statistics.
By defining, for simplicity reasons the columns as Unicode, do not only affect the space of the table (since they want two bytes per character) but and the length that statistics can keep.
Until TD 13.10, it was 16 and a lot of wrong stats have been defined due to this.From 14 and on, this has been partial
solved from TD by setting the length at 25 or manually change up to 50 (even when collecting stats on multicolumn,
the acceptable length from TD until 13 then summed to be 16, but from TD 14 every column on the multicolumn can be
from 25 to 50 ).
Furthermore, as you mentioned, never define a varchar column with length much more than the expected one. TD has an issue here, on spool space during transactions. If the columns participate on a group by / order by / union or on Multiload / FastLoad operations,
then TD does not take the actual length of the varchar column but the defined one. This leads TD to consume more CPU / IO
even for a simple query. Imagine, on your example, if you have 1 Byte rows and the
column is defined as VARCHAR(200) but your actual max length is 50, you waste 150 bytes * 1 B rows (and if the column is defined as
Unicode, * 2 ).
Thanks.
Hello Dimitrios!
Thank you for your refinement on the length and character set issue!
You inspire me to investigate and test the relationship between Character set choice, length and statistics a little deeper.
Kind Regards,
Paul