Teradata Interview Questions
- 1) Explain Teradata utilities. What is MultiLoad , Fast Load, Tpump ?
- 2) When do you use each of these? Why? Explain in detail.
- 3) Explain Teradata Architecture in detail .
- 4) How do I check access rights in Teradata?
- 5) What is conflict in teradata?
- 6) Explain the Teradata Primary Index Mechanics in detail with a diagram.
- 7) In general, how do you optimze any SQL in Teradata?
- 8) What are the various indexes in Teradata? How to use them? Why are they preferred?
- 9) What are dimensions, facts?
- 10) Explain and compare pros and cons of Snow Flake Schemas, and Start Schemas.
- 11) What is Spool Space? Why do you get spool space errors? How do trouble-shoot them?
- 12) Explain Stored Procedure in detail.. its structure…exact syntax .. in and out?
- 13) What is Macro?
- 14) How do you do Backup and recovery in Teradata?
- 15) What tools would you use for that?
- 16) How do you create tables in Teradata?
- 17) How would you load a very large file in Teradata in general? What utility would you use for it? Why?
- 18) What are non-additive facts?
- 19) What are the different methods ot loading a dimension table? a fact table etc?
- 20) What are aggregate tables? How do you design them using Teradata?
- 21) What is the BTEQ script in Teradata?
Below are the list of Best Teradata Interview Questions and Answers
Teradata utilities offer a powerful solution for managing all your data load requirements from batch to real-time.
FastLoad can sort the incoming rows directly into the target table.
MultiLoad first sorts the incoming rows into a worktable and then applies the worktable to the target table.
TPump is the shortened name for Teradata Parallel Data Pump.
It is used because Teradata utilities offer a powerful solution for managing all your data load requirements from batch to real-time.
Teradata architecture is based on Massively Parallel Processing (MPP) architecture. The major components of Teradata are Parsing Engine, BYNET, and Access Module Processors (AMPs).
You can check access rights in Teradata with the help of given code:
FROM dbc. allrights.
The primary index provides the fastest way to specify where the data resides in Teradata. It is used to specify which AMP gets the data row therefore each table in Teradata is required to have a primary index defined.
You can optimize any SQL in Teradata with the help of the following effective optimization tricks:
- Avoid multiple Joins to the same Table
- Avoid functions in Equi-Join Conditions
- Avoid the usage of UNION
- Avoid GROUP BY over Expressions
- Divide large Teradata SQL queries into smaller ones
- Consider the creation of additional statistics and indexes.
The index is an important aspect of database design used in Teradata.The various types of Indexes used in Teradata are:
No primary index - The absence of a primary index is counted as No primary Index.
Primary indexes - The primary index value is set to determine on which AMPS the rows of a primary‑indexed table are to be stored.
Hash and join indexes - The various join indexes are not necessarily mutually exclusive types.
Secondary indexes - It includes Unique, Nonunique hash‑ordered on all columns with the ALL option, etc.
In Data Warehouse, Fact is a quantitative piece of information stored in fact tables and has a foreign key relationship with a number of dimension tables whereas Dimensions are companions to facts, and describe the objects in a fact table.
Snowflake Schema is easier to maintain and change because of no redundancy and it is good to use for data warehouses with complex relationships. Whereas Star Schema has redundant data and hence difficult to maintain/change therefore it is good for Datamarts with simple relationships only.
Spool space problems are the problems that generally occur either when you have an inefficient query or when statistics have not been properly collected on the tables you are using. This problem can be fixed with the help of the following steps:
- Ensure up-to-date statistics
- Choose a good Primary Index
- Prevent “no more spool space” on system level
- Release Spool Space as soon as possible
Stored procedures differ from ordinary SQL statements and from batches of SQL statements in that they are precompiled. A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
Macro is a set of SQL statements that are stored and executed by calling the Macro name.
You can do Backup by using the Changed Block Backup (CBB) feature and recovery in Teradata with the help of RMAN recovery commands are RESTORE and RECOVER.
The tools will be used as per requirement.
You can create tables in Teradata with the help of given steps:
- Database_Name: Where the table will be created.
- Column_name: Name of the column you want in the table.
- Data_type: Specifies single datatype for a column.
- Attribute: Defines attributes for a column. Example – NOT NULL, NOT CASE SPECIFIC, UPPER CASE, FORMAT etc.
17) How would you load a very large file in Teradata in general? What utility would you use for it? Why?
The 64-bit BTEQ on HPUX (Itanium ia-64) automatically supports LF, therefore, load a very large file in Teradata in general.
Non-additive facts are the opposite of additive facts that means these are the facts that cannot be summed up for any of the dimensions present in the fact table. Some of the examples of non-additive facts are average, discount, ratios, etc.
The different methods of loading a dimension table and fact table are:
- Conventional Load – Before loading the data, all the Table constraints will be checked against the data.
- Direct load Faster Loading– All the Constraints will be disabled. Data will be loaded directly. Later the data will be checked against the table constraints and the bad data won’t be indexed.
The fact table is normally loaded from transaction tables such as order tables or from transactional files, such as web logs.
An aggregate table is those tables which contain the summary of the existing warehouse. It is used to save the time as retrieving data from a million of records in the actual table will take too much time. So, aggregate the tables to a certain level of dimensions and use them.
Teradata supports some aggregate functions which are:
- COUNT: It is used to count the rows.
- SUM: used to calculate the sum of specified columns.
- MIN: To find the minimum value from specified columns.
- MAX: to find the maximum value from specified columns
- AVG: to calculate the average of specified columns.
BTEQ utility or script is a powerful utility in Teradata that can be used in both the batch and interactive mode. It can be also used to run any DDL statement, DML statement, create Macros and stored procedures. BTEQ can be used to import data into Teradata tables from flat file and it can also be used to extract data from tables into files or reports.
Latest Interview Questions
Weblogic Interview Questions
AngularJS Interview Questions
React Js Interview Questions
HTML5 Interview Questions
Firebase Interview Questions
BPO Interview Questions
Cobol Interview Questions
Actuarial Interview Questions
Tally Interview Questions
Hive Interview Questions
Xml Interview Questions
Redis Interview Questions
C Programming Interview Questions
CICS Interview Questions
UFT Interview Questions
AEM Interview Questions
C# Interview Questions
Zend Framework Interview Questions
Mainframe Interview Questions
Jenkins Interview Questions
Cassandra Interview Questions
Oracle Interview Questions
SDLC Interview Questions
Matlab Interview Questions
UX Designer Interview Questions
Swift Interview Questions
8051 Microcontroller Interview Questions
AWS Lambda Interview Questions
Vue.js Interview Questions
Postgresql Interview Questions
Subscribe Our NewsLetter
Never Miss an Articles from us.