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

A schema where all tables connect in a circular chain

Answer

A fully normalized OLTP schema shaped like a star in the ERD

Answer

A schema using only one large table with all data

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

Snowflake uses more fact tables; Star uses more dimension tables

Answer

Snowflake is for OLTP; Star is for OLAP

Answer

Snowflake adds surrogate keys; Star uses natural keys

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 table containing only lookup codes and their descriptions

Answer

A table that stores audit logs of all data changes

Answer

A table containing master data like customers and products

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

A dimension that is never updated once created

Answer

A dimension updated on a slow nightly schedule

Answer

A dimension that stores one record per change with full history

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

All historical records for a business entity

Answer

Only the relationships between two entities

Answer

Descriptive attributes that change over time

Question 6

In Data Vault modeling, what is the purpose of a Link?

Accepted Answer

To capture the relationships and associations between two or more Hubs

Answer

To store descriptive attributes about an entity

Answer

To record the historical values of Hub business keys

Answer

To connect the data vault to the presentation layer