Teradata Data Warehousing Flashcards

What is a snowflake schema and how does it differ from a star schema?

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.

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 row partitioning by date used for in a data warehouse?

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.

What does OLAP stand for and what is its role in Teradata data warehousing?

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.

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).

What is the benefit of using a Teradata Columnar table (Column Partition) in a data warehouse?

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.