Database Normalization and Design Patterns Flashcards

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

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

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.

What is an anchor modeling pattern?

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

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.

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

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

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.

What is a degenerate dimension in data warehouse modeling?

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

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.

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

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

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.

What defines a 'grain' in the context of a fact table, and why is it important?

Answer: The precise definition of what a single row in the fact table represents, declared before adding dimensions or measures

The grain defines exactly what one row in the fact table represents — such as one line item on one sales invoice — and must be declared first to ensure all measures and dimensions are consistent.