Data Transformation Guide

Data transformation turns raw data into analysis-ready datasets. This is where most data engineering work happens.

The Transformation Layer

Ingestion → Staging (raw) → Transformation (cleaned/joined) → Marts (ready for analysis)
                    ↑                            ↑
              Minimal changes              Business logic

Staging

Lightly cleaned version of raw data. Column renaming, type casting, deduplication.

-- Staging model example
WITH source AS (
    SELECT * FROM raw_orders
)
SELECT
    id AS order_id,
    customer_id,
    order_date::date AS order_date,
    amount / 100.0 AS amount_dollars,    -- cents to dollars
    status
FROM source
WHERE _deleted = FALSE

Marts (Marts are business-facing)

Transformation Approaches

ELT vs ETL

Modern standard: ELT. Load raw data into the warehouse, then transform with SQL or Python.

SQL Transformations

Pros: Native in every warehouse, simple, well-understood.

WITH customer_orders AS (
    SELECT
        customer_id,
        MIN(order_date) AS first_order_date,
        COUNT(*) AS total_orders,
        SUM(amount) AS lifetime_value
    FROM fct_orders
    GROUP BY 1
)
SELECT
    c.*,
    co.first_order_date,
    co.total_orders,
    co.lifetime_value,
    CASE
        WHEN co.total_orders >= 10 THEN 'VIP'
        WHEN co.total_orders >= 3 THEN 'Regular'
        ELSE 'New'
    END AS customer_segment
FROM dim_customers c
LEFT JOIN customer_orders co ON c.customer_id = co.customer_id

Python Transformations

Pros: More complex logic, ML integration, libraries.

import pandas as pd
import numpy as np

def create_rfm_scores(df):
    """RFM (Recency, Frequency, Monetary) scoring."""
    today = pd.Timestamp('2026-01-01')
    
    rfm = df.groupby('customer_id').agg({
        'order_date': lambda x: (today - x.max()).days,  # Recency
        'order_id': 'count',  # Frequency
        'amount': 'sum'  # Monetary
    }).rename(columns={
        'order_date': 'recency',
        'order_id': 'frequency',
        'amount': 'monetary'
    })
    
    # Score into quintiles
    for col in ['recency', 'frequency', 'monetary']:
        rfm[f'{col}_score'] = pd.qcut(rfm[col], 5, labels=False) + 1
    
    rfm['rfm_total'] = rfm[['recency_score', 'frequency_score', 'monetary_score']].sum(axis=1)
    return rfm

dbt (Data Build Tool)

The industry standard for SQL transformations.

dbt Model Structure

models/
├── staging/
│   ├── stg_orders.sql
│   ├── stg_customers.sql
│   └── stg_payments.sql
├── intermediate/
│   └── int_customer_orders.sql
├── marts/
│   ├── dim_customers.sql
│   └── fct_orders.sql
└── sources.yml

Example dbt Model

-- fct_orders.sql
WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
customers AS (
    SELECT * FROM {{ ref('dim_customers') }}
)
SELECT
    orders.order_id,
    customers.customer_id,
    customers.customer_name,
    orders.order_date,
    orders.amount
FROM orders
LEFT JOIN customers
    ON orders.customer_id = customers.customer_id

dbt Tests

# schema.yml
version: 2
models:
  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: email
        tests:
          - not_null
          - unique

Data Cleansing

| Issue | Detection | Fix | |

ETL vs ELT Architecture

Traditional ETL

Extract-Transform-Load transforms data before loading into the target system. This was the dominant pattern when storage was expensive and compute on the target database was limited. Transformation happens in a dedicated processing layer (Spark, Informatica, Talend) and only clean, structured data reaches the target.

Pros: Clean data in the target, lower storage costs, established tooling. Cons: Rigid — schema must be defined before loading. Hard to reprocess historical data.

Modern ELT

Extract-Load-Transform loads raw data first and transforms in-place using the target system’s compute power. This pattern is enabled by cloud data warehouses (Snowflake, BigQuery, Redshift) with virtually unlimited storage and compute separation:

-- Stage raw data
COPY INTO raw_orders
FROM @stage/orders.json
FILE_FORMAT = (TYPE = JSON);

-- Transform with SQL
CREATE TABLE cleaned_orders AS
SELECT
    $1:id::INT AS order_id,
    $1:customer_id::INT AS customer_id,
    $1:amount::DECIMAL(10,2) AS amount,
    $1:timestamp::TIMESTAMP AS order_ts
FROM raw_orders;

Pros: Flexible — load raw data, transform as needed. Data reprocessing is simple (rebuild the table). No transformation pipeline bottleneck. Cons: Requires powerful target system. Raw data storage is larger.

dbt for Transformation

dbt (data build tool) is the most popular ELT transformation tool. It allows analysts to write transformations as SQL SELECT statements, handles dependency resolution, and generates documentation:

-- models/cleaned_orders.sql
{{ config(materialized='table') }}

SELECT
    id AS order_id,
    customer_id,
    amount::DECIMAL(10,2) AS amount,
    created_at::TIMESTAMP AS order_date
FROM {{ source('raw', 'orders') }}
WHERE amount IS NOT NULL

dbt handles incremental models, test automation, and documentation generation. Its lineage graph shows which models depend on which sources and intermediate models.

Data Transformation Testing

