ADE Data Modeling & Schema Design 3

Question 1

What is the key difference between OLTP and OLAP data models?

Accepted Answer

OLTP models are normalized for fast transactional reads/writes; OLAP models are denormalized for fast analytical queries

Answer

OLTP databases use normalized schemas to minimize redundancy and support high-throughput transactional operations, while OLAP systems use denormalized star/snowflake schemas tuned for complex aggregation queries over large datasets.

Question 2

A composite key in relational modeling is:

Accepted Answer

A primary key composed of two or more columns that together uniquely identify a row

Answer

A composite key uses a combination of two or more columns to form the primary key, where no single column alone is sufficient to uniquely identify each row.

Question 3

Which schema design pattern is known as OBT (One Big Table)?

Accepted Answer

A fully denormalized single wide table that joins all facts and dimensions

Answer

OBT pre-joins all relevant fact and dimension data into a single wide, flat table, eliminating join costs at query time and simplifying analytics but increasing storage and update complexity.

Question 4

When designing a data model for a streaming pipeline, which consideration is MOST important?

Accepted Answer

Choosing an append-only schema that supports late-arriving data and event-time ordering

Answer

Streaming data models must accommodate append-only writes and handle out-of-order or late-arriving events by incorporating event timestamps for correct time-window computations.

Question 5

What is a Type 1 Slowly Changing Dimension (SCD) strategy?

Accepted Answer

Overwrite the existing dimension record with the new attribute value, losing history

Answer

SCD Type 1 simply overwrites the existing row with the new attribute value, making it the simplest approach but one that permanently loses the previous historical value.

ADE - Associate Data Engineer Certification Practice Test

ADE - Associate Data Engineer Certification Practice Test

ADE Data Modeling & Schema Design 3