Teradata DBA Interview Questions

Teradata DBA Interview Questions

Q1) What is the difference between FastLoad and MultiLoad?

Ans: FastLoad uses multiple sessions to quickly load large amounts of data on an empty table. MultiLoad is used for high-volume maintenance on tables and views. It works with non-empty tables also. A maximum of 5 tables can be used in MultiLoad.

Q2) What is the difference between inner join and outer join?

Ans:

• An inner join gets data from both tables where the specified data exists in both tables.
• An outer join gets data from the source table at all times and returns data from the outer joined table ONLY if it matches the criteria.

Q3) What Type Of Indexing Mechanism Do We Need To Use For A Typical Data Warehouse?

Ans: The Primary Index mechanism is the ideal type of index for the data warehouse.

Q4) How Do You Determine The Number Of Sessions?

Ans:

• Teradata performance and workload.
• Client platform type, performance, and workload.
• Channel performance for channel attached systems.
• Network topology and performance for network-attached systems
• The volume of data to be processed by the application.

Q5) What is OLAP?

Ans: Online Analytical Processing, a category of software tools that provides an analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data. For example, it provides a time series and trend analysis views. The chief component of OLAP is the OLAP server, which sits between a client and a database management system (DBMS). The OLAP server understands how data is organized in the database and has special functions analyzing the data.

Inclined to build a profession as Teradata DBA Developer? Then here is the blog post on, explore Teradata DBA Training

Q6) What Are The Things To Be Considered While Creating a Secondary Index?

Ans: Creating a secondary index causes Teradata to build a sub-table to contain its index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated. Secondary index sub-tables are also duplicated whenever a table is defined with FALLBACK, so the maintenance overhead is effectively doubled.

Q7) What are the roles and responsibilities of Teradata DBA?

Ans:

• User Management – Creation and managing Users, Databases, Roles, Profiles, and Accounts.
• Space Allocation – Assigning Permanent Space, Spool Space, and Temporary Space.
• Access of Database Objects – Granting and Revoking Access Rights on different database objects.
• Security Control – Handling login and logoff rules for Users.
• System Maintenance – Specification of system defaults, restart, etc.
• System Performance – Use of Performance Monitor(PMON), Priority Scheduler, and Job Scheduling.
• Resource Monitoring – Database Query Log(DBQL) and Access Logging.
• Data Archives, Restores, and Recovery – ARC Utility and Permanent Journals.

Q8) What are Access Module Processors?

Ans: The AMP vproc manages Teradata Database interactions with the disk subsystem. Each AMP manages a share of the disk storage.

Q9) How do you transfer large amounts of data in Teradata?

Ans:

• Transferring large amounts of data can be done using various Application Teradata Utilities which resides on the host computer ( Mainframe or Workstation) i.e. BTEQ, FastLaod, MultiLoad, Tpump, and FastExport.
• BTEQ (Basic Teradata Query) supports all 4 DMLs: SELECT, INSERT, UPDATE, and DELETE.
• BTEQ also supports IMPORT/EXPORT protocols.
• Fastload, MultiLoad, and Tpump transfer the data from Host to Teradata.
• FastExport is used to export data from Teradata to the Host.

Q10) What are TPUMP Utility Limitations?

Ans: Following are the limitations of Teradata TPUMP Utility:

• The use of the SELECT statement is not allowed.
• Concatenation of Data Files is not supported.
• Exponential & Aggregate Operators are not allowed.
• Arithmetic functions are not supported.

Q11) How do you create a table with an existing structure of another table with data and with no data?

Ans: Create table Customer dummy as Customer with data / with no data.

Q12) While creating a table my DBA has FALLBACK or NO FALLBACK in his DDL. What is that?

Ans: FALLBACK requests that a second copy of each row inserted into a table be stored on another AMP in the same cluster. This is done when AMP goes down or disk fails.

Q13) What Is The Purpose Of Indexes?

Ans: An index is a mechanism that can be used by the SQL query optimizer to make table access more performant. Indexes enhance data access by providing a more or less direct path to stored data and avoiding the necessity to perform full table scans to locate the small number of rows you typically want to retrieve or update.

Q14) What is the opening step in the Teradata Query script?

Ans: .Logon tdipid/username, password.

Q15) What’s the syntax of substring?

Ans: SUBSTRING (string_expression, n1 [n2])

Q16) Did you use the CASE WHEN statement? Can you tell us a little about it?

Ans: Yes. When a case has to be selected depending upon the value of the expression.