CDP Data Architecture and Modeling 2 — Questions and Answers
Question 1: In a snowflake schema, how do dimension tables differ from those in a star schema?
- They contain fact data instead of descriptive data
- They are normalized into multiple related tables rather than a single denormalized table (Correct answer)
- They do not have primary keys
- They store historical data using SCD techniques
Correct answer: They are normalized into multiple related tables rather than a single denormalized table
In a snowflake schema, dimension tables are normalized into multiple related sub-tables, reducing redundancy compared to the denormalized dimension tables in a star schema.
Question 2: What is a Slowly Changing Dimension (SCD) Type 2?
- A dimension where old values are overwritten with new values
- A dimension that tracks history by adding new rows with effective date ranges when attributes change (Correct answer)
- A dimension that keeps only the original value forever
- A dimension with no historical tracking
Correct answer: A dimension that tracks history by adding new rows with effective date ranges when attributes change
SCD Type 2 tracks historical changes by adding new rows with start/end dates, allowing analysis of both current and historical dimension attribute values.
Question 3: Which data architecture style uses a central repository where raw data is stored in its native format until needed?
- Data Mart
- Operational Data Store (ODS)
- Data Lake (Correct answer)
- Data Vault
Correct answer: Data Lake
A Data Lake stores raw data in its native format at scale, allowing flexible analysis later, unlike data warehouses that require structure upfront.
Question 4: What is a conceptual data model?
- A detailed physical design of database tables and indexes
- A high-level representation of business concepts and relationships without technical detail (Correct answer)
- A model that defines data types and constraints
- A diagram showing ETL data flows
Correct answer: A high-level representation of business concepts and relationships without technical detail
A conceptual data model provides a high-level, business-focused representation of key entities and relationships without technical implementation details.
Question 5: In data modeling, what does cardinality describe?
- The number of columns in a table
- The numerical relationship between instances of two entities (e.g., one-to-many) (Correct answer)
- The size of a database in gigabytes
- The number of indexes on a table
Correct answer: The numerical relationship between instances of two entities (e.g., one-to-many)
Cardinality describes the quantitative relationship between entity instances, such as one-to-one, one-to-many, or many-to-many relationships.
Question 6: What is the primary advantage of a Data Vault modeling approach over traditional star/snowflake schemas?
- Simpler queries for end users
- Better auditability, flexibility for change, and support for loading from multiple source systems (Correct answer)
- Faster OLAP query performance
- Reduced storage requirements
Correct answer: Better auditability, flexibility for change, and support for loading from multiple source systems
Data Vault modeling excels at auditability, handles source system changes gracefully, and supports parallel loading from multiple sources using its Hub-Satellite-Link structure.
CDP Data Architecture and Modeling 2
In a snowflake schema, how do dimension tables differ from those in a star schema?