Data Modeling & Schema Design Flashcards
7 cards from real ADE practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Data Modeling & Schema Design flashcards as text
What is the key difference between OLTP and OLAP data models?
Answer: OLTP models are normalized for fast transactional reads/writes; OLAP models are denormalized for fast analytical queries
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.
A composite key in relational modeling is:
Answer: A primary key composed of two or more columns that together uniquely identify a row
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.
Which schema design pattern is known as OBT (One Big Table)?
Answer: A fully denormalized single wide table that joins all facts and dimensions
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.
When designing a data model for a streaming pipeline, which consideration is MOST important?
Answer: Choosing an append-only schema that supports late-arriving data and event-time ordering
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.
What is a Type 1 Slowly Changing Dimension (SCD) strategy?
Answer: Overwrite the existing dimension record with the new attribute value, losing history
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.
In data warehouse design, a 'degenerate dimension' is best described as:
Answer: A dimension attribute stored directly in the fact table with no corresponding dimension table
A degenerate dimension is a dimension key (like an invoice number or order ID) that resides in the fact table itself because it has no descriptive attributes that would justify a separate dimension table.
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?
Answer: Data Vault with Hubs, Links, and Satellites
Data Vault is designed for auditability (every row is timestamped and sourced), scalability (new sources are added without breaking existing structure), and enterprise integration, making it ideal for large, complex environments.