Mixed Deck — All Teradata Topics Flashcards

What is the function of the Ferret utility in Teradata?

Answer: Scans disk storage to verify data integrity and detect cylinder failures

Ferret is a Teradata utility that scans the disk storage subsystem to check for data integrity issues and hardware cylinder faults.

What is the difference between a volatile table and a derived table in Teradata?

Answer: Volatile tables are materialized on disk and persist for the session; derived tables exist only in the query plan

A volatile table is created and populated as a separate DDL/DML step and survives for the session duration, while a derived table is a subquery defined inline within the main SQL.

In Teradata data warehousing, what is the purpose of an ODS (Operational Data Store)?

Answer: Provides near-real-time integrated data from multiple operational systems for tactical decisions

An ODS integrates current-state data from multiple source systems at a low latency, supporting operational reporting and short-term tactical decision-making.

In Teradata, what is the maximum number of Secondary Indexes allowed per table?

Answer: 64

Teradata allows up to 64 secondary indexes (USI + NUSI combined) to be defined on a single table.

What is a Hash Index in Teradata and how does it differ from a Join Index?

Answer: A Hash Index provides fast single-AMP access for non-PI columns; a Join Index pre-materializes joined rows

A Hash Index copies and reorganizes a table using a different hash key, enabling single-AMP access on non-PI columns, while a Join Index stores pre-computed join results.

What is a skewed table in Teradata and why is it a performance problem?

Answer: A table where most rows hash to only a few AMPs, causing uneven workload

Skew occurs when a poorly chosen Primary Index causes most data to land on a small number of AMPs, leaving others idle and creating bottlenecks.

What does the Workload Management (TASM/TDWM) feature do in Teradata?

Answer: Classifies queries into workloads and allocates resources based on priority and throttle rules

TASM (Teradata Active System Management) classifies incoming requests into workload groups and enforces resource allocation, throttling, and priority rules to manage concurrency.

What is the purpose of the WITH clause (Common Table Expression) in Teradata SQL?

Answer: Defines a temporary named result set for use within the query

The WITH clause defines a Common Table Expression (CTE), which is a temporary named result set referenced within the same query.

What is the purpose of aggregate tables in a Teradata data warehouse?

Answer: Store pre-computed summary results to speed up common analytical queries

Aggregate tables contain pre-calculated summaries (e.g., monthly sales totals by region) so that analytical queries can read the small aggregate rather than scanning the entire fact table.

In Teradata, what is a 'fallback' table?

Answer: A table with a duplicate copy of each row on a different AMP

Fallback protection stores a duplicate of every row on a different AMP so data remains accessible if one AMP fails.

What is the purpose of the Teradata 'COLLECT STATISTICS' command?

Answer: To gather demographic data about column values for the optimizer

COLLECT STATISTICS gathers demographic information about column data distributions so the optimizer can build more efficient query plans.

What is a conformed dimension in data warehousing?

Answer: A dimension table shared and consistently defined across multiple fact tables or subject areas

A conformed dimension has standardized definitions and values that are shared across multiple fact tables, enabling consistent cross-subject-area analysis (e.g., a shared Date dimension).

In Teradata, what is a 'volatile table'?

Answer: A temporary table that exists only for the duration of a user session

Volatile tables are session-scoped temporary tables automatically dropped when the session ends.

You create user A with 30 GB of permanent space on a Teradata system that has 100 GB of permanent space. What will user A have if user B is created with 20 GB of permanent space?

Answer: 10 GB

Explanation: If you have a Teradata system with 100 GB of permanent space and you create user A with 30 GB of permanent space, and then user A creates user B with 20 GB permanent space, user A will have 10 GB of permanent space left.

What is a Global Temporary Table in Teradata, and how does it differ from a Volatile Temporary Table?

Answer: A Global Temporary Table's definition persists in the Data Dictionary; data is session-specific, unlike a Volatile Table which has no persistent definition

The definition of a Global Temporary Table is stored in the Data Dictionary and shared across sessions, but each session has its own private data instance that is deleted at session end.

What Teradata module is used to design and oversee ETL procedures?

Answer: Teradata PT

Explanation: Teradata PT is used to create and manage ETL processes.

What does the EXPLAIN command do in Teradata?

Answer: Returns the optimizer's execution plan without running the query

EXPLAIN produces a human-readable description of the execution steps the optimizer plans to use, including join strategies and data movement.

What is the purpose of Fallback in Teradata?

Answer: Stores a second copy of each row on a different AMP for fault tolerance

Fallback protection keeps a duplicate copy of every row on a different AMP, ensuring data availability if one AMP fails.

What does the QUALIFY clause do in Teradata SQL?

Answer: Filters rows based on window function results

QUALIFY filters rows based on the result of an ordered analytical (window) function, similar to HAVING for aggregates.

What is a surrogate key in a Teradata dimensional model?

Answer: A system-generated integer key used as the Primary Index of a dimension table, replacing the natural key

A surrogate key is a meaningless integer assigned by the ETL or warehouse system to uniquely identify dimension rows, independent of the source system's natural key.