ADE Data Modeling & Schema Design 2 — Questions and Answers
Question 1: In the context of data lakes and big data, what does 'schema-on-read' mean?
- The schema is enforced when data is written to storage
- The schema is applied and interpreted when data is queried or read (Correct answer)
- The schema is validated by a central metadata catalog
- The schema is automatically inferred and stored in the file header
Correct answer: The schema is applied and interpreted when data is queried or read
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?
- Hash partitioning on a user ID column
- Range partitioning on an event timestamp column (Correct answer)
- List partitioning on a country code column
- Round-robin partitioning across all available nodes
Correct answer: Range partitioning on an event timestamp column
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?
- A materialized view executes the query at runtime; a standard view stores pre-computed results
- A materialized view stores pre-computed query results on disk; a standard view is a virtual query alias (Correct answer)
- A materialized view only works with columnar storage; a standard view works with row storage
- A materialized view cannot be refreshed; a standard view is always up to date
Correct answer: A materialized view stores pre-computed query results on disk; a standard view is a virtual query alias
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:
- Adding a foreign key column to one of the two tables
- Creating a junction (bridge) table with foreign keys to both entities (Correct answer)
- Merging the two entities into a single wide table
- Adding a composite primary key to one entity
Correct answer: Creating a junction (bridge) table with foreign keys to both entities
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?
- They allow faster single-row lookups by primary key
- They compress and read only the columns referenced in a query, reducing I/O (Correct answer)
- They enforce ACID transactions across all write operations
- They replicate data automatically across multiple nodes
Correct answer: They compress and read only the columns referenced in a query, reducing I/O
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.
Question 6: In the Data Vault modeling approach, which component stores descriptive attributes and their historical changes?
- Hub
- Link
- Satellite (Correct answer)
- Bridge
Correct answer: Satellite
Satellites in Data Vault store the contextual, descriptive attributes (business data) attached to Hubs or Links, with each row versioned by load date to capture historical changes.
Question 7: Cardinality in data modeling most directly refers to:
- The number of indexes defined on a table
- The number of unique values in a column or the nature of a relationship between entities (Correct answer)
- The physical size in bytes of a table's storage footprint
- The number of foreign keys pointing to a dimension table
Correct answer: The number of unique values in a column or the nature of a relationship between entities
Cardinality describes either the distinctness of values in a column (high/low cardinality) or the numerical relationship between entities (one-to-one, one-to-many, many-to-many).
ADE Data Modeling & Schema Design 2
In the context of data lakes and big data, what does 'schema-on-read' mean?