What is Data Transformation?

Summary

Data transformation turns raw data extracted from source systems into usable, trusted datasets for operational needs, analytics and decision-making. The process addresses inconsistencies, missing values and duplicates through techniques such as cleansing, de-duplication, integration, aggregation, validation and format revision, often mapping data to a target schema during ETL. Performance considerations include compaction, partitioning, file-size tuning, data skipping and pruning, and caching to reduce storage, scanning, processing time and latency. The article also compares on-premises, cloud, open-source, proprietary, no-code, low-code and codable ETL tools, then describes Spark Declarative Pipelines and Lakeflow Jobs on Databricks for quality checks, pipeline development, monitoring and orchestration. A Navy Federal Credit Union proof of concept illustrates a rapid deployment, with a proof of concept completed in a week and a dashboard released days after production deployment.

Takeaways

1. 1

   In ETL, transformation is the stage where data is cleansed, mapped and converted into a usable resource, often according to a specific schema, before loading.

2. 2

   Partitioning can improve query performance by enabling parallel processing and reducing the amount of data scanned; data skipping and pruning further limit unnecessary reads.

3. 3

   Spark Declarative Pipelines includes Expectations for applying data quality checks to every record passing through a query, while Lakeflow Jobs manages and monitors multitask workflows.