ADE Data Modeling & Schema Design 2

Question 1

In the context of data lakes and big data, what does 'schema-on-read' mean?

Accepted Answer

The schema is applied and interpreted when data is queried or read

Answer

Schema-on-read defers schema enforcement to query time, allowing raw data to be stored in flexible formats (JSON, Parquet) and interpreted with different schemas as needed.

Question 2

Which partitioning strategy is most effective for time-series event data that is queried by date range?

Accepted Answer

Range partitioning on an event timestamp column

Answer

Range partitioning on a timestamp column allows the query engine to prune irrelevant partitions entirely, drastically reducing I/O for date-range filters on time-series data.

Question 3

What is a materialized view and how does it differ from a standard view?

Accepted Answer

A materialized view stores pre-computed query results on disk; a standard view is a virtual query alias

Answer

A materialized view physically stores the query result set on disk and must be refreshed periodically, whereas a standard view is just a saved SQL definition that executes on demand.

Question 4

In entity-relationship (ER) modeling, a 'many-to-many' relationship between two entities is typically resolved by:

Accepted Answer

Creating a junction (bridge) table with foreign keys to both entities

Answer

A junction or bridge table holds foreign keys referencing both entities, allowing each entity to relate to multiple rows in the other, which is the standard relational resolution for many-to-many relationships.

Question 5

What is the primary advantage of columnar storage formats (e.g., Parquet, ORC) for analytics workloads?

Accepted Answer

They compress and read only the columns referenced in a query, reducing I/O

Answer

Columnar formats store each column's data contiguously, enabling queries to scan only relevant columns and achieving high compression ratios since column values are similar in type and distribution.

ADE - Associate Data Engineer Certification Practice Test

ADE - Associate Data Engineer Certification Practice Test

ADE Data Modeling & Schema Design 2