Data Modeling and Schema Design Flashcards

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

Answer: Star schema

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

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

Answer: Reduce storage redundancy

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

What is a surrogate key in dimensional modeling?

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

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

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

Answer: Dimensional modeling

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

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

Answer: Hub

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

What does a 'degenerate dimension' refer to in dimensional modeling?

Answer: A dimension key stored in the fact table with no corresponding dimension table

A degenerate dimension is a dimension attribute (like an order number) stored directly in the fact table without a separate dimension table.