Data Processing ETL and Data Transformation 2 — Questions and Answers
Question 1: What is data aggregation in ETL transformation?
- Combining multiple values into a single summary value such as a sum or average (Correct answer)
- Splitting one large table into multiple smaller ones
- Encrypting data before it is loaded
- Filtering out invalid records before storage
Correct answer: Combining multiple values into a single summary value such as a sum or average
Data aggregation summarizes detailed records into higher-level values, such as computing daily totals from individual transaction records.
Question 2: What is a data pipeline?
- A series of automated steps that move and transform data from source to destination (Correct answer)
- A physical cable connecting two database servers
- A type of database index for large tables
- A graphical report of data flow between departments
Correct answer: A series of automated steps that move and transform data from source to destination
A data pipeline is an automated series of processes that ingests, transforms, and delivers data from source systems to target destinations.
Question 3: What does data enrichment mean in the context of ETL?
- Enhancing source data by adding additional information from other sources (Correct answer)
- Removing unnecessary columns before loading
- Compressing data to reduce storage costs
- Sorting records before inserting them into tables
Correct answer: Enhancing source data by adding additional information from other sources
Data enrichment augments existing records by appending additional relevant attributes from external or reference data sources during transformation.
Question 4: Which tool is widely used in the US enterprise environment for building ETL workflows visually?
- Apache Informatica PowerCenter (Correct answer)
- Microsoft Notepad
- Adobe Acrobat
- VirtualBox
Correct answer: Apache Informatica PowerCenter
Informatica PowerCenter is a leading enterprise ETL platform in the US that provides a visual interface for designing, deploying, and managing data integration workflows.
Question 5: What is a slowly changing dimension (SCD) in data warehousing?
- A dimension table whose attribute values change slowly over time (Correct answer)
- A table that is rarely queried
- A dimension with only one row of data
- A compressed archive of historical fact tables
Correct answer: A dimension table whose attribute values change slowly over time
A slowly changing dimension describes how to handle attributes in a dimension table that change infrequently, such as a customer's address or job title.
Question 6: What is the difference between ETL and ELT?
- ETL transforms data before loading; ELT loads raw data first then transforms it in the target system (Correct answer)
- ETL is for small datasets; ELT is for large datasets only
- ETL uses SQL only; ELT uses Python only
- ETL is batch-only; ELT is always real-time
Correct answer: ETL transforms data before loading; ELT loads raw data first then transforms it in the target system
In ETL the transformation occurs in a staging area before loading, while in ELT raw data is loaded into the target first and transformed there using the target system's compute power.
Data Processing ETL and Data Transformation 2
What is data aggregation in ETL transformation?