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 models are optimized for complex aggregations; OLAP models are optimized for fast transactional writes

Answer

OLTP models use columnar storage; OLAP models use row-based storage

Answer

OLTP models support only batch processing; OLAP models support only streaming

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 primary key generated by a sequence or UUID function

Answer

A foreign key that references multiple parent tables simultaneously

Answer

An index created across two or more columns for query optimization

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

A heavily normalized schema where every attribute is in its own table

Answer

A schema where all tables share a single composite primary key

Answer

A partitioned schema where one physical table spans multiple nodes

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

Defining multi-column composite foreign keys for every entity relationship

Answer

Normalizing all dimension tables to at least 3NF before ingestion

Answer

Using integer surrogate keys instead of UUIDs for all primary keys

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

Add a new row for each change and maintain effective date columns

Answer

Store current and previous values in separate columns on the same row

Answer

Create a separate history table linked to the main dimension table

Question 6

In data warehouse design, a 'degenerate dimension' is best described as:

Accepted Answer

A dimension attribute stored directly in the fact table with no corresponding dimension table

Answer

A dimension table with very few rows that is merged into the fact table

Answer

A dimension that has been deprecated and is no longer actively loaded

Answer

A dimension with extremely high cardinality that degrades query performance

Question 7

Which data modeling approach is BEST suited for an enterprise requiring a single source of truth, full historical auditability, and agile addition of new data sources?

Accepted Answer

Data Vault with Hubs, Links, and Satellites

Answer

Inmon-style 3NF enterprise data warehouse

Answer

Kimball-style dimensional star schema data mart

Answer

One Big Table (OBT) approach