---
title: "Data transformation"
description: "Data transformation is the process of converting data from its raw, source format into a structured form suited to analysis — through operations like cleaning, joining, aggregating, and reshaping."
canonical: "https://motherduck.com/glossary/data-transformation/"
related:
  - title: "Data Transformation | MotherDuck Docs"
    url: "https://motherduck.com/docs/integrations/transformation/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Transform Stock Data with SQLMesh on MotherDuck | MotherDuck Docs"
    url: "https://motherduck.com/docs/cookbook/sqlmesh-demo/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# Data transformation

> Data transformation is the process of converting data from its raw, source format into a structured form suited to analysis — through operations like cleaning, joining, aggregating, and reshaping.

## Overview

Data transformation is the "T" in ETL/ELT: the step where raw data extracted from source systems is converted into the shape needed for reporting, analytics, or downstream applications. Transformations range from simple (casting a string to a date, renaming a column) to complex (joining a dozen tables, computing rolling aggregates, applying business logic to derive a metric).

## ETL vs. ELT

In traditional **ETL** (extract, transform, load), data is transformed in a separate processing step before being loaded into the destination warehouse. In the now more common **ELT** (extract, load, transform) pattern, raw data is loaded first, and transformation happens afterward using the compute of the destination system itself — typically expressed as SQL. This shift is largely why SQL-based transformation tools like dbt have become central to modern data stacks: transformation logic lives as version-controlled SQL models that run directly against the warehouse or query engine.

## Transformation logic in SQL

Most transformations are combinations of a small set of primitives:

```sql
SELECT
  o.order_id,
  c.customer_name,
  CAST(o.order_date AS DATE) AS order_date,
  o.quantity * o.unit_price AS line_total,
  CASE WHEN o.quantity * o.unit_price > 100 THEN 'large' ELSE 'small' END AS order_size
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
```

Aggregating and reshaping are equally common:

```sql
SELECT
  customer_id,
  DATE_TRUNC('month', order_date) AS month,
  SUM(line_total) AS monthly_revenue
FROM stg_orders
GROUP BY ALL;
```

DuckDB's `GROUP BY ALL` groups by every selected column that isn't an aggregate, which removes a common source of errors when a `GROUP BY` list falls out of sync with the `SELECT` list as a model evolves.

## Transformation pipelines with dbt-duckdb

dbt organizes transformation logic into layered SQL models (commonly staging → intermediate → mart), each building on the last, with dependencies tracked automatically. Using dbt with the `dbt-duckdb` adapter lets you develop and test this same layered SQL transformation logic locally against files or an embedded DuckDB database, then run the identical models against a larger warehouse in production.

## Why it matters

Transformation is where raw, source-shaped data becomes business-meaningful: normalized keys, consistent types, derived metrics, and joined context. Because transformation logic encodes business rules, keeping it version-controlled, tested, and documented (as dbt encourages) matters as much as getting any individual query right.