Hi Everyone,
This post aims at giving the important/repeated questions that have been asked in the interview for Teradata positions. Instead of just giving answers, i have explained most of the concepts in a detail manner for the better understanding and makes you to answer if the question is based out of your answer. Please post if you need any more details on the concepts or Teradata related questions if you need an answer. I will keep updating this post and will include any sections if you are interested. ALL THE VERY BEST and Happy Reading.
TERADATA INTERVIEW QUESTIONS AND CONCEPTS
What is Teradata & how does it differ from other
conventional Systems?
Teradata is a relational database that can store billions of
rows and petabytes (1 petabyte=1000 terabytes) of data. The architecture of the
system makes it possible and provides the flexibility to access & process
the data in a faster way.
It differs from other conventional database systems from its
architecture to the processing speed. Easy scalability and its fault tolerance keep
the demand high for this system.
What are the main components of Teradata System?
Teradata has 3 main components which do wonder to the world
of data management & Storage.
It has
1. PE (Parsing Engine) : Acts as a gate keeper to the
Teradata Systems and manages all sessions, interprets the SQL statements for
any errors, manages the access rights for the user, defines a least expensive
optimizer plan for the query to execute and sends the request to AMP via Bynet.
2. Message Passing Layer (Bynet) : Carries messages between the AMPs and PEs,
provides Point-to-Point and Broadcast communications, Merging answer sets back
to the PE and Making Teradata parallelism possible
3. AMP (Access Module Processor) : AMP is the heart of
Teradata which does most of the operations for data storage and retrieval. It
also takes care of finding the rows
requested, Lock management of the tables and rows, Sorting rows, Aggregating
columns, Join processing etc.
What are Primary Index and Primary Key in Teradata?
Unlike other database systems, Teradata distributes the data
based on PI (Primary Index). PI is defined at the time of table creation and database
automatically takes the first column as the PI if the PI is not mentioned explicitly.
Since the data distribution is based on PI, it is wise to
choose a PI that evenly distributes the data among the AMP.
For example, if Table A has two columns like below and we
have 5 AMPs in the System.
ID Gender
1 Male
2 Male
3 Male
4 Male
5 Female
If we choose ID as PI, since the values are distinct all 5
rows are distributed evenly across all 5 AMPs. But if GENDER has been chosen as
PI , we have only 2 distinct values and data will be stored in only 2 AMPS
leaving other 3 AMPS empty and idle.
Note: Same Value of PI will be stored in the same AMP.
Primary Key is a concept that uniquely identifies a
particular row of a table.
What are the types of PI (Primary Index) in Teradata?
There are two types of Primary Index. Unique Primary Index (
UPI) and Non Unique Primary Index (NUPI). By default, NUPI is created when the
table is created. Unique keyword has to be explicitly given when UPI has to be
created.
UPI will slower the performance sometimes as for each and
every row , uniqueness of the column value has to be checked and it is an additional
overhead to the system but the distribution of data will be even.
Care should be taken while choosing a NUPI so that the
distribution of data is almost even . UPI/NUPI decision should be taken based
on the data and its usage.
How to Choose Primary Index(PI) in Teradata?
Choosing a Primary Index is based on Data Distribution and
Join frequency of the Column. If a Column is used for joining most of the
tables then it is wise to choose the column as PI candidate.
For example, We have an Employee table with EMPID and DEPTID
and this table needs to be joined to the Department Table based on DEPTID.
It is not a wise decision to choose DEPTID as the PI of the
employee table. Reason being, employee table will have thousands of employees
whereas number of departments in a company will be less than 100. So choosing
EMPID will have better performance in terms of distribution.
How the data is distributed among AMPs based on PI in
Teradata?
• Assume a row is to be
inserted into a Teradata table
• The Primary Index Value
for the Row is put into the Hash Algorithm
• The output is a 32-bit
Row Hash
• The Row Hash points to a
bucket in the Hash Map.The first 16 bits of the Row Hash of is used to locate a
bucket in the Hash Map
• The bucket points to a
specific AMP
• The row along with the
Row Hash are delivered to that AMP
When the AMP receives a row it will place the row into the
proper table, and the AMP checks if it has any other rows in the table with the
same row hash. If this is the first row with this particular row hash the AMP
will assign a 32-bit uniqueness value of 1. If this is the second row hash with
that particular row hash, the AMP will assign a uniqueness value of 2. The
32-bit row hash and the 32-bit uniqueness value make up the 64-bit Row ID. The
Row ID is how tables are sorted on an AMP.
This uniqueness value is useful in case of NUPI's to
distinguish each BUPI value.
Both UPI and NUPI is always a One AMP operation as the same
values will be stores in same AMP.
How Teradata retrieves a row?
For example, a user runs a query looking for information on
Employee ID 100. The PE sees that the Primary Index Value EMP is used in the
SQL WHERE clause.
Because this is a Primary Index access operation, the PE
knows this is a one AMP operation. The PE hashes 100 and the Row Hash points to
a bucket in the Hash Map that represents AMP X. AMP X is sent a message to get the Row Hash and
make sure it’s EMP 100.
What are Secondary Indexes (SI) , types of SI and
disadvantages of Secondary Indexes in Teradata?
Secondary Indexes provide another path to access data.
Teradata allows up to 32 secondary indexes per table. Keep in mind; row
distribution of records does not occur when secondary indexes are defined. The
value of secondary indexes is that they reside in a subtable and are stored on
all AMPs, which is very different from how the primary indexes (part of base
table) are stored. Keep in mind that Secondary Indexes (when defined) do take
up additional space.
Secondary Indexes are frequently used in a WHERE clause. The
Secondary Index can be changed or dropped at any time. However, because of the
overhead for index maintenance, it is recommended that index values should not
be frequently changed.
There are two different types of Secondary Indexes, Unique
Secondary Index (USI), and Non-Unique Secondary Index (NUSI). Unique Secondary
Indexes are extremely efficient. A USI is considered a two-AMP operation. One
AMP is utilized to access the USI subtable row (in the Secondary Index
subtable) that references the actual data row, which resides on the second AMP.
A Non-Unique Secondary Index is an All-AMP operation and
will usually require a spool file. Although a NUSI is an All-AMP operation, it
is faster than a full table scan.
Secondary indexes can be useful for:
• Satisfying complex
conditions
• Processing aggregates
• Value comparisons
• Matching character
combinations
• Joining tables
How are the data distributed in Secondary Index Subtables
in Teradata?
When a user creates a Secondary Index, Teradata
automatically creates a Secondary Index Subtable. The subtable will contain
the:
• Secondary
Index Value
• Secondary
Index Row ID
• Primary Index
Row ID
When a user writes an SQL query that has an SI in the WHERE
clause, the Parsing Engine will Hash the Secondary Index Value. The output is
the Row Hash, which points to a bucket in the Hash Map.
That bucket contains an AMP number and the Parsing Engine
then knows which AMP contains the Secondary Index Subtable pertaining to the
requested USI information.
The PE will direct the chosen AMP to look-up the Row
Hash in the Subtable. The AMP will check to see if the Row Hash exists in the
Subtable and double check the subtable row with the actual secondary index
value. Then, the AMP will pass the Primary Index Row ID back up the BYNET
network. This request is directed to the AMP with the base table row, which is
then easily retrieved.
What are the types of JOINs available in Teradata?
Types of JOINs are : Inner
Join, Outer Join (Left, Right, Full), Self Join, Cross Join and Cartesian
Joins.
The key things to know about Teradata and Joins
• Each AMP holds a portion
of a table.
• Teradata uses the Primary
Index to distribute the rows among the AMPs.
• Each AMP keeps their
tables separated from other tables like someone might keep clothes in a dresser
drawer.
• Each AMP sorts their
tables by Row ID.
• For a JOIN to take place
the two rows being joined must find a way to get to the same AMP.
• If the rows to be joined
are not on the same AMP, Teradata will either redistribute the data or
duplicate the data in spool to make that happen.
What are the types of Join Strategies available in
Teradata?
Join Strategies are used by the optimizer to choose the best
plan to join tables based on the given join condition.
• Merge (Exclusion)
• Nested
• Row Hash
• Product (including
Cartesian Product joins)
There are different types of merge join strategies
available. But in general , while joining two tables the data will be
redistributed or duplicated across all AMPs to make sure joining rows are in
the same AMPs.
If the two tables are joined based on PI, no redistribution/duplication
will happen as the rows will be in the same AMP and performance will be
better. If one table PI is used and
Other table PI not used, redistribution/duplication of the table will happen
based on the table size.In these cases Secondary Indexes will be helpful.
Explain types of re-distribution of data happening for
joining of columns from two tables in Teradata?
Case 1 - P.I = P.I joins
Case 2 - P.I = non Index joins
Case 3 - non Index = non Index joins
Case1 - there is no redistribution of data over amp's. Since
amp local joins happen as data are present in same AMP and need not be
re-distributed. These types of joins on unique primary index are very fast.
Case2 - data from second table will be re-distributed on all
amps since joins are happening on PI vs. non Index column. Ideal scenario is
when small table is redistributed to be joined with large table records on same
amp
case3 - data from both the tables are redistributed on all
AMPs. This is one of the longest processing queries; Care should be taken to
see that stats are collected on these columns
What is Partitioned Primary Index (PPI) in Teradata?
Partitioned primary index is physically splitting the table
into a series of subtables, one for every partitioning value. When a single row
is accessed, it looks first at the partitioning value to determine the
subtable, then at the primary index to calculate the rowhash for the row(s).
For example, we have PPI on a MONTH Column, the rows of
particular months are all sorted with in the same partition and whenever data
is accessed for particular month, it will retrive the data in a faster way.
It helps to avoid full table scans.
What are the advantages and disadvantages of PPI in Teradata?
Advantages:
· Range queries don’t have
to utilize a Full Table Scan.
· Deletions of entire
partitions are lightning fast.
· PPI provides an excellent
solution instead of using Secondary Indexes
· Tables that hold yearly
information don’t have to be split into 12 smaller tables to avoid Full Table
Scans (FTS). This can make modeling and querying easier.
· Fastload and Multiload
work with PPI tables, but not with all Secondary Indexes.
Disadvantages:
· A two-byte Partition
number is added to the ROW-ID and it is now called a ROW KEY. The two-bytes per
row will add more Perm Space to a table.
· Joins to Non-Partitioned
Tables can take longer and become more complicated for Teradata to perform.
· Basic select queries
utilizing the Primary Index can take longer if the Partition number is not also
mentioned in the WHERE clause of the query.
· You can’t have a Unique
Primary Index (UPI) if the Partition Number is not at least part of the Primary
Index. You must therefore create a Unique Secondary Index to maintain
uniqueness.
Volatile and Global Temporary Tables in Teradata?
Volatile tables are temporary tables that are materialized
in spool and are unknown to the Data Dictionary.
A volatile table may be utilized multiple times and in more
than one SQL statement throughout the life of a session. This feature allows
for additional queries to utilize the same rows in the temporary table without
requiring the rows to be rebuilt.
Volatile tables are local to session and the tables are
dropped once the session is disconnected.
ON COMMIT PRESERVE ROWS option should be mentioned at the
time of table creation. It means that at the end of a transaction, the rows in
the volatile table will not be deleted. The information in the table remains
for the entire session. Users can ask questions to the volatile table until
they log off. Then the table and data go away.
Global Temporary Tables are similar to volatile tables in
that they are local to a user’s session. However, when the table is created,
the definition is stored in the Data Dictionary. In addition, these tables are
materialized in a permanent area known as Temporary Space. Because of these
reasons, global tables can survive a system restart and the table definition
will not discarded at the end of the session. However, when a system restarts,
the rows inside the Global Temporary Table will be removed. Lastly, Global
tables require no spool space. They use Temp Space.
Statistics can be collected in both of the tables in TD13
Version. Previously Collecting Stats on Volatile tables are not allowed.
SubQuery and Correlated Subquery in teradata?
Sub queries and Correlated Sub queries are two important
concepts in Teradata and used most of the times.
The basic concept behind a subquery is that it retrieves a
list of values that are used for comparison against one or more columns in the
main query. Here the subquery is executed first and based on the result set,
the main query will be executed.
For example,
Select empname,deptname from employee where empid IN (
select empid from salarytable where salary>10000).
In the above query, empid will be choosen first based on the
salary in the subquery and main query will be executed based on the result subset.
Correlated Subquery is an excellent technique to use when
there is a need to determine which rows to SELECT based on one or more values
from another table.It combines subquery processing and Join processing into a
single request.
It first reads a row from the main query and then goes into
the subquery to find the rows that match the specified column value.Then it
goes for the next row from the main query. This process continues untill all
the qualifying rows from MAIN query.
For example,
select empname,deptno, salary
from employeetable as emp
where
salary=(select max(salary) from employeetable as emt where
emt.deptno=emp.deptno)
Above query returns the highest paid employee from each
department. This is also one of the scenario based questions in teradata.
How to calculate the tablesize , database size and free space left in a database in
teradata?
DBC.TABLESIZE and DBC.DISKSPACE are the systems tables used
to find the space occupied.
Below Query gives the table size of each tables in the
database and it will be useful to find the big tables in case of any space
recovery.
SELECT DATABASENAME,
TABLENAME,
SUM(CURRENTPERM/(1024*1024*1024)) AS "TABLE SIZE"
FROM
DBC.TABLESIZE
WHERE
DATABASENAME = <'DATABASE_NAME'> AND TABLENAME =
<'TABLE_NAME'>
GROUP BY 1,2;
Below query gives the total space and free space available
in a database.
SELECT
DATABASENAME
DATABASE_NAME,
SUM(MAXPERM)/(1024*1024*1024) TOTAL_PERM_SPACE,
SUM(CURRENTPERM)/(1024*1024*1024) CURRENT_PERM_SPACE,
TOTAL_PERM_SPACE-CURRENT_PERM_SPACE as FREE_SPACE
FROM
DBC.DISKSPACE
WHERE
DATABASENAME =
<'DATABASE_NAME'>
group by 1;
What are the Performance improvement techniques available
in Teradata?
First of all use EXPLAIN plan to see how the query is
performing. Keywords like Product joins, low confidence are measures of poor
performance.
Make Sure, STATS are collected on the columns used in WHERE
Clause and JOIN columns. If STATS are collected , explain plan will show HIGH
CONFIDENCE This tells the optimizer about the number of rows in that table
which will help the optimizer to choose the redistribution/duplication of
smaller tables.
Check the joining columns & WHERE Clause whether PI, SI
or PPI are used.
Check whether proper alias names are used in the joining
conditions.
Split the queries into smaller subsets in case of poor performance.
What does” Pseudo
Table” Locks mean in EXPLAIN Plan in Teradata?
It is a false lock which is applied on the table to
prevent two users from getting
conflicting locks with all-AMP requests.
PE will determine an particular AMP to manage all AMP LOCK
requests for given table and Put Pseudo lock on the table.
Can you compress a column which is already present in
table using ALTER in Teradata?
No, We cannot use
ALTER command to compress the existing columns in the table.
A new table structure has to be created which includes the
Compression values and data should be inserted into Compress column table.
Please note - ALTER can be used only to add new columns with compression values to table.
How to create a table with an existing structure of
another table with or without data and also with stats defined in Teradata?
CREATE TABLE new_TABLE AS old_TABLE WITH DATA
CREATE TABLE new_TABLE AS old_TABLE WITH NO DATA
CREATE TABLE new_TABLE AS old_TABLE WITH DATA AND STATS
How to find the duplicate rows in the table in Teradata?
Group by those fields and add a count greater than 1
condition for those columns
For example –
SELECT name, COUNT (*) FROM TABLE EMPLOYEE GROUP BY name
HAVING COUNT (*)>1;
Also DISTINCT will be
useful. If both DISTINCT and COUNT(*) returns same number then there are no
duplicates.
Which is more efficient GROUP BY or DISTINCT to find
duplicates in Teradata?
With more duplicates GROUP BY is more efficient while if we
have fewer duplicates the DISTINCT is efficient.
What is the difference between TIMESTAMP (0) and
TIMESTAMP (6) in teradata?
Both has the Date and Time Values. The major difference is
that TIMESTAMP (6) has microsecond too.
What is spool space and when running a job if it reached
the maximum spool space how you solve the problem in Teradata?
Spool space is the space which is required by the query for
processing or to hold the rows in the answer set. Spool space reaches maximum
when the query is not properly optimized. We must use appropriate condition in
WHERE clause and JOIN on correct columns to optimize the query. Also make sure
unncessary volatile tables are dropped as it occupies spool space.
Why does varchar occupy 2 extra bytes?
The two bytes are for the number of bytes for the binary
length of the field.
It stores the exact no of characters stored in varchar
What is the difference between User and database in
Teradata?
- User is a database with password but database cannot have password
- Both can contain Tables , views and macros
- Both users and databases may or may not hold privileges
- Only users can login, establish a session with Teradata database and
they can submit requests
What are the types of HASH functions used in teradata?
These are the types of HASH, HASHROW, HASHAMP and
HASHBAKAMP. Their SQL functions are-
HASHROW (column(s))
HASHBUCKET (hashrow)
HASHAMP (hashbucket)
HASHBAKAMP (hashbucket)
To find the data distribution of a table based on PI, below
query will be helpful. This query will give the number of records in each AMP
for that particular table.
SELECT HASHAMP(HASHBUCKET(HASHROW(PI_COLUMN))),COUNT(*) FROM
TABLENBAME GROUP BY 1.
TERADATA UTILITIES:
Explain Fast Load in Teradata?
Loads large amount of data from external file into an empty
table at high speed.Only load one empty table with 1 fast load job.Tables
defined with Referential integrity, secondary indexes, join indexes, hash
indexes or triggers cannot be loaded with FastLoad.Duplicate rows cannot be
loaded into a multiset table with FastLoad
FastLoad has two phases – Data Acquisition and Application.
It requires separate error table for each phase. Use the BEGIN LOADING
statement to specify (and create) the two error tables for the operation.
We
can specify the error table in the same database as the data table or different
database.
We must remove the error tables before we re-run the same
load job or it will terminate in an error condition.
Explain Multi Load in Teradata?
Used for loading, updating or deleting data to and from
populated tables, typically with batch inputs from a host file.Cannot process
tables defined with USI’s, Referential Integrity, Join Indexes, Hash Indexes, or
Triggers.No data retrieval capability.Import tasks require use of Primary Index
The Multiload supports five target tables per script. Tables
may contain pre-existing data. Ability to do INSERTs UPDATEs, DELETEs and
UPSERTs.
Use RELEASE MLOAD tablename; statement if MLOAD doesn’t
successfully completed and there is no desire to restart the MLOAD. This will
return the target table to general availability. Error tables should be dropped
manually, as RELASE MLOAD will not drop them automatically.
Explain TPUMP (Teradata Parallel Data Pump) Utility in
Teradata?
•TPUMP allows near real time updates from Transactional Systems into
the Data Warehouse.It can perform Insert, Update and Delete operations or a
combination from the same source.
•It can be used as an alternative to MLOAD for low volume batch
maintenance of large databases.
•TPUMP allows target tables to have Secondary Indexes, Join Indexes,
Hash Indexes, Referential Integrity, Populated or Empty Table, Multiset or Set
Table or Triggers defined on the Tables.
•TPUMP can have many sessions as it doesn’t have session limit.
•TPUMP uses row hash locks thus allowing concurrent updates on the same
table
How do you transfer large amount of data in Teradata?
Transferring of large amount of data can be done by using
the various Teradata Utilities i.e. BTEQ, FASTLOAD, MULTILOAD, TPUMP and
FASTEXPORT.
•BTEQ (Basic Teradata Query) supports all 4 DMLs: SELECT,
INSERT, UPDATE and DELETE.BTEQ also support IMPORT/EXPORT protocols.
•Fastload, MultiLoad and Tpump transfer the data from Host
to Teradata.
•FastExport is used to export data from Teradata to the
Host.
How to make sure BTEQ utility is not erroring out while
dropping a table when the table doesnt exist in Teradata?
Setting the error level to 0 will achieve this.
ERRORLEVEL (3807) SEVERITY 0;
DROP TABLE TABLENAME;
ERRORLEVEL (3807) SEVERITY 8;
Difference between MultiLoad and TPump in Teradata?
Tpump provides an alternative to MultiLoad for low volume batch
maintenance of large databases under control of a Teradata system.
Tpump updates information in real time, acquiring every bit
of a data from the client system with low processor utilization.
It does this through a continuous feed of data into the data
warehouse, rather than the traditional batch updates.
Continuous updates results in more accurate, timely data.
Tpump uses row hash locks than table level locks. This allows you to run
queries while Tpump is running.
Different phases of MultiLoad in Teradata?
• Preliminary phase
• DML phase
• Acquisition phase
• Application phase
• End phase
Which is faster – MultiLoad delete or Delete command in
Teradata?
MultiLoad delete is faster than normal Delete command, since
the deletion happens in data blocks of 64Kbytes, whereas delete command deletes
data row by row.
Transient journal maintains entries only for Delete command
since Teradata utilities doesn’t support Transient journal loading
For smaller table deletes, simple DELETE command is enough.
Multiload delete is useful when the delete has to be performed on a large table
in teradata.
How to Skip or Get first and Last Record from Flat File
through MultiLoad in Teradata?
In .IMPORT command in Mload we have a option to give record
no. from which processing should begin. i.e. ‘FROM m’ ‘m’ is a logical record
number, as an integer, of the record in the identified data source where
processing is to begin. You can mention ’m’ as 2 and processing will start from
second record.
THRU k and FOR n are two options in the same Mload command,
functions same towards the end of the processing.
Adding to the above, if from n"start record" and
for n "stop record" are not mentioned, mload considers records from
start till the end of the file
Why Fload doesn’t support multiset table in Teradata?
Fload does not support Multiset table because of restart
capability.
Say, the fastload job fails. Till the fastload failed, some
number of rows was sent to the AMP's.
Now if you restart FLOAD, it would start loading record from the last
checkpoint and some of the consecutive rows are sent for the second time. These
will be caught as duplicate rows are found after sorting of data.
This restart logic is the reason that Fastload will not load
duplicate rows into a MULTISET table. It assumes they are duplicates because of
this logic. Fastload support Multiset table but does not support the duplicate
rows. Multiset tables are tables that allow duplicate rows. When Fastload finds
the duplicate rows it discards it. Fast Load can load data into multiset table
but will not load the duplicate rows
How to skip the header row in the fastload script
RECORD 2; /* this skips first record in the source file */
DEFINE ...
