Data Engineering Data Modeling and Schema Design

Question 1

Which schema design uses a central fact table surrounded by multiple dimension tables with no normalization of dimensions?

Accepted Answer

Star schema

Answer

A star schema has a central fact table directly joined to denormalized dimension tables, resembling a star shape.

Question 2

In a snowflake schema, dimension tables are normalized into multiple related tables primarily to:

Accepted Answer

Reduce storage redundancy

Answer

Snowflake schemas normalize dimensions to reduce data redundancy, trading query simplicity for storage efficiency.

Question 3

What is a surrogate key in dimensional modeling?

Accepted Answer

A system-generated integer used as the primary key for a dimension

Answer

A surrogate key is a system-generated (usually integer) identifier assigned to dimension records, independent of source system keys.

Question 4

Which modeling approach is best suited for ad-hoc analytics and is optimized for read performance in data warehouses?

Accepted Answer

Dimensional modeling

Answer

Dimensional modeling (Kimball approach) uses denormalized star/snowflake schemas optimized for analytical read queries.

Question 5

In data vault modeling, which component stores the unique business keys and acts as the hub of integration?

Accepted Answer

Hub

Answer

Hubs in data vault modeling store unique business keys and serve as the integration point between different source systems.

Data Engineering Practice Test