Transforming Data

Data Transformation

Transform raw data into analytical assets using modern transformation patterns, frameworks, and orchestration tools.

Purpose

Select and implement data transformation patterns across the modern data stack. Transform raw data into clean, tested, and documented analytical datasets using SQL (dbt), Python DataFrames (pandas, polars, PySpark), and pipeline orchestration (Airflow, Dagster, Prefect).

When to Use

Invoke this skill when:

• Choosing between ETL and ELT transformation patterns
• Building dbt models (staging, intermediate, marts)
• Implementing incremental data loads and merge strategies
• Migrating pandas code to polars for performance improvements
• Orchestrating data pipelines with dependencies and retries
• Adding data quality tests and validation
• Processing large datasets with PySpark
• Creating production-ready transformation workflows

Quick Start: Common Patterns

dbt Incremental Model

{{
  config(
    materialized='incremental',
    unique_key='order_id'
  )
}}

select order_id, customer_id, order_created_at, sum(revenue) as total_revenue
from {{ ref('int_order_items_joined') }}
group by 1, 2, 3

{% if is_incremental() %}
    where order_created_at > (select max(order_created_at) from {{ this }})
{% endif %}

polars High-Performance Transformation

import polars as pl

result = (
    pl.scan_csv('large_dataset.csv')
    .filter(pl.col('year') == 2024)
    .with_columns([(pl.col('quantity') * pl.col('price')).alias('revenue')])
    .group_by('region')
    .agg(pl.col('revenue').sum())
    .collect()  # Execute lazy query
)

Airflow Data Pipeline

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta

with DAG(
    dag_id='daily_sales_pipeline',
    schedule_interval='0 2 * * *',
    default_args={'retries': 2, 'retry_delay': timedelta(minutes=5)},
    start_date=datetime(2024, 1, 1),
    catchup=False
) as dag:
    extract = PythonOperator(task_id='extract', python_callable=extract_data)
    transform = PythonOperator(task_id='transform', python_callable=transform_data)
    extract >> transform

Decision Frameworks

ETL vs ELT Selection

Use ELT (Extract, Load, Transform) when:

• Using modern cloud data warehouse (Snowflake, BigQuery, Databricks)
• Transformation logic changes frequently
• Team includes SQL analysts
• Data volume 10GB-1TB+ (leverage warehouse parallelism)

Tools: dbt, Dataform, Snowflake tasks, BigQuery scheduled queries

Use ETL (Extract, Transform, Load) when:

• Regulatory compliance requires pre-load data redaction (PII/PHI)
• Target system lacks compute power
• Real-time streaming with immediate transformation
• Legacy systems without cloud warehouse

Tools: AWS Glue, Azure Data Factory, custom Python scripts

Use Hybrid when combining sensitive data cleansing (ETL) with analytics transformations (ELT).

Default recommendation: ELT with dbt unless specific compliance or performance constraints require ETL.

For detailed patterns, see references/etl-vs-elt-patterns.md.

DataFrame Library Selection

Choose pandas when:

• Data size < 500MB
• Prototyping or exploratory analysis
• Need compatibility with pandas-only libraries

Choose polars when:

• Data size 500MB-100GB
• Performance critical (10-100x faster than pandas)
• Production pipelines with memory constraints
• Want lazy evaluation with query optimization

Choose PySpark when:

• Data size > 100GB
• Need distributed processing across cluster
• Existing Spark infrastructure (EMR, Databricks)

Migration path: pandas → polars (easier, similar API) or pandas → PySpark (requires cluster)

For comparisons and migration guides, see references/dataframe-comparison.md.

Orchestration Tool Selection

Choose Airflow when:

• Enterprise production (proven at scale)
• Need 5,000+ integrations
• Managed services available (AWS MWAA, GCP Cloud Composer)

Choose Dagster when:

• Heavy dbt usage (native dbt_assets integration)
• Data lineage and asset-based workflows prioritized
• ML pipelines requiring testability

Choose Prefect when:

• Dynamic workflows (runtime task generation)
• Cloud-native architecture preferred
• Pythonic API with decorators

Safe default: Airflow (battle-tested) unless specific needs for Dagster/Prefect.

For detailed patterns, see references/orchestration-patterns.md.

SQL Transformations with dbt

Model Layer Structure

1. Staging Layer (models/staging/)

   • 1:1 with source tables
   • Minimal transformations (renaming, type casting, basic filtering)
   • Materialized as views or ephemeral

2. Intermediate Layer (models/intermediate/)

   • Business logic and complex joins
   • Not exposed to end users
   • Often ephemeral (CTEs only)

3. Marts Layer (models/marts/)

   • Final models for reporting
   • Fact tables (events, transactions)
   • Dimension tables (customers, products)
   • Materialized as tables or incremental

dbt Materialization Types

View: Query re-run each time model referenced. Use for fast queries, staging layer.

Table: Full refresh on each run. Use for frequently queried models, expensive computations.

Incremental: Only processes new/changed records. Use for large fact tables, event logs.

Ephemeral: CTE only, not persisted. Use for intermediate calculations.