Data Transformation:
Methods, Process, and Examples

Data transformation converts source data into a format or structure suited to its intended use, as described in dbt Labs’ definition. It includes changing data types, cleaning records, combining datasets, and calculating summaries.

Transformation prepares data for a specific destination or analysis. IBM’s overview identifies business intelligence, data warehousing, machine learning, and data migration as common uses. The required output determines the operations: a report needs appropriate groupings and measures; a machine learning model may need numerical representations of its inputs.

Common data transformation techniques#

Transformation covers changes to individual values, relationships between records, and the structure of an entire dataset. The following operations are documented in database and preprocessing references.

Format standardization also includes converting measurement units and date/time representations into consistent forms, as described in AWS’s transformation guidance. A format change and a type conversion are related operations, but they need separate specifications: the desired representation and the target data type.

Numerical normalization has a more specific meaning in scikit-learn: its normalization documentation defines the operation as scaling individual samples to unit norm. That differs from feature standardization, which centers and scales columns. Name the mathematical operation explicitly when specifying numerical transformations.

Documented examples of data transformation#

Converting text to a number#

BigQuery’s SAFE_CAST documentation includes this query:

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

In Google’s documented example, the result is NULL: the text cannot become an integer, so SAFE_CAST substitutes a null for the runtime conversion error. Ordinary CAST raises an error for that conversion. Impossible conversions between incompatible types can still fail during static analysis.

The practical distinction is failure handling. A successful query containing SAFE_CAST can still contain unsuccessful conversions. Check the resulting nulls rather than treating query completion as evidence that every value converted correctly.

Removing duplicate records#

In pandas’ documented deduplication method, subset selects the columns used to identify duplicates. The keep argument retains the first occurrence, retains the last occurrence, or removes every occurrence of a duplicated value combination.

The documented keep setting selects occurrences by row order. Keeping the last occurrence therefore does not independently identify the most recently updated record. Define the comparison columns and required ordering before removing records.

Preparing numerical features#

The scikit-learn preprocessing guide documents fitting a scaler on training data and applying the fitted transformation to test data. This preserves the training-derived parameters when processing later inputs. Scaling and category encoding are transformations for model inputs, alongside the structural transformations used in reporting.

Data transformation in ETL and ELT#

ETL and ELT describe where transformation occurs within a larger data pipeline. AWS explains the sequence as extraction, transformation, and loading for ETL; ELT reverses the final two stages.

The design question is where the required operations can run. Specify the destination’s processing capabilities and the transformations required before data enters it. ETL and ELT describe execution order; neither acronym defines the correct rows, values, or calculations for a report.

The data transformation process#

IBM describes a process that includes discovery, cleaning, mapping, code generation, execution and validation, and review. These stages can be expressed as concrete tasks:

1. Inspect the source. Identify its structure, types, missing values, inconsistencies, and intended use.
2. Define the target. Specify the output fields, required types, and the meaning of each record.
3. Map the changes. Record how source fields become target fields, including cleaning and derived values.
4. Implement the rules. Write transformation code or configure a transformation tool.
5. Execute and validate. Run the operations and check the resulting data against the requirements.
6. Review the output. Confirm that the transformed dataset supports its intended analysis or destination.

For analytical fact tables, the meaning of each record is called its grain. Kimball Group defines grain as the measurement event represented by one fact-table record. Keys and numerical facts must be consistent with that definition.

A transformation specification should therefore state the output grain before defining keys and calculations. Keep measures at the level they actually describe, and make the grouping used for each summary explicit.

How grain and joins affect revenue totals#

Join behavior directly affects the records available for summing. PostgreSQL documents that an inner join returns a row for every matching pair. Multiple matches repeat values from the other side of the join. Summing an amount after that repetition includes each repeated occurrence.

For revenue-related transformations, check input key uniqueness, the number of matches per key, and the grain of the output before aggregating. Pre-aggregate inputs to a compatible grain when the required result is a summary. These checks follow from the documented join and grouping behavior.

The measure itself must also support addition. Kimball’s fact-table guidance distinguishes additive extended prices from nonadditive unit prices. Summing a unit-price column is a different calculation from summing the extended amounts attached to sales records.

Reconciliation therefore requires an explicit record definition, matching rules, and measure definition. Correct SQL execution alone does not establish that the chosen calculation answers the reporting question.

How to validate transformed data#

Data tests make assertions about inputs or outputs. dbt’s data-test documentation describes these built-in checks:

Custom tests can select records that violate a specific rule. Under dbt’s default data-test behavior, a test passes when its query returns no failing records. Choose assertions that match the output’s grain and field requirements.

It follows from dbt’s assertion-based testing that a passing uniqueness test establishes the configured uniqueness condition. It does not establish the correctness of an amount or the completeness of the source; those are separate conditions.

Tools and incremental processing#

dbt Labs describes SQL and Python as transformation languages and dbt as a SQL-first workflow with modular logic, documentation, and testing. The pandas and scikit-learn operations above provide Python-based examples. AWS Glue provides data integration capabilities for preparing and moving data.

Full rebuilds and incremental runs differ in the rows they process. In dbt’s incremental model documentation, the first run transforms all source rows; later runs process the rows selected by the model’s incremental filter. Updated records require a suitable unique key to avoid introducing modified records as duplicates.

Changing transformation logic also requires attention to historical output. The incremental model reference explains that previously transformed rows can diverge from newly processed rows after a logic change, requiring a rebuild. An incremental filter is therefore part of the transformation’s definition, alongside its calculations and keys.

Frequently asked questions#