ICT 394 Quiz Session 03 - ETL, OLTP, OLAP &
Multidimensional Data Questions and Correct Answers |
myMurdoch Learning 2026 | 100% Score - 147 Questions and
Answers Already Graded A+ Premium Exam Tested And
Verified
Subject Area Data Warehousing and Business Intelligence
Description This exam assesses advanced knowledge of ETL processes, OLTP vs. OLAP
systems, multidimensional data modeling, and data warehousing architectures. It
covers topics such as star schemas, snowflake schemas, fact tables, dimension
tables, slowly changing dimensions, data integration, and query performance
optimization.
Expected Grade A+
Total Questions 147
Duration 3 hours
Learning Outcomes 1. Analyze and design ETL workflows for complex data integration scenarios.
2. Differentiate OLTP and OLAP systems and apply appropriate modeling
techniques.
3. Evaluate multidimensional data models using star and snowflake schemas.
4. Implement and manage slowly changing dimensions and fact table strategies.
5. Optimize query performance for OLAP workloads using indexing and
aggregation techniques.
Accreditation This exam adheres to the rigorous standards of top-tier US research universities
(Ivy League and R1 institutions) for undergraduate and graduate-level data
management courses.
Page 1
,Question 1 of 147
In a slowly changing dimension (SCD) Type 2 implementation for a 'Customer' dimension, what is
the primary impact on a star schema when a customer changes their address?
A. The existing fact rows referencing the old customer key are updated to point to the new customer surrogate
key.
B. A new row is inserted for the customer with a new surrogate key, and existing fact rows remain referencing the
old surrogate key.
C. The address attribute in the dimension row is overwritten, and a new surrogate key is assigned to the same
natural key.
D. The dimension table is normalized to a snowflake schema to avoid duplicate keys.
The correct answer is:
Correct Action: A new row is inserted for the customer with a new surrogate key, and existing fact rows
remain referencing the old surrogate key.
Rationales
• A new row is inserted for the customer with a new surrogate key, and existing fact rows remain referencing the
old surrogate key. (Correct):
This is the correct action. SCD Type 2 preserves history by inserting a new dimension row for the changed attribute with a
new surrogate key. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A describes
SCD Type 1 (overwrite). Option C is incorrect because Type 2 inserts a new row, not overwrites. Option D describes
• The existing fact rows referencing the old customer key are updated to point to the new customer surrogate
key. (Incorrect):
This option is not appropriate. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A
describes SCD Type 1 (overwrite)
• The address attribute in the dimension row is overwritten, and a new surrogate key is assigned to the same
natural key. (Incorrect):
This option is not appropriate. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A
describes SCD Type 1 (overwrite)
• The dimension table is normalized to a snowflake schema to avoid duplicate keys. (Incorrect):
This option is not appropriate. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A
describes SCD Type 1 (overwrite)
Page 2
,Question 2 of 147
A data warehouse fact table stores sales data with foreign keys to dimensions: Date, Product, Store,
and Customer. Which design would minimize the table's row count while maintaining the same
granularity?
A. Pre-join all dimensions into a single wide dimension table.
B. Use a degenerate dimension for attributes like transaction ID.
C. Partition the fact table by month using range partitioning.
D. Convert the fact table to a snowflake schema with normalized dimensions.
The correct answer is:
Correct Action: Use a degenerate dimension for attributes like transaction ID.
Rationales
• Use a degenerate dimension for attributes like transaction ID. (Correct):
This is the correct action. A degenerate dimension is a dimension key stored in the fact table without a corresponding
dimension table, often used for transactional identifiers. This reduces the number of dimension tables but does not change
row count. Pre-joining dimensions would not reduce rows. Partitioning changes physical storage but not row count.
• Pre-join all dimensions into a single wide dimension table. (Incorrect):
This option is not appropriate. This reduces the number of dimension tables but does not change row count. Pre-joining
dimensions would not reduce rows
• Partition the fact table by month using range partitioning. (Incorrect):
This option is not appropriate. This reduces the number of dimension tables but does not change row count. Pre-joining
dimensions would not reduce rows
• Convert the fact table to a snowflake schema with normalized dimensions. (Incorrect):
This option is not appropriate. This reduces the number of dimension tables but does not change row count. Pre-joining
dimensions would not reduce rows
Page 3
, Question 3 of 147
During the ETL process for a data warehouse, a source system transaction table contains a
timestamp column with time zone information. The warehouse requires all timestamps to be in UTC.
Which transformation step is most appropriate to ensure consistency?
A. Apply a time zone offset conversion in the extraction phase before any staging.
B. Store the original timestamp and offset in a separate column and convert only during query execution.
C. Convert the timestamp to UTC during the transformation phase after staging but before loading into the
warehouse.
D. Ignore time zones and assume all source systems are already in UTC.
The correct answer is:
Correct Action: Convert the timestamp to UTC during the transformation phase after staging but before
loading into the warehouse.
Rationales
• Convert the timestamp to UTC during the transformation phase after staging but before loading into the
warehouse. (Correct):
This is the correct action. Converting to UTC during the transformation phase ensures that all data loaded into the
warehouse is consistent and ready for analysis. Extraction should be as raw as possible. Storing with offset adds complexity
and requires runtime conversion. Ignoring time zones can lead to errors if sources are in different zones.
• Apply a time zone offset conversion in the extraction phase before any staging. (Incorrect):
This option is not appropriate. Extraction should be as raw as possible. Storing with offset adds complexity and requires
runtime conversion
• Store the original timestamp and offset in a separate column and convert only during query execution.
(Incorrect):
This option is not appropriate. Extraction should be as raw as possible. Storing with offset adds complexity and requires
runtime conversion
• Ignore time zones and assume all source systems are already in UTC. (Incorrect):
This option is not appropriate. Extraction should be as raw as possible. Storing with offset adds complexity and requires
runtime conversion
Page 4
Multidimensional Data Questions and Correct Answers |
myMurdoch Learning 2026 | 100% Score - 147 Questions and
Answers Already Graded A+ Premium Exam Tested And
Verified
Subject Area Data Warehousing and Business Intelligence
Description This exam assesses advanced knowledge of ETL processes, OLTP vs. OLAP
systems, multidimensional data modeling, and data warehousing architectures. It
covers topics such as star schemas, snowflake schemas, fact tables, dimension
tables, slowly changing dimensions, data integration, and query performance
optimization.
Expected Grade A+
Total Questions 147
Duration 3 hours
Learning Outcomes 1. Analyze and design ETL workflows for complex data integration scenarios.
2. Differentiate OLTP and OLAP systems and apply appropriate modeling
techniques.
3. Evaluate multidimensional data models using star and snowflake schemas.
4. Implement and manage slowly changing dimensions and fact table strategies.
5. Optimize query performance for OLAP workloads using indexing and
aggregation techniques.
Accreditation This exam adheres to the rigorous standards of top-tier US research universities
(Ivy League and R1 institutions) for undergraduate and graduate-level data
management courses.
Page 1
,Question 1 of 147
In a slowly changing dimension (SCD) Type 2 implementation for a 'Customer' dimension, what is
the primary impact on a star schema when a customer changes their address?
A. The existing fact rows referencing the old customer key are updated to point to the new customer surrogate
key.
B. A new row is inserted for the customer with a new surrogate key, and existing fact rows remain referencing the
old surrogate key.
C. The address attribute in the dimension row is overwritten, and a new surrogate key is assigned to the same
natural key.
D. The dimension table is normalized to a snowflake schema to avoid duplicate keys.
The correct answer is:
Correct Action: A new row is inserted for the customer with a new surrogate key, and existing fact rows
remain referencing the old surrogate key.
Rationales
• A new row is inserted for the customer with a new surrogate key, and existing fact rows remain referencing the
old surrogate key. (Correct):
This is the correct action. SCD Type 2 preserves history by inserting a new dimension row for the changed attribute with a
new surrogate key. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A describes
SCD Type 1 (overwrite). Option C is incorrect because Type 2 inserts a new row, not overwrites. Option D describes
• The existing fact rows referencing the old customer key are updated to point to the new customer surrogate
key. (Incorrect):
This option is not appropriate. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A
describes SCD Type 1 (overwrite)
• The address attribute in the dimension row is overwritten, and a new surrogate key is assigned to the same
natural key. (Incorrect):
This option is not appropriate. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A
describes SCD Type 1 (overwrite)
• The dimension table is normalized to a snowflake schema to avoid duplicate keys. (Incorrect):
This option is not appropriate. Existing fact rows reference the old surrogate key, maintaining historical accuracy. Option A
describes SCD Type 1 (overwrite)
Page 2
,Question 2 of 147
A data warehouse fact table stores sales data with foreign keys to dimensions: Date, Product, Store,
and Customer. Which design would minimize the table's row count while maintaining the same
granularity?
A. Pre-join all dimensions into a single wide dimension table.
B. Use a degenerate dimension for attributes like transaction ID.
C. Partition the fact table by month using range partitioning.
D. Convert the fact table to a snowflake schema with normalized dimensions.
The correct answer is:
Correct Action: Use a degenerate dimension for attributes like transaction ID.
Rationales
• Use a degenerate dimension for attributes like transaction ID. (Correct):
This is the correct action. A degenerate dimension is a dimension key stored in the fact table without a corresponding
dimension table, often used for transactional identifiers. This reduces the number of dimension tables but does not change
row count. Pre-joining dimensions would not reduce rows. Partitioning changes physical storage but not row count.
• Pre-join all dimensions into a single wide dimension table. (Incorrect):
This option is not appropriate. This reduces the number of dimension tables but does not change row count. Pre-joining
dimensions would not reduce rows
• Partition the fact table by month using range partitioning. (Incorrect):
This option is not appropriate. This reduces the number of dimension tables but does not change row count. Pre-joining
dimensions would not reduce rows
• Convert the fact table to a snowflake schema with normalized dimensions. (Incorrect):
This option is not appropriate. This reduces the number of dimension tables but does not change row count. Pre-joining
dimensions would not reduce rows
Page 3
, Question 3 of 147
During the ETL process for a data warehouse, a source system transaction table contains a
timestamp column with time zone information. The warehouse requires all timestamps to be in UTC.
Which transformation step is most appropriate to ensure consistency?
A. Apply a time zone offset conversion in the extraction phase before any staging.
B. Store the original timestamp and offset in a separate column and convert only during query execution.
C. Convert the timestamp to UTC during the transformation phase after staging but before loading into the
warehouse.
D. Ignore time zones and assume all source systems are already in UTC.
The correct answer is:
Correct Action: Convert the timestamp to UTC during the transformation phase after staging but before
loading into the warehouse.
Rationales
• Convert the timestamp to UTC during the transformation phase after staging but before loading into the
warehouse. (Correct):
This is the correct action. Converting to UTC during the transformation phase ensures that all data loaded into the
warehouse is consistent and ready for analysis. Extraction should be as raw as possible. Storing with offset adds complexity
and requires runtime conversion. Ignoring time zones can lead to errors if sources are in different zones.
• Apply a time zone offset conversion in the extraction phase before any staging. (Incorrect):
This option is not appropriate. Extraction should be as raw as possible. Storing with offset adds complexity and requires
runtime conversion
• Store the original timestamp and offset in a separate column and convert only during query execution.
(Incorrect):
This option is not appropriate. Extraction should be as raw as possible. Storing with offset adds complexity and requires
runtime conversion
• Ignore time zones and assume all source systems are already in UTC. (Incorrect):
This option is not appropriate. Extraction should be as raw as possible. Storing with offset adds complexity and requires
runtime conversion
Page 4
