Teradata Interview Questions

Teradata Interview Questions for Intermediate

21. What is Spool space in Teradata? Why is it used?

Spool space is the unused space in the system where intermediate results of a SQL query are held. Users lacking spool space are unable to execute queries. The number of AMPs determines how much spool space is allocated, although each AMP only has access to a portion of the total. The user will be informed that they have run out of spool space if the per AMP limit is exceeded.

22. What do you mean by nodes in Teradata?

The fundamental components of the Teradata System are nodes. Individual servers make up a Teradata system's nodes. Each node has its own Teradata RDBMS software, operating system, CPU, RAM, and disk space.

23. Can you name a few ETL (Exact, Transform, and Load) tools under Teradata?

There are many ETL (Extract, Transform, and Load) tools that are frequently used in Teradata as follows: 

• Informatica,
   
• DataStage, and
   
• SSIS (SQL Server Integration Services).
   

24. Is Teradata an ETL (Exact, Transform, and Load) tool or a database?

Teradata is not an ETL (Extract, Transform, and Load) tool. Teradata is a free and open-source RDBMS (relational database management system) that works with Windows, Linux, and other operating systems. Teradata is an RDMS or relational database management system that can handle data loads up to terabytes. Large-scale data warehouse applications can be run on the system.

25. Can you tell me some benefits of using ETL tools over Teradata?

ETL stands for three separate database management tasks: extract, transform, and load. Compared to Teradata, ETL tools have a few advantages, such as:

• It supports multiple heterogeneous sources and destinations of data. 
   
• The ETL tools offer a complete GUI that makes controlling databases' debugging procedures easier.
   
• ETL tools also allow you to reuse components. As a result, if the central server is updated, all associated applications that use that server are also updated automatically.
   
• ETL tools can pivot and de-pivot (turn rows into columns) (transform columns into rows).
   

26. What is Caching in Teradata?

In Teradata, caching is the process of keeping frequently used Data and Information in the cache memory so that, when the data is needed again, it may be retrieved immediately from memory rather than requiring the application to generate it again. In Teradata, caching always happens in the same order; therefore, changes are not frequent. Many applications frequently share caches.
 

27. What do you mean by Channel Driver?

A channel driver serves as a conduit of communication between PEs and the application programs that operate on channels connected to clients. By acting as a conduit between the Parse Engine and programs connected to network clients, the Teradata Gateway functions similarly to a channel driver.
 

28. What are the different types of tables supported by Teradata?

Following are the types of tables that Teradata supports: 

• Permanent Table: This table holds all of the user-inputted data and keeps it on file forever after it has been entered. Permanent tables allow users and sessions to share the same material. It's the default table.
   
• Volatile Table: When information is added to a volatile table, it is only kept for the duration of the current user session, and the table is immediately deleted once the user session has ended. They are typically employed to hold intermediate data during data transformation.
   
• Global Temporary Table: Global temporary tables are an additional class of permanent tables. For the duration of the entire application, this kind of table maintains the globally used values, and the user's session determines its lifespan. The table is dumped or removed after the session.
   
• Derived Table: Derived tables have the shortest lifetime of all the tables. These tables are where queries' intermediate results are kept after execution. During a query, tables are built, utilized, and then deleted.
   

29. What do you mean by PDE (Parallel Data Extension)?

Parallel Database Extension or PDE is a software layer between the operating system and Teradata Database. It enhances the speed and Scalability of a Teradata Database by providing Parallelism across system nodes. Through PDE, Teradata Database is capable of: 

• Parallel processing, 
   
• Prioritizing and managing Teradata Database workloads, 
   
• Managing memory, I/O (Input/Output), and messaging system interfaces consistently across different OS platforms, etc.
   

30. Can you explain the fallback feature in Teradata?

In Teradata, the fallback feature protects the data of the table. It stores the second copy of the rows of the table on another AMP known as the Fallback AMP. When one AMP fails, the fallback rows are used. Due to the fallback feature, if any of the AMPs fail, data can still be retrieved because a fallback AMP is also available.

31. What are the different ways to improve query performance in Teradata?

Some ways to improve query performance include:

