What is data transformation?

Data transformation is the process of changing the format, structure, or values of data. For data analytics projects, data may be transformed at two stages of the data pipeline.

Organizations that use on-premises data warehouses generally use an ETL (extract, transform, load) process, in which data transformation is the middle step.

Today, most organizations use cloud-based data warehouses, which can scale compute and storage resources with latency measured in seconds or minutes.

The scalability of the cloud platform lets organizations skip preload transformations and load raw data into the data warehouse, then transform it at query time a model called ELT ( extract, load, transform).

Processes such as data integration, data migration, data warehousing, and data wrangling all may involve data transformation.

Data transformation may be constructive (adding, copying, and replicating data), destructive (deleting fields and records), aesthetic (standardizing salutations or street names), or structural (renaming, moving, and combining columns in a database).

An enterprise can choose among a variety of ETL tools that automate the process of data transformation. Data analysts, data engineers, and data scientists also transform data using scripting languages such as Python or domain-specific languages like SQL.

Benefits and challenges of data transformation

Transforming data yields several benefits:

  • Data is transformed to make it better-organized. Transformed data may be easier for both humans and computers to use.
  • Properly formatted and validated data improves the data quality and protects applications from potential landmines such as null values, unexpected duplicates, incorrect indexing, and incompatible formats.
  • Data transformation facilitates compatibility between applications, systems, and types of data. Data used for multiple purposes may need to be transformed in different ways.

 

There are challenges to transforming data effectively:

  • Data transformation can be expensive. The cost is dependent on the specific infrastructure, software, and tools used to process data. Expenses may include those related to licensing, computing resources, and hiring necessary personnel.
  • Data transformation processes can be resource-intensive. Performing transformations in an on-premises data warehouse after loading, or transforming data before feeding it into applications, can create a computational burden that slows down other operations. If you use a cloud-based data warehouse, you can do the transformations after loading because the platform can scale up to meet demand.
  • Lack of expertise and carelessness can introduce problems during transformation. Data analysts without appropriate subject matter expertise are less likely to notice typos or incorrect data because they are less familiar with the range of accurate and permissible values. For example, someone working on medical data who are unfamiliar with relevant terms might fail to flag disease names that should be mapped to a singular value or notice misspellings.
  • Enterprises can perform transformations that don't suit their needs. A business might change information to a specific format for one application only to then revert the information back to its prior format for a different application.