Teradata Interview Questions

As the name implies, a channel driver acts as a means of communication between PEs and the applications that run on channels connected to clients. The Teradata Gateway acts in much the same way as a channel driver, acting as a conduit between the Parse Engine and applications connected to network clients. 

The following are reasons why Teradata is important:  

• The system has the capability of handling (storing and processing) large volumes of data, more than 50 petabytes.  
• You can integrate it with various business intelligence (BI) tools.  
• This software supports OLAP (online analytical processing), enabling users to perform complex analytics on data.  
• Teradata offers a comprehensive set of services (full) concerning data warehousing like cloud-based and hardware-based data warehousing, business analytics, etc.  
• SQL (Structured Query Language) is supported by Teradata as a means of interacting with data stored in tables. The application consists of diverse queries that offer flexibility to users. 
• Using the Teradata platform, companies can consolidate their core business objectives by organizing their analytical capabilities. 
• Teradata is based on Massive Parallel Processing (MPP), which makes it possible to run multiple tasks simultaneously and efficiently, resulting in fast processing speeds. 

In simplest terms, caching is a benefit of using Teradata that involves storing frequently used data and information in cache memory so that, when the next time the data is needed, it can be retrieved directly from memory instead of requiring the application to generate it again. In Teradata, caching remains in the same order, which means that it does not change very often. In fact, caches are typically shared among several applications.

Extract, Transform, Load (ETL) means three distinct tasks for managing databases.  ETL tools offer some advantages over Teradata, including: 

• Support for multiple heterogeneous destinations and sources of data.  
• The ETL tools provide a full-featured GUI that simplifies the debugging process for managing databases. 
• ETL tools offer the advantage of being able to reuse components. Thus, if the main server is updated, all corresponding applications connected to the server are automatically updated.  
• ETL tools can be used to pivot (transform rows into columns) and de-pivot (transform columns into rows).

Teradata is not an ETL (Extract, Transform and Load) tool. Teradata is an open-source RDBMS (relational database management system) that runs on different operating systems, including Windows, Unix, and Linux. Teradata is a relational database management system capable of handling data loads in terabytes. The system is capable of handling large-scale data warehouse applications.

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

• DataStage 
• Informatica
• SSIS (SQL Server Integration Services)

The Teradata System consists of nodes, which are the basic units. Nodes are individual servers in a Teradata system. Each node comprises a separate operating system, CPU, memory, Teradata RDBMS software, and disk space.

Spool space refers to the unused space in the system in which intermediate results from a SQL query are stored. Query execution is not possible for users without spool space. Amount of spool space is divided based on the number of AMPs, but each AMP has only a fraction of the space available. In the event that the per AMP limit is exceeded, the user will receive a warning that they have run out of spool space. 

Example: Let's say the user was assigned a spool space of 200000000 bytes. This is the maximum space the user is allowed to use and it is distributed evenly across all AMPs as shown below. 

As a statistical concept, "skewness" refers to the row distribution on AMPs (Access Module Processors). In data distribution, the Skew Factor refers to the distribution of table data among AMPs. Skewed factor of 0 indicate that the data is evenly distributed among the AMP's. In the case of highly skewed data, it means that some AMPs have more rows and some have very few, i.e., the distribution is not even. The Skew Factor is high in this case (unequal distribution of data), affecting performance and Teradata's parallelism.  

Choosing the right index can control the skewness of the data distribution. Ideally, you should choose a Primary Index that contains as many unique values as possible so as to avoid skewness. 

Specifically, Teradata Performance tuning involves identifying all bottlenecks in the database and resolving them. The bottleneck does not cause errors, but it certainly causes delays in data retrieval from the database. A company without performance tuning for its database could suffer from imperfect responses to queries, causing unnecessary difficulties in accessing its data and other issues. 

The reasons for consistent performance tuning are as follows:  

