Teradata Interview Questions Set 05

81. Explain Columnar in Teradata?

The main purpose of a Columnar in a Teradata table is to spread the rows evenly across the AMPs.

82. Can you write a Columnar example code?

Columnar example code:

CREATE Table Employee

(

  Emp_Id        Integer

 ,Dept_Id       Integer

 ,First_Name    Varchar(20)

 ,Last_Name     Char(20)

 ,Salary        Decimal (10,2)

)

No Primary Index

PARTITION BY COLUMN;

83. What are Locks in Teradata?

Teradata locking prevents multiple users from trying to access the same data simultaneously.

84. What are the Levels of Locking in Teradata?

Locks can be applied at three levels:

• Database Locks
• Table Locks
• Row Hash Locks

85. What are the types of Locks in Teradata?

Teradata contains four types of Locks, they are:

• Exclusive Lock: Applied to databases or tables.
• Write Lock: Enable users to modify data while maintaining data consistency.
• Read Lock: Used to ensure consistency during reading operations.
• Access Lock: Specified by users unconcerned about data consistency. 

86. What are the Data Protection features available in Teradata?

Teradata offers different types of data protection for databases.

 They are:

RAID, Cliques, Hot Standby Nodes, Fallback, Journaling, and Locks.

87. What is RAID in Teradata?

RAID stands for Redundant Array of Inexpensive Disks. RAID is a storage technology in Teradata that provides data protection at the disk drive level.

88. What are Cliques in Teradata?

A clique is a group of nodes that share access to the same disk arrays. Each multi-node system has at least one clique. 

89. What is Fallback in Teradata?

Fallback is a feature of the Teradata Database, Fallback protects data in case of an AMP vproc failure. This protection can be done at the database or table level.

90. What is the importance of using Teradata?

Following are the reasons for using Teradata:

• The system can handle, i.e., storing and processing a large amount of data, even more than 50 petabytes. 
• You can integrate Teradata with various business intelligence (BI) tools.
  
• Teradata supports OLAP or Online Analytical Processing, allowing users to perform complex analytics on their data. 
• Teradata also offers a comprehensive set of services concerning data warehousing, like cloud-based and hardware-based data warehousing, business analytics, etc. 
• Teradata supports SQL or Structured Query Language as a means of interacting with data that is stored in the tables. 

91. What are Teradata’s newest features?

Following are some of the newest features of Teradata:

• Unlimited Parallelism
  
• Linear Scalability, 
• Shared Nothing Architecture, 
• Connectivity, 
• Structured Query Language, 
• Mature Optimizer, 
• Low TCO (Total Cost of Ownership).

92. Why doesn’t Multi-load support a Unique Secondary Index (USI) instead of a Non-Unique Secondary Index)?

All AMP (Access Module Processors) can function independently, thanks to Teradata. With USI, the index subtable would need to be present on several AMPs, requiring AMP-to-AMP communication. However, NUSI would place the index subtable on the same AMP as the data row, allowing that AMP to be handled separately. For this reason, multi-load supports NUSI.

93. How do you restart MLOAD Client System when it fails?

Whether a Teradata Multiloads job halted during the application phase (applying all DML procedures) or was terminated due to a client system failure, the job can be restarted.

• If the Teradata MultiLoad job was interrupted before or after the application phase, you could restart the job precisely as it was without making any modifications to the script. Teradata MultiLoad establishes its stopping point using the entries from the restart log database and starts processing there.
   
• If a Teradata MultiLoad job is stopped or the client system fails during the application stage, you must resolve the issue that led to the failure and then restart the job.
   

94. How do you restart MLOAD Teradata Server after its execution? 

Typically, the process starts from the most recent checkpoint and after executing the MLOAD script. Then the server is restarted.

95. What do you mean by Skewness in Teradata? What is its usage?

In Teradata, “Skewness” is the row distribution on AMPs (Access Module Processors). In data distribution, the Skew Factor is the distribution of table data among AMPs. A skewed factor of 0 denotes an equal data distribution among the AMPs. When data is highly skewed, it indicates that the distribution is not uniform and that some AMPs contain more rows while others have relatively few. This case’s high Skew Factor (unequal data distribution) impacts Teradata’s Parallelism and performance.

96. 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).

97. 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).
   

98. 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.

99. 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.

100. 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: 

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

101. 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.

102. 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.

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

Teradata has a very powerful feature known as 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 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.

104. 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. 

105. 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.