DWHPro Insights

Teradata Data Types And Data Modeling

By Roland Wenzlofsky ·

Teradata Data Types And Data Modeling

Introduction to Teradata Data Types and Data Modeling

Suppose you work in a well-organized environment where diligence and discipline prevail and significant effort is put into designing things right in the beginning. In that case, the following post might seem outlandish to you. Else, read on.

In a more “experimental” table design landscape, you will soon encounter inconsistencies between column names, Teradata data types, and length decimal or format settings. Some affect table sizes and performance, so do not turn a blind eye to them, even if you first see yourself as a technician and administrator. If you do not wish to be reduced to that role, here might be your ladder upstairs.

Evaluate the following aspects of your data warehouse landscape upon deployment and creation and when the opportunity for change exists. For every point mentioned, find out to what extent your tables and columns are neat and tidy (The Good), outright irregular but obviously and visibly so (The Bad), or deficient at a second glance (The Ugly).

Cross-Table Name and Content Matches

Here, we have to differentiate between what I would like to call semantic, lexical, and technical mismatches.

The worst case is the semantic mismatch. Attributes containing prices and ratios are named equally across all tables, they look like prices and rates, but while they are used correctly, their content is different. Sometimes even in such a subtle way that you cannot detect it. That is the ugly case.

Lexical mismatches – inconsistent naming of related columns – are bad, but you can detect and correct them with little effort as they are in plain sight. Here is what you can look for:

  • Prefix and suffix conventions

Which existing prefixes indicate the table content, not just remnants from input sources or early developer sketches?

  • Abbreviation conventions

Is every date field called “_DT” in the end, or are there some “_DATE”s also?

  • Diverging labeling

Is what is called “_ID” in one place a “_NUM” or something with nothing attached to it in another?

  • Name switching along

Avoid renaming table columns to please particular end users. Solve that issue in view layers. Embellishment comes last.

  • Content over Source

Name your tables and columns according to their content and not based on the source the data came from. Expect to be loading from different sources sooner or later.

  • “Blind” and “disguised” attributes

Do not create reserve fields “just in case” to fill them with specific content when necessary. An even worse habit is to reuse abandoned attributes for different content. Will you remember one year from now what the “EXTRAFIELD1” contains and from when the EXCHANGE_RATE was used for holding commission fees instead and why?

If you are on the safe side, all your columns carry straightforward names and have the same data type and settings within all tables.

Here is an example of where to start your investigation.

SELECT DatabaseName, TableName, ColumnName, ColumnType, ColumnLength, DecimalTotalDigits, DecimalFractionalDigits, Nullable FROM DBC.ColumnsV WHERE DatabaseName = 'P_1111_A' AND ColumnName LIKE '%FLAG%';

Compare the flags in the inventory with actual business values before changing their types. VARCHAR(32) declares a maximum; it does not store 32 characters in every row. Savings from conversion to CHAR(1) depend on actual byte lengths, character set, row layout and compression, so a universal 31+2-byte saving is incorrect.

Data Type Appropriateness

Evaluate fixed versus variable length using actual stored values, character set, row format and compression; variable-length overhead is only one part of the comparison.

A one-character flag may suit CHAR(1), but validate semantics and measure storage rather than assuming every VARCHAR(1) row consumes exactly three bytes.

Second, does it come in fractions? Do not define significant decimals when fractions of a unit never occur or are always .00 in the source.

Third, aim for unmixing input. In-house grading or rating input should either be all numbers or all characters.

Parsimony

Since you should allow name and description fields to have a certain length to cope with unusual entries, these fields should not become grotesque. Reduce a VARCHAR(2000) to VARCHAR(500) or even less if the largest entry after years of loading is 250 tokens long.

The same parsimony should prevail with Decimal fields. It is overdone to store a percentage rate with two digits that can never reach the 0.00 to 100.00 range as DECIMAL(38,18). Not only is it extra space consumed for no reason, but it is also misleading from a business point of view.

The common motives for extremely long data types are fear of failure and uncertainty about the upcoming content. If you detect these reasons, take them as a warning sign that the project went into implementation too early because the source interface definitions are missing or incomplete. It will return to you tenfold through ongoing fixes and reporting user complaints.

Type-Specific Size Threshold Awareness

Be aware that a narrower data type parameter setting does not linearly translate into less space consumed. DECIMAL(19,6) and DECIMAL(20,6) cost the same, whereas DECIMAL(18,6) is one level down in the bytes reserved for entries. Look up the technical handbook overview to see which point you can be generous without extra space cost.

Character Set Suitability

Whenever you rate character settings, consider your most exotic named friend and how they would fit in. Loads can fail for not considering the customer base’s cross-culture nature. The ugly case is a landscape of “rotten” entries, i.e., misconverted and now unreadable characters that happen without runtime fails.

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

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

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

Leave a Comment

Get more practical Teradata advice

Join the DWHPro newsletter for Teradata query-tuning tips, troubleshooting examples and data warehousing insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam