CTP CTP Data Management & Analytics 1 — Questions and Answers
Question 1: Which data modeling technique is most appropriate for designing a relational database to support complex reporting requirements in an enterprise system?
- Dimensional modeling with star schema (Correct answer)
- Flat file storage with CSV exports
- Key-value pair storage only
- Unstructured blob storage
Correct answer: Dimensional modeling with star schema
Dimensional modeling with a star schema separates facts from dimensions, making it optimal for complex enterprise reporting queries.
Question 2: What is the primary purpose of data normalization in relational database design?
- To reduce data redundancy and improve data integrity (Correct answer)
- To increase query response time by duplicating data
- To compress binary files for storage savings
- To encrypt sensitive fields automatically
Correct 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.
Question 3: A CTP candidate is evaluating ETL pipeline performance. Which metric best indicates pipeline throughput?
- Records processed per second (Correct answer)
- Number of source tables
- Total disk space used
- Count of transformation rules
Correct answer: Records processed per second
Records processed per second directly measures how efficiently the ETL pipeline moves and transforms data over time.
Question 4: Which approach best handles slowly changing dimensions (SCD) in a data warehouse?
- SCD Type 2: adding new rows with effective date ranges (Correct answer)
- Deleting old records and inserting new ones
- Storing only the latest value in a flat table
- Using real-time streaming for all historical data
Correct 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.
Question 5: What does ACID stand for in the context of database transactions?
- Atomicity, Consistency, Isolation, Durability (Correct answer)
- Accuracy, Completeness, Integrity, Distribution
- Automation, Consistency, Integration, Data
- Atomicity, Compression, Isolation, Delivery
Correct answer: Atomicity, Consistency, Isolation, Durability
ACID properties—Atomicity, Consistency, Isolation, Durability—guarantee reliable database transactions even in the event of errors or failures.
Question 6: A technical professional must choose between OLTP and OLAP systems. Which system is best suited for complex analytical queries across large historical datasets?
- OLAP (Online Analytical Processing) (Correct answer)
- OLTP (Online Transaction Processing)
- A simple flat-file reporting system
- A real-time event streaming platform only
Correct answer: OLAP (Online Analytical Processing)
OLAP systems are optimized for read-heavy analytical queries, aggregations, and historical data analysis across large datasets.
CTP CTP Data Management & Analytics 1
Which data modeling technique is most appropriate for designing a relational database to support complex reporting requirements in an enterprise system?