1) What is Teradata and what are it's capabilities?
A) Teradata is a Relational Database Management System (RDBMS), capable of supporting many concurrent users from various client platforms. Teradata is compatible with the ANSI standard and built completely on the parallel architecture.
2) What are the features of Teradata?
A) Teradata Features
Acts as a "database server" to client applications throughout the enterprise
Uses parallelism to manage "terabytes" of data
Capable of supporting many concurrent users from various client platforms (over TCP/IP or IBM channel connections).
3) What are the different types of Teradata utilities?
A) 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).
4) What is BTEQ in Teradata?
A) BTEQ stands for Batch Teradata Query (BTEQ) and it is an query tool to load data and export data off at a time.
5) What is FAST EXPORT in Teradata?
A) In Teradata FAST EXPORT refers to exports data from Teradata to a Flat file.
6) What is FAST LOAD in Teradata?
A) In Teradata, FAST LOAD option loads a huge amount of data from flat file into EMPTY tables.
7) What is MULTI LOAD in Teradata?
A) We can use MULTI LOAD option to load multiple tables at one time from either a LAN or Channel environment.
8) What is TPump in Teradata?
A) TPump refers to Teradata Parallel Data Pump(TPump) and it loads data one row at a time, using row hash locks.
9) What is TPT in Teradata?
A) TPT stands for Teradata Parallel Transport (TPT) and it's a combination of BTEQ, FastLoad, MultiLoad, Tpump, and FastExport utilities.
10) Can you share a few advantages of Teradata?
A) There are many advantages of using Teradata database, they are:
Larger Warehouses: Teradata supports a huge warehouse data than all its competitors.
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.
Parallel Aware Optimizer: Parallel aware optimizer makes query tuning simplifies query running.
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.
It supports Concurrent Users: Teradata can handle the various complex queries often run by users simultaneously.
Parallel Archtecture: Teradata runs on Parallel Archtecture to support unconditional parallelism.
Supports Ad-Hoc Queries: Teradata built with ad-hoc SQL queries to support a particular purpose. This helps developers in increasing the productivity.
Teradata Manager: Teradata Manager is a central place to control the Database.
Teradata Lowest Total Cost: Teradata is the only vendor, which provides lowest total cost of ownership.
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.
Teradata Interview Questions # 11) In how many ways you can use Teradata database?
A) We can use Teradata database in various ways, they are: As an Enterprise data warehousing, Active data warehousing, CRM, EBusiness and Data marts.
Teradata Interview Questions # 12) What are the main components in Teradata Architecture?
A) The main components of Teradata Architecture are:
Parsing Engine(PE), Access Module Processors(AMPs), BYNETs and Disks.
Teradata Interview Questions # 13) What is Parsing Engine (PE) in Teradata?
A) The Parsing Engine (PE) in Teradata is the main component that convert SQL requests, receives input records, and passes data. PE sends the messages through the BYNET to the AMPs.
Teradata Interview Questions # 14) What is BYNET and What is the purpose of it?
A) BYNET is the main component of Teradata architecture and it acts as a message-passing layer. BYNET decides which AMP should receive a message.
Teradata Interview Questions # 15) What is Access Module Processor (AMP) and What is the purpose of it?
A) The Access Module Processor (AMP) is the main component of Teradata architecture and it is a virtual processor (vproc) designed to manage a portion of the entire database. AMP performs sorting, aggregating, and formatting data. It receives data from the PE, formats rows, and distributes them to the disk storage units it controls.
Teradata Interview Questions # 16) What is Disks in Teradata?
A) Disks are the main component of Teradata architecture. In Teradata, disks are disk drives associated with an AMP that store the data rows.
Teradata Interview Questions # 17) Explain about Unique Primary Index(UPI) in Teradata?
A) 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.
Teradata Interview Questions # 18) How do you create a Unique Primary Index in Teradata?
A) The SQL syntax to create a Unique Primary Index is:
CREATE TABLE sample_1
UNIQUE PRIMARY INDEX (col_b);
Teradata Interview Questions # 19) Explain about Non-Unique Primary Index (NUPI) in Teradata?
A) A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist.
Teradata Interview Questions # 20) How do you create a Non-Unique Primary Index in Teradata?
A) The SQL syntax to create a Non-Unique Primary Index is:
CREATE TABLE sample_2
PRIMARY INDEX (col_x);
Teradata Interview Questions # 21) Explain about Columnar in Teradata?
A) The main purpose of a Columnar in a Teradata table is to spread the rows evenly across the AMPs.
Teradata Interview Questions # 22) Can you write a Columnar example code?
A) Columnar example code:
CREATE Table Employee
,Salary Decimal (10,2)
No Primary Index
PARTITION BY COLUMN;
Teradata Interview Questions # 23) What are Locks in Teradata?
A) In Teradata locking prevents multiple users trying to access same data simultaneously.
Teradata Interview Questions # 24) What are the Levels of Locking in Teradata?
A) Locks can be applied at three levels: Database Locks, Table Locks, Row Hash Locks.
Teradata Interview Questions # 25) What are the types of Locks in Teradata?
A) 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.
Teradata Interview Questions # 26) What are the Data Protection features available in Teradata?
A) Teradata offers different types of data protection for databases. They are:
RAID, Cliques, Hot Standby Nodes, Fallback, Journaling, and Locks.
Teradata Interview Questions # 27) What is RAID in Teradata?
A) RAID stands for Redundant Array of Inexpensive Disks. RAID is a storage technology in Teradata that provides data protection at the disk drive level.
Teradata Interview Questions # 28) What are Cliques in Teradata?
A) Clique is a group of nodes that share access to the same disk arrays. Each multi-node system has at least one clique.
Teradata Interview Questions # 29) What are Hot Standby Nodes (HSN) in Teradata?
A) Hot Standby Node (HSN) is a node that is a member of a clique that is not configured (initially) to execute any Teradata vprocs.
Teradata Interview Questions # 30) What is Fallback in Teradata?
A) 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.
31) What are Journals in Teradata?
A) 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.
32) How many types of BTEQ Exports are there in Teradata?
A) Teradata BTEQ Exports are four types, they are: Export DATA, Export INDICDATA, Export REPORT, Export DIF.
33) Explain diff b/w SET and MULTISET tables in Teradata?
A) SET Table: Not allows duplicate records.
MULTISET Table: Allows duplicate records.
34) Explain the ways to create a table in Teradata?
A) 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 SELECT statement.
35) Write a statement to find duplicate records in a table?
A) We can find duplicate records using DISTINCT or GROUP BY statement.
Finding Duplicate Records Using DISTINCT statement:
36) Write a query to identify the number of AMPs in the system?
A) By using
SELECT HASHAMP() + 1; query we can find number of AMPs.
37) What's the purpose of this below query?
SELECT HASHMAP(HASHBUCKET(HASHROW(primaryindexvalue))), COUNT(*)
FROM tablename GROUP BY 1;
A) The above query identifies the number of rows in each AMP for a perticular database table.
38) What is Spool Space Error in Teradata?
A) 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.
39) Explain TENACITY command in Teradata?
A) TENACITY command in Teradata specifies the total waiting time to establish a new connection.
40) Explain the diff b/w NUSI and Full table scan?
A) In Teradata, NUSI and FTS will access all the AMPs, but the difference is,
FTS will access all the blocks within the AMP.
Whereas NUSI will access the blocks only if the sub-table contains the qualifying rows.
41) When should you use primary key or index? Which is better and why?
A) Basically, a primary key is (at the implementation level) a special kind of index. Specifically:
A table can have only one primary key, and with very few exceptions, every table should have one.
A primary key is implicitly UNIQUE - you cannot have more than one row with the same primary key, since its purpose is to uniquely identify rows.
A primary key can never be NULL, so the row(s) it consists of must be NOT NULL
A table can have multiple indexes, and indexes are not necessarily UNIQUE. Indexes exist for two reasons:
To enforce a uniquness constraint (these can be created implicitly when you declare a column UNIQUE)
To improve performance. Comparisons for equality or "greater/smaller than" in WHERE clauses, as well as JOINs, are much faster on columns that have an index. But note that each index decreases update/insert/delete performance, so you should only have them where they're actually needed.
42) Teradata MultiLoad supports how many populated tables?
A) Teradata Multiload supports upto five populated tables.
43) What is Transient Journaling in Teradata?
A) 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.
44) What are Database Privileges in Teradata?
A) 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:
• Access, create, modify, or delete specific database objects and data
• Execute specific macros, stored procedures, and UDFs
• Monitor system-wide activity
• Grant privileges to other users
45) What are the Privilege Levels available in Teradata?
A) 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 privileges
46) Explain GIVE statement in Teradata?
A) 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
47) Explain GRANT statement in Teradata?
A) GRANT establishes explicit privileges for one or more users, proxy users, databases, or roles.
48) What are the different GRANT forms available in Teradata?
A) In Teradata there are different GRANT forms are there, they are:
GRANT (Monitor Form) - Performance monitoring of Teradata Database.
GRANT (Role Form) - Granting role membership to users and other roles.
GRANT (SQL Form) - Granting access to, creation of, or logging of, various Teradata database objects.
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.
GRANT CONNECT THROUGH - Granting the ability to connect as a proxy permanent or proxy application user through a trusted user.
GRANT LOGON - Granting system logon privileges.
49) How do you use GRANT (SQL Form) and GRANT (MONITOR Form)?
A) 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
WITH GRANT OPTION;
GRANT MONITOR PRIVILEGES
WITH GRANT OPTION;
50) Explain about REVOKE statement in Teradata?
A) 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.
51) What is the purpose of SHOW statement?
A) 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.
52) Explain about Global Temporary Tables in Teradata?
A) Global temporary tables have a persistent definition but do not have persistent contents across sessions.
The following list describes characteristics of global temporary tables.
• Space usage is charged to the temporary space of the user.
Note that a minimum of 4KB times the number of AMPs on the system of permanent space is also required to contain the table header for each global temporary table.
• A single session can materialize up to 2,000 global temporary tables at one time.
• You materialize a global temporary table locally by referencing it in a data manipulation request. To materialize a global temporary table, you must have the appropriate privilege on the base global temporary table or on the containing database or user as required by the request that materializes the table.
• Any number of different sessions can materialize the same table definition, but the contents are different depending on the DML requests made against the individual materialized tables during the course of a session.
• You can log global temporary table updates. To do this, specify LOG immediately after the table name in the CREATE TABLE statement. LOG is the default.
• You can save the contents of a materialized global temporary table across transactions. To do this, specify ON COMMIT PRESERVE ROWS as the last keywords in the CREATE TABLE statement. The default is not to preserve table contents after a transaction completes (DELETE).
• The primary index for a global temporary table can be nonpartitioned or row-partitioned. The table can be defined without a primary index.
You cannot create a column-partitioned global temporary table. A global temporary table cannot be defined with a primary AMP index.
The following options are not permitted for global temporary tables.
• Referential integrity constraints
• Permanent journaling
• You cannot create secondary, hash, or join indexes on a global temporary table.
53) Explain about Volatile Tables in Teradata?
A) 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.
• Referential integrity constraints
• CHECK constraints
• Permanent journaling
• DEFAULT clause
• TITLE clause
• Named indexes
• Column partitioning
• Primary AMP index
54) Whatis LOG and NO LOG in Teradata?
A) 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.
55) Explain about Permanent Journaling?
A) 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.
56) Explain the MERGEBLOCKRATIO in Teradata?
A) 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.
57) Explain the DATABLOCKSIZE in Teradata?
A) 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.
58) Explain the BLOCKCOMPRESSION?
A) 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.
59) Explain Surrogate Keys in Teradata?
A) 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.
60) Difference between PRIMARY KEY Constraints Versus UNIQUE Constraints in Teradata?
A) 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, 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.
61) Explain CHECK Constraints in Teradata?
A) 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.
62) Explain Referential Constraints in Teradata?
A) 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.
63) What is ADD Option in Teradata?
A) 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.
64) 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:
• A table can have at most 1 primary key and need not have a primary index.
• If a table has a primary index, it can have only 1.
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• Columns defined with either of the following constraints cannot be defined to be nullable.
? PRIMARY KEY
• Columns defined with any of the following constraints can be defined as nullable:
? PRIMARY INDEX
? UNIQUE PRIMARY INDEX
? UNIQUE INDEX
You should declare the column set that constitutes these index types to be NOT NULL unless there is a compelling reason not to.
• You cannot define a PRIMARY KEY or UNIQUE constraint with the same column set as a secondary index defined on the same table.
• You cannot define a UNIQUE constraint explicitly on the same columns as a PRIMARY KEY constraint.
65) Explain QUEUE Keyword?
A) 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).
66) What is QITS Column in Teradata?
A) 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.
67) How REPLACE TRANSFORM Differs From CREATE TRANSFORM in Teradata?
A) For REPLACE TRANSFORM:
• If the specified transform group exists, the system replaces it with the new definition.
• 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.
68) What are Transition Tables in Teradata?
A) A transition table is a dynamically created table that you can reference using a correlation name.
69) Why DATABASE Statement is Not Valid In 2PC Mode?
A) In the 2PC mode, DATABASE is treated as a DDL statement, so it is not valid.
70) What is SYSUDTLIB Database?
A) SYSUDTLIB is a system database that contains all UDTs, their methods, cast routines, ordering routines, transform routines and any UDFs that are used as cast routines, ordering routines, or transform routines. Because SYSUDTLIB is a system database, it is created by a DIP script. SYSUDTLIB is created as a database, not a user, so you cannot log on as user SYSUDTLIB.
Learn Teradata from experts and boost your career.
Book your FREE seat for Teradata Training webinar.!
Get Updates on Tech posts, Interview & Certification questions and training schedules