CompTIA Data+ (DA0-002)Data MiningMedium
A data team is migrating historical sales data from an old, disparate system into a new data warehouse. The old system has multiple tables with slightly different schemas and naming conventions for customer information (e.g., 'Cust_ID' in one table, 'CustomerID' in another). Before loading, they need to ensure that the customer data from all sources maps correctly to the unified 'CustomerID' and 'CustomerName' columns in the new warehouse. Which step of the ETL process is primarily concerned with reconciling these schema differences and preparing data for the target format?
- ALoading
- BTransformation
- CExtraction
- DValidation
Show answer & explanationAnswer & explanation
Correct answer: B. Transformation
Transformation is the stage in ETL where data is cleaned, standardized, aggregated, and mapped to fit the schema and requirements of the target data warehouse. Reconciling schema differences and standardizing naming conventions (like 'Cust_ID' to 'CustomerID') are core transformation activities.
Why the other options are wrong
- A. Loading is about writing the transformed data into the target system, not performing the changes.
- C. Extraction is about pulling data from source systems, not changing its structure or content.
- D. Validation is a quality check that can occur at various stages, but the actual restructuring and mapping happen during transformation.
ETL Transformation Stage
In the ETL process, the Transformation stage involves applying a set of rules or functions to the extracted data to prepare it for loading into the target data warehouse. This includes cleansing, standardization, aggregation, and schema mapping.
- Key for data quality and consistency.
- Can be resource-intensive depending on complexity.
- Ensures data conforms to the target system's requirements.
Memory trick: Extract, Transform, Load: Get it, Fix it, Put it!