Data Engineering MCQ — Questions and Answers
Question 1: What term does not have the same meaning as ETL?
- Load
- Extract
- Transform
- To translate (Correct answer)
Correct answer: To translate
ETL stands for Extract, Transform, Load, which are the three core phases of a data integration process. 'Extract' involves gathering data from source systems, 'Transform' involves cleaning and structuring the data, and 'Load' involves writing the processed data to a target system. 'To translate' does not represent a distinct phase or a synonym for the overall ETL process, unlike the other options which are components of ETL.
Question 2: Except for one, they are data technologies found in Google Cloud.
- Bigquery
- Kinesis (Correct answer)
- GCS
- Dataflow
Correct answer: Kinesis
Kinesis is a data streaming service primarily associated with Amazon Web Services (AWS). In contrast, BigQuery (a data warehouse), GCS (Google Cloud Storage), and Dataflow (a data processing service) are all core data technologies offered within the Google Cloud Platform (GCP) ecosystem. Therefore, Kinesis is the technology not found in Google Cloud among the given options.
Question 3: In a data warehouse, a ______ stores and manages both current and historical data over time. It is regarded and implemented as one of the most critical ETL tasks in tracking dimension record history.
- Incremental Changing Dimension (ICD)
- Change Data Capture (CDC)
- Delta Records Capture (DRC)
- Slowly Changing Dimension (SCD) (Correct answer)
Correct answer: Slowly Changing Dimension (SCD)
A Slowly Changing Dimension (SCD) is a technique used in data warehousing to manage and track changes in dimension data over time. It ensures that both current and historical versions of data attributes are stored, providing a complete historical view for analysis. This is crucial for maintaining data integrity and enabling accurate trend analysis in a data warehouse.
Question 4: The unique, meaningless, and sequential numbers generated by the system and attached to data warehouse tables are referred to as:
- Natural or business keys
- Source keys
- Surrogate Keys (Correct answer)
- BKEYS / BMAP
Correct answer: Surrogate Keys
Surrogate keys are system-generated, unique, and meaningless identifiers assigned to rows in data warehouse tables. Unlike natural or business keys, they are not derived from the source system's data and are typically sequential numbers. Their purpose is to provide a stable, efficient, and independent primary key for dimension and fact tables, preventing issues that can arise from changes in natural keys.
Question 5: You have a BigQuery table that contains information about customer purchases, such as the date of purchase, the type of product purchased, the product name, and several other descriptive attributes. There is roughly three years of data. You typically query data by month, followed by customer. You want to scan as little data as possible. How would you set the table?
- Partition by purchase date and cluster by customer (Correct answer)
- Partition by purchase date and cluster by product
- Partition by customer and cluster by product
- Partition by customer and cluster by purchase date
Correct answer: Partition by purchase date and cluster by customer
In BigQuery, partitioning by purchase date significantly reduces the amount of data scanned for monthly queries, as only relevant date partitions are accessed. Clustering by customer within those partitions further optimizes queries that filter or group by customer, as data for specific customers will be physically co-located. This combination minimizes data scanned and improves query performance for the described access patterns.
Question 6: Each row in a Star Schema ________ table represents an event that occurred at a specific time (such as a client opening a contract)
- Dimension Table
- Staging Table
- Primary Table
- Fact Table (Correct answer)
Correct answer: Fact Table
In a Star Schema, a Fact Table is the central table that stores quantitative measures and foreign keys to dimension tables. Each row in a fact table represents a specific event or transaction, such as a sale or a contract opening, along with associated metrics. Dimension tables, on the other hand, provide descriptive attributes related to these events.
Question 7: You're creating a data pipeline to feed into a sales data mart. The project's sponsor has previously experienced quality control issues and has developed a set of rules for filtering out bad data before it enters the data mart. At what point in the data pipeline would you put those rules in place?
- Extract
- Transformation (Correct answer)
- Storage
- Analysis
Correct answer: Transformation
The 'Transformation' phase of a data pipeline is where data is cleaned, validated, enriched, and structured to meet the requirements of the target system. Implementing data quality rules, such as filtering out bad data, is a critical activity performed during this phase. This ensures that only high-quality, reliable data enters the data mart for analysis.
Data Engineering MCQ
What term does not have the same meaning as ETL?