Practice Test GeeksADE - Associate Data Engineer Certification Practice Test
← All ADE Flashcard Decks

Data Modeling & Schema Design Flashcards

7 cards from real ADE practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 Data Modeling & Schema Design flashcards as text

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

    Answer: Store measurable, quantitative data about business events

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

  2. Which normal form eliminates transitive dependencies in a relational database?

    Answer: Third Normal Form (3NF)

    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.

  3. What distinguishes a snowflake schema from a star schema?

    Answer: Snowflake schemas normalize dimension tables into sub-dimensions

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

  4. A surrogate key in data modeling is best described as:

    Answer: A system-generated unique identifier with no business meaning

    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.

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

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

    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.

  6. What is denormalization and when is it typically applied?

    Answer: The process of combining normalized tables to reduce joins, applied in OLAP/analytical systems

    Denormalization intentionally introduces redundancy by merging tables, reducing the number of joins needed and improving read performance in analytical (OLAP) workloads.

  7. In dimensional modeling, a 'conformed dimension' is one that:

    Answer: Is shared and has consistent meaning across multiple fact tables or data marts

    A conformed dimension uses the same keys, column names, and definitions across multiple fact tables or subject areas, enabling consistent drill-across analysis.