• Using COLLECT STATISTICS to help the optimizer make better decisions.
• Choosing an appropriate Primary Index for even data distribution.
• Using Join Indexes to precompute joins.
• Avoiding Full Table Scans by using Secondary Indexes or WHERE conditions.

32. Explain the use of Teradata View and how it differs from a table.

A View is a virtual table created using a SELECT statement. It does not store data but provides an abstraction layer to simplify complex queries. Unlike a table, a View does not hold physical data.

33. What is the purpose of Collect Statistics in Teradata?

COLLECT STATISTICS helps the optimizer understand data distribution, improving the execution plan and query performance. It reduces the risk of skewed data access and enhances indexing efficiency.

34. How do Teradata locks work, and what are the different types?

Locks prevent conflicts when multiple users access the same data. Types of locks:

• READ Lock: Allows reading but prevents modifications.
• WRITE Lock: Prevents both reading and writing by others.
• EXCLUSIVE Lock: Blocks all other operations.
• ACCESS Lock: Allows concurrent read/write but without strict consistency.

35. Explain how FastLoad handles duplicate records.

FastLoad does not support duplicate records in a MULTISET table because it loads data in two phases without unique checks. To handle duplicates, users must preprocess data or use a different loading utility like MultiLoad.

Teradata Interview Questions for Experienced

36. How can you run Teradata jobs in a UNIX Environment?

If you want to run Teradata utilities in a UNIX Environment, you need to run them in the following order:

