Teradata Interview Questions

What is Teradata? Give a brief idea about the key characteristics of Teradata.

Ans: Teradata is a Relational Database management system that allows the users to perform data functionalities. Teradata is for the creation and management of the databases, datamart, data warehouses, etc. Teradata is well known for its parallel processing model that concisely allows the management of data. Many organizations use Teradata as it is faster and flexible than any other database management system. Some of the key characteristics of Teradata are:

• Teradata is compatible to work with the American National Standards Institute (ANSI).
• Teradata uses a parallelism model.
• Server and Teradata work in a similar way.
• It exhibits the running capabilities of multi-nodes with an open framework.

Become a Teradata Certified professional by learning Teradata Training from hkrtrainings! 

Point out the dis-similarities between Teradata and Oracle.

Ans: Both Teradata and Oracle are Relational database management systems that allow the management of databases. Oracle uses the Object-Relational Database management system while Teradata uses Relational database management systems.
Oracle uses an architecture that follows the term "Shared Everything ", whereas Teradata uses non Shared architecture. Teradata uses non-conditional parallelism whereas Oracle uses conditional parallelism. Teradata is termed as linearly scalable.

Mention the newly updated features developed in Teradata?

Ans: The newly developed features in Teradata are listed below:

• Javascript object notation
• XML Data type
• Data compression
• Teradata query grid
• Automated analytics
• Customer associated innovation
• Performance enhancement.

Do you have an idea about the most commonly used BTEQ scripts?

Ans: The most commonly used BTEQ scripts are:

• LOGON: used to login to the Teradata system
• ERROR CODE: returns the status code of the query.
• DATABASE: Sets up the default database.
• LABEL: Assigns a label to the SQL commands
• GOTO: Transfers the control to the label.
• RUN FILE: Executes the query present in the file.
• ACTIVITY COUNT: Returns the number of rows affected by the query.
• IMPORT: Specifies the input file path.
• EXPORT: Specifies the output file path
• LOGOFF: Terminates all the sessions and logs off from the database.

Evaluate the differences between Teradata and RDBMS?

Ans: Teradata includes a large number of different destinations, while RDBMS does not include various destinations. Source operation is allowed in Teradata while RDBMS does not permit. Components can be reused in Teradata for any number of times while in RDBMS, it is limited. Debugging is easy in Teradata while it is complex in RDBMS.

How do you determine the duplicates in the table? How do you check the version of Teradata?

Ans: The duplicates in the table can be found by grouping the fields and select id, count (*) from table group by id having count (*) > 1.

The version of Teradata is determined by using the command ".SHOW VERSION".

Define Views in Teradata with the syntax?

Ans: Views refer to the database objects built using queries on multiple tables or a single table. Data for the view is a dynamic process at the execution time.

Syntax:

CREATE/REPLACE VIEW

AS

Briefly explain about Caching in Teradata?

Ans: Teradata comprises an added advantage called Caching, which specifically works with the source represented in the same order. In simpler terms, frequent changes do not take place. In some scenarios, cache sharing is allowed amongst different applications.

How will you restart the system if the fast load script fails to work, all you have is the error tables available with you?

Ans: There are two different methods to restart the system. The user can start running the old files without dropping the table. Another approach is to run a new file that generates scripts and removes all the conflicts from the tables.

If you want to Explore more about Teradata? then read our updated article - Teradata Tutorial

. Mention some of the ETL Tools that would come under Teradata?

Ans: The most commonly used ETL Tools in Teradata are SSIS, Informatica, DataStage, etc.

Teradata Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project-Based Learning

Explore Curriculum

 

. Explain the terms: PDE, TPD, PPI

Ans:

PDE: PDE(Parallel Data Extension) is an interface layer of software that allows the database to perform the functionalities by operating in a parallel milieu. The parallel data extension layer is present above the operating system.

TPD: TPD( Trusted Parallel Database) works under Parallel Data extension. As TPD works under PDE, Teradata is also known as a Trusted parallel or Pure parallel database.

PPI: PPI (Partition Primary Index) used for category based or range-based data storage operations. Concerning the Range based queries, a Full table scan utilization is not required as it moves to the consequent partition by skipping the other partitions.

. Briefly mention the different forms of locks that come under Teradata.

Ans:  There are four different forms of locks represented under Teradata. They are:

• Read Lock
• Write Lock
• Access Lock
• Exclusive Lock

. Can you explain the purpose of the Dispatcher in Teradata?

Ans: A dispatcher refers to the collection of the requests and stores them in the form of a queue. Dispatcher utilizes the same queue to provide multiple sets of responses. A dispatcher is used to perform various tasks at a time by storing numerous requests in a defined manner.    

Related Article: OLAP functions in Teradata  

. Define Channel driver in Teradata? Explain how the channel driver is different from Gateway?

Ans: Effective communication should be established while handling tasks related to data management between the applications and the PEs. To ensure and develop an interface of communication, Channel driver software is used. It serves as a direct link between applications and PEs.It also enables the sharing of data efficiently.
There is a minute difference between Channel driver and Gateway. Gateways enable communication only for network clients or clients that are attached to the network.

. Define AMP in Teradata?

Ans: AMP (Access Module Processor) is an integral part of Teradata architecture that stores the data on the disks.AMP is used to manage a part of a database or a table. It ensures that all the tasks are accomplished by generating a result set. It provides its extensive support by performing space and lock management.

. List the components of Teradata?

