DMC Database Normalization and Design Patterns 2

Question 1

What is the Star Schema pattern used in data warehouse modeling?

Accepted Answer

A central fact table surrounded by denormalized dimension tables, optimized for analytical query performance

Answer

The Star Schema places a central fact table containing measures and foreign keys surrounded by denormalized dimension tables, enabling fast analytical queries with simple joins.

Question 2

How does a Snowflake Schema differ from a Star Schema?

Accepted Answer

Snowflake normalizes dimension tables into sub-dimensions, reducing redundancy but requiring more joins; Star denormalizes for simplicity

Answer

A Snowflake Schema normalizes dimension tables by splitting hierarchies into separate tables, reducing data redundancy at the cost of more complex queries requiring additional joins.

Question 3

What is a fact table in dimensional modeling?

Accepted Answer

A central table in a star or snowflake schema that stores measurable business events with foreign keys to dimension tables

Answer

A fact table stores measurable, quantitative data about business events — such as sales amounts, quantities, or counts — along with foreign keys linking to the relevant dimensions.

Question 4

What is a slowly changing dimension (SCD) Type 1?

Accepted Answer

A method of handling attribute changes by simply overwriting the old value, preserving no history

Answer

SCD Type 1 handles changes by overwriting the old attribute value with the new one, which is simple but destroys historical data — appropriate when history is not needed.

Question 5

In a Data Vault model, what does a Hub contain?

Accepted Answer

The unique business keys of a core business entity along with metadata about its source and load date

Answer

A Hub in Data Vault modeling stores the unique business keys for a core business concept, along with a surrogate key, load date, and record source — no descriptive attributes.

DMC - Data Modeling Certification Practice Test

DMC - Data Modeling Certification Practice Test

DMC Database Normalization and Design Patterns 2