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

This set of Data Warehousing MCQs with Answers covers advanced concepts of Data Warehousing Architecture, including Snowflake Schema, Galaxy Schema, fact constellation models, surrogate keys, conformed dimensions, Kimball and Inmon methodologies, and dimensional modeling techniques. Useful for GATE, UGC NET, University Semester Exams, and Big Data Technology preparation.

Topic: Data Warehousing Architecture | Set: 2

Difficulty: Medium to Hard | Total Questions: 15

Important Data Warehousing MCQs with Answers

Q1. What is the structural layout of a “Snowflake Schema” compared to a Star Schema?

A. It removes the central fact table completely to build a series of independent views
B. Its dimension tables are normalized into further nested, secondary tables, reducing data redundancy
C. It forces all numeric metrics to be stored as character text formats
D. It relies entirely on a single flat table that requires no SQL join clauses

Q2. A “Fact Constellation Schema” (or Galaxy Schema) is characterized by:

A. Having zero dimension tables connected to a single small fact table
B. Multiple distinct fact tables sharing one or more conformed dimension tables
C. A design that can only be hosted inside specialized non-relational memory caches
D. A system architecture that blocks users from running multi-table joins

Q3. Bill Inmon’s approach to Data Warehousing architecture is best described as a:

A. Bottom-up design that builds independent data marts before combining them
B. Top-down, centralized architecture where an integrated, normalized Enterprise Data Warehouse (EDW) feeds departmental data marts
C. Cloud-exclusive model that completely avoids using standard ETL software tools
D. System that relies entirely on unindexed flat text files without database structures

Q4. Ralph Kimball’s Data Warehousing design philosophy centers on which primary methodology?

A. A top-down, completely normalized monolithic enterprise relational schema
B. A bottom-up approach focusing on dimensional modeling through conformed data marts joined via an Enterprise Data Bus
C. Eliminating the use of staging environments during data integration pipelines
D. Forcing all analytics to be performed straight against live production OLTP systems

Q5. What does the concept of “Grain” (or Granularity) define in a fact table design?

A. The total physical file footprint measured in kilobytes of the underlying database table
B. The exact level of atomic detail or meaning represented by a single row entry inside the fact table
C. The total count of index structures allowed across surrounding dimension tables
D. The speed degradation factor encountered during simultaneous multi-user database access

Q6. What is the primary architectural purpose of an Operational Data Store (ODS)?

A. Providing long-term, multi-decade deep historical archival storage for data analysis
B. Serving as an integrated, volatile, current-valued repository for real-time operational query needs before data moves to the warehouse
C. Generating visual chart dashboards for corporate executive presentations
D. Executing database backup tasks to deep offline tape storage drives View Answer & Explanation

Q7. In a standard Three-Tier Data Warehouse Architecture, what constitutes the “Middle Tier”?

A. The raw source transactional database systems (OLTP)
B. An OLAP server that provides an abstract multidimensional view of the data for analytical tools
C. The frontend reporting client dashboard visualization software application
D. The staging area hard drive sectors used for raw data cleansing

Q8. What is the core design purpose of a “Surrogate Key” inside a dimension table?

A. Hiding sensitive personal data values from data warehouse database analysts
B. Serving as a simple, internally generated integer primary key that isolates the warehouse from operational natural key changes
C. Connecting physical cloud processing hardware units to local database tables
D. Automatically calculating tax metrics during the loading phase of an ETL pipeline

Q9. A “Degenerate Dimension” is uniquely defined as an attribute that:

A. Contains corrupt or unmapped data rows caused by a pipeline system error
B. Is stored directly inside the fact table row without an associated dimension table reference (e.g., an invoice number)
C. Has been normalized out into a multi-tiered snowflake sub-table layout
D. Is scheduled to be deleted from the system automatically due to old age

Q10. What is a “Junk Dimension”?

A. A low-quality database table filled with unvetted, corrupt source rows
B. A single consolidated dimension table created to store multiple unrelated low-cardinality indicator flags or booleans, reducing fact table columns
C. A temporary table that is automatically generated and deleted after a user runs an OLAP report
D. A dimension table that contains no foreign keys connecting it to the core fact table

Q11. What role does a “Role-Playing Dimension” fulfill in a star schema dimensional model?

A. It changes its internal data types dynamically depending on active user permissions
B. A single database dimension table that is referenced multiple times by a single fact table under different contexts (e.g., Date as Order Date, Shipping Date)
C. It stores employee payroll details and system operational login timestamps
D. It replicates itself across multiple data marts to act as a system firewall

Q12. Why are “Conformed Dimensions” critical to an enterprise data warehouse strategy?

A. They limit data warehouse access strictly to single administrative accounts
B. They maintain a single, universally standardized definition across multiple distinct fact tables, enabling cross-process analysis
C. They force all dimension text values to be compressed using binary hash signatures
D. They convert relational database structures into unindexed text documents

Q13. What is a “Factless Fact Table”?

A. A broken data warehouse table that contains zero data rows due to an ingestion pipeline error
B. A fact table that logs occurrences, events, or relationships (e.g., student class attendance) using only foreign keys, containing no numeric metrics
C. A dimension table that has been mislabeled inside the database metadata registry
D. A temporary analytical index pointer held strictly within volatile server system RAM V

Q14. A metric inside a fact table is classified as “Semi-Additive” if it can be added across:

A. All dimensions unconditionally under any query situation
B. None of the dimensions in the system, requiring averages instead
C. Some dimensions (such as Geography) but not across the Time dimension (e.g., account balance logs)
D. Only text columns using automated string concatenation loops View Answer & Explanation

Q15. What defines a “Non-Additive Fact”?

A. A metric value that can only be stored inside a database table as a text string
B. A numeric measurement, such as a ratio or percentage, that cannot be meaningfully summed across any dimension in the schema
C. A dimension table key that has been hidden from frontend analytical software tools
D. A calculated system attribute that tracks database query runtime speeds

Conclusion

These Data Warehousing MCQ questions covered advanced concepts such as Snowflake Schema, Galaxy Schema, Kimball and Inmon methodologies, surrogate keys, conformed dimensions, factless fact tables, and dimensional modeling strategies.

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

For better understand of concepts and theory, refer Data Warehousing Fact & Dimension Table.

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.