Last Updated: July 2, 2018
Q1) Compare TeraData with a basic relational database management system
|Tera Data||Basic RDBMS|
|It has large number of heterogeneous destinations||They lacks with the same|
|Source operation is allowed||It is not necessary always|
|Components can be used again and again without any limit||Component reusability is limited|
|It is easy to debug the TeraData||Debugging is difficult|
Q2) What do you know about TeraData?
It is basically a relational database management system which is known due to some of its vast and useful features that are best enough to be considered for a number of tasks which are related to the data. It is considered when it comes to driving the datamart, OLAP and data warehouse applications of a business. It is best enough to b considered when it comes to managing the parallel processing of the data simply in no time.
Q3) What is the true purpose of Dispatcher in Teradata according to you?
Well, it is capable to be considered for performing multiple tasks. However, it is widely considered as best for taking and storing the requests in a queue. It can help storing a very large number of requests in a defined manner. The multiple set of responses can easily be created after this and the users can save time and efforts both.
Q4) Name any two common ETL tools that you can use in the Teradata?
These are Informatica and DataStage.
Q5) Is it possible to bypass or to send the TeraData jobs in the Unix Environment simply?
Yes, the same is possible and the users are free to do the same provided some basic conditions are fulfilled by them. This can be done by performing execution in the UNIX.
Q6) Why managing the data is important
Well, data is the real source of deriving useful information and without data many important tasks such as business management, decision taking, problem formulation and many other tasks which are useful cannot be accomplished easily. When the same is not managed, there are strong chances that the user will get the errors in all of the above tasks. A well-managed data always enable users to save time and to locate the things easily. There are a lot of other reasons as well due to which data management is important.
Q7) What exactly do you know about Catching in TeraData?
It is basically an add-on feature in Teradata which simply let the users to share the cache simply with all the applications. This is because it works closely with the source and even let the users mold the outcomes in the manner they are comfortable with. This approach let them save time when the data is complex and have so many errors associated with them.
Q8) What according to you are the features of TeraData?
Q9) What do you know about a Channel driver in TeraData? How it is different from Gateway?
While managing the data and handling other tasks related to it, the users have to make sure that there is an effective communication between the applications and the PE’s that are considered. For this, a software called as Channel Driver is used. Generally, it acts as a direct link between them and enables clients to share information easily.
Gateways are quite similar to that of Channel driver but the only difference is they enable the communication of PE and applications in case of network clients or for the clients which are attached to the network.
Q10) Name an approach that you can apply easily when it comes to controlling the traffic in TeraData?
This can be done with the help of a feature called as Tpump which is actually a dedicated one for controlling and monitoring the traffic.
Q11) How you will restart the system in case the Fast Load Script fails to operate in the reliable manner?
This can be done with the help of two useful methods. The users can go ahead with running the old file without dropping the error table completely. Rather than rectifying the errors, they can execute the command again and again. The other method that can enable them to simply keep up the pace is running a new file. Basically the script is executed in this process and the something which helps them in this matter are end load and beginning table. This generally removes all the barriers from the tables and the entire script can be run again easily.
Q12) While working with the TeraData, what are the important functions that you have performed with the help of Access Module Processor?
Access Module Processor is actually loaded with the Database Manager subsystems. IT is because of no other reason the tasks that can easily be performed through it are performing DDL, DML, implementation of Joins and Aggregations, applying and as well as releasing of the locks.
Q13) What is the easiest method to generate the sequence during the display time in Teradata?
This can be done with the help of a sub feature known as CSUM.
It is not always necessary that these components are applied at the same time. Depending on the task, a specific or all of them can be used for getting the outcomes.
Q14) What is the limit of assigning the gateway to the node in the Teradata?
In complex applications, there is no such limit how, one Gateway is assigned per node during the general use.
Q15) How you will check the version of TeraData? Is there any command in the Teradata to for same?
It can be checked easily with the following command
Q16) In TeraData, why the multi-load support NUSI rather than USI?
In TeraData, all the AMP are allowed to operate separately and they cannot be clubbed with USI especially when multi operations are executed at the same time. NUSI is a better approach that can easily be clubbed and a lot of time can be saved.
Q17) Tell something about the Parallel Data Extension in TeraData?
Parallel Data Extension is actually an interface layer which is responsible for operating the data in a parallel mode. In most of the cases, the volume of the data really doesn’t matter and the users are free to generate outcomes which are simply the best. This layer is present above the Operation system layer.
Q18) How can data be saved in case of a node failure?
While using the Teradata, the users are free to take advantage of a supreme feature for this. All the Vprocs are set to automatically move to the parallel nodes from the one that get fails and thus, the data can easily be recovered without making complex efforts.
Q19) What are the characteristics of TeraData which you are familiar with and how do you think they can help getting the desired outcomes.
Q20) Tell something about the Virtual Disk?
It is actually a general compilation of a large group of cylinders which are nothing but disks. The users also call it the Disk Array. It is done to assure that in case of any emergency, the loss to the data is minimum as the same is already divided in the segments. It is possible to create backups and the users are therefore free to derive the outcomes accordingly.
Q21) Is it possible in Teradata to analyze the symbols of string present at different locations?
Yes, the users can perform this task and generally for this, a process known as parsing is considered. The process simply check the different type of errors such as syntactical and, semantics. In addition to this, it can also be considered for the purpose of checking whether the object exists actually or not.
Q22) In TeraData, what exactly is Access Module Processor?
Access Module Processor is a virtual processor that is responsible for managing and controlling a single segment of a database. It is not possible by any other Access Module Processor to share the same. Thus, it is also called as a secure or a share-nothing architecture.
Q23) Can you tell something about the Parsing engine in TeraData?
It is basically an approach that is responsible for enabling the users to handle all the SQL requests and then provide responses in the SQL. There are several software components present in it and the users are free to split the SQL the SQL in different sections. These sections are then transferred to the AMP in a defined or in a random manner.
Q24) How data management is different from that of a database management?
Data management simply means managing a volume of data that is generally measurable or when the data size is small. It doesn’t take a lot of efforts even if the data is unstructured. The data is mostly a part of any large group in this approach. On the other side, database management is a bit different from that of data management although they both seem similar. In this, the volume of data is high and data can have multiple sources and types which are stored at a specific location. Managing the entire database is a bit difficult and sometime need times.
Q25) What is the PPI in TeraData?
PPI stands for Partition Primary Index and is basically a feature used for storage purpose. The storage is middle range and avoids the need of complete table scan.
Q26) What are the components of TeraData with which you are familiar?
The TeraData has many useful components and the users are always free to get the desired outcomes in the shortest possible time. A few of them which are widely used and play significant role in making the tasks simple are spotlighted below.
1. Passing Engine
2. Virtual Disk
3. Access Module Processor
5. Virtual Storage System
6. Meta Manager
Q27) Name the important locks in Teradata which you have already accessed?
These are Read Lock, Write Lock, Exclusive Lock, Maintain Lock and Access Lock
Q28) Name any unique feature which you find only in TeraData and not in any other Relational
There is a command known as “Update else Insert” (USERT) which is available only in TeraData.
Q29) In TeraData, what according to you is the biggest issue and how it can be avoided simply?
The failure of node is one of the major problems in the Teradata. Although the chances are less, it creates several glitches when happen. There is a dedicated feature in Teradata to control this situation and i.e. creating a Clique. The same is actually an assortment of nodes which is shared between the drives which are common. When this approach is considered, the failure of one node doesn’t make much impact.
Q30) When you will make use of Spool space in TeraData?
It is used for running the queries
Q31) What are Journals in Teradata?
In Teradata, Journals are placed on the system to provide data availability in the absence of process failure. There are two types of journals are available they are Recovery Journals and Permanent Journals.
Q32) How many types of BTEQ Exports are there in Teradata?
Teradata BTEQ Exports are four types, they are: Export DATA, Export INDICDATA, Export REPORT, Export DIF.
Q33) Explain diff b/w SET and MULTISET tables in Teradata?
1. SET Table: Not allows duplicate records.
2. MULTISET Table: Allows duplicate records.
Q34) Explain the ways to create a table in Teradata?
Tables can be created using
CREATE TABLE statement,
CREATE TABLE statement with column definition,
CREATE TABLE from an existing table,
CREATE TABLE statement with a
Q35) Write a statement to find duplicate records in a table?
We can find duplicate records using DISTINCT or GROUP BY statement.
Finding Duplicate Records Using DISTINCT statement:
Q36) Write a query to identify the number of AMPs in the system?
SELECT HASHAMP() + 1; query we can find number of AMPs.
Q37) What's the purpose of this below query?
SELECT HASHMAP (HASHBUCKET(HASHROW(primaryindexvalue))), COUNT(*)
FROM tablename GROUP BY 1;
The above query identifies the number of rows in each AMP for a perticular database table.
Q38) What is Spool Space Error in Teradata?
It's one type error in Teradata. Spool Space Error will trigger when intermediate results of the query exceeds AMP spool space limit set for a user.
Q39) Explain TENACITY command in Teradata?
TENACITY command in Teradata specifies the total waiting time to establish a new connection.
Q40) Explain the diff b/w NUSI and Full table scan?
In Teradata, NUSI and FTS will access all the AMPs, but the difference is,
1. FTS will access all the blocks within the AMP.
2. Whereas NUSI will access the blocks only if the sub-table contains the qualifying rows.
Q41) What is FAST EXPORT in Teradata?
In Teradata FAST EXPORT refers to exports data from Teradata to a Flat file.
Q42) Teradata MultiLoad supports how many populated tables?
Teradata Multiload supports upto five populated tables.
Q43) What is Transient Journaling in Teradata?
The transient journal is a system-maintained dictionary table that provides a way to protect transactions from various system failures and from deadlock (see SQL Request and Transaction Processing for a description of deadlock). Each transaction processed by Teradata Database records a before change image of rows that are touched by the transaction.
Then if a transaction fails for some reason, the before change image of any modified rows can be retrieved from the transient journal and written over the modifications, returning the row to the same state it was in before the transaction occurred. Teradata Database removes all before change images from the transient journal after a transaction commits.
Q44) What are Database Privileges in Teradata?
A database privilege is a permission to access or to manipulate a database object or data. Specific privileges are required for nearly everything that can be done in Teradata Database.
Teradata Database privileges are used by administrators to control access to database objects and data, and to control the types of actions and activities available to users.
The privileges are used to control which users can:
1. Access, create, modify, or delete specific database objects and data
2. Execute specific macros, stored procedures, and UDFs
3. Monitor system-wide activity
4. Grant privileges to other users
Q45) What are the Privilege Levels available in Teradata?
Teradata Database supports system-level, object-level, row-level, and zone-level privileges.
• System-level privileges
• Object-level privileges
• Row-level privileges
• Zone-level privileges
Q46) Explain GIVE statement in Teradata?
GIVE statement transfers ownership of a database or user space to another user. Also transfers all databases and users owned by the transferred database or user.
GIVE database_name TO
GIVE user_name TO
Q47) Explain GRANT statement in Teradata?
GRANT establishes explicit privileges for one or more users, proxy users, databases, or roles.
Q48) What are the different GRANT forms available in Teradata?
In Teradata there are different GRANT forms are there, they are:
1. GRANT (Monitor Form) - Performance monitoring of Teradata Database.
2. GRANT (Role Form) - Granting role membership to users and other roles.
3. GRANT (SQL Form) - Granting access to, creation of, or logging of, various Teradata database objects.
4. GRANT ZONE - Granting zone guest status to users or roles that do not belong to any zone. GRANT ZONE does not automatically grant users access to database objects within the zone. Zone users must grant privileges to zone guests before access is permitted.
5. GRANT CONNECT THROUGH - Granting the ability to connect as a proxy permanent or proxy application user through a trusted user.
6. GRANT LOGON - Granting system logon privileges.
Q49) How do you use GRANT (SQL Form) and GRANT (MONITOR Form)?
The GRANT (SQL Form) controls access to, and manipulation of, database objects, while the GRANT (MONITOR form) privilege set relates to monitoring system-wide performance. To grant a user all privileges, including MONITOR, you must perform both of the following requests:
Q50) Explain about REVOKE statement in Teradata?
REVOKE rescinds explicit privileges from one or more users, proxy users, databases, or roles. The privileges might have been conferred either automatically or by a previous GRANT statement.
Q51) What is the purpose of SHOW statement?
SHOW statements return the result of the last data definition statement performed against the named database object in the form of a
CREATE database_object or
Q52) What are Hot Standby Nodes (HSN) in Teradata?
Hot Standby Node (HSN) is a node that is a member of a clique that is not configured (initially) to execute any Teradata vprocs.
Q53) Explain about Volatile Tables in Teradata?
The primary index for a volatile table can be nonpartitioned or row-partitioned. The table can also be defined without a primary index (NoPI).
The following options are not permitted for volatile tables.
1. Referential integrity constraints
2. CHECK constraints
3. Permanent journaling
4. DEFAULT clause
5. TITLE clause
6. Named indexes
7. Column partitioning
8. Primary AMP index
Q54) Whatis LOG and NO LOG in Teradata?
Global temporary and volatile tables permit you to define whether their activity is logged to the transient journal. While the NO LOG option reduces the system overhead of logging, it is also true that table modifications are lost and cannot be recovered upon an aborted SQL request.
Q55) Explain about Permanent Journaling?
The permanent journal is a user-specified table that can be used to capture both before images and after images of Teradata Database transactions. Journal entries in this table can be used by the Archive/Recovery utility to roll forward or roll back transactions during a recovery operation.
Q56) Explain the MERGEBLOCKRATIO in Teradata?
The MERGEBLOCKRATIO option provides a way to combine existing small data blocks into a single larger data block during full table modification operations for permanent tables and permanent journal tables. This option is not available for volatile and global temporary files. The file system uses the merge block ratio that you specify to reduce the number of data blocks within a table that would otherwise consist mainly of small data blocks.
Q57) Explain the DATABLOCKSIZE in Teradata?
DATABLOCKSIZE sets the maximum data block size for blocks that contain multiple rows. The data block is the physical I/O unit for the Teradata file system.
Larger block sizes enhance full table scan operations by selecting more rows in a single I/O. Smaller block sizes are best for transaction-oriented tables to minimize overhead by retrieving only what is needed.
Q58) Explain the BLOCKCOMPRESSION?
Use this option to set the temperature-based block compression state of a table.
Teradata Virtual Storage tracks data temperatures at the level of cylinders, not tables, and the file system obtains its temperature information from Teradata Virtual Storage, so it also handles temperature-related compression at cylinder level.
Q59) Explain Surrogate Keys in Teradata?
Situations sometimes occur where the identification and choice of a simple primary key is difficult, if not impossible. There might be no single column that uniquely identifies the rows of a table or there might be performance considerations that argue against using a composite key. In these situations, surrogate keys are an ideal solution.
A surrogate key is an artificial simple key used to identify individual rows uniquely when there is no natural key or when the situation demands a simple key, but no natural non-composite key exists. Surrogate keys do not identify individual rows in a meaningful way: they are simply an arbitrary method to distinguish between them.
Q60) Difference between PRIMARY KEY Constraints Versus UNIQUE Constraints in Teradata?
Q61) Explain CHECK Constraints in Teradata?
CHECK constraints are the most general type of SQL constraint specification. Depending on its position in the CREATE TABLE or ALTER TABLE SQL text, a CHECK constraint can apply either to an individual column or to an entire table.
Q62) Explain Referential Constraints in Teradata?
In some circumstances, the Optimizer is able to create significantly better query plans if certain referential relationships have been defined between tables specified in the request. The Referential Constraint feature also referred to as soft referential integrity, permits you to take advantage of these optimizations without incurring the overhead of enforcing the suggested referential constraints.
Q63) What is ADD Option in Teradata?
The ADD option reserves additional partition numbers for a partitioning level to enable adding partitions to a partitioning level at a later time using an ALTER TABLE statement The following rules apply to the ADD clause for a row or column partition.
Q64) Difference between PRIMARY KEY and UNIQUE Constraints Versus Primary Indexes?
You can define the primary index for a table using either a PRIMARY KEY or a UNIQUE constraints as the default primary index in a CREATE TABLE statement.
The following bullets list the rules for defining primary keys and UNIQUE constraints with respect to primary indexes:
1. A table can have at most 1 primary key and need not have a primary index.
2. If a table has a primary index, it can have only 1.
3. You cannot define a primary index and a PRIMARY KEY or UNIQUE constraint on the same column set.
You can still define a relationship for referential integrity by referencing the UPI of a table even if no primary key is defined explicitly for that table because it is always valid to define a referential integrity relationship with any alternate key.
1. If both a primary index and primary key are specified in a CREATE TABLE statement, then the primary index is the hashing index and the primary key is mapped to a unique secondary index by default.
2. If a primary key is specified in a CREATE TABLE statement, but a primary index is not, then the system maps the primary key to a UPI by default.
3. If neither primary index nor primary key is specified in a CREATE TABLE statement, then the system defines the first column that has a UNIQUE constraint as the UPI by default.
4. If there is no PRIMARY INDEX, PRIMARY KEY, or UNIQUE constraint defined in a CREATE TABLE statement, and the PrimaryIndexDefault parameter is set to either D or P, then Teradata Database defines the first index-eligible column defined for the table to be its primary index.
The system defines this index as a NUPI by default except for the case of a single column table defined with the SET (no duplicate rows permitted) option, in which case the system defines it as a UPI.
1. If there is no PRIMARY INDEX, PRIMARY KEY constraint, or UNIQUE constraint defined in a CREATE TABLE statement, and the PrimaryIndexDefault parameter is set to N, then Teradata Database creates the table with no primary index.
2. Columns defined with either of the following constraints cannot be defined to be nullable.
3. Columns defined with any of the following constraints can be defined as nullable:
UNIQUE PRIMARY INDEX
You should declare the column set that constitutes these index types to be NOT NULL unless there is a compelling reason not to.
1. You cannot define a PRIMARY KEY or UNIQUE constraint with the same column set as a secondary index defined on the same table.
2. You cannot define a UNIQUE constraint explicitly on the same columns as a PRIMARY KEY constraint.
Q65) Explain QUEUE Keyword?
Each queue table you define must stipulate the keyword QUEUE as 1 of the CREATE TABLE options following the table name; otherwise, the table you define does not have the properties associated with queue tables and you cannot use consume mode when you select from it (see SQL Data Manipulation Language).
Q66) What is QITS Column in Teradata?
The first column defined for any queue table must be a Queue Insertion Time Stamp (QITS) column. Each queue table has only 1 QITS column, and it must be defined exactly as indicated with the following attributes.
QITS_column_name TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
QITS_column_name indicates the name you specify for the QITS column.
Q67) How REPLACE TRANSFORM Differs From CREATE TRANSFORM in Teradata?
For REPLACE TRANSFORM:
1. If the specified transform group exists, the system replaces it with the new definition.
2. If the specified transform group does not exist and the associated UDT does not have a defined transform group, the system creates the specified transform group.
Q68) What are Transition Tables in Teradata?
A transition table is a dynamically created table that you can reference using a correlation name.
Q69) Why DATABASE Statement is Not Valid In 2PC Mode?
In the 2PC mode, DATABASE is treated as a DDL statement, so it is not valid.
Q70) What are the different types of Teradata utilities?
There are various utilities available in Teradata. They are:
Batch Teradata Query (BTEQ), FAST EXPORT, FAST LOAD, MULTI LOAD, Teradata Parallel Data Pump(TPump), Teradata Parallel Transport (TPT).
Q71) What is FAST LOAD in Teradata?
In Teradata, FAST LOAD option loads a huge amount of data from flat file into EMPTY tables.
Q72) Can you share a few advantages of Teradata?
There are many advantages of using Teradata database, they are:
1. Larger Warehouses
2. It's Scalable
3. Parallel Aware Optimizer
4. Automatic Data Distribution
5. It supports concurrent users
6. Parallel Architecture
7. Supports Ad-Hoc Queries
8. Teradata Manager
9. Teradata Lowest Total Cost
10. Fault Tolerance
1. Larger Warehouses: Teradata supports a huge warehouse data than all its competitors.
2. It's Scalable: In Teradata database, we can store from 100 GB to over 100+ Petabytes of data on a single system. We can scale this huge data without affecting any performance related issues.
3. Parallel Aware Optimizer: Parallel aware optimizer makes query tuning simplifies query running.
4. Automatic Data Distribution: Automatic Data Distribution is a great feature in Teradata, it enhances the performance of applications. It eliminates complex indexing schemes with even data distribution to reduce the time-consuming and reorganizations.
5. It supports Concurrent Users: Teradata can handle the various complex queries often run by users simultaneously.
6. Parallel Archtecture: Teradata runs on Parallel Archtecture to support unconditional parallelism.
7. Supports Ad-Hoc Queries: Teradata built with ad-hoc SQL queries to support a particular purpose. This helps developers in increasing the productivity.
8. Teradata Manager: Teradata Manager is a central place to control the Database.
9. Teradata Lowest Total Cost: Teradata is the only vendor, which provides lowest total cost of ownership.
10. Fault Tolerance: In Teradata fault tolerance is the built-in feature, due to this feature data availability is very high and failure ratio is very very less.
Q73) In how many ways you can use Teradata database?
We can use Teradata database in various ways, they are: As an Enterprise data warehousing, Active data warehousing, CRM, EBusiness and Data marts.
Q74) What are the main components in Teradata Architecture?
The main components of Teradata Architecture are:
Parsing Engine(PE), Access Module Processors(AMPs), BYNETs and Disks.
Q75) What is BYNET and What is the purpose of it?
BYNET is the main component of Teradata architecture and it acts as a message-passing layer. BYNET decides which AMP should receive a message.
Q76) Explain about Unique Primary Index(UPI) in Teradata?
A Unique Primary Index (UPI) is unique and cannot have any duplicates. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. A UPI enforces UNIQUENESS for a column.
Q78) How do you create a Unique Primary Index in Teradata?
The SQL syntax to create a Unique Primary Index is:
Q79) Explain about Non-Unique Primary Index (NUPI) in Teradata?
A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist.
Q80) How do you create a Non-Unique Primary Index in Teradata?
The SQL syntax to create a Non-Unique Primary Index is:
Q81) Explain about Columnar in Teradata?
The main purpose of a Columnar in a Teradata table is to spread the rows evenly across the AMPs.
Q82) Can you write a Columnar example code?
Columnar example code:
Q83) What are Locks in Teradata?
In Teradata locking prevents multiple users trying to access same data simultaneously.
Q84) What are the Levels of Locking in Teradata?
Locks can be applied at three levels: Database Locks, Table Locks, Row Hash Locks.
Q85) What are the types of Locks in Teradata?
Teradata contains four types of Locks, they are:
Q86) 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.
Q87) 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.
Q88) What are Cliques in Teradata?
Clique is a group of nodes that share access to the same disk arrays. Each multi-node system has at least one clique.
Q89) What is Fallback in Teradata?
Fallback is a feature of Teradata Database, Fallback protects data in case of an AMP vproc failure. This protection can be done at the database or table level.
Get Updates on Tech posts, Interview & Certification questions and training schedules