• Increasing the speed of data retrieval: A database that isn't optimized can cause retrieval of data to be slower if you have a lot of data. Performance tuning allows you to build indexes and fix problems that could delay the retrieval of your data.
• Avoiding coding loops: Coding loops (repeating a certain block of code repeatedly until a certain condition is met) can increase the load on your database. SQL queries are executed multiple times if present in a loop, however if you move them out of the loop, performance can be improved since they are executed once instead of many times.
• Optimize SQL query performance: In order to enhance query performance, it is best to avoid correlated subqueries, to avoid overusing select (and instead declare each column individually), and to avoid temporary tables if possible. The purpose of performance tuning is generally to reduce the response time for the end user or the latency.

In general, the process begins from the last known checkpoint, and after the MLOAD script is executed, the server is restarted.

Teradata MultiLoad jobs that failed or were aborted because of client system failure can be restarted depending on whether they stopped during the application phase (apply all DML (Data Manipulation Language) operations). 

• When the Teradata MultiLoad job was stopped prior to or after the application phase, you should restart the job as it is, without making any changes to the script. Terradata MultiLoad uses the entries from the restart log table to determine its stopping point and begins processing at that point. '
• When a Teradata MultiLoad job is aborted or the client system fails during the application stage, resolve the issue associated with failure and then restart the job again. 

Teradata allows all AMP (Access Module Processors) to operate independently. With USI, the index subtable would have to be present on multiple AMPs, which would require communication between AMPs. But with NUSI, the index subtable would be present on the same AMP as the data row, which would allow that AMP to be handled independently. This is why NUSI is supported by multi-load.

The Teradata String functions are used to manipulate strings and are also compatible with the ANSI standard. Additionally, it supports some standard string functions as well as the Teradata extensions to those functions.

• SUBSTRING: It extracts a selective portion of the long string (ANSI standard).

Example:  Consider a string “InterviewBit” from a table.

SELECT SUBSTRING('Interviewbit' FROM 1 FOR 5);

Output: 

Inter

• POSITION: An individual character in a string (ANSI standard) can be located.

Example:

SELECT POSITION("r" IN "InterviewBit");  

Output:

5

• TRIM: Removes (trims) blank space from a specified string.

Example: 

SELECT TRIM("  InterviewBit  ");  

Output:

InterviewBit

• UPPER: The string is converted to uppercase.

Example:

SELECT UPPER("InterviewBit");  

Output:

INTERVIEWBIT

• LOWER: The string is converted to lowercase.

Example:

SELECT LOWER("INTERVIEWBIT");  

Output:

interviewbit

Data can be loaded into Teradata databases as well as exported from Teradata databases to client applications using Teradata utilities. There are many Teradata utilities available, including: 

• Basic Teradata Query (BTEQ): Teradata provides the BTEQ utility, which can be used in batch (executing a collection of statements as a script) or interactive mode (executing statements one by one). You can also use it to execute any DDL (Data Definition Language) or DML statement, macro, or stored procedure. With BTEQ, you can import data into Teradata tables as well as export data to files and reports.
• Fastload: The FastLoad utility loads data into tables quickly. There will be no duplicate rows even if the target table is a Multiset table (stores duplicate records).
• Multiload: MultiLoad is able to load data into multiple tables simultaneously and can perform different types of tasks such as INSERT, UPDATE, DELETE, and UPSERT. It is best suitable for operations such as bulk update, delete,  upsert and as well as complex interface manipulations.
• Fastexport: This utility exports Teradata data into flat files. Alternatively, the utility can export the data as reports. Using joins, the utility can extract data from several tables at one time
• Teradata Parallel Data Pump(TPump): It helps maintain Teradata Databases by updating, deleting, inserting, and upserting data in databases. Multiple changes can be made at the same time.
• Teradata Parallel Transport (TPT): This is an all-in-one tool to load and export data into/from Teradata databases. Among existing utilities like fastload, fastexport, multiload, and TPUMP, Teradata recommends TPT.

The following diagram illustrates Teradata's architecture:

Teradata's architecture is based on MPP (massively parallel processing). In general, it can be divided into two parts i.e., storage architectures and retrieval architectures. In total, the architecture consists of four components, namely, a parsing engine, BYNET, AMPs, and disks. The first two components make up the storage architecture, and the last two are retrieval architecture components. 