Ans: Teradata has a wide variety of components that provides the desired outputs within a short period. Some of the important elements of Teradata are listed below:

• Parsing engine
• Binet
• Virtual Storage system
• Access Module Processor
• Virtual Disk
• Meta manager

. What is the unique feature that is only available in Teradata?

Ans: Teradata comprises a unique command called "Update else Insert" (USERT), which is only applicable in Teradata.

Related Article: Teradata SQL Assistant

. Elucidate the differences between Fastload and Multiload? Which is faster among them?

Ans: Fastload loads a large amount of data on an empty table by using multiple sessions. Multiload is used for loading and performing functionalities on high volume tables and views. It is capable of working with non-empty tables too. Multiload allows a maximum of 5 tables. Among Fastload and Multiload, Fast load is faster.

. What is the use of FALLBACK?

Ans: Teradata comprises a unique feature called FALLBACK, which allows it to handle AMP failures. It is capable of protecting the data by storing a copy of each row, if VPROC failure occurs.
FALLBACK is transparent, automatic, default-enabled when Teradata is deployed in a system. It is available at both the table level and the database level.

. What are the various types of joins available in Teradata?

Ans: There are various types of Joins available in Teradata. They are listed below:

Inner Join: Joins the records from multiple tables and provides a result of the values in both the tables.

Left Outer Join: Returns all the records in the left table and matching records from the right table.

Right Outer Join: Provides a result that returns all the records in the right table and matching records from the left table.

Full Outer Join: Full Outer join is a combination of left outer join and right outer join. It returns all the values that are matching and non-matching from both the tables.

Self-Join: Self Join compares the column values with the other values in the same column of the table.

Cross Join: Cross Join joins every row of the right table to every row of the left table.

Cartesian Product Join: It is similar to cross join.

Subscribe to our YouTube channel to get new updates..!

Subscribe

 

. What is meant by star schema in Teradata?

Ans: Star schema in Teradata represents the blueprint of multi-dimensional data. It contains at least one measurement and reality tables. The pattern is represented in the form of a star as the entity-relationship between the measurements and reality tables will form a star. A table is associated with many measurements. Star pattern possesses advantages like execution increment, information comprehension.

. What is meant by clique in Teradata?

Ans: A clique is a group or assortment of nodes that are shared among common disk drives. To avoid failure of nodes, the clique is used in Teradata. Each multi-node at least has one clique.

. Explain VPROC in Teradata?

Ans: VPROC refers to a Virtual Processor, an instance of the processor software on the Teradata platform. Each VPROC uses a portion of the processor's physical resource and runs independently.

. Can you explain the procedure of running Teradata jobs in the Unix environment?

Ans: An execution is performed in UNIX represented in the below format.

$Sh > BTEQ < [Script path] TEE [Logfile Path]

Or

$Sh > BTEQ < [Script Path] > [Logfile Path]

. Elucidate the differences between Primary index and primary key?

Ans: Primary Index is the technique that specifies the location of data that exists in the Teradata. Every table consists of a Primary index in Teradata. If there is no primary index specified, Teradata will assign a Primary index itself.

The primary key is the unique value that represents a row in the table. Primary Index is mandatory while the Primary key is optional. The primary key does not allow duplicates or null values, whereas the Primary index allows the same. The primary key comprises a logical mechanism, while the Primary Index utilizes a physical mechanism.

. Define the term: Teradata Gateway?

Ans: Teradata Gateway is used to establish communication between the parsing engine and the network client-based applications. Teradata Gateway is similar to Channel drive that drives an interface between network clients and parsing engine. One Gateway is related to one node.

Related Article: CSUM in Teradata

. Can you figure out the method used to generate a sequence during display time in Teradata?

Ans: Generation of sequence can be accomplished in Teradata using a sub-feature called CSUM. All the components need not be applied at the same time. Instead, they can be used based on the task and requirements.

. Elucidate the differences between UNION ALL and UNION?

Ans: The difference between UNION ALL and UNION is simple. UNION ALL represents the result set values including the duplicates while UNION represents the result set values by ignoring the duplicates

. Briefly describe the set operators in Teradata?

Ans: Set operators are used to combining and returning the results from multiple select statements. Set operators are different from Join operators as Join operators work on combining the columns in various tables. Set operators are specifically used to combine different rows.

There are four different operators defined in Teradata. They are listed below:

• UNION
• UNION ALL
• INTERSECT
• EXCEPT/MINUS

. Give a brief idea about the different types of tables supported by Teradata?

Ans: There are four different types of tables supported by Teradata. They are listed below:

Permanent table: It is a default table used to store the data permanently in a system.

Volatile table: In this type of data table, the data will be stored only during the session. Once the session ends, the table drops along with the data.

Global Temporary table: Global Temporary data also stores the data during a particular session like the volatile table. However, the global temporary table only stores the global values used during the session.

Derived table: Derived tables have the shortest lifetime as they hold the intermediate result values represented during the execution of a query.

. Define a Journal? What are the different types of Journals supported by Teradata?

. What is a database privilege? What are the different types of privileges available in Teradata?

Ans: A database privilege refers to the permissions given to an individual or developer to access and perform any function. To make any changes in the Teradata database, database privileges are required. The different types of privileges necessary to complete any functionality on Teradata database are:

• System-level privileges
• Object-level privileges
• Table
• Row or column
• Database
• Row-level privileges
• Zone level privileges