Teradata DBA Interview Questions

What is the difference between FastLoad and MultiLoad?

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

What is the difference between Inner join and outer join?

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.

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

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

How Do You Determine The Number Of Sessions?

Teradata performance and workload.

Client platform type, performance and workload.

Channel performance for channel attached systems.

Network topology and performance for network attached systems

Volume of data to be processed by the application.

What is OLAP?

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

Aspired to become an Teradata DBA? 

Explore the post to discover the know-hows on Teradata DBA Training.

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

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.

What are the roles and responsibilities of teradata DBA ?

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 logon 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.

What is Access Module Processors?

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

How do you transfer large amount of data in Teradata?

Transferring of large amount 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 support 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.

What are TPUMP Utility Limitations?

Following are the limitations of Teradata TPUMP Utility:

Use of SELECT statement is not allowed.

Concatenation of Data Files is not supported.

Exponential & Aggregate Operators are not allowed.

Arithmatic functions are not supported.

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

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

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

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.

What Is The Purpose Of Indexes?

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 moreor- 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.

What is the opening step in Teradata Query script?

.Logon tdipid/username, password.

What’s the syntax of sub string?

SUBSTRING (string_expression, n1 [n2])

Did you use CASE WHEN statement. Can you tell us a little about it?

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