Teradata Scenario - interview question

A scenario like ,there is a BTEQ where it has delete statement followed by an insert and update.Suppose the delete statement has been executed successfully and there occurs an error during insert. So in this scenario it should exit the BTEQ and the table should get the previously loaded value which was present before Delete.(handling rollback in Bteqs)

Answer

The Delete and the Insert statements should be written within the BT and ET statemants and rollback should be given before end transaction. if insert fails then it should go to the rollback step. This works only in teradata mode and not in ANSI (please confirm the ans for this question before consolidation)

Read more

What do you mean by skewness?

Skewness refers to the variation in the number of rows among the various AMPs. The higher the variation (some have high number of rows and some have very much less number of rows) then it is said to have high skewness. This affects the effective parallel execution in teradata. The way to get around is by assigning proper indexes to bring about even distribution of rows across the AMPs.

Skewfactor:
It tells the distribution of the rows.
-->If uniformly distributed(normal distribution) skew
factor is 0.


There is aformula to identify the skewness.
Please find the below.
SELECT TableName,SUM(CurrentPerm) AS
CurrentPerm,SUM(PeakPerm) AS PeakPerm,(100 -
(AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor
FROM Dbc.TableSize
WHERE DataBaseName = 'SALES'
GROUP BY 1 ORDER BY 1

Read more

What is the process to restart the multiload if it fails?

MULTILOAD will creates 2 error tables, 1 work table When MULTILOAD fails We have to unlock the Main Table, here error tables and work tables are not locked like FLOAD.

To Unlock the Main Table in case of acquisation Phase :
Mload RELEASE MLOAD ;
To release lock in application phase failure :
RELEASE MLOAD .IN APPLY;

Read more

How would you execute the BTEQ?

Consider a BTEQ script contains the following code in it.

    .logon tpid/user, pass; /* Login credentials */
    SEL  * FROM tab_a; /* Query */
    .quit
You can submit the bteq script in batch mode by .RUN FILE command.

 .RUN FILE = BTEQfile.btq

At unix prompt,Please execute in the below way.

$Sh>BTEQ<[Script Path]>[Logfile Path]
or
$Sh>BTEQ<[Script Path]| TEE [Logfile Path]

Read more

Teradata Realtime Question - Query

The question was how to grade the students according to the total marks.

sel ,stu_id,
CASE WHEN marks>=400 THEN 'GRADE_A'
WHEN marks>300 AND marks < 400 THEN 'GRADE_B' WHEN marks>250 AND marks < 300 THEN 'GRADE_C' ELSE 'FAIL' END

Read more

Teradata Realtime Scenario Question

There is a scenario like the load should start after a particular table fetches a value else it should Bteq session should exit

there is a table A(flag_table) and main table B

No flag

table_a 1

Only when the Flag value is 1 in this table A, the main table B load should start else it should quit.


Answer

SELECT flag FROM table_a WHERE flag =1;

.IF ACTIVITYCOUNT = 0 THEN .GOTO .EXIT

so if the record is not found then activitycount will be zero and it ll exit the BTEQ

Read more

Query to find the highest 3 salaries in a department

SELECT emp_no ,RANK() OVER (ORDER BY salary DESC)"rank" FROM emp_table QUALIFY rank < 3( if there is only one dept) SELECT emp_no,RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) as hig_sal FROM emp_table QUALIFY high_sal < 3(mul depts)

Read more