Important Data Warehousing MCQs with Answers (Set 3) | Big Data Technology

This set of Data Warehousing MCQs covers advanced concepts of Data Warehousing Optimization, including Slowly Changing Dimensions (SCDs), bitmap indexes, materialized views, partitioning, bridge tables, and advanced dimensional modeling techniques. Useful for GATE, UGC NET, University Semester Exams, and Big Data Technology preparation.

Topic: Data Warehousing Optimization | Set: 3

Difficulty: Hard | Total Questions: 15

Important Data Warehousing MCQs with Answers

Q1. In a Slowly Changing Dimension (SCD) implementation, a “Type 1” update strategy handles changes by:

A. Appending a brand-new historical record row with effective date time windows
B. Overwriting the existing attribute data value directly, losing all historical track of the old value
C. Creating a secondary separate data mart table to house the modified values
D. Issuing an automatic query error and halting the entire ETL integration pipeline

Q2. How does a “Type 2” Slowly Changing Dimension (SCD) strategy preserve attribute history?

A. By creating an additional column within the same row to store the immediate past value
B. By inserting a brand-new dimension row containing the updated attributes alongside version numbers or effective start/end dates
C. By moving the entire table structure from a relational database onto deep tape storage media
D. By forcing the fact table to duplicate its rows to match the dimensional update

Q3. What is the defining characteristic of an SCD “Type 3” update pattern?

A. It deletes the entire dimension row if any historical attribute shifts
B. It records a single layer of history by introducing a dedicated “Previous Value” column within the existing row alongside the current value
C. It completely normalizes the star schema layout into a multi-level nested snowflake structure
D. It relies on a separate audit database log table to track changes outside the warehouse

Q4. How does an SCD “Type 4” strategy manage high-frequency dimensional changes?

A. By skipping the use of primary keys across all dimension records
B. By splitting the dimension member into a current-state table and routing all historical change records to a separate dedicated history table
C. By forcing the entire warehouse system to operate exclusively in read-only mode
D. By converting all text attribute descriptions into binary hash keys

Q5. An SCD “Type 6” (or Hybrid) dimension configuration combines the characteristics of which types?

A. Types 0, 4, and 5
B. Types 1, 2, and 3
C. Types 2, 4, and 5
D. Types 1, 4, and 6

Q6. Why are “Bitmap Indexes” heavily preferred over standard B-Tree indexes for certain columns in analytical data warehouses?

A. Bitmap indexes consume significantly more storage space, making them highly secure
B. They are exceptionally efficient for low-cardinality columns (like Gender or Region) and allow fast bitwise logical operations (AND, OR)
C. They require zero computational memory allocations during database search execution paths
D. They completely block multiple users from querying the same table simultaneously

Q7. What database optimization benefit do “Materialized Views” provide to a data warehouse?

A. They compress the underlying hard drive files into human-readable text documents
B. They physically compute and store pre-aggregated query results, allowing analytical tools to bypass scanning massive raw fact tables
C. They prevent the database engine from using surrogate key indexing structures
D. They automatically encrypt all tables from external user view pathways

Q8. Partitioning a massive historical fact table by a chronological attribute (like Order_Date) primarily serves to:

A. Force all query commands to execute slowly across a single drive sector
B. Enable “Partition Pruning,” allowing the query engine to skip reading entire years of irrelevant data blocks during execution
C. Convert a fact constellation schema back into a simple single flat data mart
D. Eliminate the need to define relational foreign keys across the database schema

Q9. In an advanced Data Warehouse environment, what is a “Late-Arriving Dimension”?

A. A dimension row that takes several hours to download from an online cloud storage lake
B. A scenario where a fact record arrives at the warehouse before its associated dimension context has been created in the source system
C. A database table that automatically re-indexes itself after midnight has passed
D. A dimension attribute that refuses to accept string character input values

Q10. How should an engineer handle a “Ragged Hierarchy” inside a structural dimension table (such as an organizational chart)?

A. By completely deleting any row that contains an unequal count of structural levels
B. By utilizing self-referential recursive joins or specialized parent-child table configurations to handle variable-depth levels
C. By forcing the schema to transform into a flat factless fact table layout
D. By banning the use of text attributes across the entire dimension structure

Q11. What design resolution is required when a fact table row maps to a “Multi-Valued Dimension” (e.g., a bank account with multiple independent owners)?

A. Creating duplicate copies of the core fact table for each independent owner attribute
B. Implementing a “Bridge Table” (or associative table) between the fact table and the dimension to map the many-to-many relationship
C. Flattening the entire schema into a single unindexed read-only text file matrix
D. Blocking users from running group aggregations over the target dimensions

Q12. What does an “Aggregate Navigator” do inside an advanced enterprise OLAP architecture?

A. It directs database administrators to the correct server room to fix physical hard drives
B. It automatically intercepts user queries and re-routes them to the smallest available pre-computed summary table that can fulfill the request
C. It translates SQL relational query statements into uncompiled procedural code strings
D. It deletes duplicate historical records from a staging directory

Q13. What is the main structural risk of over-indexing a data warehouse fact table with B-Tree indexes?

A. Queries will be entirely prohibited from running across the database system
B. Update and batch load performance degrades significantly due to the overhead of reconstructing indexes for millions of rows
C. The fact table will automatically drop all of its primary surrogate key indicators
D. Data values will change their relational formatting styles unpredictably

Q14. Tracing “Data Lineage” across an enterprise data warehouse environment is critical because it:

A. Automatically encrypts the user database tables from internal business analysts
B. Provides a transparent audit trail showing exactly how data moved and transformed from its original source system down to final reports
C. Shrinks the physical file system storage allocation on target warehouse disk drives
D. Compiles database dashboard graphics into native machine system code

Q15. When designing conformed facts across a multi-process corporate Fact Constellation schema, what design constraint must be enforced?

A. The facts must use entirely separate text descriptions across different data marts
B. Identical technical names and underlying mathematical calculation logic must be identical across all fact tables to ensure consistent metrics
C. The fact tables cannot share any dimension lookup tables across the schema core
D. The facts must be stored as character data strings instead of numeric attributes

Conclusion

These Data Warehousing MCQs questions covered advanced concepts such as Slowly Changing Dimensions (SCDs), bitmap indexes, materialized views, partition pruning, bridge tables, and enterprise OLAP optimization strategies.

Practicing these questions is useful for Big Data Technology, Enterprise Data Warehousing, GATE CS, UGC NET, and university semester examinations.

For better understanding of theory and concepts, refer to Slowly Changing Dimensions – Data Warehousing

What’s Next?

• HDFS MCQs with Answers
• MapReduce MCQs with Answers
• YARN MCQs with Answers
• Big Data MCQ Questions with Answers
• Hadoop Architecture MCQs with Answers
• Apache Hive MCQs with Answers
• Data Warehousing MCQs with Answers

Please leave this field empty

Prepare smarter with PrepIt 📚

Subscribe to receive new MCQ sets, exam practice questions, semester resources, and technical interview preparation updates directly in your inbox.

We don’t spam! Read our privacy policy for more info.

Check your inbox or spam folder to confirm your subscription.