• Parsing Engine (PE): The Parsing Engine receives client queries and prepares an efficient execution plan to execute/run SQL queries. As soon as a user executes a SQL query, it is first connected to the PE (Parsing Engine). The PE performs the following functions: 
  • Verifies whether the queries have syntax errors. 
  • Determines whether or not the objects utilized by the SQL query exist. 
  • Prepares execution plans for these queries and then sends them to BYNET. 
  • Get the results of the SQL query from the AMPs and send it to the client.
• Access Module Processors (AMPs): It is a virtual processor that is connected to PE via BYNET. Each AMP contains its own disk allowing it to read and write data from disks.  As such, it is referred to as shared nothing architecture. After receiving the data and execution plan from Parsing Engine, AMPs perform any data type conversion, aggregation, filtering, and sorting, and further write (store) data to the corresponding disks. When the query is fired, all AMPs work together to give back the data. 
• BYNETs: BYNET functions as a communication channel between PEs and AMPs. This component receives the execution plan from the parsing engine and passes it on to the AMPs. Teradata has two BYNETs, named BYNET 0 and BYNET 1, but we refer to them as a single system. Reason for having 2 BYNETs:   
  • The second BYNET can take over if the first BYNET fails.   
  • Both BYNETs can be made functional when data volume is large, which will enhance communication between PEs and AMPs, and therefore speed up the process. 
• Disks: These are Virtual Disks offered by Teradata for each AMP. Vdisk, or Virtual Disk, is the storage area of each AMP. 

Teradata offers the following features:  

• Unlimited Parallelism: Teradata is based on Massive Parallel Processing (MPP), which allows it to divide large tasks (related to data processing) into smaller tasks and run them in parallel.   
• Shared Nothing Architecture: As this database is built on a shared-nothing architecture, the disks, Teradata nodes, and AMPs (Access Module Processors) are all independent and do not share resources with others, resulting in an optimized performance for the given task. 
• Linear Scalability: Teradata Systems are linearly scalable and can handle large volumes of data in the most efficient manner.   
• Connectivity: Teradata is best in terms of connectivity as it can connect with channel-attached systems, such as mainframes or networks. 
• Mature Optimizer: Teradata offers a mature optimizer (which provides the most efficient method for retrieving the data required by a SQL query) that can handle up to 64 joins (combining data from different tables in a database) per SQL query.  
• SQL (Structured Query Language): This standard language is supported by Teradata as a means of interacting with data stored in tables. It also offers its own extensions.  
• Robust Utilities: As part of Teradata's robust utility suite, Teradata provides utilities for importing and exporting data from/to Teradata systems such as Fastload, Fastexport, Multiload, and TPT (Teradata Parallel Transporter), and many more.    
• Load & Unload utilities: Teradata features load & unload utilities, which allow users to move data into and out of the Teradata system. 
• Automatic Distribution: Teradata automatically distributes data evenly among the disks, requiring no manual intervention.  
• Low TCO (Total cost of ownership): Due to its ease of setup, administration, and maintenance, it has a low TCO.  

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

With the above query, you can determine the number of rows in each AMP for a given database table. HASHAMP, HASHBUCKET, and HASHROW indicate the number of rows in the AMPs when used together.

Conclusion 

Among the most popular database management systems are Teradata. Employers are seeking candidates with extensive knowledge of its architecture and ability to use it efficiently. It is important to prepare for Teradata Interview Questions if you are seeking employment related to Teradata. 

It is true that every interview is different depending on the position. In this article, we have provided the important Teradata Interview Questions and Answers for freshers and experienced, which will help you excel in your interview. There are questions on architectural basics, utilities related to Fastload, Multiload, BTEQ, SQL, and Stored Procedures. 

Ready to ace your Teradata interview now? 

Teradata enables update and insert operations to be performed simultaneously on a table from another table using UPSERT Command. Updates are made if the update condition matches in another table and unmatched rows are inserted into the table if the update condition does not match.  

UPDATE-ELSE-INSERT syntax:

UPDATE department SET budget_amount = 60000 WHERE department_number = 600 ELSE INSERT INTO department(department_number, department_name, budget_amount, manager_employee_number) VALUES(600, 'Test Dept', 60000, NULL);

This statement updates the row where department_number matches 600 to set budget_amount to 60000. In the event that no match is found, a new row will be inserted.

