DMC DMC ETL Processes & Data Warehousing 2 — Questions and Answers
Question 1: Which of the following best describes a data warehouse according to Bill Inmon's definition?
- A physical server room for storing data hardware
- A subject-oriented, integrated, time-variant, non-volatile collection of data for decision support (Correct answer)
- A database optimized for transactional processing
- A backup storage system for operational data recovery
Correct answer: A subject-oriented, integrated, time-variant, non-volatile collection of data for decision support
Inmon defined a data warehouse as subject-oriented, integrated, time-variant, and non-volatile — properties that distinguish it from operational databases.
Question 2: What is a 'star schema' in data warehouse design?
- A schema with multiple fact tables connected in a sequential chain
- A central fact table connected directly to multiple denormalized dimension tables (Correct answer)
- A normalized schema shaped like a snowflake with sub-dimension tables
- A schema design that contains no dimension tables
Correct answer: A central fact table connected directly to multiple denormalized dimension tables
The star schema features a central fact table linked directly to denormalized dimension tables, creating a star shape that simplifies query writing.
Question 3: How does a 'snowflake schema' differ from a star schema?
- It has no fact table at its center
- Dimension tables are normalized into multiple related sub-tables (Correct answer)
- It uses significantly fewer dimensions than a star schema
- All tables are stored in third normal form including the fact table
Correct answer: Dimension tables are normalized into multiple related sub-tables
In a snowflake schema, dimension tables are normalized into related sub-tables, reducing data redundancy at the cost of increased query complexity.
Question 4: What is a 'fact table' in a data warehouse?
- A table containing static reference or lookup data
- A table that stores measurable, quantitative data about business events (Correct answer)
- A master table listing all available data sources
- A table containing row-level user permissions
Correct answer: A table that stores measurable, quantitative data about business events
A fact table stores quantitative measurements about business events — such as sales amounts or order quantities — and links to dimension tables via foreign keys.
Question 5: What is a 'slowly changing dimension' (SCD) in data warehousing?
- A dimension table that is updated every second with real-time data
- A dimension where attribute values change gradually over time and must be tracked historically (Correct answer)
- A dimension table that contains only a small number of rows
- A dimension that is queried infrequently in production reports
Correct answer: A dimension where attribute values change gradually over time and must be tracked historically
A slowly changing dimension tracks how dimension attributes change over time — such as customer address updates — with different SCD types managing history retention differently.
Question 6: What does OLAP stand for in data warehousing?
- Online Logical Analysis Processing
- Offline Large-scale Analytical Processing
- Online Analytical Processing (Correct answer)
- Operational Load and Aggregation Protocol
Correct answer: Online Analytical Processing
OLAP stands for Online Analytical Processing, which supports multidimensional data analysis for complex queries and business decision support.
DMC DMC ETL Processes & Data Warehousing 2
Which of the following best describes a data warehouse according to Bill Inmon's definition?