Teradata For Beginner 5 — Questions and Answers
Question 1: What is data skew in Teradata and why is it a problem?
- When queries return incorrect results due to bad statistics
- When rows are unevenly distributed across AMPs, causing some AMPs to do much more work (Correct answer)
- When index subtables grow larger than the base table
- When the BYNET becomes congested during peak hours
Correct answer: When rows are unevenly distributed across AMPs, causing some AMPs to do much more work
Skew occurs when one or more AMPs hold significantly more rows than others, creating a bottleneck because overall query time is limited by the slowest AMP.
Question 2: Which Teradata SQL window function assigns a unique sequential number to each row within a partition?
- RANK()
- DENSE_RANK()
- ROW_NUMBER() (Correct answer)
- NTILE()
Correct answer: ROW_NUMBER()
ROW_NUMBER() assigns a unique integer to every row in a partition with no gaps or ties, unlike RANK() which can have gaps.
Question 3: What is a Global Temporary Table (GTT) in Teradata?
- A permanent table shared across all users
- A table whose definition persists but whose data is private and session-scoped (Correct answer)
- A table automatically populated by Teradata macros
- A table stored entirely in BYNET memory
Correct answer: A table whose definition persists but whose data is private and session-scoped
A GTT has a permanent definition in the data dictionary, but each session gets its own private data instance that disappears when the session ends.
Question 4: Which Teradata utility allows you to both insert new rows and update existing rows in a single operation?
- FastLoad
- BTEQ
- MultiLoad (Correct answer)
- FastExport
Correct answer: MultiLoad
MultiLoad supports multiple DML operations including INSERT, UPDATE, DELETE, and UPSERT against populated tables in a single load job.
Question 5: What is the purpose of a Derived Table in a Teradata SQL query?
- A permanent summary table refreshed on a schedule
- A temporary result set defined in a subquery within the FROM clause (Correct answer)
- A table created from a COLLECT STATISTICS command
- A system-generated table used for index lookups
Correct answer: A temporary result set defined in a subquery within the FROM clause
A derived table is an inline view — a subquery in the FROM clause that produces a temporary result set used by the outer query.
Question 6: In Teradata, what does the NULLIFZERO function do?
- Converts NULL values to zero
- Converts a zero value to NULL (Correct answer)
- Returns zero if the expression is NULL
- Checks whether two values are both zero
Correct answer: Converts a zero value to NULL
NULLIFZERO converts a value of zero to NULL, which is useful for avoiding division-by-zero errors in calculations.
Question 7: Which statement correctly describes a Non-Unique Secondary Index (NUSI) in Teradata?
- It enforces uniqueness on the indexed column and routes directly to one AMP
- It creates a subtable on every AMP and allows duplicate values in the indexed column (Correct answer)
- It is stored only on the primary AMP for the base table
- It replaces the Primary Index for query routing purposes
Correct answer: It creates a subtable on every AMP and allows duplicate values in the indexed column
A NUSI creates a subtable on every AMP containing the index value and row identifiers, allowing duplicate values and enabling faster access for non-PI column lookups.
Teradata For Beginner 5
What is data skew in Teradata and why is it a problem?