Teradata Interview Questions And Answers For Freshers $ Experienced. Here Mindmajix shares top real-time Teradata questions. These interview questions are prepared by the Teradata experts and asked during many Teradata interviews. We are sure that these Teradata SQL interview questions will help to crack your next interview. Learn Now!
Below mentioned are the Top Frequently asked Teradata Interview Questions and Answers that will help you to prepare for the Teradata interview. Let's have a look into them.
Frequently Asked Teradata Interview Questions and Answers - [Updated 2020]
Top 89 Teradata Interview Questions And Answers
Teradata Interview Questions And Answers - Basic Level
Q1) Compare Teradata with a basic relational database management system
|Tera Data||Basic RDBMS|
|It has a large number of heterogeneous destinations||They lack 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?
Ans) 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?
Ans) 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 in storing a very large number of requests in a defined manner. The multiple sets of responses can easily be created after this and the users can save time and effort both.
Q4) Name any two common ETL tools that you can use in the Teradata?
Ans) The two common ETL tools are Informatica and DataStage.
Q5) Is it possible to bypass or to send the Teradata jobs in the Unix Environment simply?
Ans) 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 UNIX.
Q6) Why managing the data is important
Ans) Well, data is the real source of deriving useful information and without data, many important tasks such as business management, decision making, problem formulation, and many other tasks that 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 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?
Ans) It is basically an add-on feature in Teradata which simply lets the users share the cache simply with all the applications. This is because it works closely with the source and even lets the users mould the outcomes in the manner they are comfortable with. This approach lets them save time when the data is complex and has so many errors associated with them.
Q8) What according to you are the features of Teradata?
- Teradata can manage a large volume of data without facing any glitch
- There are very limited factors that can impact its important parameters such as performance
- The data can easily be managed even if most of the segments are unstructured
- A lot of useful information about the tasks that can be performed can be derived at the same time when data is managed with Teradata
- Teradata is an approach that has wide scalability and adaptability
- The tasks can be divided among the teams to obtain the true results
- It can help to manage bulk data in the shortest possible time
- Relational database management can be made superior in every aspect through Teradata
Q9) What do you know about a Channel driver in Teradata? How it is different from Gateway?
Ans) While managing the data and handling other tasks related to it, the users have to make sure that there is effective communication between the applications and the PE’s that are considered. For this, a software called 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?
Ans) This can be done with the help of a feature called Trump 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 a 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 something which helps them in this matter ends to 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 the Access Module Processor?
Ans) 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?
Ans) 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?
Ans) In complex applications, there is no such limit on 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 for the same?
Ans) It can be checked easily with the following command
Q16) In Teradata, why the multi-load support NUSI rather than USI?
Ans) 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?
Ans) Parallel Data Extension is actually an interface layer which is responsible for operating the data in a parallel mode. In most cases, the volume of the data really doesn’t matter and the users are free to generate outcomes that 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?
Ans) 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 gets 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 to get the desired outcomes.
- One of the best things about his approach is users are free to simply keep up the pace when it comes to running it on single, and on multi-nodes.
- The users are able to make it act just like a server and without doing much
- There are dedicated features of maintaining parallel processing in this technology
- When it comes to working with ANSI, it is largely compatible and the users are free to get the results
- It is actually a useful approach that can easily be clubbed with a different database management system for data migration
Q20) Tell something about the Virtual Disk?
Ans) 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 an emergency, the loss to the data is minimum as the same is already divided into 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?
Ans) Yes, the users can perform this task, and generally for this, a process known as parsing is considered. The process simply checks the different types 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?
Ans) 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 shared-nothing architecture.
Q23) Can you tell something about the Parsing engine in Teradata?
Ans) 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 into 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 database management?
Ans) 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 effort 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 sometimes need time.
Q25) What is the PPI in Teradata?
Ans) PPI stands for Partition Primary Index and is basically a feature used for storage purposes. The storage is the middle range and avoids the need for a complete table scan.
Q26) What are the components of Teradata with which you are familiar?
Ans) 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 are widely used and play a 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?
Ans) 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
Ans) 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?
Ans) The failure of the node is one of the major problems in the Teradata. Although the chances are less, it creates several glitches when happens. 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 that are 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?
Ans) It is used for running the queries
Teradata Interview Questions and Answers For Experienced
Q31) What are Journals in Teradata?
Ans) 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?
Ans) 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?
Ans) 1. SET Table: Not allows duplicate records.
2. MULTISET Table: Allows duplicate records.
Q34) Explain the ways to create a table in Teradata?
Ans) Tables can be created using CREATE TABLE statement, CREATE TABLE a statement with column definition, CREATE TABLE from an existing table, CREATE TABLE statement with a SELECT statement.
Q35) Write a statement to find duplicate records in a table?
Ans) We can find duplicate records using DISTINCT or GROUP BY statement.
Finding Duplicate Records Using DISTINCT statement:
SELECT DISTINCT column 1, column 2… FROM tablename; Finding Duplicate Records Using GROUP BY statement: SELECT column 1, column 2,… FROM tablename GROUP BY column 1, column 2….;
Q36) Write a query to identify the number of AMPs in the system?
Ans) By using SELECT HASHAMP() + 1; query we can find the number of AMPs.
Q37) What's the purpose of this below query?
SELECT HASHMAP (HASHBUCKET(HASHROW(primaryindexvalue))), COUNT(*)
Subscribe to our youtube channel to get new updates..!
FROM table-name GROUP BY 1;
Ans) The above query identifies the number of rows in each AMP for a particular database table.
Q38) What is Spool Space Error in Teradata?
Ans) It's one type error in Teradata. Spool Space Error will trigger when intermediate results of the query exceed the AMP spool space limit set for a user.
Q39) Explain the TENACITY command in Teradata?
Ans) 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?
Ans) 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.
Teradata SQL Interview Questions and Answers
Q41) What is FAST EXPORT in Teradata?
Ans) In Teradata FAST EXPORT refers to exports data from Teradata to a Flat file.
Q42) Teradata MultiLoad supports how many populated tables?
Ans) Teradata Multiload supports up to five populated tables.
Q43) What is Transient Journaling in Teradata?
Ans) 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?
Ans) 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 the 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?
Ans) Teradata Database supports system-level, object-level, row-level, and zone-level privileges.
- System-level privileges
- Object-level privileges
Row or column
Other object types
- Row-level privileges
- Zone-level privilege
Get FREE Teradata Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!
Q46) Explain GIVE statement in Teradata?
Ans) 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.
Syntax-1: GIVE database_name TO recipient_name
Syntax-2: GIVE user_name TO recipient_name
Q47) Explain the GRANT statement in Teradata?
Ans) GRANT establishes explicit privileges for one or more users, proxy users, databases, or roles.
Q48) What are the different GRANT forms available in Teradata?
Ans) 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 off, 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)?
Ans) 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:
GRANT ALL PRIVILEGES ON object TO user WITH GRANT OPTION; GRANT MONITOR PRIVILEGES TO user WITH GRANT OPTION;
Q50) Explain the REVOKE statement in Teradata?
Ans) 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.
Teradata SQL Interview Questions And Answers For Experienced
Q51) What is the purpose of the SHOW statement?
Ans) 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 ALTER database_objectstatement.
Q52) What are Hot Standby Nodes (HSN) in Teradata?
Ans) Hot Standby Node (HSN) is a node that is a member of a clique that is not configured (initially) to execute any Teradata process.
Q53) Explain about Volatile Tables in Teradata?
Ans) 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?
Ans) 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?
Ans) 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 rollback transactions during a recovery operation.
Q56) Explain the MERGEBLOCKRATIO in Teradata?
Ans) 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?
Ans) 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 BLOCK COMPRESSION?
Ans) 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?
Ans) Situations sometimes occur where the identification and choice of a simple primary key are 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?
- UNIQUE and PRIMARY KEY constraints can only be defined on a column set that is also constrained to be NOT NULL.
- To create a composite, or multicolumn, the primary key for a table, you must specify the PRIMARY KEY constraint at the table level, not the column level.
- Both UNIQUE and PRIMARY KEY constraints can be defined on a UDT column.
- Teradata Database also supports the related constraints UNIQUE INDEX and UNIQUE PRIMARY INDEX.
Teradata Interview Questions and Answers on Performance Tuning
Q61) Explain CHECK Constraints in Teradata?
Ans) 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?
Ans) 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?
Ans) 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?
Ans) You can define the primary index for a table using either a PRIMARY KEY or 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 an 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?
Ans) 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?
Ans) 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)
where QITS_column_name indicates the name you specify for the QITS column.
Q67) How REPLACE TRANSFORM Differs From CREATE TRANSFORM in Teradata?
Ans) 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?
Ans) 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?
Ans) 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?
Ans) 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?
Ans) In Teradata, the FAST LOAD option loads a huge amount of data from a flat-file into EMPTY tables.
Q72) Can you share a few advantages of Teradata?
Ans) There are many advantages of using the 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 huge warehouse data than all its competitors.
2. It's Scalable: In the 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 Architecture: Teradata runs on Parallel Architecture 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 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 the 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?
Ans) We can use the 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?
Ans) 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?
Ans) 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?
Ans) 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?
Ans) The SQL syntax to create a Unique Primary Index is:
CREATE TABLE sample_1 (col_a INT ,col_b INT ,col_c INT) UNIQUE PRIMARY INDEX (col_b);
Q79) Explain the Non-Unique Primary Index (NUPI) in Teradata?
Ans) 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?
Ans) The SQL syntax to create a Non-Unique Primary Index is:
CREATE TABLE sample_2 (col_x INT ,col_y INT ,col_z INT) PRIMARY INDEX (col_x);
Teradata Developer Interview Questions and Answers
Q81) Explain about Columnar in Teradata?
Ans) 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?
Ans) 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;
Q83) What are Locks in Teradata?
Ans) In Teradata locking prevents multiple users from trying to access the same data simultaneously.
Q84) What are the Levels of Locking in Teradata?
Ans) Locks can be applied at three levels: Database Locks, Table Locks, Row Hash Locks.
Q85) What are the types of Locks in Teradata?
Ans) 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.
Q86) What are the Data Protection features available in Teradata?
Ans) 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?
Ans) 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?
Ans) A 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?
Ans) 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.