BTEQ - $Sh> BTEQ< [ Script path]/TEE< LOGFILE PATH > (OR) BTEQ - $Sh> BTEQ< [ Script path > [LOG FILE PATH]

FLOAD - Sh> FASTLOAD< [ Script path]/TEE< LOGFILE PATH > (OR) FLOAD - $Sh> FASTLOAD < [ Script path > [LOG FILE PATH]

MLOAD - $Sh> Mload < [ Script path]/TEE< LOGFILE PATH > (OR) MLOAD - $Sh> Mload < [ Script path > [LOG FILE PATH]

TPUMP - $Sh> TPUMP < [ Script path]/TEE< LOGFILE PATH > (OR) TPUMP - $Sh> TPUMP < [ Script path > [LOG FILE PATH]

FEXP - $Sh> FEXP < [ Script path]/TEE< LOGFILE PATH > (OR) FEXP - $Sh> FEXP < [ Script path > [LOG FILE PATH]

37. Can you explain the difference between Teradata and Oracle?

38. What will you do if the Fast Script is not running reliably?

If the Fast Load Script is not working reliably and you only have the error tables, then there are two ways to restart: 

• Rerunning the old file: You must be sure not to remove the error tables altogether. Instead, you can fix the errors in the file or script and then execute the file again. 
   
• Running a new file:  You can also restart using the ending loading and beginning statements. Due to this, you can remove the lock that was present on the target table. You can also remove the record from the fast-log table. If this all works fine, you can rerun the whole script. You can also drop the table and try recreating it.
   

39. How can you verify the current version of Teradata?

There are some methods if you want to determine the current version as well as the release of the software. You can check the current Teradata Database version and release level via any client session or any console just by running the following Query: 

SELECT * FROM DBC.DBCInfoV;  

In Teradata, the DBC.DBCINFO table has the information relevant to the Teradata release and version. Here, 

• DBC stands for Database computer.
   
• DBCInfoV is the table where database information is stored.
   

40. Can you name some commonly used BTEQ scripts?

The following are some standard BTEQ scripts:  

• LOGON: It helps you to log in to the Teradata system. 
   
• ERRORCODE: It returns the status code of the last Query performed. 
   
• ACTIVITYCOUNT: It details the number of rows affected by the previous Query performed.  
   
• DATABASE: It sets the default database.  
   
• RUN FILE: This command helps to execute the Query that is present in a file.  
   
• LABEL: It specifies a label for a set of SQL commands.  
   
• GOTO: It turns the control over to a label.  
   
• IMPORT: It specifies the path to the input file and starts the import to the input file. 
   
• LOGOFF: It terminates all sessions and logs you off from the database.  
   
• EXPORT: It specifies a path to the output file and starts exporting to the output file.
   

41. What do you mean by PPI (Partitioned Primary Index)?

Teradata has a very powerful feature known as the Partitioned Primary Index. PPI is a feature that enables users to use a specific part of the table rather than using the complete table. PPI is an indexing system that will enhance query performance. PPI divides data according to range or case as specified in the database, working similarly to how the Primary Index performs when used for data distribution. PPIs (partitioned primary indexes) arrange rows according to the partition number. 

• Instead of running a full table search, PPI can be used to restrict access to necessary partitions.
   
• PPI eliminates the need for secondary indexes and extra I/O maintenance.
   
• It makes a part of a large table comfortably accessible.
   
• PPI makes it simple to update data in a table and add new data.
   

42. How can you find the duplicate records present in a table?

We can use the "DISTINCT" or the "GROUP BY" statement to identify the duplicate records in a table. We can either do this:

SELECT DISTINCT column 1, column 2... FROM tablename;  

Or this:

SELECT column 1, column 2,... FROM tablename GROUP BY column 1, column 2....;

43. What is the function of CASE Expression in Teradata?

A "CASE" expression compares each row with a condition using the "WHEN" clause. It returns the result of the first match. If the condition does not match, it returns the result of the "ELSE" clause.  

Syntax: 

CASE <expression>  
WHEN <expression> THEN result-1  
WHEN <expression> THEN result-2  
ELSE  
  Result-n  
END

44. What is the benefit of using the UPSERT command in Teradata?

In Teradata, you can perform "update" and "insert" operations simultaneously on a table from another table using the "UPSERT" Command. The updates are only done if the update condition in another table matches completely. If the condition does not check, the unmatched rows are inserted into the table. 
 

45. What do you think this Query does?

SELECT HASHMAP (HASHBUCKET(HASHROW(emp_id))), COUNT(*) FROM Employee GROUP BY 1;

The given Query enables us to find the number of rows in each AMP in a specific database. When we use HASHBUCKET, HASHAMP, and HASHROW together, they show the number of rows in the AMPs.

• HASHRAMP: It returns the ID number of the primary AMP corresponding to the specified hash bucket number.
   
• HASHBUCKET: It returns the hash bucket number corresponding to the row hash value that has been specified.
   
• HASHROW: It returns the row hash value in hexadecimal for an expression or multiple expressions in a sequence.

46. What are the benefits and limitations of using Teradata BTEQ?

Benefits:

• Allows both batch and interactive SQL execution.
• Supports error handling and looping.
• Generates formatted reports.

Limitations:

• Not optimized for bulk data loading (unlike FastLoad or MultiLoad).
• Slower for large dataset transfers compared to dedicated utilities.

47. How do Teradata macros help in query optimization?

A Macro is a stored SQL script that allows query execution with parameters. It improves reusability, reduces redundant query writing, and enhances performance by reducing client-server communication.

48. What is the difference between Teradata SQL and standard SQL?

• Teradata SQL includes extensions like HASH functions and system-defined tables.
• Standard SQL follows ANSI rules without database-specific optimizations.

49. Explain the concept of Skew Factor and its impact on performance.

Skew Factor measures the uneven data distribution across AMPs. A high skew factor means some AMPs handle more data than others, causing performance bottlenecks. Optimizing indexes and rebalancing data distribution can reduce skewness.

50. How can you check for space utilization in Teradata tables?

Use the following query to check space utilization:

SELECT DatabaseName, SUM(CurrentPerm) AS UsedSpace FROM DBC.TablesizeV GROUP BY DatabaseName;

This helps monitor storage usage across databases.

Conclusion

Teradata is a powerful database management system known for its scalability, parallel processing, and high performance in handling large datasets. Whether you're a fresher looking to understand the basics, an intermediate-level professional exploring query optimization, or an experienced developer working with advanced features, preparing for Teradata interview questions will help you excel in your career.

By mastering concepts such as Teradata architecture, indexing strategies, data distribution, performance tuning, and utilities like BTEQ, FastLoad, and MultiLoad, you can confidently tackle interviews at top companies.

Recommended Readings:

• Interview questions for freshers
• Kotlin Interview Questions
• WCF Interview Questions
• Html interview questions