Data Modeling & Schema Design Flashcards

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

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.

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

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.

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

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.

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

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.

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

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.

In the Data Vault modeling approach, which component stores descriptive attributes and their historical changes?

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.

Cardinality in data modeling most directly refers to:

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).