DMC Database Normalization and Design Patterns 3

Question 1

What is an ODS (Operational Data Store) and how does it differ from a data warehouse?

Accepted Answer

An ODS stores current integrated operational data for near-real-time reporting; a warehouse stores historical data for strategic analysis

Answer

An ODS integrates current operational data from multiple source systems for tactical, near-real-time reporting, while a data warehouse stores historical data optimized for strategic analytics.

Question 2

What is an anchor modeling pattern?

Accepted Answer

A highly normalized temporal modeling technique where each attribute is stored in its own separate table for maximum flexibility and historization

Answer

Anchor modeling stores each attribute in its own table with full historization, enabling maximum schema evolution flexibility at the cost of many joins for queries.

Question 3

What is the purpose of a conformed dimension in data warehouse design?

Accepted Answer

A dimension table that is shared and used identically across multiple fact tables and subject areas, enabling consistent cross-subject analysis

Answer

A conformed dimension is defined once and used consistently across multiple fact tables and data marts, enabling drill-across queries that compare results across subject areas.

Question 4

What is a degenerate dimension in data warehouse modeling?

Accepted Answer

A dimension attribute that lives in the fact table rather than in a separate dimension table because it has no associated descriptive attributes

Answer

A degenerate dimension is a dimensional attribute — such as an invoice number or order number — stored directly in the fact table because it has no associated descriptive data to justify its own table.

Question 5

What is the purpose of a bridge table in dimensional modeling?

Accepted Answer

To resolve many-to-many relationships between a fact table and a dimension that can have multiple values per fact row

Answer

A bridge table resolves multi-valued dimensions — such as a sale attributed to multiple salespeople — by sitting between the fact table and the dimension without violating dimensional model principles.

DMC - Data Modeling Certification Practice Test

DMC - Data Modeling Certification Practice Test

DMC Database Normalization and Design Patterns 3