ADE Data Modeling & Schema Design

Question 1

In a star schema, what is the primary role of a fact table?

Accepted Answer

Store measurable, quantitative data about business events

Answer

A fact table stores quantitative metrics (measures) about business events, such as sales amounts or order counts, and references dimension tables via foreign keys.

Question 2

Which normal form eliminates transitive dependencies in a relational database?

Accepted Answer

Third Normal Form (3NF)

Answer

Third Normal Form (3NF) eliminates transitive dependencies by ensuring every non-key column depends only on the primary key, not on other non-key columns.

Question 3

What distinguishes a snowflake schema from a star schema?

Accepted Answer

Snowflake schemas normalize dimension tables into sub-dimensions

Answer

In a snowflake schema, dimension tables are further normalized into related sub-dimension tables, reducing redundancy at the cost of more complex joins.

Question 4

A surrogate key in data modeling is best described as:

Accepted Answer

A system-generated unique identifier with no business meaning

Answer

A surrogate key is a system-generated (often sequential integer or UUID) unique identifier that has no inherent business meaning, used to uniquely identify rows in a dimension table.

Question 5

Slowly Changing Dimension (SCD) Type 2 handles attribute changes by:

Accepted Answer

Creating a new row with the updated value and tracking effective dates

Answer

SCD Type 2 inserts a new dimension row for each change, adding effective start/end dates or a current flag to maintain full historical tracking of attribute changes.

ADE - Associate Data Engineer Certification Practice Test

ADE - Associate Data Engineer Certification Practice Test

ADE Data Modeling & Schema Design