All The Useful Teradata Interview Questions & Answers

Q 1. What is Teradata and why it is used?
Teradata is a vast parallel open relation data management and processing system primarily used to develop large scale warehousing applications. As this is an open system, it can operate on Linux, Unix and Windows server platform. It also provides support to multiple data warehouse operations at the same time to different clients.

Q 2. What are the advantages of Teradata?
Here are a few benefits of using Teradata for your business:

Superbly linearly scalable
Extensive parallel processing
Shared nothing architecture
Improved performance of tables>
Substantially improved compression

Q 3. Explain the Teradata architecture.
Teradata is based on MPP or Massively Parallel Processing architecture. Again, the MPP architecture of Teradata has three core components:

Parsing Engine (PE) or Optimizer
          Parser
         Optimizer
         Dispatcher

Access Module Processor (AMPs)
Messaging Layer (BYNETs)
         Point-to-Point Communication
         Multi-cast Communication
         Broadcast Communication

Q 4. How is the Teradata different from Oracle?

Teradata    Vs   Oracle

1.    Database Management System primarily used for data analytics. - Widely used Relational database management systems
2.    Server operating system: Linux.    - Server operating system: AIX, HP-UX, Linux, OS X, Solaris, windows.
3.    APIs and another access method: .NET Client API, HTTP REST, JMS Adapter, JDBC, ODBC and OLE DB.     - APIs and another access method: ODP.NET, JDBC, ODBC and Oracle Call Interface (OCI)
4.    Horizontal partitioning method used.    - Sharding partitioning method used.

Q 5. What do you mean by Teradata SQL Assistant?
Teradata SQL assistance is an advanced web-based query tool which developers used to store, retrieve and manipulate data from Teradata Database or any other database with an ODBC interface Its Java Edition can be used to attach to Teradata Database or any other database that provides the JDBC interface.

Q 6. How to run a query in Teradata?
To execute the query, press F5 . Type the query text in the Query window. Select a statement in the History window. 3 Click Execute.

Q 7. Explain Teradata vs. redshift.

Teradata    Vs  Redshift

1.    DBMS primarily used for data analytics    - A large scale data warehouse service to be used with business intelligence tools.
2.    Not based cloud    - Cloud-based only
3.    Linux server operating system -    Hosted server operating system
4.    Offers XML support. -    No XML support.
5.    Offers secondary indexes. -    I have restricted secondary indexes.
6.    Has triggers -    No Triggers

Q 8. What do you mean by Teradata IntelliCloud?
Teradata IntelliCloud is an as-a-service offering for analytics at scale. It enables businesses to focus on analytic workloads and data warehousing while relying on Teradata for the setup, maintenance, management and support of the infrastructure and software.

Q 9. What do you mean by Teradata Intelliflex?
Teradata Intelliflex is a platform specially designed for scalable enterprise analytics. It uses an advanced self-service software controls and MPP architecture for scaling of processing data and power capacity independently.

Q 10. What do you mean by TTU in Teradata?
Teradata Tools and Utilities or TTU is a collection of database tools which offer an excellent set of supporting functions to Teradata database.

Q 11. What is BTEQ script in Teradata?
BTEQ or Basic Teradata Query Script is a file present in Teradata which contains BTEQ commands and SQL statements. The BTEQ script is built for command sequences that are going to be executed on more than one occasion, which can be daily, weekly or monthly.

Q 12. What are different table types used in Teradata?
There have four table types supported in Teradata:

Permanent Table: It's the default table and contains data put by the user and stores that data permanently.
Global Temporary Table: Persistent but the data present in the table will be deleted at the end of the user session.
Volatile Table: The data into this table is retained only during the user session.
Derived Table: It holds the intermediate results in a query. The lifetime of this table depends on the query in which they are created, dropped and used.
 

Q 13. What is the primary index in Teradata?
In Teradata, the primary index is an index present on a set of fields which includes the primary key for the field and is guaranteed not to contain any duplicates.

There are two types of primary indexes:
UPI / Unique Primary Index
NUPI /Non-Unique Primary Index

Q 14. Explain the new features of Teradata.
Enlisted new features added to the Teradata:

New string functions added; PIVOT and UNPIVOT
Addition of DATASET datatype
Performance improvement in COUNT DISTINCT
Introduction of Global Space Accounting
Unicode Pass-Through added
Increased PERM and Response Rows (1 MB)
More choice added to Compression Table-Level Option

Q 15. Explain fastload in Teradata.
In Teradata, fastload is a utility which can be used by developers to load a large amount of data in an empty table on a Teradata system. Its unique features include increased speed to load a huge amount of data in tables.

Q 16. What do you mean by fastexport in Teradata?
In Teradata, FastExport is a utility used to export data from Teradata tables into flat files. Teradata - FastExport can also generate the data in report format,

Q 17. What is the use of UPSERT COMMAND In Teradata?
Developers use the UPSERT command inserts rows that do exist. It can be achieved using UPDATE-ELSE-INSERT or MERGE.

Q 18. Explain the most common data types used in Teradata.
Here are the common data types used in Teradata:

Byteint
Smallint
Integer
Bigint
Decimal
Numeric
Float
Date
Varchar
Time
Timestamp

Q 19. What do you mean by Caching in Teradata?
The dictionary cache is a buffer that stores the non-demographic Data Dictionary information most recently used to process SQL queries. Teradata Database caches the most recently used statistics and demographic data in the statistics cache (see Statistics Cache).

Q 20. What is a node in Teradata? Explain.
Node is a term used for the processing unit (General Purpose) which is controlled by a single operating system. Node is known as the basic building block of Teradata System. The Node is the destination where the processing occurs for the database.

Q 21. Explain parsing engine in Teradata.
In Teradata, Parsing Engine is a type of virtual processor for session control, SQL parsing and task dispatching in the multitasking and possibly Teradata Database's parallel-processing environment.

Q 22. Explain vproc in Teradata.
In Teradata, Vproc or Virtual Processor is a simulated processor present in a processing software system, or in a software version of a dedicated physical processor. Each of Vproc uses a portion of the physical processor resource and runs independently of another virtual processor.

Q 23. Explain fallback in Teradata.
In Teradata, Fallback is a feature that offers enhanced data protection beyond that provided by redundant hardware components. Fallback is enabled when a failure occurs, Teradata can continue to perform the update, delete and insert functions because redundant copies of data exist on one or more fallback AMPs.

Q 24. What are Teradata utilities?
Teradata utilities offer developers options to quickly load data into a Teradata database or export data from a database of Teradata to a client application.

Q 25. What do you mean by TPT in Teradata?
TPT or Teradata Parallel Transporter is a new generation and advance load/unload utility provided by Teradata. TPT acts as an integrated ETL suite that helps to extract data from multiple sources, load data in target Teradata database and apply transformation logic.

Q 26. What is the difference between union and union all in Teradata?

Union  Vs    Union All

1.    It removes duplicate records where all columns in the results are the same. -    It doesn't remove duplicate records where all columns in the results are the same.
2.    The performance will be impacted with the use of Union as additional work will be performed to remove duplicate rows. -    The performance will not be impacted.