10 Teradata SQL Interview Questions and Answers

Teradata SQL is a powerful tool used for managing and analyzing large-scale data warehousing operations. Known for its scalability and parallel processing capabilities, Teradata SQL is a preferred choice for organizations that handle vast amounts of data and require high-performance analytics. Its robust architecture and comprehensive SQL support make it an essential skill for data professionals.

This guide offers a curated selection of Teradata SQL interview questions designed to help you demonstrate your expertise and problem-solving abilities. By familiarizing yourself with these questions, you can confidently showcase your knowledge and stand out in technical interviews.

Teradata SQL Interview Questions and Answers

1. How would you handle NULL values in a dataset when performing aggregations?

In Teradata SQL, NULL values can be managed using functions like COALESCE or CASE WHEN to replace NULLs with a default value before performing aggregations. This ensures that the NULL values do not skew the results of the aggregation.

Example:

SELECT 
    SUM(COALESCE(column_name, 0)) AS total_sum,
    AVG(COALESCE(column_name, 0)) AS average_value,
    COUNT(CASE WHEN column_name IS NOT NULL THEN 1 ELSE NULL END) AS non_null_count
FROM 
    table_name;

In this example, COALESCE is used to replace NULL values with 0 for the SUM and AVG functions. For the COUNT function, a CASE WHEN statement is used to count only non-NULL values.

2. What is the difference between a SET table and a MULTISET table?

The primary difference between a SET table and a MULTISET table in Teradata SQL is their handling of duplicate rows. A SET table does not allow duplicates, ensuring each row is unique, while a MULTISET table permits duplicates, offering flexibility for data that may naturally contain them.

Here is a brief comparison:

• SET Table: Ensures uniqueness by eliminating duplicates.
• MULTISET Table: Allows duplicate rows.

3. Explain the concept of Partitioned Primary Index (PPI) and its benefits.

A Partitioned Primary Index (PPI) in Teradata SQL organizes data by partitioning tables based on column values, enhancing query performance, especially for range-based conditions. Benefits include improved query efficiency, better data management, and reduced I/O operations.

4. Write a SQL query to delete duplicate records from a table while keeping one instance of each record.

To delete duplicate records from a table while keeping one instance, use the ROW_NUMBER() window function with a common table expression (CTE).

Example:

WITH CTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column1) AS row_num
    FROM 
        your_table
)
DELETE FROM CTE
WHERE row_num > 1;

Replace your_table with the table name and column1, column2 with the columns defining a duplicate record. The CTE assigns a row number to each record, and the DELETE statement removes duplicates.

5. Describe how Teradata handles transaction control and what commands are used.

Teradata handles transaction control with commands like BEGIN TRANSACTION, COMMIT, and ROLLBACK. Transactions can be implicit or explicit, with explicit transactions manually controlled by the user.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

INSERT INTO audit_log (action, timestamp) VALUES ('Updated Sales salaries', CURRENT_TIMESTAMP);

COMMIT;

In this example, the salary update and audit log insertion are treated as a single transaction.

6. Explain the concept of Skewness and how it affects performance.

Skewness in Teradata SQL refers to uneven data distribution across AMPs, leading to performance issues. To mitigate skewness, choose an appropriate primary index, use secondary indexes, or consider partitioning.

7. Describe the process of collecting statistics and why it is important.

Collecting statistics in Teradata SQL involves using the COLLECT STATISTICS statement to gather data about columns or indexes. This helps the optimizer choose efficient execution plans, manage resources, and maintain query performance.

8. Write a SQL query to implement recursive querying using WITH RECURSIVE clause.

Recursive querying in Teradata SQL is achieved using the WITH RECURSIVE clause, allowing operations on hierarchical data by repeatedly executing a query until a condition is met.

Example:

WITH RECURSIVE EmployeeHierarchy (EmployeeID, ManagerID, Level) AS (
    SELECT EmployeeID, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    SELECT e.EmployeeID, e.ManagerID, eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, ManagerID, Level
FROM EmployeeHierarchy;

In this example, the anchor member selects top-level employees, and the recursive member finds employees reporting to them.

9. Discuss the importance of data distribution and how it impacts query performance.

Data distribution in Teradata SQL impacts query performance. Even distribution ensures balanced workload across AMPs, while uneven distribution can cause performance issues. The Primary Index (PI) controls data distribution, so selecting an appropriate PI is important for balanced distribution.

10. Provide an example of how you would optimize a query for better performance.

To optimize a query for better performance, consider strategies like proper indexing, query rewriting, collecting statistics, and using efficient joins.

Example:

-- Original Query
SELECT a.column1, b.column2
FROM table1 a
JOIN table2 b ON a.id = b.id
WHERE a.column3 = 'value';

-- Optimized Query
COLLECT STATISTICS ON table1 COLUMN (column3);
COLLECT STATISTICS ON table2 COLUMN (id);

SELECT a.column1, b.column2
FROM table1 a
JOIN table2 b ON a.id = b.id
WHERE a.column3 = 'value'
AND b.column4 = 'another_value';

In the optimized query, statistics are collected on relevant columns, and an extra filter condition is added to reduce the number of rows processed.