TOP Teradata Interview Questions and Answers
In Teradata, what is the significance of UPSERT command?
UPSERT basically stands for Update Else Insert. This option is available only in Teradata.
Highlight the advantages of PPI(Partition Primary Index).
PPI is basically used for Range-based or Category-based data storage purposes. When it comes to Range queries, there is no need of Full table scan utilization as it straightaway moves to the consequent partition thus skipping all the other partitions.
Give the sizes of SMALLINT, BYTEINT and INTEGER.
SMALLINT – 2 Bytes – 16 Bites -> -32768 to 32767
BYTEINT – 1 Bytes – 8 Bits -> -128 to 127
INTEGER – 4 Bytes – 32 Bits -> -2,147,483,648 to 2,147,483,647
What is meant by a Least Cost Plan?
A Least Cost Plan basically executes in less time across the shortest path.
Highlight the points of differences between the database and user in Teradata.
- A database is basically passive, whereas a user is active.
- A database primarily stores all the objects of database, whereas a user can store any object whether that is a macro, table, view, etc.
- Database does not has password while the user has to enter password.
Highlight the differences between Primary Key and Primary Index.
- Primary index is quite mandatory, whereas Primary Key is optional.
- Primary Index has a limit of 64 tables/columns, whereas Primary Key does not have any limit.
- Primary Index allows duplicates and nulls, whereas Primary Key doesn’t.
- Primary Index is a physical mechanism, whereas Primary Key is purely logical mechanism.
Explain how spool space is used.
Spool space in Teradata is basically used for running queries. Out of the total space that is available in Teradata, 20% of the space is basically allocated to spool space.
Highlight the need for Performance Tuning.
Performance tuning in Teradata is basically done to identify all the bottlenecks and then resolve them.
Comment whether bottleneck is an error or not.
Technically, bottleneck is not a form of error, but it certainly causes a certain amount of delay in the system.
How can bottlenecks be identified?
There are basically four ways of identifying a bottleneck. These are: –
- Teradata Visual Explain
- Explain Request Modifier
- Teradata Manager
- Performance Monitor
What is meant by a Highest Cost Plan?
As per Highest Cost Plan, the time taken to execute the process is more, and it takes the longest path available.
Highlight all the modes that are present under Confidence Level.
Low, No, High and Join are the four modes that are present under Confidence Level.
Name the five phases that come under MultiLoad Utility.
Preliminary Phase, DML Phase, Data Acquisition Phase, Application Phase and End Phase.
Highlight the limitations of TPUMP Utility.
Following are the limitations of TPUMP utility: –
- We cannot use SELECT statement.
- Data Files cannot be concatenated.
- Aggregate and Exponential operators are not supported.
- Arithmetic functions cannot be supported.
In BTEQ, how are the session-mode parameters being set?
.set session transaction BTET -> Teradata transaction mode
.set session transaction ANSI -> ANSI mode
These commands will work only when they are entered before logging into the session.