When a CASE expression is used, each row is compared with a condition or WHEN clause, and the result of the first match is returned. Else, the result from the ELSE clause will be returned if there are no matches.

Syntax: 

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

Operators such as SET combine the results from multiple SELECT statements. It may seem similar to Joins, but Joins combine columns from different tables, whereas SET operators combine rows from different tables. The following set operators are supported by Teradata SQL: 

• UNION: It combines results from several SELECT statements. Duplicate values are ignored.
• UNION ALL: It combines results from several SELECT statements, including duplicate rows.
• INTERSECT: It combines results from several SELECT statements. The function can be used to return those rows in the first SELECT statement that match those in the second SELECT statement. Basically, it returns the rows which exist in both SELECT statements.
• MINUS/EXCEPT: It combines results from several SELECT statements. The function can be used to return those rows in the first SELECT statement that are not matched with those in the second SELECT statement.

Example: Assume the student table below is Table1 and the attendance table is Table2.

1. UNION

Following is a UNION query that combines the Roll_No values from both Table1 and Table2.

SELECT Roll_No FROM Table1  
UNION  
SELECT Roll_No FROM Table2;  

Output: Following is the output of the above query when executed. It does not includes duplicate values.

2. UNION ALL

Here is an example of a UNION ALL statement.

SELECT Roll_No FROM Table1  
UNION ALL   
SELECT Roll_No FROM Table2;  

Output: Following is the output of the above query when executed. It includes duplicate values as well.

3. INTERSECT

Here is an example of an INTERSECT statement. This command returns the Roll_No value that exists or present in both tables i.e., Table1 and Table2.

SELECT Roll_No FROM Table1  
INTERSECT   
SELECT Roll_No FROM Table2;  

Output: Following is the output of the above query when executed. The Roll_No105 is excluded because there is no such record in Table2.

4. MINUS/EXCEPT

Here is an example of a MINUS/EXCEPT statement.

SELECT Roll_No FROM Table1  
MINUS
SELECT Roll_No FROM Table2;

Output: Following is the output of the above query when executed. Only Roll_No105 is included because it is present in Table1 but there is no such record in Table2.

The DISTINCT statement or GROUP BY statement can be used to identify duplicate records in a table. 

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

OR  

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

Example: Consider the following student table.

DISTINCT

Here is an example of a DISTINCT statement.

SELECT DISTINCT First_Name FROM tablename;  

Output: Following is the output of the above query when executed. With a distinct statement, duplicate values can be eliminated.

As the name implies, the fallback feature stores the second copy of rows from a table on a different AMP, which is called the Fallback AMP. In the event that one of the AMPs fails, the fallback rows are accessed. As a result, even if one of the AMPs fails, data can still be accessed since a fallback AMP is available.  

In the below diagram, you can see how a duplicate (fallback) copy of each row is stored in another AMPs. In AMP 0, a duplicate copy of the primary row of AMP1 (3) and AMP2 (6) is stored in fallback rows. Similarly, in AMP 1, a duplicate copy of the primary row of AMP0 (1) and AMP2 (5) is stored in the fallback row. All the AMPs stored fallback values in the same way.

Every object in the database is shared between multiple users who access the data simultaneously. If, for example, a user was updating a table and another user tried to view it simultaneously, the second user would receive inaccurate and inconsistent information. Locking mechanisms have been invented to avoid this kind of data inconsistency or data corruption. Having a lock prevents multiple users from changing the same data at the same time, reducing the possibility of data corruption/data inconsistency. In general, Teradata comprises four types of locks as follows: 

• Exclusive: Teradata applies an exclusive lock whenever anyone attempts to modify the structure of any objects ( like a table or view). In other words, Teradata holds the lock on that object and no other user is permitted to access or manipulate that object until the lock is released from the object. 
• Write: Table that is secured with a write lock can only be modified by the lock owner. If other users try to insert, delete, or update a table, a write lock will be applied. With a write lock, other users cannot modify the same table.  
• Read: When a user submits a SELECT query, a read lock is applied.  Multiple users can hold READ locks on an object, during which the system does not permit changes to that object. Hence, data integrity is thus maintained since the data in the tables cannot be altered when read locks are applied.  
• Access: It only prevents exclusive access. ACCESS locks do not restrict access to another user except for when an EXCLUSIVE lock is required to prevent others from accessing. 

