Data transformation

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:

Copy code
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:

Copy code
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.