50 Teradata Interview Questions & Answers
I am sharing below 50 Teradata Interview questions which covers various topics and shall help in preliminary rounds of Interview. It covers basic architecture questions, utilities related – Fastload, Multiload, BTEQ questions, SQL questions, Stored Procedure related questions below.
Q1. Explain Teradata Architecture ?
Ans1. Teradata Architecture majorly consists of Parsing Engine (PE) , BYNET, Access Module Processors (AMP) and VDISK. The parallelism is built in core of Teradata Architecture. read more…
Q2. What is Primary Index in Teradata and how it is different from Primary Key ?
Ans2. Primary Index is used for determining the access path to retrieve or store data into any table in Teradata. Primary key just enforces the uniqueness in the table however it has nothing to do with data distribution. read more…
Q3. How Teradata distributes rows in AMP’s ?
Ans3. Teradata uses ROWHASH on PRIMARY INDEX column to determine which AMP should store what part of the data. read more…
Q4. How to determine the size of table in Teradata?
Ans4. In Teradata, we can use DBC.TABLESIZE view to check the storage space used by any Table. read more…
Q5. How to check SQL history in Teradata?
Ans5. In Teradata we can use DBC.QRYLOG to check SQL history. Also in most of the Teradata prod environment DBA will have a back up database which will store entire history. This database is generally named PDCRINFO. read more…
Q6. How to find the number of AMPs in any system?
Ans6. SELECT HASHAMP()+1 ; [ We have to do +1 because the HASHAMP number starts with 0] read more…
Q7. Which one is better IN or BETWEEN?
Ans7. If we have to search for range of values, it is always advisable to use BETWEEN rather than list of values in IN clause. BETWEEN tells the optimizer that it is range of values we are going to handle rather than individual values. Hence, BETWEEN is much faster than IN. read more…
Q8. What are disadvantages of Secondary Index?
Ans8. Secondary Indexes need more I/O during INSERT operation. Secondary Index slows down the insert/update process because of Sub-table maintenance. Secondary Index requires more Physical Storage.read more…
Q9. What is default session transaction mode in Teradata?
Ans9. Teradata has two session transaction mode : BTET(Default) and ANSI. To change session mode we use following command before establishing session: .SET SESSION TRANSACTION (ANSI/BTET) read more…
Q10. What is MSR (Multi Statement Request) in Teradata?
Ans10. Sometimes we may have to insert data into table by treating several INSERT blocks as one transaction. In that case, we use MSR. It can be implemented by putting a semi colon “;” in front of next line rather than putting it on end of existing line. eg:
INSERT INTO EMPLOYEES(NAME,AGE) VALUES(‘WILL’,26)
;INSERT INTO EMPLOYEES(NAME,AGE) VALUES(‘SMITH’,28);
Q11. Which is better GROUP BY or DISTINCT to remove duplicates present in a table?
Ans11. It depends on the tablesize. If the records are more than half a million then GROUP BY is much better than DISTINCT. However if the records are very less than DISTINCT performance is better than GROUP BY. With latest version of Teradata, this may become insignificant. read more…
Q12.How can you apply access lock on table rather than read lock?
Ans12. We can override default read lock on any table by explicitly mentioning the ACCESS LOCK before using the table. Syntax is:
LOCK TABLE EMPLOYEES FOR ACCESS
SELECT * FROM EMPLOYEES; read more…
Q13. Explain different methods of releasing the FASTLOAD lock on the table ?
Ans13. Execute the FASTLOAD script by removing the DML block , i.e only BEGIN LOADING and END LOADING in the FASTLOAD script should be kept. Another method is to drop the table and create it again. Since FASTLOAD works on empty table, dropping/creating should not result in data loss. read more…
Q14. What does SLEEP command do ?
Ans14. SLEEP command is used to specify the waiting time Teradata will take before re-trying for establishing the connection or session.
Q15. What does Tenacity command do ?
Ans15. Tenacity command is used to specify the total time Teradata will continue trying to establish the connection. After which Teradata won’t make any more attempts for establishing the connection.
Q16. How to skip 5 rows while reading data from file using Teradata Utilities?
Ans16. In BTEQ, we can use SKIP command and other utilities we can use RECORD command to specify from which record, Teradata should start reading data from file. read more…
Q17. How many Join Strategies are available in Teradata?
Ans17. There are five Join Strategies available in Teradata : Merge, Nested, Hash, Product, Exclusion. read more…
Q18. Different levels of Confidence Optimizer may have while creating EXPLAIN Plan?
Ans18. HIGH CONFIDENCE, LOW CONFIDENCE, NO CONFIDENCE.
Q19. Can we load duplicate data using FASTLOAD?
Ans19. No, we cannot load duplicate data via FASTLOAD. FASTLOAD property is to discard the duplicate records. When we restart the FASTLOAD, it send the data again from last checkpoint. In that case, it may send some data again. So FASTLOAD identify such records as duplicate and hence discard it.
Q20. Can we have several Primary Index on a table?
Ans20. No we cannot have multiple Primary Index on a table. However we can create 32 Secondary Indexes on a table. read more…
Q21. How to save a string with single quote (‘) as part of the value?
Ans21. INSERT INTO CUSTOMER(COMMENTS) VALUES(‘I”m Good’);
Q22. How to select 5th row from a table?
Ans22. SELECT * FROM TABLENAME QUALIFY ROW_NUMBER() OVER (ORDER BY COLUMN1) = 5;
Q23. Where is password stored in Teradata?
Ans23. User can view the password in DBC.DBASE view however it will be in encrypted format.
Q24. How to find Teradata Release and Version details?
Ans24. SELECT * FROM DBC.DBCINFO;
Q25. How many phases are in MultiLoad?
Ans25. MultiLoad has 5 phases : Preliminary Phase, DML Transaction Phase, Acquisition Phase, Application Phase and Clean-Up phase. read more…
Q26. How many populated tables are supported by MultiLoad?
Ans26. Teradata can support upto 5 populated tables.
Q27. What are the DML operations possible in MultiLoad?
Ans27. INSERT, UPDATE, DELETE and UPSERTS are possible in Multiload.
Q28. Does Multiload use Transient Journaling ?
Ans28. Multiload does not use Transient Journaling. It uses a LOG table to maintain the process status.
Q29. Can we do selective loading in Multiload?
Ans29. Yes, we can do selective loading in Multiload by specifying condition with the APPLY statement. eg: APPLY (DML LABELNAME) WHERE FIELD_NAME=’some condition’
Q30. How can you specify the charset to be used for MULTILOAD?
Ans30. mload -c [UTF8/ASCII…] scriptname
Q31. While exporting data using BTEQ Export, you are getting junk characters in the exported file. How will you remove these junk characters ?
Ans31. Add a BTEQ setting before your SELECT clause in BTEQ Export:
.SET RECORDMODE OFF;
Q32. While retrieving COUNT(*) in your SQL Query, it is giving numeric overflow error. How will you get the count now ?
Ans32. Try to cast count() to some other bigger datatype in order to avoid numeric overflow error:
SEL CAST(COUNT(*) AS DECIMAL(18,0)) FROM TABLE_NAME;
Q33. Will the count be same if we use COUNT(), COUNT(20), COUNT(COLUMN_NAME) from some table ?
Ans33. COUNT() and COUNT(20) will give same number of records however COUNT(COLUMN_NAME) will return number of records excluding NULL values. So if all the records are NOT NULL , then all three will result in same number of records.
Q34. Why Secondary Index is considered as overhead and leads to more processing ?
Ans34. Secondary Index needs a sub-table to be maintained. Maintaining a sub-table requires processing. Also, if the table is defined as FALLBACK, then sub-table is also FALLBACK protected. Hence, it requires more processing. There is also space used for saving the sub-tables.
Q35. When to use DISTINCT and not GROUP BY in SQL Query ?
Ans35. When the number of records are less then we should use DISTINCT. Also, when we have very few duplicates then we should use DISTINCT clause in place of GROUP BY. However, TD13 onwards, use of GROUP BY is suggested by Teradata and Optimizer will automatically decide whether to use DISTINCT or GROUP BY in SQL Query.
Q36. Name three result code variables in Stored Procedures?
Ans36. SQLSTATE, SQLCODE, ACTIVITY_COUNT.
Q37. What is the default value for any three result code variables?
Ans37. SQLSTATE : ‘00000’, SQLCODE: 0,ACTIVITY_COUNT: 0
Q38. What are the SQL SECURITY Options available in Stored Procedures in Teradata?
Ans38) SQL SECURITY OWNER/CREATOR/DEFINER/INVOKER read more…
Q39. What is the default SQL SECURITY OPTION in Stored Procedures in Teradata?
Ans39. SQL SECURITY DEFINER
Q40. I want to process all the column values in some table using Stored Procedure. How to implement it in Teradata ?
Ans40. We can use REF CURSOR to implement this in Stored Procedure in Teradata.
Q41. Date column is not shown properly in TSA. How to overcome this issue?
Ans41. CAST the date column into varchar, now the date column will be displayed in proper format.
Q42. How can I change NULL and DATE values representation in TSA?
Ans42. Go to TOOLS –> OPTIONS –> DATA FORMAT
Q43. When we get the error in TSA, we can see it in bottom status bar. However how to see last encountered error when we have executed some other successful query?
Ans43. You can press F11 and it will display last error encountered.
Q44. When I am fetching column from table of BIGINT datatype I am seeing junk characters. How to see proper values? I am using old version of TSA.
Ans44. Try using CAST to change the value to VARCHAR and you can see the value now. Alternatively, you can use BTEQ utility to preview correct format values.
Q45. What are the connectivity options available for TSA?
Ans45. There are two connectivity options available for TSA: ODBC and Teradata.net
Q46. How to check all the volatile tables created for the current session ?
Ans46. HELP VOLATILE TABLE; read more…
Q47. How to specify security mechanism option in BTEQ?
Ans47. We can use logmech command to specify security mechanism in BTEQ in Teradata eg: .LOGMECH LDAP;
Q48. What is datatype of keyword NULL ?
Ans48. NULL keyword is of INTEGER datatype.
Q49. ORDER BY clause is not allowed along with SELECT clause in which situation ?
Ans49. In Subqueries
Q50. Can you specify ORDER BY COLUMN_NAME in SET OPERATORS like UNION/MINUS etc ?
Ans50. No. If you need to use ORDER BY , then specify COLUMN ORDER rather than COLUMN NAME.