Test transformations at every stage: schema validation (column presence, data types), data quality (null rates, value ranges, uniqueness), referential integrity (foreign key relationships), and business rules (calculated fields match expectations). Automate tests in CI/CD to catch regressions before they reach production.

FAQ

What is data engineering? Data engineering builds infrastructure and pipelines to collect, store, and process data at scale. It involves ETL/ELT processes, data warehousing, streaming systems, and ensuring data quality and accessibility for analytics and machine learning.

What is the difference between ETL and ELT? ETL (Extract-Transform-Load) transforms data before loading into the target system. ELT (Extract-Load-Transform) loads raw data first and transforms in-place. ELT leverages modern data warehouses like Snowflake and BigQuery for compute power.

What is a data pipeline? A data pipeline moves data from source systems to destinations, applying transformations along the way. Pipelines can be batch (scheduled intervals) or streaming (continuous processing). Tools include Airflow, dbt, and Spark.

Should I use a data lake or a data warehouse? Data lakes store raw data in native formats (cheap, flexible). Data warehouses store structured, curated data optimized for analytics (faster, more expensive). Modern architectures use both — lakehouse architecture combines their strengths.

What is the role of a data catalog? A data catalog indexes and documents available data assets — their schemas, lineage, quality metrics, and ownership. It helps data consumers discover, understand, and trust the data they work with.

ETL vs ELT Architecture

Traditional ETL

Extract-Transform-Load transforms data before loading into the target system. This was the dominant pattern when storage was expensive and compute on the target database was limited. Transformation happens in a dedicated processing layer (Spark, Informatica, Talend) and only clean, structured data reaches the target.

Pros: Clean data in the target, lower storage costs, established tooling. Cons: Rigid — schema must be defined before loading. Hard to reprocess historical data.

Modern ELT

Extract-Load-Transform loads raw data first and transforms in-place using the target system’s compute power. This pattern is enabled by cloud data warehouses (Snowflake, BigQuery, Redshift) with virtually unlimited storage and compute separation:

-- Stage raw data
COPY INTO raw_orders
FROM @stage/orders.json
FILE_FORMAT = (TYPE = JSON);

-- Transform with SQL
CREATE TABLE cleaned_orders AS
SELECT
    $1:id::INT AS order_id,
    $1:customer_id::INT AS customer_id,
    $1:amount::DECIMAL(10,2) AS amount,
    $1:timestamp::TIMESTAMP AS order_ts
FROM raw_orders;

Pros: Flexible — load raw data, transform as needed. Data reprocessing is simple (rebuild the table). No transformation pipeline bottleneck. Cons: Requires powerful target system. Raw data storage is larger.

dbt for Transformation

dbt (data build tool) is the most popular ELT transformation tool. It allows analysts to write transformations as SQL SELECT statements, handles dependency resolution, and generates documentation:

-- models/cleaned_orders.sql
{{ config(materialized='table') }}

SELECT
    id AS order_id,
    customer_id,
    amount::DECIMAL(10,2) AS amount,
    created_at::TIMESTAMP AS order_date
FROM {{ source('raw', 'orders') }}
WHERE amount IS NOT NULL

dbt handles incremental models, test automation, and documentation generation. Its lineage graph shows which models depend on which sources and intermediate models.

Data Transformation Testing

Test transformations at every stage: schema validation (column presence, data types), data quality (null rates, value ranges, uniqueness), referential integrity (foreign key relationships), and business rules (calculated fields match expectations). Automate tests in CI/CD to catch regressions before they reach production.

—|—|—| | Null values | IS NULL check | Default value, or filter | | Duplicates | ROW_NUMBER() OVER (...) | Deduplicate | | Outliers | Statistical (z-score, IQR) | Cap, flag, or investigate | | Invalid formats | Regex patterns | Standardize | | Inconsistent casing | Lower/upper check | Normalize |

-- Deduplicate with row_number
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM raw_customers
)
SELECT * FROM ranked WHERE rn = 1

Data Quality in Transformations

# Python transformation with quality checks
def transform_orders(raw_df):
    assert raw_df['order_id'].is_unique, "order_id must be unique"
    assert not raw_df['customer_id'].isna().any(), "customer_id cannot be null"
    
    transformed = (raw_df
        .assign(
            amount_dollars = raw_df['amount'] / 100.0,
            order_date = pd.to_datetime(raw_df['order_date'])
        )
        .query('amount >= 0')  # Filter negative amounts
    )
    
    MIN_ROWS = len(raw_df) * 0.9
    assert len(transformed) >= MIN_ROWS, "Too many rows dropped"
    
    return transformed

Advanced Transformations

Slowly Changing Dimensions (SCD Type 2)

Track changes to dimension attributes over time.

-- SCD Type 2 merge
INSERT INTO dim_customers (customer_id, email, city, effective_date, end_date, is_current)
SELECT
    customer_id,
    email,
    city,
    '2026-01-01' AS effective_date,
    NULL AS end_date,
    TRUE AS is_current
FROM source
WHERE (customer_id, email, city) NOT IN (
    SELECT customer_id, email, city
    FROM dim_customers
    WHERE is_current = TRUE
)

Window Functions

-- Running total per customer
SELECT
    order_id,
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM fct_orders

Transformation Pipeline Anti-Patterns

A well-structured transformation pipeline makes data analysis reliable and repeatable.

Data Ingestion Guide — Data Modeling Guide — dbt Guide