Total Interview Questions

1. What does the EXPLAIN extension do? What are the different confidence levels?

EXPLAIN brings out the sequence of steps that is prepared by the optimizer to perform an SQL transaction. The confidence level represents the awareness of the Optimizer regarding the data involved in joins and the number of rows/records of data that is generated as output. There are three levels of confidence namely high confidence, low confidence and no confidence.

2. What is BTEQ?

The BTEQ is the basic SQL query language for teradata. The BTEQ offers the commands and the SQL statements that are required to export, import or report the data from the database. It is possible to have single or batch execution of the SQL statements.

3. What are the different error tables used during a mload job execution?

The mload jobs involve the creation of the following 4 error tables:

ñ The Acquisition error table or the ET table is the one that contains all the errors related to the data errors occurring in the acquisition phase of the MultiLoad task.

ñ Similarly the UV table or the Application phase error table contains the data errors from the application phase.

ñ The Work Table is one which is used to store the presently active records which form the selection set.

ñ The log table containing the info about the execution and the tracking info for the various check points of the execution of the mload is used for restoration in case of job failure.

4. What do you mean by skewness?

Skewness refers to the variation in the number of rows among the various AMPs. The higher the variation (some have high number of rows and some have very much less number of rows) then it is said to have high skewness. This affects the effective parallel execution in teradata. The way to get around is by assigning proper indexes to bring about even distribution of rows across the AMPs.

 

5. What are the different phases involving in loading of the data when it comes to FastLoad and MultiLoad?

The FastLoad loads the data in 2 phases as:

ñ  The records are moved to all the AMP without any hashing done. Then the endloading singalling is given.

ñ  The AMP then starts hashing the loaded records and moves it across to the AMPS appropriately.

The Multiload loads the data in 5 phases as:

ñ  File is imported and the scripts are checked for correctness.

ñ  The base table records are read into the memory of the Work Table.

ñ  Lock the appropriate table headers.

ñ  Perform the data manipulation tasks over the tables.

ñ  Drop the Work table/release all the acquired locks.

6. Does Fastload support the use of multiset and duplicates? Why?

FastLoad has restriction when it comes to restart logic. The restart also sometimes means that Fasload job can send certain rows again, the fastload identifies the duplicates and rejects the duplicates to maintain internal consistency.  Also the MS tables are not supported by the Fastload. These conditions can give the inference that multiset and duplicates.

7. At what time of table creation should the GRANT option be used for access permissions declaration? What is the default access granted?

The tables can be created and at any point of time during creation or later. The default permission is usually READ access for the different users, but it depends on the overall schema and the user who is creating the table.

8. How does Teradata avoid duplicate rows in the SET Tables?

The Teradata has the hashes across every row and assigns a primary index. The row is hashed and given to the specific AMP for insertion into the SET table. If the AMP finds that the hash is already in the database,  then the comparison of the entire row is taken up. If at this level there is again a match, then the row is skipped and not inserted into the SET table.

9. What is a vdisk?

The disk array that is connected to the teradata are associated with each vproc of AMP through ranks. The vdisk refers to the total disk space accessible for specific AMP. The vdisk associates itself with upto 3 different ranks for contacting with the physical storage devices.

10. How will you go about optimizing the performance characteristics of the Teradata execution?

Performing optimization tuning usually involves the following aspects:

ñ  Statistical analysis of the joins and the rows involved in such cross table joins. Reducing it to the minimal level is required.

ñ  Choosing of the right indexes to have the evenly distributed rows (avoiding skewness).

ñ  NUSI should be avoided.

11. What is the difference between Volatile And Global Temporary tables?

ñ  The definition of the Global temporary table goes into the Data dictionary even though data is stores in the temp space. The definition of the VTT goes into the cache and the data goes to the spool space. Thus the definition of the VTT ends with session unlike GTT.

ñ  We cannot collect stats information on the VTT while we can do so on GTT.

12. What are cliques?

Cliques are the set of teradata nodes that are connected to the same disk array.

13. What is the difference between the FSLDM and Starschema in teradata?

The FSLDM refers to the Financial services Logical Data Model. This is designed to model the solution for the financial management sectors like banks efficiently. The starchema on the other hand is a general implementation of the relational model for the database, where the centralized data storage is mapped into dimensions with many other tables to represent the relationships.

14. In which stage of Active Data warehousing do you have the assessments over the future data?

In the prediction stage which is the third stage of the warehouse evolution, we use the data mining tools and the other historic reference information to predict the future changes expected in data. This can be used to have the better idea of the targets and strategic plans.

15. What is OLTP? How does teradata fit into the database requirements?

The OLTP applications usually have the use of very small amounts of data and are based on very quick transactions but many such occurring overall. The online reservation systems are the best examples of such systems. The quick responses and the availability of small data  loads with concurrent transactions makes the teradata suitable for the database requirements.

16. What is the new feature that came along with teradata V2R6?

The Teradata VR6 has the replica feature which allows for the database backup to kept on another system simultaneously.

 

teradata interview questions and answers teradata interview questions