Data transformation

Data transformation can be divided into the following steps, each applicable as needed based on the complexity of the transformation required.

• Data discovery
• Data mapping
• Code generation
• Code execution
• Data review

These steps are often the focus of developers or technical data analysts who may use multiple specialized tools to perform their tasks.

The steps can be described as follows:

Data discovery is the first step in the data transformation process. Typically the data is profiled using profiling tools or sometimes using manually written profiling scripts to better understand the structure and characteristics of the data and decide how it needs to be transformed.

Data mapping is the process of defining how individual fields are mapped, modified, joined, filtered, aggregated etc. to produce the final desired output. Developers or technical data analysts traditionally perform data mapping since they work in the specific technologies to define the transformation rules (e.g. visual ETL tools,[3] transformation languages).

Code generation is the process of generating executable code (e.g. SQL, Python, R, or other executable instructions) that will transform the data based on the desired and defined data mapping rules.[4] Typically, the data transformation technologies generate this code[5] based on the definitions or metadata defined by the developers.

Code execution is the step whereby the generated code is executed against the data to create the desired output. The executed code may be tightly integrated into the transformation tool, or it may require separate steps by the developer to manually execute the generated code.

Data review is the final step in the process, which focuses on ensuring the output data meets the transformation requirements. It is typically the business user or final end-user of the data that performs this step. Any anomalies or errors in the data that are found and communicated back to the developer or data analyst as new requirements to be implemented in the transformation process.[1]

There are numerous languages available for performing data transformation. Many transformation languages require a grammar to be provided. In many cases, the grammar is structured using something closely resembling Backus–Naur Form (BNF). There are numerous languages available for such purposes varying in their accessibility (cost) and general usefulness.[14] Examples of such languages include:

• AWK - one of the oldest and popular textual data transformation language;
• Perl - a high-level language with both procedural and object-oriented syntax capable of powerful operations on binary or text data.
• Template languages - specialized to transform data into documents (see also template processor);
• TXL - prototyping language-based descriptions, used for source code or data transformation.
• XSLT - the standard XML data transformation language (suitable by XQuery in many applications);

Additionally, companies such as Trifacta and Paxata have developed domain-specific transformational languages (DSL) for servicing and transforming datasets. The development of domain-specific languages has been linked to increased productivity and accessibility for non-technical users.[15] Trifacta's “Wrangle” is an example of such a domain specific language.[16]

Another advantage of the recent DSL trend is that a DSL can abstract the underlying execution of the logic defined in the DSL, but it can also utilize that same logic in various processing engines, such as Spark, MapReduce, and Dataflow. With a DSL, the transformation language is not tied to the engine.[16]

Although transformational languages are typically best suited for transformation, something as simple as regular expressions can be used to achieve useful transformation. A text editor like vim, emacs or TextPad supports the use of regular expressions with arguments. This would allow all instances of a particular pattern to be replaced with another pattern using parts of the original pattern. For example:

foo ("some string", 42, gCommon);
bar (someObj, anotherObj);

foo ("another string", 24, gCommon);
bar (myObj, myOtherObj);

could both be transformed into a more compact form like:

foobar("some string", 42, someObj, anotherObj);
foobar("another string", 24, myObj, myOtherObj);

In other words, all instances of a function invocation of foo with three arguments, followed by a function invocation with two arguments would be replaced with a single function invocation using some or all of the original set of arguments.

Another advantage to using regular expressions is that they will not fail the null transform test. That is, using your transformational language of choice, run a sample program through a transformation that doesn't perform any transformations. Many transformational languages will fail this test.

• File Formats, Transformation, and Migration (related wikiversity article)
• Data Cleansing
• Data Mapping
• Data Integration
• Data Preparation
• Data Wrangling
• Extract Transform Load
• Information Integration

• Extraction and Transformation at Curlie