Transforming Data

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.

dbt Testing