Here are some common interview questions for Teradata.
What is BTEQ?
- BTEQ is a variant of the core set of ANSI SQL with proprietary ‘extensions’ that are specific to Teradata databases only.
- Similar to PL/SQL for Oracle or T-SQL for Microsoft SQL Server BTEQ includes the ability to perform database-vendor-specific instructions that are particular to Teradata.
What is AMP?
- AMP, acronym for “Access Module Processor,” is the type of vproc used to manage the database, handle file tasks and and manipulate the disk subsystem in the multi-tasking and possibly parallel-processing environment of the Teradata Database.
What is FSLDM?
- Financial Services Logical Data Model (Teradata FS-LDM 7.0) its developed by Teradata for the financial sectors (Speciafially for Bank).
What are SET tables and MULTISET tables in Teradata?
- Set table – Duplicates are not allowed while insertion.
- Multiset table – Duplicates are allowed.
How Teradata makes sure that there are no duplicate rows being inserted when its a SET table?
- Teradata will redirect the new inserted row as per its PI to the target AMP (on the basis of its row hash value), and if it find same row hash value in that AMP (hash synonyms) then it start comparing the whole row, and find out if duplicate.
- If its a duplicate it skips it without throwing any error.
What is the difference between Global temporary tables and Volatile temporary tables?
Global Temporary tables (GTT):
- When they are created, its definition goes into Data Dictionary.
- When materialized data goes in temp space.
- Data is active upto the session ends, and definition will remain there upto its not dropped using Drop table statement.If dropped from some other session then its should be Drop table all;
- Can collect stats on GTT.
Volatile Temporary tables (VTT):
- Table Definition is stored in System cache.
- Data is stored in spool space.
- Data and table definition both are active only upto session ends.
- Stats can not be collected or VTT.
- Can not have default values on column level while creating table.
What is Fastload in Teradata?
As the name suggests Fasload is used to load an empty table in Teradata. Its a two phase approach to load the data. No intermediate tables are required.
- It moves all the records to all the AMPs without any hashing .
- After giving endloading command Amp will hashes the record and send it to the appropriate AMPS .
However it has few prerequisites:
To perform Fload:
- Target table must be empty.
- Performs only Inserts (No updates).
- Only one target table at a time.
- Can not have secondary indexes.
What is Multiload in Teradata?
Teradata MultiLoad is a command-driven parallel load utility for high-volume batch maintenance on multiple tables and views of the Teradata Database. It is specially designed for high-speed batch creation and maintenance of large databases.
- Multiload allows nonunique secondary indexes – automatically rebuilds after loading.
Loading happens in 5 phases in mulitload:
- Gets the import file and checks the script.
- Reads the record from the base table and store in the work table.
- Locks the table header.
- DML opreations will be done in the tables
- Table locks will be released and work tables will be dropped.
How Indexing works in Teradata?
In general indexing is a way to physically reorganise the records to enable some frequently used queries to run faster.
Indexes in Teradata are bit different than other databases.
Primary Index(PI) in Teradata is solely related to the data distribution i.e. decide how evenly the data can be distributed across all AMPs.
Primary Index with Unique attribute or Primary Key (PK) becomes Unique Primary Index(UPI). Uniqueness of the Primary index is ensured by additional index on the same column which is a Unique Secondary Index(USI). Therefore UPI automatically creates USI on the same column.
The Secondary Indexes are the traditional indexes which are basically pointers to the original row.