As its name suggests, Partitioned Primary Indexes (PPI) are one of Teradata's powerful features that allow users to access a specific portion of a table instead of the whole table. Essentially, PPI is an indexing mechanism that will help improve query performance. When used for data distribution, PPI works the same way as Primary Index, and partitions are created based on range or case as specified in the table. In partitioned primary indexes (PPIs), rows are sorted according to the partition number. 

• By using PPI, you can avoid a full table scan and only access required partitions. 
• Using PPI prevents the use of secondary indexes, as well as additional I/O maintenance. 
• It enables quick access to a subset of a large table. 
• PPI enables removing old data and adding new data to a table with ease. 

Between the operating system and Teradata Database lies a software layer called Parallel Database Extensions (PDE). This enhances the speed and scalability of Teradata Database by supporting parallelism across system nodes. Through PDE, Teradata Database is capable of: 

• Parallel processing 
• Prioritize and manage Teradata Database workloads. 
• Manage memory, I/O (Input/Output), and messaging system interfaces consistently across multiple OS platforms, etc. 

The following are some common BTEQ scripts:    

• LOGON: This allows you to log in to the Teradata system.   
• ACTIVITYCOUNT: It specifies how many rows were affected by the last query performed.   
• ERRORCODE: This returns the status code of the last query performed.  
• DATABASE: This sets the default database.   
• LABEL: It specifies a label for a set of SQL commands.   
• RUN FILE: This command executes the query contained in a file.   
• GOTO: Turns the control over to a label.   
• LOGOFF: This terminates all sessions and logs you off from the database.   
• IMPORT: It specifies the path to the input file and initiates import to the input file.   
• EXPORT: It specifies a path to the output file and initiates export to the output file.

The following types of tables are supported by Teradata:  

• Permanent Table: This table contains all the data inserted by the user and stores the data permanently after it has been entered in the table. The content of permanent tables can be shared between different sessions and users. It is the default table. 
• Volatile Table: When data is added to a volatile table, it is only retained during the current session, and the table is automatically dropped after the user session ends. They are generally used to store data that is intermediate during data transformation. 
• Global Temporary Table: Other types of permanent tables include global temporary tables. This type of table stores the globally used values for the entire application, and its lifetime is dependent on the session of the user. Upon the end of the session, the table is deleted/dropped. 
• Derived Table: Among all the tables, derived tables have the shortest lifetime. During query execution, intermediate results of queries are stored in these tables. Tables are created, used, and then dropped in the course of a query.

For determining the current version and release of software, there are several methods. You can view the current Teradata Database version and release level via any console or client session by running the following query: 

SELECT * FROM DBC.DBCInfoV;  

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

• DBC is a Database computer.
• DBCInfoV is the table where database information is saved.

In the event the Fast Load Script does not work for you, and only the error tables are available to you, then there are two ways to restart: 

• Getting the old file to run again: Be sure not to completely remove the error tables. Instead, fix the errors in the script or file, and then execute it again. 
• Running a new file: Alternatively, you can restart by using the ending loading and beginning statements. As a consequence, the lock that was put on the target table can be removed and the record can also be removed from the fast-log table. If that works, you can run the whole script again. Or, you might drop the table and recreate it.

Among the most popular RDBMS systems are Teradata and Oracle:

• Teradata: Teradata is an open-source RDBMS (relational database management system) that runs on different operating systems, including Windows, Unix, and Linux. The Teradata architecture is based on Massive Parallel Processing (MPP), and is therefore widely used for large-scale data warehouse applications.   
• Oracle: Oracle is a well-known Relational Database Management System (RDBMS), also referred to as Oracle database or OracleDB. OracleDB runs seamlessly on various platforms such as Windows, Unix, Mac, etc. Oracle was the first database designed exclusively for business and enterprise grid computing. As a result of Real Application Clustering and Portability features, Oracle databases are scalable to meet the demands of workloads. Enterprises choose it as a cost-effective solution to their application and data management problems.

In order to run Teradata utilities under UNIX, you simply need to execute them in the following manner:

• 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]