Teradata Teradata Data Warehousing 1 — Questions and Answers
Question 1: What is a star schema in the context of Teradata data warehousing?
- A design with a central fact table surrounded by denormalized dimension tables (Correct answer)
- A schema where all tables have the same Primary Index
- A distributed schema across multiple Teradata nodes
- A schema using only views with no base tables
Correct answer: A design with a central fact table surrounded by denormalized dimension tables
A star schema has a central fact table containing measurable business events and surrounds it with denormalized dimension tables describing the context of those events.
Question 2: What is the difference between a fact table and a dimension table in Teradata?
- Fact tables store measurable quantitative data (metrics); dimension tables store descriptive attributes (Correct answer)
- Fact tables are small; dimension tables are large
- Fact tables store current data; dimension tables store historical data
- Fact tables use UPI; dimension tables use NUPI
Correct answer: Fact tables store measurable quantitative data (metrics); dimension tables store descriptive attributes
Fact tables contain numerical measures (sales amount, quantity) and foreign keys to dimensions, while dimension tables contain descriptive attributes (product name, customer city) used for filtering and grouping.
Question 3: What is a Slowly Changing Dimension (SCD) Type 2 in data warehousing?
- Adds a new row for each change, preserving full history with effective date columns (Correct answer)
- Overwrites the existing record with the new value
- Adds a new column for each attribute change
- Stores only the first and last versions of a record
Correct answer: Adds a new row for each change, preserving full history with effective date columns
SCD Type 2 inserts a new row for each attribute change, keeping all historical versions with start/end date or a current-flag indicator.
Question 4: In Teradata data warehousing, what is the purpose of an ODS (Operational Data Store)?
- Provides near-real-time integrated data from multiple operational systems for tactical decisions (Correct answer)
- Stores aggregated historical data for strategic analysis
- Acts as a staging area for raw ETL data before cleansing
- Holds archive data older than a defined retention period
Correct 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.
Question 5: What is the ETL process in data warehousing?
- Extract, Transform, Load — the process of moving and preparing data from source systems into a warehouse (Correct answer)
- Execute, Test, Launch — a software deployment methodology
- Encrypt, Transfer, Log — a data security process
- Extract, Translate, Link — a schema mapping process
Correct answer: Extract, Transform, Load — the process of moving and preparing data from source systems into a warehouse
ETL extracts data from operational sources, transforms it (cleansing, aggregation, enrichment), and loads it into the target data warehouse for analysis.
Question 6: What is a surrogate key in a Teradata dimensional model?
- A system-generated integer key used as the Primary Index of a dimension table, replacing the natural key (Correct answer)
- A foreign key linking a fact table to a dimension table
- A composite key made up of multiple natural key columns
- A key automatically generated by Teradata's IDENTITY column
Correct 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.
Teradata Teradata Data Warehousing 1
What is a star schema in the context of Teradata data warehousing?