TeraData Interview Questions

If you're looking for Teradata Interview Questions for Experienced or Freshers, you are in the right place. There are a lot of opportunities from many reputed companies in the world. According to research, Teradata has a market share of about 9.8%. So, You still have the opportunity to move ahead in your career in Teradata. Mindmajix offers Advanced Teradata Interview Questions 2023 that helps you in cracking your interview & acquire a dream career as Teradata Developer.

Frequently Asked Teradata Interview Questions

1. What are the different LOCKS in Teradata?

2. How to find duplicates in a table?

3. How do you verify a complicated SQL?

4. Did u ever use UPPER Function?

5. What does a LOWER Function do?

Teradata Interview Questions and Answers

1. What are the different LOCKS in Teradata?

Ans: There are 4 types of LOCKS are there in Teradata, they are:

Read Lock, Access Lock, Exclusive Lock, Write Lock.

If you would like to become a Teradata DBA certified professional, then visit Mindmajix - A Global online training platform: "Teradata DBA Training and Certification Course". This course will help you to achieve excellence in this domain.

2. My table got locked during MLOAD due to a failed job. What do I do to perform other operations on it?

Ans: Using RELEASE MLOAD. It removes access locks from the target tables in Teradata. It must be entered from BTEQ and not from MultiLoad.  To proceed, you can do RELEASE MLOAD.

3. How to find duplicates in a table?

Ans: Group by those fields and select id, count(*) from table group by id having count (*) > 1.

4. How do you verify a complicated SQL?

Ans: I use an explanatory statement to check if the query is doing what I wanted it to do.

5. Did u ever use UPPER Function?

Ans: UPPER Function is used to convert all characters in a column to the same characters in the upper case.

6. What does a LOWER Function do?

Ans: The LOWER function is used to convert all characters in a column to lower case characters.

7. How do you see a DDL for an existing table?

Ans: By using the show table command.

8. Which is more efficient GROUP BY or DISTINCT to find duplicates?

Ans: With more duplicates GROUP BY is more efficient, if only a few duplicates exist DISTINCT is more efficient.

9. Syntax for CASE WHEN statement?

Ans: CASE value_expression_1 WHEN value_expression_n THEN scalar_expression_n END;

10. What’s the difference between TIMESTAMP (0) and TIMESTAMP (6)?

Ans: TIMESTAMP (0) is CHAR (19) and TIMESTAMP (6) is CHAR (26)
Everything is the same except that TIMESTAMP (6) has microseconds too.

11. How do you determine the number of sessions?

Ans:

1. Teradata performance and workload
2. Client platform type, performance, and workload
3. Channel performance for channel attached systems
4. Network topology and performance for network-attached systems.
5. The volume of data to be processed by the application.

12. What is a node? How many nodes and AMPs were used in your previous project?

Ans: Node is a database running in a server. We used 318 nodes and each node has 2 to 4 AMPS.

13. What is a clique?

Ans: a clique is a group of disk arrays physically cabled to a group of nodes.

14. What is the difference between FastLoad and MultiLoad?

Ans: FastLoad uses multiple sessions to quickly load large amounts of data on an empty table. MultiLoad is used for high-volume maintenance on tables and views. It works with non-empty tables also. A maximum of 5 tables can be used in MultiLoad.

Related Article: Teradata Tutorials for Beginners

15. Have you used procedures?

Ans: No. I have not used procedures. But I have expertise in knowledge writing procedures. My company has not encouraged me to write procedures because it becomes a single AMP operation, as such uses a lot of resources and is expensive in terms of resources and time.

16. What is the purpose of indexes?

Ans: An index is a mechanism that can be used by the SQL query optimizer to make table access more performant. Indexes enhance data access by providing a more-or-less direct path to stored data and avoiding the necessity to perform full table scans to locate the small number of rows you typically want to retrieve or update.

17. What are the primary index and secondary index?

Ans: The primary index is the mechanism for assigning a data row to an AMP and a location on the AMP’s disks. Indexes also used to access rows from a table without having to search the entire table.
Secondary indexes enhance set selection by specifying access paths less frequently used than the primary index path.

Secondary indexes are also used to facilitate aggregate operations. If a secondary index covers a query, then the Optimizer determines that it would be less costly to access its rows directly rather than using it to access the base table rows it points to. Sometimes multiple secondary indexes with low individual selectivity can be overlapped and bit-mapped to provide enhanced

18. Why primary and secondary indexes are used?

Ans: Refer answer to the previous question.

19. What are the things to consider while creating a secondary index?

Ans: Creating a secondary index causes Teradata to build a sub-table to contain its index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated. Secondary index sub-tables are also duplicated whenever a table is defined with FALLBACK, so the maintenance overhead is effectively doubled.