CTP Data Management & Analytics Flashcards

Which data modeling technique is most appropriate for designing a relational database to support complex reporting requirements in an enterprise system?

Answer: Dimensional modeling with star schema

Dimensional modeling with a star schema separates facts from dimensions, making it optimal for complex enterprise reporting queries.

What is the primary purpose of data normalization in relational database design?

Answer: To reduce data redundancy and improve data integrity

Normalization eliminates redundant data and organizes columns and tables to ensure data dependencies make logical sense, improving integrity.

A CTP candidate is evaluating ETL pipeline performance. Which metric best indicates pipeline throughput?

Answer: Records processed per second

Records processed per second directly measures how efficiently the ETL pipeline moves and transforms data over time.

Which approach best handles slowly changing dimensions (SCD) in a data warehouse?

Answer: SCD Type 2: adding new rows with effective date ranges

SCD Type 2 preserves full history by inserting new rows with start/end dates, enabling point-in-time historical analysis.

What does ACID stand for in the context of database transactions?

Answer: Atomicity, Consistency, Isolation, Durability

ACID properties—Atomicity, Consistency, Isolation, Durability—guarantee reliable database transactions even in the event of errors or failures.

A technical professional must choose between OLTP and OLAP systems. Which system is best suited for complex analytical queries across large historical datasets?

Answer: OLAP (Online Analytical Processing)

OLAP systems are optimized for read-heavy analytical queries, aggregations, and historical data analysis across large datasets.