Teradata Teradata Data Warehousing 2 — Questions and Answers
Question 1: What is a snowflake schema and how does it differ from a star schema?
- A snowflake schema normalizes dimension tables into sub-dimensions, creating more joins than a star schema (Correct answer)
- A snowflake schema has no fact table; a star schema requires one
- A snowflake schema uses fewer joins than a star schema
- A snowflake schema stores aggregations; a star schema stores raw data
Correct answer: A snowflake schema normalizes dimension tables into sub-dimensions, creating more joins than a star schema
A snowflake schema normalizes dimension tables into multiple related tables (e.g., splitting Customer into Customer and City), reducing redundancy but requiring more joins.
Question 2: What is the purpose of aggregate tables in a Teradata data warehouse?
- Store pre-computed summary results to speed up common analytical queries (Correct answer)
- Archive raw data before it ages out of the warehouse
- Store intermediate results for ETL transformation steps
- Hold error records from failed load operations
Correct 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.
Question 3: In Teradata, what is row partitioning by date used for in a data warehouse?
- Enables partition elimination so queries with date filters scan only relevant partitions (Correct answer)
- Distributes rows evenly by date across all AMPs
- Enforces a retention policy that automatically deletes old partitions
- Provides row-level security based on the date column
Correct answer: Enables partition elimination so queries with date filters scan only relevant partitions
Partitioning a large fact table by date means queries with a date-range filter can skip entire partitions (partition elimination), dramatically reducing I/O.
Question 4: What does OLAP stand for and what is its role in Teradata data warehousing?
- Online Analytical Processing — supports complex multi-dimensional analysis of large historical datasets (Correct answer)
- Online Application Processing — manages real-time transaction records
- Optimized Load and Aggregate Process — an ETL framework
- Offline Latent Analytics Platform — a batch reporting system
Correct answer: Online Analytical Processing — supports complex multi-dimensional analysis of large historical datasets
OLAP systems, including Teradata-based warehouses, support complex analytical queries involving aggregations, trend analysis, and multi-dimensional data slicing over large historical datasets.
Question 5: What is a conformed dimension in data warehousing?
- A dimension table shared and consistently defined across multiple fact tables or subject areas (Correct answer)
- A dimension table that changes slowly over time using SCD methods
- A dimension automatically generated by the ETL tool without user definition
- A dimension table partitioned by a range of values
Correct 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).
Question 6: What is the benefit of using a Teradata Columnar table (Column Partition) in a data warehouse?
- Stores each column separately, reducing I/O for queries that access only a few columns of a wide table (Correct answer)
- Sorts all columns alphabetically for faster lookups
- Enables row-level encryption per column
- Automatically compresses numeric columns to 2 bytes
Correct answer: Stores each column separately, reducing I/O for queries that access only a few columns of a wide table
Column Partitioning stores each column or column group in separate physical blocks, so queries that select a subset of columns read only the relevant blocks rather than entire rows.
Teradata Teradata Data Warehousing 2
What is a snowflake schema and how does it differ from a star schema?